Forum Discussion
Data Modelling (Flat file to Star Schema)
Hello all,
I'm in need of some advice from the data modelling gurus. I have made my first attempt at a star schema data model which i have transformed, in Power Query, from a flat excel file.
I have done this for practice, and would really like to know if i have approached this correctly.
Here is my current star schema with all columns:
Here is the Column names from the flat file:
My question is, does this transformation make sense? I am very new to this. Are the dimension tables creating correctly or would you have grouped some categories together?
How do i know if i should use more than one column in my dimension field (excluding ID)?
Will this make a difference to performance if i have done this in power query rather than sql?
Any help would be appreciated, if you need more info please let me know.
Thanks!
Hello Anonymous ,
1- so first of all, you would need to create a dim date table.
refer to this https://youtu.be/-li7sxUxEqA?si=9SvFGgvlyZsa89Gm
2- you would need to put in each dim table all the columns related to it, like for example in customer table you put first name, last name, email, age, address,etc..
like in product table, product Id, name, category, sub-category, unit of measure, etc..
make sure to remove all these details from the flat fact table and only keep the IDs.check this about data Modeling
https://www.youtube.com/live/vr304H4O2tQ?si=Y23cKEMp2_OUHfEWhttps://www.youtube.com/live/vr304H4O2tQ?si=R8o1z3Xxa8z5Vir5
https://www.youtube.com/live/WjXZWA279Cw?si=d0G-TdWyNUq3dSKc
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
2 Replies
Hello Anonymous ,
1- so first of all, you would need to create a dim date table.
refer to this https://youtu.be/-li7sxUxEqA?si=9SvFGgvlyZsa89Gm
2- you would need to put in each dim table all the columns related to it, like for example in customer table you put first name, last name, email, age, address,etc..
like in product table, product Id, name, category, sub-category, unit of measure, etc..
make sure to remove all these details from the flat fact table and only keep the IDs.check this about data Modeling
https://www.youtube.com/live/vr304H4O2tQ?si=Y23cKEMp2_OUHfEWhttps://www.youtube.com/live/vr304H4O2tQ?si=R8o1z3Xxa8z5Vir5
https://www.youtube.com/live/WjXZWA279Cw?si=d0G-TdWyNUq3dSKc
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍