How do I get a cell value based on an attribute of another dimension

0
votes

I am trying to somehow link two dimensions together in SSAS.

I have the following Dimensions:

  • Location
  • Price Scheme
  • Product

I have a fact and measure group that is linked through Product and Price Scheme (there are only about 3 price scheme members).

Price Scheme is also an attribute of Location. I want my measure group to show up against each location. If in my source query I do a join to Location based on the Price Scheme, then I get about 100 million records which makes the cube processing take a long time. There is less than 1 million rows in the measure group when at the Sales Price Scheme level.

I suppose my question is: how do I write an MDX query that will get the measures from my measure group based on the Price Scheme of the attribute against the Location dimension?

I know I can do a referenced dimension.. but doesn't that just modify the query by performing an inner join onto the Location dimension and as such will still give me 100 million rows to process?

1
ssasmdx

1 Answers

0
votes

Simple OLAP rule: The less you process, the slower MDX you get.

There are two ways:

  • Use many-to-many relation: which is also slow and you need one more extra fact table to join two dimensions.
  • Use Slow-changing dimension: which is super fast on your cube, but will take some time to process. You may speed it up, by setting indexes and ProcessingGroup property to ByTable. Roughly saying it's what you described above.

In order to say more we want to see your data scheme.