“Combining financial historical data from Microsoft Dynamics NAV and new Business Central data into one common BI reporting environment”
Katoen Natie is a global service provider for several key industries, offering integrated logistics solutions. The company has known a great expansion over the years, having grown into one of the largest integrated logistics services providers in the world, servicing many different markets, each with its own specific needs. Today Katoen Natie employs more than 9000 people in 28 countries and is still a privately-owned company.
Customer Challenges
Katoen Natie has been working for many years on Microsoft Navision 4.0 as their global ERP system and has decided to migrate to Dynamics 365 Business Central.
Their current reports are created within Navision and sourced directly from the Navision database. This implies that the reports need to be refreshed manually and that it can take a long time to refresh. Their current solution has no dashboarding or visuals capabilities.
They wanted to use a reporting tool that is easy to use, easy to access, and allows users to make their own reports. As they are moving from Navision 4.0 to Business Central, they want to continue reporting on the historical data from Navision to be able to do “trend” analysis over time. No data migration was considered in the operational upgrade from Navision to Business Central. This triggered the need for a well-structured “data layer” to be able to combine both historical (Navision) as new (Business Central) transactional data. This requirement made their current reporting solution (straight onto the ERP/source system) not to be sustainable nor future proof. The envisioned scope was Finance covering General Ledger, Analytical Ledger, Accounts Payable and Accounts Receivable.
As Katoen Natie has entities all over the world and therefore report consumers all over the world, it was crucial to them to secure their data. An extended set of security rules has been set up to define what data the users can access based on their “user profile”
Since reports were created directly on top of Navision, business users always saw the latest data. An important requirement was to be able to see new data being refreshed during the day.
Solution
As they were migrating to Business Central and wanted to be able to have reports on both Navision and Business Central, the traditional setup of creating reports directly on the ERP system was no longer valid. After consulting different vendors, they decided to go forward with the Dynamics Business Central / NAV BI “Out-of-the-Box” solution from element61.
The Business Central / NAV BI “Out-of-the-Box” solution is a pre-packaged Business Intelligence solution - based on Navision / Business Central as a source – providing dozens of viewpoints on business data. The solution works for all versions of Navision and Business Central, allowing to integrate data very rapidly from Navision and Business Central in a data warehouse (hosted in MS SQL Server). At Katoen Natie, quite some custom development had been done in Navision and Business Central and this custom logic was also implemented in the data warehouse ETL logic.
The prepackaged data warehouse based solution was set up using standard Microsoft technology. Microsoft’s own ETL (extract, transform and load) tool SQL Server Integration Services (SSIS) was used to load data from source to staging and from staging to data warehouse.
SQL Server Master Data Services (MDS) is used to manage security and the entities that need to be loaded. Regarding security MDS manages the master data for the security rules and users. Security filters are driven by following dimensions; company, business unit, reporting line, profit center and location. MDS contains the configuration rules but does not enforce these rules, this is done the Tabular analysis model. As Katoen Natie has many entities we did not want to load all the entities into the data warehouse during development. Instead a table was set up in MDS that allows to select which Navision entities should be loaded and add entities if new ones are created. Next to a web interface MDS also has a Microsoft Excel interface, which allowed the business end-users to quickly adapt to this new interface.
On top of the data warehouse a tabular model was built using Microsoft SQL Server Analysis Services. This tabular model holds all the calculations and business logic and ensures fast & performant analysis and reporting. In this model the security rules defined in MDS are built in and those rules (and related filters) will be applied to the end-users consulting the reports.
As a reporting tool SQL Server Reporting Services (SSRS) was chosen. SSRS is a server-based report generating solution, ideal for fixed-layout reports optimized for printing, such as PDF. The reports were built on top of the Tabular model. Some very custom reports were built in SSRS via SQL Server stored procedures as this logic would overload the Tabular model (because of complexity) for self-service reporting.
Business users (mainly financial controllers) can also do their own analysis via Excel. Excel allows to connect to the Tabular model, giving users the opportunity to create their own reports and analysis in their “good old” Excel interface. Because the row-level security is defined in the Tabular model, it 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
An important requirement from the business users was that new data should be loaded during the day. A lot of data is involved since the production environment will contain data from all entities worldwide. Therefore, incremental loading was implemented, allowing business users to see new data within one hour.
Technology
The solution was built using the Microsoft Business Intelligence stack.
Microsoft SQL Server Enterprise is used as back-end:
- Microsoft SQL Server for database engine
- Microsoft SQL Server Integration Services for ETL, supported by BIML for automation
- Microsoft SQL Server Analysis Services Tabular as semantic layer technology
- Microsoft SQL Server Master Data Services for maintaining general ledger account mappings and groupings, reporting hierarchies and security configuration
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 responsible for the following activities:
- Installation and configuration of all standard components of the Dynamics Business Central / NAV BI “Out-of-the-Box”, delivered in 3 working days by module
- Building the Tabular model for analysis
- Applying row-level security in Tabular model
- Incremental loading of data warehouse
- Creating set of reports in SSRS
element61 is a Gold Microsoft Partner in Data Analytics, Data Platform and Cloud Platform.
Conclusion
Business users can now analyze financial data, both historic Navision as new Business Central data, with SSRS reports and can create custom reports using Excel. The built-in security prevents users from unauthorized access to data. The delivered BI platform is quicker, more extensive and more user-friendly, allowing Katoen Natie to easily analyse financial data from all entities worldwide.
