Business Challenge:

Any organization building a Power BI report sourcing from millions of transactional records. The model started lagging as:

• Dataset exceeded size limits (especially with Power BI Pro)
• The report took 20+ seconds to load visuals
• User filters and slicers became unresponsive
• Publishing to the Power BI Service resulted in timeouts

They could not simply remove historical or granular data as business leaders relied on this data for trend analysis, forecasting, and segmentation.

Business Use Case:

I recently worked on a Retail Sales Report where the dataset was 1.5 GB in memory, with over 100M rows in the fact table and 20 dimension tables. The initial report load time was around 40 seconds, which was not ideal for a fast-paced business environment.

The business needed a flexible yet optimized reporting experience with:

• Daily transaction-level data for the last 3 months
• Monthly summarized data for the last 5 years

Proposed Solution:

Here is how I cut the dataset size without impacting reporting capabilities:

1. Aggregation Tables (Summary Tables)

• Created a summarized table for sales by month.
• Used DAX and Power Query to summarize only older data.
• Retained granular detail only for the last 3 months.

How to create Aggregation Tables in PBI:

Step 1: Identify Granularity of Aggregation:

Decide the level of summarization, such as:

• By Product and Month
• By Region and Category

Step 2: Create the Aggregation Table (in Power Query or DAX):

1. Go to Transform Data.
2. Reference your fact table.
3. Use Group By to summarize:
   Group by: Product, Month
   Aggregations: Sum(Sales), Sum(Quantity), etc.
4. Rename the query to something like Sales_Agg.

Step 3: Set Up Relationships:

• Connect your aggregation table to the same dimension tables (Date, Product, etc.) used in your original fact table.
• Do not connect it to the main fact table.

Step 4: Use in Visuals:

• Use the same dimensions (Product, Date, etc.) in visuals.