The most commonly used reporting tool by the users to query Analysis Services may surely be Excel. As professionals, while testing SSAS solutions, we also add BIDS and SSMS to the list.
One of the great but little known feature of those SSAS reporting tool, is their ability to hide the multiple measure groups complexity by selecting one of them and only displaying the measures and dimensions that belongs to it.
Just in case you don’t know what I’m talking about, here’s a screenshot :
I got asked last week to design a solution where technical measures groups would not be exposed to the users. This is required when, for example, you use many to many bridge measure groups which has no proper business meaning for the users. You can hide the measures, but there is no way in BIDS to properly hide a measure group since there is no “visibility” property attached to them. So in the past; I’ve always handle it by hiding all measures inside the group. The immediate effect in all the above mentioned tools is the complete disappearance of the measure group in the metadata tree view. The problem is that it is still visible in the list of measure groups available in the “Show fields related to …” combo box, which is quite confusing for the users and inconsistent with the metadata tree view behavior.
So I created a feature suggestion to ask the SSAS team to allow us to properly handle measure group visibility so that reporting tools we use know exactly how to behave when we want to hide a measure group rather than to rely on other features side effects. You can find the suggestion here. If you agree with it, please take a moment to vote for it.
In the meantime, we can always create perspectives to properly hide measure groups that should not be exposed.