Update in SSAS Cube using MDX

0
votes

I'm new in SSAS cube and I'm trying to learn how to update a cube. I looked at the samples and tried to make my own but I am getting an error. My question is how do I make the update cube query?

There is an ExchangeRate measure group that has the Rate and having currencyID and Period to tell which month the rate will be applied to. There is another measure group of Sales where it has customerID, ProductID, DateKey, etc. and Amount in it.

I would like to update the Amount to become Amount * Rate in that Sales Measure Group. Can anyone help me create the update cube query for that or any other way to calculate and update it? I tried to create the update query on my own but I'm having error. So, obviously I'm not doing it right.

Exchange Rate Measure Group

Sales Measure Group

I added the view of the 2 measure when you browse it. Of course it doesn't include all fields. I hope this could help clear things. I just want the amount to be multiplied by the rate. They are both linked to the currency dimension.

1
ssasmdxcube
Too broad. Waaay too broad. – Mitch Wheat
I'm sorry. I updated the question. May I know what I could add to make it less broad? – user6037348
One does not generally "update cubes" with queries. One does it by opening the project in BIDS, making desired changes, and deploying the project. Is there some tutorial you are following where it tells you to update the cube with a query? Can you post the "update query" you say you tried to run, and post the error message that it generated? – Tab Alleman
My apology. I should have started with the story. They told me that oracle essbase can do the exchange rate calculation on the sales amount in the essbase cube. I am trying to do the same. below is the select query with a calculated member – user6037348
with member sales as [Measures].[Local Sales] * [Measures].[Rate] SELECT NON EMPTY { [Measures].[Local Sales] } ON COLUMNS, NON EMPTY { ([SSAS Dim Date].[Invoice Date].[Invoice Date].ALLMEMBERS * [SSAS Dim Period].[Period].[Period].ALLMEMBERS * [SSAS Dim Currency].[Currency ID].[Currency ID].ALLMEMBERS * [SSAS Dim Customer].[CustomerID].[Cust ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [SSAS Dim Period].[Period].&[201512] } ) ON COLUMNS FROM [MM Sales]) – user6037348

1 Answers

0
votes

Thanks Tab Alleman. Your comment make me want to look on just doing the calculation on the DW and just push it to the cube again. It works and its way better.