What is exactly a measure in star schema in data warehouse design?

Question

Star schema consists of dimension and fact tables.

Fact tables contain foreign keys for each dimension and in addition to that, it contains "measures". What exactly comprises of this measure ?

Is it some aggregate function's answer that is stored ?

sehe sehe · Accepted Answer · 2011-09-30T22:05:22

Basically yes.

If you had a simple grid

Salary     Januari   Februari   March   April   May    June
           Q1                         Q2

Me         1100      1100      1100    1100    1500    1500
Collegue1   2000      2000      2000    0       0      0

Time is a hierarchical dimension with two levels (shown). The other dimension shown is 'EmployeeID'. The other dimension (not shown) could be in the PointOfView (e.g. Budget/Actual).

The Amount (1100, e.g.) is the Measure and it constitutes your facts (the non-identifying parts of the facts). The dimensions define consolidation functions for each measure on the various levels (E.g. Amount(Q1) == SUM(Amount(January...March))). Note that the consolidation will behave differently depending on the measure (e.g. the income tax % will not be summed, but somehow consolidated: how exactly is the art of OLAP Cube design).

(trivia: you can have calculated measures, that use MDX to query e.g. the deviation of Amount in comparison the the preceding Quarter, the Average salary acoss the whole quarter etc.; it will be pretty clear that again, the consolidation formulas require thought).

At this point you will start to see that designing the consolidation rules depends on the order in which the rules are calculated (if the formula for 'salary deviation %' is is evaluated FIRST and then consolidated, you need to average it; however if the raw SALARY measure is consolidated (summed) to the Q1,Q2 level first, then the derived Measure can be calculated like it was at the lowest level.

Now things become more fun when deciding how to store the cube. Basically two ways exist:

precalculate all cells (including all consolidations in all scenarios)
calculate on the fly

It won't surprise anyone that most OLAP engines have converged on hybrid methods (HOLAP), where significant parts of frequently accessed consolidation levels are pre-calculated and stored, and other parts are calculated on the fly.

Some will store the underlying data in a standard RDBMS (ROLAP) other won't (OLAP). The engines focused on high performance tend to keep all data in precalculated cubes (only resorting to 'many small sub-cubes' for very sparse dimensions).

Well, anywyas, this was a bit of a rant. I liked rambling off what I once learned when doing datawarehousing and OLAP

What is exactly a measure in star schema in data warehouse design?

4 Answers