SQL Server 2017 ist bereits seit knapp einem Jahr verfügbar (Erscheinungsdatum: 2.10.2017), in diesem Blogbeitrag werden die weitreichenden Neuerungen im Tabular Model gegenüber SQL Server 2016 vorgestellt und bisher vorliegende Erfahrungswerte geteilt.

Tabular Model ist ab SQL Server 2017 die neue Standardinstallation für SQL Server Analysis Services (bisher: SSAS Multidimensional). Die Komponenten Multidimensional und Power Pivot for SharePoint werden im Produktlebenszyklus (seitens Microsoft) als reif betrachtet und daher nicht mehr weiterentwickelt. Wir empfehlen daher, alle neuen SSAS Anwendungen auf Basis der Tabular Model Technologie umzusetzen.

--- Update vom 08.02.2019 ---

• Unsere bisherigen Erfahrungen mit dem Query Editor in SSDT sind katastrophal. Das Tool ist auch 1,5 Jahre nach dem Erstrelease voll mit Bugs. Wir empfehlen daher, die Queries in Power BI Desktop aufzubauen und dann den fertigen M-Code über den Advanced Editor einzukopieren (mit gfg. Anpassungen bei den Datasources). Das funktioniert nach unserer Erfahrung dann ganz gut.
• Leider gibt es auch beim Zusammenspiel der Queries mit den Output Tables im Datamodel zahlreiche Probleme. Beispielsweise müssen bereits bestehende Tables gelöscht werden, sobald sich die Feldstruktur (durch Änderung der zugrundeliegenden Query) ändert.
• Als Ergänzung zum SSDT Client sollte der Tabular Editor von sqlbi.com evaluiert werden, da damit sehr rasch und effizient die Metadaten des Datamodels (Measures, Display Folder, Translations, usw.) einer bereits bestehenden Anwendung aktualisiert werden können. Leider beinhaltet das Tool bisher noch keinen Editor für die Queries ...

Grundsätzliches

Tabular Model in SQL Server 2017 ist als Schwesterprodukt von Power BI und Excel zu sehen und alle 3 Produkte zusammen bilden den sogenannten Microsoft BI Stack:

Die Data Model Technologie ist ebenso wie die Get Data Technologie in allen 3 Produkten eingebaut, sie sind im Kern ident und unterscheiden sich lediglich durch die Ausrichtung und durch den Entwicklungsstand. Während Power BI das Tool für mobiles Dashboarding und Excel das Tool für (adhoc) Analyse ist, nimmt das Tabular Model im Microsoft BI Stack die Funktion der Enterprise BI ein.

Mit Tabular Model können also große Anwendungen realisiert werden, die die aktuellen Speichergrenzen von Power BI und Excel (oft bei weitem) übersteigen. Als Frontend für das Reporting werden am häufigsten Power BI, Excel und SQL Server Reporting Services verwendet. Tabular Model steht nicht nur als Serverprodukt in SQL Server 2017 sondern auch als Cloud Service in der Microsoft Azure Cloud zur Verfügung.

Compatibility Level 1400

Anders als in Power BI oder Excel kann im Tabular Model in jeder Anwendung der sogenannte Kompatibilitätsgrad festgelegt werden. Wir empfehlen für neue Anwendungen den aktuellen Kompatibilitätsgrad 1400 zu verwenden, dieser entspricht dem Funktionsumfang von SQL Server 2017 und die im folgenden vorgestellten Neuerungen beziehen sich auch allesamt auf den Compatibility Level 1400.

Die beiden Clienttools SQL Server Management Studio (SSMS) und SQL Server Data Tools (SSDT) werden seit geraumer Zeit als eigenständige Installationen bereitgestellt und unterstützen jeweils ab Version 17.0 die Funktionalität von SQL Server 2017. Wir empfehlen, das jeweils aktuellste Monatsrelease dieser Tools zu verwenden.

Neue Funktionen im Tabular Model

1. Query-Komponente ("Modern Get Data Experience")

Die wichtigste Neuerung im SQL Server 2017 ist die Integration der sogenannten Query-Komponente, die bereits aus Power BI ("Get Data") und Excel ("Power Query") gut bekannt ist. Damit wird eine große Lücke bei der Realisierung von Self-Service BI Anwendungen geschlossen, da der Bedarf nach einem vorgelagerten relationalem Datawarehouse (mit perfekt aufbereiteten Daten) mit dieser neuen Komponente deutlich reduziert wird und damit BI Anwendungen viel schneller und mit weniger Aufwand realisiert werden können.

Unterschiede zur Funktionalität in (den Clienttools) Power BI und Excel ergeben sich in folgenden Bereichen:

• in der Handhabung der Verbindungsinformationen (Verspeicherung der Passwörter)
• in der klaren Trennung von Datasource und Queries
• in der Verwendung eines sogenannten Dienstkontos ("Service Account"), das bei der Ausführung des Processing verwendet werden sollte:
  
• in der Möglichkeit zur Aufgliederung der Zieltabelle in Partitionen:
  
  Quelle: blog.crossjoin.co.uk/2017/10/08/creating-a-partitioned-table-in-ssas-tabular-2017-and-ssdt-using-m-functions

Wichtige Grundeinstellungen für die Query-Komponente werden in den Eigenschaften der sogenannten Model.bim getroffen:

Anders als in Excel und Power BI Desktop können derzeit bei Verwendung des SQL Server Konnektors im Verbindungsdialog leider nicht individuelle SQL Statements erfasst werden, dies ist derzeit nur im Advanced Query Editor möglich (Blogbeitrag von Chriss Webb hier).

Ähnlich wie in Excel können die "alten Konnektoren" als sogenannte Legacy-Datenquellen über die Projekteinstellungen aktiviert werden und so auch im Compatibility Level 1400 bei Bedarf eingesetzt werden:

Tabular Model Anwendungen, die aus früheren SQL Server Versionen auf den Compatibility Level 1400 migriert werden, verwenden nach der Migration diese Legacy-Datenquellen. Bestehende Anwendungen werden also nicht automatisch auf die neuen Queries umgestellt.

Weiterführende Informationen:

blogs.msdn.microsoft.com/analysisservices/2017/08/28/supporting-advanced-data-access-scenarios-in-tabular-1400-models

blogs.msdn.microsoft.com/analysisservices/2017/04/19/introducing-directquery-support-for-tabular-1400

blogs.msdn.microsoft.com/analysisservices/2017/04/19/new-get-data-capabilities-in-the-ga-release-of-ssdt-tabular-17-0-april-2017

2. Balancierung unausgeglichener Hierarchien ("Ragged Hierarchies")

Unausgeglichene Hierarchien sind "nicht an allen Stellen gleich tief" und kommen in der Praxis häufiger vor als ausgeglichene Hierarchien. Typischerweise ist eine Zeithierarchie eine ausgeglichene und eine Profit & Loss Zeilenstruktur eine unausgeglichene Hierarchie. Letztere wurden bisher standardmäßig "unbalanciert" (also mit allen Blank-Ebenen) dargestellt und waren in dieser Form nicht herzeigbar (die Unterdrückung der Leerzeilen konnte mit speziellen DAX-Statements auf Measure-Ebene erreicht werden):

In SQL Server 2017 steht in den Hierarchie-Eigenschaften das neue Setting Elemente ausblenden zur Verfügung, damit können die Blank-Ebenen einer Hierarchie automatisch unterdrückt werden:

Hier das Ergebnis der "balancierten" Hierarchie als Excel Pivot Report:

Die zugrundeliegende Dimensionstabelle muß folgendermaßen vorbereitet werden:

• Als unterste Ebene (hier: Level 7) kann nicht einfach die Spalte Metric Name verwendet werden, sondern der Metric Name muß auf die verschiedenen Hierarchiestufen als jeweils letzte Ebene aufgetragen werden (= orange Zellen)
• Die zu unterdrückenden Ebenen müssen "blank" sein, also vollständig leer sein

3. Drillthrough für Measures ("Detailrows")

In SQL Server 2017 kann pro Measure ein sogenanntes Detailrows-Statement definiert werden und damit der Drillthrough auf die Einzeldatensätze (idR der Faktentabelle) exakt definiert werden:

Der Drillthrough wird als DAX-Statement definiert, es kommen dabei Funktionen der  Gruppe "DAX Queries" zur Anwendung, allen voran die SELECTEDCOLUMNS-Funktion:

• SELECTCOLUMNS(
  'Fact InternetSales',
  "Customer Name",RELATED('Dim Customer'[FirstName]) & " " & RELATED('Dim Customer'[LastName]),
  "Order Date",'Fact InternetSales'[OrderDate],
  "Sales",[Sales]
  )

Hier das Ergebnis des Drillthrough in Excel:

Neu eingeführt wurde für diesen Kontext die DAX-Funktion DETAILROWS, die es ermöglicht, auf eine bestehende Drillthrough-Definition eines anderen Measures zu referenzieren und diese lediglich - bspw. mit einem Year-to-Date Zeitfilter - abzuwandeln:

• CALCULATETABLE(
  DETAILROWS([Sales]),
  DATESYTD('Dim OrderDate'[DateKey])
  )

Hier wiederum das Ergebnis in Excel, der Tabellenaufbau ist gleich wie im ersten Beispiel, lediglich ist die Tabelle länger, da nicht nur das einzelne Monat April sondern der YTD-Zeitraum Januar bis April angezeigt wird:

Der Drillthrough für das Measure Margin ist analog zum Drillthrough des Measures Sales aufgebaut, lediglich werden mehr Measures angezeigt:

• SELECTCOLUMNS(
  'Fact InternetSales',
  "Customer Name",RELATED('Dim Customer'[FirstName]) & " " & RELATED('Dim Customer'[LastName]),
  "Order Date",'Fact InternetSales'[OrderDate],
  "Sales",[Sales],
  "Cost",'Fact InternetSales'[Cost],
  "Margin",'Fact InternetSales'[Margin]
  )

Beobachteter Bug: in der deutschen Version der SSDT müssen die Detailrows-Statements mit Komma als Trennzeichen geschrieben werden, DAX-Statements in den Measures verwenden hingegen das Semikolon. Bei Verwendung des Kommas funktionieren dann leider die Intellisense-Funktionen im DAX-Editor nicht mehr, was das Schreiben der DAX-Statements erheblich erschwert ...

Wichtiger Hinweis: derzeit gibt es die Drillthrough-Aktion in den Excel Pivottables, nicht aber in Power BI. Daher können die Detailrows-Definitionen auf den Measures vorerst nur bei Verwendung von Excel Pivottables als Frontend genutzt werden.

Weiterführende Informationen:

www.sqlbi.com/articles/controlling-drillthrough-using-detail-rows-expressions-in-dax (Hinweis: der Lösungsansatz mit dem NATURALINNERJOIN hat bei unseren Tests keine sinnvollen Ergebnisse geliefert)

insightsquest.com/2018/01/14/query-detail-rows-in-power-bi (interessanter Zugriff auf die Detailrows-Expression mittels Power BI)

insightsquest.com/2018/02/16/drillthrough-to-detail-rows-paginated-reports (interessanter Zugriff auf die Detailrows-Expression mittels SQL Server Reporting Services)

4. Object Level Security

Zusätzlich zur Row Level Security (RLS) gibt es in SQL Server 2017 eine Object Level Security (OLS), mit der Tabellen, Spalten und indirekt Measures berechtigt werden können. Die Definition erfolgt über den neuen Reiter Tabellen und Spalten im Rollen-Manager:

Hier wurde beispielhaft der Rolle Vertrieb Europa die Berechtigung für die Spalte TotalProductCost - diese wird zur Ermittlung der beiden Measures Cost und Margin verwendet - und für die Tabelle Dim Product entzogen. Mit der Funktion In Excel analysieren kann bequem das Berechtigungssystem getestet werden, indem eine der definierten Rollen angenommen wird:

Im Excel PivotTable werden jetzt die beiden deaktivierten Objekte, aber auch die beiden davon betroffenen Measures aus der Anzeige in der Feldliste entfernt:

Alternativ kann die OLS auch in der Codeansicht der Model.bim bearbeitet werden, möglicherweise sind hier fortgeschrittenere Szenarien umsetzbar (haben wir aber nicht näher untersucht).

Folgende Besonderheiten zu sind zu beachten:

• Tabellen, die eine Brücke in einer Beziehungskette darstellen, darf die Berechtigung nicht entzogen werden (einzelne Spalten hingegen können sehr wohl berechtigt werden)
• Für die OLS dürfen keine eigenen Rollen angelegt werden und diese dann - auf Member-Ebene - mit RLS-Rollen kombiniert werden (d.h. die OLS muß in den gleichen Rollen wie die RLS definiert werden)
• Dynamische Berechnungen wie Measures, KPIs und Drillthroughs werden automatisch von der OLS begrenzt

Weiterführende Informationen:

docs.microsoft.com/en-us/sql/analysis-services/tabular-models/object-level-security

5. Sonstige Neuerungen

• DAX Editor for SSDT
  Der Editor ist gegenüber der bisher üblichen Formelzeile sehr praktisch und besitzt darüber hinaus ausgezeichnete Intellisense-Funktionen. Ungewohnt ist lediglich, daß die Eingabe nicht mit der Taste Enter abgeschlossen werden kann sondern nur durch Anklicken des Häkchen.
  
  Hinweis: die Intellisense-Funktionen funktionieren in der deutschen Version der SSDT im DAX Editor nicht für Detailrows-Statement, da - wie oben beschrieben - hier auch in der deutschen Version das Komma als Trennzeichen verwendet werden muß.
  Weiterführend: blogs.msdn.microsoft.com/analysisservices/2017/04/19/introducing-a-dax-editor-tool-window-for-ssdt-tabular
• Encoding hints
  an advanced feature for optimizing data refresh of large in-memory tabular models
  
  
• Hierarchy and column reuse surfaced in more helpful locations in the Power BI field list
• Date relationships to easily create relationships to date dimensions based on date fields
• Default installation option for Analysis Services is now for tabular mode
• DAX Enhancements: IN operator for specifying multiple values
• SSMS improvements, such as viewing, editing, and scripting support for structured data sources

BI Developer Extensions (vormals "BIDS Helper")

In unseren ersten Tabular Model Projekten unter SQL Server 2014 war der "BIDS Helper" eine absolut unverzichtbare Erweiterung für SSDT, um leistungsfähige Tabular Models zu erstellen. Mit den Releases SQL Server 2016 und 2017 sind sehr viele Features aus dieser Erweiterung in den Standard gewandert. Aktuell stehen nur noch 4 (unwichtige) Funktionsbereiche (= grüne Markierung) unter SQL Server 2017 zur Verfügung:

Ein Verlust ist der Tabular Actions Editor (= rote Markierung), der zwar teilweise durch die neue Detailrows-Funktion kompensiert wird aber eben nur teilweise. Insofern ist uns nicht bekannt, wie in SQL Server 2017 die Additional Actions in einem Excel PivotTable befüllt werden können:

Weiterführende Informationen: bideveloperextensions.github.io/

Tabular Editor von SQLBI.com -- Blogupdate vom 08.02.2019

Als Ergänzung zum SSDT Client sollte der Tabular Editor von sqlbi.com evaluiert werden, da damit sehr rasch und effizient die Metadaten des Datamodels (Measures, Display Folder, Translations, usw.) geändert werden können. Leider beinhaltet das Tool bisher noch keinen Query Editor.

Der Tabular Editor wird als Open Source Projekt unter der MIT License vertrieben und kann daher kostenlos von GitHub heruntergeladen werden:

Hier die Charakterisierung des Tools vom Entwickler:

"Tabular Editor is an editor alternative to SSDT for authoring Tabular models for Analysis Services even without a workspace server. The Tabular Editor is an open source project that can edit a BIM file without accessing to any data. This offline capability makes any change very quick to apply to the BIM file, especially when you manipulate and manage measures, calculated columns, display folders, perspectives and translations."

Der Vorteil gegenüber dem SSDT Client liegt für den Power User also darin, daß die Definitionen des Tabular Model sehr rasch und unkompliziert überarbeitet werden können, da das Tool ohne Datenimport arbeitet und auch beim Deployment nur die Metadaten bereitgestellt werden (das Processing kann dann im SSMS erfolgen). Positiv ist auch zu bewerten, daß der nutzlose "Visual Studio Overhead" aus den SSDT  wegfällt und beispielsweise die Display Folder Strukturen sofort als Baumstruktur angezeigt werden:

Der integrierte DAX Editor unterstützt Drag & Drop von Tabellen, Attributen und bereits bestehenden Measures, jedoch fehlt überraschend die Intellisense Funktion für die DAX Funktionen. Über die Settings kann das "Trennzeichen-Regime" (Komma oder Semikolon) für DAX Statements definiert werden.

Es können bestehende Tabular Model Projekte und/oder Datenbanken bearbeitet werden oder es können auch neue Datenbanken angelegt werden. Beim Datenimport stehen jedoch nur die "Legacy Datenquellen" (siehe oben) zur Verfügung, da der neue Query Editor noch nicht verfügbar ist. Auch steht keine Diagrammansicht zur Verfügung, die Beziehungen können aber auch über die Settings hergestellt werden.

Weitere Tools von sqlbi.com -- Blogupdate vom 08.02.2019

• DAX Studio ("DAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular")
• DAX Editor (Plugin für SSDT)
• Vertipaq Analyzer
• SSAS Tabular Translator
• SSAS Events Analyzer
• SSAS Activity Monitor
• BISM Normalizer

Kritische Würdigung

Kritisch ist festzustellen, daß die Erstellung von Tabular Models mit Compatibility Level 1400 auch noch 1 Jahr nach dem Ersterscheinungsdatum von zahlreichen Bugs und unerwartetem Programmverhalten begleitet wird, insbesondere in Zusammenhang mit dem neuen Query Editor. Die bisherigen SQL Server Versionen waren deutlich stabiler, was vermutlich an der noch nicht vollständig gelungenen Integration der neuen Query Komponente liegt.

Sehr schade ist, daß bisher noch keine Power BI Desktop Anwendungen importiert werden können, die Integration der Query Komponente würde dies grundsätzlich ermöglichen (und wird auch schon in Azure Tabular Model unterstützt).

Trotz dieser Einschränkungen bringt SQL Server 2017 dennoch das leistungsfähigste Tabular Model das es je gab und sollte nach unserer Einschätzung für alle neuen SSAS Anwendungen eingesetzt werden.

Quellen

docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017

docs.microsoft.com/en-us/sql/analysis-services/what-s-new-in-sql-server-analysis-services-2017

blog.crossjoin.co.uk/2018/04/26/deprecated-and-discontinued-functionality-in-ssas-2017