Organizations who choose to accelerate their PowerBI dashboards with Incorta can perform direct queries against 100s of millions of records with sub-second performance. With this integration, data can easily be brought together from multiple data sources without the need to do complex data modeling first. Additionally, Incorta can simplify the development of PowerBI dashboards by offering a semantic layer in Incorta (called business views). These business views can contain human readable fields and complex business formulas that abstract away the complexities of the underlying data.
Many of Incorta's customers choose to power their PowerBI dashboards with Incorta's extremely performant data platform. To maximize performance and avoid as many configuration headaches as possible, there are a few best practices that should always be followed that are outlined below:
-
Design business schemas with one view for a PowerBI Report. Each PowerBI visualization should be built against a single Incorta business view. With this approach joins do not have to be defined (again) in PowerBI. And why would you want to define them in PowerBI since they are already defined in Incorta? When designing your business views, do not use analyzer views as they have performance limitations.
-
Every Incorta business view used by PowerBI should have a base table defined. This enforces a well-understood "record grain" to the Incorta view such that it cannot change as different fields within the business view are used on the PowerBI dashboard.
-
Every Incorta business view used by PowerBI should generate valid SQL. From the Analyzer, create a simple listing table based on each business view and then click the SQL icon in the upper right corner to view the SQL that is generated. If the SQL does not generate, the business view will not work in PowerBI. Starting with Incorta release 2024.1, Incorta validates business views for compatibility with external BI tools, like PowerBI, and SQL compliance. You can easily identify a verified business view by the green circle with a tick mark displayed next to it in the Business Schema Designer.
-
Use Incorta's date dimension. Since PowerBI offers little support for time intelligence functions, a "calendar" table should be set up in your Incorta schema to provide the date attribution needed to deliver powerful temporal analysis features.
-
Be careful using PowerBI "DAX" functions with direct query. This is not an Incorta deficiency, but some DAX do not work with direct query against any database. More can be referenced about this here: https://docs.microsoft.com/en-us/dax/dax-function-reference.
-
Define your formulas in Incorta, not in PowerBI. Take advantage of Incorta's lightning fast in-memory Direct Data Mapping (DDM) analytics and push all the formulas to Incorta. They will be much slower if rendered on the PowerBI side.
-
Configure Incorta to use a higher value for “Max Groups” in the Incorta CMC. The default is 1M, but this should be increased to 5M or 10M. Be careful though. If set too high, this can have a negative effect on Incorta system performance.
-
Prior to Incorta release 2024.7, the only option was for PowerBI to use a SQLi connection to fetch data from Incorta. When using SQLi, make sure that the SQLApp Application is given enough memory and cores from the CMC. Starting with Incorta release 2024.7 and going forward, we recommend that you use the Advanced SQL Interface instead. If you wish to use Direct Query, do not use SQLi. Instead, use the Advanced SQL Interface.
-
Avoid creating visualizations in PowerBI which download thousands of records. If you add a table visualization in PowerBI which is fetching thousands of records, unlike Incorta, PowerBI will download all records and will take time to download and then render data in the sheet. Please add a default filter for the PowerBI table visualization to limit the number of records downloaded.
-
Make sure to use the Incorta SQL port instead of the Spark fallback port as performance is better when using the Incorta engine and you can trust that the results will be correct.
You may find this video to be helpful reference.
Additional References:
.png "Employee"){kind=icon}
