TM1 Forum

Hello everyone,

I am using a cube view as datasource in TI.
I am doing some calculation where I need data in descending order, so the source cube data should flow in data & Metadata tabs in descending order.
I tried using 'Filter option available in cube viewer, but it works only in the cube viewer as 'Filter'option requires only 1 row dimension & column dimension,whereas in TI, the cube view has multiple row dimensions.
I also tried creating dynamic subset using ORDER function in MDX query. Still no desired results!

Can anyone please correct if I am going wrong anywhere. Can we get sorted cube view as datasource in TI?
Or is there any work around for such requirement.
'
Thanks in advance.

When trying to do anything like this (particularly when I need to sort by date) I find the easiest way is often too leverage the power of a relational database and have a TI export cube data to a temporary table then have your load/calc TI use that as a datasource before deleting the table.
You can also use MDX and create your own loop on smaller data sets.

Another way of doing things like that is to build your own While ... End loops on respective TI tabs for those dims, where you wish to have a certain order on.
In that case you have to use a single C-element for these dims in your view. And you need to exclude C-elements in all other dims that are aren't managed in this way.

The more complex a model becomes and especially with large cubes being rule calculated only, the more it can make sense to move into this direction even without the need of a specific sorting. This is simply much faster than traditional processing with zero suppressed views (because you can build in logically checks and skip processing whenever possible).

hello123 wrote:Can we get sorted cube view as datasource in TI?

No. Your best option is going to be exporting to an SQL table where you can access using ORDER BY. TM1 doesn't have this.

Are you sorting only on one element/measure or sorting on a value within one or more dimensions/elements?
e.g. If I have an employee cube with 6 dimensions, I may only be sorting by employee based or their salary vs sorting on employees within a cost centre within a year etc.

tomok wrote:

hello123 wrote:Can we get sorted cube view as datasource in TI?

No. Your best option is going to be exporting to an SQL table where you can access using ORDER BY. TM1 doesn't have this.

Or, export to a text file and use some DOS commands. Might be much quicker than going to SQL.
But as always, there are pros and cons to both solutions.

Someone has done most of the hard work. Here's a template approach to achieving this.
https://everanalytics.wordpress.com/201 ... grator-ti/