Detecting the root member

The root member is the topmost member of a hierarchy. It is present in all hierarchies (in user hierarchies as well as in attributes hierarchies) as long as the IsAggregatable property is enabled, as in its default state.

The root member represents the highest level of granularity within a hierarchy the data can be aggregated up to. When calculating the percentage of a total, we need to detect whether the current member in the query context is pointing to the root member of a hierarchy or not. Based on the detection result, we can make our calculation of the percentage of a total response different to that of the root member.

Although we have the real-world application of the root member detection in mind, this recipe shows only how to detect the root member.

Getting ready

Start SQL Server Management Studio and connect to your SSAS 2016 instance. Click on the New Query button and check that the target database is Adventure Works DW 2016.

In this example, we're going...