Distinct Count in PowerPlay Transformer

[monsky]

Several users have attempted to find a solution on this "classic" problem. So here is another attempt hoping someone would be able to address this finally.

I have a transaction file composed of agency no., agent no., and policy.

Agency No. Agent No. Policy No.
001 001 000001
001 002 000001
002 003 000002
002 004 000002

Agency No and Agent No are my dimensions. I use Policy No for my measure in counting the policies for each of the dimension. I need to count the policy only once dynamically for a dimension.

For example:

Agency No. Policy Count
001 1
002 1

Agent No. Policy Count
001 1
002 1
003 1
004 1

Total Policy Count
2


Using Category Count to create the Policy Count may pose problems. I was hoping that Tranformer has a roll-up function for a distinct count but when I checked it I did not find anything.

 

[yksvaan]

I know the following might sound like plan B (or Plan 9), but it takes little exercise to workaround the problem.


Actually we do NOT need a extra level as I presented earlier, but the mechnics is the same:
On the level from where the measurement must not rollup (Agent Class), we subtract everything that is rolling up from levels beneath to upper levels to eliminate them.
And we calculate new values on the next upper level, which can then rollup if the hierarchy is unique upwards (which we presume in this case).
We put the subtraction to a blank catgory, which are suppressed.

You have three standard measures and one calculated measure which is the Policy Count. The three standard measures are excluded from cube defined in cube's properties.


Your dimension in this example will look like this. Levels Agency and Agent have to be unique (level properties, source value). I wount use the No. suffix in any of the names.

Sector
Agency
Agent Class
Agent

Set Agent Class level's Inclusion setting to Suppress blanks.

Here are the measures and what kind of query to provide values for them. PC is for policy count:

premium : this is your basic query with Sector, Agency, Agent Class, Agent, Policy Number and maybe additional measures, which can be rolled-up as sum
this query gives the hierarchy in this example
Agent PC : SELECT Agent, COUNT(DISTINCT Policy Number) AS "Agent PC" FROM data GROUP BY Agent
Agengy PC : SELECT Agengy, COUNT(DISTINCT Policy Number) AS "Agengy PC" FROM data GROUP BY Agency
AgencyRU PC : SELECT Agengy, COUNT(DISTINCT Policy Number) AS "AgencyRU PC" FROM data GROUP BY Agency, Agent
Policy Count : Agency PC + Agent PC - AgencyRU

If you can't use COUNT(DISTINCT , you should be able to do all of them with sub-queries, the last one being:
SELECT Agency, COUNT(*) AS "AgencyRU PC" FROM ( SELECT DISTINCT Agency, Agent, Policy Number) FROM data ) DERIV GROUP BY Agency

In then datasource of AgentRU PC in Transformer, insert a calculated column called Agent Class, with text value as empty string.

On the levels Agency and Agent Class check the Allocation tab that measures have the setting Do Not Allocate.


Note: all measures have default Rollup sum (not Count)


Cheers

 

[yksvaan]

Have to mention that if there wasn't the Agent Class level, you would need an intermeditae level. Now Agent Class can be used for that purpose.

Cheers

 

[monsky]

hi yksvaan,

I'll try your suggestion. Thanks.

Again, I'm open to any other suggestions that you guys might still have.

 

[monsky]

Hi yksvaan,

I haven't gone back to you about this solution. Apparently haven't really tried this successfully.

Please bear with me as I'm not a real expert in PowerPlay yet.

Am I right to assume that each of those queries are to be saved as individual .iqds?

If so, when I add them as data sources how do I resolve the problem of scoping?

Do I create these sqls manually?

Does this mean I need to have 3 measures in the measures window?

I create iqds in Impromptu using the Query builder dialog box. I select which columns I will use, run it for a while and save it as an iqd.