1

I have a MutliDimensional Cube, it has a Dimension(Hierarchy) named "Scenario", within the Scenario dimension I have a "SA - Scenario" level which refers to a DimensionAttribute "Scenario ID", with the following configuration:

enter image description here

So for the Key Column, the "Scenario ID" column is referenced, for the display text "Description" is referenced. So if in the SSAS cube browser(or excel) if you filter to that dimension you will see MDX such as this:

 SELECT 
   NON EMPTY { } ON COLUMNS 
 FROM ( 
   SELECT ( { [Scenario].[SA - Scenario].[Scenario].&[164] } )...

But you will see:

enter image description here

I would like to accomplish the same thing in a tabular cube(generate the same MDX statement when the user selects a scenario), so I built another tabular cube with the same hierarchy(based on the same info) with a "Scenario" hierarchy:

enter image description here

But I don't see an option to configure a "Key" and "Name" for my hierarchy, just "Source" column:

enter image description here

So in MDX that looks like:

 SELECT 
   NON EMPTY { } ON COLUMNS 
 FROM 
    ( 
      SELECT ( { [Scenario].[SA - Scenario].[Scenario].&[2016 Budget] } 
    )

Is that even possible in tabular cubes, to have a hierarchy where the selection of it's element contains a single key column(rendered in MDX/DAX) and another visible to the user column used for display only?

I see some discussion about it here, but I don't see how you accomplish this with a perspective, how would that work? I though perspective we're all about changing visibility and don't directly affect record selection or display.

whytheq
  • 34,466
  • 65
  • 172
  • 267
David Rogers
  • 2,601
  • 4
  • 39
  • 84

1 Answers1

0

In Tabular the key/ID columns would be brought into the model as they are used to form relationships between dimensions and facts, but these columns would be marked as hidden so that the users of the model do not have access to the key/ID. The descriptive columns, such as Name, would be left as visible and therefore used in PowerBI or whatever downstream client is used.

The keys will therefore still exist in the model and should be available for a query.

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • And how exactly would one query them? How would one configure a cube such that the you could query them in the same way a MultiDimensional cube would? – David Rogers Jun 21 '23 at 20:19
  • Hi David - I'm probably misunderstanding the general question. In Tabular for say a Geography hierarchy we might have a 2 column table `GeoKey | CountryName` with the GeoKey set to hidden, so the user only sees the CountryName but if I wrote DAX I believe I could still use the GeoKey column in the script [not tested as I'm currently on a Linux machine] – whytheq Jun 22 '23 at 07:50