Forum Discussion
Star Schema Fundamentals
- 1 year ago
You don't need to create either the fact tables or dimension tables in the lab space, you can just run the queries in Power Query as part of the import process. Depending on what your lab space is, you might consider creating views of the queries, perhaps in a separate schema if you're trying to avoid clutter. Views don't take up any additional space unlike creating new tables.
The one thing I would say is to do as much as you can in the lab space rather than doing it in Power Query. For example, don't use Power Query to pull out unique values from the fact table to create a dimension table, as that would put unnecessary load on the import process and you would be wasting compute resource within the Power BI Service. Instead write a query to get the dimension direct from the lab space.
On your foreign keys question it depends on the length of the keys. For the race example I would say that it is fine to use the text as the field for relationship between fact and dimension. From a storage point of view, all the unique values of the text field are only stored once in the dictionary along with an integer identifier ( generated internally by Power BI ) and it is this integer that is stored for every row of the fact table. So the difference between using text or using a code that you generate will be minimal.
For the plan's unique identifier, it depends on a couple of things. How long is the identifier, and what do you need it for? If you only want to be able to mark each row in the fact table with a unique key then I would generate something within the query, perhaps using ROW_NUMBER in SQL. If you actually need to display the unique plan identifier so that users can look it up in another report or system then you have no choice but to include it.
What you could do is to import the data with the existing unique identifier and then use DAX Studio to view the metrics and see how large the column for the unique ID is, and how much of the whole model size that represents. If you consider that its too big then replace it with an integer identifer and see how much that takes up.
Hi CenTeam ,
Thank you for reaching out to the Microsoft Community Forum.
Hi johnt75 , Thank you for your prompt response.
Hi CenTeam , Could you please try the proposed solution shared by johnt75 . I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
- v-dineshya1 year ago
Community Support
Hi CenTeam ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
- v-dineshya1 year ago
Community Support
Hi @CenTeam ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
- v-dineshya1 year ago
Community Support
Hi @CenTeam ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh