Anna Lynch, a solution architect at Saxony Partners, helps you decide between the leading data analytics platforms. Should you pick Qlik Sense or Power BI?
In my consulting role with Saxony Partners, I have encountered Qlik Sense clients who are considering re-platforming their Qlik Sense applications to Power BI.
While not an exhaustive list, read on for some considerations and comparisons to better understand how well you are positioned to execute this re-platform.
How large are your applications and how frequently do you need to refresh your reports?
Depending upon your Power BI license, there are storage and refresh constraints you need to consider.
There’s Power BI Pro, which uses a shared compute environment and has a maximum storage limitation of 10 GB per user. The maximum size of per individual dataset is 1 GB, and the maximum number of automatic refreshes per day is eight.
And there’s also Power BI Premium, which has a dedicated compute environment, a maximum storage limitation of 100 TB, and a maximum size of 10 GB per individual dataset. The limit on automatic refreshes per day is 48. Power BI Premium is the only option with an incremental refresh.
Data Access Modes
Power BI has a few data access modes: Import Mode, Direct Query Mode, Live Connection Mode, and Dual Mode (in Preview).
In general, you should use Import Mode whenever possible for datasets that are less than 1 GB and refreshed eight times per day (or less). Imported data is compressed, optimized, and then stored on disk by the Vertipaq storage engine.
For rough estimation purposes, when source data is loaded, it is reasonable to expect a 10 GB dataset to take approximately 1 GB of storage once it is imported into Power BI. Using Import Mode takes advantage of the high-performance query engine. Your tables will be cached and you’ll have full functionality of DAX (Data Analysis Expressions) and Power Query.
DirectQuery mode is exactly what it sounds like: you are connecting directly to the original repository. You should use DirectQuery Mode when datasets are too large, when real-time (15 minute) updates are needed, or when data sovereignty restrictions apply. You will have limited DAX and Power Query functionality.
There are three things to note here, however. One, DirectQuery will fail if more than 1 million rows are returned. Two, response times must be under 225 seconds. Three, it is not possible to use a query that utilizes Common Table Expressions (CTEs) – nor one that invokes a stored procedure.
Live Connection Mode is for SQL Server Analysis Services (SSAS) data sources. When using Live Connection Mode, you are shown the entire external model in the Power BI field list. The identity of the user opening the report is passed to the underlying SSAS source.
Dual Mode, which is in preview, allows a report to have two or more data connections, including Import and Direct Query, in any combination. Dual mode has the same constraints as DirectQuery Mode. Because this feature is still in preview, I will not cover it in additional detail.
What data sources do you need to connect to?
Where are your business rules?
If you utilize a traditional data warehouse to feed data to your Qlik Sense application, you are better positioned to re-platform from Qlik Sense to Power BI. This is because most of your business rules should have been applied to source data via ETL when data was loaded into a data warehouse. Doing so enabled you to access your data with other analysis tools, such as Excel. It also made the creation of your QVD data layer much easier.
If, instead of using a data warehouse, you loaded files directly into the Qlik Sense data layer using Qlik script, you will need to reverse engineer the business logic that is contained in the Qlik script so you can decide how to implement it in Power BI. Some logic may be able to be implemented when creating the Power BI model; other logic may require DAX code.
How are your Qlik Sense data models designed?
If you embraced Qlik’s guidance to use star schema data models, you’ll be happy to know star schema design is highly relevant to developing Power BI models that are optimized for performance and usability.
In Qlik Sense, you can develop an application using Qlik Sense Desktop or from the Qlik Sense Hub. If an application is developed using Qlik Sense Desktop, it can be uploaded to the Hub to be shared with others. However, many organizations prefer all development to be done on the Hub for security and control purposes.
In Power BI, the typical development workflow goes like this: Power BI Desktop is used for authoring reports. Using Power BI desktop, a report is developed and then published from Power BI Desktop to a workspace on the Power BI service. The service is meant for sharing published content (similar to the Qlik Sense Hub). From the Power BI service, you can then create dashboards that combine visuals from multiple reports. Combining visuals from multiple reports into a dashboard is functionality Qlik Sense does not offer. You have the option to publish an application. The application can be comprised of dashboards and reports (and the underlying datasets).
You can also create reports from the Power BI Service, from a dataset that has been published to the service. It’s important to know that when a web-created report is downloaded, and then updated using desktop, and then uploaded to the service, it results in a duplicate report and a duplicate dataset. For this reason, I would author reports using Power BI Desktop.
Creating Data Models
Like Qlik Sense, when creating your data model using Power BI you can pull multiple tables from multiple sources.
In Power BI Model View, you are presented with a diagrammatic view of your data where you can visually establish relationships between tables. Power BI has an Autodetect feature to find relationships in data, and you can manually edit relationships. You can set the cardinality and cross-filter direction of your relationships. You can also hide tables and columns.
In Power BI Data View, you can create a column that does not exist in a table using a calculated column. You might do this when your source data does not contain a field you need for your analysis. You can also create calculated tables. They provide the ability to create intermediate calculations and data that you want persisted as part of your model rather than part of a query. Example usage includes “unioning” or cross-joining tables.
From either Data View or Report View, you can create custom calculations using calculated measures.
A Qlik Sense capability Power BI does not provide is “the power of gray.” This differentiator is made possible by the Qlik Associate engine. It enables the user to see where data is not associated: customers who are not buying certain products, salespersons who are not selling certain products, mortgage loans not associated with a loan processor. Power BI does not have similar capability.
Developing Reports: Measures
With Qlik Sense, you write set expressions to create measures beyond simple sums and counts. In Power BI, you use DAX.
Many developers use master items in Qlik Sense to promote reuse and ease maintenance. A master item can be a dimension, measure, or visualization.
In Power BI, to mimic similar functionality, you can create calculated measures and assign them to a home table (say, “Measures” for example) to organize them and make them easy to find. Power BI does not have a concept like master items for visualizations or dimensions.
Developing Reports: Visuals
Both products have the typical assortment of visuals to use, but their implementations vary.
These visuals are available in both products:
- KPI: Qlik Sense KPI allows a title, subtitle, footer, and the ability to not specify a second measure (which is extremely helpful). With Power BI, when you don’t have a second measure, you have to use the Power BI “card” visual which is not nearly as rich as the Qlik Sense KPI; the card will have to be combined with text, etc.
- Bar Chart
- Combo Chart
- Line Chart
- Pie Chart/Donut Chart
- Scatter Plot
- Pivot Table: Qlik Sense’s pivot table has richer functionality than Power BI equivalent. A Qlik Sense pivot table can be interacted with; you can drag and drop other dimensions and rearrange the pivot table on the fly.
- Waterfall Chart
These visuals are present in Power BI but not in Qlik Sense:
- Ribbon Chart
- Card (Single and Multi-Row)
- R script visual
- Python visual
- Key Influencers
- ArcGIS Maps
These visuals are missing from Power BI:
- Box Plot (can import free extension certified by Power BI: Box and Whisker Chart by MAC Software)
- Distribution Plot (can import free extension, not certified by Power BI: Box and Whisker Chart by MAC Software)
- Histogram (can import free extension, not certified by Power BI: Histogram with Points by MAC Software)
Both products provide the ability to add functionality that is not available “out of the box.” Qlik Sense provides the ability via Dashboard Bundles and Visualization Bundles. You can also download extensions from Qlik Branch and add them to your application.
Power BI provides the ability via the Power BI Marketplace (available from within Power BI). You can also download add-ins from AppSource and import them into your Power BI file.
Both products allow you to create your own extensions/add-ins.
Both products have themes you can specify off the shelf. Qlik Sense has three default themes to choose from. Power BI has approximately a dozen. Power BI also has a Theme Gallery provided by the Power BI community where you can download a theme and import it into your Power BI file.
Both products provide the ability to create and use a custom theme. In Qlik Sense, a custom theme is a collection of files: QEXT file, JSON file, and optionally CCS files, custom font files and images. In Power BI you simply have a JSON file.
Power BI Pro
- Workspaces are created and administered from the Power BI service.
- Licenses are assigned to individual user accounts from the Microsoft 365 Admin Center.
- Dataset refreshes are schedule from the Power BI service.
- Datasets and reports are shared/collaborated from the Power BI service.
Power BI Premium
- Power BI Premium is licensed based on compute, not individual user. Other tasks are accomplished in the same manner as Power BI Pro.
Is making the switch from Qlik Sense to Power BI is right for your organization? The team at Saxony Partners can help you answer that question. We have a group of data scientists and data strategy experts who can assess your needs and help you make a determination that aligns with your business goals.