Creating Star Schema from single table

[clay_75]

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

[Idrissshatila · Accepted solution]

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_75]

Perfect, thanks!

[clay_75]

Where's the Edit option?? Sorry, correcting typo above:

I'm NOW creating a report that will  extract data from a log file