Getting DISTINCT Counts using TI Process

[ExApplix]

I want to get the DISTINCT Counts using a TI Process.

I have many dimensions in my cube like Customer, Region, Products, Period, Measures etc. I want to get the count of the customers who have bought a certain product.

I have added a measure 'CustomerCount' to the measures dimension. I have written a TI Process (Data Tab)

Code: Select all

Sales = CellGetN (Cube, d1, d2....,Sales)
CustomerCount = CellGetN (Cube, d1, d2....,CustomerCount)

IF ((Sales <> 0) & (CustomerCount < 1));
  CellPutN (1,Cube, d1, d2....,CustomerCount)
ENDIF;

which gives me following result:

PRODUCT SALES CustomerCOUNT
Customer A Product1 300 50
Customer A Product1 250 22
Customer B Product3 170 15
Customer C Product4 0 0

But I only want to see how many Customers bought a certain product NOT how many times they bought this product. For example:

PRODUCT SALES CustomerCOUNT
Customer A Product1 30 1
Customer A Product1 20 1
Customer B Product3 10 1
Customer C Product4 0 0

Please suggest me some solution ASAP. Thanks

[Martin Ryan]

You'll be getting a high count because of your other dimensions, e.g. region and period. Two suggestions for resolving it.

1) Add elements like 'No Region' and 'No Period' to your dimensions and write the counts there, so that Regions and Periods will be ignored.

2) Create a new cube that only has Customer and Product as dimensions (plus a measures dimension), then write a rule that says ['Count'] = if(DB('MainCube', !Customer, !Product, 'All Regions', 'All Periods', 'Sales')<>0, 1, 0);

I'd go with option 1.

HTH,
Martin

[ExApplix]

Martin,

Option 1 is not what I am looking for.

I did this thing through a rule. But my prod cube is huge and I have performance issues - specially with the EV. This is the reason I want to do this through a Process so that values will be stored in the cube itself and not calculted bu rule.

Any other help for TI Process?

[Martin Ryan]

Option 1 will still work with a TI process (in fact, that's how I meant it). Virtually any rule approach can be replicated via a TI process. Option 2 would work better with a rule I would think, but could still be done via TI too.

Cheers,
Martin

[ExApplix]

Martin,

Please see the attached file. It has got my sample cube, dimensions, processes and the source CSV file.

Please have a look at the process LOAD_SALES_COUNT.pro and let me know what I am missing there. I am using this process to add the Count values to the cube. But I dont want the Transaction Count (which my process is doing). I want DISTINCT count.

If you can send me the *.PRO file it will be highly appreciated.

Thanks

[Martin Ryan]

I wouldn't want to rob you of the sense of achievement

But here's step by step instructions
1) Add 'No Region' to your region dimension. Don't add it to the hierarchy though.
2) Add 'No Period' to your period dimension. Don't add it to the hierarchy though.
3) Amend your TI code like so

Code: Select all

    
Sales = CellGetN (Cube, Region, Product, Customer, Period,Sales);
IF ((Sales <> 0) & (CustomerCount < 1));
    CellPutN (1,Cube, 'No Region', Product, Customer, 'No Period', CustomerCount)
ENDIF;

HTH,
Martin

[ExApplix]

Thanks Martin your solution does work but it has 2 issues

1) Users have to select 'No Region', 'No Period' etc from all the other dimensions except Customer and Product
2) Users will not be able to see the Count if they view the cube by Region or by Period etc. The Counts will only be applicable when the users are looking at the Customer/Product.

what do you think?

[Martin Ryan]

Hi Ex,

1) True. I guess you could add 'No Region' and 'No Period' to the 'All Regions' and 'All periods' consolidations. Users could then look at the consolidated point.
2) I had thought that was the aim? To ignore region and period completely? If that's not the aim, for example if you wish to sometimes run for Australia, sometimes for the World, sometimes for Sydney, then you'll have to add that in as a parameter for your TI process. If you want to do that I'd suggest setting up a separate cube that has Customer, Product and }Clients and Measure as dimensions, then add two parameters to your process pRegion and pPeriod. Your TI would then be.

Code: Select all

    
if(region@=pRegion % elisanc('Region', pRegion, region)=1, 0, itemskip);
if(period@=pPeriod % elisanc('Period', pPeriod, period)=1, 0, itemskip)
Sales = CellGetN (Cube, Region, Product, Customer, Period,Sales);
    IF ((Sales <> 0) & (CustomerCount < 1));
        CellPutN (1,CountCube, Product, Customer, tm1user(), CustomerCount)
    ENDIF;

You might wish to have a couple of string elements in Measure called 'Period' and 'Region', so that you can record what the users specified. You'd have a prolog like so (I've arbitrarily put it at the consolidation point of all products and customers. You could put it anywhere, or in a cube of its own).

Code: Select all

cellputs(pRegion, CountCube, 'All Products', 'All Customers', tm1user(), 'Region');
cellputs(pPeriod, CountCube, 'All Products', 'All Customers', tm1user(), 'Period');

Martin