Sql-server – Compare Validate TSQL Data Warehouse to SSAS

data-warehousemdxsql serversql-server-2016ssas

How do I compare Data in T-SQL DataWarehouse to SSAS Cube?

I would like to conduct this from SSMS (SQL Server Studio Management Studio).

Example

Select CustomerName from dbo.DimCustomer where CustomerId = 5    

SHOULD EQUAL

SELECT [Customer].[CustomerName].Members ON ROWS FROM [DimCustomer] where [Customer].[CustomerId].[5]

https://xzwang.wordpress.com/2013/06/17/validatingcubesvsrelational/

Following blog recommends OPENROWSET. Is there any other method to validate data in same environment rather than OPENROWSET in SSMS? Otherwise, team will utilize this or may research Powershell or C#.

select cast("[Customer].[Customer].[Customer].[Email Address]" as nvarchar(50)) EmailAddress,
"[Measures].[Internet Sales Amount]" SalesAmount
from openrowset('MSOLAP', 'datasource=localhost;Initial Catalog=AdventureWorksDW2012Multidimensional-EE;',
' SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS ) }
DIMENSION PROPERTIES [Customer].[Customer].[Email Address] ON ROWS FROM [Adventure Works] ')

Best Answer

If you wish to run MDX within SSMS then I believe OPENROWSET is your only option. Read about it carefully as there are some permissions/configurations that you must change to enable it, and they may affect your security.

If you do not have to use SSMS then you could use any programming language that can call the database drivers which communicate with SQL Server and OLAP, such as ADOMD and ADODB. You mention C# which would be perfect - use Microsoft.AnalysisServices.AdomdClient namespace. Or even a simple VBScript file could work if you want a quick and simple solution on Windows, just for short-term testing.

Related Question