Home / Questions / 263489
Accepted
SeveredementiA
Asked: 2020-05-15 08:33:24 +0800 CST 2020-05-15 08:33:24 +0800 CST

Measurement in a datawarehouse fact table

  • 772

I have a doubt in some concepts in the subject of data warehouse , if someone could clarify it for me I would be very grateful.

To explain my doubt, I will present the following example. There is the following dimensional scheme :

Example Dimensional Modeling

Taking into account that there are measures of the Additive, Semi-Additive and Non-Additive type .

How do you insert the average data into the fact table since an average is a coarser-grained measure? (Different granularity).

First of all, Thanks.

sql

1 Answers

  1. Best Answer

    The average cannot be aggregated in a fact table.

    The average is not a measure that can be summarized, or calculated based on averages. It is a measure that only serves on the universe to take into account. Therefore, this measurement should always be calculated at the time of need.

    If you had a table that said:

    dia cantidad preciototal promedio
1   100      500         5
2   1000     1999        1.9

    Note that if we wanted to calculate the total average, it is not enough to add the averages, but rather we have to add the quantity and the return prices:

    // Promedio del promedio
(5 + 1.9) / 2 = 3.45

//Promedio de las sumas como corresponde
2499 / 1100 = 2.27

    Therefore, except as a point reference, it is not useful to have an average at any level of facts.

    • 1