Computer question - help with MS OLAP.

Factual Questions
1

Anyone familiar with MS OLAP, please help?

I have a calculated measure. I want to save the value generated by the calculated measure to a physical measure so that it goes to the write back table and from where I will move it to the fact table. How do I copy the value from a calculated member to a physical measure in the OLAP cube?

Thanks.

2

I’m not too familiar with ‘write back tables’ or moving stuff OUT of an OLAP cube into the fact table, but I suspect this is impossible. Physical measures in a cube are only saved when the cube is processed UP from the fact table, AFAIK.

Is there any way that you could make this measure a physical one from the start by adding a computed column in the fact table or building a fact view on top of your old fact table? That’s probably the way I’d approach something in this area, though I don’t know all of the details of your cube and what you want to do with it…

[Looks up ‘write back’ in Books Online and finds the entry for Write-Enabled Cubes.]

Okay, based on this I think that I was on the ‘write track’, as it were. :smiley: I don’t see anything here about how calculated measures fit into write-enabled cubes, but I guess that there’s no allowance made for writing into them, but a variation of my workaround will work:

  • Add the field for your ‘formerly calculated’ measure into the fact table and populate it with an UPDATE statement based on your original physical measures. It should be a real field so that you can best track changes. You might want to make one of the old physical measures calculated now, so that if there’s a basic equation inconsistent data can’t be entered. For instance, if you had physical measures for speed and time and calculated distance as speed * time, now you could physicalize distance and calculate speed as distance / time.

Good luck with this, let me know if my suggestion was any help.

3

Thanks but I am not sure I quite understand what I should be doing. Let me explain the issue in some more detail.

  1. I have a write enabled physical cube. Let us call it ‘X’. This cube is based on a Oracle database view and has a physical measure called ‘Revenue Forecast’. Since the cube is write enabled the measure will get written to the corresponding write back table whenever a value is written into the physical measure.

  2. I then have a virtual cube of which the physical cube ‘X’ is one of the component cubes. In this cube there is a calculated measure ‘*Revenue Forecast’ that has the MDX which multiplies the selling price with the forecast quantity (both entered by user) to determine the revenue. This calculated revenue needs to be saved to the database and the only way to do that is to copy this value to the physical measure ‘Revenue Forecast’ of cube ‘X’ so that it gets written to the corresponding write back table from where an Oracle procedure can then move it to wherever required.

A write enabled cube has its own write back table. Whenever the value of any of the measures of the cube changes the ‘delta’ is written to the write back table. This ‘delta’ has then to be summed and moved to the cube’s fact table after which the write back tables are truncated.

4

Umm… okay. Why don’t you simply move the selling price and forecast quantity into the writeback table - and then multiply them there with an update statement in an Oracle procedure or something similar?? That will get revenue forecast calculated for you just as surely, the way I see it.