Databases
StackOverflow2013
Postsdbo
POMDX calculation has wrong order of precendence
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMDX calculation has wrong order of precendence
    primarykey
    data
    text
    <p>Im having an issue with an MDX query, and I think it boils down to the order of precedence between calculating an aggregate and a calculated member.</p> <p>Let me start with the underlying data, which revolves around a valuation (which has a date, and some other data such as a member type, a scheme - and crucially for this question; a loading factor) and an associated value. </p> <h2>The data</h2> <p>Valuation Table</p> <pre><code>Id | Valuation Date | Member Type | Scheme | Loading Factor ============================================================= 1 | 2010-01-01 | TypeA | Scheme X | 0.02 2 | 2010-01-01 | TypeB | Scheme X | 0.02 3 | 2010-01-01 | TypeA | Scheme Y | 0.02 4 | 2010-01-01 | TypeB | Scheme Y | 0.02 </code></pre> <p>ValuationValue table</p> <pre><code>ValuationId | Value ==================== 1 | 1000.0 2 | 2000.0 3 | 3000.0 4 | 4000.0 </code></pre> <p>This, when loaded into a cube has a Valuation dimension with attributes MemberType, Scheme and date. And a cube with Measure group ValuationValue containing Value measure, and a Valuation measure group containing Loading Factor like so:</p> <pre><code>Cube -Measure Groups - Valuation |_Loading Factor - ValuationValue |_Value - Dimensions - Valuation |_MemberType |_Scheme |_Date </code></pre> <h2>The question</h2> <p>Loading factor is used to load the Value, think of it like a tax, so 0.02 means "Loading amount is 2% of the value". When returning Value from a query, I need to also calculate the amount to load this value by. A typical query might look like</p> <pre><code>SELECT { [Measures].[Value] } ON 0, [Valuation].[Scheme] ON 1 FROM Cube </code></pre> <p>This would return 2 rows, and as you can see by comparing to the data above it correctly sums across memberType:</p> <pre><code>Scheme | Value ================= Scheme X | 3000.0 Scheme Y | 7000.0 </code></pre> <p>Now, if I try to calculate my loading factor in that query, all goes wrong - i'll demonstrate. Given the following query:</p> <pre><code>WITH MEMBER [Measures].[Loading Value] AS ( [Measures].[Value] * [Measures].[Loading Factor] ) SELECT { [Measures].[Value] , [Measures].[Loading Value] } ON 0, [Valuation].[Scheme] ON 1 FROM Cube </code></pre> <p>I get the result</p> <pre><code>Scheme | Value | Loading Value ================================= Scheme X | 3000.0 | 120.0 Scheme Y | 7000.0 | 280.0 </code></pre> <p>Basically, what is happening is that it is suming my Loading Factor and then multiplying that by the Sum of my values(The first row above should be <code>1000 * 0.02 + 2000 * 0.02 = 60</code>. Instead it's calculating <code>3000 * 0.04 = 120</code>).</p> <p>This is of course a contrived example, my actual structure is a bit more complex - but I think this demonstrates the problem. I was under the impression that the calculated member in the example above should occur on a row-by-row basis, instead of at the end of an aggration of my Value measure.</p> <p>Thanks for any replies.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USvimuth
    1. USJamiec
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMDX calculation has wrong order of precendence
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload