Hi Hubert,
I had ordered the data source query using an order by clause with the attributes concerned.
Yes, you are right in stating that the dimension should have been loaded in the same order.
Yet, it wasn't. There were these occasional input records that were not sorted right.
This was what was originally frustrating me. I tried deleting all members from leaves, the default hierarchy, and even destroying and recreating all the "attribute" hierarchies, assuming that the data was being cached somehow from a previous run.
None of this worked.
The only thing that has worked is creating the member names in a manner that the members can be sorted "By Name", by appending the sort fields to the beginning of the member name.
Perhaps the fact that the dimension was not sorted in load order is a bug? Not sure.
------------------------------
Shubho Ghosal
------------------------------
Original Message:
Sent: Wed November 20, 2019 04:00 AM
From: Hubert Heijkers
Subject: Cannot get members to order using a field in a data source query
Hi Shubho,
You started off with stating that the sorting was already applied to the data source query already, so would it be fair to assume that there the elements are actually sorted by this attribute that you'd want to sort them by already? And therefore, presuming you are simply adding these elements one by one to a dimension you are recreating, that TM1 is sorting those elements for you once again? By name?
------------------------------
Hubert Heijkers
Original Message:
Sent: Tue November 19, 2019 02:06 PM
From: Shubho Ghosal
Subject: Cannot get members to order using a field in a data source query
One workaround is to add the attribute information I need the members sorted by to the original member name - this should work but will stretch the member name out further which is an inconvenience. I will add an enhancement request to sort members by attribute if that is at all possible.
------------------------------
Shubho Ghosal
Original Message:
Sent: Tue November 19, 2019 01:45 PM
From: Shubho Ghosal
Subject: Cannot get members to order using a field in a data source query
Thanks Scott - I looked into these and they don't seem to work for me, as I want the members sorted not by name but by other fields/attributes.
------------------------------
Shubho Ghosal
Original Message:
Sent: Mon November 18, 2019 07:38 PM
From: Scott Brown
Subject: Cannot get members to order using a field in a data source query
Hi,
Take a look at the }DimensionProperties cube. You can set how a dimension sorts elements there. Not that all sorting is based on the key element, and not an alias.
Here is a reference to the columns in the cube:
https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_op.2.0.0.doc/c_dimensionproperties_n7041d.html
Take a look at the sort columns. Note that these will take effect after you next update the dimension.
You can also add this to a TI to set the values. This sets the dimension so it first sorts by hierarchy, and then by element name within the hierarchy.
DIMENSIONSORTORDER( DIM, 'ByName', 'ASCENDING', 'ByHierarchy', 'ASCENDING');
Good luck!
------------------------------
Scott Brown
Original Message:
Sent: Mon November 18, 2019 03:36 PM
From: Shubho Ghosal
Subject: Cannot get members to order using a field in a data source query
Does Ti cache previously loaded data? and is there a way to prevent that?
I am running into a frustrating issue with regard to how to load a dimension sorted by a field - e.g., name.
The sorting is applied in the data source query. The dimension members get sorted but there is an occasional member that is not in the right sorting order.
I resorted to destroying all the attribute/alternate hierarchies and deleting all the members in the dimension and even the Leaves prior to re-loading it, but this is not working.
One more question - if I drop and recreate the dimension itself, will the cube still work? Willing to try this as a final step to see if this fixes the ordering issue.
------------------------------
Shubho Ghosal
------------------------------
#PlanningAnalyticswithWatson