Forum Discussion
Making Data Model From Flat File
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,
KellyDid I answer your question? Mark my post as a solution!
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.
v-kelly-msft and edhans - thank you both for your information.
I will try it with Values. That will work perfectly. This is also what Aaron from Maven Analytics recommends in his Advanced DAX course. Using Values is easy and cool way to do it.
edhans - will try to get rid of my current labelling of the tables 🙂 I learned to arrange my tables like so from Chris Dutton of Maven Analytics.
Thank you one more time and wish you all the best,
Atanas
Community ChampionI'm glad you got an answer you can work with Anonymous but long term I would advise against working in DAX to shape and model your data. For the very simple example you have shown, VALUES() works, but when the data is more complex - for example, you have additional fields beyond the key field in the flat table, Power Query is the better practice as it can ensure uniqueness of the first key, then add the relevant fields (think Customer Key is unique, and customer name may be, but as you add more fields like state or zip code, those will not be unique), and the DAX gets a bit crazy. From the DAX master himself:
The earlier you fix your data, the better. Delaying data transformation along data supply chain creates a technical debt, which you pay only on data you actually consume.
Additionally, the DAX method will not allow you to remove the redundant data from the flat table, whereas Power Query will if you use the approach I listed above - summarized:
- Connect to the flat table
- Disable load on it
- Create as many references as necessary for all subsequent DIM and FACT tables, with each table only keeping the necessary columns to create a Star Schema.
edhans- Thank you very much. I will consider and experiment with your suggestion.
I understand VALUES is not the best option, at first i was just surprised by the total MB of the data model. Usually I am not Power BI user, but Power Pivot. And when i create similar dim tables from a flat file Excel loads only the rows I told Power Query to filter. So the cardinality of the tables is low and performance is ok. I was truly shocked to see table with 15 rows in it to be 46MB.
- edhans
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.