OLAP cube with filtered data

0
votes

I have an ordinary OLAP cube (MS AS2000) with three dimensions, time, market, geography. Each of these dimensions has a simple hierarchy, e.g. time - [all][year][quarter][month], product - [all][market][brand][product]. There are two measures: value, units.

Assume that for business reasons I don't want to distribute that cube with all product brands data. Someone may order/buy sales data for his brand and selected competitor. However for the market level, the cube should have full market aggregated data. In other words, there are four brands: B1, B2, B3, B4. A client orders data only for B1 and B2, so his cube should have data for B1 and B2. But the brands market should have aggregated sum of four brands.

  1. It is possible to build a such OLAP cube, where aggregated data of lower level cells doesn't sum up to parent cell value?
  2. If yes to above, then how to find cells with values that do not equal to aggregated lower levels.
1
mdxolap

1 Answers

1
votes

I'd probably be looking to do that in the data warehouse rather than the cubes. So for your example where they've bought B1 and B2 I'd create a new product in the product dimension called "Rest of Market" and then replace the B3 and B4 ID's in the Fact table(s) with the ID for "Rest of Market".

You may be able to use Parent/Child hierarchies because with that option you can have "data members". These are non-leaf members that can contain data. Have a lok at this link here for more info Parent Child Dimensions