This project has moved. For the latest updates, please go here.

MDX Script Injection v.3.7

How to use MDX Script Injection

  1. Add  <MDXInjection/> tags as comment  between block comment mark like /*…*/ in the calculation script of the cube.
  2. Deploy SSAS solution onto Analysis Server.
  3. Process the cube, in minimal run one structure process.
  4. Open comandline and execute mdc.CubeManager.exe with parameter.

Comandline Syntax

mdc.CubeManager.exe

/Server:  <string>     server\instance name (short form /Ser)
/Catalog: <string>     catalog name (short form /Cat)
/Cube:    <string>     cube name (short form /Cu)

Element

Example calculation script

  1. Example simple union using data source view connection string
  2. Example query Adventure Works DW using data source view connection string
  3. Example query Adventure Works DW using local connection string
  4. Example xPath query using local XML source


1. Example

Simple union using data source view connection string

/*
<MDXInjection>
<InjectionDefinition UniqueId='[UnionExample]'>
  <ProcessStatus>Unprocessed</ProcessStatus>
  <QueryCommand>
       SELECT 2
       UNION ALL
       SELECT 3
  </QueryCommand>  
  <ConnectionString>Adventure Works DW</ConnectionString>
  <LastHandling>2010-06-06T12:59:46.2958023+02:00</LastHandling>
  <StatusMessage></StatusMessage>
</InjectionDefinition>
</MDXInjection>
*/

CREATE SET [MyUnionExample1]
AS
{    /*<InjectionBegin UniqueId='[UnionExample]'/>*/
    /*<InjectionEnd UniqueId='[UnionExample]'/>*/
};

 

Result

CREATE SET [MyUnionExample1]
AS
{    /*<InjectionBegin UniqueId='[UnionExample]'/>*/
    2, 3
    /*<InjectionEnd UniqueId='[UnionExample]'/>*/
};

 


2. Example

Connection is taken from Analysis data source view

/*
<MDXInjection>
<InjectionDefinition UniqueId='[Geography].[City] by localsource'>
  <ProcessStatus>Unprocessed</ProcessStatus>
  <ConnectionString>Adventure Works DW</ConnectionString>
  <QueryCommand>
   SELECT DISTINCT [City]
          FROM [dbo].[DimGeography]
         WHERE [StateProvinceCode] = 'CA'
  </QueryCommand>  
  <DelimiterString>, </DelimiterString>
  <RowItemStringFormat>[{0}]</RowItemStringFormat>
  <LastHandling>2010-06-06T12:59:46.2958023+02:00</LastHandling>
  <StatusMessage></StatusMessage>
</InjectionDefinition>
</MDXInjection>
*/

CREATE SET [MyGeographyExample2]
AS
{   /*<InjectionBegin UniqueId='[Geography].[City] by localsource'/>*/
    /*<InjectionEnd UniqueId='[Geography].[City] by localsource'/>*/
};

 

Result

CREATE SET [MyGeographyExample2]
AS
{   /*<InjectionBegin UniqueId='[Geography].[City] by localsource'/>*/
    [Alhambra], [Alpine], [Auburn], [Baldwin Park], [Barstow], …
/*<InjectionEnd UniqueId='[Geography].[City] by localsource'/> */ };

 


3. Example

Query Adventure Works DW using local connection string.

/*
<MDXInjection>
<InjectionDefinition UniqueId='[Geography].[City]'>
  <ProcessStatus>Unprocessed</ProcessStatus>
  <ConnectionString>Provider=SQLNCLI10.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008</ConnectionString>
  <QueryCommand>
        SELECT DISTINCT [City]
          FROM [dbo].[DimGeography]
         WHERE [StateProvinceCode] = 'CA'
  </QueryCommand>  
  <DelimiterString>, </DelimiterString>
  <RowItemStringFormat>[{0}]</RowItemStringFormat>
  <LastHandling>2010-06-06T12:59:46.2958023+02:00</LastHandling>
  <StatusMessage></StatusMessage>
</InjectionDefinition>
</MDXInjection>
*/

CREATE SET [MyGeographyExample3]
AS
{    /*<InjectionBegin UniqueId='[Geography].[City]'/>*/
    /*<InjectionEnd UniqueId='[Geography].[City]'/>*/
};

 

Result

CREATE SET [MyGeographyExample3]
AS
{   /*<InjectionBegin UniqueId='[Geography].[City] by localsource'/>*/
[Alhambra], [Alpine], [Auburn], [Baldwin Park], [Barstow], …
/*<InjectionEnd UniqueId='[Geography].[City] by localsource'/> */ };


4. Example

xPath query using local XML source.

/*
<MDXInjection>
<InjectionDefinition UniqueId='[Geography].[City] by XML'>
  <ProcessStatus>Unprocessed</ProcessStatus>
  <ConnectionString>MDXInjection.xml</ConnectionString>
  <QueryCommand>//dbo.DimGeography/@City</QueryCommand>  
  <LastHandling>2010-06-06T12:59:46.2958023+02:00</LastHandling>
  <StatusMessage></StatusMessage>
</InjectionDefinition>
</MDXInjection>
*/

CREATE SET [MyGeographyExample4]
AS
{   /*<InjectionBegin UniqueId='[Geography].[City] by XML'/>*/
    /*<InjectionEnd UniqueId='[Geography].[City] by XML'/>*/
};

 

Source

<?xml version="1.0" encoding="utf-8"?>
<root>
<dbo.DimGeography City="Alhambra" />
<dbo.DimGeography City="Alpine" />
<dbo.DimGeography City="Auburn" />
</root>

 

Result

CREATE SET [MyGeographyExample4]
AS
{   /*<InjectionBegin UniqueId='[Geography].[City] by localsource'/>*/
    [Alhambra], [Alpine], [Auburn]
/*<InjectionEnd UniqueId='[Geography].[City] by localsource'/> */ };

Last edited Apr 21, 2014 at 3:46 PM by marcodellacasa, version 31