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, ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.