Microsoft SQL Server 2017 became generally available in October. Although there are many new enhancements across the entire technology stack, this blog post will showcase and highlight potential use cases of some of the new features available in SQL Server Analysis Services (SSAS) 2017. For a complete list of all the new features available in SSAS 2017, check out Microsoft’s documentation here!
ATTENTION: To explore the new features on your own, you must use the latest version of SQL Server Data Tools (SSDT). Additionally, your Tabular models must be set to compatibility level 1400 for SQL Server 2017 or Azure Analysis Services.
Modern Get Data Experience AKA Power Query
Enhancing your Data Warehouse
New to SSAS 2017, Microsoft has introduced the same “modern Get Data experience” you may recognize from Power BI Desktop and Excel.
NOTE: This “Get Data” technology is known internally as Power Query. The language behind Power Query is called M. Microsoft dropped the Power Query name, at least externally, to be less confusing to users.
Figure 1: The “Power” Query Editor
The “Get Data” feature provides developers with another method to not only acquire data for their semantic models, but to cleanse, transform, and enhance it, as well. Traditionally, SSAS would “sit on top of” a mature data warehouse residing in a relational, on-premises database such as SQL Server. However, over the years the product has evolved to allow connectivity to a variety of data sources, from file-based sources such as Excel and text files, to Azure components such as SQL Data Warehouse and Data Lake Store. SSAS 2017 takes this a step further by not only allowing connectivity to additional sources, but by providing the ETL capabilities of Power Query to shape, manipulate, and append your incoming data as well.
USE CASE: Enhancing the Data Warehouse
Contrary to product marketing campaigns and the latest trends in the analytics space, the Enterprise Data Warehouse is not a thing of the past. In fact, the data warehouse should be the main engine that drives a company’s decision making. That said, the data warehouse can always be enhanced further. For example, let’s say your company is planning to expand into foreign markets, and they want to see currency exchange rates along with their sales data. As an SSAS developer, you can leverage the new “Get Data” engine to pull in historical exchange rates from the web and integrate this with your historical sales data. Having ETL capabilities in SSAS essentially fast-tracks your development efforts. You’re no longer dependent on integrating data points such as publicly available datasets directly in your data warehouse. You can bridge the data directly in SSAS.
Another common use case is creating calculated columns in SSAS. Calculated columns are an effective way to enhance your tabular models, but they may not always perform well when filtered upon. For this reason, it may be best to derive your new column prior to populating your tabular model.
Figure 2: Adding a custom column with the M language during the data import
process, as opposed to creating a calculated column with DAX
NOTE: Get Data/Power Query and the M language are topics that warrant their own blog post (okay, series of blog posts). I wanted to bring attention to this new feature of SSAS 2017, but it is out-of-scope to discuss the plethora of powerful ETL capabilities that this feature truly provides.
Ragged Hierarchy Improvement
Modeling Financial Data with Hide Members Property
SSAS Tabular models allow parent-child hierarchies, but previous versions struggled to properly display ragged hierarchies. A ragged hierarchy is a type of parent-child hierarchy that has a variable and indeterminate depth. The textbook ragged hierarchy example is the manager-employee relationship in an organization. In one direction, an employee typically has a direct manager. Conversely, that manager may have other employees he/she oversees. In turn, these employees may be managers themselves, thus creating a ragged hierarchy.
USE CASE: Modeling a Chart of Accounts
Another ragged hierarchy example we encounter often in the BI space is displaying a company’s Chart of Accounts (COA). A COA is a hierarchical listing of accounts that categorize transactions against a company’s general ledger. It is used to segregate a company’s assets, revenue, expenditures, liabilities, etc. in a meaningful way. Each company defines their own unique COA, resulting in indeterminate depth and creating a ragged hierarchy. SSAS 2017 introduces an improved way to browse ragged hierarchies by allowing you to hide blank members.
To demo this, I developed a Tabular model on top of the AdventureWorks DW database we all know and love. This sample database is perfect because it includes an Account dimension for us to work with. As with any parent-child dimension, there is a field for the current record’s key, as well as a separate field for the parent record key.
Figure 3: Sampling of the Account dimension inside the Adventure Works DW database
I need to bring this relationship to life by building the COA hierarchy path for each record. There are many ways to implement this, including leveraging a recursive common table expression (CTE) inside of SQL Server itself, but hey, this is a blog post on SSAS, so I used DAX to do the heavy lifting inside of the Tabular model. Creating the COA hierarchy path is one half of the battle. The next piece is to split the values, or levels, of our hierarchy into individual fields. Since ragged hierarchies do not have a fixed depth, I calculated the maximum depth of the entire COA hierarchy to determine how many level fields were needed.
Figure 4: Using DAX PATH functions to visualize the COA hierarchy.
NOTE: This is not a blog post on DAX, but rather new features of SSAS 2017. For a great in-depth walkthrough of how I leveraged the various PATH functions in DAX to visualize my hierarchy, check out this post by the SQLBI guys. They are the renowned experts in all things DAX.
After creating new level fields in my Accounts table, I created a simple hierarchy aptly named “Chart of Accounts”, comprising the six level fields constructed above. The screenshots below contrast the default display from older versions of SSAS with the new Hide Members property set to Hide blank members in SSAS 2017. As you can see, the hierarchy is much cleaner to work with in SSAS 2017.
|
|
Figure 6a: Hide Members set to Hide blank members |
 Figure 5b: COA hierarchy by default |
 Figure 6b: Hide blank members property in action. A simple, yet long-awaited, new feature! |
Detail Rows
“Just show me the numbers”
SSAS, at its core, is an analytical reporting product. It provides users a semantic layer that contains complex logic composed of key business entities and advanced measures. Users can leverage products such as Power BI and Excel to easily gain quick insights into their business via a drag-n-drop interface.
USE CASE: Drill into the Details
With analytical reporting, you are analyzing your data at highly aggregated level. For instance, let’s say you are analyzing your company’s sales by month for a specific year. You may find that a given month is an outlier in the data set and want to dive deeper into the transactions that contribute to the amount. You know you want to see details about individual orders including their transaction date, the products that were sold, and the customer they were sold to. In other words, you simply want to “see the details”. Finance teams are often asked to provide these kinds of transaction details to auditors to identify precisely how they arrived at a value.
Traditionally, the problem with these types of requests is that there is nothing analytical in providing a list of individual transactions. Technologies such as Excel pivot tables are not optimal to provide such a detailed list. However, SSAS 2017 introduces a new feature called Detail Rows. With Detail Rows you can define an expression on a given measure that will allow users to see the details behind that single data point! The series of screenshots below shows an example of this new feature in action.
Figure 7: I created a new measure in my Tabular model called Sales Amt. In the properties menu for my Sales Amt measure I wrote a DAX query for my Detail Rows Expression that will retrieve my desired fields.
Figure 8: In Excel, I constructed a Pivot Table against my Tabular model that slices the Sales Amt measure by Month. As you can see, for the year 2013 our sales steadily increased, but we had a dip in February. I want to see the details behind this number.
Figure 9: I right-clicked on my February data point and selected Show Details…
Figure 10: This created a new worksheet that displays the transactions that contribute to the February data point on my Pivot Table. And wouldn’t you know, those are the same fields I specified in my Detail Rows Expression at the beginning.
NOTE: There are additional capabilities that go along with the new Detail Rows feature. This includes the ability to set an expression for an entire table in your model, as well as the new DETAILROWS DAX function. Please refer to the Microsoft documentation listed at the beginning of this post for more information.
Bonus Feature – Object-Level Security
I would be remiss if I didn’t mention Object-Level Security because it’s awesome! We can now define security on specific tables and columns in addition to the row-level security tabular models already provided. We can even make it so sensitive objects such as a salary and/or social security number field aren’t discoverable by malicious users. Read more about it here!
Still looking for help? BlueGranite experts can assist with the strategy and implementation of SSAS at your organization. To learn more about the process and what to look out for during implementation, contact us!
