SSAS Local Cube Kopie von SSAS Server Cubes erstellen.

Local Cubes werden schon immer von den Analysis Services angeboten. Hier ist interessant, dass der Cube von der Client Komponente „Analysis Services OLE DB Provider“ erstellt wird, welche hier angeboten wird und auch mit Excel installiert wird. Es gibt also sowas wie Analysis Services Express.  Um einen Local Cube zu erstellen ist kein SSAS Server notwendig. Man braucht das XMLA von der SSAS Datenbank. Chris Webb hat das schon hier beschrieben. CubeSlice, Bi-Lite, OlapCube und weitere BI Anbieter nutzen diese Funktionalität. Der Local Cube darf keine Partitionen oder Distinct Count Measures beinhalten.

Diese Prozedur erstellt eine Kopie von einem Server Cube. Es müssen die „Analysis Management Objects“ und „ADOMD.NET“ referenziert werden.

        Dim start As DateTime = Now
        ‚Erstelle das Server Object
        Dim Svr As New Microsoft.AnalysisServices.Server
        ‚Verbindung zum Server erstellen
        Svr.Connect(„localhost“)
        ‚Verbindung zur Datenbank
        Dim Db As Microsoft.AnalysisServices.Database = Svr.Databases.FindByName(„Fussball“)
        ‚Erstelle das Scripter Object
        Dim scripter As New Microsoft.AnalysisServices.Scripter
        ‚Erstelle die Script Info
        Dim scriptInfo As New Microsoft.AnalysisServices.ScriptInfo _
        (Db, Microsoft.AnalysisServices.ScriptAction.Create _
         , Microsoft.AnalysisServices.ScriptOptions.Default, False)
        ‚Das Scripter Object erwartet ein Array
        Dim scriptInfos() As Microsoft.AnalysisServices.ScriptInfo = {scriptInfo}
        ‚Der Stream
        Dim stream As New System.IO.StringWriter
        Dim writer As New System.Xml.XmlTextWriter(stream)
        ‚Das XMLA Scipt erstellen
        scripter.Script(scriptInfos, writer)
        ‚Dem XMLA Scipt das Process hinzufügen
        Dim strLocalCubeXMLA As String
        strLocalCubeXMLA = „<Batch xmlns=““http://schemas.microsoft.com/analysisservices/2003/engine„“>“
        strLocalCubeXMLA += stream.ToString
        strLocalCubeXMLA += „<Parallel><Process>“
        strLocalCubeXMLA += „<Object><DatabaseID>“ & Db.ID & „</DatabaseID></Object>“
        strLocalCubeXMLA += „<Type>ProcessFull</Type>“
        strLocalCubeXMLA += „<WriteBackTableCreation>UseExisting</WriteBackTableCreation>“
        strLocalCubeXMLA += „</Process></Parallel></Batch>“
        ‚Mit dem Local Cube verbinden
        Dim localSvr As New Microsoft.AnalysisServices.Server
        localSvr.Connect _
        (My.Computer.FileSystem.SpecialDirectories.MyDocuments & „\DerLocalCube.cub“)
        ‚Datenbanken löschen. Falls die Datei schon besteht, wird das Create nicht ausgeführt
        For i As Integer = localSvr.Databases.Count – 1 To 0 Step -1
            localSvr.Databases(i).Drop()
        Next i
        ‚Das XMLA ausführen
        localSvr.Execute(strLocalCubeXMLA)
        ‚Aufräumen
        stream.Dispose()
        localSvr.Disconnect() : localSvr.Dispose()
        Svr.Disconnect() : Svr.Dispose()

        MsgBox(„done in “ & (Now – start).ToString)



Excel Funktion zum lesen von SSAS Datenbank Informationen

Gerade habe ich einen Beitrag gelesen, wie man Informationen über eine SSAS Datenbank in Excel oder andere Client Applikationen anzeigen kann. Da habe ich mich gefragt, ob das nicht auch mit VBA in Excel geht. Auch die Analysis Services bieten Schemas. Die werden hier gelesen.

Function CubeProcessed(Connection As String)

    On Error GoTo errorhandler
   
    Dim conn As Object: Set conn = CreateObject(„ADODB.CONNECTION“)
    Dim rec As Object
    Dim wc As WorkbookConnection
    On Error GoTo unknownConn
    Set wc = ThisWorkbook.Connections(Connection)
    On Error GoTo errorhandler
        
    If wc.Type = xlConnectionTypeOLEDB Then
        If wc.OLEDBConnection.OLAP = True Then
          conn.ConnectionString = Mid(wc.OLEDBConnection.Connection, 7)
          conn.Open
          Set rec = conn.OpenSchema(32, Array(„“, „“, wc.OLEDBConnection.CommandText))
          CubeProcessed = rec.Fields(„LAST_DATA_UPDATE“).Value
          Exit Function
           
        Else
            CubeProcessed = „This is not an OLAP Connection“
        End If
    Else
        CubeProcessed „This is not an OLEDB Connection“
    End If
   
   
    Exit Function
   
unknownConn:
   
    CubeProcessed = „unknown Connection“: Exit Function
   
errorhandler:
   
    CubeProcessed = Err.Description
   

End Function

Reporting Services und SAP BW

Mit der Veröffentlichung von dem SP1 für den SQL Server 2005 kommt eine wichtige Neuerung
innerhalb der Reporting Services.
 
Man kann aus den SSRS nun auch auf SAP Cubes per MDX zugreifen.
 
 
Das zum Download angebotene White-Paper ist sehr gut und beschreibt
auch das Trouble-Shooting.  

Microsoft übernimmt Proclarity

Microsoft hat gestern bekannt gegebeben, dass sie Proclarity übernehmen.
Da hat MS mal auf die Community gehört. MS hat sich nur im Ziel vertan.
Chris Webb und andere hatten sich Tableau gewünscht.
 
Wenn Proclarity-Software zu attraktiven Preisen angeboten wird, müssen sich die ISVs
schnell eine neue Nische suchen.
 
Microsoft ist das BI-Geschäft mit Enterprise Frontends und BI, speziell in Deutschland, eher zögerlich
angegangen. Gibt es eigentlich einen BI MVP in Deutschland? MS Deutschland Präsentationen zum Thema
beginnen mit der Information-Worker Pyramide und enden nach 20 Minuten  mit einem HTML Bericht aus den
Reporting Services.
 
Nun stehen BI-Frontends im Businness-Plan.
Da wird Microsoft engagierter im Markt auftreten. Das muss aber nicht sofort gelingen(s. Navision).
Auch bleibt abzuwarten, ob MS die Innovationskraft von Proclarity erhalten kann.
 
Vielleicht bekommen wir ja schon vor Excel 12 eine BI-Suite von Microsoft.
Auf jeden Fall ist Proclarity der stärktste 2k5 Client und vielleicht auch bald bezahlbar.
Ich freue ich darauf.

Rekursive Abfragen in SK5

Rekursive Abfragen in SQL Server 2005

 Was ist ein CTE (Common Table Expression) ?

 Ein CTE ist eine temporäre Ergebnismenge, die im Ausführungskontext einer einzelnen Abfrage definiert ist. Sie ähnelt einer abgeleiteten Tabelle (Unterabfrage in der FROM Klausel), da sie ebsonso wie diese nur im Kontext der Abfrage definiert ist und nicht als Objekt in der Datenbank gespeichert wird. Im Unterschied zur abgeleiteten Tabelle kann sie allerdings selbstreferenzierend sein, womit rekursive Abfragen definiert werden können.

Hier zunächst ein einaches Beispiel einer nicht rekursiven CTE:

 

USE AdventureWorks;

GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)

AS

(

    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

    FROM Sales.SalesOrderHeader

    GROUP BY SalesPersonID

)

SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,

    E.ManagerID, OM.NumberOfOrders, OM.MaxDate

FROM HumanResources.Employee AS E

    JOIN Sales_CTE AS OS

    ON E.EmployeeID = OS.SalesPersonID

    LEFT OUTER JOIN Sales_CTE AS OM

    ON E.ManagerID = OM.SalesPersonID

ORDER BY E.EmployeeID;

GO

Hier ist ein Teil der Ergebnismenge:

 

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate

———– ————– ———- ——— ————– ———-

268         48             2004-06-01 273       NULL           NULL

275         450            2004-06-01 268       48             2004-06-01

276         418            2004-06-01 268       48             2004-06-01

277         473            2004-06-01 268       48             2004-06-01

 

In der Hauptabfrage wird die CTE zweimal referenziert, um die Bestellungen der Angestellten und deren Vorgesetzten gegenüberzustellen.

 

Was ist ein rekursiver CTE ?

Um hierarchische Abfragen zu erstellen, können rekursive CTEs verwendet werden. Ein rekursiver CTE besteht im einfachsten Fall aus zwei Teilen: einem Anchor member und einem rekursiven member.

Hier folgt die Struktur eines rekursiven CTEs:

 

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT * FROM cte_name

 

Hier folgt der Ablauf der Ausführung:

1.              Zunächst wird zwischen anchor von dem rekursiven member getrennt

2.              der anchor member wird ausgeführt, welcher Ergebnismenge T0 ergibt. (Beginn der Ausführung)

3.              der rekursive member mit Ti als input und Ti+1 als output wird ausgeführt. (Rekursion)

4.              Schritt 3 wird ausgeführt, bis eine leere Ergebnismenge zurückgegeben wird. (Abbruchbedingung)

5.              Rückgabe der Ergebnismenge. Dies ist der UNION ALL von T0 bis Tn.

 

Hier ein Beispiel:

 USE AdventureWorks;

GO

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)

AS

(

— Anchor member definition

    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,

        0 AS Level

    FROM HumanResources.Employee AS e

    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

    WHERE ManagerID IS NULL

    UNION ALL

— Recursive member definition

    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,

        Level + 1

    FROM HumanResources.Employee AS e

    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

    INNER JOIN DirectReports AS d

        ON e.ManagerID = d.EmployeeID

)

— Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

INNER JOIN HumanResources.Department AS dp

    ON DirectReports.DeptID = dp.DepartmentID

WHERE dp.GroupName = N’Research and Development‘ OR Level = 0;

GO

 

Gelb markiert ist der Teil, in dem die Rekursion ausgelöst wird, hier refernziert sich der CTE selbst

Hier ist die Ergebnismenge:

ManagerID EmployeeID Title                                   Level

——— ———- ————————————— ——

NULL      109        Chief Executive Officer                 0

109       12         Vice President of Engineering           1

12        3          Engineering Manager                     2

3         4          Senior Tool Designer                    3

3         9          Design Engineer                         3

3         11         Design Engineer                         3

3         158        Research and Development Manager        3

3         263        Senior Tool Designer                    3

3         267        Senior Design Engineer                  3

3         270        Design Engineer                         3

263       5          Tool Designer                           4

263       265        Tool Designer                           4

158       79         Research and Development Engineer       4

158       114        Research and Development Engineer       4

158       217        Research and Development Manager        4

(15 row(s) affected)

 

Der rot markierte Teil ist Ergebnis des Anchor members, der gelb markierte des rekursiven members.

Client Programmierung und SSAS Metadaten

Das CubeDef Objekt ist der naheliegendste Weg, um Metadaten per ADOMD.NET aus einer SSAS Datenbank zu lesen.
 
Nicht immer ist das die beste Lösung. Das CubeDef Objekt stresst den SSAS-Server gehörig.
Das macht sich bermerkbar, wenn eine sehr grosse Datenbank mit einer vielzahl von Measures abgefragt
werden soll.
 
Eine Alternative sind SchemaDatasets, welche es uns eine defensive Möglichkeit bieten Metadaten aus einer Datenbank zu ermitteln. SchemaDatasets enthalten die Metadaten in Tabellenform.
 
Die SchemaDatasets verschaffen uns auch einen hervorragenden Überblick
über die SSAS Architektur.
 
Welche Datenbanken sind auf dem Server?
 
Public Class Form1
    Sub SSASDatenbanken()
        Dim conn As New AdomdClient.AdomdConnection
        Dim ds As New DataSet
        conn.ConnectionString = "Data Source=NX7000JS\SQL2005"
        conn.Open()
        ds = conn.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, Nothing)
        For i As Integer = 0 To ds.Tables(0).Rows.Count – 1
            Console.WriteLine(ds.Tables(0).Rows(i)("CATALOG_NAME"))
        Next
        ds.Dispose()
        conn.Close() : conn.Dispose()
    End Sub
Welche SchemaRowsets werden vom Provider angeboten?
 
ds = conn.GetSchemaDataSet(AdomdSchemaGuid.SchemaRowsets, Nothing)
For i As Integer = 0 To ds.Tables(0).Rows.Count – 1
   Console.WriteLine(ds.Tables(0).Rows(i)(0))
Next
Hier eine Überladung von GetSchemaDataSet inkl. AdomdRestrictionCollection
Wir geben keine GUID an, sondern den Schema Namen als String.
Welche Hierarchien gibt es in Adventure Works DW/Customers?
 
 
Dim rest As New AdomdRestrictionCollection
rest.Add(New AdomdRestriction("CATALOG_NAME", "ADVENTURE WORKS DW"))
rest.Add(New AdomdRestriction("DIMENSION_UNIQUE_NAME", "[CUSTOMER]"))
ds = conn.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", rest)
For i As Integer = 0 To ds.Tables(0).Rows.Count – 1
    Console.WriteLine(ds.Tables(0).Rows(i)("HIERARCHY_CAPTION"))
Next

MDX Subcubes

Wir können nun auch Subcubes erstellen. Das erinnert natürlich gleich an Subselects aus T-SQL.
Leider ist ein Subcube nicht so eindeutig wie das Equivalent aus T-SQL.
Die Syntax ist einfach:

Select

[Internet Sales Amount] on 0,[Category].members on 1

from

(

Select

{[Category].[Bikes],[Category].[Clothing]} on 0

from

[adventure works])

 
Internet Sales Amount
All Products $28,657,917.26
Bikes $28,318,144.65
Clothing $339,772.61

Der Subcube gibt auch das All Member zurück, obwohl nur Bikes und Clothing mit angegeben wurde.

 
Ein SubCube enthält alle Ascendants und Descendants. Hier das MDX auf die gesamte Product Dimension
erweitert; im ersten MDX wurde nach Member des Level Category eingeschränkt.
 

Select [Internet Sales Amount] on 0,[Product].[Product Categories].Members on 1

from

(

Select

{[Category].[Bikes],[Category].[Clothing]} on 0

from

[adventure works])

Internet Sales Amount
All Products $28,657,917.26
Bikes $28,318,144.65
Mountain Bikes $9,952,759.56
Mountain-100 Silver, 38 $197,199.42
 
Ein Subcube wird immer über die Achsen defiiniert. Ein Slicer wird nur im Kontext des Subcubes ausgeführt,
um z.Bsp. Set-Funktionen (TopCount) zu steuern.
Man kann einen Subcube aber mit beliebig vielen Achsen erstellen.
Select [Internet Sales Amount] on 0,[Category].members on 1

from

(Select

{[Category].[Bikes],[Category].[Clothing]}

on 0

,[Internet Sales Amount]

on 1

,[Date].[Calendar Year].&[2004]

on 2

,[Customer Geography].[Country].&[Germany]

on 3

from

[adventure works])

 
Das obenstehende MDX ist dann auch die Empfehlung für die Verwendung von SubCubes.