Rule fo cubes with different Dimensions

Post Reply
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Rule fo cubes with different Dimensions

Post by ExApplix »

Hi,

I am tying to write a Rule between the cubes with different dimensions. Please see the attached screen shot for the sample cube structure. As you can see in Cube 1 I have Department and Account dimensions which are not present in the Cube 2. Thats why I am problems in writing DB() function.

I think if I create Attributes on the Employee dimensions (i.e. one attribute for the Department and another for the Account) then I will be able ot use the ATTRS in the DB() formula. But my problem is that Employe has one to many realtionship with the Department dimension and I think thats why ATTRS will not really work.

Is there any other approach to this?

Your help in this matter will be highly appreciated. Thanks
Attachments
Rule different Dimension
Rule different Dimension
Rule_DifferentDims.JPG (11.43 KiB) Viewed 10003 times
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Rule fo cubes with different Dimensions

Post by Martin Ryan »

You didn't mention if you'd searched the forum - did you check these posts and not find any help?
http://forums.olapforums.com/viewtopic.php?f=3&t=3945
http://forums.olapforums.com/viewtopic.php?f=3&t=992 (I think this one has some useful points about a mapping table that might be relevant)
http://forums.olapforums.com/viewtopic.php?f=3&t=2781
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: Rule fo cubes with different Dimensions

Post by ExApplix »

Yes Sir, I did search through the forum but as I said that in my example one Employee can belong to multiple Departments and Accounts (there is no one to one realtionship), that is why I think that I can not use the Attributes (ATTRS etc.).

Please suggest me some way (preferably using Rules) by which I can connect these dis-similar cubes.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Rule fo cubes with different Dimensions

Post by Alan Kirk »

ExApplix wrote:Yes Sir, I did searched through the forum but as I said that in my example one Employee can belong to multiple Departments and Accounts (there is no one to one realtionship), that is why I think that I can not use the Attributes (ATTRS etc.).

Please suggest me some way (preferably using Rules) by which I can connect these dis-similar cubes.
And you read chapters 4 and 6 of the Rules manual as well?

You haven't said whether you want the rule in cube 1 pulling from cube 2, or a rule in cube 2 pulling from cube 1. However from the tone of it, it seems likely that you want the rule to pull values from cube 1 into cube 2.

If that is indeed the case then you'd probably want the sum of all of the departments and the sum of all of the accounts to be referred to in your rule formula for any given combination of employee, period, version and measure.

Hmm, it's quite a puzzler, but there must be some way to do that...
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
ExApplix
Posts: 103
Joined: Mon Sep 21, 2009 7:09 pm
OLAP Product: Cognos Tm1
Version: 10.2.2
Excel Version: 2016

Re: Rule fo cubes with different Dimensions

Post by ExApplix »

Alan Kirk wrote:You haven't said whether you want the rule in cube 1 pulling from cube 2, or a rule in cube 2 pulling from cube 1. However from the tone of it, it seems likely that you want the rule to pull values from cube 1 into cube 2.

If that is indeed the case then you'd probably want the sum of all of the departments and the sum of all of the accounts to be referred to in your rule formula for any given combination of employee, period, version and measure.
I am trying to write a Rule in Cube 2 to pull the data from Cube 1.

In the source cube (Cube 1) I have some Measures of String type and thus I do not have anything like Sum of all Departments. Example of my Measures are the Service Start Dates and the Service End Dates. One Employee can belong to multiple departments and thus they can have multiple Service Start Dates and the Service End Dates.

Any help in this matter would be higly appreciated. Thanks!
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Rule fo cubes with different Dimensions

Post by Alan Kirk »

ExApplix wrote:
Alan Kirk wrote:You haven't said whether you want the rule in cube 1 pulling from cube 2, or a rule in cube 2 pulling from cube 1. However from the tone of it, it seems likely that you want the rule to pull values from cube 1 into cube 2.

If that is indeed the case then you'd probably want the sum of all of the departments and the sum of all of the accounts to be referred to in your rule formula for any given combination of employee, period, version and measure.
I am trying to write a Rule in Cube 2 to pull the data from Cube 1.

In the source cube (Cube 1) I have some Measures of String type and thus I do not have anything like Sum of all Departments. Example of my Measures are the Service Start Dates and the Service End Dates. One Employee can belong to multiple departments and thus they can have multiple Service Start Dates and the Service End Dates.

Any help in this matter would be higly appreciated. Thanks!
So again, you oversimplify the question to the point where you're not actually asking for a solution to what your real problem is. In this case, string aggregation.

As I subtly hinted you pull across numeric values by using consolidations in your DB() formulas. This is a principle generally learned in "Rules 101". Or from chapters 4 and 6 of the manual.

But you were planning to aggregate values like start and end dates into a meaningful (important qualification there) value in cube 2... how? As in, even if you were to be able to do this, what's the point of having a series of string dates in a single cell in cube 2 without any context about which departments they belong to? And if you DO plan to add extra strings to identify the departments, then the better approach is not to exclude the department dimension from Cube 2 anyway.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Rule fo cubes with different Dimensions

Post by tomok »

ExApplix wrote:Example of my Measures are the Service Start Dates and the Service End Dates. One Employee can belong to multiple departments and thus they can have multiple Service Start Dates and the Service End Dates.
What you are trying to do does not make any sense at all in an OLAP solution! In order to have multilpe Start and End dates for a single employee in a cube you need another dimension in the cube to provide a cell in which to store those. In your example that would be department from Cube 1. For each employee you have the potential for one Start and End date for each combination of employee and department. All is well and good. When you get to Cube 2 the department dimension is gone, so now you only have the storage capacity for one Start and one End date. How can you reference in data from multiple Start and End dates from multiple cells in Cube 1 into just one cell in Cube 2? You can't without monkeying with the Measures dimension and concatenating the measure with the department like this:

Start_Date_Dept1
Start_Date_Dept2
Start_Date_Dept3
End_Date_Dept1
.......

However, this opens up a whole pandora's box of complexities in your model. If this were a relational database you would just have multiple records in the Cube 2 table but this isn't a relational DB, it's OLAP and as such, the boundaries are fixed by your dimensionality. You need to scrap Cube 2 and just use Cube 1.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply