Hi All,

I m new to writing MDX ,can some one help me how/whts the function to
write MDX correspnding to a select statement which has 4 to 5 group by
fields and aggregates like sum,avg etc in the select statement.Thanks a lot
for the help.

Regards
Maneesh M

Hi Maneesh,

You can consult the BOL link below on MDX query fundamentals, or a book on
MDX such as "MDX Solutions". Since multidimensional data is organized by
dimensions with hierarchies, "grouping" by levels in those hierarchies is
already implicit in an MDX query. And Sum, Count or other aggregation
functions are part of the definition of the intrinsic measures (though
aggregation can be done dynamically in calculated members as well).

http://msdn2.microsoft.com/en-us/library/ms144785.aspx
The Basic MDX Query (MDX)

The basic Multidimensional Expressions (MDX) query is the SELECT
statement—the most frequently used query in MDX. By understanding how an MDX
SELECT statement must specify a result set, what the syntax of the SELECT
statement is, and how to create a simple query using the SELECT statement,
you will have a solid understanding of how to use MDX to query
multidimensional data.
....

"Maneesh" wrote:

Hi Deepak,

Thanks a lot for your reply. Actually I need to calculated AVG() & STDEV()
of couple of measures.The OLTP select statement for the same is as shown
below.

SELECT

colA,

colB,

colC,

AVG(colD),

stdev(colD)

FROM table1

GROUP BY

colA,

colB,

colC

Getting avg() is not an issue , But I stuck in getting the STDEV() as the
syntax says STDEV(<SET>,numeric expression).

Based on the above sample query , colA , colB and colC are my dimensions.
Now I really have no idea of generating a set which gives me a group by
effect of all the three fields.I tried using crossjoin as

nonemptycrossjoin(colA[].Members,colA .[ID].MEMBERs,colC.[ID].members)

Could you please tell me whether this approach is correct or any other
method for grouping to generate the <setexpression>



Thanks a lot,

Maneesh

"Maneesh" <mails2me (AT) gmail (DOT) com> wrote

Hi Maneesh,

Hopefully this previous NG post will help:

http://groups.google.com/group/micro...olap/msg/3938d
be165a016d5

From: Deepak Puri
Date: Tues, Feb 22 2005 12:07 am

If the Standard Deviation is to be computed at the fact table row
granularity, then it's useful to add a base measure that sums the square
of the fact table value:

http://groups-beta.google.com/group/...rver.olap/msg/
77e4ce2bffd7d72a


Avi Perez Sep 6 2004, 12:16 pm show options

Newsgroups: microsoft.public.sqlserver.olap
From: "Avi Perez" <avi.pe... (AT) irisbi (DOT) com>
Date: Mon, 6 Sep 2004 15:16:16 -0400


Subject: Re: MDX Stdev


this is a typical headache problem with OLAP - and one not really
addressed
well in AS2K


its the same issue with distinct count - because you really want to
evaluate
a formula based on the atomic level of your data - not an aggregated
level.
Even if you were to run a stdev in the database - you'll lose you
ability to
slice and dice it when you bring it into the cube - since a stdev
calculate
is not additive - and will produce different results depending on what
choices you make with the other dimensions.


The good news is that there is a solution - depending on your exact
setup.


the trick is to load up your base measures as follows:
load in a simple SUM measure (x)
load in a simple SUM measure of x squared (x2)
load in a counter called cnt.


Then using the formula for stdev, create a measure in the cube as
follows:


((x2 - ((x^2)/cnt))/cnt)^0.5


if you're using the sampled approach, you'll need to use


((x2 - ((x^2)/cnt))/(cnt-1))^0.5


best part is, it is dynamic, and will work as you twist the cube around.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

To follow up on implementation issues - this approach, though originally
discussed for AS 2000, should work fine in an AS 2005 implementation as
well.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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