Post reply

Has MDX Structure changed SQL 2005 to 2008?

  • May 15, 2012 at 9:31 am

    #147223

    Hi All,

    We are trying to migrate a SQL 2005 DW database and its related cubes to SQL 2008 environment. It turns out that there also a quite number MDX queries written against 2005 cube.

    We haven't made any changes to the cube but after the migration mdx queries stopped working. I have had a quick look at the queries that 2008 generates and I can see some differences. Please see the example below:

    2005

    SELECT { { {

    [Time Standard].[Acc Prd].&[201112] } * { [SCC].[SCC].&[CAD], [SCC].[SCC].&[EUR], [SCC].[SCC].&[GBP], [SCC].[SCC].&[JPY], [SCC].[SCC].&[USD], [<#SharedMember.RESERVED.PWH.Conv(GBP) 2011 Quarter 4 (USD1 55 CAD1 58 EUR1 20 JPY 119 57#>] } * { [YAC].[YAC].&[1993], [YAC].[YAC].&[1994], [YAC].[YAC].&[1995], [YAC].[YAC].&[1996], [YAC].[YAC].&[1997], [YAC].[YAC].&[1998], [YAC].[YAC].&[1999], [YAC].[YAC].&[2000], [YAC].[YAC].&[2001], [YAC].[YAC].&[2002], [YAC].[YAC].&[2003], [YAC].[YAC].&[2004], [YAC].[YAC].&[2005], [YAC].[YAC].&[2006], [YAC].[YAC].&[2007], [YAC].[YAC].&[2008], [YAC].[YAC].&[2009], [YAC].[YAC].&[2010], [YAC].[YAC].&[2011] } } } ON COLUMNS ,

    { { { [Syndicate].[Syndicate].&[0382], [Syndicate].[Syndicate].&[1441], [Syndicate].[Syndicate].&[1481], [Syndicate].[Syndicate].&[3820] } * { [Measures].[EPIGrCalc], [Measures].[EPIGrSgnd], [Measures].[EPIGrWtn], [Measures].[EPIMktGr], [Measures].[EPINetCalc], [Measures].[EPINetSgnd], [Measures].[EPINetWtn], [Measures].[PdCm], [Measures].[PdPm], [Measures].[PdRiCm], [Measures].[PdRiPm], [Measures].[OsCm], [Measures].[OsCmAdjd], [Measures].[OsCmNet], [Measures].[OsCmNetAdjd], [Measures].[AccrCm], [Measures].[AccrPm] } } } ON ROWS

    FROM [vALL]

    WHERE ( [AdviceID].[AdviceID].[All AdviceID], [Advice Status].[Advice Status].[All Advice Status], [UnitPsu].[All UnitPsu], [PeriodType Standard].[Cum] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

    2008

    SELECT { { {

    [Time Standard]. [Time Standard].[AccPrd].&[201112] } *

    { [SCC].[SCC].&[CAD], [SCC].[SCC].&[EUR], [SCC].[SCC].&[GBP], [SCC].[SCC].&[JPY], [SCC].[SCC].&[USD]} *

    { [YAC].[YAC].&[1993], [YAC].[YAC].&[1994], [YAC].[YAC].&[1995], [YAC].[YAC].&[1996], [YAC].[YAC].&[1997],

    [YAC].[YAC].&[1998], [YAC].[YAC].&[1999], [YAC].[YAC].&[2000], [YAC].[YAC].&[2001], [YAC].[YAC].&[2002],

    [YAC].[YAC].&[2003], [YAC].[YAC].&[2004], [YAC].[YAC].&[2005], [YAC].[YAC].&[2006], [YAC].[YAC].&[2007],

    [YAC].[YAC].&[2008], [YAC].[YAC].&[2009], [YAC].[YAC].&[2010], [YAC].[YAC].&[2011] } } } ON COLUMNS ,

    { { { [Syndicate].[ Syndicate].[Syndicate].&[0382],

    [Syndicate]. [Syndicate].[Syndicate].&[1441],

    [Syndicate]. [Syndicate].[Syndicate].&[1481],

    [Syndicate]. [Syndicate].[Syndicate].&[3820] } * { [Measures].[EPIGrCalc], [Measures].[EPIGrSgnd], [Measures].[EPIGrWtn],

    [Measures].[EPIMktGr], [Measures].[EPINetCalc], [Measures].[EPINetSgnd], [Measures].[EPINetWtn], [Measures].[PdCm],

    [Measures].[PdPm], [Measures].[PdRiCm], [Measures].[PdRiPm], [Measures].[OsCm], [Measures].[OsCmAdjd],

    [Measures].[OsCmNet], [Measures].[OsCmNetAdjd], [Measures].[AccrCm], [Measures].[AccrPm] } } } ON ROWS

    FROM [vALL]

    WHERE (

    [Adv Id].[AdviceID].[All AdviceID],

    [Advice Status].[Advice Status].[All Advice Status],

    [Unit Psu]. [UnitPsu].[All UnitPsu],

    [PeriodType Standard].[ PeriodType Standard].[Cum] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

    As you can see above, a new hierarchy level is added in 2008 to produce the output. The question is that whether there is a way to update these queries, without recreating each query. Or third party tool perhaps?

    Appreciate if you have any comment on this.

    Regards

    Enis

  • May 16, 2012 at 7:12 am

    #1488370

    Has anyone experienced such an issue please?

Viewing 2 posts - 1 through 1 (of 1 total)

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