Aliases in SSAS Tabular Models

Prerequisite

All information in this page is conditional upon the following setting in the CXO Configurator:

Open

In previous versions, this setting only applied to SSAS Multidimensional Models. In the current version it also applies to Tabular Models.

Meta-data retrieval Dimensional Models

For Multidimensional models:

Open

the dimension-mapping usually looks like:

Based on this specification, in case a meta-data refresh is requested the following MDX query is being executed for the Account dimension:

WITH MEMBER [Measures].[__CXO&MD_Id] AS [ACC].[ACC].CURRENTMEMBER.Name
MEMBER [Measures].[__CXO&MD_Name] AS [ACC].[ACC].CURRENTMEMBER.Name
MEMBER [Measures].[__CXO&MD_Parent] AS [ACC].[ACC].CURRENTMEMBER.PARENT.Name
MEMBER [Measures].[__CXO&MD_DESCR] AS [ACC].[ACC].CURRENTMEMBER.PROPERTIES("Language1")
SELECT {
[Measures].[__CXO&MD_Id], [Measures].[__CXO&MD_Name],
[Measures].[__CXO&MD_Parent], [Measures].[__CXO&MD_DESCR]
}
ON COLUMNS,
[ACC].[ACC].ALLMEMBERS ON ROWS
FROM [CXO]

With outcome:

On the basis of this result set the internal hierarchy for building lists, drill-paths, etc. is constructed. The [Measures].[__CXO&MD_DESCR] column is used for the Member Descriptions

Meta-data retrieval Tabular Models

For Tabular Models:

The dimension mapping looks like:

Since SSAS Tabula does not support Attribute Hierarchies, the column Alias Location only has 2 options:

1. Member Name - the alias of a member equals the member-name (unless the functionality represented by columns ‘Custom member name pattern’ or 'Member description SQL statement' is being used).

2. Custom MDX Query - the alias will be based on an MDX query

In case of ‘Member Name’, the automatically generated MDX query for retrieving the meta-data looks like (example for Account dimension):

WITH MEMBER [Measures].[__CXO&MD_Id] AS [ACC].[ACC].CURRENTMEMBER.Name
MEMBER [Measures].[__CXO&MD_Name] AS [ACC].[ACC].CURRENTMEMBER.Name
MEMBER [Measures].[__CXO&MD_Parent] AS [ACC].[ACC].CURRENTMEMBER.PARENT.Name
SELECT {
Measures].[__CXO&MD_Id], [Measures].[__CXO&MD_Name],
Measures].[__CXO&MD_Parent]
ON COLUMNS,
[ACC].[ACC].ALLMEMBERS ON ROWS
FROM [Model]

As you can see, only 3 columns are retrieved and [Measures].[__CXO&MD_DESCR] is omitted, because we cannot retrieve it from the current hierarchy ([ACC]).

By selecting ‘Custom MDX Query' we can use the 'Custom Set expressions to retrieve meta data’

to enter our own query. Although we map the [ACC] hierarchy to the CXO ACC (Account) dimension, in the custom query we use the [ACC_Allias] hierarchy, which is defined as ‘member name - alias’ (so, separated by
' - '). We use this query to

1. Isolate the Alias ([Measures].[__CXO&MD_DESCR]and the member-name.[Measures].[__CXO&MD_Name]

2. Exclude the empty members which are used to balance parent-child hierarchies.

The query:

WITH Member [Measures].[__CXO&MD_Filter] AS IIF(Len([ACC].[ACC_Alias].CURRENTMEMBER.Name)=0,0,1)
MEMBER [Measures].[CXO&MD_Id] AS
IIF(
InStr([ACC].[ACC_Alias].CURRENTMEMBER.Name, " - ") = 0,
Len([ACC].[ACC_Alias].CURRENTMEMBER.Name),
InStr([ACC].[ACC_Alias].CURRENTMEMBER.Name, " - ") - 1
)
MEMBER [Measures].[__CXO&MD_Id] AS Trim(Left([ACC].[ACC_Alias].CURRENTMEMBER.Name,[Measures].[CXO&MD_Id]))
MEMBER [Measures].[__CXO&MD_Name] AS [Measures].[__CXO&MD_Id]
MEMBER [Measures].[CXO&MD_Parent] AS
IIF(
InStr([ACC].[ACC_Alias].CURRENTMEMBER.PARENT.Name, " - ") = 0,
Len([ACC].[ACC_Alias].CURRENTMEMBER.PARENT.Name),
InStr([ACC].[ACC_Alias].CURRENTMEMBER.PARENT.Name, " - ") - 1
)
MEMBER [Measures].[__CXO&MD_Parent] AS Trim(Left([ACC].[ACC_Alias].CURRENTMEMBER.PARENT.Name,[Measures].[CXO&MD_Parent]))
MEMBER [Measures].[CXO&MD_DESCR] AS
IIF(
InStr([ACC].[ACC_Alias].CURRENTMEMBER.Name, " - ") = 0,
0,
InStr([ACC].[ACC_Alias].CURRENTMEMBER.Name, " - ") + 3
)
MEMBER [Measures].[__CXO&MD_DESCR] AS Trim(Mid([ACC].[ACC_Alias].CURRENTMEMBER.Name,[Measures].[CXO&MD_DESCR]))
SELECT
{
[Measures].[__CXO&MD_Id], [Measures].[__CXO&MD_Name],
[Measures].[__CXO&MD_Parent], [Measures].[__CXO&MD_DESCR]
} ON COLUMNS,
FILTER([ACC].[ACC_Alias].ALLMEMBERS,[Measures].[__CXO&MD_Filter]) ON ROWS
FROM [Model]

Resulting in:

Some remarks

1. Entering a full MDX query in the field 'Custom Set expressions to retrieve meta data’ (Source System Manager) can only be done if ‘Custom MDX Query' was selected in the Alias Location field. Otherwise you can only enter a filter for the selected rows. In that case the entered text replaces the
   [Dim].[Hier].AllMembers part of the (autogenerated) query

2. This also works for SSAS Multidimensional Models

3. You can use as many helper calculated members in the query as needed. However, the only permitted columns in the result set are [__CXO&MD_Id], [__CXO&MD_Name], [__CXO&MD_Parent], [__CXO&MD_ACC_TYPE] (optional for the Account Type) and [__CXO&MD_DESCR]

4. Entyering a custom query in the Source Sytem Manager is a bit annoying because then you have to make it one single text-line. It’s easier to insert it with an SQL Statement in table bs_cockpit_dimension of the Repository Database. Something like:
   
   

   (lookup fk_ss_id in table source_system)