none
SSAS MD 2017 (latest SP) -- deploy MDX script only RRS feed

  • Question

  • hello folks,

    I was wondering if it is possible to only deploy MDX script changes, in an otherwise fairly complex cube, without deploying measure groups and all other objects.

    When I try the below  XMLA code, to only include the MDX script, I get an error that the cube does not have any measure groups and must contain at least one.   Yes, the cube otherwise has many measure groups and dimensions, but I'd like to *only* deploy the MDX script changes.   It appears that the BI Developer Extensions for VS 2017 is not working either, as far as deploying the MDX script only.  Not sure if in the earlier versions of SSAS this was possible, but now in SSAS 2017 it is no longer possible, or I'm missing something elementary.

    any thoughts on what the issue might be?
    many thanks --
    Cos

    <Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <Object>
            <DatabaseID>DBName</DatabaseID>
            <CubeID>DBName</CubeID>
        </Object>
        <ObjectDefinition>
            <Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
                <ID>CubeName</ID>
                <Name>CubeName</Name>
                <MdxScripts>
                    <MdxScript>
                        <ID>MdxScript</ID>
                        <Name>MdxScript</Name>
                        <Commands>
                            <Command>
                                <Text>/*
    The CALCULATE command controls the aggregation of leaf cells in the cube.
    If the CALCULATE command is deleted or modified, the data within the cube is affected.
    You should edit this command only if you manually specify how the cube is aggregated.
    */
    
    CALCULATE; 
    
    blah, blah, blah....
    
          </Cube>
        </ObjectDefinition>
    </Alter>

    Monday, October 21, 2019 8:24 PM

Answers

  • Hi Cos2008,

    Thanks for your post.

    You couldn't only update MDX script in the cube via XMAL script. Because there are many calculated measure members, specific dimension sets, scope statements etc. inside the MDX script. They all make changes to the structure of the cube. It needs to be compiled to generate the new ALTER XMAL script. But SSMS is not good place to compile MDX script.

    In my opinion, you could only deploy the changes to SSAS server instance by using SSDT, other ways of deployment such as XMAL script would lead you wrong direction. It is hard to free you from the mistakes. 

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Cos2008 Tuesday, October 22, 2019 2:20 PM
    Tuesday, October 22, 2019 3:33 AM
  • VS is mostly for development work, not that much for production/support,
    SSMS is well suitable for monitoring/security
    For advanced admin/maintenance simplest approach would normally be to maintain these via AMO API (C#, PowerShell, etc.)

    MD object model can store multiple (perhaps up to 128 instances) different "copies" of MDX script per cube (with only one set as "default" for sessions, and I'm not sure you can control it dynamically via connection string during session initialization as there's no additional property documented for this purpose)
    For simplicity let's assume you have only one MDX script in your cube.
    Please explore it here in more details:
    https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.cube?view=analysisservices-dotnet

    Here's a quick PowerShell example to overwrite MDX script in a first cube in your database:

    using assembly Microsoft.Analysisservices
    cls;$i="MySSAS_Server\MySSAS_Instance"
    $b="MySSAS_Test_DB";$n="`r`n"#new line
    $s=new-object Microsoft.Analysisservices.Server
    $s.Connect($i);$d=$s.Databases[$b];$c=$d.Cubes[0];$m=$c.DefaultMdxScript
    $t="Calculate;"+$n+"create calculated member currentcube.[Measures].[my_test1] as 1;"
    $m.Commands[0].Text=$t
    $d.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull,[Microsoft.AnalysisServices.UpdateMode]::Update)
    $s.Disconnect();Remove-Variable * -ea 0;$Error.Clear()

    Alternatively your script can come from external file or use replacement rules in existing script (read it first from cube) plus execute additional validation on a cube level for error-handling.

    • Marked as answer by Cos2008 Wednesday, October 23, 2019 2:25 PM
    Wednesday, October 23, 2019 1:04 AM

All replies

  • Hi Cos2008,

    Thanks for your post.

    You couldn't only update MDX script in the cube via XMAL script. Because there are many calculated measure members, specific dimension sets, scope statements etc. inside the MDX script. They all make changes to the structure of the cube. It needs to be compiled to generate the new ALTER XMAL script. But SSMS is not good place to compile MDX script.

    In my opinion, you could only deploy the changes to SSAS server instance by using SSDT, other ways of deployment such as XMAL script would lead you wrong direction. It is hard to free you from the mistakes. 

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Cos2008 Tuesday, October 22, 2019 2:20 PM
    Tuesday, October 22, 2019 3:33 AM
  • There's a known issue with the current 2.3.4 version of BIDE (https://github.com/BIDeveloperExtensions/bideveloperextensions/issues/78), whereby its 'Deploy MDX script' functionality fails with the more recent versions of the Analysis Services projects extension.

    What's wrong with the default 'Deploy Changes Only' value of the Server Mode setting in project's Configuration Properties/Deployment for you, though?


    Expect me to help you solve your problems, not to solve your problems for you.


    Tuesday, October 22, 2019 7:46 AM
  • hi Will,
    I should have been more specific:
    I'm trying to cleanly manage *production* deployments that generally *only* contain MDX Script changes, on an otherwise fairly big project with *many* dimensions and measure groups, besides tons of calcs.

    We currently have developed an inhouse tool to strip annotations and object GUID's, for comparison purposes, one deployment to the next, and we were hoping to be able to only deploy the MDX script changes, via SSMS, but it appears that it's only feasible via SSDT, as Alexei pointed out.

    I have the Microsoft Analysis Services Projects version 2.8.17 (with no Automatic update), BIDE 2.3.4 (with No automatic update) and SSDT 2017 15.9.16.

    We were basing our assumption on this article, at https://snipplr.com/view/71793/deploy-mdx-calculations-with-xmla/,   but it appears that maybe this strategy is no longer an option, in the later versions of SSAS (2017)?

    many thanks for any additional thoughts,

    Cos

    Tuesday, October 22, 2019 1:53 PM
  • hi Alexei, I have replied above, more extensively, on the need for Production deployment, hopefully, via SSMS only, but it appears that that's not the case.  yes, the SSDT is always an option but we were hoping to Productionalize the deployment to not need SSDT.   many thanks -- 
    Tuesday, October 22, 2019 1:55 PM
  • VS is mostly for development work, not that much for production/support,
    SSMS is well suitable for monitoring/security
    For advanced admin/maintenance simplest approach would normally be to maintain these via AMO API (C#, PowerShell, etc.)

    MD object model can store multiple (perhaps up to 128 instances) different "copies" of MDX script per cube (with only one set as "default" for sessions, and I'm not sure you can control it dynamically via connection string during session initialization as there's no additional property documented for this purpose)
    For simplicity let's assume you have only one MDX script in your cube.
    Please explore it here in more details:
    https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.cube?view=analysisservices-dotnet

    Here's a quick PowerShell example to overwrite MDX script in a first cube in your database:

    using assembly Microsoft.Analysisservices
    cls;$i="MySSAS_Server\MySSAS_Instance"
    $b="MySSAS_Test_DB";$n="`r`n"#new line
    $s=new-object Microsoft.Analysisservices.Server
    $s.Connect($i);$d=$s.Databases[$b];$c=$d.Cubes[0];$m=$c.DefaultMdxScript
    $t="Calculate;"+$n+"create calculated member currentcube.[Measures].[my_test1] as 1;"
    $m.Commands[0].Text=$t
    $d.Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull,[Microsoft.AnalysisServices.UpdateMode]::Update)
    $s.Disconnect();Remove-Variable * -ea 0;$Error.Clear()

    Alternatively your script can come from external file or use replacement rules in existing script (read it first from cube) plus execute additional validation on a cube level for error-handling.

    • Marked as answer by Cos2008 Wednesday, October 23, 2019 2:25 PM
    Wednesday, October 23, 2019 1:04 AM
  • many thanks, Yuri, for the great insight, yes, AMO and/or Powershell should do the trick, need to dig more into the logistics but yes, this looks like a great solution!  

    thanks again,
    Cos


    • Edited by Cos2008 Wednesday, October 23, 2019 2:27 PM
    Wednesday, October 23, 2019 2:27 PM