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.