How do resume / reduce a imported table?

Hi Frbelotto 

Given your scenario with a large DB2 SQL dataset and the specific challenge of calculating unique customers efficiently, here are steps and considerations to create and use aggregation tables:

1. Creating Aggregation Tables

You can manually create aggregation tables at different levels of granularity (daily, monthly, yearly) based on your sales data. These tables should summarize the total unique customers for each period.

Here's a simplified example of what the process might look like in SQL for monthly aggregations:

SELECT
YEAR（salesDate） AS Year，MONTH
（salesDate） AS Month，COUNT
（DISTINCT customerID） AS UniqueCustomers
FROM
salesTable
GROUP BY
YEAR（salesDate）,
MONTH（salesDate）;

You would create similar queries for daily and yearly aggregations.

2. Importing Aggregation Tables

After creating these aggregation tables in your database, you can import them into Power BI. Since you're using a hybrid approach (import + direct query), these tables would typically be imported to reduce query times.

3. Setting Up Aggregations in Power BI

Once you have your aggregation tables in Power BI,go to "Manage Relationships" and ensure your aggregation tables are correctly related to your main sales table and date table, matching on the appropriate date fields (day, month, year).Then, use the "Manage Aggregations" feature in Power BI to define aggregations. For example, for your unique customers' aggregation table, you would map the count of unique customers in the aggregation table to the equivalent measure in the main table.

4. Using Aggregations

With aggregations set up, Power BI will automatically use the appropriate aggregation table based on the level of detail in your report visuals. For example, if a user is viewing data by month, Power BI can pull the count of unique customers from the monthly aggregation table, thereby avoiding processing the entire dataset.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.