Powerplay - Counts 1

[bsellick]

Am not having much luck coming up with counts for the fact table I am using in a PP cube.

The goal is to have a count of one per order however the granularity of the table is such that there can be multiple records per order.

One thing I have thought of but haven't figured out how to do is somehow "allocate" a count (if there are two records on the order then each individual count is .5 which would then be rolled up to one).

Thanks in advance for any suggestions.

 

[EPMDEV]

Why dont you try to get the unique values of order and then count it. Hope it should work out.

 

[nagrajm]

Include a calculation called "Count of orders" with the expression - If (running-count(field) for Order_No = 1) then(1) else(0).

The "field" can be any field associated to the order no.

What the new calculation will do is return a value of 1 for every first row of the new order no. Other rows for the same order would have the values 0.

Example

District Order No. running_count Count of Orders

NJ 1001 1 1
NJ 1001 2 0
NJ 1001 3 0
MN 1002 1 1
MN 1002 2 0

so on so forth.

Just make sure that the Order No. field is sorted within your logical grouping of columns.

 

[bsellick]

Hi nagrajm,

Your example works fine by the "district" dimension and in fact is something I did to at least make the count work in that fashion. However, there is an additional element to orders that I did not make anyone aware of and that is a "unit branch". Each order can be fulfilled by one or more units (which is why the fact table can have 1 or more records per order). Your count example would not work in all cases when browsing the cube by the unit branch dimension because it is possible that only one of the records on the order is for that unit branch and that one may not be the one with the count.

Hope that clarifies things.

To EPMDEV: I am not sure what you mean. Perhaps my note here will further clarify what the data looks like.

 

[nagrajm]

You should then have 2 columns in the IQD. One that counts orders by "District" and the Other by "Unit Branch". Let's call them "COUNT OF ORDERS BY DISTRICT" and "COUNT OF ORDERS BY UB".

The "COUNT OF ORDERS BY DISTRICT" would have the expression of running_count mentioned in my earlier post.

If the design of your FACT table is such that it can have one and only row for every "Unit Branch" then the 2nd expression "COUNT OF ORDERS BY UB" will have a simple expression of "1". In effect, you are counting every row.

If there can exist multiple rows for an "ORDER NO-UNIT BRANCH" then the expression would instead be
If (running-count(field) for order_No, Unit_branch = 1) then(1) else(0).

Make sure that Unit_Branch is sorted within Order_No.

Include both measures "COUNT OF ORDERS BY DISTRICT" and "COUNT OF ORDERS BY UB" in your powerplay cube and the rest is simple.

You can use your own imagination with these concepts.

What EMPDEV was suggesting was to have an expression like

COUNT (distinct Order_no) for District

This would definitely work but in my experience the performance of cube building has always been slower when the IQD has a COUNT summary function. However, you may experiment with both these concepts and evaluate for yourself what suits you best.

Have a nice day!