Forum Discussion
Star Schema Fundamentals
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.
Great feedback and thank you as I now know what more is going on under the hood to help make future decisions. I have a final question (for now) that is slightly different but still within the umbrella of modeling. I'll ask it here though if I should create a new topic for this I will do so.
As per a typical model, my Fact table is on the "Many" side and my DIM tables is on the "One" side. Now I have a Conditions table, where my unique members in my Fact is now on the 1-side and the Conditions table is on the Many side (as a member can have more than one condition). In order for my Conditions selection to also filter the visualizations for the data in the DIM tables I need to change the Cross-Filter Direction to "Bi-Directional" which I know is a general performance hit (though my visualizations are still performing fine). Is this the nature of the beast due to how the datasets are related or am I to do something else in order to still get the filtering I want in the visualizations, while reducing the performance strain?
Super UserI would strongly recommend against using bi-directional filtering. Not only can it degrade performance but it can also lead to unintended and unpredictable results.
I would recommend that you create a Conditions dimension table, containing all the unique conditions you need to handle.
There are several ways of linking that to the model. One possibility would be to denormalise your existing fact and condition tables into 1, so that each member would be replicated for each condition they have. This would obviously increase the amount of data stored, but if the dataset is not too big that might not be an issue. One thing to bear in mind though would be that you could no longer use COUNTROWS to get the number of members, you would have to use a DISTINCTCOUNT on the member ID. That would be much slower.
Another possibility would be to link the Condition dimension to the Condition fact table, and use columns from the Conditions dimension in all your filters and visuals. However for this approach to work, either the measures you use must depend on values in the condition fact table, or you could create a calculation group and apply it to pages or visuals as required, something like
Condition modifier =
IF (
ISFILTERED ( 'Condition Dimension' ),
CALCULATE (
SELECTEDMEASURE (),
KEEPFILTERS (
TREATAS ( VALUES ( 'Condition Fact'[Member ID] ), 'Member Fact'[Member ID] )
)
),
SELECTEDMEASURE ()
)
I think the calculation group option makes the most sense with the data model I have. I will explore that, thanks.