Renaming column names in an MDX Query RRS feed

  • Question

  • Hi,

    I have a power pivot report which used to pull data from analytics server A. I have to now point it to Analytics server B. Both have same identical data but the dimension and measure names are different( Please note I am not owner or having control of both, they are upstream servers to say).

    I  need to now have column names same as old column names so that I could simply change connection to B. As I have no control over A,  I need to rename the column names  to old column column names as of A in MDX query.

    My column names are combination of measures and dimensions for example as below 

    Old Query

    SELECT NON EMPTY { [Measures].[OppoConstAmount], [Measures].[OppoServiceCDAmount] } ON COLUMNS, NON EMPTY { ([Opportunity].[OpportunityID].[OpportunityID].ALLMEMBERS * [Opportunity].[OppOwner].[OppOwner].ALLMEMBERS * ...

    and goes on


    New Query

    SELECT NON EMPTY { [Measures].[OppoProAmount], [Measures].[OppoCD Amount] } ON COLUMNS, NON EMPTY { ([Oppo].[OppoID].[OppoID].ALLMEMBERS * [Oppo].[Owner].[Owner].ALLMEMBERS * and goes on ...


    So I want my new query to build such a way that it should map data to  old names so that when I change over the connection the new MDX query would be mapping with new one. (for example I want [Oppo].[OppoID].[OppoID].ALLMEMBERS * mapping to [Opportunity].[OpportunityID].[OpportunityID].ALLMEMBERS *) I think it would be straight forward for measures, but i want it for dimensions too as mentioned above.

    I am glad for all the solutions except for the one that I need to delete and re-create all the report.

    Thanks & Regards Tejaswi Chandrapatla

    Wednesday, February 3, 2016 6:40 AM


  • Unfortunately there is no way to alias hierarchy or level names in MDX queries. I can't think of any easy solution for you if you can't change the cubes.

    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Charlie Liao Thursday, February 18, 2016 9:42 AM
    • Marked as answer by Charlie Liao Monday, February 22, 2016 8:42 AM
    Wednesday, February 3, 2016 8:12 AM