Is a fact table contains summary data

View previous topic View next topic Go down

Is a fact table contains summary data

Post  samimusleh on 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 : 50
Location : Saudi Arabia

View user profile

Back to top Go down

Is a fact table contains summary data

Post  hkandpal on 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

View user profile

Back to top Go down

Re: Is a fact table contains summary data

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Is a fact table contains summary data

Post  samimusleh on 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 : 50
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Is a fact table contains summary data

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Is a fact table contains summary data

Post  samimusleh on 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 : 50
Location : Saudi Arabia

View user profile

Back to top Go down

Re: Is a fact table contains summary data

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