Assign Dimension value to SSIS variable

Question

I have data cube hierarchy as follows.

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 ?

Answer 1

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).