Unanswered: Reusing calculated vales in Analysis Server 2000
I am new to Analytical Services and MDX and I have to solve a problem with a cube.
The cube shows polling results. In the current cube, the measures (subtotal and totals) origin from pre-calculated columns in the staging relational table. Each subtotal or total is a number from 0 to 10. The aggregate functions on these measures is SUM. Each record in the staging table has an extra Cnt column with value 1.
In the Calculated members, the function takes the (sum of the) relevant measure and divides it by (the sum of) Cnt, thereby calculating an average number that is again between 0 and 10. Given two polls, one with an overall score (AppreciationIndex AI) of 5 and another with an overall score of 10, the result would be 7.5.
Suppose that the poll with the low score of 5 was the result of a poll filled in by 3 people and the poll with the high score of 10 was the result of a poll filled in by 300 people. The importance or weight of the 300-poll is 100 times higher that that of the other poll and the result should be a lot closer to 10. And that is what our new cube should give.
I think we can’t rely on pre-calculated (sub)totals and that the calculations have to happen at run time, depending on the selections made in the pivot table. There are three levels of (sub)total calculations, first calculating question subtotals (QI), then group subtotals (GI) and finally the final result (AI). The QI is calculated based on the measures. The GI’s are defined as certain combinations of QI’s and the AI is defined as a combination of QI’s.
I have made a new cube. First I have defined a Measure for each measure column in the staging relational table. Then I created Calculated Members to calculate the QI’s, GI’s and AI. But they are all calculated using the basics Measures, though it would be a lot better if GI could use the values of the QI’s , and AI could use the values of the GI’s.
The cube gives the desired results, but sometimes it is so slow! While drilling down it sometimes crashes (or takes forever to present the results?). The reason is that I have not found a way to reuse subtotals (Calculated Members). AS gives “circular dependency” errors when I try.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages