Concentrating two dimensions elements together to form a unique identifier

[KaneHenderson]

Hi,

I was wondering if it is possible to design a TI Process to take two dimensions elements and concatenate them together to form a unique identifier in a new dimension that i could setup as a chore? Ideally i would like to do this using incremental loads, rather than clearing out the new UID dimensions elements as a mapping cube will be built off the back of my new dimension. Both dimensions have differing number of elements and would create a UID for every possible dimension element combination.

Additionally, i am also wondering if there is a PAX formula available that would enable me to pull down a elements name (or alias) based on it's top level hierarchal parent or an attribute or both! I'm currently using SUBSN and DIMNM but both aren't quite what i'm after.

Any assistance would be greatly appreciated!

Thanks in advance,
Kane

[ascheevel]

Am I understanding correct that you want to populate a new dim with the cartesian product of two source dims? Consider dim1 and dim2 below, the cartesian product would be what's listed in dim3. Is that what you're looking for?

Code: Select all

Dim1:
A
B
C

Dim2:
D
E
F


Dim3:
AD
AE
AF
BD
BE
BF
CD
CE
CF

If so, what you seek could be accomplished with loops in a TI. You also could accomplish this with a cube that has 3 dims: dim1, dim2, and a measure dim with a single string element. You'd then set a rule in the cube for your string measure to be the concatenation of !dim1 and !dim2. You'd then write a TI that uses a view from the cube as source and updates dim3 based on the cube value. See uploaded screenshot for cube example.

cartesian_cube.png (81.31 KiB) Viewed 1746 times

I'm not following what you're trying to do in your second question. Post code, pictures, or both.

[lotsaram]

It might help if you can explain why you need to do this. Do you need to flatten data and export using the key to another system?

[KaneHenderson]

Thanks for answering my question @ascheevel, very helpful and exactly the solution i'm looking for - great!

@Lotsaram, to answer your question and i am building a mechanism that will hold mappings that will be exported from TM1 to a BI reporting solution. My issue is that the mappings table is entirely 1-1, in some places it's 1-many and by forming a UID i will be able to form as many mapping permutations as i may require!

As for the PAX formula, i was looking for a formula that would look at the attribute of an element and pull down the element name if the attribute field isn't empty. I know i could do this using an IF statement in Excel, but i was worried about performance issues... I'm using SUBSN and DIMNM currently, with filters applied in Excel, but would like something a little less clunky! TIA!!

[KaneHenderson]

I've found a solution for my PAfE report, but doesn't enable me to bring the PafE report into PAW. Using TM1ELLIST in collaboration with a MDX statement to make the subset dynamic. Is anyone aware of a function that can be used with PAW to achieve a similar objective to TM1ELLIST?