One or many fact tables

View previous topic View next topic Go down

One or many fact tables

Post  tale103108 on Tue Apr 12, 2011 1:43 pm

I have a requirement to load customer transaction records (extract) but a reporting requirement that states that no customer (or other user) wants to see another customer's data -- i.e. there is no requirement for cross-customer analysis. Question -- from a design perspective, would it be better to use one fact table for all records, partitioned by customer and date/time or use an individual customer fact table for each customer, partitioned by date/time? Performance is an issue here and each customer has no desire to search through other customer rows, so to speak.

tale103108

Posts : 1
Join date : 2011-04-12

View user profile

Back to top Go down

Re: One or many fact tables

Post  John Simon on Tue Apr 12, 2011 6:07 pm

Why not have materialized views for each customer looking at the same fact table?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: One or many fact tables

Post  ngalemmo on Wed Apr 13, 2011 1:21 am


Some things to think about...

What is a customer?
How many are there?
Do you really want to make major schema changes every time there is a new customer?
What would be the cost and time required to bring a new customer into the system?
Are all customers given the same functionality?
Would a customer ever know there is other customer's data in the same table?
If so, how?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: One or many fact tables

Post  hang on Wed Apr 13, 2011 1:57 am

Is that a matter of implementing a proper security model around your customer dimension rather than fragmenting your conformed dimensional model. I can think of two options, divide your customers into user groups and relate the user group to your customer dimension. Another option is to implement the security model at OLAP cube level using MDX which could give you more granular and programmatic access control.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: One or many fact tables

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