MDX – 1


Microsoft SQL Server SSAS provides an architecture for access to multidimensional data. This data is summarized, organized, and stored in multidimensional structures called cubes for rapid response to user queries. MDX – Multi dimensional eXpressions , can be said as an extension of TSQL because both are fundamentally similar in concept. But if we go in details both has a lot of difference. In a query you can place a data item in row section or column section or in filter part.

A hello world MDX syntax is explained below ,

Select [Measures].[MeasureName] ON COLUMNS,

[DimensionName].[Attribute] ON ROWS

From [CUBENAME]

Ex.

Select [Measures].[FctCaseDetailsCount] ON COLUMNS,

[DimStage].[StageText] ON ROWS

From

[Cradle]

Will Return a single count of the mentioned measure

Select [Measures].[FctCaseDetailsCount] ON COLUMNS,

[DimStage].[StageText].Children ON ROWS

From

[Cradle]

The above statement will give counts for all the StageText Group . Instead of .Children, we can also put .Members which will give all the members as well as the ‘All’ part.

Select ([DimProgram].[ProgramName].Children, [Measures].[FctCaseDetailsCount]) ON COLUMNS,

[DimStage].[StageText].Children ON ROWS

From

[Cradle]

This will split up the count into different program names, Just like a cross tab.

Select NON EMPTY ([DimProgram].[ProgramName].Children, [Measures].[FctCaseDetailsCount]) ON COLUMNS,

NON EMPTY [DimStage].[StageText].Children ON ROWS

From

[Cradle]

Above statement will avoid the null rows. Just ecluded ‘NON EMPTY’ to the pull out part.

Advertisements

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s