TI -Using single column to aling fact to multiple dimensions

[Ogram]

Hey and a lovely wintery morning to you all.

I'm working on an interesting case implementing a budgeting and planning solution for a rather fractured company that has several subsidiaries. Due to inorganic growth, the ERP field is akin to minefield but that's not my problem at this stage. I'm currently at the early phase on implementation, doing the first few tests using actual fact and dimensional data.

This is a TM1 Cloud-based solution so using .csvs to transfer the factual data is a must - I cannot connect directly to the DBs to run queries any way I'd wish so I need to look for a solution on TM1 end of things.

The problem (finally) is that the fact data I received has a single column that is used to map the data to multiple dimensions. Better yet, the values in this column follow no rhyme, reason or logic in any such a way I could use a specific string to map it to a specific dimension and element during the import process (Eg. 'LegalOrg = SUBST(Column_1,4,3);'). Instead, each value in this column corresponds to a specific configuration of elements across multiple dimensions. I have this mapping data available but I'm unsure as to how I should proceed. I could of course add the values in the column in question as lowest level elements to each of the dimensions but we're talking about several hundred elements which would appear in each of the dimensions. While there'd be some perverse fun to be had watching TM1 struggle with the sheer volume of cells and sparse data, I'd rather find a solution that's actually usable.

Basic layout of the fact data:

Mapping_key	Account	Date	Value
ABC111	300100	12 2014	450000
BCD222	300100	12 2014	150000

Mapping info:

Mapping_key	Organisation DIM	Function DIM	Business Line DIM
ABC111	Example Norway LTD	Marketing & Sales	Local Contracting
BCD222	Example Norway LTD	Fixed Production Overheads I	Turnkey Contracting

One possible approach that came to my mind is to create a dimension out of this mapping data with the mapping key as the element and the dimensions as attributes but can I use ATTRS to read an element's attribute from a dimension that not directly involved in the import process (= not included in the target cube)?

Any ideas or suggestions as to how I should proceed would be greatly appreciated.

Regards,
Jussi

[Alan Kirk]

Hey and a lovely wintery morning to you all.

It's a summer evening here, and the humidity is making Hell seem temperate and pleasant by comparison.

One possible approach that came to my mind is to create a dimension out of this mapping data with the mapping key as the element and the dimensions as attributes but can I use ATTRS to read an element's attribute from a dimension that not directly involved in the import process (= not included in the target cube)?

We have a similar issue here in that there are multiple GL systems within the group, all of which have to be mapped into one common "master" GL system. I do believe that you're on the right track but I'm not sure that I'd use a dimension and attributes. I've used a mapping cube for this, but I suppose a dimension and attributes would work; I'd be a bit wary about the speed of reading attributes compared to the speed of reading data values though. I haven't done any formal tests but have found that reading (and worse updating) very large numbers of attributes can be painful.

You could have a cube made up of two dimensions (possibly more if you need to specify different sources, but let's say two for now).

Dimension 1 has elements representing your key values such as ABC111.
Dimension 2 consists of the names of your dimensions such as, in this case Organisation, Function and Business Line. These will of course be S elements.

So at the intersection of element ABC111 and Organisation you find Example Norway LTD. At the intersection of element ABC111 and Function you find Marketing & Sales, and so on.

So in your data tab you extract the element names into variables by reading them from the mapping cube. This is just air code but it'll give you the idea:

Code: Select all

s_Organisation = CellGetS('MappingCube', Mapping_Key, 'Organisation');
# The above will now contain the value Example Norway LTD

s_Function = CellGetS('MappingCube', Mapping_Key, 'Function');

and so on. Having retrieved these values you can then simply use:

Code: Select all

CellPutN(value, 'Target Cube', s_Organisation, s_Function, s_BusinessLine, etc);

(Aggregating if necessary obviously.)

If you go down this path though I do strongly recommend that you see whether you can get a regular data dump of the mapping table and write a TI to update it. Maintaining those things manually can be a huge time suck.

[Ogram]

One possible approach that came to my mind is to create a dimension out of this mapping data with the mapping key as the element and the dimensions as attributes but can I use ATTRS to read an element's attribute from a dimension that not directly involved in the import process (= not included in the target cube)?

We have a similar issue here in that there are multiple GL systems within the group, all of which have to be mapped into one common "master" GL system. I do believe that you're on the right track but I'm not sure that I'd use a dimension and attributes. I've used a mapping cube for this, but I suppose a dimension and attributes would work; I'd be a bit wary about the speed of reading attributes compared to the speed of reading data values though. I haven't done any formal tests but have found that reading (and worse updating) very large numbers of attributes can be painful.

*snip*

If you go down this path though I do strongly recommend that you see whether you can get a regular data dump of the mapping table and write a TI to update it. Maintaining those things manually can be a huge time suck.

Thanks Alan, I got the gist of it. The process should be simple enough but I haven't had the opportunity to implement anything like this before so I was unsure as to how to proceed. We are talking about monthly (and month-level figures) uploads so I don't think speed will be an issue even when reading attributes from a dimension but I'll take the cube route nonetheless. Keeping the mapping cube up to date won't be a problem, I'll have a process to update it monthly before the actuals are imported and I'll add a view to the deployed maint. application so the business (the select few users) can manually make changes if need be.

Hey and a lovely wintery morning to you all.

It's a summer evening here, and the humidity is making Hell seem temperate and pleasant by comparison.

So on the average we're both ok.