Forum Discussion

flaviosouzaab's avatar
flaviosouzaab
Regular Visitor
4 years ago
Solved

Data reduction in dimension tables

Hi,

I have an Excel spreadsheet that connects to a SQL Server database to obtain data from a DW, my difficulty is in keeping in the dimension tables only the records that are present in the fact table, users see different data when accessing the spreadsheet, so the Dimensions should reflect the data according to what is in fact.

 

My attempt was as follows (I'll give an example with the customer dimension):

1) I create a table in buffer with the ids of the clients that are in fact.

2) In the customer dimension I do an inner join in this buffer table created above so that the dimension only has the keys existing in the fact.

 

But when I do that, the data loading takes a long time, the impression I have is that the fact table is loaded in each dimension over and over again.

How can I do this type of transformation in power query?

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    If these are from the same database, a mere inner join from dimension to fact would normally be your best option (without the buffer, which causes the problem you describe above.

     

    You could also try a buffered list instead of table, by making a query that gets the distinct values from the customer column of the fact table, like List.Distinct(Fact[Customer])

     

    Name that query FilterValues.

     

    Now when you make your fact table query, filter like:

     

    Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(FilterValues), [CustomerColumnNameFromThisFactTable]))

     

    --Nate

     

     


    Try using a buffered list instead of a buffered table:

     

    Table.SelectRowsFactTabls

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    If these are from the same database, a mere inner join from dimension to fact would normally be your best option (without the buffer, which causes the problem you describe above.

     

    You could also try a buffered list instead of table, by making a query that gets the distinct values from the customer column of the fact table, like List.Distinct(Fact[Customer])

     

    Name that query FilterValues.

     

    Now when you make your fact table query, filter like:

     

    Table.SelectRows(PriorStepOrTableName, each List.Contains(List.Buffer(FilterValues), [CustomerColumnNameFromThisFactTable]))

     

    --Nate

     

     


    Try using a buffered list instead of a buffered table:

     

    Table.SelectRowsFactTabls