Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Making Data Model From Flat File

Dear Community,   I have 1 flat CSV file which I'd like to break down by it's dimensions and create star schema in my data model. I loaded the file in power query and with the "Reference" function...
Need Help
Show and Tell
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous,

     

    How about creating the dim tables with dax formular rather than in power query?

    For example,my oringinal table is as below from an Excel file:

    To achieve what you need:

    Create dim tables as below:

    Table = VALUES(Sheet2[category])

    Then create relationships as you need:

    Pls note that the direction should be from fact table side to dim table side.

    Then you would see when you refresh the table,it will only load one time:

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

     

  • edhans's avatar
    edhans
    5 years ago

    Anonymous It is not loading 46mb. It is processing the 46mb file to load the data. The actual 15 record table is probably a few KB at most. You could validate this using DAX Studio and the Vertipaq Analyzer feature which will tell you the size of each loaded table. 

    this is an issue working with flat tables. With no database on the back end power query has to fully process the entire file even if you have filtered it to just one record. 

    But it is best practice to do this in PQ. Consider if your csv file has the customer number and name in it.


    You could create a DIM table of that info, but by using DAX, you cannot remove that redundant customer name info from the FACT table. You can in power query. Smaller model over all.  

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

That is how it works. Power Query doesn't start at the first query and work down, it starts at the bottom (last) query and works backwards, so 6 tables from 1 will cause it to process that first source table 6 times.

 

One thing you might want to try that may help is all of your DIM tables (except calendar) are based on the fact table, and the fact table is itself loading.

 

Instead, mark the fact table to "disable load" and create a reference from the original FACT table to a new one that is just a pointer. Probably no further transformations. If you go to Query Dependency View, you'd see your Load Disabled FACT table at the top, and all DIMS and new FACT table at the bottom. 

 

And someting to consider: Get rid of DIM and FACT in your table names. Will not help performance one bit, but it is best practice to use table and field names in the language of your audience with no tech speak or CamelCase. If two words, use a space. "Sales Data" - not SalesData, or SalesFACT or any other nerdy stuff. 😁

I love you have your DIMs at the top and FACT on the bottom. Learned from Rob Collie or Matt Allington? Filters flow downhill! 👍