Ranking Cube Methodology

[garry cook]

Been having a think about how to do something and come up with a couple of options but open to suggestion if anyone can think of a quicker/more efficient way of completing this problem given the uselessness of MAX functions in current version (9.0 / 9.1).

In essence, I have consolidation A which in turn has 3 consolidations (B, C, D) under it with values as follows -

A 1000
B 300
C 150
D 550

I need to transfer this to a seperate 2 dim cube (dim above and another with three elements for Rank 1, 2 & 3) which ranks the data by numerical order so that in this cube it looks something like this -

Rank 1 Rank 2 Rank 3
A 550 300 150
B ---- 300 -----
C ---- ----- 150
D 550 ----- -----

Obviously in reality it's far more complex but that's the fundamental root of the problem.

I've debated a few ways and the best I've come up with so far is to TI run an ELCOMPN on A to get no. children, SubsetCreatebyMDX against the .Children to sort the data numerically and then run through a while loop using SUBNM against this new MDX created subset to load to each rank in order (whilst deleting generated sub each run and all the other normal malarky), etc.

Only problem is that it's a large, complicated cube it's pulling data from and a massive data set it's processing. It's not the fastest process in the world, even when dumping the data into a trimmed down staging post cube.

Any other simpler ideas? Been through the though process of IF/While/etc looping (yuk), Excel VBA based manipulation (double yuk) and a few even more ridiculous thought processes and best idea so far seems to be the MDX basis.

Anyone got any simpler / more efficient thoughts on this?

TIA

[lotsaram]

Hi Garry,

Have done this before for a large retailer that had the requirement to do store rankings by:
- area, region, total
- week, MTD, QTD, STD, YTD
- department, total store
with change in rank delta vs last year, last month last season and delta of the weekly or MTD rank vs the STD or YTD.

An individual ranking report it can be achieved easily with an active form but to do comparative rankings and ranking deltas the rankings need to be stored in a separate cube. This was all achieved via TI running MDX to generate subsets of store rank for specific time period / department / region combinations, then process the subset to populate the rank cube. Its a bit distant now but I think the design was a simple rank dimension, flat time dimension, string measure to hold the store location code, rank was obviously from the rank dimension and a rule driven numerical sales measure fed from the string measure to pull the sales value from the mother cube into the ranking cube. In the end it was relatively elegant, fully automated and exactly as per the customers requirements.

[MSidat]

Hi Garry,

Could you not export out your relevant view into a flatfile ensuring that it flat files out 3 columns :

1. A Concatenanted Value which is - Consolidation Name | Value (Value may have to be padded out with zero's to ensure all Values are in the same format and length)
2. Element Name
3. Value

You can then utilise a one liner batch script to sort this file by the first column.

You then run your second Ti to load this file in order using a global variable to track when your new data line has a different consolidation than the last one so you know when to restart your Rank Order which is also kept a track of using a global variable.

Did that make sense??

Regards,
Mo

[mce]

Similar to the approach MSidat has mentioned, you can export data to a relational DB using a TI process and then load it back to TM1 using an SQL that pre-sorts the data based on amount and that gives a rank number so that you can load it into a cube that has ranking dimension. Obviously this approach could be feasable and efficient only if your data volume is not too big.

I did not give much thought about it, but I guess it would be really difficult to deliver such a requirement in a very large cube that is being incremenetally updated for real time reporting.

Utilizing ranking functionality in front end tools might help to some degree, such as ranking facility in Cognos 8 BI Analysis Studio.

[garry cook]

Well, for reference, built it along the lines of original idea with a temporary storage cube being utilised then running through a TI that generated a Topcount MDX statement against it then loaded to the new cube based on a While loop counter to the subset elements against incrementally increasing indexing.

End result was surprisingly fast (around 3 mins to process around 300,000 elements) although I did run into an issue where the TI would die claiming it couldn't write to the log file after a certain number of MDX creations. Off the top of my head, it was around 150,000 MDX subset creation and deletions it ran through before it died (althought that's a very rough guess). Seemed strange to me - even incrememnted the name of the subset but still had same result.

Still, re-run the TI in a chore 8 times using different subsets of the data I was trying to rank and ran fine so got a solution that's pretty quick and effective but I still have no idea why it was dying after a set number of MDX creation / deletions.

For reference, I know it's a set number because it died on exactly the same element each time it ran. When I re-selected the view with one indexed element further down on the dimension being queried, it stopped one element further in the chain.

Very strange but still, the final workaround is usable so musn't grumble.

[tsw]

Just wanted to add another option since I recently had a need to do this.

Similar to mce's suggestion, using relational DB's rank works.. but I wanted to find another possibility so I tried using R
I'm new to R, but it looks like a wonderful framework and programming language to do some analytic work.

Using TI:
- export to csv
- rank using R
- import back into TM1