PowerBI data modeling question - big data volume

[PeterRA]

Hello Power BI Community,

I am currently working on a Power BI project involving a complex dataset with large volumes of records. As the project grows, I am looking for advice on the best practices and approaches for data modeling when dealing with large numbers of records, especially with respect to performance and scalability.

Overview of the Data Model:

Here’s a brief overview of the tables involved, their respective record counts, and how they manage historical data:

1. D_ENTITIES (Entity Dimension):

   • Contains entity-related details (e.g., entity name, type, country).
   • Approximate record count: 50 million records.
   • History: This table keeps historical entity data, with snapshots taken at the end of each month. 6 years of snapshots are retained in the table.

2. D_PARTNERS (Partner Dimension):

   • Contains partner-specific information (e.g., partner ID, region, role).
   • Approximate record count: 5 million records.
   • History: Partner data is updated monthly, with 6 years of historical snapshots stored.

3. D_PRODUCTS (Product Dimension):

   • Contains product-level details (e.g., product name, category, price).
   • Approximate record count: 200 million records.
   • History: Product data is updated monthly, maintaining 6 years of historical data.

4. F_TRANSACTIONS (Fact Table):

   • Stores detailed information like transaction amounts, product purchases, and partner commissions.
   • Approximate record count: 500 million records.
   • History: This fact table keeps monthly snapshots of transaction data, and 6 years of historical data is retained.

5. D_TIME (Time Dimension):

   • Contains time-based information (e.g., date, shift, production period).
   • Approximate record count: 20 years worth of data (around 240 rows).
   • History: This dimension reflects the complete timeline, with each month’s data included.

6. Other Dimensions: We also have additional dimensions such as D_LOCATIONS (for locations), D_ACTORS (for actors involved), and others, but they are less frequently used and have smaller record counts in comparison (e.g., 5 million records each).

Pre-Aggregated Table and Model Design:

We have created a pre-aggregated table for key metrics like total transaction volume, total inforce premium, and total product sales. This table aggregates data at the entity and product level, significantly improving performance in Power BI.

• Pre-Aggregated Table: The pre-aggregated table includes aggregated values like total inforce premium, total lives, and product sales across various entities.
• We chose to import this table into Power BI because it contains pre-calculated key metrics at a summary level, which speeds up report loading times.

Connection to D_DATE:

• The pre-aggregated table is connected to the D_TIME dimension (via TIME_KEY) to ensure time-based analysis.
• This allows us to track key metrics over time (monthly, quarterly, yearly) using D_TIME.

Linking to Star Schema:

• While the pre-aggregated table provides fast access to summary metrics, we also maintain a star schema for more detailed analysis and drill-downs.
• The star schema includes D_ENTITIES, D_PARTNERS, D_PRODUCTS, and F_TRANSACTIONS.
• These tables are linked through their respective keys (e.g., Entity_SKEY, Partner_SKEY, Product_SKEY) to the fact table, which stores detailed transaction data.

How the Model Works:

• Pre-Aggregated Table: This table simplifies reporting and provides fast performance for high-level metrics. It is connected to D_TIME for time-based filtering.
• Star Schema: Provides detailed records and allows users to drill through for more granular insights. The detailed fact table (F_TRANSACTIONS) and dimensions (D_ENTITIES, D_PARTNERS, D_PRODUCTS, etc.) are used for detailed analysis.

Key Relationships Between Tables:

1. D_ENTITIES (Entity Dimension) is connected to F_TRANSACTIONS via Entity_SKEY.
2. D_PARTNERS (Partner Dimension) is connected to F_TRANSACTIONS via Partner_SKEY.
3. D_PRODUCTS (Product Dimension) is connected to F_TRANSACTIONS via Product_SKEY.
4. F_TRANSACTIONS (Fact Table) connects to D_TIME via Time_SKEY for time-based analysis.
5. Pre-Aggregated Table: This table connects to D_TIME for time-based aggregation and is used for summary-level reporting.

Questions for the Power BI Community:

Given the large size of the datasets and the number of records involved, I would appreciate some guidance on the following:

1. How can we optimize performance in Power BI when dealing with such large tables, especially when joining large fact tables (e.g., F_TRANSACTIONS) with dimension tables?

   • We are using DirectQuery for some tables, but performance is slow, especially with many rows in the fact table.

2. What is the best approach for handling large fact tables like F_TRANSACTIONS (with over 500 million records)?

   • Should we continue with our flat structure or consider partitioning the fact table or creating additional aggregated tables for better performance?
   • Should we create an aggregated fact table with pre-summarized data (e.g., by month, quarter, entity) to improve refresh time and report performance?

3. What model design principles should we follow to ensure scalability in the long term as our dataset grows?

   • Should we maintain the star schema with detailed fact tables, or would a snowflake schema or hybrid model work better in this case?

4. What role does indexing play in improving performance for DAX calculations and the relationships between large tables (especially with large fact tables and slow queries)?

   • Are there specific indexing strategies you recommend for the fact table and dimension tables?

5. Other Best Practices:

   • Are there other best practices for handling large datasets in Power BI that we might be missing? For instance, optimizing refresh times, managing large aggregations, and efficiently handling slicers and filters?

Current Strategy:

• We are continuously monitoring query performance and optimizing our SQL views and DAX calculations.
• We are exploring DirectQuery and import mode to balance data volume and performance.
• We are also leveraging Power BI's incremental refresh feature to reduce full dataset refresh times.

Any advice or guidance on how to proceed with optimizing our data model and improving the performance of large datasets in Power BI would be greatly appreciated!

Looking forward to your insights!

Best regards,
RavP

[rajendraongole1 · Accepted solution]

Hi @PeterRA  - You're already implementing a solid approach with a mix of star schema, pre-aggregated tables, and incremental refresh.

Please find the attached file. 

Hope this helps.