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

4 posters

Page

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

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

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

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

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

**BoxesAndLines**- Posts : 1212

Join date : 2009-02-03

Location : USA

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

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

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

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

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

Thanks a lot.

**rameshusa**- Posts : 3

Join date : 2013-04-17

Similar topics

» Dimension design question

» Question on Multiple fact table design

» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.

» dimension table design question for around 100 attributes and higher level calculated attributes

» Dimension Design Question

» Question on Multiple fact table design

» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.

» dimension table design question for around 100 attributes and higher level calculated attributes

» Dimension Design Question

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum