- The client needed a consistent data model for the financial systems used in the organization.
- Efficient planning and cost accounting analysis from various accounting systems called for the integration of multiple data sources.
- An additional goal of the project carried out by JCommerce was the development of financial reporting and the preparation of operational reports for financial controllers.
- Due to the large amount of data, the challenge was to solve performance problems that forced changes to the model during the project.
- The necessity of introducing a complicated Row-Level Security solution that enables access management of the data at each user level was also a big challenge. This security is based on the data from the warehouse and makes it possible to limit access to specific data by defining dynamic permissions, which may even change on a daily basis.
- It was also important to create a single, coherent account plan for all units in the organization. This is a long-term goal that will be fulfilled during using the report by users.
Read also: Business Intelligence services
The main purpose of the work was to implement the OPEX report, including the provision of more detailed business requirements, for the industrial operations department – it consisted of creating a tabular model of SQL Server Analysis Services Tabular enterprise class and supplying it with Power BI technology. As part of the project, we were also responsible for the validation and correct preparation of data for the tabular model. During the project, the JCommerce team presented changes in the report on a regular basis during weekly meetings. User acceptance tests (UATs) were also conducted with the participation of business users, followed by training.
Cooperation on the project
The implementation of the project required cooperation with many people on the client side, including project managers, business analysts, and individuals responsible for source data or with a data warehouse team.
“Thanks to the visits of JCommerce consultants to Sweden at key moments, such as acceptance tests, we have managed to reduce the distance between both sides – we were able to get to know each other personally and establish partner relationships. The rest of the work was carried out remotely by JCommerce employees.”
Katarzyna, Senior BI Specialist
The Data Warehouse team prepared a comprehensive model on the data warehouse side, which then had to be simplified and adapted to the requirements of in-memory technology, ensuring better efficiency. The client has gained a tool in which he receives any reports, e.g. based on a two-year data range, in a few minutes. It previously took several hours to generate such a report.
- shortening the report generation time from a few hours to a few minutes,
- faster and more effective analysis of financial data,
- data security thanks to Row-Level Security access security.
Good practices in the project
- using TFS – a version control system,
- using BISM Normalizer to manage tabular models,
- using Performance Analyzer as part of Power BI to search for problematic visualizations and minimize performance problems,
- frequent validation of new functionalities with users,
- presence at the key moments of the project.
- SSAS Tabular Model 2017,
- Power BI Report Server,
- Data Lake,
- Visual Studio,
- DAX Studio,
- Tabular Editor,
- BISM Normalizer.