Fact one to one relationship with Dim

View previous topic View next topic Go down

Fact one to one relationship with Dim

Post  bfnz on Thu Jul 10, 2014 3:19 pm

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

bfnz

Posts : 4
Join date : 2014-07-10

View user profile

Back to top Go down

Re: Fact one to one relationship with Dim

Post  ngalemmo on Thu Jul 10, 2014 3:37 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact one to one relationship with Dim

Post  bfnz on Thu Jul 10, 2014 4:03 pm

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.

bfnz

Posts : 4
Join date : 2014-07-10

View user profile

Back to top Go down

Re: Fact one to one relationship with Dim

Post  nick_white on Fri Jul 11, 2014 2:33 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Fact one to one relationship with Dim

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum