5 Innovative Enhancements in Analysis Services in SQL Server 2017

The article brings the innovative features in SQL SERVER 2017 into limelight and provides a quick walkthrough of the new features.

In the 2017 edition of SSAS i.e. MS SQL-Server Analysis Services, the Redmond based software giant has introduced new features of connectivity and modeling for enterprise scale solutions which can deliver valuable insights. SSAS is basically a part of SQL server suite and runs on windows allowing professionals to design semantic models. The features introduced in SSAS 2017’ has 1400 compatibility level, it includes Object-level Security for tables, enhances support for organizational charts, extended support for BI tools, improved infrastructure for data-connectivity. There are also great improvements in performance and monitoring of Power BI models.A Quick Lowdown On Innovative Enhancements Done In Analysis Services In SQL Server 2017

Let’s take a detailed look into these added features.

1. Data Connectivity Improvements

There is a newly introduced Infrastructure for connectivity for tabular models with additional support for TMSL scripting and TOM APIs in 1400 models. It is said to be based on the Power Query function in MS Excel and Power BI Desktop. To avail the new features, you can download and Install SSDT – SQL Server Data Tools and after download, you can easily select them from the compatibility level menu.

2. Detailed Rows

An exciting feature for tabular models is introduced which allows the users to define custom rows which can contribute in measuring value. It will certainly help end-users in viewing information in a detailed manner as compared to the previous aggregated level.

Now if there is a table, and you wish to view the contained information you can simply right click the cell and select ‘Show Details’ option from the dropdown menu.

3. Additional security

1400 models have also introduced additional table and column level security to protect critical data and also the table and column names. These features are called Object level Security OLS. It prevents unauthorized users from accessing the marked columns in Role Manage in SSDT. They can’t even make queries in these selected columns by using MDX or DAX tools.

So in Role manager, you can specify roles for the tabular project. Under the Table and Column Security you can mark the columns which you don’t want other users to see.

4. Ragged Hierarchies

Ragged hierarchies are user defined hierarchies with an uneven number of levels. These hierarchies were not compatible with previous levels in tabular models.

Example for ragged hierarchy could be an organizational chart. Usually, these hierarchies are shown as blank spaces just below the lowest levels by default and it looks untidy to users. The new features included a Hide Members property to avoid this folly. Users can now choose to set the Hide members property and create a more presentable hierarchy.

5. Detail Rows Expression Property

SQL Server 2017The newly added property for measures helps the users (or modeler) to customize rows and columns which are returned to the end user. The property is called Detail Rows Expression property and when it is defined and the model is deployed, the row set is returned to the end user when Show Details is selected.

In addition to these features, 1400 models have also made improvement in Transaction performance for better response and also enhanced dynamic management view. The new DAX is more powerful and accessible and includes IN operator and row constructor.

The 2017 edition of SQL Server is undoubtedly one of the best database applications in vogue today. However that does not make it flawless. It is still advisable to invest in a SQL Server fix tool to deal with any incidents of database crash.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdb recovery and sql recovery software products. For more information visit https://www.datanumen.com/

One response to “5 Innovative Enhancements in Analysis Services in SQL Server 2017”