No consolidated data when applying rules

[A.Pete]

Hi
I have a questions regarding rules and summations. Ill try to explain my scenario below!

Cube Target
Rules
SKIPCHECK;
['Amount' ] =N: DB('Cube Source', !AccountID, !BrandID, !Business PartnerID, !Calendar PeriodID, !CompanyID, !Cost CenterID, !Fiscal PeriodID, !Product GroupID, !ProjectID, !StatusID, 'Amount');
['Quantity' ] =N: DB('Cube Source', !AccountID, !BrandID, !Business PartnerID, !Calendar PeriodID, !CompanyID, !Cost CenterID, !Fiscal PeriodID, !Product GroupID, !ProjectID, !StatusID, 'Quantity');
FEEDERS;

Cube Source
Rules
SKIPCHECK;
FEEDERS;
['Amount' ] => DB('Cube Target', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Amount');
['Quantity' ] => DB('Cube Target', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Quantity');


The ID Dimensions are the dimensions that has the star schemas IDs as lowest levels and since those are not valid to users i want to move the data to Cube Target using rules and into more user friendly dimensions.

An example of the Account dimensions follows:
AccountID:
Level 1, Level ID
1000, 1
2000, 2
1234, 3

Account:
Level 1, Level 2, Level 3
1, 10, 1000
2, 20, 2000
1, 12, 1234

I want to load from dimension AccountID level 1 to dimensions Account level 3.

The above rules compile but i get no data. I i remove the feeder statement in Cube Source i get values at the lowest levels in Cube Target but they dont sum up.
What im I doing wrong here?

/Peter

[Duncan P]

Would it be possible to show a screenshot from Architect of the dimensions of both the source and target cubes.

I would have expected from your explanation of what you are trying to do that the ID dimensions would be on the source cube, but you appear to be using them in the rule for the target cube. In a rule you can only use ! against dimensions of the cube to which the rule is applied so if the ID dimensions are only on the source then the rule as you have shown it should not compile.

To your other point, the lack of a feeder to a cell will not prevent that cell from showing a correct calculated value if it is accessed directly. Feeders are for identifying which cells to query in the two situations where otherwise the engine would have to visit every cell in a range, zero suppression and aggregation. In your case it is only the aggregation that will be wrong if the feeder is missing.

[A.Pete]

Hi

ive attached a screenshot of the cubes and dimensions. The cube with the ID dims is the source cube.

Br
Peter

[Duncan P]

Thanks Peter.

If "GL_M3_DIMS" is your source and "General Ledger M3" is the target then that rule cannot have compiled as it appears in the post, for the reason I described earlier. Did something get changed in copying it into the forum. If so it would help to see the statements from the actual rules of the cube.

Cheers,
Duncan.

[tomok]

You must be a Cognos Planning person that is trying to make the switch to TM1 because you are using the wrong terminology for levels which may be confusing some of us. I think what you mean is that you want to move a consolidated node (which is any level above 0) from your GL_M3_Dims cube into a leaf level node (which is level 0) in the General Ledger M3 cube. As Duncan pointed out, your syntax in the rules is incorrect. A rule for a cube cannot contain a dimension reference for a dimension that does not exist in the cube. In your example you are referencing AccountId, BrandID, etc., in the rule for the General Ledger M3 cube. This is invalid and will not compile. You should have received an error message when you attempted to save this rule. What you should have used as the references is the dimension name that DOES exist in the General Ledger M3 cube: Account, Brand, etc. Writing an intercube reference rule this way acts like a JOIN statement in SQL. What will happen is that the leaf level nodes in the General Ledger M3 cube will be populated with ANY matching element from the AccountID dimension from the GL_M3-Dims cube, which is what I think you want.

For the FEEDERS statement you need to reference the dimension names that are in the source cube; AccountID, BrandID, etc.

[A.Pete]

Hi

Im actually a Cognos BI person .

Well I changed to this:

Target
SKIPCHECK;
['Amount' ] =N: DB('General Ledger M3', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Amount');
['Quantity' ] =N: DB('General Ledger M3', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Quantity');
FEEDERS;

Source
SKIPCHECK;
FEEDERS;
['Amount' ] => DB('GL_M3_Dims', !AccountID, !BrandID, !Business PartnerID, !Calendar PeriodID, !CompanyID, !Cost CenterID, !Fiscal PeriodID, !Product GroupID, !ProjectID, !StatusID, 'Amount');
['Quantity' ] => DB('GL_M3_Dims', !AccountID, !BrandID, !Business PartnerID, !Calendar PeriodID, !CompanyID, !Cost CenterID, !Fiscal PeriodID, !Product GroupID, !ProjectID, !StatusID, 'Quantity');


It seems like its compiling (takes a while to save the rule) but im not getting any figures at all in the target cube.

[lotsaram]

The rule is taking a while to compile because of the feeders being processed due in turn to the volume of data in the source cube (as the volume of data grows this rule save time will grow in proportion as well so watch out.) the reason values don't consolidate in the target cube is that you haven't given the correct address for the feeder "join" as the "ID" dimension elements don't find a matching named element in the target cube hence the feeders are wasted.

To address this you need to add an alias to the dimensions with the "human recognizable names" of the ID from the rdbms table or you "bend" the feeder by embedding an AttrS within the DB to lookup the correct element to feed to.

From your description of the use case however I think that a far better fix would be in the model design itself and load direct to BOTH cubes as using a rule for this is needless performance and processing overhead.

[A.Pete]

Hi

The design could probably be built in a better way. The problem i have is that alot of visible lowest levels could have multiple IDs (links ot the fact in the star schema). Thats why im doing it this way.

What i just saw was that ive emptied the source cube of data somehow so thats problably one of the reasons im not getting any figures...

Thanks for all the answeres though!

BR
/Peter

[lotsaram]

In case you should repopulate the source cube with data and find that you still have the same issue in the target cube then reread all the pointers in the thread carefully and I think you will find the answer.

[A.Pete]

Hi again

Ive populated the cube with data and changed the rules to these

Target:
SKIPCHECK;

['Amount' ] =N: DB('GL_M3_Dims', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Amount');
['Quantity' ] =N: DB('GL_M3_Dims', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Quantity');

FEEDERS;

Source:
SKIPCHECK;

FEEDERS;

['Amount' ] => DB('General Ledger M3', !AccountID, !BrandID, !Business PartnerID, !Calendar PeriodID, !CompanyID, !Cost CenterID, !Fiscal PeriodID, !Product GroupID, !ProjectID, !StatusID, 'Amount');
['Quantity' ] => DB('General Ledger M3', !AccountID, !BrandID, !Business PartnerID, !Calendar PeriodID, !CompanyID, !Cost CenterID, !Fiscal PeriodID, !Product GroupID, !ProjectID, !StatusID, 'Quantity');


This gives me nothing in the target cube. If i change the =N: to =C: i get data at the lowest level and it sums to the top node (total) but the the levels in between.

I feel like a total idiot here but i cant seem to wrap my head around this properly. Could someone write what i should write in my rules since my monday (errr friday) head is totally lost.

Br
Peter

[Duncan P]

Hi Pete,

Is it possible for you to post screenshots of a pair of your dimensions with the hierarchy expanded, e.g. Brand and BrandID? Then we would be able to see what items matched and at what level.

Assuming that all the items of Brand etc. are higher level items of BrandID and that none of the leaf items of BrandID are in Brand, and that the same principle applies for all the other dimension pairs, then your feeder statements are targeting items that don't exist in the target cube. If for each of these dimension pairs the parent of the leaf item in e.g. BrandID is a leaf item in Brand then you could use :-

Code: Select all

FEEDERS;

['Amount' ] => DB('General Ledger M3', ..., ELPAR('BrandID, !BrandID, 1), ..., 'Amount');
...

If the leaf item of Brand is higher up the hierarchy in BrandID then it gets more complicated. In that case it would probably be better to have an attribute in BrandID identifying for each leaf item the corresponding leaf item in Brand to which it belongs. Then your feeder would look like this :-

Code: Select all

FEEDERS;

['Amount' ] => DB('General Ledger M3', ..., ATTRS('BrandID, !BrandID, 'CorrespondingBrand'), ..., 'Amount');
...

Of course this applies in the same way to all the other dimension pairs.

Cheers,
Duncan.

[A.Pete]

Hi Duncan

Below are the Account and AccountID dimensions. It is probably as you say that i have to create a attribute but the problem is that i could have multiple ID for one account. This will not work as an alias right?

Br
Peter

[Duncan P]

You are right that it would not work as an alias (I presume you mean on the target dimension). You will just have to use ATTRS in your feeder statement as I have shown, unless you can guarantee that the parent of each source leaf will always be in the target dimension, in which case you can use ELPAR.

[A.Pete]

Hi

Well i think i understand this but im still not getting the right summations...

This is what it looks like now:

Target
SKIPCHECK;

['Amount' ] =C: DB('GL_M3_Dims', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Amount');
['Quantity' ] =C: DB('GL_M3_Dims', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, 'Quantity');

FEEDERS;

Source

SKIPCHECK;

FEEDERS;

['Amount' ] => DB('General Ledger M3', ELPAR(!AccountID,!AccountID,1), ELPAR(!BrandID,!BrandID,1), ELPAR(!Business PartnerID,!Business PartnerID,1), ELPAR(!Calendar PeriodID,!Calendar PeriodID,1), ELPAR(!CompanyID,!CompanyID,1), ELPAR(!Cost CenterID,!Cost CenterID,1), ELPAR(!Fiscal PeriodID,!Fiscal PeriodID,1), ELPAR(!Product GroupID,!Product GroupID,1), ELPAR(!ProjectID,!ProjectID,1), ELPAR(!StatusID,!StatusID,1), 'Amount');

['Quantity' ] => DB('General Ledger M3', ELPAR(!AccountID,!AccountID,1), ELPAR(!BrandID,!BrandID,1), ELPAR(!Business PartnerID,!Business PartnerID,1), ELPAR(!Calendar PeriodID,!Calendar PeriodID,1), ELPAR(!CompanyID,!CompanyID,1), ELPAR(!Cost CenterID,!Cost CenterID,1), ELPAR(!Fiscal PeriodID,!Fiscal PeriodID,1), ELPAR(!Product GroupID,!Product GroupID,1), ELPAR(!ProjectID,!ProjectID,1), ELPAR(!StatusID,!StatusID,1), 'Quantity');


And the result in a picture below.

I feel that im doing something wrong in the target rules since i have to use =C: to even get any figures in the cube.. If i use =N: its totally blank.

[tomok]

You've only shown us the details behind one of the dimensions (Account) and based on your feeder statement, your assertion is that every single dimension is structured the same way, i.e, there is a parent level consolidation in the source dimension that corresponds with a leaf level element in the target. This seems a bit dubious to me. I can see that for the Account, Project, Brand, Business Partner, etc., but the Calendar Period and Fiscal Period??? Your feeder is not going to work unless that is the case EXACTLY. Perhaps you should post screen shots for ALL the dimensions.

[A.Pete]

Hi

All dimensions are structured in this way. Specially the time dimensions (fiscal and calendar) since those are per day but the client only want it per month. This will give me alot of ID below the month.

So... I should work, shouldnt it?

Br
Peter

[qml]

All dimensions are structured in this way. (...)
So... I should work, shouldnt it?

If this really is the case (forgive me if I remain doubtful on this) then it should workon the N level, provided you fix all your incorrect ELPAR functions in the feeder statements, e.g. ELPAR(!AccountID,!AccountID,1) should be ELPAR('AccountID',!AccountID,1) etc.

Also, personally I think the code would look better if you avoided redundancy by writing your rules and feeder statements in the following fashion:

Code: Select all

[{'Amount','Quantity'}] = N: DB('GL_M3_Dims', !Account, !Brand, !Business Partner, !Calendar Period, !Company, !Cost Center, !Fiscal Period, !Product Group, !Project, !Status, !Measure);

Code: Select all

FEEDERS;
[{'Amount','Quantity'}] => DB('General Ledger M3', ELPAR('AccountID',!AccountID,1), ELPAR('BrandID',!BrandID,1), ELPAR('Business PartnerID',!Business PartnerID,1), ELPAR('Calendar PeriodID',!Calendar PeriodID,1), ELPAR('CompanyID',!CompanyID,1), ELPAR('Cost CenterID',!Cost CenterID,1), ELPAR('Fiscal PeriodID',!Fiscal PeriodID,1), ELPAR('Product GroupID',!Product GroupID,1), ELPAR('ProjectID,!ProjectID',1), ELPAR('StatusID',!StatusID,1), !Measure);

[A.Pete]

Thanks qml this seems to work! I got it to work for one measure that hasnt all that much data but the rest is eating up all
the memory (as some of you suspected).

Its not that much data so ill need to try another solution. Any tips would
be appreciated!

Br
Peter

[lotsaram]

If you do stick with rule based summarization from one cube to another then I would strongly advise NOT to use ELPAR as elements in TM1 dimensions can have multiple parents and the 1st, 2nd, 3rd, etc parent is not a definable property but is determined by dimension index order of the parent elements and so may be subject to change. Maybe now the dimensions have only one hierarchy or a defined order of hierarchies but this is easily changed and may not be picked up by and impact analysis then all the rules will cease to work. Therefore it is best to always define a "parent" or "feeder" mapping as a string attribute and use ATTRS not ELPAR in such situations.

However for such a use case as you have as I said already having rule based summarization is IMO very bad design and you would be much better served by loading directly from the DWH tables to your reporting cube. You can load to both cubes and cumulate values into the reporting cube and display the relationship between the cubes via a drill-through but I would ditch the rules, it is only adding performance overhead and complexity for no added value.