When you are creating relationships between lookup and fact tables, does the data referenced as the primary key have to be exactly equal to the data referenced as the foreign key? For example, if you are creating a relationship using building names and the names in the lookup table are Food Hall, Sleep Hall and Gym Hall, does the data table have to have all 3 buildings as well?
Eric,
Are you saying that in your look up table there is a Column called: Building Names with Food, Sleep and Gym. Then in your fact table there is also a column called Building names but there is only, say, Sleep and Gym?
if so, that relationship will work fine. Just as long as your lookup table does not have duplicate values. The fact table will more than likely have duplicates, and thats to be expected.
-Nick
Nick,
Yes, that is exactly what I am saying. If there are multiple look up tables with the Building Names as the primary key, do they all have to be same? When I created a matrix to see if the data was pulling into the report view correctly, I ran into some issues as the data was not properly lining up to the correct building (by name).
Thank you for your help.
Eric
Eric,
Could you add some sample data? I think I know what you are asking here, but would be much easier to see it.
Thanks!
Nick,
Here are screen shots of the relationships and a matrix in report view. I have highlighted the issue in the matrix.
Eric
Matrix View.pdf (288.6 KB)
Relationship View.pdf (1.3 MB)
Which table is Asset Name coming from in the Matrix?
What’s the difference between the BuildingData_Lookup and the BuildingGroups_Lookup tables?
Asset name is pulled from FCA Building Data lookup.
Building data lookup has estimated cost of repairs, dates, etc. It also has the grouping of buildings (classroom, athletics, dorm, etc)
Building groups has names and their group.
hmm. would be much easier if it was possible to get some sample data that you are using, or something close?
Okay. I’ll send over later today.
My question, asked another way, is should I use the primary key (asset name) from the building lookup table as the rows in the matrix and then use foreign keys (expenses, depreciation) from the building data table as the values?
Filters should come from your lookup tables and your measures (generally) are from Fact Tables.
Nick, I figured it out. Many thanks for your help.