none
Need help on MDX query to retrieve data from linked table

    Question

  • Hi All,

    If we have some table (other than Dimension and Fact table) in our cube which are linked with some of the existing dimensions (circled in Red) and if we need to retrieve data from the end table using MDX, then what is the way to retrieve that?


    Chandan Nayek
    Monday, October 24, 2011 10:22 AM

Answers

  • Hi Chandan

    To Achive this kind of functanality you need to write Hybrid Query(SQL+MDX). Using OpenQuery fuction from SQL you can do. See below SQL to Get more idea.

    You Need to create ##TestTemp Table For MDX Result. And Then Join the Temp Table with your ((circled in Red)) Table.

    Note.- You Nee to Select Comman column in MDX Query for joining purpose with temp table.

    See Below SQL

    EXEC sp_addlinkedserver
    @server='TestTest',
    @srvproduct='',
    @provider='MSOLAP',
    @datasrc='localhost',
    @catalog='CM' 

    Declare @MDXExpression as Varchar(MAX)
    Select @MDXExpression = '
          SELECT
          NON EMPTY
           {
           [Measures].[Economic Capital Amount],
           [Measures].[Diversified Capital Amount],
           [Measures].[Standalone Capital Amount],
           [Measures].[Diversification Benefit]
           } ON COLUMNS,
          NON EMPTY
           {
           [Time].[Time Hierarchy].[Day of Month]
           } ON ROWS
          FROM
           [Summary]
          Where
             (
             [Scenarios].[Scenarios Hierarchy].[Base Scenario] 
             )
          ';      
    Exec ('SELECT * INTO ##TestTemp FROM OpenQuery(TestTest,''' + @MDXExpression + ''')')

    SELECT
     CONVERT(varchar,t."[Time].[Time Hierarchy].[Day of Month].[MEMBER_CAPTION]") AS [Date],
     (CONVERT(nvarchar,t."[Measures].[Economic Capital Amount]")) AS [Economic Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Diversified Capital Amount]")) AS [Diversified Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Standalone Capital Amount]")) AS [Standalone Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Diversification Benefit]")) AS [Diversification Benefit]
    from
     ##TestTemp t

    --Drop table ##TestTemp;


    SUHAS http://suhaskudekar.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Monday, October 24, 2011 10:49 AM

All replies

  • Hello!

    1. You can create new dimensions from these tables and link them to the dims that are used in your measure groups (see linked dimensions)

    2. You can add new attributes to existing dimensions from that tables (snowflake)

    3. Redesign fact tables to be linked to new tables and create new dims from them (regular dims).

    If you don't use that tables in your SSAS UDM (in a way I described), I think you can't access them anyway.

    Monday, October 24, 2011 10:48 AM
  • Hi Chandan

    To Achive this kind of functanality you need to write Hybrid Query(SQL+MDX). Using OpenQuery fuction from SQL you can do. See below SQL to Get more idea.

    You Need to create ##TestTemp Table For MDX Result. And Then Join the Temp Table with your ((circled in Red)) Table.

    Note.- You Nee to Select Comman column in MDX Query for joining purpose with temp table.

    See Below SQL

    EXEC sp_addlinkedserver
    @server='TestTest',
    @srvproduct='',
    @provider='MSOLAP',
    @datasrc='localhost',
    @catalog='CM' 

    Declare @MDXExpression as Varchar(MAX)
    Select @MDXExpression = '
          SELECT
          NON EMPTY
           {
           [Measures].[Economic Capital Amount],
           [Measures].[Diversified Capital Amount],
           [Measures].[Standalone Capital Amount],
           [Measures].[Diversification Benefit]
           } ON COLUMNS,
          NON EMPTY
           {
           [Time].[Time Hierarchy].[Day of Month]
           } ON ROWS
          FROM
           [Summary]
          Where
             (
             [Scenarios].[Scenarios Hierarchy].[Base Scenario] 
             )
          ';      
    Exec ('SELECT * INTO ##TestTemp FROM OpenQuery(TestTest,''' + @MDXExpression + ''')')

    SELECT
     CONVERT(varchar,t."[Time].[Time Hierarchy].[Day of Month].[MEMBER_CAPTION]") AS [Date],
     (CONVERT(nvarchar,t."[Measures].[Economic Capital Amount]")) AS [Economic Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Diversified Capital Amount]")) AS [Diversified Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Standalone Capital Amount]")) AS [Standalone Capital Amount],
     (CONVERT(nvarchar,t."[Measures].[Diversification Benefit]")) AS [Diversification Benefit]
    from
     ##TestTemp t

    --Drop table ##TestTemp;


    SUHAS http://suhaskudekar.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Monday, October 24, 2011 10:49 AM
  • Suhas, thanks for the post!

    It will be helpful for me too! )

    Monday, October 24, 2011 10:52 AM