Enhancing Power BI Data Model Efficiency with the Ideal Framework Selection
Power BI is gaining popularity in enterprise reporting because of its strong data visualization and business intelligence capabilities. It is useful for various enterprise reporting needs, including centralized content management, enterprise BI, resource management, financial reporting dashboards, sales scorecards, billing, claims, collections reports, paginated reports, and on-premises reporting.
In an enterprise, data can be found in a variety of sources, each serving distinct purposes and offering unique value to the department or the entire enterprise. These sources typically include, but are not limited to, relational databases such as SQL Server, Oracle, MySQL and PostgreSQL; cloud services like Azure SQL Database, Azure Blob Storage and AWS; online services such as Salesforce, Microsoft Dynamics, Google Analytics; streaming data from real-time data streams; SaaS platforms like Jira, CRM, ERP; and flat files such as CSV, Excel, XML, JSON, etc.
Power BI supports integration into the majority of the different data sources. By default, A power BI model is a tabular model containing relationships, hierarchies, and calculations. Every power BI model table has a storage mode property, and the storage mode property can be either import, DirectQuery or Dual mode. The table storage mode setting defines the model framework.
Selecting the right model framework to optimize Power BI efficiency is a nuanced decision. Each framework offers distinct advantages and limitations. Let’s delve into the various frameworks to understand their strengths and weaknesses.
1. Import Model
An import model consists of tables configured with their storage mode set to ‘Import’, It includes calculated tables, which can be defined with a DAX formula too.
Benefits:
· Universal Data Source Compatibility: Accommodates every type of Power BI data source, such as databases, files, feeds, web pages, and dataflows.
· Flexible Data Integration: Enables the amalgamation of diverse data sources; for instance, linking a table from a relational database with another that pulls data from a web page.
· Full DAX and Power Query Support: Provides comprehensive support for all functionalities within DAX and Power Query (M).
· Calculated Table Functionality: Facilitates the creation and use of calculated tables.
· Optimal Query Performance: Ensures superior query performance due to the data being cached and optimized for analytical operations like filtering, grouping, and summarizing, with the entire model residing in memory.
Limitation:
Model Size:
· Dataset Size Limits: Power BI enforces a 1-GB limit per dataset in shared capacity, with refresh failures if exceeded.
· Premium Capacity Expansion: In dedicated (Premium) capacity, datasets can exceed 10 GB if the large dataset storage format is enabled.
Data Refresh:
· Refresh Frequency: Power BI limits scheduled refreshes to 8 times daily in shared capacity and 48 times in dedicated capacity.
2. DirectQuery Model
A DirectQuery model comprises tables set to the DirectQuery storage mode, all of which are part of the same data source group. A data source group is a collection of model tables associated with a single data source. There are two categories:
Import: includes all tables in the import storage mode and calculated tables. A model is limited to just one import source group.
DirectQuery: encompasses all tables in the DirectQuery storage mode that are linked to a particular data source.
Benefits:
· Ideal for Big Data: DirectQuery is optimal for large datasets, such as data warehouses, where importing the entire dataset is not feasible.
· Real-time Insights: Offers near real-time query results, making it suitable for data that changes frequently.
· Query Handling: Power BI forwards report queries directly to the underlying data source, like Azure SQL Database.
· Row-Level Security (RLS): DirectQuery allows the source database to enforce its own RLS rules, eliminating the need to replicate them in Power BI.
· Data Sovereignty Compliance: For organizations with data residency requirements, DirectQuery enables compliance by connecting to on-premises data sources.
· Supports Specialized Datasets: While typically used with relational databases, DirectQuery can also chain up to three Power BI datasets or Azure Analysis Services models for extended functionality.
· Model Personalization: Allows for the customization of remote models by renaming objects or adding new measures, columns, and tables, leading to the creation of composite models.
Limitation:
· Supported Data Sources: Not every type of data can be used. Mostly, it works with big, well-known databases, and with Power BI and Azure Analysis Services.
· Transformations: Some data changes, like pivoting or unpivoting, can’t be done because the system must be able to understand the changes in its own language.
· Performance: If the data isn’t set up right or there’s not enough power for the analysis, it can be slow.
· Impact on Source System: Running these analyses can make the whole system slower, which might affect other jobs that are running at the same time.
3. Composite model:
The Composite model consists of multiple source groups, typically including both an import group and a DirectQuery source group.
Benefits:
· Composite models increase design flexibility.
· You can mix different storage modes to balance between imported and pass-through data.
· Enterprise models often use DirectQuery tables for large datasets and imported tables to improve query speed.
· Composite models enhance DirectQuery model performance by allowing Power BI to use imported data for some queries.
· If your model has DirectQuery tables linked to a remote model, you can add new calculated columns and tables.
Limitation:
· Within the composite model the tables which are set to import, require periodic refresh else they get out of sync with the tables that are set to DirectQuery.
· When there is a need to combine imported and DirectQuery data in Power BI, it can affect performance. To mitigate this, consider adding import aggregation tables for higher-grain queries and enabling automatic aggregations. Also, set related dimension tables to use dual storage mode.
· When chaining models, changes made to the upstream model can disrupt the downstream models. It’s crucial to evaluate the impact of modifications by conducting a dataset impact analysis beforehand.
· Limited Relationships occur when tables come from different source groups. Since Power BI detects relationships automatically, the relationship is considered limited when Power BI cannot determine a clear “one” side of the relationship. In this case, queries and calculations within the model may vary.
Conclusion:
After examining different model frameworks and considering their advantages and limitations, choosing the right model is essential. This is particularly important for enterprise solutions that manage large amounts of data, need high query throughput, and require quick responsiveness. The criteria for selecting a model differ based on the specific requirements of the project.
The import model is often recommended for its design flexibility, data transformation capabilities, and swift performance. This model is effective when data is aggregated and various data reduction techniques are utilized, ensuring that Power BI loads the minimal amount of data necessary.
The DirectQuery model is advisable for near real-time reporting or when dealing with data sources containing vast amounts of data. This allows the bulk of the data to remain within the source, using Power BI solely for reporting purposes.
The Composite model is recommended when you need high query performance similar to the DirectQuery model, as well as near real-time querying from an import model. Additionally, the Power BI dataset can be enhanced by using the Azure Analysis Services model.
About the Author
Kunal Khule is a BI Developer at Version 1.
