It is the real truth.
, 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 🙂
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.
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.