SQL Server Analysis Services Multidimensional Modeling

Designing Actions

Actions in SQL Server Analysis Services (SSAS) are user-defined operations that can be initiated from a cube or a specific cell within a cube. They provide a way to link actions, reports, URLs, or even other SSAS actions to the context of data analysis. This allows users to perform deeper dives into data or trigger relevant business processes directly from their BI tools.

Types of Actions

SSAS supports several types of actions:

• Drillthrough Actions: These actions allow users to retrieve the detail rows from the source data that contribute to a specific cell's value. This is invaluable for understanding the underlying data.
• Report Actions: Launch specific reports (e.g., SQL Server Reporting Services reports) passing the current context of the cube selection as parameters to the report.
• URL Actions: Navigate the user to a web page or a resource on the web. The URL can be dynamically constructed using cube data.
• Command Actions: Execute a specified command. This can include invoking other SSAS actions or executing stored procedures.
• Proprietary Actions: Allow for custom actions defined by third-party applications.

Creating an Action

Actions are typically defined within SQL Server Data Tools (SSDT) or directly using MDX scripts. When creating an action, you'll specify:

• Name: A unique identifier for the action.
• Target Object: The cube, dimension, hierarchy, level, or attribute to which the action applies.
• Action Type: The type of action to perform (Drillthrough, Report, URL, etc.).
• Action Expression: The MDX expression that defines the action's behavior or data. For drillthrough actions, this specifies the target tables and columns. For URL or report actions, it defines the target and any parameters.
• Condition: An optional MDX expression to determine if the action should be visible or executable in a given context.

Example: Creating a Drillthrough Action

Let's say you want to enable users to see the individual sales transactions that make up the total sales for a specific product in a particular month. You would define a drillthrough action targeting the 'Sales' measure, associated with the 'Product' dimension and 'Date' dimension.

CREATE ACTION [New Action] ON CUBE
FROM
    [Adventure Works DW]
WHERE
    ([Measures].[Internet Sales Amount], [Product].[Product Categories].[Category].&[1], [Date].[Calendar Year].&[2014])
AS
    'Drillthrough to Sales Transactions',
    ACTIONS =
    {
        DRILLTHROUGH
        {
            [FactInternetSales].[SalesOrderNumber],
            [FactInternetSales].[OrderDate],
            [FactInternetSales].[CustomerName],
            [FactInternetSales].[ProductName],
            [FactInternetSales].[Quantity],
            [FactInternetSales].[UnitPrice],
            [FactInternetSales].[ExtendedSalesAmount]
        }
        ON 1
    }
            

Example: Creating a URL Action

You can create a URL action to link to a product detail page on your company's website. The URL can include the product key for dynamic linking.

CREATE ACTION [Product Detail Link] ON [Product].[Product]
FROM
    [Adventure Works DW]
AS
    'View Product Details Online',
    ACTIONS =
    {
        URL
        PROPERTIES
        {
            URL PROPERTY = 'http://www.example.com/products?id=' +
                STRIP([Product].[Product Key].CURRENTMEMBER.PROPERTIES("KEY"))
        }
    }
            

Best Practices for Actions

• Clarity: Ensure action names and descriptions are clear and intuitive to users.
• Contextivity: Design actions that are relevant to the data being viewed. Use conditions to control visibility.
• Performance: Optimize drillthrough actions by selecting only necessary columns and ensure source tables are well-indexed.
• User Experience: Make actions easily discoverable and accessible from BI tools.