Integrate your SSAS based Excel reports … A SSAS action story

 You see there is one constant. One universal.

  It is the real truth.

 Causality.

 , reaction. Cause and effect.

 – Matrix Reloaded, Merovingian

The other day I was working on a major data warehouse release for a client. One of the many improvements is that I have enabled the client to browse back to his main operational system (in this case Microsoft CRM 4.0) from within Excel. I have done this by configuring a feature called “Actions” in SSAS 2008 (SQL Server Analysis Services).

SSAS distinguishes three kind of action types:

  • Drillthrough action
  • URL action
  • Reporting action

The names of each of these types make them self explantory, however, I have a blog to fill so here we go 🙂

Drillthrough action
Used to define which attributes are visibile after you have drilled through. Example;  I have drilled through to the underlying assignments which make up my total planning for a certain week. Out – of – the – box SSAS will show you the details . In this case it consists of:

  • Employee name
  • Taks name
  • Project number
  • Planned date
  • Planned hours
  • Actual hours

But let’s say I want to see the project type and name as well. These attributes are in a seperate dimension. Defining a”Drillthrough action” will enable you to select other and / or more attributes which you want to see when you drill through. More information can be found here.

URL action
This enables the user to jump from any level in a SSAS – based report to a website. You may configure a static or dynamic URL. A dynamic URL is configured using MDX. Yes MDX folks and no in this case “using MDX” does not equal a headache, really.

There are loads of blogs out there which you can read to find out how URL action works. I just want to give you one important hint which many leave out (which I found out together with a colleague). Make sure that the attribute level you use in the URL is unique. For example; let’s say I want to jump from my Product level to a webiste which shows me the details of that product. The URL contains a unique identifier to identifiy the product.

In SSAS the product name on which you can click to go to the website should be unique as well. Product name should have the surogate or natural key defined as its key. This way SSAS will know which product name belongs to which any other attribute, in this example the corresponding unique identifier used in the URL.

Excel action navigation example

A reporting action
A reporting action is similar to a standard or URL type action. you basicly choose the output form (PDF, HTML etc) and configure the URL pointing to the report. This is very handy when u need a ‘hard copy pre formatted report’ based on an analaysis made using a self service BI client tool like Excel. More information can be found here.

Advertisements

One thought on “Integrate your SSAS based Excel reports … A SSAS action story

  1. Hi
    When you say; Make sure that the attribute level you use in the URL is unique.
    Do you mean that you only can chose one member or value to send to SSAS.
    For example if you want to filter on products in Excel, chosing 3 products from the report filter “Product”….that will not work?
    Does that mean that you only can send in one unique member, currentmember to the cube?
    BR
    /Martin

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