Post reply

Using MDX within SSIS

  • May 25, 2011 at 6:17 am

    #250795

    Hi Guys/Girls,

    I have been looking around a number of questions people have had with regards to handling SSAS data within SSIS but haven't been able to get the solid confirmation either way on what I am trying to find out so I thought I'd ask it myself 😀

    I have got a number of SSIS packages that are extracting data from SSAS using MDX via an OLE connector and then being successfully converted/combined etc. then pushed to CSV files ready for the Oracle guy next to me.

    My question is:

    When you pull data from SSAS using MDX you get the 'External Data Name' which can be mapped to a 'Internal Data Name' but if the data set you are pulling is dynamic (e.g. Category name, Period, Entity) then that external name changes and causes the conversions etc. to fail as the columns that were being used are now different.

    Suppose what I am asking is there a way to have the same functionality in MDX as I can use in SQL with the "AS 'xxx' " statement to keep the output column names 'labels' static??

    Thanks,

    Dean.

  • May 25, 2011 at 6:24 am

    #1329617

    Instead why can't you use Named Set ?

    Plese read following link.

    http://msdn.microsoft.com/en-us/library/ms144790.aspx

  • May 25, 2011 at 6:58 am

    #1329630

    Why not indeed 🙂 Thanks for the link.

    Probably time to get out of the MDX paddling pool and jump in the pool!

    Thanks,

    Dean.

  • June 1, 2011 at 2:30 am

    #1332402

    Hi,

    I have been trying to get the 'WITH MEMBERS' to work within my MDX but I am having trouble.

    I can knock together a quick query that gives me the desired results (external colunms named [measures].[test] etc returned in SSIS).

    When I try to run this within the following query I get an error regarding 'The Measures hierarchy already appears in the Axis0 axis'. Is this because the data [ACCLAB].[VAL] and [ACCLAB].[VAL2] are considered 'measures'?

    The query I run is:

    with member [test] as [ACCLAB].[VAL]

    member [test1] as [ACCLAB].[VAL2]

    SELECT non empty {[test],[test1]} on 0,

    non empty crossjoin(crossjoin({(Descendants([PIP].[TOT_PIP],999,LEAVES))},

    {(Descendants([TIME].[2011.TOTAL],999,LEAVES))}),

    {[MACHINE].[TOT_PROD],[MACHINE].[TOT_PP],[MACHINE].[TOT_SP] }) on 1

    FROM [LAB]

    WHERE ([CATEGORY].[QF],

    [DATASRC].[TOTALADJ],

    [ENTITY].[TOT_ENTITY],

    [MEASURES].[PERIODIC],

    [LINE].[TOT_LINE],

    [PRODUCT].[ALL_BU])

    Is there a way around this or another method to get the 'named colunms'?

    Thanks

    Dean.

  • June 1, 2011 at 6:59 am

    #1332483

    Hi,

    After some more playing around I have found that by fully qualifying the MEMBER I don't get the error. Would seem that without that it will default to [MEASURES] (which is were the issue was).

    Working query:

    with member [ACCLAB].[H1].[test] as [ACCLAB].[VAL]

    member [ACCLAB].[H1].[test1] as [ACCLAB].[VAL2]

    SELECT non empty {[ACCLAB].[H1].[test],[ACCLAB].[H1].[test2]} on 0,

    non empty crossjoin(crossjoin({(Descendants([PIP].[TOT_PIP],999,LEAVES))},

    {(Descendants([TIME].[2011.TOTAL],999,LEAVES))}),

    {[MACHINE].[TOT_PROD],[MACHINE].[TOT_PP],[MACHINE].[TOT_SP] }) on 1

    FROM [LAB]

    WHERE ([CATEGORY].[QF],

    [DATASRC].[TOTALADJ],

    [ENTITY].[TOT_ENTITY],

    [MEASURES].[PERIODIC],

    [LINE].[TOT_LINE],

    [PRODUCT].[ALL_BU])

    Cheers,

    Dean.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply