Using a dummy dimension to implement histograms over nonexisting hierarchies

As seen in the previous recipe, Analysis Services supports attribute-based histograms by design. All it takes is a distinct count measure and we're good to go.

This recipe illustrates how to implement a more complex type of histograms over nonexisting hierarchies.

The complexity comes from the fact that the hierarchy which we'd like to base the calculation on does not exist. That's a big issue where a multidimensional cube is concerned.

OLAP cubes operate on predetermined structures. It is not possible to build items on the fly. In other words, it is not possible to create a new hierarchy based on a calculation and use it the way we would use any other hierarchy. In OLAP, every hierarchy must be prepared in advance and must already be a part of the cube, otherwise it can't exist.

In this recipe, we're interested in the fact table. The fact table represents a series of events that are taking place and are being recorded...