Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Project doubles in size when creating dimension tables

I have one large data set (300MB about 700k rows).

 

I would like to make a series of dimension tables to reduce the size. My process has been to duplicate the original query, remove the columns I don't need, remove duplicates, add an index column. Then I will merge it back to the original query and just keep the index column.

 

However when I refresh my project it goes to 600MB. And when I add another query and follow the dame process it goes to 900MB. How can I avoid this I thought creating Dim tables would make the project run smoother with less data..

Need Help

4 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    1 year ago

    Hi Anonymous 

     

    From which source are you getting your data from? Can you write an SQL query just to bring in the columns you need?

     

    I would also do the following

     

    • Remove any unnecessary columns from your Fact table before duplicating.
    • Convert any DateTime columns to Date if you don't need the time. If you Do create a separate column for time.
    • Do you have a Date table in your model? (see my Bio) If yes turn off Auto date/time for new files in both sections of the settings

       

    • There isalso something I came across recently, you will need to install tabular editor, but it can reduce the size by a few hundred MBs. https://datamozart.substack.com/p/hidden-little-gem-that-can-save-your

     

    Hope this helps

    Joe

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I don't have access to SQL on my work computer but that could be an option. Thanks for your response. I didn't realize that the duplication would still be a large file after I removed the columns I didn't need.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    First, are you confusing the query size with the data model size? You might see the table queried 3 times, but your resulting data model will be much smaller if you've replaced a non-integer dimension with integers.

     

    Second, instead of duplicating the table, duplicate just the data source, remove all of the other columns (after duplicating the filter logic if necessary), then make distinct and add the index. Then perform your join. If your indexes are both sorted the same way, then you can use Table.Join instead of NestedJoin, and use the JoinAlgorithm.Sortmerge for the JoinAlgorithm parameter. This way the data will be streamed and not have the table brought into memory.

     

    --Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for the solutions Anonymous  and Joe_Barry  provided and i want to offer some more information for user to refer to.

    hello Anonymous , based on your description , you can consider to use calculated table to create the demension table in desktop. such as 

     

    Table=summerize(table,[index]...)

     

     You can refer to the following link about it.

    Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn

     

    Best Regards!

    Yolo Zhu

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