Can SSAS MDX Query be used in a: 1. Stored Procedure  2. Function (Table?)

Posted on 2014-08-04
Medium Priority
Last Modified: 2016-02-18

We have a section of our company (National) that creates MDX Queries using SSAS. We are a separate region but would like to take their MDX logic that is created on a National level and use this MDX Query in our local Regional Stored Procedures and Functions.

Is this possible?k

I think the main question is: Can MDX be used to access data withing Stored Procedures and/or Functions... to produce "datasets" for further use in pulling in data using T-SQL...?
Question by:MIKE
1 Comment
LVL 37

Accepted Solution

ValentinoV earned 2000 total points
ID: 40240899
There is a method to use MDX from within T-SQL but it's not very straightforward.

The first step is to set up a linked server from your SQL Server to your SSAS server.  Once you've got that you can use OPENQUERY() to execute the MDX, syntax goes like this:

SELECT * FROM OPENQUERY(YourLinkedServer, 'Your MDX SELECT statement')

Open in new window

There are some interesting examples in the following article: MDX + T-SQL: Combining relational and multi-dimensional data into one query result set

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question