Rule needed for cubes with different dimensionality..

[mr j]

Hi,

Yes, many similar topics do exist.. I'd need to write a rule to pull data from a source cube with three dimensions to a target with six. Two of the target dimensions have elements which are defined in the attributes of one of the source dimensions. One target dimension is Measures. Measure A should be populated with data in the intersections of the source IF measure B is flagged.

If it is NOT flagged a whole different set of rules should take over to populate measure A. This part works already on it's own, but there's a need to put these together. The target areas would not be overlapping. So I'm thinking if this would work
- check the flag of measure B
- if flagged populate according to mentioned attributes (the rule I'm asking help for..)
- if not flagged populate according to following rows in the rules (function "CONTINUE"?)

Target cube:
- Measures (populate measure A if B is flagged (this could be numeric or string whichever suits the rule best))
- Cost center
- Grouping A
- Accounts
- Grouping B
- Months

Source cube:
- Months
- Cost center (attributes holding exactly the Grouping A and Grouping B elements above)
- Accounts

How to write the rule..? Please..

[rmackenzie]

if flagged populate according to mentioned attributes (the rule I'm asking help for..)

Which dimension(s) contain the attributes that map back to the source cube and what are the attribute names? Account or Cost Centre, or both?

[mr j]

Yes, sorry,

Dimension "Cost center" is the same in both cubes. It has attributes say "CC_Group A" and "CC_Group B" which contain a 'mapping' to elements in dimensions "Grouping A" and "Grouping B" correspondingly.

[rmackenzie]

It has attributes say "CC_Group A" and "CC_Group B" which contain a 'mapping' to elements in dimensions "Grouping A" and "Grouping B" correspondingly.

If there are only a few combinations then you could have a few rules doing the pull in the target cube with the left-hand side of each rule targeting each combination. Otherwise you should have a TI process a view of the Source cube and populate the intersections in the Target cube according to the mapping logic. Rules aren't what you want in this situation. What is the purpose of having two separate cubes?

[mr j]

No, there’s several and as there’s possibility the mapping will change over time it’s better to maintain it in attributes than in rules or TI process code.

How to write this so you bear with me.. (I edited my original post at some point today possibly before the first reply, but I’m not sure. This I mention later below.)

So,
Source data is defined only by Account and Cost Center despite the Groupings A & B in attributes. I’ll come to this later.

Initially the Source data is divided to 6 cubes with dimensions similar to here mentioned Target. In these cubes rules utilize Accounts or Account consolidations & Cost Center or Cost center consolidations in source to determine the allocation to Grouping A and B. Several cubes are needed as these rules are somewhat overlapping and the source data is split manually (or calculated by factors) to several Cost Centers different from the Source allocation and to combinations of Groupings A and B. And, of course, transfer accounts different to the Source…

In the end the data from these 6 cubes is combined to the cube I referred to in my original as Target. All this so far works ok with Target rules where the overlapping is handled with summing up etc.

What I need is to pull from the original Source cube all the remaining data to the Target. That is the data that is not transferred to the 6 other cubes for manual allocation ie. not in the scope of the accounts defined in any of the rules in these 6 cubes. The ‘mapping’ in Cost Center attributes CC_Grouping A and B only applies to this remaining data to specify where to put it in the Target cube.

So… what I was thinking is to flag the data that does not go through the 6 cubes and based on this flag and the attributes make another couple of magic rows on top of what works fine now in Target rules to pull it in.. (At some point today I edited my original post to include this requirement for flag, not sure if you read it)

What comes to using TI processes, given the above the user would need to keep track in which order to run the processes. Yes, it would be a business user doing this. Also, there’s a requirement to have a possibility to make changes in the allocation rules which would apply to history data. To not need to have control over of what’s happening I think rules would suit better. Then again, given my novice level I could be totally wrong.

Or… maybe use TI only for the remainder data as you suggest. There’s the maintenance problem in case the Groupings are changed, but it's a problem anyhow in all the rules too.

[tomok]

Initially the Source data is divided to 6 cubes with dimensions similar to here mentioned Target. In these cubes rules utilize Accounts or Account consolidations & Cost Center or Cost center consolidations in source to determine the allocation to Grouping A and B. Several cubes are needed as these rules are somewhat overlapping and the source data is split manually (or calculated by factors) to several Cost Centers different from the Source allocation and to combinations of Groupings A and B. And, of course, transfer accounts different to the Source…

In the end the data from these 6 cubes is combined to the cube I referred to in my original as Target. All this so far works ok with Target rules where the overlapping is handled with summing up etc.

Forgive me for being blunt but this is a really bad design. While you CAN make this overlapping work with your design, it's going to always be extremely confusing and forget about any transparency or traceablity. What about when you're gone and the next poor schmuck has to take over? You need to segregate the data in the Target by where it's coming from or what process it's been through with an additional dimension, like "Source" or "Process", or whatever. Then you don't have to worry as much about the overlapping and write the rules in an organized and easy to understand manner.

As to your original question, I don't like hypothetical examples, they're too confusing, with the "Measure A", "Element B", etc. Come back with some specific stuff and I'll see what I can help with.

[mr j]

All feedback appreciated, I'm a humble newbie to this.

For the schmuck taking over and the poor end user;
I've applied views to the Source cube corresponding to what's in the rules in the 6 intermediate cubes so the user can check whether there's all the data to be processed manually that should be there. Also, in each of these cubes there's a measure dimension with a measure for the data brought in by the rules just for reference plus a measure for the manual entry which again is driven to Target cube. It's not nice, but it's there.
The rules are not actually very long and are written in an organized manner with #region applied. I wouldn't be worried about the traceability of rules if the poor person taking over has even slightly more experience of this than I do.

I'm sorry if the examples don't seem concrete enough, but if I told you exactly what they are you would be more puzzled as I bet you don't speak the language they are written in ;D
In the measure dimension there's basically only two (or three if needed for the flag)
- Measure A for the original data mentioned above. Call it "Original".
- Measure B for the manual entry. Let's call it "Share" as it's manually split to Cost centers etc.
- Possibly 'Measure' C for the flag to point whether this data in question is to be processed manually or to be transferred to Target 'direct' as described.

Groupings are two different kinds of let's say product groupings with less than 10 elements. The element names actually are just something we've agreed upon with business specific descriptions, so it won't make a difference what we call them. Grouping A elements are say A1 to A10, B correspondingly. Maybe I understood you wrong, but this is the way I see it. I'll think this over... Now I have to run.

It probably sounds like a bad design, but it's a c**p tool, to be blunt. Coming with a Planning background, I'd already have a working app for all this without any 'coding'. Serious. Just D-Links with different running modes and sequence applied and couple of conditional D-Lists all there's to it. But, here we are.

[stephen waters]

All feedback appreciated......It probably sounds like a bad design, but it's a c**p tool, to be blunt..

A bad workman???

Out of interest, does your client ( referred to in previous posts) know how little experience and training you have had on TM1?

[mr j]

All feedback appreciated......It probably sounds like a bad design, but it's a c**p tool, to be blunt..

A bad workman???

Out of interest, does your client ( referred to in previous posts) know how little experience and training you have had on TM1?

Hint taken. My apologies for the comment quoted. Seriously.

Given the requirement to manually split data from different combinations of Cost centers and accounts at a time to the same sets (what I call overlapping, sorry my English) of accounts, cost centers and Groupings A & B I thought it would be more clear for the user to have separate cubes. With the data to be split visible as reference. Then put it back together in one cube as required. Apparently there's better ideas I'm not aware of.

[tomok]

What you are trying to do is about as clear as mud to me, perhaps it's your English or just that I'm stupid. For that reason I'm out (for all the Shark Tank fans out there ).

[mr j]

All good reasons to get out.

Still, I attached a very shortened version of the spec I'm looking at. The idea is first to have a solution to pull the data in the Source columns to Target side. For example, the two first rows cc 1000 and a consolidation of accounts 3000_C hold a sum that is moved to both cc's on the Target side for manual splitting between them two. The same logic for the rest of the rows.
"_C" means it's a consolidation (both in cost centers and accounts). The overlapping I mentioned is mentioned in the sheet. There's a need to manually split different source data to the same targets. That's why I'm thinking several cubes to do the splitting. Also other combinations exist.
Then in a final cube pull together everything. Source - Target splitting cubes - Final cube.

The original question was related to how to pull in Final cube the data that's not recognized by any of the mapping rules, but Tomok questioned the whole idea of having several cubes as "a bad design". I don't know if the attached still gives any clearer idea of what's needed, but I'd be interested to know what would be a 'good' design in this case.

[rmackenzie]

Tomok questioned the whole idea of having several cubes as "a bad design"

I don't think the multiple cube design was specifically tomok's target. In principle, having several cubes splitting out the logic of a set of allocations isn't a bad design, per se. If there are different analysis dimensions for each allocation then having multiple cubes may be the preferred method, rather than one big cube where some dimensions are redundant depending on which slice was being viewed.

There's normally a set of interconnected mistakes that have to happen to arrive at a bad design and having multiple cubes for the same data-set doesn't just catapult you there. Having said that, I'm not too clear what the 'over-lapping' piece is all about. Also, I agree with tomok in that it is hard to give feedback when the example is presented in a very generic way.

The original question was related to how to pull in Final cube the data that's not recognized by any of the mapping rules

It strikes me that the problem is not multiple cubes, but cubes of different dimensionality. In your original post you are trying to pull data into a 6-d cube from a 3-d cube based on attribute data in the Cost Center dimension. If your source cube was the same shape as the target cube then your rule could possibly be:

Code: Select all

['Measure A'] = N:
  # check flag
  IF ( !Measure B = 1,
  # pull if 1
  DB ( 'Source', 
    !Measures, 
    !Cost Center, 
    ATTRS ( 'Cost Center', !Cost Center, 'CC_Group A' ), 
    !Accounts, 
    ATTRS ( 'Cost Center', !Cost Center, 'CC_Group B' ),
    !Months ),
  # otherwise, continue to next rule
  CONTINUE
);

Why does the source cube only have 3 dimensions? <edit> To be specific why 3d to 6d (as 6d to 3d is a different proposition)</edit>