TM1 Forum

Hi all,

I'm trying to overwrite the value at Consolidation for an element.

The member ID (part of measure dimension) is accumulating the values of the ID's for the number of occurrences for a member and showing up in consolidation which is incorrect. The data is being loaded from ODBC through TI process. I used the 'Accumulate Values' in the load cube process instead of 'Store Values' as I needed other $ amount values in the measure dimension to be summed up and displayed in the cube. Since Member ID is also part of the measure dimension, it is also getting summed up.

To overcome this issue, I tried few alternatives such as -
1) Manipulating the CellPutN(CellGetN) code that generates in the Data tab to clear off the current value for the element (CellGetN(0, cubename, el1, el2...)) and then passing the value to CellPutN (eg. CellPutN(CellGetN(0, cubename, el1, el2)+MEM_ID, cubname, el1, e2));).
By doing this, the value still gets accumulated at the consolidated level.

2) I tried adding in the value at C level through rules but it takes so much time to just build the cube view.

3) In TI, I tried Ellev and wrote an IF condition to insert CellPutN(CellGetN)) value if the level is 1 (Consolidated).

These are not helping me. Could you please guide me as to what I'm missing? I'd appreciate your time and help.

You would probably change the type of your 'member ID' element into String and adjust your process accordingly. There would be no summation then.

Alternatively, you could create a member dimension in your destination cube instead of having it in the measures dimension

Thanks Dan and Marcus for your replies.

Marcus, I'm keeping the Member ID as Simple (numeric) instead of String because my requirement is to show the Member ID details as well in the view when consolidation from all the dimensions are selected. By making it String, it wont display the value at consolidation. Hence, Simple.

Dan, I preferred to make the Member ID a part of measure dimension because I have to drill from the ID at source cube to a cube that stores member's other demographic details (repository cube).

I tried another alternative where member's static details such as first name, last name, gender, etc..including the ID is stored against the member number in a cube (that acts as a repository). From Source cube if ID is drilled, it will open the repository cube to show ID and other demographic details. Eg shown below - Then from the original cube, I wrote a rule to pull data for Member ID at C level for all the members from that repository cube using DB.
Eg. [Member ID] =C: DB('repository cube', !Mem No.,, 'Mem ID.');

But when I save the rule, I get error for the !Mem No. (highlighted in bold) as incorrect string expression. In both the cubes, the property of this element is Simple.

Could you please let me know if the approach I used is correct and how to resolve this issue?

Why do you think that adding a MemberID dimension to the cube would stop you from being able to drill into details behind the member ID? That's exactly the approach I would take.

My understanding is that Drill works from a cell. If I make the ID as dimension I would not be able to drill from the element of that dimension. Please correct me if I'm wrong.

ViRa wrote:If I make the ID as dimension I would not be able to drill from the element of that dimension.

Wherever did you get such an idea???????? A single cell represents the intersection of one element from each dimension, correct? So, when you drill from a cell you have an element from each dimension available to you to pass to the drill TI process so you would pass the Member ID to the drill process.

Yes I agree Tom. But I dont understand how would that help resolve the issue of the ID values being consolidated at C level. If I add ID as a dimension it further increases the sparsity of the cube because I have to arrive at the proper ID (and other dimension elements) for that member number to retrieve the results. Since a member number can have only one ID, I felt it would be better to have it as a measure instead of dimension.

Please advice.

I was merely responding to your assertion that you can't drill into a member ID when that dimension is part of the cube where the drill originates. As to your original question you did a poor job of explaining the issue and I don't have the bandwidth necessary to figure it all out. Sorry.