Named Calculations for measures with formula

Hi,

The details of background are avaliable in this prev post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=550862&SiteID=1

After edward melomed's advice I tried to create a named calculation for the respective Fact table.

The problem is, let us say I have a two measures M1 and M2 in a cube. Now I want a named calculation say 'NM3' , that contains the formula M1 + M2. So I write the following SQL in the named calculation:

(select M1 + M2 from fact table)

although the query is right, while processing I get the error that the query returns multiple values. So now how do I specify the current row? or how do I specify something similar to a cursor?

I know that I can achieve this using calculated measures instead of named calculations, but then some formulae contain functions like decode, sum of metrics over a paticular dimension, average of metrics over a paticular dimension etc.

also, this entire process is automated using AMO. The details of the metrics and their formulae are got from an existing oracle data warehouse. (see the prev post for more details)

so how can this be achieved generally? and considering that it has to be automated using AMO?

Thanks and Regards

Hello. Why do you write (select M1 + M2 from fact table) in your named calculation?

I have tried m1 + m2 in the named calculation by using SalesAmount + TaxAmt from Adventure works.

You do not need select in the named calculation.

The cube process and the new measure, from the named calculation, seems to be correct in the browser.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thank you!

That was dumb of me. Actually I saw the microsoft help, where the example contained a select statement.

1. So, does the actual calculation take place at the database server (backend source database)?

2. and does that also mean that I can use functions like decode?

3. can I use AMO to do this programmatically?

Finally, what is the difference between Named Calculations, Named Sets and Named queries. (do we have anything else thats named.....?) apart from named calculation are created from DSV, named sets in the calculation tab.

Regards

|||

1. It is done in the DSV so I suspect that it is done in the cube only.

2. Decode? Is that PLSQL(Oracle)? If so then TSQL CASE is the same.

3. I do not know.

4. Named queries(see them as views) and named calculations(columns) are DSV-features. Named Sets are MDX and Cube related features. With them you build subsets of dimension members.

Regards

Thomas Ivarsson