Averaging on Multiple Levels.

Hello,

I am extremely new at MDX and Analysis Services.

I have read through chapter 7 of the fast track to MDX.It is a wonderful book.But it talks a lot about sales / time data.I know this is the way 99% of people will use cubes.I am trying to use a cube for another purpose and am having a hard time moving concepts on how we do things.I am the IT person for a Truss Plant in Northern Wisconsin.

This business is a job shop, 99% of the production is custom built trusses. Quoting the jobs for this type of business is always a struggle I would like to provide valuable information to management on how much it costs to build the trusses.I know this is an almost impossible task as the rules change constantly, but I believe we can use analysis services to help us find patterns in the data.I am hoping it has the capability to average measures on multiple levels without having to know what levels we want beforehand.

We have been collecting production times for about a year now. We don’t know exactly what we are looking for as of yet, but we have some ideas.I am thinking we need to start with pieces completed per man hour. But I would like to see how this changes based on all kinds of different dimensions (Truss Properties).

We have a measure called run_time_per_piece (Lumber pieces in the truss) . I would like to analyze what the average is based on different combinations of dimensions / hierarchies.I have tried using the standard AverageofChildren, but this is not working as I would think it should.I was expecting it to Average all returned values at each level no matter what the level is.One issue here is I don’t know what the hierarchies are going to be ahead of time. We only have a one measure we are looking at, at any one time but we don’t know what the hierarchies could be.

I would appreciate it if anyone could point me in the right direction. Am I using the right product for what I am trying to do?If so, how would I write the MDX statement to get the cube to average on all levels correctly.

Thanks in Advance.

Leo

Hi Leo,

If you browse the "Average Sales Amount" calculated measure in the Adventure Works cube, does it behave as you want, across hierarchies and levels? It is defined as:

Create Member CurrentCube.[Measures].[Average Sales Amount]

As [Measures].[Sales Amount]

/

[Measures].[Order Count],

Format_String = "Currency" ;

This is a common pattern for computing averages which work across all hierarchies, so it may help.

Average In MDX

Hi, Can anyone help me, I'm new in Analysis Services 2005,

how can I get the average, I did not understand the AVG function sintax

any suggestions?

Thanks.

Erika :)

Erika, have you looked at the AVG() function's description and usage examples at: http://msdn2.microsoft.com/en-us/library/ms146067.aspx?

--Artur

|||

Thank you Artur, yes I have looked these examples, I

have this code, and y run it in SQL Management Studio in the mdx query, and I get the right result but, how you

can put this code as a Calculated Member in Analysis Services 2005

WITH MEMBER AvgAge AS
'[Measures].[Age]/[Measures].[AgeCount]'
SELECT {AvgAge} ON 0,
{[Sex].[Sex].Members} ON 1
FROM PCPCube

The error says, incorrect syntax near WITH MEMBer, so what It's wrong.

Thanks

Erika :)

|||Hello Erika,

you have to put your calculation '[Measures].[Age]/[Measures].[AgeCount]' only into the expression field and the 'AvgAge' into the name field of the new Calculated Member.

Kind Regards,

Christian