SOLVED: Emulating PowerPlay Category Counts

[MFGF]

Hi all,

I came across an interesting challenge today. I was asked how it could be possible to emulate category count measures in Transformer in a dimensional report not using a PowerCube. I (naively) assumed it would be easy - just add a calculation to count( currentMeasure within set <desired level of hierarchy>)

While this works fine when the members of the hierarchy are not used in the report, it doesn't, however, emulate what Transformer does. Let me explain further...

The expression I used returns the number of members in the desired level, regardless of the context used in rows or columns. For example, if I have a hierarchy of Product Line > Product Type > Product, and my expression is count(currentMeasure within set descendants([Time],2)) I see the number of Product Type members with measure values associated. When I don't have any part of my Products hierarchy in the crosstab this emulates a Category Count measure in Transformer exactly, which is good...

However, it still displays the same values when I have the Product Line level members as my rows - each row shows the total number of Product Type members across the whole level. This is different from a Transformer Category Count measure, which displays the number of Product Type members within the context of each Product Line member.

When I drill down a level to a Product Type, the expression result shows the same count across the whole Product Type level whereas the Category Count measure shows 1.

The images below show this:

1. Expression (against my Dynamic Cube) to count the Product Type members - Product Lines in rows:


2. Expression (againstr my Dynamic Cube) to count the Product Type members - Product Types (children of Golf Equipment) in rows:


3. Category Count measure in my Powercube to count Product Type categories - Product Lines in rows:


4. Category Count measure in my PowerCube to count Product Type categories - Product Types (children of Golf Equipment) in rows:


My challenge is to try to figure out how to code an expression that emulates the way the Category Count measure works. I can't use a Category Count measure because the source is a Dynamic Cube, not a PowerCube. So... Anyone tried this? Anyone have any ideas?

Cheers!

MF.

[CognosPaul]

Dynamic cubes is on my ever-growing list of things I need to start playing with. For some reason, I was under the impression dynamic cubes had a distinct count capability.

You're almost there, but there are a few parts missing.

1. currentMeasure probably won't resolve to anything, as you're using the count as the default measure. Try replacing that with quantity.
2. By simply counting the descendants, the query isn't likely to suppress nulls.

Try using this expression as the default measure:

count([Quantity] within set filter(descendants(currentMember([Product Hierarchy]), [Product Level]]),[Quantity]>0)

By using the filter, it's evaluating the set for each intersection, finding all of the products that have a quantity greater than zero.

This isn't necessarily the most efficient way to do it, however. I'm not familiar enough with the DQM engine to tell you if triggering the database suppression mechanism (perhaps using the generate function) would be better.

[MFGF]

Thanks for this Paul!

This is almost exactly perfect! You are a genius, my friend!!!

The one thing it doesn't do is to keep the "1" count when I drill down below Product Type in the crosstab. The count for each product appears as 0, with the Product Type summary still showing 1.

Here are the wonderfully correct counts with Product Lines in the rows:



Here are the wonderfully correct counts with Product Types in the rows:



Here are the results with Products in the rows - zeros are returned:



To compare, here are the Category Counts for Product Type done in Transformer with Products in the rows:



Is there a nifty trick to make the counts available from levels below the Product Type level as well as above/on the level? Or is this as good as it gets? :)

Thanks SO much for your help to get this far - I really appreciate it!!

Cheers!

MF.

[CognosPaul]

The zeros make sense, since the number of descendants of Hailstorm Steel Irons on the product type level is zero. The same way that none of my parents are descended from me. (Well, except for maybe Moe Jaffe.)

We could always do it the stupid way:

if(roleValue('_levelNumber',currentMember([Product Hierarchy])) > ordinal([Product Type Level]))
then (count([Quantity] within set filter(ancestor(currentMember([Product Hierarchy]),[Product Type Level]),[Quantity]>0))
else (count([Quantity] within set filter(descendants(currentMember([Product Hierarchy]),[Product Type Level]),[Quantity]>0))


I'm still a bit concerned about the performance here. filters tend to be an expensive operation, and they bypass the quick OLAP suppression techniques. Of course with this being dynamic cubes all bets are off.

[MFGF]

Hi Paul,

It is not working :(


















Oh! Mark! COME ON! REALLY??? What a muppet you are!! You know the rules - you wrote them (and got your loved one to sanity-check them and make sure you were not being a despot!) Forum Etiquette applies to muppets just as much as to real, clever Cognoisers who know what they are talking about! You must do better or else ban yourself!!




















Ha ha! Sorry! Couldn't resist! I'm now waiting for the flood of posts pointing me to the Forum Etiquette post, Paragraph 1 Point d :)

The logic here exactly emulates the results you get with a Category Count measure in Transformer, and is exactly what was required. Thanks a million!!

I had to add some missing close parentheses to the expression to make it valid, and the final, working expression is this:

if (roleValue('_levelNumber',currentMember([Product Hierarchy])) > ordinal([Product Type Level]))
then (count([Quantity] within set filter(ancestor(currentMember([Product Hierarchy]), [Product Type Level]), [Quantity]>0)))
else (count([Quantity] within set filter(descendants(currentMember([Product Hierarchy]), [Product Type Level]), [Quantity]>0)))

This was for use in a Dynamic Cube, so in Cube Designer I defined a Calculated Measure (on the Calculated Measures tab) and built this expression.

Paul, you are a star!!

MF.

[CognosPaul]

Hi Mark,

I'm afraid that you're in breach of the Forum Etiquette Guidelines as posted by MFGF here. As punishment, you will now listen to Poet Master Grunthos the Flatulent recite his poem "Ode to a Small Lump of Green Putty I Found in My Armpit One Midsummer Morning".




Glad it worked!

[MFGF]

Hi Mark,

I'm afraid that you're in breach of the Forum Etiquette Guidelines as posted by MFGF here. As punishment, you will now listen to Poet Master Grunthos the Flatulent recite his poem "Ode to a Small Lump of Green Putty I Found in My Armpit One Midsummer Morning".




Glad it worked!

Oh my! Azgoth poetry is dangerous stuff! It's most definitely worse than the humdrum Vogon variety. I have to admit to never having plucked up courage to read any of the twelve volumes of "My Favourite Bathtime Gurgles", I'm ashamed to admit. However, in penance for my etiquette transgression, I shall hereby endure a nausea-inducing Vogon poem. This is, for completeness, the full, unedited version the Vogon Captain wrote...

O freddled gruntbuggly
Thy micturations are to me
As plurdled gabbleblotchits on a lurgid bee  Aaaaagh!! Aaaaaaaaaaaagggghhhhhhh!!!
That mordiously hath bitled out its earted jurtles
Into a rancid festering cup of tea

Now the jurpling slayjid agrocrustules  Please! No more!! I'm begging you!! Stop!!!
Are splurping hagrilly up the axlegrurts
And living glupules frat and slipulate
Like jowling meated liverslime

Gashee morphousite, thou expungiest quoopisk!  My ears!! My ears are bleeding!!
Fripping lyshus wimbgunts, awhilst moongrovenly kormzibs.
Bleem miserable venchit! Bleem forever mestinglish asunder frapt!
Gerond withoutitude form into formless bloit, why not then? Moose!

Groop, I implore thee, That's it! I'm never doing anything again to break forum etiquette!
my foonting turlingdromes I PROMISE!!! PLEASE STOP!!!!
And hooptiously drangle me with crinkly bindlewurdles
Or else I shall rend thee in the gobberwarts with my blurglecruncheon
See if I don't. Take me to hospital! I need CPR! Thank goodness that's over!

There! That wasn't too hard. In fact, I am on such a roll that I shall recite a further poem from the same author:

Oh blobit of dribble NOOOOO Not more!! Please! For the love of anything dear...
Oozing from the upturned corner of my mouth
You look to me,
Like you should be,
The thing that dropeth from the cloud

A tiny bit of thee is stuck upon my lip
A little more is stuck up my nose
Some has adhered to my hip

My eyes are open and glassy
My snot is thick and green
And from my ears,
Something obscene appears,
And I think it might be me. Please! Make it stop! Make it stop!


I think that's penance enough for one day!

If you're interested, you can generate your own poetry here

Thanks for all your help!!!

MF.

[Lynn]

You guys are simply too much fun
;D

[bdbits]

Couldn't you have just hard-coded 42 as the count?

[MFGF]

Couldn't you have just hard-coded 42 as the count?

Only after seven and a half million years of calculation :)

[BigChris]

Trying to think of a way to work in "So long and thanks for all the fish"...

[Lynn]

I am both surprised and impressed that you are all so familiar with the great literature of Jane Austen.

;)

[MFGF]

I am both surprised and impressed that you are all so familiar with the great literature of Jane Austen.

;)

Oh! I know who you mean! The wife of Steve? Right?? :) It's alleged she married him thinking he was an actor because he kept mentioning an Oscar ;)