FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?

[rameshusa]

Hi,

I am designing a Fact Table, where I have about 10 Keys and 2 degenerate dimension (The field has values say A and B. However, the business requirement involves showing up values for A, B and C (which is basically A - B values).
Keys Degenerate Dimension 1 measures1 ....30
Record1 ...... A 50 ........
Record2 ...... B 35 ........

Users can select parameters in the dashboard, A (Which will show the value of 50 ), B (Which will show the value of 35). However, when the user selects C - I should take the values of measures A and measure B, subtract and then show ( In this case - 50 - 35 = 15 ).

What is the best practice, Whether I should insert records for type C (Which increases the number of rows considerably in the FACT Table) or do the computation on the fly in the dashboard application. The dashboard application is on the mobile platform and faster response is required.

Please let me know what should be done in this case.

Thanks for your time.

RameshUSA

[BoxesAndLines]

Have the BI tool subtract the two numbers. No design required on your part.

[TheNJDevil]

Basic math should be done by the BI tool. The only time I include an math result is when the formula is complex and the chance for the BI & ad/hoc users to get incorrect results is high.

[VHF]

I think you are going to have to test whether calculating on the fly in your dashboard is fast enough to meet user requirements. That would certainly be the prefered approach from a dimensional modling perspective. Storing what is essentially a subtotal in the fact table is not prefered because of the potential for getting incorrect results (i.e. adding A, B, and C records and getting a meaningless number), plus making the fact table larger reduces performance all around.

If calculating on the fly isn't adquate performance-wise, you could create a separate fact table to store the 'C' records. This would essentialy be an aggregate fact table. Of course, your dashboard would need to choose the alternative fact table when the user selects 'C'.

[rameshusa]

Thanks a lot.

[Sponsored content]