OK, Here's another one...
I want to create a calculated member that compares the current member to the average of the other members at the same level the member is in.
The catch is that I want this to be a calculated member in the cube that works regardless of what dimensions/hierarchies the data is broken down with.
The MDX below shows what I want to do except that it requires the SERIAL_NUM hierarchy to be hardcoded:
Code Snippet
WITH MEMBER [Avg] AS AVG([SERIAL_NUM].Siblings, Measures.[Count Of Failures])
SELECT NON EMPTY { [Measures].[Count of Failures] , [Avg]} ON COLUMNS, NON EMPTY { ([Vehicle Info].[SERIAL_NUM].[SERIAL_NUM].ALLMEMBERS ) } ...
The end result should be something like this:
But I again, I don't know what dimensions hierarchies will be used later on, I just want all the values the user is currently looking at be included in the calculated measure. If they replaced SERIAL_NUM with MODEL as the dimension, I want it to perform the same calculated on the data provided from that query.
I tried something like MEASURES.SIBLINGS, MEASURES.MEMBERS, etc, but they returned only the current value, as it appears to be the lowest hierarchy so it has no siblings.
(The end result, BTW, is not the average, but the number of std dev's a certain value is from the average of the other values, essentially a dynamic outlyer flag. So it'll be (Count of Failures - Avg (siblings)) / StdDev (siblings), I'm just trying to keep things simple)
Geof
Well, I thought I had it for a moment...
The function I tried was Axis(0)...
Code Snippet
IIF(MEASURES.[Count of Failures] = Null, Null,
(MEASURES.[Count of Failures] - AVG(AXIS(0), MEASURES.[Count of Failures])) / StDev(Axis(0), MEASURES.[Count of Failures])
)
Seems like it should work, but it's not giving the correct data. The data varies according to where I've drilled down to, and isn't consistent. Perhaps I could filter the Axis(0) set somehow? It looks like it's including the subtotal members from each dimension (the more dimensions I add, the more rows are reported in the Axis(0) set)
|||You might be able to use a combination of generate and/or filter to filter out the dimension in MDX, but given that you don't really know how many dimensions could be stacked on the axis, I can't envisage how it would really work.
The other problem with Axis(0) is that it refers to the column axis, if a user flips the query around to put the dimension on the rows you would need to use Axis(1), but there is not easy way to tell what the user is doing.
How many dimensions would you want to use with this calculation? Would it be feasable to create a calculated measure for each applicable dimension? This might also lead to some interesting analysis opportunities.
|||The biggest problem I've got with Axis(0) is that it include the subtotals and grand totals as simply another row, and since I don't have a fixed dimension to work with, I've been unable to filter them out of the set.
The Rows/Columns issue you mention is a problem, but not the end of the world. The users doing the queries will be reasonably experienced, just not enough to craft their own MDX. I can warn them about that scenario (if the other problem can be fixed)
How many dimensions? So far 4 is the largest number for the basic queries currently planned, though when people start wanting more estoteric comparisons, it will increase. So that could be feasible.
|||This sort of filtering might be something that is best suited to a .Net stored proc. I think you might need to iterate over the set a couple of times to figure out what you are looking at and which members are at the lowest level on the axis. The best set of stored proc samples I know if is at a project which I contribute to at www.codeplex.com/asstoredprocedures
No comments:
Post a Comment