17 January 2020
The client wished to migrate an existing Qlik Sense dashboard into Power BI as part of a larger firmwide move into Azure Cloud Services. The Qlik Sense dashboard had functionality native to the Qlik Sense tool that needed to be replicated in Power BI. Additionally, the data modeling had to be recreated using Power BI’s Query Editor as the two tools handled the data differently.
The client was migrating their infrastructure to Azure and this project allowed the dashboard to fall inline with that larger strategy. It also enables them to leverage the capabilities of the Microsoft Power Platform to improve data quality and process efficiency.
The principal challenges were to replicate visualisations and analytics built using native Qlik Sense functionality, and to recreate the Qlik data model in Power Query M. These included:
- Loading data from multiple data sources in one data model so business users could access and do analysis on data in one place
- Maintaining the Self-Service functionality that Pomerol had built into the Qlik Sense report
- Restricting user’s access to only data from their business unit
- Distributing the report to over 100 business users across different business units
- Alternate dimensions and measures (user ability to configure dimensions and measures in a chart)
- Comparing results across different time periods
- The ability to view the data in different currencies
- Ensuring the visualisations continue to get real value from the data
Pomerol created a new report in Power BI tracking marketing spend and ROI against internal targets.
A new data model was designed to align with Power BI’s functionality and best practices. Using the new data model Pomerol were able to replicate the dimension switching functionality of Qlik Sense.
A series of nested measures were created that allowed the business users to view the data by their chosen currency as well as other business defined metrics based on slicer selection in data islands.
Another series of nested measures allowed the user to select which measure was reflected in charts based on filter selections in data islands.
Rules were built into the report to restrict data access by username so that the business could distribute the report to over 100 users with the correct security access applied.