Problem

We have an ad-hoc reporting system that implemented in .ASPX and MVC. This website retrieves data through MDX queries. However, we are having a problem when we want to modify the MDX queries, we must re-build and deploy our application. It requires a long downtime period in our system.

Approach

To avoid the issue, we are thinking about a solution that allows us to modify MDX queries easily and deploying it to LIVE environment quickly. One of them is to include MDX queries in SQL store procedure. In this article, I would like to execute a demo how to implement it.

Implementation

The prerequisite condition is to have a linked server between a SQL database server instance and SQL Analysis server instance (SSAS). SQL Database server instance is where we execute a store procedure that calls a MDX query.

Setting up a linked server

This step is to create a linked server by executing system store procedure master.dbo.sp_addlinkedserver

EXEC master.dbo.sp_addlinkedserver
@server = N’AdventureWorks’
,@provider= N’MSOLAP’
,@srvproduct =N”
,@datasrc=’DUNGDT\SQLSERVER2014′
,@catalog =’Adventure Works DW 2008R2′

@server = linked server name what you want

@provider = library name to create connection to SQL Analysis Service

@datasrc= SQL Analysis Service Instance name

@catalog = Database Cube name that you want to query data

Implement a MDX query

This step is to create a MDX query to execute on SSAS instance directly. This query is to sever a report of how much Internet Sale Amount and Gross Profit of all states of United States country for 2013 fiscal year.

SELECT
NON EMPTY({
[Measures].[Internet Sales Amount],
[Measures].[Internet Gross Profit]
}) ON COLUMNS,
NON EMPTY([Customer].[State-Province].Children) ON ROWS
FROM [Adventure Works]
WHERE ([Customer].[Country].&[United States],[Date].[Fiscal Year].&[2013])

The query returns a list of states of United States country

MDX Query
MDX Query

Implement a store procedure

This step is to implement SQL store procedure and integrate the MDX query about. One of techniques is to use OPENQUERY statement to execute a MDX query in SSAS linked server.

CREATE PROCEDURE usp_GetStateProviceTotalSalesAmount

AS

BEGIN

SELECT “[Customer].[State-Province].[State-Province].[MEMBER_CAPTION]” AS [State-Provice],

CONVERT(float,”[Measures].[Internet Sales Amount]”) AS [Internet Sales Amount],

CONVERT(float,”[Measures].[Internet Gross Profit]”) AS [Internet Gross Profit]

FROM OPENQUERY([AdventureWorks], — Linked server

— Starting MDX Query

‘SELECT  NON EMPTY({

[Measures].[Internet Sales Amount],

[Measures].[Internet Gross Profit] }) ON COLUMNS,

NON EMPTY([Customer].[State-Province].[State-Province]) ON ROWS

FROM [Adventure Works]   WHERE ([Customer].[Country].&[United States],[Date].[Fiscal Year].&[2013])’

)

END

 

MDX and Store Procedure

We need to define the mappings between MDX dataset returned by OPENQUERY and SELECT statement. We should remember the use of double quotes and bracket digits in SQL SELECT statement. In case of measures, we only use the same measures as MDX query but we need [MEMBER_CAPTION], which returns name of members of a hierarchy of a dimension in case of dimension. In above example, it returns list of names of State-Province hierarchy of Customer dimension.

Now, we can execute the store procedure usp_GetStateProviceTotalSalesAmount

SQL Store Procedure

Conclusion

Obviously, this approach allow us to modify the business logic quickly and easily without impacting our application and reduce downtime period. For example, we can pass parameters Country and Fiscal Year to query which data we want by using dynamic SQL. However, there are some disadvantages when we use this approach in my view

  1. We need a linked server to SSAS instance. In case, SQL Server Instance and SSA instance are installed on separated servers, performance is an issue which we will have.
  2. We need to grant permissions to any account who executes the store procedure and linked server

 

Advertisements

One thought on “How to execute a MDX query in SQL store procedure

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s