Forum Discussion
Creating Star Schema from single table
Hi all,
I have created several reports using a standard star schema. These tables in these reports (I assume) would be fairly typicaly in that the dimension tables come from "masterfile tables" in the oltp db and the measure table comes from some transaction table in the oltp (e.g. orders).
I'm not creating a report that will extract data from a log file (this will is big and will be refreshed rarely) - the data in the log table does not use foreign keys in the oltp, it instead logs the business key (not the surragote) as it allows for master records to be deleted without affecting the log (unfortunatley the oltp doesn't use "soft deletes"). The business keys are enough for the analysis required in this report (i.e. we don't need any additional attributes - as you'd usually obtain from masterfile records).
I want to know the best way to model this - it probably depends - I'm currently leaning towards the number 1 below - so I'm just looking for confirmation that's not necessarily a really bad idea 🙂
1. Extract only the log table and then create dimension tables in DAX e.g. ALL(ProductCode)
2. Extract only the log table - is there really any point in creating the dimensions (other than perhaps making the model look like all other reports i.e. using a star schema)
3. Put the work in to the source queries and just use an unknown when a masterfile has been deleted
Thanks in advance for any advice.
Clay
Hello clay_75 ,
If the log table has the dim values in it, extract them in Power Query and build the relationship.
check it out https://youtu.be/7ciFtfi-kQs?si=p0PfkW3mOs36-RYF
3 Replies
- IdrissshatilaSuper User
Hello clay_75 ,
If the log table has the dim values in it, extract them in Power Query and build the relationship.
check it out https://youtu.be/7ciFtfi-kQs?si=p0PfkW3mOs36-RYF
- clay_75Frequent Visitor
Perfect, thanks!
- clay_75Frequent Visitor
Where's the Edit option?? Sorry, correcting typo above:
I'm NOW creating a report that will extract data from a log file