I'm a SQL guy getting pulled into setting up an SSAS cube and pulling it into PowerBI. I'm experimenting with MDX, and have run into something I haven't been able to find an answer for.
Scenario
I have a huge table full of data captured from our SQL Server cohort, and I'm using this to inform a decomposition tree showing a measure and then breaking it down by Instance Name, Server Name, Database Name, Client Hostname and Application Name. My starting MDX query is:
SELECT
{[Measures].[CPU]} ON COLUMNS,
{(
[Profile Data].[Instance Name].children
, [Profile Data].[Server Name].children
, [Profile Data].[Database Name].children
, [Profile Data].[Host Name].children
, [Profile Data].[Application Name].children
)} ON ROWS
FROM [MyCube]
Pretty simple, right? And the actual result is what I need it to be - the sum of CPU time in milliseconds over the capture period, aggregated by the five attributes.
The Problem
When running the MDX query in SSMS, I get a nice caption for my CPU column, and nothing for my 5 aggregators. When I run the same query in Power BI I get a slightly more information column name:
[Profile Data].[Instance Name].[Instance Name].[MEMBER_CAPTION]
And so on for the other four. What I'd like is to be able to control that member caption, whether that's in the Dimension/Cube, or just in the MDX query.
What I've Tried
I've tried declaring them as calculated sets like:
SET [Instance Name] AS [Profile Data].[Instance Name].children
And even tried appending:
SET [Instance Name] AS [Profile Data].[Instance Name].children, CAPTION = 'Instance Name'
To no avail. I suspect the actual answer lies somewhere in the SSAS database. Each Attribute has a caption in the default language, but I have no idea how to go about setting a caption for the members/children.
Any direction would be much appreciated.