0

I have data cube hierarchy as follows. enter image description here

I can access the highlighted node as 

SELECT  [Calendar].[Report Days].[All Members].[All].[WantInReport].[Yesterday].LastChild ON 0

I tried to run this query in Execute SQL task and assign the output to an SSIS variable. But issue is the column name is changing. I tried to alias the column name also. How can I achieve this ?

SouravA
  • 5,147
  • 2
  • 24
  • 49
udaya726
  • 1,010
  • 6
  • 21
  • 41

1 Answers1

3

You can use a query-scoped calculated measure to create the alias. As an example, I'm using the AdventureWorks cube. The following query would give me the last child in the calendar hierarchy for the member I provided. 

SELECT [Date].[Calendar].[All Periods].[CY 2014].[H1 CY 2014].lastchild on 0
FROM [Adventure Works]

As you stated, since the last child changes over time, the member name changes, creating the need to alias it to provide a constant name. To do this, create a calculated measure. You move your logic to the WITH MEMBER statement and get the member caption instead of the member, and then use the new calculated measure on the 0 axis. 

WITH MEMBER [Measures].[MyLastChild] AS 
       [Date].[Calendar].[All Periods].[CY 2014].[H1 CY 2014].LASTCHILD.MEMBER_CAPTION
SELECT {Measures.MyLastChild} on 0
FROM [Adventure Works]

So your query would be something like

WITH MEMBER [Measures].[Last Day] AS
[Calendar].[Report Days].[All Members].[WantInReport].[Yesterday].LastChild.MEMBER_CAPTION
SELECT [Measures].[Last Day] ON 0
FROM [MyCube]

If you are having trouble executing an MDX query and returning that result in SSIS, you have a couple of options.

  1. Use an OLE DB source as illustrated here.
  2. Set up a linked server and use OpenQuery to return the MDX results as T-SQL results (not recommended for this situation).
mmarie
  • 5,598
  • 1
  • 18
  • 33
  • Thanks @MMarie! I was completely unaware of this! But it is not working for me. I am using SSIS 2012 and have SSAS 2012 Tabular model. – SouravA Mar 16 '15 at 15:22
  • 1
    The syntax is not specific to multidimensional, it's just a part of SSAS, and both Tabular and MD can be queried via MDX. Perhaps you have a syntax error or typo? – mmarie Mar 16 '15 at 15:43
  • No it's not related to querying part. I am talking about the hyperlink you have in answer which talks about having an OLEDB source to query the cube. Under data links, in extended properties, I did not see a drop down. So I manually put "FORMAT = TABULAR. But still the OLEDB source didn't recognize the conn manager. Probably this hack is not applicable for tabular model? – SouravA Mar 16 '15 at 16:01
  • I was able to set up the Analysis Services conn manager. Just the hack part doesn't work. :( – SouravA Mar 16 '15 at 16:03
  • You may not need the hack. But I'm pretty sure the connection works the same way regardless of if it is tabular or multidimensional. – mmarie Mar 16 '15 at 16:56