Multiple facts in a fact table
4 posters
Page 1 of 1
Multiple facts in a fact table
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.
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
Re: Multiple facts in a fact table
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...).
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...).
Re: Multiple facts in a fact table
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
Re: Multiple facts in a fact table
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.
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
Similar topics
» Multiple Facts or Single Facts and Status Table?
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» How to handle multiple aggregations for multiple KPIs in fact table
» Textual Facts in the fact table
» Insurance single fact vs multiple facts
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» How to handle multiple aggregations for multiple KPIs in fact table
» Textual Facts in the fact table
» Insurance single fact vs multiple facts
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum