TM1 Forum

Hi

I want to read all attribute values from the consolidated members of a Named Hierarchy.

I understand that the only way to create a source view for a TI process that references Named Hierarchies in one or more dimensions is to use an MDX View.

I can generate the MDX easily enough to show this in PAX. However, PAX subtly modifies the MDX behind the scenes. As well as adding a HEAD 500, it is changing the ON ROWS to ON 0. If I try ON 1, it again gets changed to on 0. In other words, if you try to put all Hierarchies/Dimensions ON ROWS, PAX is changing this to ON COLUMNS. However, even though it changes the MDX to say ON COLUMNS (ON 0), when it displays the data it does show it on rows. There is probably some fix in PAX to adjust for this since in Excel you will potentially run out of columns, way before you run out of rows.

If I use TI CreateViewByMDX to create an MDX View using the same MDX as in PAX, and try to use this as a Data Source in TI, then the fix to get the data to rows is not happening, everything is on columns which is useless. I get every member of the Named Hierarchy crossed with every Attribute on columns. If I try changing the ON 0 to ON 1, the MDX fails and the view is not created.

The usual way around this with any other cube is to use a measures dimension but that is not possible with an }ElementAttributes cube since the }ElementAttributes dimension is the last dimension in the cube

I want to use a view so that I get the benefit of zero suppression.

At present it seems that the only options I have are :

a) Modify the process to call a sub process which passes in each Attribute in turn. The sub process can then create an MDX View with the single Attribute on columns and the subset of the Named Hierarchy on rows. Then I can read in values from this view and get the benefit of zero suppression from the MDX View. However, that seems a little tedious.

b) Read every consolidated member of the Named Hierarchy using an MDX Subset which I can use as a data source, and then loop through all attributes and CellGet the value. This will work but I won't get any benefit from zero suppression.

Is there another way?

Regards

Paul Simon

Sorry but I don't understand what isn't working with ViewCreateByMDX. Surely there you can put the hierarchy on rows or columns (whichever you like) and the attributes likewise. As long as you have a hierachy on axis 0 then the MDX is OK and there shouldn't be an issue?!

Even if there is some issue with MDX on an attribute cube what's wrong with assigning the hierachy as a subset data source to the TI and simply looping on the attributes dimension? Can't be any real null supression benefit on a 2D cube ...

Hi Lotsaram

The problem with having the hierarchy or ElementAttributes on columns is that you then have a variable number of columns coming in to the TI. I want to create something re-usuable so it can be used against any dimension hierarchy all of which can have different numbers of attributes. So I want to see something that is the same as my existing routines that already work on a standard dimension, ie just the columns

| Element | Attribute | Value |

That then gives me something generic that I can process on the Data Tab. As the view is zero suppressed it will come through efficiently.

If I just read in the subset and then do a loop through all attributes I will end up reading a lot more data than I need to. Our dimensions have a number of defunct codes that have few attribute values. Not all attributes are relevant to all levels. Reading zero/blank values is probably not too much of an issue on a few thousand elements but that may be different on 100,000 with 20 or so attributes.

I can get zero suppression if I put just one attribute on columns and have the hierarchy on rows, while still having the same set of uniform columns as above. However, that means that I can only process one attribute at a time. I will need to do some experiments to work out which is more efficient.

This just seems like another area that IBM have not yet fully addressed on hierarchies.

Regards

Paul Simon

paulsimon wrote: ↑Mon Sep 09, 2019 12:53 pm The problem with having the hierarchy or ElementAttributes on columns is that you then have a variable number of columns coming in to the TI. I want to create something re-usuable so it can be used against any dimension hierarchy all of which can have different numbers of attributes. So I want to see something that is the same as my existing routines that already work on a standard dimension, ie just the columns

I don't understand again. Although the view you create with MDX may have rows and columns, even nested rows and columns. When TI reads the view it still treats it as a view extract. i.e. it will always be in the shape
element ¦ attribute ¦ value
... so having only 3 columns and therefore perfectly suitable for a generic TI.

What am I missing about what you are trying to do?

Hi Lotsaram

Maybe it is because we are on 2.0.5 and you are on a later version, but that is not what is happening. It is reading the view with columns across, and not reading it as though it was a ViewExtract.

Regards

Paul Simon

Hi Paul,

If you could provide a PRO file and some dummy data / dimensions / attribute values ?
I can run some tests with the code on a 2.0.8 installation.

Hi Wim

Thanks for the offer but we should have our own test server with 2.0.8 up in a week or so.

I have referred the issue over to IBM.

Regards

Paul Simon

Hi

Just an update on this. We have now got data coming in the correct columns. However, the problem is now that any text attribute values are being read in as numbers instead of as text. IBM have re-produced this and are investigating.

Regards

Paul Simon