How to write DB function in rules if cubes have different di

[abcuser]

Hi,
in Cognos TM1 v9.5 I have two cubes which have the same members but have different dimension name.

When I use DB function in rule I get error: "Syntax error on or before: !CUBE1_MARKET_DIMENSION invalid string expression." I think the error is because dimensions from two cubes don't have the same name.

How to write a DB formula to tell TM1 server that I have two cubes with different names?

See attached file for simple sample.

Regards

[David Usherwood]

(Just posted this in IBM Developerworks - but you're much better off posting here.)

To link cubes with different dimensions, just use (say)
!Cube2_market_dimension
in your rule - assuming that's the name of the dimension in the cube the rule resides. Then matching elements will pull through.
If you need to do lookups, use an attribute. Remember to create and use the inverse attribute for the feeder. (That's why I prefer to simply use matching elements if possible.)
HTH

[abcuser]

David,
thanks a lot for answer. I changed !cube1_market_dimension with !cube2_market_dimension and error disappeared, but I get zeros for "quantity" in whole cube.

I have figured out that using formula without "N:" (and changing !cube1... to !cube2...) it displays data in cube2 at dimension level "countries" which is ok, but on countries summary level "first single top element in market dimension" has zero displayed, but I would like to have a sum of child countries. This zero overwrite at top_most_countries_level is most probably because I have removed "N:" from formula. But is it some way that I can create sum of "countries" at top_most_countries_level? So is there something like [top_most_countries_level]=sum(children[top_most_countries_level])) or some other way to get a sum to top_most_countries_level?

P.S. Are feeders really needed? If I am not mistaken there are only for performance reasons, am I right?
P.S. I have attached new picture (marked red for single_top_element in market dimension).
Regards

[lotsaram]

I changed !cube1_market_dimension with !cube2_market_dimension and error disappeared, but I get zeros for "quantity" in whole cube.

I have figured out that using formula without "N:" (and changing !cube1... to !cube2...) it displays data in cube2 at dimension level "countries" which is ok, but on countries summary level "first single top element in market dimension" has zero displayed, but I would like to have a sum of child countries. This zero overwrite at top_most_countries_level is most probably because I have removed "N:" from formula. But is it some way that I can create sum of "countries" at top_most_countries_level? So is there something like [top_most_countries_level]=sum(children[top_most_countries_level])) or some other way to get a sum to top_most_countries_level?

P.S. Are feeders really needed? If I am not mistaken there are only for performance reasons, am I right?
P.S. I have attached new picture (marked red for single_top_element in market dimension).

If you are pulling data from one cube to another cube then you need to feed from the source cube as that is where the physical data resides.

The question of whether feeders are necessary depends on cube structure, the answer is mostly yes but for very small cubes and very dense cubes it may not matter. To elaborate, although it is true that "feeders are only required for performance" the magnitude of performance gain with the skipcheck algorithm and feeders can be extreme. Without skipcheck to consolidate TM1 will check every cell where the rule applies, with skipcheck and appropriate feeders it will only add the flagged cells that contain values, if the cube is 1% dense then this will equate to a 100x performance gain or 2 orders of magnitude, if however the cube is only 0.01% dense (not uncommon in TM1 multi-dimensional OLAP models) then the performance gain will be 4 order of magnitude or 100,000 times in which case it is not really just about performance it is mandatory to have a workable model.

If your cube structure is really only 2 dimensional then probably you don't need feeders as the cube will be small or dense or both - but in most cases you do need feeders.

[abcuser]

lotsaram,
this is only very simple sample of the problem I have. Cube1 in my real system has only two dimensions and all of the cells has a data, so cube is 100% dense. Cube2 in my real system has 8 dimensions, but I have omitted them in my simple sample to simplify my problem. I do use "skipcheck;" command, but without "feeders;" command and corresponding feeders.

OK, that is about performance, but what about my main problem. Using formula:
['QUANTITY']=DB('CUBE1,!CUBE2_MARKET_DIMENSION,'QUANTITY');
(so !cube2_market_dimension and without N:) I have zeros at "single_top_element" in cube2_market_dimension. How to get data to this upper level - sum of countries?
Regards

[Alan Kirk]

lotsaram,
this is only very simple sample of the problem I have. Cube1 in my real system has only two dimensions and all of the cells has a data, so cube is 100% dense. Cube2 in my real system has 8 dimensions, but I have omitted them in my simple sample to simplify my problem. I do use "skipcheck;" command, but without "feeders;" command and corresponding feeders.

OK, that is about performance, but what about my main problem. Using formula:
['QUANTITY']=DB('CUBE1,!CUBE2_MARKET_DIMENSION,'QUANTITY');
(so !cube2_market_dimension and without N:) I have zeros at "single_top_element" in cube2_market_dimension. How to get data to this upper level - sum of countries?
Regards

If you use Skipcheck, you use feeders. If you don't want to use feeders, don't use skipcheck. There is no middle way. Not unless you want to see screwy and/or incomplete values returned, especially at consolidation level.

[lotsaram]

this is only very simple sample of the problem I have. Cube1 in my real system has only two dimensions and all of the cells has a data, so cube is 100% dense. Cube2 in my real system has 8 dimensions, but I have omitted them in my simple sample to simplify my problem. I do use "skipcheck;" command, but without "feeders;" command and corresponding feeders.

OK, that is about performance, but what about my main problem. Using formula:
['QUANTITY']=DB('CUBE1,!CUBE2_MARKET_DIMENSION,'QUANTITY');
(so !cube2_market_dimension and without N:) I have zeros at "single_top_element" in cube2_market_dimension. How to get data to this upper level - sum of countries?

OK now we are getting somewhere. You should not have omitted this information in the first place as the correct dimensionality of "cube 2" is highly relevant to the solution to your problem. (Please review the asking for assistance guidelines, if you are less familiar with the tool it won't always be obvious what is relevant and what isn't so unless you have disclosure constraints best to include (or at least obfuscate) full code references, cube design & dimensionality, software version, etc, etc.)

If your quantity fact data is in a 2 dimensional cube (which itself seems strange as I would expect a quantity measure to be time bound but that aside ...) but you want to analyse and display it in a 8 dimensional cube then feeding from the small dense cube to the large sparse cube is a bad option as the only solution is to feed via "brute force" by feeding consolidations in each of your 6 missing dimensions. This will result in an unnecessarily dense target cube as feeding a consolidation actually feeds all ultimate leaf level descendants of the consolidation and consequently the target cube may duplicate data 1000s upon 1000s of times.

Presumably there is a "correct intersection" of buyers and quantity with each of the other 6 missing dimensions in cube 2 and hopefully there is some leaf data entered in the cube that you can feed quantity from. To continue with a rule approach to pull from cube 1 this would be the correct course of action to 1. feed quantity from within cube 1 from another measure that contains actual data in the cube and 2. Make the rule an N level rule. Making the rule an N rule and correctly feeding the N nodes will allow the values to correctly consolidate.

However I don't think this is a good option!

It looks like your system is suffering from some pretty poor design. Assuming that there is a correct matrix intersection across all 7 other dimensions in cube 2 for quantity then this is where the data should be loaded to (perhaps all 6 other dimensions are actually dependent attributes of market or buyer??) If the data is loaded correctly at the right intersections of cube 2 then everything will consolidate correctly up all hierarchies in all dimensions, no need for rules or feeders.

If you don't think this is an option please try to explain why. I can't see why producing the same quantity information for every single possible combination of the additional 6 dimensions would serve any purpose or be desirable.

[abcuser]

I tried to create a very simple sample, but it looks like it is too simple for my complex problem.

I have two cube in cube1 there is "insurance limit" (I just wrote quantity in my simple sample) - which is the maximum buyers requests that can be made on particular market. So this cube has market dimension (needs country) and measure dimension (measure "insurance limit"). So 100% dense cube.

In cube2 I have sales cube (real requests) with multiple measures all measures in cube2 are dependent on all of the 8 dimensions except for "insurance limit" (from cube1) which is only dependent on "country".

This problem is similar to planning (cube1) with sales (cube2). Planning is only done by two dimensions, but sales is done by 8. Planning is never done on low sales level data, but is done on upper level. But I need to get planning data (planed insurance limit) into sales cube.

[captnknz]

I would assume that you have appropriate metadata as attributes to the dimensions in the first cube that are used to populate the second. From here there are two options;
1. is there any reason why the second cube can't be populated from the first one using a TI process (this is likely to be significantly quicker, both in generating the data for the second cube, and viewing the data, with the added bonus of significantly smaller cube size. In my experience this is the difference between 2GB rules vs 100MB TI);
2. if you need to use a rule, you can try conditionally feeding. Ultimately the solution will be based on performance, but you can give it a try. Note: conditional feeders require the logic to reside on the right of the feeder statement, i.e. Feeder statement [a] => DB(Cube, IF(x, dimension, CONTINUE)). dimension being the metadata which can be held as an attribute to your elements in the primary cube so the can be referred to as ATTRS(). Hope this helpsl

Reading below again, the other thing is that you need to be able to use the !element from cube 2.

You also need to use the N: qualifier to pull through data at the node level, and not the consolidation. You can also test to see if exist, and if not, continue, rather that TM1 returning a 0 value from the rule which overrides the natural consolidation.

ie. ['QUANTITY']=N: DB('CUBE1,!CUBE2_MARKET_DIMENSION,'QUANTITY');

I would also expect that !CUBE2_MARKET_DIMENSION & !CUBE1_MARKET_DIMENSION have common elements.

Anyway some food for thought.

[lotsaram]

I think the tips from kaptnknz are more or less the same as I gave previously but with a better quantitative expression. It's always helpful to have the same or similar concept explained slightly differently.

I have two cube in cube1 there is "insurance limit" (I just wrote quantity in my simple sample) - which is the maximum buyers requests that can be made on particular market. So this cube has market dimension (needs country) and measure dimension (measure "insurance limit"). So 100% dense cube.

In cube2 I have sales cube (real requests) with multiple measures all measures in cube2 are dependent on all of the 8 dimensions except for "insurance limit" (from cube1) which is only dependent on "country".

This problem is similar to planning (cube1) with sales (cube2). Planning is only done by two dimensions, but sales is done by 8. Planning is never done on low sales level data, but is done on upper level. But I need to get planning data (planed insurance limit) into sales cube.

My question to add would be WHY? As in why do you need to pull through "insurance limit" into the 2nd cube? If it is needed for another downstream planning calculation you can use it in a calc without needing to first populate a measure in the other cube. If you want to display insurance limit side by side with the more detailed cube there is no requirement to have it residing in the same cube as you can easily build a report to pull data from multiple cubes. (provided you are using a websheet or spreadsheet report UI and not EV or a Cognos Studio ...)

Still not sure what your exact business analytics or planning problem is that you need to solve but if it does involve insurance there are at least 2 other colleagues on this forum I can think of who have very extensive experience with TM1 insurance underwriting planning models. If you can be less obtuse in what your actual problem is you will probably be able to receive some pretty good advice on how to proceed.

[abcuser]

@captnknz,
first of all I have tried to use the same market dimension for both cubes, but I got error that data can't be loaded to consolidated members. So I have created "clone" of dimension cube2_market_dimension in such a way that I have only loaded country_level into cube1_market_dimension. So all the members from cube1 and cube2 market dimensions are the same.
If I use formula:
['QUANTITY']=N: DB('CUBE1,!CUBE2_MARKET_DIMENSION,'QUANTITY');
then in cube2 quantity measure is 0 for all cube - don't know why.
But if I ommit "N:" like:
['QUANTITY']=DB('CUBE1,!CUBE2_MARKET_DIMENSION,'QUANTITY');
then cube2 quantity measures appears correct for all dimensions and levels execpt for cube2_market_dimension where data are correct for "contries" level, but there is zero values (instead of sum of children of contries) in single_top_element in cube2_market_dimension.

Don't know why should I use N: - I am not loading data to leaf level (like buyers level) I am loading data to upper level (consolidated level).

I have checked my TM1 data folder and sum of all files for cube2 is 20 MB, so I think cube is not huge.

At 1) I think I can't load cube2 from cube1 using TI process because error will be produced that data can't be loaded at consolidated level. I am trying to get data into cube2 at CONUNTRY level (this is consolidated level, because BUYERS is left level). That is probably why "N:" produces 0, just assumming...

At 2) Can you write some example about conditional feeder. IF(x, ....) what is x? It should be some logical extpression.


@lotsaram, I am trying to write it with simple sample. If I understand correctly my problem is: I am recesiving "quantity" data from cube1 dimension cube1_market_dimension into cube2 on cube2_market_dimension on CONSOLIDATED level (rules recesives data at COUNTRIES level which is consolidated level, not BUYERS level which is leaf level). The only problem is that single_top_element from cube2_market_dimension has a value of 0 (probably because I didn't use N: - because I can't, because I am "loading" data at consolidated level). So is there any way I could use:
[single_top_element] = children([single_top_element]);

@lotsaram, I am using Cognos v8.4 Report Studio, so I don't like to have two cubes, I like to have cube2 visible to Report Studio that is referenced to the data from cube1 at TM1 server level not at Cognos Report Studio level.

[lotsaram]

(I'm trying to help here, really I am ...)

OK I can see that you are stuck with what is bad cube design but due to your restrictions on report UI there is nothing you can do about it. A mechanism to build "meta views" or "meta cross-tabs" in TM1 is very long overdue where views from different cubes can be stitched together and share title elements! Something like this would avoid your issue altogether.

But that aside your issue has a very straightforward cause and equally straightforward resolution. You said very early on that "there is no top level consolidated node in cube 1 market dimension like there is in cube 2 market dimension." Well basically YOU NEED ONE. As you have written a global rule for "Quantity" this rule applies to all members of all other dimensions excluding the measure dimension. The rule works to collect data from the countries level because these members exist in the other dimension you see a value of zero for the top level consolidation because the same rule applies to calculate it yet when TM1 resolves the rule and checks for a value in the other cube the member does not exist so consequently (and rightly and logically) the value returned is zero.

In your case you don't need an N: rule as you don't actually want to consolidate values in the target cube but you appear to be under a misapprehension of what the N: and C: rule qualifiers do. The N: qualifier is best understood as a filter for the area statement. It has nothing to do with where the data is coming FROM. In your instance yes you are pulling consolidated data from another cube but this does not mean the N: qualifier is inappropriate, if you want to pull data into a cell that has leaf elements on all dimensions and consolidate it then that is what the N: qualifier is for. In your instance though you don't want to do this as it would result in aggregation which you don't want as you want to see the same quantity for many different levels and intersections in your target cube. As long as you don't need to see quantity with null suppression on then in this specific case you do not need to feed quantity. If you do want to display with null suppression on then you have a problem.

If you do want to aggregate quantity in the target cube then you will need feeders and you could also try ConsolidateChildren('Cube2_market_dimension'). I think for the design you have you are much better off creating the consolidations that are missing in Cube1_market_dimension though as this will do the trick for you!

I think for your level of understanding at this point there is not much point trying to explain conditional feeders.

[abcuser]

But that aside your issue has a very straightforward cause and equally straightforward resolution. You said very early on that "there is no top level consolidated node in cube 1 market dimension like there is in cube 2 market dimension." Well basically YOU NEED ONE. As you have written a global rule for "Quantity" this rule applies to all members of all other dimensions excluding the measure dimension. The rule works to collect data from the countries level because these members exist in the other dimension you see a value of zero for the top level consolidation because the same rule applies to calculate it yet when TM1 resolves the rule and checks for a value in the other cube the member does not exist so consequently (and rightly and logically) the value returned is zero.

Now I have added the top member in cube1.

In your case you don't need an N: rule as you don't actually want to consolidate values in the target cube... if you want to pull data into a cell that has leaf elements on all dimensions and consolidate it then that is what the N: qualifier is for. In your instance though you don't want to do this ...

I have removed the N:

Problem solved.

Thanks a lot for detailed explanation. I have learned so much from this thread. This forum rock!
Regards

[abcuser]

This problem is solved, but I have a new problem and according to forum rules I have opened a new thread: Consolidation problem.