Control Checks between Sql Tables (Facts and Dims) and SSAS Cubes?

[ian.coetzer]

Hi

How can one write a report that compares data in sql tables (facts / dims) to data stored in an SSAS cube?
I have to write a control check report to compare this data to ensure that the cube contains the same data as is stored in our sql server enterprise data warehouse.

It will of course be specific tests for example:
Sum of a specific measure in a specific fact table, grouped by say 5 dimensions.
And then in the Cube I want to do the same via a script? and compare the resulting tables?

Anyone done this before with some examples please?
I am not sure if one can combine mdx queries and t-sql queries in one script / stored procedure to do this?

[hang]

It's not easy as there is no direct access for TSQL to query the cube. The only approach I can think of is to use OpenQuery through linked server as follows:

SELECT *
FROM OpenQuery(linked_olap,'SELECT --measures.members
{Measures.[Internet Sales Amount]} ON COLUMNS,
[Date].[Month].members ON ROWS
FROM [Adventure Works]')

[Mike Honey]

Hi Ian

I'd be thinking an SSRS (Reporting Services) report would be the easiest solution. You can have separate Datasets - one connected to SQL, the other to SSAS. Then you can either lay them out side by side in two tables for a quick win. If you want to get fancy, you can use the Lookup functions to compare values between the Datasets.

Good luck!
Mike

[Sponsored content]