One or many fact tables
4 posters
Page 1 of 1
One or many fact tables
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
Re: One or many fact tables
Why not have materialized views for each customer looking at the same fact table?
Re: One or many fact tables
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?
Re: One or many fact tables
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
Similar topics
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum