Multiple facts in a fact table

View previous topic View next topic Go down

Multiple facts in a fact table

Post  Chan Beauvais on Fri Sep 10, 2010 12:30 pm

One proposed model for our business (wholesale/retail) sales fact is to have every register ring in one fact.
This causes refunds, voids, coupons and other 'facts' to be stored in a single fact entity.
There is a column type dimension that then tells us what 'kind' of fact is in this fact table.

I have challenged this model, as it causes a great deal of case/sum logic to aggregate.
Is it more appropriate to have multiple fact tables for each discreet type of register ring?
Some research indicates rejoining these may impede performance as well.
We're talking billions of rows, about 400,000 per day.

Chan Beauvais

Posts : 1
Join date : 2010-09-10

View user profile

Back to top Go down

Re: Multiple facts in a fact table

Post  ngalemmo on Fri Sep 10, 2010 1:01 pm

The model is fine, and 400,000 a day isn't so bad. Separate fact tables is not a good solution... it only make reporting more difficult. And summing data is the natural course of things... nobody is going to want to look at individual lines anyway.

I am assuming the measures in question are either positive or negative depending on which way the money is going, so that net sales is a simple sum. In addition to the atomic transactional facts, you should consider aggregates to improve performance for more typical queries. The aggregate may spread out the measures by general category (sale amount, refund amount, void amount, coupon amount, tax amount) and summarize at some level higher than ring (store, register, product, day, hour...).

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple facts in a fact table

Post  Jeff Smith on Fri Sep 10, 2010 3:12 pm

The design seems pretty efficient. But aggregates and cubes are the way to go to improve performance. A consultant once said that a good rule of thumb is that if you have more that 10% of the queries going against the detailed tables then you are missing an aggregate table.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Multiple facts in a fact table

Post  hang on Fri Sep 10, 2010 8:31 pm

As ngalemmo and Jeff suggested, splitting up the fact table by sales type may not be a good idea, as those measures do have shared dimensionality, and majority of sales are actually positive sales, far dwarfing other types of sales.

I agree the fist line of consideration on performance is aggregation. However I would also consider partitioning your base fact table, at least monthly, to make your fact tables more manageable and faster for ETL and most of the queries as they are likely run within certain period. It would be better if the table partitioning feature is built-in with your database, otherwise physically divide fact table into monthly named tables and use UNION to combine them if necessary.


hang

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

View user profile

Back to top Go down

Re: Multiple facts in a fact table

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