Een ‘echte’ drillthrough naar relationele data vanuit een SSAS kubus implementeren

Tijdens een Analysis Services training eerder deze week vroeg één van de deelnemers hoe je een ‘echte’ drillthrough zou kunnen implementeren vanuit een SSAS kubus. In SQL Server Analysis Services 2000 was dit het standaard gedrag van een drillthrough actie. Sinds de introductie van het UDM (Unified Dimensional Model) in SQL Server Analysis Services 2005, veronderstelt Microsoft dat je alle data die relevant is tijdens het gebruik van een Analysis Services database ook in die database opneemt. De standaard drillthrough actie is sindsdien gewijzigd in een soort snelkoppeling om vanuit ieder niveau in een kubus snel de bijbehorende leaf-level data te verkrijgen. Data die wel in de kubus aanwezig moet zijn.

Een hele eenvoudige en wat mij betreft valide vraag, maar het antwoord blijkt niet zo eenvoudig. De kern van het antwoord is gebaseerd op het gebruik van een Action van het type Rowset. Bedenk dat een Analysis Services action een soort callback mechanisme is waarmee een client applicatie zoals Excel een actie kan initiëren op de Analysis Services server waarna de server het resultaat van die actie teruggeeft aan de client applicatie. Het type van de actie bepaalt het soort resultaat. Bij de Rowset actie is het resultaat een rowset. De documentatie over de actie typen anders dan URL, Reporting en Drillthrough, is op zijn zachtst gezegd ‘beperkt’ en verwarrend. Zo zegt Books Online over de Rowset action: Returns a Multidimensional Expressions (MDX) statement, representing a tabular rowset, to be run and displayed by the client application. Dat klinkt in eerste instantie niet bepaald als geschikt om een relationele database te benaderen. En als je kijkt wat je dan in zou moeten vullen bij de ‘Action Expression’: Type the Multidimensional Expressions (MDX) expression that returns the string returned by the action to the client application for execution.

Na wat studie, het lezen van diverse blogs en forums en een uurtje experimenteren ben ik er uit. Je moet bij de Action Expression een string invullen. Die string moet een MDX expressie bevatten. Die expressie wordt door de client applicatie uitgevoerd wanneer de gebruiker de action initieert. En daarmee verandert de vraag in: hoe kun je vanuit een MDX expressie een SQL query uitvoeren? Het antwoord is: via een Analysis Services stored procedure. Een Analysis Services stored procedure is een heel ander soort stored procedure dan degene die je kent uit SQL Server. Je moet namelijk een functie schrijven in een .NET assembly en die registreren op de Analysis Services server. Je kunt de geregistreerde functie daarna aanroepen zoals je iedere andere MDX functie aanroept in een MDX statement of  buiten een MDX statement door middel van het MDX Call statement. De functie moet óf niets retourneren óf een rowset. En daar is de linking pin met de Rowset action! Het stappenplan om een drillthrough naar relationele data vanuit een SSAS kubus te implementeren wordt dus:

1. Schrijf een .NET functie die een rowset retourneert in een .NET assembly.
2. Zet de resulterende DLL ergens op de Analysis Services server en registreer de DLL op de server.
3. Maak een Rowset action aan en schrijf in de Action Expression een string met daarin het MDX Call statement naar de stored procedure.

In het vervolg van dit artikel, veronderstel ik dat je op een lokale Analysis Services server werkt en dat je de AdventureWorks2008DW database tot je beschikking hebt.

1. Schrijf de .NET functie

De volgende code demonstreert een functie ‘Query’ die een rowset retourneert gegeven een connectiestring en een SQL query. Let op dat de code geen foutcontroles bevat! Het is slechts een voorbeeld!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ASSP
{
    public class MyASSPClass
    {
        public DataTable Query(string strConnection, string strQuery)
        {
            DataTable dt = new DataTable("ResultSet");
            OleDbConnection conn = null;

            conn = new OleDbConnection(strConnection);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = new OleDbCommand(strQuery, conn);
            da.Fill(dt);
            conn.Close();

            return dt;
        }
    }
}

Deze code compileer je naar een Windows Class Library zodat je een DLL krijgt. Je kunt hiervoor overigens ook even op CodePlex kijken. Hier staat het Analysis Services Stored Procedure project wat iets soortgelijks doet.

2. Registreer de DLL op de Analysis Services server

De DLL uit de vorige stap kopieer je naar een plek op de Analysis Services server. De DLL moet vervolgens worden geregistreerd om de methoden die je in de class hebt gedefinieerd te kunnen gebruiken als stored procedures. Ga daarvoor naar SQL Server Management Studio, maak verbinding met de Analysis Services instance, klikt met rechts op de Assemblies folder en kies New Assembly… Kies vervolgens de locatie van de DLL, geef de assembly een naam en zet Permissions op Unrestricted. Dit laatste is iets anders wat bedroevend slecht (of eigenlijk gewoon weer niet) beschreven wordt in Books Online. Ik weet alleen dat met de default ‘Safe’ het aanroepen van de stored procedure niet lukt.

Hierna kun je in SQL Server Management Studio testen of je stored procedure werkt. Open daarvoor een nieuw MDX query window op de Analysis Services server waarop je zojuist de stored procedure hebt geregistreerd en schrijf de volgende query:

Call ASSP.Query(‘Provider=SQLNCLI10.1;Data Source=Localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008’, ‘SELECT * FROM DimEmployee’)

Als je dit MDX statement uitvoert, moet je als resultaat de records uit de rowset te zien krijgen.

3. Maak een Rowset action aan

De laatste stap is het aanmaken van een Rowset action. Kies daarvoor in Business Intelligence Development Studio op de Actions tab van de gewenste cube voor New Action. Geef de Action een naam en kies als Target type ‘Cells’. Om te testen of ook hier de stored procedure werkt, kun je als eerste de volgende Action Expression opgeven:

"Call ASSP.Query(‘Provider=SQLNCLI10.1;Data Source=Localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008’, ‘SELECT * FROM DimEmployee’)"

Merk op dat dit dezelfde expressie is als die je eerder in SQL Server Management Studio hebt getest, met één verschil: het hele MDX statement moet als string opgegeven worden!

Als je hierna op de Browser tab van de Cube designer met rechts op een willekeurige cel klikt, moet je de nieuwe action te zien krijgen:

Na het kiezen van de Action, worden de resultaten van de rowset in een nieuw venster getoond.

Iets realistischer wordt het wanneer we ook een deel van de context waar we klikken doorgeven aan de stored procedure:

"Call ASSP.Query(‘Provider=SQLNCLI10.1;Data Source=Localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008’, ‘select * from DimProduct where EnglishProductName = ”" + [Product].[Product Categories].CurrentMember.Name + "”’);"

Let op de dubbele quotes die je nodig hebt om de variabele member naam te gebruiken als string in de SQL query! Merk ook op dat het doorgeven van de context van waar je klikt knap lastig kan worden wanneer die voortdurend wijzigt. Een standaard Drillthrough action geeft altijd automatisch de gerelateerde onderliggende feiten terug, met deze methode zul je dat zelf moeten doen.

Conclusie

Microsoft heeft duidelijk haar best gedaan een ‘echte’ drillthrough naar een relationele database zo moeilijk mogelijk te maken. Of in ieder geval hebben ze niet hun best gedaan het makkelijk te maken! Wil je eenvoud in je oplossing, ga dan voor de standaard Drillthrough action en zorg dat alles wat je wilt laten zien in de Analysis Services database (het UDM) aanwezig is. Een ander eenvoudiger alternatief is gebruik maken van de Reporting action om in een rapport details uit een relationele database te laten zien. Wil je toch echt doordrillen naar een relationele database op de hier beschreven manier, wees dan voorbereid op enig ontwikkelwerk en hou rekening met het onderhoud daarvan. Met name wanneer je ook de context mee wilt nemen van de actie naar een SQL statement, is de hier beschreven manier niet bepaald onderhoudsvriendelijk!

Hoewel de in dit artikel beschreven oplossing misschien minder geschikt is voor het hier beoogde doel, hoop ik wel dat deze oplossing je inspireert tot het implementeren van andere oplossingen waarvoor de techniek van het aanroepen van methoden in .NET assemblies vanuit MDX zich wel goed leent. Het initiëren van iets, wat dat dan ook is, vanuit een Analysis Services client, lijkt mij iets waar je hele fraaie oplossingen mee kunt bedenken!

Share this