Extract data from a cube's dimension created from a View

1
votes

We have imported an SQL View table into a dimension. We already programmed a connector that talks with data cubes (MDX queries). That said, the view we originally imported contains all the raw data we need to query. Problem is, the MDX client requires to "select" measures only. We want to show the raw data, that means, we want to view the same columns\attributes as the initiale SQL View created.

Is this even possible ? We know we can use Linq or whatever to talk with the SQL View Table but it will be better to talk in MDX cube-like mode to a "dumb" cube dimension's data.

Thanks.

1
tsqlssasmdxdata-miningcube-dimension
have you created the cube? if you have created the dimension from your table in your view you should be able to also create some measure that represent the data you need to see, then run your mdx-query and show the data. what do you mean by "dumb" cube? – daniel_aren
Yes we created the cube. Problem is a coder had already developed the connector to query the cube with an MDX query. That's fine but the cube was created with a View where it already contains the basic info we need to retrieve for the first tasks of the project. So I was wondering if we could address the cube without any measures. But looks like the Cube manifesto is against a "dumb cube" as in just use the raw data and not do any measures. – Tommy Dubé-Leblanc

1 Answers

2
votes

I don't understand why you really want to use the cube and not your view, but anyway you've two solutions to extract dimension's members from a cube: through a DMV or through a standard MDX query.

The DMV named $system.MdSchema_members will return the members of your dimension. You should be able to retrieve the values you are looking for. http://msdn.microsoft.com/en-us/library/ms126046.aspx

The other solution is to create a dummy measure with a create measure statement above your MDX query. In your SQL statement, then put this dummy measure on axis 0 and all the attributes you're looking for on axis 1. This should return you a result close to the result returned by a select * from your view.