Get additional data from SSAS cube or relational database

Question

I have a situation where I'm using a SSAS cube to analyze the frequency of trips between locations. I have defined the locations as a dimension of the cube and trip frequency as a measure.

I’m creating a service that returns this data, and every time I return data from a location I also have to return several additional fields (name, geographical coordinates, and others) that are associated with the location but are not part of the dimension used in the cube.

I can think of two different ways to get that information:

1. Add all the needed fields to the dimension and get them from the cube in the same MDX query.
2. After getting the data from the cube then get the additional fields from the relational database that serves as the base for the cube.

Both these options can give me the information I need, what I don’t know is which one is the best option.

Answer 1

Adding the information to the dimension would make everything easy, you just run an MDX query, and everything is there. No need to implement complex lookup logic in the service. You might consider implementing some of the additional fields as custom properties like described here, but this may make retrieving them a bit more complex, depending on the exact client interface you are using. Hence, the easiest way would be to implement them as attributes.

If you want to go the a bit more complex way, and you want to get the information in the result of a normal MDX statement, you have to add DIMENSION PROPERTIES [Dim].[Attrib].[Prop] before the ON ROWS or ON COLUMNS etc. of the axis containing the attribute. This includes the property in the result. How you access it depends on your client interface.

If you are using Adomd.net, you can get the properties from the Properties property of the members, but - according to the documentation - need to call the FetchAllProperties method to populate this.

There is still another method when using the properties: Defining a measure as

[Dim].[Attrib].CurrentMember.Properties("Prop")

will deliver the property value as a measure without any special implementation need on the client.

As properties need some special treatment, either for getting access to them, or by defining a measure, in many cases a normal attribute will be the best solution.