Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Table source in new Aggregations preview

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

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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