SQL - Query Cubes

Question

So i just received a connection string to a Cube in our one of our SQL databases. I'm supposed to create a report that can be emailed to 30 something people off of this data.

I would much rather write a stored procedure and just let SSRS send it out whenever its supposed to. The problem is that the database is extremely complex and I'm not sure I can match everything up and get the correct information.

Is there anyway that I can write a store procedure that will look at the information in a cube?

I'm not sure if this matters but the database and the cube are on the same SQL server.

Any information about cubes would be helpful. I know that they to some pre-calculations based on what they will probably be used the most for.

Thanks

Answer 1

two different languages - T-SQL is the RDBMS tool for querying relational data; to talk to cubes you use MDX.

Answer 2

Try OpenQuery and/or OpenRowset and pass through the MDX

Answer 3

You can make your report use a regular stored procedure but that stored procedure still has to run an MDX query that goes against the cube (via OLEDB).

I am not sure what report you are using; specifically whether it is a Local or Server based report but SQL Server's server based reports do have an MDX generator which hides the MDX syntax from beginners and is adequate for populating straightforward Table (Tablix in SSRS 2008) and Matrix based reports. For more complex reporting, it helps learning the MDX syntax.

If you are using Local reports (runs on the app server as opposed to the reporting server) you have to know the MDX syntax because you will have to use this MDX query to query the Analysis Services Cube via an OLEDB provider on your middleware component.

One more thing, cubes are best utilized when you need to drill down and/or drill through from summary down to detailed information. They are best used for slicing and dicing data (measures) with dimension information. The matrix control is the best tool to view data in a cube with the data cell itself representing a measure and the column(s) and header(s) for this cell representing a measure. The matrix will then "transform" the data automatically depending on which dimension you choose.