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

Posted on 2014-08-04
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

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now