Table source in new Aggregations preview

[Anonymous]

Hi,

I'm trying out the new Aggregations feature in the September release and I'm wondering how to bring in the table which will be used as the aggregations table.  For example, I have a single table with 10 millions rows which is connected via DirectQuery.  If I want to create an Aggregations table (using the GroupBy options) then I'm currently bring the Fact table in again using DirectQuery but using a query to only select 3 of the dimension columns and 1 measure column.  I then set up the aggregations on the table using the GroupBy on the 3 dimension columns and the Sum on the measure column, each column is referencing the original Fact table.  I then switch the aggregations table to be "Import", it then loads in 10 million rows... I had thought that it would only load in the number of rows required to satisy the SUM grouped by the 3 dimension columns.  

So I'm wondering if I need to use SELECT Dim1,Dim2,Dim3,SUM(Measure) FROM FactTable GROUP BY Dim1,Dim2,Dim3 as my query?

Thanks

[v-cherch-msft · Accepted solution]

Hi @Anonymous

Here are the documents for your reference. 

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations

https://prologika.com/a-first-look-at-power-bi-aggregations/

Regards,

Cherie

[Anonymous]

Thanks.  The official documentation has a note stating:

"Note

The Sales Agg table is just another table, so it has the flexibility of being loaded in a variety of ways. For example, aggregation can be performed in the source database using ETL/ELT processes"

and the Prologika post states:

"As a first step for setting up aggregations, you need to add a summarized table. Unlike MD, you are responsible for designing, populating and maintaining this table"

I have therefore created a view in the SQL Database which SUMs and GROUP BY the necessary dimension key.

All is working as expected now.

Thanks

[v-cherch-msft]

Hi @Anonymous

Glad to hear the documents are helpful, please accept the helpful reply as solution, that way, other community members will easily find the solution when they get same issue.

Regards,

Cheire