Today i discovered something that could be interesting for you if you work with multidimensional projects in MS technology. I’ve got calculations in my project some of them was named sets, I would like to test them during the final step of the project. Everything was ok but few named sets wasn’t visible in the client tool. Let’s check this on AdventureWorks sample.
Let’s say that we want to create set that contain only active promotions but our company provide some promotions after first four months. Simple example above (click image to zoom) meets business needs but when we deploy our project to the server and try to verify data in SSMS we will see something unexpected:
Where is our Named Set?
Maybe we should write MDX DML query to retrieve some data from our dataset. We can use something like that:
[Measures].[Reseller Order Count] on 0,
[Active Promotions] on 1
But the result will be nothing or ( 0 rows and 0 columns) or only name of used measure.
When I try to check this in Excel the result was completely different:
Defined Named set was visible and can be used every time I want ( regardless it’s empty). It’s very interesting feature that every developer should know – some client tools try to hide named sets that are empty! Of course if client tools (like SSMS) hide object, it’s still accessible via pure MDX query.
If you want to check behavior of your client tool you can use the same mdx query with small modification:
CREATE SET CURRENTCUBE.[Active Promotions]
DISPLAY_FOLDER = 'Sets' ;
Now, it could be used via GUI of SSMS because it’s not empty anymore. So sometimes our soft try to be nice for us and hide empty objects. Pretty cool!
Latest posts by Adrian Chodkowski (see all)
- SQLDay 2017 – one of the best data platform conferences - January 16, 2017
- Analysis Services Tabular translations and Tabular model explorer - December 18, 2016
- SQL Server 2016 is available! - June 1, 2016