Neue Funktionen in SQL Server 2017 Tabular Model

Publiziert von

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.

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:

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/

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

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Seit seinem Betriebswirtschaftsstudium an der WU Wien ist er als Unternehmer in der BI Branche tätig, ist Trainer für Excel BI und Power BI und unterstützt Unternehmen bei der digitalen Transformation ihrer Business Intelligence Prozesse. Er ist Autor des Linearis Blogs und mehrerer fachlicher Publikationen.

Weitere Beiträge

Kategorien: Datawarehousing, SQL Server als DWH nutzen