Overblog
Folge diesem Blog Administration + Create my blog
Blog von Olaf Helper

olap

Was ist in einem MS Excel PowerPivot Workbook enthalten?

4. April 2012 , Geschrieben von Olaf Helper Veröffentlicht in #OLAP

Nein, ich bin nicht neugierig, ich möchte es halt nur manchmal genau wissen und die Frage ist: Was ist in einem MS Excel PowerPivot Workbook enthalten?
Hintergrund der Frage ist auch, was passiert, wenn ich ein solches Workbook mit Excel auf einem Arbeitsplatz öffne, wo kein PowerPivot installiert ist? Kann ich sie evtl. gar nicht erst öffnen oder gehen beim Speichern die PowerPivot Daten verloren?
Die Workbooks von Excel 2010 haben das "OpenXML for Documents" Format, also XML Dateien. Öffnet man eine solche Datei in einem Texteditor, sieht es aber nicht nach XML aus, man sieht nur kryptische Zeichen. Grund ist einfach der, das die eigentliche XML Datei(en) im ZIP Verfahren komprimiert sind, um Platz zu sparen; die XML Tags und Datenkodierung blähen die Datenmenge doch sehr stark auf.
Und damit liegt der Trick, wie man Einblick ins Workbook erhält, auch schon auf der Hand: Man benennt die Dateiendung von .XLSX und .ZIP um und schon behandelt Windows es wie eine ZIP Datei (zur Sicherheit bitte mit einer Kopie arbeiten!).
Den ersten Unterschied zu einer Excel Datei ohne PowerPivot findet man gleich im Root Verzeichnis, es gibt einen zusätzlichen Ordner "customerXml". Dieser enthält einige XML Dateien, vermutlich Metadaten etc., nicht besonders spannend.
Im Standard-Ordner "xl" findet man einen weiteren neuen Ordner "customData", dieser beinhaltet in meinem Fall eine Datei "item1.data" mit der Größe von 11,7 MB; da das ganze Workbook 11,9 MB groß ist, liegt die Vermutung nahe, dass das die komprimierten Daten von PowerPivot sind. Ich hatte die AdventureWorks2008R2DW Beispieldatenbank ins PowerPivot übernommen, die Datenbank hat insgesamt 45,5 MB Nettodaten (+ 26,9 MB Indizes). Also liegt hier die Komprimierungsrate bei ungefähr Faktor 4; nicht ganz so hoch wie erwartet. Anhand des "Verhältnis" der ZIP Datei sieht man aber, das ZIP es auch nicht weiter komprimieren konnte.
 
PowerPivotWKB_ItemData.jpg
 
Direkt im Ordner "xl" findet man noch die "connections.xml", das ist zunächst nichts Ungewöhnliches. Hat man in Excel z.B. eine Sql Server Tabelle eingebunden, ist diese Connections Datei auch vorhanden.
Aber der Inhalt ist interessant:
 <?xml version="1.0" encoding="UTF-8" standalone="true"?> 
 <connections xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> 
  <connection refreshedVersion="4" type="5"  
  description="Diese Verbindung wird von Excel für die Kommunikation zwischen  
  der Arbeitsmappe und eingebetteten PowerPivot-Daten verwendet  
  und sollte nicht manuell bearbeitet oder gelöscht werden."  
  name="PowerPivot Data" keepAlive="1" id="1"> 
  <dbPr commandType="1" command="Model"  
  connection="Provider=MSOLAP.5;Persist Security Info=True; 
  Initial Catalog=Microsoft_SQLServer_AnalysisServices; 
   Data Source=$Embedded$;MDX Compatibility=1; 
  Safety Options=2;ConnectTo=11.0; 
   MDX Missing Member Mode=Error;Optimize Response=3; 
  Cell Error Mode=TextValue"/> 
  <olapPr rowDrillCount="1000" sendLocale="1"/> 
  <extLst> 
  <ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"  
   uri="{D79990A0-CA42-45e3-83F4-45C500A0EAA5}"> 
  <x14:connection embeddedDataId="Microsoft_SQLServer_AnalysisServices"  
  </ext> 
  </extLst> 
  </connection> 
</connections>  
 
Hier lässt sich schon die Architektur erahnen: PivotTable kommuniziert mit PowerPivot via OleDB "MSOLAP", wobei als Data Source $Embedded$ angegeben ist.
Das wirft für mich die nächste Frage auf: Kann man PowerPivot auch in eigenen Applikationen verwenden und auf gleiche Weise damit kommunizieren? Das aber später mal.
Um die ursprüngliche Frage zu beantworten, man kann problemlos ein PowerPivot Workbook mit Excel ohne PowerPivot öffnen und auch wieder speichern, ohne das Daten verloren gehen.
Weiterlesen

PowerPivot V2 für Excel

12. März 2012 , Geschrieben von Olaf Helper Veröffentlicht in #OLAP

Mit dem Microsoft SQL Server 2012 RTM wurde auch die Version 2 von PowerPivot für Excel released und da es kostenfrei und bereits verfügbar ist, kann es jetzt schon produktiv verwendet werden.

Download: Microsoft® SQL Server® 2012 PowerPivot® für Microsoft® Excel® 2010

In dieser neuen Version wurden einige Features umgesetzt, die bisher doch schmerzlich vermisst wurden, als da wären
- Hierarchien
- KPI's
- Vordefinierbare Formate für die Datentypen
- Perspektiven
- Abweichende Sortierung von Dimensionen
- Diagramm Absicht der Tabellen

Damit fängt PowerPivot an, ein sehr interessantes Produkt zu werden, und auch deshalb will ich mich damit etwas intensiver beschäftigen.

Interessant ist auch die "UpScale" Möglichkeit. Während der Projektphase kann man sich zusammen mit der Fachabteilung vor Excel setzen und die Lösung modellieren; dort & so kann dann die Lösung zunächst auch laufen. Ist die Lösung ausgefeilt und wird häufiger genutzt, kann man das PowerPivot Workbook in Sharepoint 2010 PowerPivot Service hosten. Benötigt man noch mehr Power, kann man das Workbook in ein BISM Model übernehmen und die Lösung in SSAS in Tabular Mode bereitstellen.

Weiterlesen

SSAS Objekte per AMO verarbeiten und Verlauf per SessionTrace verfolgen

18. Februar 2011 , Geschrieben von Olaf Helper Veröffentlicht in #OLAP

Einen Microsoft Sql Server Analysis Services (SSAS) Cube und dessen Objekte lässt man im produktiven Betrieb natürlich zeitgesteuert per SSIS Package verarbeiten; da will man nicht wirklich bei „zusehen“.

Während der Entwicklungs- & Designphase eines Cube’s lässt man diesen aus BIDS heraus verarbeiten. Die Daten basieren dabei meist auf Views, die die eigentlichen Daten auf eine repräsentative als auch vertretbare Datenmenge beschränken; schließlich verarbeitet man öfter mal den Cube, um das Ergebnis zu kontrollieren und das soll ja nicht jedes Mal einen halben Arbeitstag in Anspruch nehmen. Der Processing Verlauf wird dabei in BIDS (und auch SSMS) in einem TreeView dargestellt und das bietet einem nicht gerade eine gute und informative Darstellung des Verlaufes.

Andererseits sind die Verlaufsinformationen (mir) wichtig, um von den Test- auf die Produktionsdaten hochrechnen zu können.

AMO bietet die Möglichkeit mit einem .NET Programm oder PowerShell programmatisch einzelne Objekte eines Cubes verarbeiten zu können. Über einen Trace könnte man alle Aktivitäten auf dem Analysis Server nach verfolgen und die der eigenen Session herausfiltern; nicht so ganz einfach. Es geht aber auch wesentlich einfacher, indem man das SessionTrace des AMO Server Objektes verwendet, über das man auch die Verarbeitung der Objekte durchführt. Man setzt eine Variable WithEvents, startet den Trace und kann dann alle Ereignisse Event-gesteuert empfangen. In TraceEventArgs erhält man alle Daten, so wie man sie auch aus dem SQL Profiler kennt.

Es gibt aber kleine Probleme mit ein paar Properties von TraceEventArgs, die könnten durch „Ungenauigkeit“ (alias Bug) in AMO verursacht werden und die gilt es zu umschiffen. Das betrifft zum Beispiel das Property IntegerData. Natürlich und verständlicherweise gibt es nicht bei jedem TraceEvent einen Wert für IntegerData, aber dann würde ich davon ausgehen, das entweder Nothing (C# null) oder -1 geliefert wird. Stattdessen erhält man den Laufzeitfehler

 

 System.ArgumentNullException: Der Wert darf nicht NULL sein. Parametername: String 
  bei System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) 
  bei System.Number.ParseInt64(String value, NumberStyles options, NumberFormatInfo numfmt) 
  bei Microsoft.AnalysisServices.TraceEventArgs.get_IntegerData() 

 

egal was man macht; eine einfach Prüfung auf IsNothing reicht bereits als Auslöser. Nun könnte man aufwendig auf alle EventClass / EventSubClass Kombinationen filtern, wo zugesichert ein Wert vorkommt. Ich mache es mir einfacher und deswegen kapsele ich die Abfrage des IntegerData, das bei manchen Events den Fortschritt bzw. die Anzahl verarbeiteter Datensätze enthält, in einer eigenen Funktion und fange den Fehler mit Try Catch ab. Betroffen sind von dem Phänomen die Properties CpuTime, Duration, IntegerData, ProgressTotal, Serverty.

 

Der Rest ist einfach, ich lasse die TraceEventArg-Daten in einer Console ausgeben, der VB.Net Code dazu sieht so aus:

 

 

Imports Microsoft.AnalysisServices
 
Module DimProcess
 
    Private WithEvents mSessionTrace As SessionTrace
 
    Sub Main()
        Dim server As New Server
        server.Connect("MeinServer\MeineInstanz")
 
        Dim database As Database = server.Databases("Adventure Works Cube")
        Dim dimension As Dimension = database.Dimensions("Dim Customer")
 
        mSessionTrace = server.SessionTrace
        mSessionTrace.Start()
 
        Console.WriteLine ("{0} Start of processing"Date.Now().ToString("T"))
        dimension.Process(ProcessType.ProcessFull)
 
        mSessionTrace.Stop
        mSessionTrace = Nothing
        Console.WriteLine ("{0} End of processing"Date.Now().ToString("T"))
        
        dimension.Dispose
        database.Dispose
        server.Disconnect
        server.Dispose
 
        Console.WriteLine ("Hit any key")
        Console.ReadKey
    End Sub
 
    Private Sub mSessionTrace_OnEvent(ByVal sender As ObjectByVal e As Microsoft.AnalysisServices.TraceEventArgs) _
        Handles mSessionTrace.OnEvent
        
        Select Case e.EventSubclass            
            ' Uninteressante EventSubClass:
            Case  TraceEventSubclass.Subscribe, TraceEventSubclass.SqlQuery, _
                  TraceEventSubclass.DiscoverXmlMetadata, TraceEventSubclass.ExecuteSql
            Case Else
                If Not String.IsNullOrEmpty(e.TextData) AndAlso not e.TextData.StartsWith("<"then
                    Console.WriteLine ("{0} {1} {2}"Date.Now().ToString("T"), GetIntegerdata(e), e.TextData)
                end if
        End Select
        
    End Sub
 
    Private Function GetIntegerdata(ByVal e As TraceEventArgsAs string
 
        Try
            Dim result As long = e.IntegerData
            Return result.ToString().PadLeft(6)
 
        Catch ex As Exception
            Return String.Empty.PadLeft(6)
        End Try
 
    End Function
 
End Module

 

Zugegeben, mit dem AdventureWork Demo gibt es nicht groß Daten zu verarbeiten, deswegen ist die Ausgabe auch nicht gerade spektakulär:

 

ProcessCubeObject.jpg

Weiterlesen

Power Pivot

24. Oktober 2009 , Geschrieben von Olaf Helper Veröffentlicht in #OLAP

Das Kind "Project Gemini" hat nun einen finallen Namen und eine eigene Website bekommen: Power Pivot.
Weiterlesen

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

11. September 2009 , Geschrieben von Olaf Helper Veröffentlicht in #OLAP

Eine, wie ich finde, wirklich gute Dokumentation zum Thema Design-Tipps von Cubes gibt es im zum Dowload als Word Doukument im Download-Center von Microsoft.
In der Best Practice Tipps wird aufgeführt, mit welchen Ordnungselementen und anderen Eigenschaften man im SQL Server Analysis Services (SSAS) eine OLAP Cube mit deren Dimension und Attribute so gestaltet kann, das dem Endanwender ein einfaches und übersichtliches Arbeiten ermöglicht wird.

Weiterlesen

Analysis Services Cube Statusdaten per PowerShell abfragen

26. Juni 2009 , Geschrieben von Olaf Helper Veröffentlicht in #OLAP

Will man die Detailinformationen zu seinen Cubes und Dimensionen erhalten, muss man diese entweder manuell einzeln über das Sql Server Management Studio (SSMS) abrufen oder muss sie über XML/A abfragen, wieder einzeln.

Ein anderer Weg ist, sich die ganzen Informationen über ein einfaches PowerShell-Script auflisten zu lassen; das könnte dann z.B. auch automatisch über SSIS nach erfolgter Verarbeitung zur Kontrolle erfolgen.

 

 ## PS Script 
 ## Ausgabe der Statusdaten (Update und Verarbeitung) von Cubes 
 ## und Dimensionen
 ## AdoMd namespace hinzufügen $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")  
 ## Mit AS verbinden 
 $cat = New-Object MiCrosoft.AnalysisServices.AdomdClient.AdomdConnection 
 $cat.ConnectionString = "Data Source=localhost;Catalog=SmallCube" 
 $cat.Open()
 ## Eckdaten des Servers + DB ausgeben
Write-Output ("`nVersion: {0} State: {1} DB: {2}`n" -f
  $cat.ServerVersion, $cat.State.ToString(), $cat.Database 
  )  
 ## Für jeden Cube / Dimension die Statusdaten ausgeben 
 foreach ($cube in $cat.Cubes) 
 { 
  Write-Output ("Cube: {0} ({1})`nUpd: {2}`tPrc: {3}`n" -f 
   $cube.Name, $cube.Type.ToString(), $cube.LastUpdated, $cube.LastProcessed 
  ) 
 }  
 ## Und sauber wieder schließen 
 
$cat.Dispose()
$cat.Close()
Weiterlesen