hi,

i'm new to Analysis Services and MDX, so thanks in advance for your
patience :-). i'd like to create a set of (calculated member) measures
based on a string field (column name: LABEL) of my fact table. let's
say for each record, the string contains the values A, B, C, D, or E.
i'd like to create eight measures -- CountA, PercentA, CountB, PercentB,
CountC, PercentC, CountOther, and PercentOther. these should be:

CountA = count of LABEL with a value of A
PercentA = CountA/# non-NULL LABEL instances
....
CountOther = count of anything that is not A, B, or C
PercentOther = CountOther/# non-NULL LABEL instances

how could i do this? can i create a numeric calculated member based on
a string comparison that can be aggregated?

also, if any of you have pointers/suggestions for good sources for
ramping up on creating these kinds of measures, i'd really appreciate it!

thanks much!

wg

MDX is unable to reference rdbms, only olap objects.

public @ the domain below
www.tomchester.net

"wg" <wg (AT) nospam-voiceme (DOT) org> wrote

I can think of a couple of approaches to this problem. Whereas I would
favor creating a dimension from the LABEL column, and using that to
define the calculated members, it would entail more up-front work. So
the other approach is:

Add a measure: CountAll, of Aggregation Function: "Count".

Add a series of measures to the cube, each using the default "Sum"
Aggregation Function. Define the Source Columns for these measures with
SQL expressions like:

- CountA => case MyFact.LABEL when 'A' then 1 else 0 end
- CountB => case MyFact.LABEL when 'B' then 1 else 0 end
- CountC => case MyFact.LABEL when 'C' then 1 else 0 end
- CountOther => case MyFact.LABEL when 'A' then 0 when 'B' then 0 when
'C' then 0 else 1 end

Add the Percent calculated members, defined like:

- PercentA => CountA/CountAll
..etc


The assumption above was that LABEL is non-null. To allow for nulls, the
SQL expressions should include coalesce().


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak,

thanks very much for the quick and helpful reply! what you suggested
worked great! i had no idea you could type in SQL for the Source Column
-- good thing to know. what kinds of SQL snippets can one enter --
e.g., what's the formal syntax expected for Source Column?

if i could, i also had some additional questions about your post:

a) what exactly is the alternative you mention, based on LABEL as a
dimension? prior to your solution, i had been hacking through, making
LABEL both a measure (e.g., COUNT(LABEL) ) as well as a dimension, and
then i forced the presentation to be a % in the client (e.g., in Excel).
is there something else that can be done with LABEL as a dimension?

b) could you explain what you mean by using coalesce()? i looked at the
docs about source column (also new to me -- learning T-SQL on the fly
:-) ), but i couldn't wrap my head around how to apply it to the Source
Column query.

thanks again for your time!

-wg

Deepak Puri wrote:

It's hard to locate any documentation of the formal SQL syntax allowed
in "Source Column" (maybe [MS] folks can help); but I suppose that any
SQL expression, using only columns from the cube fact table, should
work.

With LABEL both as a dimension (say, DimLabel) and a count measure (say,
LabelCount), the approach would be:

DimLabel would have a ragged hierarchy, like:

All -> A, B, C, Other
Other -> D, E

Then the Calculated Measures would be defined like:

- Measure.CountA => (Measure.LabelCount,DimLabel.A)
..
- Measure.CountOther => (Measure.LabelCount,DimLabel.Other)

- Measure.PercentA => Measure.CountA/
(Measure.LabelCount,DimLabel.All)
..

Coalesce() can be useful with nulls. It is short-hand for:
case when A is not null then A else B end

If you don't want to consider null values of LABEL at all,
then excluding such rows from the fact table is best; otherwise
LabelCount can be a Sum of 1's in non-null rows.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!