SQL Server Analysis Services – Using the DMV MDSCHEMA_ACTIONS in SSAS

In last blog which was about Microsoft SQL Server Analysis Services – Tabular Model Actions which can be found here, described how to create new Actions in the Tabular Model using BIDS Helper 2012.

Using BIDS Helper, you can not only define new actions on the tabular model but also administer any existing actions such as – View, Delete and Edit.

There are two ways the developer can see the actual MDX that is being executed to see the underlying data which of course is generated at run time on the fly.

•    Run the SQL Profiler and capture the MDX.
•    Use the DMV MDSCHEMA_ACTIONS to view the actual metadata.

This post is about a useful DMV (Dynamic Management Views) MDSCHEMA_ACTIONS which is can be used to retrieve all the necessary information about the SSAS Actions that the user has defined on the tabular model (this will also work on the multidimensional model).

For this blog, I have deployed a tabular model by the name Model on the catalog AdventureWorks Tabular Model SQL 2012. Also, I have defined a drillthrough action on the measure group “Internet Sales”. You should read my earlier blog on how to define Actions on Tabular Model which is here.

Let’s get started…

1. Open SQL Server Management Studio and connect to Analysis Service.
2. Open the MDX window and copy this MDX and click on execute.

As there is a drillthrough action defined on this model, I see the output as shown in the graphic below:

To see any metadata for this Action, we can use the DMV MDSCHEMA_ACTIONS

• Open the XMLA window and execute a similar script

This will return

The node which is of more significance from point of view of this blog is <CONTENT>.

This node stores the actual MDX of the SSAS drillthrough action.

Also, you can also see the list of columns returned by the drillthrough action by viewing the code. See the graphic attached below, but this does not helps much for the very simple reason it is not user friendly.

Free take away:

• The SCHEMA_NAME for the DMV MDSCHEMA_ACTIONS is NULL, so the standard syntax like the one below will not work.

•  Analysis Service in multidimensional mode supports using external assemblies ASSP (you can find this assembly here), you can use the standard syntax like the one below

Reference:

http://msdn.microsoft.com/en-us/library/ms126032(v=sql.110).aspx

Note the value returned in the ACTION_TYPE is of type decimal but the return type mentioned in the link above is actually hexadecimal.

This is it!!

Regards

Raunak Jhawar

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook