March 31, 2019 at 2:57 pm
I'm fairly new to dimension modelling and looking for some advise on the approach to take in modelling this scenario. I'm struggling to identify the Fact where as everything feels like a Dimension.
Scenario:
I have Project which has various attributes (value, location, start and end dates etc)
A Project can have many characteristics (a mixture of boolean flags, dropdown and numeric values)
A Project can have many Awards (type, score)
A Project can have many References (type, who, reference)
None of this data changes frequently, most is fairly static. The source system contains many tables incorporating key values pairs and various relationships.
Outcome:
The ultimate goal is to simplify the model for use in self service reporting along with creating a view of each project containing characteristics, awards, references etc.
The types of questions end users may ask are:
"Show me projects with these characteristics where the project location is x"
"Show me projects with more than 5 awards"
"Show me projects where value is greater than X with these characteristics"
Any advise on the approach I should take to modelling this would be much appreciated.
April 1, 2019 at 11:16 pm
This looks like a typical "fact-less fact" scenario, where the fact table is not a transaction in time but rather the intersection (or existence) between different dimensions.
There's a few different ways you can go about this in terms of modelling: Either have one fact table that contains the dimension key references to the Awards and Reference dimensions, or two separate fact tables (one for references and one for awards). The relationship between the fact and dimension tables will most likely be 1:1, and sometimes I like to add a hard-coded "1" as a counter in each fact record to make counting the rows easier.
Hope this makes sense. Think of a fact in this case as a project reference or project award. The existence of a fact record will indicate the relationship between the dimensions.
April 2, 2019 at 10:28 am
I've done a little more research and I've come to the same conclusion regarding a fact-less fact table, so thanks for reaffirming this and your response, much appreciated.
Dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply