MSBI # 43 – SSAS # 13 – How Defining Actions in SSAS

Hi friends,

Continuing from my today last two posts

• MSBI # 41 – SSAS # 11 – How to Defining Calculations in SSAS
• MSBI # 42 – SSAS # 12 – How to Defining Key Performance Indicators (KPIs) in SSAS

In this post we are going to cover Action in SSAS covering following points

• SQL Server Analysis Services supports the types of actions
• Defining and Using a Drillthrough Action
• To define the drillthrough action properties
• To use the drillthrough action
• To review the modified drillthrough action

In this post, we will learn to define actions in your Microsoft SQL Server Analysis Services (SSAS) project. An action is just a Multidimensional Expressions (MDX) statement that is stored in Analysis Services and which can be incorporated into client applications and started by a user.

SQL Server Analysis Services supports the types of actions

SQL Server Analysis Services supports the types of actions that are described in the following table.

CommandLine	Executes a command at the command prompt
Dataset	Returns a dataset to a client application.
Drillthrough	Returns a drillthrough statement as an expression, which the client executes to return a rowset
Html	Executes an HTML script in an Internet browser
Proprietary	Performs an operation by using an interface other than those listed in this table.
Report	Submits a parameterized URL-based request to a report server and returns a report to a client application.
Rowset	Returns a rowset to a client application.
Statement	Runs an OLE DB command.
URL	Displays a dynamic Web page in an Internet browser.

Actions let users start an application or perform other steps within the context of a selected item.

Defining and Using a Drillthrough Action 

We already learned to define a fact dimension so that users can dimension measures by the members of the fact dimension and return details about specific fact measures, such as order detail information. Dimensioning fact data by a fact dimension without correctly filtering the data that the query returns can cause slow query performance for all users, which unnecessarily frustrates users. We can eliminate this frustration by defining a drillthrough action to return the same kind of information but to restrict the total number of rows that are returned, which will significantly improve query performance fro all.

In the tasks in this topic, we will define a drillthrough action to return order detail information for sales to customers over the Internet.

To define the drillthrough action properties

1.In Cube Designer for the Analysis Services Tutorial cube, click the Actions tab.

The Actions tab includes several panes. On the left side of the tab are an Action Organizer pane and a Calculation Tools pane. The pane to the right of these two panes is the Display pane, which contains the details of the action that is selected in the Action Organizer pane.

The following image shows the Actions tab of Cube Designer.

2.On the toolbar of the Actions tab, click New Drillthrough Action.

A blank Action template appears in the Display pane.

3.In the Name box, change the name of this action to Internet Sales Details Drillthrough Action.

4.In the Measure group members list, select Internet Sales.

5.In the Drillthrough Columns box, select Internet Sales Order Details in the Dimensions list.

6.In the Return Columns list, select the Item Description and the Order Number check boxes, and then click OK. The following image shows the Action template as it should look at this point in this procedure.

7.Expand the Additional Properties box, as shown in the following image.

8.In the Maximum Rows box, type 10.

9.In the Caption box, type Drillthrough to Order Details….

These settings limit the number of rows returned and specify the caption that appears in the client application menu. The following image shows these settings in the Additional Properties box.

To use the drillthrough action

1.On the Build menu, click Deploy Analysis Services Tutorial.

2.When deployment has successfully completed, click the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.

3.Remove all hierarchies and measures from the Data pane and all dimension members from the Filter pane.

4.Add the Internet Sales-Sales Amount measure to the data area.

5.Add the Customer Geography user-defined hierarchy from the Location folder in the Customer dimension to the Filter pane.

6.In the Filter Expression list, expand All Customers, expand Australia, expand Queensland, expand Brisbane, expand 4000, select the check box for Adam Powell, and then click OK.

The total sales of products by Adventure Works Cycles to Adam Powell are displayed in the data area.

7.Click the data cell in the Data pane, then right-click that data cell and click Drillthrough to Order Details.

The details of the orders that were shipped to Adam Powell are displayed in the Data Sample Viewer, as shown in the following image. However, some additional details would also be useful, such as the order date, due date, and ship date. In the next procedure, you will add these additional details.

8.Click Close to close the Data Sample Viewer window.

To modify the drillthrough action

1.Open Dimension Designer for the Internet Sales Order Details dimension.

Notice that only three attributes have been defined for this dimension.

2.In the Data Source View pane, right-click an open area, and then click Show All Tables.

3.On the Format menu, point to Autolayout and then click Diagram.

4.Locate the InternetSales (dbo.FactInternetSales) table by right-clicking in an open area of the Data Source View pane, clicking Find Table and clicking dbo.FactInternetSales and clicking OK.

5.Create new attributes based on the following columns:

• OrderDateKey
• DueDateKey
• ShipDateKey

6.Change the Name property for the Due Date Key attribute to Due Date, and then change the Name Column property for this attribute to DimTime.SimpleDate (WChar).

7.Change the Name property for the Order Date Key attribute to Order Date, and then change the Name Column property for this attribute to DimTime.SimpleDate (WChar).

8.Change the Name property for the Ship Date Key attribute to Ship Date, and then change the Name Column property for this attribute to DimTime.SimpleDate (WChar).

9.Switch to the Actions tab of Cube Designer for the Analysis Services Tutorial cube.

10. In the Drillthrough Columns box, add the following columns to the Return Columns list and then click OK:

o Order Date

o Due Date

o Ship Date

The following image shows these columns selected.

To review the modified drillthrough action

1.On the Build menu, click Deploy Analysis Services Tutorial.

2.When deployment has successfully completed, switch to the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.

3.Click the single data cell, and then right-click that cell and click Drillthrough to Order Details.

The details of these orders shipped to Adam Powell are displayed in the Data Sample Viewer, including their order date, due date, and ship date information, as shown in the following image.

4.Click Close to close the Data Sample Viewer.

Hope this helps !!

Hope We have understood basic aspect of Action in SSAS and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource « Dactylonomy of Web Resource