TM1 Forum

Hi all,

Hope the following makes sense

Just looking for some design advice before proceeding with the actual work.

I have created a journal template in TM1 (websheet) which includes lots of validation.
There are 4 parts to the organisations chart of accounts, they are Cost Centre, Detail Code, Fund and Nominal.

I can use dimix or the disabled flag attribute to test if each of these 4 account keys is valid or not

However I also want to test if the codes can be used in combination, so are valid 4 part keys – i.e. are the relationships valid or not (this can be done outside TM1 via some validation software but the users don't always make use of it and sometimes invalid entries end up in the suspense accounts or are rejected by the general ledger).

The account relationship is between the nominals and the other 3 keys.

What I am puzzling over is how to create the cube to hold this information.

I can use sql to link to an account relation table to populate TM1. It should be noted that this table holds the nominal in one column and in another column holds the other 3 account keys, in other words there isn’t a column for each account key, they are all in the same column. So you can’t determine if the four part combination is valid from a row in this table, you can only say for example Nominal X appears in the nominal column and in the other column Cost Centre Y appears therefore X and Y is a valid combination.

I think I have 2 options:

1) Create 3 cubes, one for Cost Centre, one for Detail and one for Fund and have 3 dimensions in each cube, the other dimensions being Nominal and a measure to flag 1 if the relationship is valid.

2) Create one cube and have the nominal and measure dimension and create a new dimension to hold Cost Centre, detail and fund all in one dimension.

In both of these options in order to determine if the four part account key is valid I will have to use logic to say if Cost centre and nominal relation flag is 1 and Detail and nominal relation flag is 1 and Fund and nominal relation flag is 1 then valid otherwise not valid.

I am thinking the first option is the best one

I would be grateful if anyone can propose anything better or comment on my options?

cheers, Mark

I think you are saying that I have 4 dimensions (A, B ,C and D).

Only certain combinations of !A, !B, !C and !D are valid.

How do I manage / track / validate this.

I'd have a dimension called Valid Code Combination that was a concatenation of the valid combinations of A, B, C and D

I'd then test against this with a Dimix( 'Valid Code Combination' , !A |!B |!C |!D )=0 during a data load and so forth.

Not sure if I'm missing the point..

Hi steve,

The problem is that the actual table where the validations are held doesn't test against A, B, C and D but tests that A is valid against B or A is valid against C or A is valid against D.

So in the TI odbc data source you would have 2 columns, one with elements from dimension A and the other column with elements from dimensions B, C and D.

So the problem becomes how can I test against a concatenation of A, B, C and D when the data held at source is not held in this way?

Steve already gave you an answer that should have put you on the right path. Given your new facts you could just create three dimensions; one with the valid AB combos, one with the valid AC combos and on with the valid AD. Then in your TI you just test the incoming record against each of these dimensions and reject if any of them don't match.

Ah! Right I get it...finally.

And yes that would obviously work perfectly.

thanks all!

Depends on the volume of data but I would still be inclined to merge my AB, AC, AD combos into one long ABCD valid combination dimension. Then I would only have one test to perform in my data load.

Also if I am allowing direct entry to my data cube for budget purpose for example, then having the valid ABCD combo would make it very easy to validate / mask with rules the input space.

Hi Steve,

Yes I would tend to agree with you but in my original post I didn’t want to burden you all with the full story, in my case it is better to go with 3 concatenated dimensions instead of 1 because for the detail code dimension I basically need to hold the invalid combinations and not the valid ones because detail codes get auto created if they don’t already exist as a relationship, unless they exist in the relationship table and are disabled, in which case they are invalid.

So given all the rules around how journal validation works in this case the 3 dimensions will work for my purposes. So the Cost Centre and Fund relationship dimension will hold valid relationships but the detail code dimension will hold invalid relationships, effectively unless the detail nominal relationship is flagged disabled in the relationship table any detail code can be used with any nominal meaning that dimension would be way way bigger than the dimension that just held the invalid relationships.

But your and Tom’s advice has set me on the right path because I had got it into my head that I would need to create cubes for this! So big thanks to you both.

Here is another approach, taking Steve and Tomok's advice:

NOTE: this only works 100% if the keys for B, C and D are and always will be mutually exclusive.
One dimension:
Three consolidation AB, AC, AD.
For every valid combination of AB, add that component to consolidation AB, etc for AC and AD.
Then, instead of using DIMIX, use ELISCOMP or ELISANC to test. The advantage is a single dimension to manage for testing.

If processing a large number of records, may want to test DIMIX vs the two functions I've mentioned for performance.