Search code examples
ssascubesmsbi

SSAS -How to select a particular attribute when we drag dimension into query editor


I have about 4 attributes in Race dimension as shown in blow

dimension name is Race

1)Race 2)RACE DESC 3)RACE KEY 4)RACE SHORT NAME

when go to cube browse and right click on Race dimension and select add query as below

1)when i drag Race dimension to browser data panel it showing default Race Attribute data

2)i want show RACE DESC data only

3) at the same time i drag the RACE DIMENSION in Filter panel

4) i want show RACE DESC Attribute only

5) i don't want set attributehierarchyvisible =false

how do i achieve my above requirement

Thanks for the help


Solution

  • Create your own user's hierarchy and place it on the 1st place in dimension hierarchies part of screen. This will let SSAS to use it.

    Hierarchies order

    Here Report Date hierarchy is selected by default.

    And Product Categories on the image below:

    HierarchiesOrder2

    UPDATE

    Here is detailed explanation:

    Now you have 5 flat hierarchies and server takes first alphabetical one by default, like this (Count is measure here):

    DraggedDimDefaultHierarchy1

    To fix this, you need to disable attributes hierarchy, which you want to be selected by default:

    DraggedDimDefaultHierarchy2

    Than rename attribute, to be able to create user's hierarchy with the same name (so for users this will be identical as previous flat attribute hierarchy):

    DraggedDimDefaultHierarchy3

    Finally, process this dimension again, and when you drag dimension, it will show your first user's hierarchy, which is State in our case.

    DraggedDimDefaultHierarchy4

    Hope this helps.

    UPDATE-2 (New example with races)

    To achieve this, you need to do the same as described in UPDATE #1:

    1. Rename RACE DESC to some other name (e.g. RACE DESC Attr) and disable it's hierarchy visibility by setting attributehierarchyvisible = false
    2. Create user's hierarchy on this attribute with desirable name: RACE DESC
    3. Process dimension.

    That's all. Now default attribute will be RACE DESC. It's hierarchy is not disabled, just it's showing priority is changed to be the 1st one.