Re: MDX Stdev
From: Dave A (dave_at_sigmasolutionsdonotspamme.com.au)
Date: 09/07/04
- Next message: Dave Wickert [MSFT]: "Re: Do missing values get stored?"
- Previous message: Cazwell: "Comparing a Date to Time Dimension Members"
- In reply to: Avi Perez: "Re: MDX Stdev"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 8 Sep 2004 08:30:23 +1000
Wow - thanks! I have not tried it yet but this is an awesome suggestion. I
would never have thought of it.
Dave
"Avi Perez" <avi.perez@irisbi.com> wrote in message
news:uf1OAYElEHA.1712@TK2MSFTNGP09.phx.gbl...
> 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.
>
>
> "Dave A" <dave@sigmasolutionsdonotspamme.com.au> wrote in message
> news:%23c5N8u9kEHA.2500@TK2MSFTNGP09.phx.gbl...
> > I have a MDX problem that I have to solve (and I thought it would be
> simple)
> > but is proving to exceedingly difficult.
> >
> > I need to create the standard deviation measure.
> >
> > I have time dimension called 'Time Financial' with the values of
> '2003-2004'
> > and '2004-2005'. I have some geography dimensions too
> > Country->State->SD->SSD->PostCode->SLA. My fact is 'download speed'
which
> is
> > the user's average download speed for that financial year (it measures
> > broadband speeds). I also have 'test count' which is the total tests a
> user
> > has performed.
> >
> > I can readily create an average ('Avg Download') by doing a
> '[Measures].[Sum
> > of Download]/[Measures].[Count Of Users]'. I have verified that this
works
> > by running SQL queries at a variety of geographical levels.
> >
> > However, I also need to create a standard deviation. This is where the
> > wheels fall off.
> >
> > I can create a calculated member like:
> >
> > Stdev([Time Financial].[All Time Financial].children, [Measures].[Avg
> > Download])
> >
> > But after some checking I released this is wrong since it is the
standard
> > deviation of 'Avg Download' instead of the standard deviation of the
> > 'Download'. I think it is effectively taking the standard deviation of
the
> > 2003-2004 average and the 2004-2005 average. Its standard deviation is
> > therefore very small.
> >
> > I really want to go Stdev([Time Financial].[All Time
Financial].children,
> > [Fact].[Download]) but this syntax is not allowed since the second
> parameter
> > needs to be a measure. Can I convert an array of [Fact].[Download] into
an
> > measure?
> >
> > Does anyone have any ideas? I really am an amateur at this MDX stuff. I
> have
> > searched the web with little luck. Two people have had similar problems
> with
> > no real solution.
> >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&safe=off&threadm=2205585e.0407132108.28f71d96%40posting.google.com&rnum=1&prev=/groups%3Fq%3Dstdev%2Bmdx%26hl%3Den%26lr%3D%26ie%3DUTF-8%26c2coff%3D1%26safe%3Doff%26selm%3D2205585e.0407132108.28f71d96%2540posting.google.com%26rnum%3D1
> >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&safe=off&threadm=3124258.1058482161%40dbforums.com&rnum=2&prev=/groups%3Fq%3Dstdev%2Bmdx%26hl%3Den%26lr%3D%26ie%3DUTF-8%26c2coff%3D1%26safe%3Doff%26selm%3D3124258.1058482161%2540dbforums.com%26rnum%3D2
> >
> > In AS, in the 'aggregate function' there is Sum, Count, Min, Max, and
> > Distinct Count. Why isn't there also Average, Standard Deviation, etc ?
Is
> > it in the next version.
> >
> > thanks!
> >
> > Dave
> >
> >
>
>
- Next message: Dave Wickert [MSFT]: "Re: Do missing values get stored?"
- Previous message: Cazwell: "Comparing a Date to Time Dimension Members"
- In reply to: Avi Perez: "Re: MDX Stdev"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
