MDX Script Injection - code into an #SSAS cube's #MDX Script - inject and enlarge MDX script. The MDX script can be easily updated or extended with code without the need to deploy the cube again. This can be very useful when MDX script  will dynamically expand to for e.g.

  • update cube side SETs
  • generate SETs from external data source like SQL querys or XML content
  • create dynamic CALCULATE statement
  • create ore change CALCULATE MEBER, SCOPE, ACTION without using Cube Designer or the SQL Server Data Tools (SSDT)
  • update partial section or whole MDX script from external

Concept

With MDX script injection, add tag in the calculation script of the cube.  After cube is deployed on Analysis Server the MDX Script Injection modifies the calculation script. The tool evaluates and replaces all inject-sections with static MDX code without the need of expensive StrToSet() conversions. The injection program must be executed only once after a new cube script has been deployed.

MDX Script Injection is placed in comment sections on calculation script. All declarations are in XML. The definition with the data source and the query is declared in <InjectionDefinition/> tag. Each Injection has one unique id. The query results are placed between <InjectionBegin/>  and <InjectionEnd/> tag.

The following example shows the MDX-Script of a cube deployed from SSAS solution (please see the documentation for the complete tag)

/* <InjectionDefinition UniqueId='[Geography].[City] by localsource'>
<QueryCommand>
SELECT DISTINCT [City] FROM [dbo].[DimGeography] WHERE [StateProvinceCode] = 'CA'
</QueryCommand>
</InjectionDefinition> */

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


MDX-Script after MDX Script Injection was executed

/* <InjectionDefinition UniqueId='[Geography].[City] by localsource'>
<QueryCommand>
SELECT DISTINCT [City] FROM [dbo].[DimGeography] WHERE [StateProvinceCode] = 'CA'
</QueryCommand>
</InjectionDefinition> */

CREATE SET [MyGeographyExample]
AS{
/*<InjectionBegin UniqueId='[Geography].[City]'/>*/
[Geography].[City].[Alhambra], [Geography].[City].[Alpine], [Geography].[City].[Auburn], [Geography].[City].[Baldwin Park], [Geography].[City].[Barstow], [Geography].[City].[Bell Gardens], [Geography].[City].[Bellflower], [Geography].[City].[Berkeley], [Geography].[City].[Beverly Hills], [Geography].[City].[Burbank], [Geography].[City].[Burlingame], [Geography].[City].[Camarillo], [Geography].[City].[Canoga Park], [Geography].[City].[Carson], [Geography].[City].[Cerritos], [Geography].[City].[Chula Vista], [Geography].[City].[Citrus Heights], [Geography].[City].[City Of Commerce], [Geography].[City].[Colma], [Geography].[City].[Concord], [Geography].[City].[Coronado], [Geography].[City].[Culver City], [Geography].[City].[Daly City], [Geography].[City].[Downey], [Geography].[City].[El Cajon], [Geography].[City].[El Segundo], [Geography].[City].[Elk Grove], [Geography].[City].[Escondido], [Geography].[City].[Eureka], [Geography].[City].[Fontana], [Geography].[City].[Fremont], [Geography].[City].[Fullerton], [Geography].[City].[Gilroy], [Geography].[City].[Glendale], [Geography].[City].[Grossmont], [Geography].[City].[Hanford], [Geography].[City].[Hayward], [Geography].[City].[Imperial Beach], [Geography].[City].[Irvine], [Geography].[City].[La Jolla], [Geography].[City].[La Mesa], [Geography].[City].[Lake Elsinore], [Geography].[City].[Lakewood], [Geography].[City].[Lemon Grove], [Geography].[City].[Lincoln Acres], [Geography].[City].[Long Beach], [Geography].[City].[Los Angeles], [Geography].[City].[Mill Valley], [Geography].[City].[Milpitas], [Geography].[City].[Modesto], [Geography].[City].[Monrovia], [Geography].[City].[National City], [Geography].[City].[Newark], [Geography].[City].[Newport Beach], [Geography].[City].[Norwalk], [Geography].[City].[Novato], [Geography].[City].[Oakland], [Geography].[City].[Ontario], [Geography].[City].[Orange], [Geography].[City].[Oxnard], [Geography].[City].[Palo Alto], [Geography].[City].[Pleasanton], [Geography].[City].[Redlands], [Geography].[City].[Redwood City], [Geography].[City].[Sacramento], [Geography].[City].[San Bruno], [Geography].[City].[San Carlos], [Geography].[City].[San Diego], [Geography].[City].[San Francisco], [Geography].[City].[San Gabriel], [Geography].[City].[San Jose], [Geography].[City].[San Mateo], [Geography].[City].[San Ramon], [Geography].[City].[San Ysidro], [Geography].[City].[Sand City], [Geography].[City].[Santa Ana], [Geography].[City].[Santa Cruz], [Geography].[City].[Santa Monica], [Geography].[City].[Sherman Oaks], [Geography].[City].[Simi Valley], [Geography].[City].[Spring Valley], [Geography].[City].[Stockton], [Geography].[City].[Torrance], [Geography].[City].[Trabuco Canyon], [Geography].[City].[Union City], [Geography].[City].[Upland], [Geography].[City].[Vacaville], [Geography].[City].[Van Nuys], [Geography].[City].[Visalia], [Geography].[City].[Vista], [Geography].[City].[Walnut Creek], [Geography].[City].[West Covina], [Geography].[City].[Whittier], [Geography].[City].[Woodland Hills]
/*<InjectionEnd UniqueId='[Geography].[City]'/>*/
};


More example please, check example list on documentation.

Advantage

  • no embedded assembly call by querying the cube
  • no performance overhead querying the cube
  • no negative impact on cube cache

MDX Script Injection solution results in the best query performance (NO StrToSet, NO embedded SQL queries).  Exception: only in cases where set definition often changes during the day and there is a need to have dynamic up-to-date set definitions in the cube, you may opt for other approach at the cost of reduced query performance.

Supported data source

  • SQLNIC, OLEDB, ODBC
  • XML

**Community Edition**

The Community Edition is a available free of charge under the current project license. An Enterprise Edition (licensed per server instance) is available from me. You may send your request thru the CodePlex contact.
The Enterprise Edition includes extended features like

  • Unlimited number of InjectionDefinition on executes.
  • different execution mode to reduce update time on execution (OnDemand and Enforce)
  • support XML files as data source.

Last edited Apr 28, 2014 at 9:25 PM by marcodellacasa, version 22