Tools für DAX und Tabular BISM Entwicklung

Wer öfter in der DAX und Tabular BISM-Welt unterwegs ist, für den könnte folgender Beitrag auf www.sqlbi.com interessant sein: http://sql.bi/daxtools

Aktuell ist es leider noch so, dass die Unterstützung für DAX und BISM Entwicklung im Visual Studio recht überschaubar ist. Daher können diese Tools durchaus sinnvoll sein.

SSIS Unit Testing mit SSIS 2012/2014

Um Unittests für SSIS-Pakete zu implementieren, gibt es aktuell nicht viele sinnvolle Möglichkeiten. Leider bietet Microsoft keine Standardmöglichkeiten im VisualStudio bzw. SSIS. Man ist auf 3rd Party Anbieter angewiesen die Tools dafür bereitstellen. Auf einige dieser Tools möchte ich hier eingehen.

 

 

Historie/manuelle Test

Bevor wir auf die verschiedenen Tools kommen, ein kurzer Rückblick wo wir bezüglich SSIS Unittesting herkommen. Da es, wie gesagt, keine out-of-the-box -Möglichkeiten gibt, behelfen sich viele Projekte damit ganz simple “Unittests” mit den SSIS Bordmitteln zu erstellen. Unittests kann man diese nicht wirklich nennen, da immer ganze Pakete ausgeführt werden. Je nachdem wie groß so ein Paket ist, wird das schnell unübersichtlich. Dies ist sowohl von der Implementierung als auch von der Handhabung umfangreich und kompliziert. Als Beispiel wird ein Testpaket definiert, das das Setup, die Ausführung des eigentlichen Pakets, die Assertion und das Teardown übernimmt. Dabei treten diverse Probleme auf:

  • Es wird immer das ganze zu testende Paket ausgeführt, man kann nicht einzelne Teile (Units) ausführen, was für richtige Unittests  eigentlich unbrauchbar ist
  • Man muss dafür sorgen, dass die Parameter, Variablen und Connections für den Unittest korrekt gesetzt werden. Oft behilft man sich damit, dass die Parameter und Connection fest für die Unittest-Umgebung eingetragen sind.
  • Die Nachvollziehbarkeit bei Fehlern im Test ist mühsam, da man im Fehlerfall nicht weiß ob es am Test oder am zu testenden Paket gelegen hat.
  • usw.

Dabei sei gesagt, dass diese manuellen Tests besser sind als gar keine Test. Komfortabel und effizient ist aber etwas anderes.

Custom Unittest Frameworks

Als Alternative zu den manuellen Tests, entwickeln einige Projekte/Kunden eigene Unittest Frameworks, die dann oft auch schon ansehnliche Ergebnisse liefern. Ein komplettes Unittest-Framework zu entwickeln, was projektübergreifend verwendet werden kann, kostet aber viel Zeit und Aufwand. Meist decken diese auch nicht alle Anforderungen an Unittests ab.

3rd Party Tools

Es gibt einige 3rd party Tool die sich dem Thema SSIS Unittesting angenommen haben und daraus sind einige brauchbare Ansätze entstanden.

SSISTester
http://www.bytesoftwo.com/

Eines der genannten Tools ist der SSISTester. Es ist ein Framework von bytesoftwo was sowohl Unittest als auch Integrationstest für SSIS-Pakete ermöglicht. SSISTester integriert sich hervorragend in Visual Studio 2008/2010/2012/2013 und kann für SSIS Projekt in SQL 2008, SQL 2012 und SQL 2014 eingesetzt werden.
Unittest können auf Task Ebene im Control-Fluss definiert werden. Die Prüfung von Ergebnissen kann jedoch zwischen jeder Komponente im Data-Flow-Taks gesetzt werden. Somit hat man eine extrem granulare Möglichkeit Pakete zu testen. 
Es ist ebenfalls möglich Datenquellen zu mocken, um die Logik ohne der Abhängigkeit zu Datenbanken/Files usw. zu testen. Der Test von Constraints im Control-Flow ist ebenfalls möglich. Die zu testenden Pakete müssen für die Ausführung der Unittest nicht verändert oder angepasst werden und enthalten demzufolge keinen Testcode oder sonstige Modifikationen für die Unittests.

Folgendes Snippet zeigt den Beispielhaften Aufbau eines einfachen Unittest mit dem SSISTester:

   1: using System.Collections.ObjectModel;

   2: using Microsoft.VisualStudio.TestTools.UnitTesting;

   3: using SSIS.Test;

   4: using SSIS.Test.Dts;

   5: using SSIS.Test.Metadata;

   6: using System.IO;

   7:  

   8: namespace SDX.PB.BI.ETL.Unittest.SSISTester

   9: {

  10:     [TestClass]

  11:     [UnitTest("SDX.PB.BI.ETL", "DimUser.dtsx", ExecutableName = @"[DimUser][SEQC_Info][DFT_Info_User_Load]")]

  12:     [DataTap(@"[DimUser][SEQC_Info][DFT_Info_User_Load][CSPL_UserFound]", @"[DimUser][SEQC_Info][DFT_Info_User_Load][OLE_DST_InfoUser_Load]")]

  13:     class DimUserLoadDataTaps : BaseUnitTest

  14:     {

  15:         protected override void Setup(SetupContext context)

  16:         {

  17:             context.Package.GetConnection("PrivatbilanzBI").SetConnectionString(Constants.PrivatbilanzBIDbConnectionString);

  18:             context.Package.GetConnection("PrivatbilanzDev").SetConnectionString(Constants.PrivatbilanzDevDbConnectionString);

  19:             context.Package.GetConnection("PrivatbilanzBI").SetConnectionString(Constants.SsisDbConnectionString);

  20:  

  21:             // truncate destination table

  22:             context.DataAccess.OpenConnection(Constants.PrivatbilanzBIDbConnectionString);

  23:             context.DataAccess.ExecuteNonQuery("truncate table [info].[User]");

  24:  

  25:             string query = @"  INSERT INTO [info].[User] (UserID, UserName, LaufendeBilanzID, Vorname, Nachname, Geburtstag, Geschlecht, Einkommen, Verheiratet, Kinder) 

  26:                                     VALUES (-1, 'TestUser', -1, 'Test', 'User', '1999-01-01', 'm' ,100000, 1, 2)";

  27:  

  28:             context.DataAccess.ExecuteNonQuery(query);

  29:  

  30:  

  31:             // truncate source table dbo.UpdatedUser 

  32:             context.DataAccess.ExecuteNonQuery("truncate table [info].[UpdatedUsers]");

  33:  

  34:             // truncate source table dbo.User and fill with test data

  35:             context.DataAccess.ExecuteNonQuery("truncate table [stage].[User]");

  36:  

  37:             query = @"  INSERT INTO [stage].[User] (ID, UserName, LaufendeBilanzID, Vorname, Nachname, Geburtstag, Geschlecht, Einkommen, Verheiratet, Kinder) 

  38:                                     VALUES (2, 'NeuerUser', -1, 'Test', 'NeuerName', '1999-01-01', 'm' ,100000, 1, 2)";

  39:  

  40:             context.DataAccess.ExecuteNonQuery(query);

  41:             context.DataAccess.CloseConnection();

  42:         }

  43:  

  44:         protected override void Verify(VerificationContext context)

  45:         {

  46:             Assert.AreEqual(true, context.Package.IsExecutionSuccess);

  47:             Assert.AreEqual(true, context.ActiveExecutable.IsExecutionSuccess);

  48:  

  49:             context.DataAccess.OpenConnection(Constants.PrivatbilanzBIDbConnectionString);

  50:             var rowCount = (int)context.DataAccess.ExecuteScalar(@"select count(*) from [info].[User]");

  51:             context.DataAccess.CloseConnection();

  52:  

  53:             Assert.AreEqual(2, rowCount);

  54:  

  55:             ReadOnlyCollection<DataTap> dataTaps = context.DataTaps;

  56:  

  57:             DataTap dataTap = dataTaps[0];

  58:             foreach (DataTapSnapshot snapshot in dataTap.Snapshots)

  59:             {

  60:                 string data = snapshot.LoadData();

  61:  

  62:                 Assert.IsFalse(string.IsNullOrEmpty(data));

  63:             }

  64:         }

  65:  

  66:         protected override void Teardown(TeardownContext context)

  67:         {

  68:             context.DataAccess.OpenConnection(Constants.PrivatbilanzBIDbConnectionString);

  69:             context.DataAccess.ExecuteNonQuery("truncate table [info].[User]");

  70:             context.DataAccess.CloseConnection();

  71:         }

  72:     }

  73: }

Die Tests können mit einer eigenen Test GUI, mit dem VS Testexplorer oder auch mit dem Resharper ausgeführt und visualisiert werden.

Visualisierung der Testergebnisse mit VS Testexplorer:

image

Visualisierung der Testergebnisse mit der TEST GUI:

image

SSISUnit/Pragmatic Workbench BIxPress (SSISUnit)

https://ssisunit.codeplex.com/

http://pragmaticworks.com/Products/BI-xPress/Features/SSISUnitTesting.aspx

SSISUnit von Pragmatic Work verfolgt einen etwas anderen Ansatz. Hierbei werden die Unittests über eine GUI erstellt um möglichst wenig Code schreiben zu müssen. Die Definition der Unittest wird als XML abgelegt.

Es gibt zum einen die kostenlose Codeplex Variante und dann die Luxus-Variante als Teil von BIxPress oder der Pragmatic Workbench. Beide Varianten setzen auf die SSISUnit-Engine auf. Die Workbench kann über Unittest hinaus noch sehr viel mehr. Darauf gehe ich hier aber nicht weiter ein.

Folgendes Beispiel zeigt die Konfiguration und das Ergebnis eines Unittest in der SSISUnit-GUI.

imageimage

Das Vorgehen in der Pragmatic Workbench ist ähnlich der Codeplex Variante.

image

image

Fazit: Die genannten Tools bieten tolle Möglichkeiten echte “Unittest” zu implementieren, wobei der SSISTester noch einen Schritt weiter geht und Assertions zwischen allen Komponenten im Dataflow-Task ermöglicht. Je nachdem welche BI-Entwickler in einem Projekt eingesetzt werden, kann eine dieser Varianten eingesetzt werden. Besonders die Integrationsmöglichkeiten in Builds, Autotest oder Check-In Vorgänge bringen viel Qualitäspotential für eine BI-Solution mit.

SSAS: regelmäßiges ProcessFull in Cubes

Generell ist es sinnvoll einen SSAS Cube inkrementell (ProcessAdd, ProcessUpdate) zu verarbeiten, da dies Zeit spart. Bei den Dimensionen gibt es aber eine Besonderheit, die man beachten sollte. Bei allen inkrementellen Verarbeitungsmethoden (Insert, Delete, Update) bleiben alle bestehenden Dimensionseinträge bestehen und werden nur als gelöscht bzw. inaktiv markiert.

Dies kann dazu führen, dass die Cube-Dateien auf einem SSAS-Server sehr groß werden. Erst ein ProcessFull auf den Dimensionen sorgt dafür, dass nur noch die aktuellen Daten gespeichert bleiben.

Folgender Blogeintrag beschreibt das Problem im Detail:  http://byobi.com/blog/2013/07/why-you-need-to-routinely-processfull/

Power BI App für iPad und iPhone kommt “bald”

Nachdem bereits im November 2012 die Power BI App für diverse Plattformen (Windows, Android, iOS) angekündigt wurde (Microsoft’s Mobile BI Solution für iOS, Android und Windows), und die App auch im August 2013 im Windows Store bereitgestellt wurde (Power BI App für Windows 8/RT im Windows Store verfügbar), kommt demnächst auch die iOS App.

Interessenten können sich für eine Preview unter folgendem Link anmelden: http://microsoft-news.com/microsoft-is-going-to-release-power-bi-ios-app-soon/

 
Power BI ist eine touch optimierte Business Intelligence App. In folgenden Beiträgen wird Power BI detaillierter beschrieben:
 
 

SQL Server Reporting Services – Mehrere Datasets in einer Tabelle nutzen

Nutzt man SQL Server Reporting Services für Dashboards, hat man oft die Anforderung mehrere Datasets in einer Steuerelement mit Datenbereich (Liste, Tabelle, Matrix, Diagramm, …) anzuzeigen. In Reporting Services wird aber per Default nur ein Dataset pro Datenbereich unterstützt. Es gibt trotzdem jedoch mehrere Möglichkeiten um Daten aus unterschiedlichen Datasets in einer Tabelle zusammenzufassen

Befinden sich die Datensets in einer Datenquelle, wie z. B. SQL Server, so kann man diese mit SQL Server-Bordmitteln (Unterabfragen, Joins, Stored Procedure) in ein Dataset zusammenführen. Sind die Datenquellen über verschiedene Systeme verteilt, kann man die Daten mit einer Linked-Server-Verknüpfung dennoch miteinander verknüpfen.Doch auch Reporting Services stellen einige Mittel zur Verknüpfung der Daten zur Verfügung.

Subreports

Eine Möglichkeit ist der Einsatz von Subreports bei der Verwendung von Liste, Tabelle oder Matrix. Dafür fügt man in einer Zelle des Steuerelements einen Subreport ein. In diesem Subreport kann man wiederrum alle möglichen Inhalte darstellen z.B. Tabellen, Textboxen usw. Diesen Tabellen kann man dann wiederrum die anderen Datasets als DataSource zuweisen. Der Nachteil dabei ist, dass man sich dann um Parameterübergabe usw. beim Aufruf des Subreports kümmern muss. Um nur bestimmte Informationen aus einem zweiten Dataset dazu zu mischen, viel zu viel Aufwand.

Lookup() 

Möchte man nur einen Wert aus einem anderen Dataset als dem in der Tabelle festgelegten anzeigen, kann man die Lookup Funktion http://msdn.microsoft.com/de-de/library/ee210531.aspx verwenden um Daten aus einem anderen Dataset einzubinden.

In meinem Beispiel werden in folgender Tabelle zwei Datasets benötigt, da das erste Dataset die Stammdaten und Audit-Informationen aus der Datenbank (dsDefault) holt und das zweite Dataset auf einen Analysis Services Cube (dsData) zugreift um die Kennzahlen zu selektieren.

image

Um die beiden Datasets zu verknüpfen wird das erste Dataset ganz normal in der Tabelle als Datasource hinterlegt. Bei den Zellen, die die Kennzahlen beinhalten ist dann folgende Formel hinterlegt:

   1: =Lookup

   2: (

   3:     Fields!CityCode.Value

   4:     ,Fields!City.Value

   5:     ,Fields!Count.Value

   6:     ,"dsData"

   7: )

Hinweis: Bekommt man folgenden Fehler beim Rendern des Reports liegt es daran, dass die beiden Felder nicht den selben Datentypen haben und nicht verglichen werden können.

Warning 44 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox.Paragraphs[0].TextRuns[0]’ contains an error: Exception of type  'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException_ComparisonError' was thrown.

 

Hier hilft eine Konvertierung der beiden Felder in den selben Datentyp und schon funktioniert es.

   1: =Lookup

   2: (

   3:     CStr(Fields!CityCode.Value)

   4:     ,CStr(Fields!City.Value)

   5:     ,Fields!FactCount.Value

   6:     ,"dsData”

   7: ")

Andere Möglichkeiten sind:

· die Verwendung von Custom Code in einen Bericht (beispiel siehe http://sqlserverbiblog.wordpress.com/2011/09/29/using-custom-code-to-synchronize-different-datasets)

· Data Processing Extension (Beispiel siehe: http://sqlmag.com/database-development/creating-custom-data-processing-extension)

SQL Server Data Tools (SSDT) – BI wieder für Visual Studio 2013 verfügbar

Nachdem die SQL Server Data Tools für Business Intelligence Projekte (SSIS, SSRS, SSAS) eine Zeit lang bereits für Visual Studio 2013 verfügbar waren und danach wieder zurückgezogen wurden, sind sie jetzt (hoffentlich dauerhaft) wieder verfügbar. Den Download von Microsoft gibt es hier: 

http://www.microsoft.com/en-us/download/details.aspx?id=42313

Die Entkopplung der Entwicklung und Installation der BI-Tools von der SQL Server-Installation ist sehr erfreulich. Führt dies doch dazu, dass wir zeitnah BI-Tools für aktuelle Visual Studio Versionen bekommen.

Power BI App für Windows 8/RT im Windows Store verfügbar

Wie bereits angekündigt hat Microsoft jetzt eine Power BI App für Windows 8/RT veröffentlicht. Diese kann Reports und Analysen, die in Office365 zur Verfügung gestellt werden, auf mobilen Devices anzeigen. Anzeigen ist hierbei ein wenig untertrieben. Aber schaut es euch selbst an. Die App mit Beispielreports ist im Windows Store zum Download bereitgestellt. Die ebenfalls angekündigte iOS App lässt bisher noch auf sich warten.

 

Im folgenden ein kurzer Überblick und ein paar Screenshots der Power BI Features

Navigation und Bedienung

Nach dem Starten der App bekommt man direkt die angepinnten Report Favoriten angezeigt. Angezeigt werden die Reports und jeweils bis zu 6 Seiten Vorschau der Reports. Diese können bei Bedarf einzeln oder alle gemeinsam neu synchronisiert werden.

image_thumb[40]

In der Detailsicht, die einer Seite eines Reports entspricht, kann dann die Interaktivität der dahinter liegenden Reports genutzt werden. Funktionen wie Filtern, Sortieren und das Blättern auf die nächsten Seite werden unterstützt.

image_thumb[41]

Die Filter der Seite (View) oder eines Reportteils (Chart, Table) können zusätzlich eingeblendet und bearbeitet werden.

image_thumb[42]

Die Integration in Windows 8 funktioniert wie gewohnt. Die Windows 8 Gesten für das Kontextmenü und die Charm Bar sind wie bekannt möglich. Über das Kontextmenü kann z.B. auf die Datenquellen navigiert werden und mehrere Reports gleichzeitig synchronisiert werden.

image_thumb[43]

Über die Charm Bar kann man z.B. Einstellungen verändern und Reports teilen.

image_thumb[44]

Datenquellen und Favoriten

Als Datenquellen können derzeit scheinbar nur Reports aus Office365 bzw. SharePoint Online eingebunden werden. Welchen Einfluss diese Einschränkung auf die Verbreitung der Power BI App haben wird, werden wir sehen. Ich persönlich finde es sehr schade, dass aktuell kein On Premise SharePoint unterstützt wird.

image_thumb[45]

image_thumb[46]

Von der App sind momentan Power View, Excel Services und Reporting Services Reports/Analysen auswertbar, die in Office365 abgelegt sind. Die Reports können entweder zur Anzeige ausgewählt werden, oder als Favoriten auf den App Startscreen gepinnt werden.

iOS Verfügbarkeit

Angekündigt wurde ebenfalls eine iOS iPad App. Dazu gibt es aber aktuell aber noch keine Neuigkeiten.

Fazit

Als Version 1.0 einer App ist das schon ganz nett. In einem Szenario wo Office365/SharePoint Online eingesetzt werden kann, ist die App sicher eine sinnvolle Erweiterung. Bestehende Reports/Analysen können so auf einem Windows Tablet bzw. iPad abgerufen und analysiert werden.

 

Quellen:

http://microsoft-news.com/microsoft-power-bi-app-available-for-windows-8rt-devices/

HTML5 Mobile Power BI

Microsoft’s Mobile BI Solution für iOS, Android und Windows

SQL Server Data Tools – Business Intelligence Projekte im Visual Studio 2012

Microsoft hat ein Add-On für das Visual Studio 2012 (eigentlich ein Shared Feature für SQL Server 2012) veröffentlich mit dem “endlich” Business Intelligence Projekte im Visual Studio 2012 erstellt und entwickelt werden können.

Bisher war mit mit BI-Projekten immer gezwungen die ältere Version des Visual Studio zu nutzen. Hatte man Projektbestandteile die die aktuellste Version von Visual Studio erforderten, musste man mit 2 Studios rumhantieren. Das ist nun vorbei. Das Add-On ist im Web verfügbar: http://www.microsoft.com/download/details.aspx?id=36843

Hier ein Auszug aus dem Business Intelligence Blog:

Today we are releasing SQL Server Data Tools – Business Intelligence for Visual Studio 2012 (SSDT BI) templates. With SSDT BI for Visual Studio 2012 you can develop and deploy SQL Server Business intelligence projects. Projects created in Visual Studio 2010 can be opened in Visual Studio 2012 and the other way around without upgrading or downgrading – it just works.

The setup includes:

  • Visual Studio 2012 integrated shell (if you don’t have Visual Studio 2012 on your machine)
  • Project templates for
    • Analysis services projects
    • Reporting services projects
    • Integration services projects

Und ein paar Screenshots, ebenfalls aus dem Business Intelligence Blog, als Appetitmacher. Viel Spaß damit!

PowerPivot für Sharepoint 2013 mit minimalen benötigten Berechtigungen

Welche Benutzer mit welchen Berechtigungen benötige ich um eine produktive Umgebung vor unbefugten Zugriff zu schützen?!
Für viele Entwickler ist dieses Thema ein Buch mit sieben Siegeln. Im Analysis Services  und PowerPivot Team Blog ist ein Beitrag – Configuring a Min-priv environment for PowerPivot for SharePoint 2013 – veröffentlich worden, der genau dafür Hilfestellung bietet. Konkret geht es in dem Beitrag darum PowerPivot für Sharepoint 2013 mit den minimalen benötigen Berechtigungen auszustatten die dafür notwendig sind.
Im Vergleich zu PowerPivot für Sharepoint 2010 ist für die Verarbeitung eines PowerPivot Modells kein Domain-Account mehr notwendig. Dies geht mit Sharepoint 2013 jetzt auch mit dem Network Service account.
Hier die Zusammenfassung der Berechtigungseinstellungen aus dem Blogbeitrag:

Account
  type
Privilege
Example
SharePoint Administrator account Local administrator
Backend Database roles:

  • securityadmin
  • dbcreator
MyDomainAdmin
Farm account Backend Database roles:

  • securityadmin
  • dbcreator
MyDomainFarmAct
Service Account for PowerPivot Administrator at the Analysis Services PowerPivot instance MyDomainSvcAct

Quelle: http://blogs.msdn.com/b/analysisservices/archive/2012/11/05/configuring-a-min-priv-environment-for-powerpivot-for-sharepoint-2013.aspx