Feeding one cube with data from multiple source cubes

[Dev1975]

Hello Everyone,

I'm not very well versed in TM1 Rules and am having a bit of a tough time getting this to work. I've got 3 cubes, the first contains data for the current quarter, the second contains next quarter data and the final is one I created, which is a consolidation of the first two.

I'm using Cognos Express 10.2 and am using Performance Modeler to create the cubes and everything else. The quarter cubes are fine and both contain exactly the same columns and dimensions as each other. The consolidation cube has all the same columns and dimensions, however it only contains data from one cube. I setup two links to put the data into the consolidation cube but it only put in the data from the first one I setup.

I've right-clicked on my consolidation cube and selected "generate rule" and it showed the following:

#Region Link rule: Next QTR Leads > All Sales Leads - Numeric
#Source cube: Sales Leads Nxt QTR
#Target cube: All Sales Leads
#Autogenerated LINK NUMERIC 7D4C696E6B5F4E65787420515452204C65616473205F334520416C6C2053616C6573204C65616473
#@AutoGenerated
['salesleadscrntqtr.measures':{'Consulting Revenue','Product Revenue','Product Margin','Support Revenue'}] = N:DB('salesleadsnxtqtr',
!opportunities, !centreofexcellence, !expectedclosedate,
ATTRS('salesleadscrntqtr.measures', !salesleadscrntqtr.measures,
'}Map_}Link_Next QTR Leads _3E All Sales Leads'));
#EndRegion

#Region Link rule: Current QTR Leads > All Sales Leads - String
#Source cube: Sales Leads Crnt QTR
#Target cube: All Sales Leads
#Autogenerated LINK STRING 7D4C696E6B5F43757272656E7420515452204C65616473205F334520416C6C2053616C6573204C65616473
#@AutoGenerated
[] = S:DB('salesleadscrntqtr', !opportunities, !centreofexcellence,
!expectedclosedate, !salesleadscrntqtr.measures);
#EndRegion

#Region Link rule: Current QTR Leads > All Sales Leads - Numeric
#Source cube: Sales Leads Crnt QTR
#Target cube: All Sales Leads
#Autogenerated LINK NUMERIC 7D4C696E6B5F43757272656E7420515452204C65616473205F334520416C6C2053616C6573204C65616473
#@AutoGenerated
[] = N:DB('salesleadscrntqtr', !opportunities, !centreofexcellence,
!expectedclosedate, !salesleadscrntqtr.measures);
#EndRegion

How do I edit this so that the data from both the source cubes populates the consolidation cube I created, rather than just have it populate from the one cube? Apologies if this is a really basic question but I'm kind of thrown in at the deep end here and am struggling to get my head around rules.

Any pointers, info, etc, greatly appreciated.

Many thanks,
Dev

[qml]

What is the reason for a setup like this? It seems you would be much better off having one cube with data for both quarters, or, in fact, for all possible/available quarters.

And just in the interest of doing thought experiments - if your consolidation cube has the exact same dimensions as your two source cubes then how are you planning to separate the data from both cubes? Would you not need another dimension for time/quarters? Once you have that, the syntax of the rules should be much more obvious. This, however, is the wrong way to do it anyway, like I said in my first paragraph.

[java_to_tm1]

Dev,
the way rules in TM1 work is this...
Going down a rule file, once a rule is found that applies to a cell (or area or slice), no other rule will apply to that same cell (area or slice).

If your rules in cube 3 are like this:

Code: Select all

[] = N: DB ( Cube2, !dim1, !dim2, !dim3, !msr );
[] = N: DB ( Cube1, !dim1, !dim2, !dim3, !msr );

TM1 will consider only the first rule and not the second. The firs rule covers every single slice of the cube and hence the second rule is never evaluated.

If your first rule had been like this:

Code: Select all

['msr':'Consulting Revenue'] = N: DB ( Cube2, !dim1, !dim2, !dim3, !msr );
[] = N: DB ( Cube1, !dim1, !dim2, !dim3, !msr );

The first rule covers only the 'Consulting Revenue' slice of the cube and the second rule is evaluated for the other measures.

If what you want is for the data from both soures to add up, you COULD fix your rule by merging your two rules into 1 like this:

Code: Select all

[] = N: DB('salesleadsnxtqtr', !opportunities, !centreofexcellence, !expectedclosedate, !salesleadscrntqtr.measures)
+ DB('salesleadscrntqtr', !opportunities, !centreofexcellence, !expectedclosedate, !salesleadscrntqtr.measures);

Note that you CANNOT merge these two rules through Performance Modeler and links: PM links do not allow you to consolidate data from 2 sources to the same measure on the target cube. You will have to disable the links and then write the rule yourself.

Having said all that:
Why would you NOT want to (as Kamil suggests) have your data in one cube (with an added quarters dimension)?
A simple hierarchy on your quarters dim (refer the screenshot I've shared) should give you the consolidation without any need to use rules / links/ Performance Muddler.

[Dev1975]

Thanks for the help, guys, really appreciate it. After some tinkering I've got the data in one cube and am able to happily display it as need be.

I now want to refine the cube so that, whenever I open the cube, the default view shows me only data from today and beyond. I believe that this could be accomplished by writing a rule on the cube using the "Today()" function but am not able to reference the "Expected Close Date" dimension in the cube to do the evaluation.

Are dimensions not able to be referenced in cube rules? Is there another approach that I should be using?

TIA...

[qml]

Are dimensions not able to be referenced in cube rules?

Of course they are. They need to be dimensions present in the cube the rule is attached to and you need to use the bang ('!dim') notation to reference the 'current' element.

Is there another approach that I should be using?

Yes. You do not need any rules there. Your default view should have a subset attached to it that will always return the days you want to show. The subset can be a dynamic one or a static one. I suggest a static subset that is rebuilt every night by a chore. All the logic would then be in the TI script, including an instance of the NOW() function to get the current date and TIMST() function to format it for your needs.

[Dev1975]

Hi qml, thanks for taking the time and responding, much appreciated. Would it be possible for you to show me how to write the TI process to display current information in my cube? I've spent the last 2 days going over it myself (amongst other things) but cannot get my head around it and I am honestly getting frustrated with myself.

I've attached a small screenshot of what my cube currently looks like, basically where it says "2014 expected close date", which refers to the dates associated with the imported data, I want the process to evaluate that date value and only display data in the cube if it evaluates to today's date or beyond. Anything prior to the current date is not to be shown, nor should be selectable from the context explorer.

TIA!

[tomok]

Based on your screen shot it would appear that "2014 Expected Close Date" is an element in your Time dimension. Is it a leaf node or a consolidation? If a consolidation then what you need to do is have a TI process that runs each night and updates that node to only includes today's dates and those beyond. Shouldn't be that hard to do. The first thing you will want to do is unwind the current hierarchy for that node:

Code: Select all

vDim = 'NameofYourTimeDimension';
vNode = '2014 Expected Close Date';
vChildCount = ELCOMPN(vDim, vNode)
IF(vChildCount <> 0);
	i = 1
	WHILE(i <= vChildCount);
		vChild = ELCOMPN(vDim, vNode, 1);
		DIMENSIONELEMENTCOMPONENTDELETE(vDim, vNode, vChild);
		i = i + 1;
	END;
ENDIF;

Then you'll rebuild it:

Code: Select all

Count = DimSiz(Dim);
i = 1;
WHILE (i <= Count) ;
	vEl = DIMNM(vDim,i);
	IF(ConditionThatChecksforDatesisTrue);
		DimensionElementComponentAdd(vDimName, vNode, vEl, 1);
	ENDIF;
	i = i +1 ;
END;

[Dev1975]

Hi Tomok,

The "Expected Close Date" shown in the screenshot is at the consolidation level. The dates underneath it are the ones related to the cube entries that have been imported. As you can imagine, there is only data for certain dates in 2014, not the entire calendar year. From the context area, if I click on "2014", it shows me the other date entries that are available to choose from, including those from before the current date.

Thank you for your response, really appreciate it. I'll give that a try and see what happens.

[tomok]

The "Expected Close Date" shown in the screenshot is at the consolidation level. The dates underneath it are the ones related to the cube entries that have been imported. As you can imagine, there is only data for certain dates in 2014, not the entire calendar year.

???????? If this is the case then why are you wasting our time? The view is already going to only show data for the periods that roll up to "Expected Close Date". No further work is necessary. This is how rollups work in TM1, only data from child elements that are part of the consolidation will be included in the total. There is no need to write a rule to make all the other time components zero because they aren't part of the total to begin with.

[Dev1975]

My apologies Tomok, I didn't set out to waste anyone's time. I appreciate the fact that you took the trouble to answer my initial query. Perhaps it was my limited understanding of the product but I am more aware now.

Thanks again for all your help, really appreciate it.