Table of contents
In an import model, tables are configured with their storage mode property set to Import. This model encompasses calculated tables as well, allowing users to define these tables using DAX formulas.
Import model benefits
Import models are the predominant framework due to their numerous advantages:
Versatile Data Source Support: They accommodate all Power BI data source types, ranging from databases and files to feeds, web pages, and dataflows.
Data Integration Capability: Import models facilitate the integration of diverse source data. For instance, a table can pull data from a relational database, while a related table sources its data from a web page.
Comprehensive Functionality: They support all functionalities of DAX (Data Analysis Expressions) and Power Query (M).
Calculated Tables Support: Import models provide support for calculated tables, allowing users to incorporate tables derived from specific calculations.
Optimal Query Performance: These models deliver superior query performance by caching data in the model, optimizing it for analytic queries such as filtering, grouping, and summarizing. The entire model is stored in memory, contributing to swift performance.
In summary, import models offer extensive options, design flexibility, and rapid performance. As a result, Power BI Desktop defaults to using the import storage mode during the "Get data" process.
Import model limitations
Despite the many compelling benefits, there are limitations of import models that you must bear in mind. Limitations are related to model size and data refresh.
Model size
Power BI enforces limitations on dataset sizes, impacting the overall model. When deploying the model to shared capacity, there exists a 1-GB cap per dataset. Exceeding this limit results in a failed dataset refresh. Alternatively, utilizing dedicated capacity (Premium capacities) allows for a model size exceeding 10 GB, contingent on activating the Large dataset storage format setting.
Efficient data management is crucial. To enhance model performance, reduce data volume by employing various techniques:
Eliminate Unnecessary Columns
Remove Redundant Rows
Utilize Grouping and Summarization for Fact Tables
Optimize Column Data Types, Favoring Numeric Formats
Prioritize Custom Columns in Power Query Over Calculated Columns in the Model
Deactivate Power Query Query Load
Disable Auto Date/Time
Leverage DirectQuery Table Storage, Explored in Later Module Units
Implementing these strategies not only shortens model refresh durations but also accelerates query processing.
Data refresh
Regular data refresh is essential for keeping imported data current in Power BI datasets. The dataset's data reflects the status as of the latest successful refresh. Users can either establish a scheduled data refresh or manually trigger an on-demand refresh.
However, Power BI enforces limitations on the frequency of scheduled refresh operations. In a shared capacity, refreshes are limited to a maximum of eight times per day, while in a dedicated capacity, the limit extends to 48 times per day.
It's crucial to assess whether this level of data latency is acceptable, considering factors such as data velocity and the urgency of providing users with up-to-date information. If the scheduled refresh limits prove inadequate, alternative strategies include utilizing DirectQuery storage tables, creating hybrid tables, or opting for a real-time dataset.
Power BI default refresh operations involve clearing and reloading entire tables, potentially straining source systems, especially with large fact tables. To alleviate this, leverage the incremental refresh feature. This functionality automates time-period partition creation, managing, and intelligently updating only necessary partitions.
When your data source supports incremental refresh, it leads to quicker, more dependable refreshes, along with reduced resource consumption on both Power BI and source systems.
For advanced data modelers, the option to tailor a partitioning strategy is available. Automation scripts offer the capability to create, manage, and refresh table partitions.
SWETA SARANGI
26.02.2024