Fact one to one relationship with Dim

[bfnz]

I’ve faced the following situation several times and I’ve never been sure what the correct approach is, so I thought it time to check.

With a data source that revolves around a particular entity, say a Case, I build event or history based Facts and the corresponding dimensions as expected. However 90% of the reporting requirements are operational and need to report detail on the Case(s) as at now and not the events, or history, or any kind of measure.

For this purpose the granularity of the Facts is not needed and would slow down queries. The Case dimension has attributes, free text, dates etc and could easily have dimension keys to all of the other dimensions but if I put them in there then it’s going to look like they’ve been snowflaked and because there are free text fields needed for reporting I can’t simply the Case Dimension.

Should I create a Case Fact with all of the dimension keys that would become the hib of most of our reporting and would have a one to one relationship with the Case Dim which contains all the rest?

Thanks

[ngalemmo]

Maybe a dimensional model is not appropriate for your use case. Dimensional models are optimized for mass analytic queries against a population. The rarely involve free-form text because you are doing summaries or trends of large numbers of events. An application that supports queries of a specific item are best served by a normalized model. That is what an ODS is for.

[bfnz]

Thanks, I've kind of always thought that myself too. I work in a government city and all the government departments have data and reporting requirements like this, much more so that you'd get in the private sector I think. The trouble is they all want a Kimball dimensional data warehouse, even though they aren't always sure what that is.

[nick_white]

YOu can combine the DM and ODS approach: query your DM to find all Cases that meet the selection criteria and then drill through to your ODS to show the detail of that subset of Cases

[Sponsored content]