How do resume / reduce a imported table?

[Frbelotto]

Hello guys,
A am working on a PWBI dashboard that offers 4 different visuals :

- Volume of sales

- Number of sales

- Total revenue

- Total unique customers

Take into consideration that each of this visuals offer, due to a date hierarchy, an option to be visualized in days, months or years.

My DB2 SQL data contains more thatn 130 mm rows from the last years os sales and it is on hybrid mode (imported + incremental refresh + direct query on recent data)

All the 3 first visuais runs normally. The issue is regarding the last one. As it requires count distinct, it becomes very hard to bem calculated e most of time, it runs on a timeout.

I was trying to create some aggregation tables, but I´ve realized that PWBI does not let me set an table as as aggregation table of a imported on.

So, what could I do (beyong just reduce the time windows of my data)?

1) Can I create a aggegation table?

2) If so, how could a create a differnt aggregated table based on days, months and years and make them the source of the query (considering that I take only unique customers, every aggregation becomes different)?

Thanks in advance!

[Anonymous · Accepted solution]

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.

[Frbelotto]

I am trying to setup the aggregations, but I does not let me select my original table as the "detailed" table. I think that is related to the fact that such table is on imported mode