top of page
Search

Financial Reporting with Power BI "Out-of-the-Box" for Business Central

Katoen Natie is one of the largest global service providers in the world. It offers logistics

solutions to companies from several key industries and markets and knows how to adapt to their specific needs. Strong of 9000 employees, the privately-owned company is present in 28 countries.


Customer Challenges

After many years working with Microsoft Navision 4.0 as their global ERP System, Katoen Natie decided to switch to Dynamics 365 Business Central.

Given that the company was creating reports within Navision and sourcing its data from the Navision database, these reports had to be updated manually, which took a long time. Moreover, the solution at the time offered no dashboarding nor visual capabilities.


The new reporting solution they were looking for had to be easy to use and had to allow users to make their own BI reports and dashboards. They also wanted to be able to continue reporting on the historical data from Navision and do "trend" analysis over time.


As no data migration was considered in the operational ERP upgrade from Navision to Business Central, a well-structured "data layer" had to be able to combine both historical (Navision) and new transactional data (Business Central).


This necessity made their reporting solution of the time unsustainable. The envisioned scope was Finance covering General Ledger, Analytical Ledger, Accounts Payable and Accounts Receivable.


As mentioned, Katoen Natie is a worldwide company that has customers and consumers all over the world. They therefore needed to have their data secured through an extended set of security rules defining what data the users could access based on their "user profile".


Since reports had always been created directly on top of Navision, another requirement was to be able to see the new data being refreshed throughout the day, allowing business users to always see the latest data.


Solution

With the will of migrating from Navision to Business Central while still being able to have reports both on Navision and Business Central, the traditional set-up of creating reports directly on the ERP system became obsolete. After consulting different vendors, Katoen Natie decided that element61's Dynamics Business Central/NAV Power BI "Out-of-the-Box" solution could fit their needs.


Our Business Central/NAV Power BI "Out-of-the-Box" solution is a pre-packaged Business Intelligence solution based, between other, on Navision/Business Central as a source and providing many viewpoints on business data. As it works for all versions of Navision and Business Central, the solution allows users to integrate data quickly from Navision and Business Central in a data warehouse layer. Katoen Natie had done quite some custom development in Navision and Business Central and this custom logic was also added and implemented in the data warehouse ETL logic.

SQL Server Master Data Services (MDS) manages the master data for the security rules and users and the entities needing to be loaded. Different security filters are set up, following dimensions such as company, business unit, reporting line, profit centre and location. While MDS contains the configuration rules, it is the Tabular analysis model that enforces them. As Katoen Natie has many entities, it was important to avoid loading all the entities into the data warehouse during development. Alternatively, a table was set up in MDS to allow the selection of Navision entities that should be loaded, as well as the addition of new entities. With both a web interface and a Microsoft Excel interface, MDS made it possible for business end-users to adapt rapidly to the new interface.


A Tabular model was built on top of the data warehouse using Microsoft SQL Server Analysis Services. This model holds all the calculations and business logic and ensures fast and performant analysis and reporting. The security rules defined in MDS are built in this model and are applied to all end-users consulting the reports.


SQL Server Reporting Services (SSRS) was chosen as a reporting tool. This server-based report-generating solution is ideal for fixed-layout reports optimized for printing. These reports were built on top of the Tabular model. As their complexity could have overloaded the Tabular model for self-service reporting, very custom reports were built in SSRS via SQL Server stored procedures.


Business users are also able to do their own analysis via Excel. The latter can be connected to the Tabular model, giving users the opportunity to create their own reports and analysis in their "beloved" Excel interface. The row-level security defined in the Tabular model will also be applied on reports created in Excel. Users creating reports in Excel will only see the data they are allowed to see based on the rules defined in the MDS configuration.


A last feature required from the business users was that the new data should be loaded during the day, as Katoen Natie has data from entities worldwide. Therefore, we implemented incremental loading to allow business users to see new data within one hour.


Technology

The Microsoft Business Intelligence stack was used to build the solution.


In the front-end Microsoft SQL Server Reporting Services was used to create pixel perfect paginated reports for financial controllers worldwide. The Tabular model can also be accessed through Microsoft Excel.


Role of element61

element61 was in charge of:

  • The installation and configuration of all standard components of the Dynamics Business Central/NAV Power BI "Out-of-the-Box", delivered in three working days per module.

  • The set-up of the Tabular model for analysis.

  • The application of row-level security in Tabular model.

  • The creation of sets of reports in SSRS.


Conclusion

Business users are now able to analyse both historical Navision and new Business Central financial data with BI reports and to create additional ad hoc analysis using Excel. The built-in security prevents users from unauthorized access to the data. Katoen Natie can easily analyse financial data from all entities worldwide thanks to the delivered BI platform, which is quicker, more extensive and user-friendly.





תגובות


התגובות הושבתו לפוסט הזה.
bottom of page