Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Is a fact table contains summary data

3 posters

Go down

Is a fact table contains summary data Empty Is a fact table contains summary data

Post  samimusleh Fri Aug 14, 2015 5:07 am

Dear all,
During a discussion with some of my expert friends , there was a confusion about the Granularity level .
our case is as folllows
suppose we have header and line item tables for an invoice , and we have in the line items to line have the same item code with different quantities , so in the fact table we have the facts (quantity ) and (price ) , is it correct that these to line are kept in one line in the fact table where the quantities and prices are summed together ( i.e. we represent these tow line in one line where quantity = QTY of line 1 + QTY of line 2 from line items table .
My opinion is NO , each line in the line item table represented in separate line in the fact table

Please advice us
Thanks to all
Sami Musleh

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 56
Location : Saudi Arabia

Back to top Go down

Is a fact table contains summary data Empty Is a fact table contains summary data

Post  hkandpal Fri Aug 14, 2015 8:15 am

Hi Sami,

it depends upon how the user are going to look into the data captured in the fact, if they want to see at a order lever then you capture that , if they want the line level then you capture the lines and if you requirement is to have the order and line they one way could be to have the line level information captured and the order summary also could be captured as a table or a materialized view.

thanks

hkandpal

Posts : 113
Join date : 2010-08-16

Back to top Go down

Is a fact table contains summary data Empty Re: Is a fact table contains summary data

Post  ngalemmo Fri Aug 14, 2015 2:49 pm

Yes, it is good practice to keep facts at the same level of detail as the original transaction. Granularity should be the invoice line. What is unusual is you have an invoice header fact. Such a table is usually unnecessary.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Is a fact table contains summary data Empty Re: Is a fact table contains summary data

Post  samimusleh Sat Aug 15, 2015 1:26 am

Thank you Mr. ngalemmo , The question is : Is the Fact table is a sum of all facts for all (group by ) dimensions , as if I use the following statement

SELECT SUM(FACT1),SUM(FACT2), SUM(FACT3) FROM TABLE1 , TABLE2 , TABLE3  WHERE (...) GROUP BU DIM1,DIM2,DIM3,DIM4

So when we need to get the actual data (without grouping ) we have to go to the OLTP tables .
Thanks

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 56
Location : Saudi Arabia

Back to top Go down

Is a fact table contains summary data Empty Re: Is a fact table contains summary data

Post  ngalemmo Sat Aug 15, 2015 2:35 pm

It depends on what you are doing.

From a data warehouse point of view (that is to say, storing a stable, accurate history of the business) then no. The primary goal is to store data at its lowest level of granularity. Once that is done, you may consider creating summary facts for performance reasons.

From an OLAP point of view, yes. OLAP tools work with summarized data. This data is usually sourced from the data warehouse.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Is a fact table contains summary data Empty Re: Is a fact table contains summary data

Post  samimusleh Sun Aug 16, 2015 12:16 am

Thank you Mr Ngalemmo
You have been very helpfull

samimusleh

Posts : 23
Join date : 2013-03-03
Age : 56
Location : Saudi Arabia

Back to top Go down

Is a fact table contains summary data Empty Re: Is a fact table contains summary data

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum