MS SQL Server OLAP - A troublesome MDX-statement once again... :(

Author

A troublesome MDX-statement once again... :(

Bernhard

2005-04-25, 8:25 pm

Hi everyone.

I'm having trouble to get a MDX-statement to work and I hope somebody
around here may point me to some helpful tutorials or similar stuff.

To start with, I'm a statistican who is quite familar with SQL queries
but I've not managed yet to create a MDX-statement to calculate a
specific computed measure. Let me explain shortly the setup of my (very
simple) data-cube. It consists of only 6 dimensions. Those are:

- region (9 parameter values, star-scheme)
- age (10 parameter values, star-scheme)
- education (4 parameter values, star-scheme)
- nationality (4 parameter values, star-scheme)
- labour-force status (4 parameter values, star-scheme)
- sex (2 parameter values, star-scheme)

From a sample survey the total population in any of the possible
subgroups is estimated with some (known) weights w. To make a long story
short, the values in the cells of the data-cube are point estimates for
the domain total in the population.

My task is now, to compute a variance estimator with the following
formuar using a MDX-Statement. The variance for a small domain "d"
within a specific region "r" is given by:

Var(total_d) = N^2 * (1-f_r) * (pd*qd)/(n-1)

with
- N... total population size (sum of estimated totals over all regions)
- n_r...sample size for region r
- f_r...sample fraction, which is different for each region. It can be
calculated as: n_r/N; where n_r is the number of objects in the
sample that belong to region
- nd... number of sample objects in small domain "d"
- pd... nd / n_r
- qd ...= 1-pd

My problem is that I have no clue how to create an MDX-Statement that
calculates the variance values simultanely for all the cells that are
returned as a result set from the data-cube.

I'd be very glad if someone has some hints for me. Really, and I'd
happily pay lots of beer of stuff for the one who may guide me to a
successfull MDX-statement :)

Greetings,
Bernhard

Deepak Puri

2005-04-25, 8:25 pm

Hi Bernhard,

I'm trying to map your formula to the data in your cube:

- Is N the total number of fact rows/samples in cube?

- How is the domain "d" within a region identified?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Bernhard

2005-04-26, 3:24 am

Hi Deepak.

First off, thanks a lot for your help. To answer your questions:

N can be calculated as: sum over all regions of the values in the fact
table, where no restrictions by the dimensions are given. N is therefore
the number of people living in the population.

Eg: N = people living in region 1 + people living in region 2 + ...
people living in region 2 (where people living in region r is the value
of the fact table when only the variable "region" is used as dimension).

The small domain "d" is the current subgrup and is identified as the
current group which is evaluated by using some values of the dimension
variable.

For example, I could be interested in the number of people living in
region "r", that are in a certain age-group and are currently unimployed
(=labour force status = "u").

Therefore, a small domain "d" is just a special view of the datacube
within a region.

Greets,
Bernhard

Deepak Puri schrieb:
> Hi Bernhard,
>
> I'm trying to map your formula to the data in your cube:
>
> - Is N the total number of fact rows/samples in cube?
>
> - How is the domain "d" within a region identified?
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server

Bernhard

2005-04-28, 7:23 am

I (hope!) that I solved my problem except by adding quite a lot
dimensions that contain the values I need to calculate the variance.
There is only one (I hope little) problem to solve.

I want to compute the rMSE for which I need a "SQRT()" statement.
Unfortumately, when I want to compute this measure within the
anlysis-manager I get an error message which tells me basically that
"SQRT()" is an unknown statement.

Anyone who knows how to compute the square root of a number using an
function? Thanks a lot :)

Bernhard

Deepak Puri

2005-04-28, 8:25 pm

Hi Bernhard,

Impressive that you were able to implement all those esoteric
statistical formulii in a cube! Anyway, you can try the MDX "^"
operator, using a power of 0.5:

http://groups-beta.google.com/group...erver.olap/msg/
9ea4917e0f679ba7[col
or=darkred]
Newsgroups: microsoft.public.sqlserver.olap
From: "Mosha Pasumansky [MS]"
Date: Mon, 10 Mar 2003 22:14:19 -0800

Subject: Re: SQR Function
Reply to Author | Forward | Print | View Thread | Show original | Report
Abuse

> the vba!sqr function (or short sqr) does the trick.

Alternatively, you could use "^" operator in MDX - this should provide
better performance then calling into VBA. Example:

WITH MEMBER Measures.X AS ' 4 ^ 0.5 '
SELECT {X} on 0 FROM Sales

====================
==========_=========
===========
Mosha Pasumansky (moshap at microsoft dot com)
Development Lead in the Microsoft Analysis Server team
More info at http://www.mosha.com/msolap
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
====================
==========_=========
===========[color=da
rkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Bernhard

2005-04-29, 3:25 am

Hi Deepak. Actually, I'm only half way done yet. Some problems still
remain. Probably the biggest ist, that I need different formulas
conditional wheater the dimensional variable "region" is used as a
filter or not. When subgroups of the entire population are considered
(eg: all male unemployed people) I just can't sum up the calculated
variance over the regions but I have to use a different formula.

So I need some kind of Iif()-Statement that returns wheather the
dim-variable "region" is used to filter the cube or not. I've tried with
several hierarchy functions, but unfortunately I didn't manage to find
out how to grab the information I need.

Concerning my square-root question. I did all my computed measures via
the "wizard" that is included in the analysis manager. So I didn't find
a suitable function. Using Power^0.5 sounds reasonable :) Thanks.

Bernhard

Deepak Puri schrieb:
> Hi Bernhard,
>
> Impressive that you were able to implement all those esoteric
> statistical formulii in a cube! Anyway, you can try the MDX "^"
> operator, using a power of 0.5:

Sponsored Links