I would like some advice regarding the following FACT table construction

View previous topic View next topic Go down

I would like some advice regarding the following FACT table construction

Post  amosgreg on Wed Jul 18, 2012 2:33 pm

I am a relative newcomer to Warehouse reporting. We have an OLTP system that I need to be able to transform into a Star and then use a Business Objects Universe to allow self-service reporting. The primary transaction log contains a number of amount fields that can be used as measures easily enough. The problem lies in that there is a 0,1 or Many relationship to a transaction detail log that also contains amount fields but not all that are contained on the transaction log. The best example I can think of is a purchase order that may be cash (no detail log records) or a payment where the department has 1 or more accounts to fund the purchase. The total for all the detail amounts equals the total amount on the transaction log.

What I need help on is understanding how to denormalize the structure from a reporting point of view.
I can solve with the ETL portion a check of the detail and if there is none, use the transaction log total amount but if there is detail rows, use those instead. My problem is how to manage the additional amounts (4 or 5) stored on the transaction log and not carried on the detail log. Trying to seperate out, divide and store the portion based on the number of details is not a good solution but I am not sure how to handle.

Thanks and please let me know if there is further clearification needed.
Greg

amosgreg

Posts : 4
Join date : 2012-07-18

View user profile

Back to top Go down

Re: I would like some advice regarding the following FACT table construction

Post  ngalemmo on Wed Jul 18, 2012 2:59 pm

You can build two fact tables with common dimensions.

On the BOBJ side, each star is a context (the fact table and its dimensions). If you need to combine measures from the two facts, BOBJ will correctly aggregate each fact individually then join on common attributes.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: I would like some advice regarding the following FACT table construction

Post  amosgreg on Wed Jul 18, 2012 3:04 pm

Thanks ngalemmo, you stated the following and I added numbers to help me understand.

ngalemmo wrote:1) You can build two fact tables with common dimensions.
On the BOBJ side, each star is a context (the fact table and its dimensions). 2)
If you need to combine measures from the two facts, BOBJ will correctly aggregate each fact individually then join on common attributes.

1) If I build 2 Facts with common dimensions and the user picks to sum Transaction amount from both facts(2), then we will overstate since 1 Fact contains the details of the other.
2)I understand BO will aggragate but again it will overstate the amounts or counts.

Am I missing something or not understanding you on this point?

amosgreg

Posts : 4
Join date : 2012-07-18

View user profile

Back to top Go down

Re: I would like some advice regarding the following FACT table construction

Post  ngalemmo on Wed Jul 18, 2012 3:43 pm

It will appear as two measures in the report. In the Universe you map each table individually. Each fact maps to different columns in different tables. BOBJ will not combine these. On the dimensions you have one instance of each table and you only need to define them once. When users pick attributes, BOBJ will know it is the same column on a table that is shared by both contexts, so it knows it can use it to combine (join) the measures from both facts.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: I would like some advice regarding the following FACT table construction

Post  amosgreg on Wed Jul 18, 2012 3:46 pm

Thanks
I'll play with this and see how it works.


amosgreg

Posts : 4
Join date : 2012-07-18

View user profile

Back to top Go down

Re: I would like some advice regarding the following FACT table construction

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