What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Chapter 1. Introduction to Microsoft Analysis Services Tabular Mode

In this chapter, we will cover the following recipes:

Learning about Microsoft Business Intelligence and SQL Server 2016
Understanding tabular mode
Learning what's new in SQL Server 2016 tabular mode
Importing sample datasets
Understanding basic concepts

Introduction

Microsoft continues to add and enhance the business intelligence offerings that are included with SQL Server. With the release of SQL Server 2012, Microsoft added Tabular Mode for Analysis services as a deployment option. Unlike traditional multidimensional Analysis Services models that write to disk and require special model design and implementation, tabular models are created using basic relational data models. Then, using in-memory technology, the model is deployed to RAM for faster access to the data. Microsoft has created a new query language to interact with the Tabular model named Data Analysis Expressions, or DAX for short. For new BI developers' tabular models can be an easier way to get started with delivering business results.

For experienced developers, Tabular models can offer an additional method to develop BI solutions. You can develop robust completed solutions or quickly develop prototypes without investing heavily in ETL or Star Schema designs.

In order to take advantage of this technology you need to understand the basics of tabular models and how they work. This chapter focuses on the background you need to get started with designing and deploying tabular models.

After reading this section, you will understand what tabular mode in SQL Server Analysis Services is. You will also learn the basic components required to create a tabular model and how to import data into your first project.

Every tabular model begins with data that you import into your project. This chapter teaches you the skills required to get started by importing a list of states in the United States and a short list of famous United States landmarks. By using these two small tables and their data, you will learn all of the core components of tabular modeling. Later you will use a much larger sample dataset from the state of Iowa to build a complete tabular model solution.

Learning about Microsoft Business Intelligence and SQL Server 2016

In SQL Server 2016, Microsoft has added many new features to the Business Intelligence stack. The Microsoft BI Stack refers to the components most used with creating data insights. These include Reporting Services, Analysis Services, and Integration Services.

Reporting Services is a standalone enterprise reporting platform. It can connect to a wide variety of data sources that allow you to create rich and powerful reports for your users.

Analysis Services includes traditional OLAP solutions as well as the newer tabular mode version. With Analysis Services you can create analytical solutions that enable your users to quickly explore the data without writing code. In addition, you can add custom calculations to create KPI's, trends, and show period over period growth to the values in the model.

Integration Services is an enterprise data integration solution. You can design and build robust ETL solutions that move data across your enterprise. In addition, you can add steps to transform, clean, or analyze the data while it is being moved to improve the data for your users.

Business intelligence projects are primarily concerned with turning raw data into business information. Source systems store and collect data to process transaction such as making an online purchase. Business intelligence systems look to gather individual transactions and present the data to business users to improve the operations of a business. BI solutions can be created for any industry. In financial industries you can monitor cash flow, expenses, and revenue. You can also create KPIs that let you know when critical metrics are hit or missed. For marketing, you can combine data from various systems, both internal and external, such as Twitter or Facebook to create a comprehensive view of customer interactions. This can help you know how well your marketing campaigns are working and which ones are most effective. In retail industries you can build solutions to track customer purchases and changes in customer patterns over time. For example, questions such as how many users made purchases over the last seven days, or what is the average purchase price per customer are typical BI questions.

This book focuses on Analysis Services, specifically the Tabular Mode engine. Developers can leverage this engine to create high-performing BI solutions that provide valuable data to your business users.

Understanding tabular mode

Microsoft SQL Server Analysis Services can be deployed in two ways, multidimensional mode or tabular mode. Tabular mode is the newest way to build and deploy BI solutions and it requires installation of the Analysis Services engine in Tabular mode within your SQL Server Installation. Once installed you design and build Tabular models in SQL Server Data Tools (SSDT). SSDT is installed inside Visual Studio and allows for a complete development experience within a single tool. You can design, build, and refactor your database solutions. When development is complete you deploy from your desktop SSDT solution to the Tabular model server. Once deployed your users are able to connect to the models and can explore and leverage the data.

Tabular models are deployed in memory or in DirectQuery mode and deliver fast access to the data from a variety of client tools.

Learning what's new in SQL Server 2016 tabular mode

The release of SQL Server 2016 includes a variety of enhancements for Tabular mode in the areas of modeling, instance management, scripting, DAX, and developer interfaces. These changes continue to make designing and building Tabular modes easier to provide better value to your business.

Modeling

Modeling is where you start with tabular mode. All users will connect to the server and access the data provided from a model. As a designer, you will spend most of your time inside the Tabular Model adding data, creating relationships, and custom calculations. With the SQL Server 2016 release, tabular models have a compatibility level of 1,200.

If you have used prior editions of SQL Server to build Tabular Models you will notice right away the designer is much faster in this release. When modeling in SQL Server Data Tools, you will notice that the performance of tabular models has been improved. Design changes will occur faster than previous versions, such as creating a relationship or copying a table. Also included now is the ability to create folders to organize your model for better end user navigation. This enables you to group your data into logical folders such as Sales, Regions, Gross, Net, and so on, and it also helps your users know where to go for data instead of a long list of values.

If you need the ability to store multiple definitions for a name or a description to account for different languages, this ability is handled under the Translation tab of the model. For instance, you could store Customer Name in English and provide a variety of cultural translations as required.

In this release, you can now deploy to a variety of environments, as you have been able to do with multidimensional modeling. Developers can develop and deploy to a test environment. Then if you need to deploy to a UAT environment on a different server, you can do that by leveraging the configuration manager.

Instance management

After setting up and configuring tabular mode on a server that is known as an instance, you can have multiple instances running on the same server provided you have enough hardware to run all instances. Each instance can have different properties, security, and configurations based on your needs.

An SQL Server 2016 Tabular mode instance can now run prior versions of tabular services. This allows for compatibility level 1,100 and 1,103 models to be run without the requirement of upgrading the model to the current release and redeploying the model to the instance.

Scripting

Microsoft continues to add functionality to improve the ability to write scripts for tabular models. Scripting allows you to write code that will perform actions instead of using the visual design tools such as SSDT or SQL Server Management Studio.

PowerShell cmdlets are able to be used, such as Invoke-ProcessAsDatabase and Invoke-ProcessTable cmdlet.

DAX

DAX is the language that you will use the most inside tabular models. New in this release is an improved formula editor. When creating formulas inside the formula bar functions, fields and measures are color coded. The intelligence function inspects your formula as you create it to let you know any known errors. In line comments can now be added to help document your function by using //. The creation of DAX measures no longer requires the measure to be complete. You can now save incomplete DAX measures in your model and complete them at a later time.

Understanding basic concepts

Tabular models are built using four main principles: tables, measures, columns, and relationships:

Tables

Tables contain the columns and rows of data that you are using to populate your Tabular data model. Data can be added from a variety of source systems. Examples include relational database structures such as tables or views, Analysis Services cubes, or text files. The tabular mode engine does not require you to transform data into special schema structures such as Star or Snowflake schemas. By leveraging the tabular model engine you can connect directly to data and transform it in the model designer if needed. This enables quicker model design and iterations without the need to invest in design and building data transformations and load processes. You can share the model with users to ensure the business need is being met. In addition to performing time calculations you will have to create and configure a table known to be a Date Table.

Using the model designer, you can view tables as either a diagram or a grid design view. The grid designer view shows the data in the table similar to viewing the data as an Excel file. This view is where you will create new DAX calculations and review the data.

When using the grid designer view, you see a data model of the table that displays the table and column names. Using this mode enables you to create hierarchies on a table and relationships between tables.

Columns

Every table will contain columns that store the data that make up your model. When you import data into your table, the designer inspects each column to automatically determine the type of data in the column and assign it a data type. In SQL Server 2016, the following data types are allowed:

Currency.
Date.
Decimal Number.
Text.
True / False.
Whole Number.

Measures

In order to perform calculations in Tabular mode you must create measures using Data Analysis Expressions, also known as DAX. Adding measures to your data improves the usefulness of the information to your business users. For instance, adding a calculation to perform period over period growth to a tabular model would allow all users to leverage the same calculation and result. Otherwise, you may have users creating calculations outside of the model that use different logic.

Relationships

As you build more complex models that contain many tables, relationships are the method to determine how data in one table relates to data in another table by linking columns. When adding a relationship to a Tabular Model, the column data must be the same. For example, if you create a relationship between an address table and a state table containing the master data of all 50 United States plus the District of Columbia, the columns used to link would have to match the data.

Once you know what columns and tables you want to link together you then must determine the type of relationship to establish. In the Tabular Model designer, you can create two types of relationships:

One-to-one
One-to-many

Continuing on our previous example will demonstrate the differences in the types of relationships. Every address can have one state and is a one-to-one relationship. However, every state can have multiple addresses and is an example of a one-to-many relationship.

When building relationships there are rules that are enforced in the model designer; first, each column can only be used in a single relationship. You cannot reuse a column that is already established in a relationship. The second rule is there can be only one active relationship between tables.

Hierarchies

Much like how relationships define how tables are joined together and related, hierarchies define how data between columns is related. You add hierarchies to your model to make it easier for your business users to leverage the data. The classic example of a well formed hierarchy is a Calendar hierarchy built on a date table. The top of the calendar is the highest unit of measure and the bottom of the hierarchy is the lowest unit of measure. Therefore, you could have a Calendar hierarchy that is defined as Year | Quarter | Month | Day. Given this hierarchy users could navigate the model starting at Year and then drill down into the next lower level (Quarter), and then ultimately down to the day to get more detail based on their needs.

Key benefits

Build and deploy Tabular Model projects from relational data sources

Leverage DAX and create high-performing calculated fields and measures

Create ad-hoc reports based on a Tabular Model solution

Useful tips to monitor and optimize your tabular solutions

Description

SQL Server Analysis Service (SSAS) has been widely used across multiple businesses to build smart online analytical reporting solutions. It includes two different types of modeling for analysis services: Tabular and Multi Dimensional. This book covers Tabular modeling, which uses tables and relationships with a fast in-memory engine to provide state of the art compression algorithms and query performance. The book begins by quickly taking you through the concepts required to model tabular data and set up the necessary tools and services. As you learn to create tabular models using tools such as Excel and Power View, you’ll be shown various strategies to deploy your model on the server and choose a query mode (In-memory or DirectQuery) that best suits your reporting needs. You’ll also learn how to implement key and newly introduced DAX functions to create calculated columns and measures for your model data. Last but not least, you’ll be shown techniques that will help you administer and secure your BI implementation along with some widely used tips and tricks to optimize your reporting solution. By the end of this book, you’ll have gained hands-on experience with the powerful new features that have been added to Tabular models in SSAS 2016 and you’ll be able to improve user satisfaction with faster reports and analytical queries.

What you will learn

Learn all about Tabular services mode and how it speeds up development

Build solutions using sample datasets

Explore built-in actions and transitions in SSAS 2016

Implement row-column, and role-based security in a Tabular Data model

Realize the benefits of in-memory and DirectQuery deployment modes

Get up to date with the new features added to SQL Server 2016 Analysis Services

Optimize Data Models and Relationships Usage

What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

Frequently bought together

SQL Server 2016 Reporting Services Cookbook

zł246.99

SQL Server 2017 Integration Services Cookbook

zł266.99

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

zł246.99

Total zł 760.97

Loraine Aug 15, 2017

As described

Amazon Verified review

Charanya S Jul 31, 2019

Great book to get started with tabular modeling. Must have resource for any professional Microsoft BI designer/ developer looking to get most out of SSAS tabular modeling, DAX measures and introduction to Power BI.

Robby Jul 11, 2019

Here's a message to Packt's genius marketing... I have purchased FAR less of your books because for some asinine reason you don't allow me to preview the book on Amazon. If I can't look at a few chapters, I ain't buying that d@nm thing.

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook: Create better operational analytics for your users with these business solutions

What do you get with Print?

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Chapter 1. Introduction to Microsoft Analysis Services Tabular Mode

Introduction

Learning about Microsoft Business Intelligence and SQL Server 2016

Understanding tabular mode