Complex Business Logic for reporting

Question

Complex Business Logic for reporting

Lexa

Hall of Fame

Points: 3314
More actions
April 13, 2012 at 3:53 pm

#256402

Hi,
We have some reports that are based off of OLTP system. We'd like to migrate those to a DW which is under way. In one of the reports we use 3 tables to repot a "Cost" value. Based on some logic the report picks a value from table 1, 2 or 3. When building ETL, should this logic be incorporated so the fact table has 1 and only correct cost, or should we load all 3 tables, and build the report using MDX? Since we are not as proficent in MDX as in SQL, we're are leaning towards the 1st.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

bleroy Hall of Fame Points: 3052 More actions · Answer 1

I would tihnk that since you are moving this to a data warehouse, you would want to have the calculations performed during the ETL process and only store the results in the fact table - this will save on processing time during report execution, giving some love to your server, and ensuring that your reports are served faster to your customers.

B

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

bleroy (4/14/2012)
I would tihnk that since you are moving this to a data warehouse, you would want to have the calculations performed during the ETL process and only store the results in the fact table - this will save on processing time during report execution, giving some love to your server, and ensuring that your reports are served faster to your customers.
B

+1

ETL is the way to go. The more calculations you do in the ETL (which normally runs during the night, so CPU is not an issue), the faster your reports will be and how easier they are to maintain.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

darth_vodka Default port Points: 1489 More actions · Answer 3

depends if your business logic is EVER going to change

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

If your business logic changes, you change your ETL and your fact table can still contain 1 correct value rather than 3 that have to be deciphered in the end query. I agree with the above, do it in your ETL and have one value.