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

View previous topic View next topic Go down

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

Post  ian.coetzer on Wed Mar 14, 2012 4:50 am

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?
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 37
Location : South Africa

View user profile

Back to top Go down

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

Post  hang on Wed Mar 14, 2012 9:04 pm

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]')

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

SSRS

Post  Mike Honey on Thu Mar 15, 2012 2:10 am

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum