Best Practices for Building PowerBI Dashboards against Incorta

[Tristan]

Organizations who choose to accelerate their PowerBI dashboards with Incorta can perform direct query 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 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:

1. 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?

2. 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.

3. 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.

4. Use Incorta's date dimension.  Since PowerBI offers little support for time intelligence functions, a "calendar" table should be setup in your Incorta schema to provide the date attribution needed to deliver powerful temporal analysis features.

5. 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.

6. 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.

7. Configure Incorta to use a higher value for “Max Group” 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 affect on Incorta system performance.

8. Since PowerBI uses a SQLi connection to fetch data from Incorta, make sure that the SQLApp Application is given enough memory and cores from the CMC.

9. 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 number of records downloaded.    

Please reach out to your Incorta customer success team for more guidance on delivering these design practices in an optimal way.