TM1 Forum

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

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

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.

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...

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!

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.

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.