Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

Go down

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

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

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

Back to top Go down

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

Post  hang 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

Back to top Go down

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

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

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

http://www.mangasolutions.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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