CODEWITHSUNDEEP
sqlssasmdxcube
user2966445
user2966445

Reputation: 1344

Updating Measure in SSAS Cube

Is there a way to create a new measure in an SSAS (SQL Server Analysis Server) cube, other than modifying and re-running the entire CREATE XMLA cube script?

Tried the following stand-alone MDX queries:

Query #1

CREATE MEMBER CURRENTCUBE.Measures.[TestMeasure] AS 
NULL;

Result:

Executing the query ...
No cube specified. A cube must be specified for this command to work.
Execution complete

Query #2

CREATE MEMBER [CubeName].Measures.[TestMeasure] AS 
NULL;

Result:

Executing the query ...
Execution complete

Note: Query #2 executes but measure does not appear in "CubeName" cube.

Upvotes: 2

Views: 3938

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

CREATE MEMBER is scoped, so those members don't persist outside of your session/query.

In order to create a new permanent calculated member on your CUBE you need to use an ALTER XMLA script, not a CREATE XMLA script.

One example, shamelessly pilfered from the web:

<Alter AllowCreate="true" ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>CVCC</DatabaseID>
    <CubeID>CVCC</CubeID>
    <MdxScriptID>MDXScript</MdxScriptID>
  </Object>
  <ObjectDefinition>
    <MdxScript>
      <ID>MDXScript</ID> 
      <Name>MDXScript</Name>
      <Commands>
        <Command>
          <Text> <!–if you want to figure out what to paste below build the calculation in visual studio then look at the ‘script view’–>
            CALCULATE;
            CREATE MEMBER CURRENTCUBE.[MEASURES].[TestMeasure]
            AS **Your calc goes here**;
          </Text>
        </Command>
      </Commands>
    </MdxScript>
  </ObjectDefinition>
</Alter>

Upvotes: 2

Related Questions