Input cube to summary cube rule???

[ADW]

I have the following cube which I want users to input data. To make it easier for them I have created a cube with many measures rather than many dimensions.

'2011' = Year, '8300100' = Cost Centre.

Data input cube.png (46.66 KiB) Viewed 10356 times

I want this data to summarize in the following cube:

'Forecast' = Version, '2011' = Year, '8300100' = Cost Centre, '4' = Employee Level, Months, and 'TEST' is where the summation of 'Effect' from the input cube should appear.

Data summary cube.png (32.43 KiB) Viewed 10356 times

In the view shown on the summary cube; Roy Keane effect of 1 should be showing in TEST for AP 8.

I am really struggling to get my head round how to sum this information up using a rule, rather than a TI process which I think would be easy to write.

Can anyone offer me any advice, whether it's that I'm going completely the wrong way about gathering the raw information, or whether you can help with the rule side of things.

Cheers.

[Christopher Kernahan]

I don't think you can do it with a rule, based on the current cube design.

The reason I say this is that you are trying to obtain values based on the month in which a position's 'Effect' has an affect on the total, however you are storing all this data in a measure which makes it difficult to obtain the proper consolidated value using a rule.

To consolidate for 'AP 8' you need to be able to say "consolidate all Headcount Positions with an Effective AP that is less than or equal to August", but in a rule there is no way to scan through all of the Headcount Position elements and only grab those which match this description.

You could create system consolidations in the input cube called i.e. 'AP 8' and use TI to add Headcount Positions that match the condition "If Effect is less than or equal to 8"....however;

from the looks of it, you also need to segregate your data based on Employee Level, so using system consolidations means you would need i.e. 'AP 8 - Level 1', 'AP 8 - Level 4' etc.

...so I think you either need this dimensionality in your input cube or you need to use TI to put it into the reporting cube.

[Duncan P]

One thing that you could do is to create an intermediate cube with the dimensions of the target and also the row dimension from your input cube.

Write a rule in the intermediate cube with statements of the form :-

Code: Select all

['data_measure'] = N: IF( !dim1 @= DB('input_cube', !row_dim, 'dim1_measure' ) & !dim2 @= DB('input_cube', !row_dim, 'dim2_measure' ) & !dim3 @= DB('input_cube', !row_dim, 'dim3_measure' ), DB('input_cube', !row_dim, 'data_measure'), 0 );

Then all you need to do is pull the "All" member from the rows dimension into your target cube.

[tomshackell]

You can do it using rules but you need to introduce an extra cube in the middle. I'll illustrate with a rather simpler example, imagine you have this source cube:

Sales.jpg (9.98 KiB) Viewed 10344 times

And you want the following summary

Summary.jpg (7.42 KiB) Viewed 10344 times

You can do this by creating an 'intermediate' cube to go in the middle. This should have all the same dimensions as the 'Summary' cube plus the 'Sales Number' dimension.

intermediate.jpg (12.88 KiB) Viewed 10344 times

The intermediate cube should have rules:

Code: Select all

['Revenue'] = N: IF (DB('Sales', !Sales Number, 'Product') @<> !Products, CONTINUE,
      DB('Sales', !Sales Number, 'Revenue'));

Now you can take the 'All Sales' slice of your intermediate cube and copy into your summary cube:

Code: Select all

['Revenue'] = N: DB('Intermediate', !Products, 'All Sales', 'Revenue');

Doing this with the correct feeders is more tricky ... if your example is small enough you can do it without using SKIPCHECK & FEEDERS. If you do need to use SKIPCHECK & FEEDERS the main thing is that you can use a conditional feeder from the 'Sales' cube to the 'Intermediate' cube.

Code: Select all

['Product'] => DB('Intermediate', DB('Sales', !Sales Number, 'Product'), !Sales Number, 'Revenue');

[Duncan P]

Thanks Tom. A much better explanation.

[ADW]

Brilliant!!

Thank you both for your responses! I will give the intermediate cube a go, I don't want to change the dimensions in the input cube as this will blow the inputters minds(!) and wanted to stay away from TI processes as it means the data is not truly live (doesn't it?).

Thanks again!

[Christopher Kernahan]

Hi Duncan P, tomshackell,

What do you think about this approach from a performance point of view? With an intermediate cube, ADW will have multiple IFs for each Measure cell, and then you need an additional Rule statement in the reporting cube, along with corresponding feeders.

ADW,

With regard to being 'live' using TI - no, it won't be. But you will have to weigh up the need for fully live information against performance reduction from this fully Rules based approach. How often is the data entering the Input cube? What is the longest wait a user would accept before it was transferred to the Reporting cube?

[tomshackell]

There definitely is a performance cost to having an intermediate cube, and a TI would be quicker. But as you note, a TI won't be dynamic you would need to re-run the TI to update the summary.

Our experience has been that for moderately sized models the performance is acceptable, especially if you use the conditional feeder trick. It does also use additional memory, but in general it's not much worse than the memory needed to store the summary cube due to the way TM1 stores data.

In the end the only way to really know how it performs is to try it. So I would recommend performance testing your model, with some realistic quantities of data. Of course, that's generally a good idea anyway ... whether you're using intermediate cube trickery or not

[tomok]

How about your input template? Does it have to be a cube view, like you've shown, or can it be an Excel sheet (used either thru Excel or TM1Web)? The reason I ask is that if you create it in Excel, it would be rather easy to create an input template that functions just like your example, yet still has all the necessary dimensionality so that you don't have to have the intermediate cube. As long as you're not having to use Contributor, which requires a cube view, then this would be the way I would go. You are just asking for performance issues with the examples I've seen here.

[lotsaram]

How about your input template? Does it have to be a cube view, like you've shown, or can it be an Excel sheet (used either thru Excel or TM1Web)? The reason I ask is that if you create it in Excel, it would be rather easy to create an input template that functions just like your example, yet still has all the necessary dimensionality so that you don't have to have the intermediate cube. As long as you're not having to use Contributor, which requires a cube view, then this would be the way I would go. You are just asking for performance issues with the examples I've seen here.

I agree. Unless the input absolutely has to be via Contributor/Planner then you are much better off using a single cube and formatted Excel or websheet - the end user or inputer doesn't need to know anything about the structure of the cube itself.

[ADW]

tomok,

It doesn't necessarily have to be a cube view. There will be several people adding information and I'm trying to move away from an excel file that gets messed up and broken quite regularly. I'm kind of new to all this so don't have any knowledge or experience of what an Excel or websheet is or how to use/create it. I will do some research.

On the other front I've nearly got the intermediate cube working, just having a problem with the fact that AP10 comes before AP 2 as they are stored as strings and when I change the measure to be simple (n) it throws me an error message saying AP 2 is not a number. It doesn't like: If (NUMBR(DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP')) @> NUMBR(!Month) although this seems correct looking at the help file.

[jim wood]

I agree with Tomok and Lotsaram. (I know, I know) The option they have outlined is by far the easiest to create and maintain. Think of it as an excel sheet published via your intranet that is ready only and while it maintains the look of an excel sheet it does not behave like an excel sheet. IBM have some good examples on their web site. Have a look.

I think your time will be better spent looking in to web forms. (If you have people at remotes sites TM1 web performs better over a WAN than a cube viewer, unless you are using citrix.) If however you are quite far down the line with the multi cube sollution, then complete that by all means and look at the web form afterwards as a potential replacement?

Jim.

[Christopher Kernahan]

ADW,

'AP 10' / 'AP 2' etc. are not numbers, even with the measure changed to Simple.

Why not keep the original String measure, and use a new Simple measure to hold a rule calculated value containing just the numeric component of 'AP 10'?

Effective AP => 'AP 10'

[ 'Effective AP Num' ] =
SUBST( 'Effective AP', 4, 2 );

Effective AP Num => '10'

[ADW]

Hi Christopher,

I changed the input months to just numeric (so 2 instead of AP 2).

Here is the working code for the intermiediate cube:

Code: Select all

['TEST','Forecast'] = N:  If (DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Level') @<> !Employee Level,
				CONTINUE,
				If (DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP') > ATTRN('Month', !Month, 'MonthNo'),
					CONTINUE,
					DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effect') )) ;

Just struggling through the feeders now. I have this in the input cube:

Code: Select all

FEEDERS;

#Internal


#External

['Effective AP', 'Level'] => DB('Headcount Intermediate', !Year, DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Effective AP'), !Cost Centre, DB('Headcount Adjustments', !Year, !Cost Centre, !Heacount Position Number, 'Level'), !Heacount Position Number, 'Effect') ;

Although I think I'm suffering the afformentioned performance issue as I came in today and the intermediate cube will not open. Think I may give the server a refresh and cross my fingers. It's wierd really because it worked brilliant yesterday, just today it doesn't want to know!