MSBI # 34 – SSAS # 10 – How to Defining Actions in SSAS

Hi geeks,

Continuing from my last post on SSAS

    Topics covered in this post are Basics of actions in Analysis Services

  • Analysis Services supports the types of actions
  • What is Action in Analysis Services
  • Defining and Using a Drill through Action
  • To define the drillthrough action properties
  • To use the drillthrough action
  • To modify the drillthrough action

Now lets go one by one for each topics

    Basics of actions in Analysis Services
    In this post , we will learn to define actions in Analysis Services (SSAS) project.
    What is Action in Analysis Services ?
    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 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 Drill through Action 

In previous articles , we have 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. You 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, you 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.

clip_image002

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

A blank Action template appears in the Display pane.

clip_image004

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.

clip_image005

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

clip_image006

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.

clip_image008

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.

clip_image010

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:

  • Order Date
  • Due Date
  • Ship Date

The following image shows these columns selected.

clip_image012

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.

clip_image014

4.Click Close to close the Data Sample Viewer.

Hope this helps !!

Thanks for reading my blog !!

For more interesting links and daily update please subscribe our link resource website

Todays link are follows:

Link Resource # 23: Sept 04–Sept 07« Dactylonomy of Web Resource

Leave a Reply

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

Blog at WordPress.com.

Up ↑

%d bloggers like this: