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

View previous topic View next topic Go down

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

Post  rameshusa on Thu Apr 18, 2013 6:54 am

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

rameshusa

Posts : 3
Join date : 2013-04-17

View user profile

Back to top Go down

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

Post  BoxesAndLines on Thu Apr 18, 2013 8:27 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

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

Post  TheNJDevil on Thu Apr 18, 2013 9:40 am

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.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

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

Post  VHF on Thu Apr 18, 2013 9:53 am

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'.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

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

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