Planning Analytics

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

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

A little more follow up.   
You may not delete a dimension that is part of a cube.  You would have to delete the cube first.

I do not know how it works in PAW, but if you use architect and edit a dimension, there is an icon you can click that turns off the automatic sorting and saves the order of the elements as you have moved them around.

------------------------------
Scott Brown
------------------------------

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

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

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

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

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

Hi Shubho,

Just out of curiosity, did you ever try to create a net new set based on the result of that data source query? And if you did did you see the same reordering happen?

Any chance you could provide me with an example, presuming you can't share the data source based on a CSV?

Cheers,

-H

------------------------------
Hubert Heijkers
------------------------------

Original Message:
Sent: Wed November 20, 2019 09:33 AM
From: Shubho Ghosal
Subject: Cannot get members to order using a field in a data source query

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

Why is the dimension order of importance? Element order typically doesn't play a major role since hierarchical sort is normally most useful. Any custom sorting is more easily accomplished using an MDX subset.

------------------------------
Ryan Clapp
------------------------------

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

This was needed for a filter which I was using on the main view. I needed the filter members to show in a particular order. Not sure if a "MDX subset" (is this the same as Subset as named in PA) can be used on a filter, particularly on a Cognos dashboard. Think I tried that before but it did not work but will retry. Also, if sorting is only By Name, that would not solve my problem of sorting on attribute values, unless I go with my workaround of appending the sort fields before the member name.

------------------------------
Shubho Ghosal
------------------------------

Original Message:
Sent: Wed November 20, 2019 02:08 AM
From: Ryan Clapp
Subject: Cannot get members to order using a field in a data source query

Why is the dimension order of importance? Element order typically doesn't play a major role since hierarchical sort is normally most useful. Any custom sorting is more easily accomplished using an MDX subset.

------------------------------
Ryan Clapp

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

Original Message------

This was needed for a filter which I was using on the main view. I needed the filter members to show in a particular order. Not sure if a "MDX subset" (is this the same as Subset as named in PA) can be used on a filter, particularly on a Cognos dashboard. Think I tried that before but it did not work but will retry. Also, if sorting is only By Name, that would not solve my problem of sorting on attribute values, unless I go with my workaround of appending the sort fields before the member name.

------------------------------
Shubho Ghosal
------------------------------

#PlanningAnalyticswithWatson

I don't see a way to edit the MDX for TM1 in Cognos dashboards. Does someone have a reference to how we can do this, in Cognos dashboards specifically?

------------------------------
Shubho Ghosal
------------------------------

Original Message:
Sent: Wed November 20, 2019 10:25 AM
From: Ryan Clapp
Subject: Cannot get members to order using a field in a data source query

Yes, that should work. In most cases I see we don't want or need the full dimension in a "filter". Try editing the mdx behind the filter you can change it to something like:

'''

{ORDER(

    {TM1SUBSETALL('<your dim name>')}

   , [<your dim name>].[<your attribute>]

   , BDESC

   )

}

'''

https://www.bihints.com/tm1sort_tm1sortbyindex_and_order

Original Message------

This was needed for a filter which I was using on the main view. I needed the filter members to show in a particular order. Not sure if a "MDX subset" (is this the same as Subset as named in PA) can be used on a filter, particularly on a Cognos dashboard. Think I tried that before but it did not work but will retry. Also, if sorting is only By Name, that would not solve my problem of sorting on attribute values, unless I go with my workaround of appending the sort fields before the member name.

------------------------------
Shubho Ghosal
------------------------------

#PlanningAnalyticswithWatson

Original Message------

I don't see a way to edit the MDX for TM1 in Cognos dashboards. Does someone have a reference to how we can do this, in Cognos dashboards specifically?

------------------------------
Shubho Ghosal
------------------------------

#PlanningAnalyticswithWatson

Original Message------

This was needed for a filter which I was using on the main view. I needed the filter members to show in a particular order. Not sure if a "MDX subset" (is this the same as Subset as named in PA) can be used on a filter, particularly on a Cognos dashboard. Think I tried that before but it did not work but will retry. Also, if sorting is only By Name, that would not solve my problem of sorting on attribute values, unless I go with my workaround of appending the sort fields before the member name.

------------------------------
Shubho Ghosal
------------------------------

#PlanningAnalyticswithWatson