Microsoft SQL Server Analysis Services (SSAS) enables IT professionals to build semantic models over large, integrated managed datasets for end-user consumption. SSAS runs on Windows and is part of the larger suite of SQL Server’s BI offerings. SSAS 2017 brings a host of new connectivity and modeling features for comprehensive, enterprise-scale analytic solutions delivering actionable insights.

SSAS 2017 introduces the 1400 compatibility level. Here are just some highlights of the new features:

• New infrastructure for data connectivity and ingestion into tabular models with support for TOM APIs and TMSL scripting. This enables support for a range of additional data sources, and data transformation and mashup capabilities.
• Support for BI tools such as Microsoft Excel enables drill-down to detailed data from an aggregated report. For example, when end-users view total sales for a region and month, they can view the associated order details.
• Object-level security to secure table and column names in addition to the data within them.
• Enhanced support for ragged hierarchies such as organizational charts and chart of accounts.
• Various other improvements for performance, monitoring, and consistency with the Power BI modeling experience.

To benefit from the new features for models at the 1400 compatibility level, you’ll need to download and install SQL Server Data Tools (SSDT) 17.0 from documentation.

In SSDT, you can select the new 1400 compatibility level when creating new tabular model projects. Alternatively, you can upgrade an existing tabular model by selecting the Model.bim file in Solution Explorer and setting the Compatibility Level to 1400 in the Properties window. Models at the 1400 compatibility level cannot be downgraded to lower compatibility levels.

New infrastructure for data connectivity

1400 models introduce a new infrastructure for data connectivity and ingestion into tabular models with support for TOM APIs and TMSL scripting. This is based on the similar Power Query functionality in Power BI Desktop and Microsoft Excel 2016. For more information, please refer to the Analysis Services Team blog.

Detail Rows

A much-requested feature for tabular models is the ability to define a custom row set contributing to a measure value. Multidimensional models achieve this by using drillthrough and rowset actions. This allows end-users to view information in more detail than the aggregated level.

For example, the following PivotTable shows Internet Total Sales by year from the Adventure Works sample tabular model. Users can right-click the cell for 2010 and then select the Show Details menu option to view the detail rows.

By default, all the columns in the Internet Sales table are displayed. This behavior is often not meaningful for the user because too many columns may be shown, and the table may not have the necessary columns to show useful information such as customer name and order information.

Detail Rows Expression Property for Measures

1400 models introduce the Detail Rows Expression property for measures. It allows the modeler to customize the columns and rows returned to the end user. The following example uses the DAX Editor in SSDT to define the columns to be returned for the Internet Total Sales measure.

With the property defined and the model deployed, the custom row set is returned when the user selects Show Details. It automatically honors the filter context of the cell that was selected. In this example, only the rows for 2010 value are displayed.

Further information on Detail Rows is available our blog post, What’s new for SQL Server vNext on Windows CTP 1.1 for Analysis Services.

Object-Level Security

Roles in tabular models support a granular list of permissions, and row-level filters to help protect sensitive data. 1400 models introduce table- and column-level security allowing sensitive table and column names to be protected in addition to the data within them. Collectively these features are referred to as object-level security (OLS).

The following example uses the Role Manager in SSDT to protect sensitive employee information. Unauthorized users cannot access the selected columns using client tools like Power BI and Excel Pivot Tables. Additionally, such users cannot query the columns using DAX or MDX, or measures that refer to them.

Further information on OLS is available in a recent post, titled, What’s new in SQL Server 2017 CTP 2.0 for Analysis Services.

Ragged Hierarchies

Tabular models with previous compatibility levels can be used to model parent-child hierarchies. Hierarchies with a differing number of levels are referred to as ragged hierarchies. An example of a ragged hierarchy is an organizational chart. By default, ragged hierarchies are displayed with blanks for levels below the lowest child. This can look untidy to users, as shown by this organizational chart in Adventure Works:

1400 models introduce the Hide Members property to correct this. Simply set the Hide Members property to Hide blank members.

With the property set and the model deployed, the more presentable version of the hierarchy is displayed.

Other Features

Various other features such as the following are introduced with the 1400 compatibility level. For more information, please refer to the Analysis Services Team blogs, What’s new for SQL Server vNext on Windows CTP 1.1 for Analysis Services and What’s new in SQL Server 2017 CTP 2.0 for Analysis Services.

• Transaction-performance improvements for a more responsive developer experience.
• Dynamic Management View improvements enabling dependency analysis and reporting.
• Date relationships to easily create relationships to date dimensions based on date columns.
• DAX enhancements to make DAX more accessible and powerful. These include the IN operator, and table and row constructors.

The post Analysis Services Innovations in SQL Server 2017 appeared first on Microsoft SQL Server Blog.

Every now and then, we like to highlight how our customers are using Microsoft’s database platform solutions now to solve for these types of needs in real-time. One such customer is CROSSMARK, a provider of sales and marketing services for manufacturers and retail companies, who recently launched a new self-service data portal powered by SQL Server 2008 R2 Parallel Data Warehouse (PDW) to bring this data and these insights to its customers. SQL Server PDW’s on-demand data access will allow CROSSMARK’s customers to leverage shopper insights and data to inform strategies and tactics to create more effective sales and marketing campaigns to boost sales and profitability.

Before implementing SQL Server PDW, CROSSMARK had a bottleneck in its legacy platform that created data reports that weren’t scalable, making employees spend valuable time with data reporting instead of working with customers. Now, with SQL Server PDW, CROSSMARK can easily scale its resources to handle the millions of in-store activities processed each year and allow CROSSMARK employees to spend more time with its customers and less time with the data.

CROSSMARK is also on-track to implement SQL Server BI tools including Power View and PowerPivot to provide more business intelligence tools to its customers.

To read more about CROSSMARK, take a look at this Customer Spotlight feature on News Center.

The post CROSSMARK Uses SQL Server 2008 R2 Parallel Data Warehouse to Quickly Deliver Business Insights appeared first on Microsoft SQL Server Blog.

Volvo Car Corporation
When Volvo Car Corporation split off from Ford in 2010, it needed to develop its own stand-alone IT environment in some areas. At the same time, the company wanted to improve its business intelligence (BI) capabilities and operational efficiency. So Volvo decided to prototype a Microsoft data management solution that simplifies its IT environment, provides graphical self-service reporting capabilities, and improves collaboration.

“Instead of having a few expert users, with Power View we can give BI to entire departments.” IT Architect, Volvo Car Corporation

Klout
Klout wanted to give consumers, brands, and partners faster, more detailed insight into hundreds of terabytes of social-network data. It also wanted to boost efficiency. To do so, Klout deployed a business intelligence solution based on Microsoft SQL Server 2012 Enterprise and Apache Hadoop. As a result, Klout processes data queries in near real time, minimizes costs, boosts efficiency, increases insight, and facilitates innovation.

“When it comes to business intelligence, Microsoft SQL Server 2012 demonstrates that the platform has continued to advance and keep up with the innovations that are happening in big data.” David Mariani, VP of Engineering

Menlo Worldwide Logistics
Con-way subsidiary Menlo Worldwide Logistics intends to drive double-digit growth of its supply chain services business over the next 3–4 years. It expects business intelligence (BI) to be a main driver of new customer value and growth but has not been satisfied with the capabilities of its existing IBM Cognos and DB2 software platform. To jump-start its BI effort, Menlo is deploying the Microsoft BI platform, which will help it attract business with improved analytics and new value-added customer services.

” The flexible Microsoft BI approach enables our end-user teams to tailor solutions to meet the unique requirements of our customers while taking advantage of a common, consistent, and cost-effective platform” Mark Molau, Senior IT Manager for Menlo Worldwide Logistics

ABB S.p.A.
ABB S.p.A. is owned by ABB, a global power and automation company headquartered in Zurich, Switzerland. To meet business demands, ABB S.p.A. needed to give employees better business intelligence (BI) capabilities. The company also wanted to consolidate its data to give employees a single view of business and customer information. ABB S.p.A. is implementing a Microsoft BI and data warehouse solution that will provide better business insight and give employees a unified data view. The solution also gives the company the flexibility to add analytical tools going forward.

“Using the SQL Server Power View tool and the BI Semantic Model in SQL Server 2012, our employees will be able to easily create multidimensional reports, providing better insight into business and customer data.” Andrea Franchi, BI Technical Lead, ABB S.p.A

Stein Mart
Stein Mart, a nationwide department store, needed better access to business data to set prices each week. By implementing a Microsoft data warehouse and business intelligence (BI) solution, the company can manage information more efficiently and cut reporting time from hours to minutes. The solution is easy to use and manage, and the company has cut its total cost of ownership by more than 60 percent.

“The alternative solutions considered would have required purchasing separate reporting, ETL and integration, and database management tools. We’ve reduced our total cost of ownership about $600,000 with a Microsoft BI solution,” Ilan Wajsman, Director of IT at Stein Mart

Which other are customers using SQL Server?

Visit the customer story page on the SQL Server website which allows you to filter on over 200 customer stories by Industry, Country, Solution Workload, Partner or SQL Server version. This will help you find the customer stories most suited to the scenario you are working on.

David Hobbs-Mallyon
Senior Marketing Manager , SQL Server Marketing

The post Customers achieving Breakthrough Insight with SQL Server 2012: Volvo Car Corporation, ABB SpA, Klout, Stein Mart and Menlo Worldwide Logistics appeared first on Microsoft SQL Server Blog.