locked
ordering self referencing employee dim by employee name RRS feed

  • Question

  • Hi.  I just noticed that when I expand my self referencing employee dimension rows and employee name was chosen as the attribute, the sort order appears to be the PK of the employee star table.  I played with the OrderBy and KeyColumns in the properties of the name attribute to no avail.  At the moment the Key in this attribute's properties is the employee name itself and the OrderBy is Key.

    How can we order expanded attribute rows by name when name is chosen?

    We run STD 2008.  I cant afford to renumber pks every time an employee is added.  At the moment there is no user defined ssas hierarchy in this dim's designer panel.  My recollection is that a hierarchy here might interfere with the security and/or self referencing behavior but I may be mistaken about that and would have to reread some old notes if that becomes an issue. 

    • Edited by db042188 Thursday, October 14, 2010 2:32 PM changed title not to use the word hierarchy
    Thursday, October 14, 2010 2:17 PM

Answers

  • Suppose, for example, this is the dimEmp:

    ID ParentID  Name

    5     NULL    Z

    1     5        D

    2     5        C

    3     5        B

    4     5        A

     

    So the root member is Z, and now the results you got area:

    Z     D

          C

          B

          A

    However, what you want is bellowing, right?

    Z     A

          B

          C

          D

     

    If so, do not sort the manager attribute (i.e. the one with the self referencing arrow). What you need is sorting the key attribute of dimension. Create the key attribute on column ID, and then set the name column to be Name, after that, set the OrderBy property of key attribute to be ‘Name’. You can also download the sample cube [Adventure Works], and find the dimension Employee to check how it works.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    • Proposed as answer by Philip Stephenson Tuesday, October 19, 2010 7:00 AM
    • Marked as answer by db042188 Wednesday, October 20, 2010 2:34 PM
    Tuesday, October 19, 2010 6:26 AM

All replies

  • Hi,

    Did you create Parent/children hierarchy in that dimension? If yes, then you cannot sort the parent attribute, because it’s a Parent/children hierarchy and order is preorder traversal of the tree. However, you can sort other attributes, for example the key attribute. In [Adventure Works], if you enable key attribute [Employee].[Employee], you will see the members are sorted by the name of the member.

     

    Hope this helps,

    Raymond
    Raymond Li - MSFT
    Monday, October 18, 2010 8:38 AM
  • thanks Raymond.  I'll try to pick my words carefully.

    In the middle pane of my employee dimension structure in VS, the pane called "Hierarchies" (rt of attribs and left of DSV) there are no attributes dragged there.

    in the list of attribs in the "attributes" pane of the dim structure in VS, the manager key shows a self referencing arrow.  Both the manager key and pk are integers.  The manager key attrib's NameColumn is the employee name attrib mentioned below.  It is the manager key I am picking and expanding in my pivot area when order seems funny.

    other important attributes in the latter pane are employee name, initials and login.

    In the attrib relationships of same dim, only one lozenge displays with my pk's name.  When I expand it, all the other attribs show below the pk.

    I think you are saying that I'm stuck.   Is that true?  If yes, I may have to resort to the following...

    I am considering a trick I've seen in other MS technologies.   I dont know if it is even possible here but I am considering changing the pk's data type (and manager fk) to var char or char or var bin depending on what I find in my notes on this subject.   Then, to preserve the same alphabetic order of name as the pk collation, a "binary value" would be used in this dim's pk to represent any new employee.  If the new employee name doesnt naturally fall in at the end of the current key assignments, a "binary value" is chosen between the two keys where it would fall.  The keys are left justified making this work.  here is an example as I remember it...

    Smith, Joe                originaly added as pk x'0001

    Taylor, Ann              originally added as pk x'0010

    Then along comes Smith, Robert.  His key is calculated as x'000101...or something like that.  I think whole bytes have to be added in t-sql when a "betweener" key is being calculated like this.   Please let me know what you think. 

    • Edited by db042188 Monday, October 18, 2010 5:27 PM binary value in double quotes to show its not really a binary value
    Monday, October 18, 2010 4:11 PM
  • Hmmm, I'm not sure if I am reading this correctly so I'll rather phrase my answer as a question...

    If you specify the "Order By" property on the manager attribute (i.e. the one with the self referencing arrow)  to be "Name" and deploy this (or save if online) do you not see the children of a member ordered by name? For example, if you have a root member of Joe Bloggs with children of Zaida, Bob and Jim you should see those members listed below Joe Blogss in the order of Bob, Jim, Zaida. This is the alphabetical sorting for the level.

    Note that some client tools don't always indent members based on level and thus you can get something that looks like it is not sorted.

    Monday, October 18, 2010 6:54 PM
  • thx Phillip.  That is correct.  They come up in what appears to be PK order even when I delete the DB, change OrderBy property in Mgr Key to Name and redeploy.   
    Monday, October 18, 2010 8:01 PM
  • Suppose, for example, this is the dimEmp:

    ID ParentID  Name

    5     NULL    Z

    1     5        D

    2     5        C

    3     5        B

    4     5        A

     

    So the root member is Z, and now the results you got area:

    Z     D

          C

          B

          A

    However, what you want is bellowing, right?

    Z     A

          B

          C

          D

     

    If so, do not sort the manager attribute (i.e. the one with the self referencing arrow). What you need is sorting the key attribute of dimension. Create the key attribute on column ID, and then set the name column to be Name, after that, set the OrderBy property of key attribute to be ‘Name’. You can also download the sample cube [Adventure Works], and find the dimension Employee to check how it works.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    • Proposed as answer by Philip Stephenson Tuesday, October 19, 2010 7:00 AM
    • Marked as answer by db042188 Wednesday, October 20, 2010 2:34 PM
    Tuesday, October 19, 2010 6:26 AM
  • nice!  Thank you Raymond.  I wasnt able to remove a SORT choice from mgr key but apparently it is ignored.  May I suggest that MS remove that misleading property on certain types of attributes in its next release?
    Wednesday, October 20, 2010 2:34 PM
  • Sure, please feel free to submit your suggestion to Product Group. Your feedback are always welcome. Just set the type as suggestion.

    Thanks,


    Raymond Li - MSFT
    Thursday, October 21, 2010 6:00 AM