Averages of virtual dimensions ..

I have a requirement to get averages for members of one dimension of the
virtual dimension member they belong to, give the total number of members
having that virtual dimension attribute. I've got this into a (simplified)
format relevant for the Foodmart cube -



Eg: Foodmart cube:



Store Type is virtual dimension from Store.

Given a list of stores, retrieve (all) the measures for a year members AND
the average measure values for the Store Type for each store.



Ie.

Info A//

Store 19 is a Deluxe Supermarket there are six (6) Deluxe Supermarkets

Store 9 is a mid size Grocery there are four (4) Mid Size Groceries

Store 10 is a Supermarket there are eight (8) Supermarkets.





Simple MDX in two parts is:



Select [Measures].Members on columns,

{

[Store].&[19],

[Store].&[9] ,

[Store].&[10] }

on rows

From HR

where

( [Time].&[1998] )



Info B//

Store Org Salary Count Number of Employees

Store 19 $5,901.39 888 74

Store 9 $8,275.52 228 19

Store 10 $25,276.31 744 62



Then,

Select [Measures].Members on columns,

{

[Store Type].[Store Type].&[Deluxe Supermarket],

[Store Type].[Store Type].&[Mid-Size Grocery] ,

[Store Type].[Store Type].&[Supermarket] }

on rows

From HR

where

( [Time].&[1998] )



Info C//

Store Type Org Salary Count Number of Employees

Deluxe Supermarket $104,311.38 5,328 444

Mid-Size Grocery $19,332.22 912 76

Supermarket $73,692.26 5,952 496



Appling the counts from A//, above, we get

Info D//

Store Type Org Salary Count Number of Employees

Deluxe Supermarket $17,385.23 888 74

Mid-Size Grocery $4,833.06 228 19

Supermarket $9,211.53 744 62



So the big question is how, in a single MDX statement does one retrieve:



All of Info B// and info D// - in effect

Store,

Salary,

Count,

Employee,

#ThisType,

Avg Sal this type,

Avg count this type,

Avg Employee this type



(the member I call #ThisType is difficult enough to get - I can only manage
it for a single store at a time!)



For a list of stores that each may have different Store Type...

Hi Chris,

Here's the solution:

WITH MEMBER MEASURES.NUMBERTHISTYPE AS 'COUNT(
FILTER(
UNION({STORE.CURRENTMEMBER} AS CURRENTSTORE, [Store].[Store Name].MEMBERS)
, STORE.CURRENTMEMBER.PROPERTIES("STORE TYPE") =
CURRENTSTORE.ITEM(0).ITEM(0).PROPERTIES("STORE TYPE")
)
)'
MEMBER MEASURES.AVERAGEEMPLOYEESTHISTYPE AS 'AVG(
FILTER(
UNION({STORE.CURRENTMEMBER} AS CURRENTSTORE2, [Store].[Store Name].MEMBERS)
, STORE.CURRENTMEMBER.PROPERTIES("STORE TYPE") =
CURRENTSTORE2.ITEM(0).ITEM(0).PROPERTIES("STORE TYPE")
)
, [Measures].[Number of Employees])'
SELECT
{MEASURES.MEMBERS, MEASURES.NUMBERTHISTYPE,
MEASURES.AVERAGEEMPLOYEESTHISTYPE} ON 0,
[Store].[Store Name].MEMBERS ON 1
FROM HR

The problem that you've been encountering is the fact that when you use a
FILTER (or similar) function, you lose the CURRENTMEMBER from the query and
replace it with the CURRENTMEMBER from the set you're iterating over, so
it's impossible to compare the two. So, in order to compare the Store Type
of the CURRENTMEMBER from the query with the same property on all the other
members on the same level what you first have to do is keep the old
currentmember in a named set defined within the calculated member
definition. In the first calculated member it is called CURRENTSTORE, and
then all you need to do is compare the Store Type of the first member in
that set with the Store Type of CURRENTMEMBER.

One other thing to note is that if you're using this trick twice in
different calculated members in a query, you must use a different name for
your named set - hence it's called CURRENTSTORE2 in the second calculated
measure in the example.

HTH,

Chris

--
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and
confers no rights.

"Chris Cameron" <chris_n_cameron (AT) hotmail (DOT) com> wrote

"Chris Cameron" <chris_n_cameron (AT) hotmail (DOT) com> wrote


One technique to do it is by following MDX:

with
member measures.type as 'Store.CurrentMember.Properties("Store Type")'
member measures.TotalOrgSalary as '( NonEmptyCrossJoin([store type].[store
type].members).Item(0).Item(0), Measures.[Org Salary], [Store].[All
Stores])'
member measures.sametype as 'Count(NonEmptyCrossJoin([Store].[Store
name].members, NonEmptyCrossJoin([store type].[store type].members) ) ) '
member measures.AvgOrgSalary AS ' TotalOrgSalary/sametype'
Select {[Type], [Org Salary], [TotalOrgSalary], sametype, AvgOrgSalary} on
columns,
{[Store].&[19],[Store].&[9] ,[Store].&[10] }on rows
From HR
where
( [Time].&[1998] )

It only takes care of Org Salary, other measures can be repeated using same
expression. This approach can be optimized too.

HTH,
Mosha

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

This is very nice technique - I like it.
After I've seen Chris's solution, I would like to simplify mine to

with
member measures.type as 'Store.CurrentMember.Properties("Store Type")'
member measures.AvgOrgSalary as 'Avg(NonEmptyCrossJoin([Store].[Store
name].members, NonEmptyCrossJoin([store type].[store type].members) ),
Measures.[Org Salary] ) '
Select {[Type], [Org Salary], AvgOrgSalary} on columns,
{[Store].&[19],[Store].&[9] ,[Store].&[10] }on rows
From HR
where
( [Time].&[1998] )


--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

I think in this case, where there's a virtual dimension present, Mosha's
approach probably performs better than mine.

--
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and
confers no rights.

"Mosha Pasumansky [MS]" <moshap (AT) microsoft (DOT) com> wrote

Very clever Chris, that's a keeper!

tom @ the domain below
www.tomchester.net

To Chris and Mosha:

Thanks - much appreciated!!!

Obviously (!!) my problem is _not_ with the Foodmart cubes so I have some
translation work to perform, but this is a start better than I could have
hoped for.