Rule - count

[davekerby]

Hi Guys,

I’m new to TM1, so please excuse me if my question comes across very straight forward. One of the measures I require is a count of stores, Would I create this in the rules? I need to be able to count the number of IDs (lower leaf element) in one of the dimensions.

Any help would be appreciated

Regards
Dave

[kpk]

Hello,

Try the ELCOMPN rule function.

Regards,

Peter

ELCOMPN returns the number of components in a specified element. If the element argument is not a consolidated element, the function returns 0.
Syntax
ELCOMPN(dimension, element)
Arguments
dimension A valid dimension name.
element The name of a consolidated element within the dimension. This argument must be the name of a valid element defined in the dimension. It cannot be a user-defined consolidation.
Example
ELCOMPN(‘Region’,’Scandanavia’)
In the Region dimension, the element Scandanavia is a consolidation of three elements. The example returns 3.

[davekerby]

Hi,

Thanks for your response. I've applied the fucntion ELCOMPN to the rules, however it takes a very long time to save the rule and open the cube now.

Any suggestions why this might be?

[Steve Rowe]

Dave,
You'll need to post your rules for us to help plus details of the cube and the dimension sizes. It's normal for the introduction of rules and feeders to slow things down. If you have the scope of the rule too wide then this can be dramtic.

From what you say you may be better off calculating the number of stores and storing it in a lookup cube. Functions that work off couting pieces of structure can be slow and you want to do them as few times as possible.

I'm going to guess what you need.

You have a dimension store, Level 0 is Store and Level 1 is region and level 2 is Country.
You want to know the number of stores at the region level.
So
Create a look up cube wit the dimensions Store and Store Properties
The Store Properties dimension has Store Count as an element
Write a rule in the look up cube for Store Count, something like this, syntax is not complete
['Store Count']=N:If ( Ellev('Store', !Store)=1, ElcompN ( blah blah blah),stet);
This calculates the result we need in the lookup cube.
Reference this result in the main cube whenever you need the Store Count.

Since TM1 caches numeric results the store count is only ever calculated once.
(This is the reason we don't use an attribute cube. We can only write string rules in an attribute cube and since TM1 doesn't cache the result of string calcs we are back to square one with many thousands of calcs where only 1 is required)

Anyway HTH

[davekerby]

Thanks for your help. Please find attached details of my dimensions and sizing.

I need to be able to count the number of stores at the leaf element with the NISA Account Group dimension. The structure looks like this

'All NISA Account Group'
'NISA Account Number'
'Account Group'
'Store'

I'd like to be able to report on the number of stores in a 4 week period or 12 week period selling a particular product

Example attached

[kpk]

Hello,

1.
In the measure dimension you can create an element: 'Number of active stores'
Then you can write a rule:

SKIPCHECK;
...
['Number of active stores']=IF(['Sales']>0,1,STET);
...
FEEDERS;
...
['Sales']=>['Number of active stores'];
...
2. Based on you have huge number of elements in many dimensions you must use SKIPCHECK / FEEDERS statement for all rules in this cube otherwise the performance will be unacceptable.

3. Do you have any string element in your measure dimension?
If not, then the order of your dimensions should start with the measures.
Please check the following topic:
http://forums.olapforums.com/viewtopic.php?f=3&t=1811

Regards,

Peter

[kpk]

Hi,

Thanks for your response. I've applied the fucntion ELCOMPN to the rules, however it takes a very long time to save the rule and open the cube now.

Any suggestions why this might be?

Hello,

The ELCOMPN function is not useful for you since you need a Sales value driven counting and not a simple count of the dimension element of the store dimension.

Regards,

Peter

[davekerby]

Thanks kpk,

I've just got a few qustions in regards to the store count rule. You said this should do the trick

SKIPCHECK;
...
['Number of active stores']=IF(['Sales']>0,1,STET);
...
FEEDERS;
...
['Sales']=>['Number of active stores'];
...

However wont this just put a 1 against any element that has a sale associated with it no matter if its a product or sales manager and so on? What i'd like to achieve is (attached) if i have the products in the rows and sales for each year in the columns, i also want the number of stores that have sold those products.

Hope this helps..

[kpk]

Hello,

You can refine the rule to eliminate multiplication of number of stores by unnecessary dimensions (eg. manager, department).

SKIPCHECK;
...
['Number of active stores','dummy department','dummy manager',.....]=IF(['Sales','Total Department','Total Manager', .......]>0,1,STET);
...
FEEDERS;
...
['Sales','Total Department','Total Manager',.....]=>['Number of active stores','dummy department','dummy manager',.....];
...

On the other hand I suppose this cube stores only actual data (you do not have any version dimension).
Therefore from performance point of view you can try to load the calculated number of the stores from your relational database into the above suggested combination.
It has some overhead during the upload, but could increase the performance of the reporting.

Regards,

Peter

[lotsaram]

You could use the ConsolidateChildren function but I wouldn't recommend it in a large (or even moderately sized) cube as it is a real performance killer.

You are better off to calculate on load and populate into a smaller cube with appropriate dimensionality or else do as Peter has suggested and write a narrowly defined rule that applies only to All Depts and All Managers etc.

[davekerby]

Thanks Peter,

Can you elaborate on you suggestion, im fairly new to TM1 and dont fully understand

['Number of active stores','dummy department','dummy manager',.....]=IF(['Sales','Total Department','Total Manager', .......]>0,1,STET);

Whats is the dummy department and manager and Total Department and Manager in the IF statement? You mentioned that I refine the rule to eliminate multiplication of number of stores by unnecessary dimensions are the dummy department the dimension names?

Regards
Dave