Is a fact table contains summary data

[samimusleh]

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

[hkandpal]

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

[ngalemmo]

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.

[samimusleh]

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

[ngalemmo]

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.

[samimusleh]

Thank you Mr Ngalemmo
You have been very helpfull

[Sponsored content]