Multi level grainulity in Fact table

[akiran]

Hi ,

I have strange scenario .

The fact table needs to be loaded in different Multi Level Grainluity ,this typical scenario was handled in ETL with complex coding.

Advantages of so,
1) The report was directly taken from single table and had no performance issues.
2)The model was exactly a replication of the existing system .So the report didnot have major frame work changes.
3)When we tried to split the table we had perfomance issues while trying to join the fact tables.So Single Fact with multi level grainulity had been effective.
4)The other alternative apporach was MOLAP which was not that cost Effective.

My point of view is

"It is not must and should that A FACT TABLE NEEDS TO HAVE A SINGLE GRAINULITY"
and i have justification as advantages quoted above.So Most of the forums and books suggest single level grainulity.So the level of grainulity basically depends upon sceanrio and requirements.

So I have two questions in this .

1) Is there any other better approach than this.

[ngalemmo]

So I have two questions in this .

1) Is there any other better approach than this.

Yes. Don't mix grains in a fact table.

What was your other question?

[akiran]

Okie in ideally scenario we should not .

I will explain you the scenrio in brief.

A legacy report was running in a legacy database table which had mutli level grainulity.

the structure for instances.

project site investigator country totalenrolled total enrolled in country total enrolled by investigator
A B C INDIA 10 100 8

So in the report the data displayed is three level multi grainulity

total enrolled is per project per site =10
total enrolled in country is per project per site =100
total enrolled by investigator per project per site per investigator=8


In ideal world scenrio we need determine three different fact tables for each grain.
When i try to define three fact table the performance of the report goes down and was acceptable by business user .


1) So I just wanted to confirm the information that not for all scenrios single level grain does not work .

2) Is there better approach rather than define all the garinulities in one fact table

[ngalemmo]

Whatever.

In general, copying the legacy data structure is ill-advised in the long run. I mean, if you are just replicating the database so you and run some reports, thats fine, but it is not a data warehouse.

I also don't see why you believe you need three fact tables. I've also never been in a situation where it is a good idea to implement a multi-grained fact table.

If you fact table was simply:

project site investigator country total_enrolled
A B C INDIA 8

Couldn't you sum total_enrolled by any of the dimensions to get the number you need? Would there not be other rows for different combinations of dimensions?

[LAndrews]

I have to agree with ngalemmo, there is a single fact grain here (the total enrolled by an investigator)

The report will then need to make 3 queries against the fact (for the 3 different aggregations), and then stitch the result sets together. If performance is an issue, then the next step would to add aggregate fact tables to the model. (e.g. one aggregate fact by Country, and another by project). The report would still make 3 queries, they would just be against different facts, and again the results would be stitched together.(your report should not "join the fact tables").

The problem with mixing grains is one of flexibility - these designs often are driven by the needs of a single report, and often cannot be used for other report designs. Fact tables that enforce the single grain can be used to create reports with any combination of dimensional attributes.

By mixing grains, you'll find yourself going down a path of "report specific" fact tables.

[akiran]

Multi level grainulity in Fact table


The table which i have coded is not that simple and has 142 columns in the table with different grain .


ngalemmo

1) We are replicating the legacy system for two reasons the users are not ready for a report frame work change nor the report and column changes including joins.

2) We cannot sum on the aggregated on the table for instance as we are calculating some predefined values on the fly and aggregated the predefined values in different grainulity.

For instance

I will calcualte the totalenrolled per project /per site and i will calculate median_rate (median value) per project /country and factor value per project and all the three columns need to be populated in the same table like this there are 100 columns out of 142 columns.


LAndrews


I have tried two scenrios both creating single grain fact and also combining the queires but both took the reasonable amount time in what whatever tuning required including partioning .


So i had a faced the scenario and verifed across different architects across various organziation in which two of them and faced the same scenario .


So i wanted to know if there is any approach or concepts to handle this type of scenario.

Where
1) we need not chnage the frame work for reporting
2) Still have a good reponse for reporting
3)Can you have ideally concept apart from MOLAP.



Thanks for your reponse it was very informative ...............

Akiran
Necessity is mother of Invention

[Sponsored content]