SQL Server 2012 SSAS Tabular Model Actions

With SQL Server 2012, SSAS has undergone a major change. There is BISM (If you are late into the party, read about BISM here and SQL Server 2012 SSAS Tabular Model here). If you ever had a chance to work with multidimensional models or had a chance to do a 1-1 comparison between SSAS multidimensional and SSAS tabular, it should not take much time to figure out that many COOL features are found missing in the tabular model. One such is ACTIONS, or you might want to know them as SSAS Actions (read here for more details).

Apparently, there is no easy method to create and manage Actions in tabular models as the design studio (SQL Server Data Tools). Yes, you can edit the BIML script to add the piece of code which would do all the ACTION, but manually editing the .BIM file has its own peril and I will not discuss this here.

So, now you have a fresh new tabular model which is deployed and processed and you are ready for end user reporting and analysis using either Reporting Services or Performance Point Services. Let’s experiment with some MDX performing DRILLTHROUGH. Open SQL Server Management Studio, connect to Analysis Services – the tabular instance and select the model and click on ta new window to open the MDX query pane. As you can see below there is a sample MDX to perform a DrillThrough on the measure [Internet Total Sales] where the country name is “United States”.

DRILLTHROUGH MAXROWS 100
SELECT [Measures].[Internet Total Sales] ON 0
FROM [AW Cube]
WHERE [Geography].[Country Region Name].&[United States]

The dataset returned is overwhelming; you have both values (measures) and keys (date key etc.), not all information is useful from the end user per se. What you can do to restrict this is explicitly state the columns required, after a DRILLTHROUGH on any cell. For example on drillthrough on the measures related to Internet Sales, only, product details (product name, category and sub category) along with the buyers details should be made available.

Had this been SSAS multidimensional, Actions can be added as and when required using the Actions tab but no such “Actions” tab is available in design studio for tabular models. This is where an excellent tool BIDS Helper proves extremely handy. This is a free download available at codeplex (See this)

Once you download and install the BIDS helper, open the SSDT and navigate to solution explorer and right click on your .BIM file and you will see three new menu items which were previously not available. Using the BIDS helper addin you can edit the BIM file (the tabular model) to add Actions.

Click on the Tabular Actions Editor menu item, this will present you with a new window using which you can add new SSAS Actions and manage and edit any existing Actions in the tabular model. What this really does is that behind the scene, in the .BIM file, it adds the necessary code to perform the desired Action. Let’s add action to the tabular model!!!

The action that we are going to create will be of type drillthrough and on interaction the action will present the user with Customer details, Date, Promotion details, Item Cost and Tax paid.

Click on OK and now build the model, deploy the model and process the model. Just incase if you are curious what exactly did the BIDS Helper added to the .BIM file, right click on the model and click on View Code. Find <Actions> and there you can see the extra code added which will perform the desired action.

An interesting point that you should note take cognizance is that, you have defined the DRILLTHROUGH action on Measure Group, which effectively means that all measures part of the measure group “Internet Sales” will have the capability to Drillthrough and return the underlying result set. In order to restrict this default behavior to say only to measures related to [Internet Sales Amount], [Internet Freight Amount] all you need to ensure is that you delete/remove any extra code block from the .BIM file from the Action code block so effectively the action created is applied to only the measures you actually require. But again a word of caution, “Carefully” edit the .BIM file.

Now open new Excel, create a new connection to the Analysis Server and connect to the SSAS tabular model which you have added the new actions.

Here you will see a new Action has been created for the measure “Internet Total Freight” by name “Discover Customers”. Click on the action and new sheet will have the details of the action.

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