Using PROC OLAP to Build Cubes with NON-Additive Measures
Ben Cochran · 2009
Most of the time, OLAP cubes are built from data that has additive measures, meaning that as you drilldown, the sum of all the lower levels will add up to the value at the highest level of the hierarchy. This is not always the case. Sometimes applications need drilldown capabilities on data where the measures are non-additive. And, sometimes data is additive in one dimension, but not another. Take for example, a car leasing company that has 2,000 cars to lease. They want to build a cube with two dimensions: Time and Geography. Across the Geography dimension, the number of cars is additive. Let’s say that the levels in the Geography dimension are: Company, Region, State and City. At the Company level, the number of cars is 2,000. When we drilldown to the Region level, the total at all the regions adds up to 2,000. When we drill down to the next level (State), the total number of cars in all the states adds up to 2,000. etc. This same measure (total number of cars) is NOT additive in the Time dimension. Let’s say that the levels of the Time dimension are: Year, Quarter and Month. If we take the number of cars that are leased each Month, they could add up to more than 2,000. And likewise, if we add up all the cars leased each Quarter, they could add up to more than 2,000. But, still this company wants to build a cube with this data. This paper looks at strategies and methods to building a cube with non-additive data. Then, a step by step approach is taken to actually build the cube.