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
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.
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.
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.
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 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.
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.
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
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.
For the examples in this chapter you will import two sets of data to create tables inside the model. This first table is a list of all states in the United States plus the District of Columbia. The second data set is a short list of famous landmarks.
These examples are available at https://github.com/derekewilson/SSAS_2016_Tabular_Model.
This example assumes you have a working tabular mode server and SQL Server Data Tools installed.
Open Visual Studio, select File and then New Project.
On the next screen select Analysis Services Tabular Project to create a new Analysis Service tabular project.
Select Model from the menu and Import from Data Source.
Select Excel File from the bottom of the Table Import Wizard and click Next.
Browse to the location of the
US States.xlsxfile. Check the Use first row as column headers box.
Select the Service Account to specify a user that has access to the data source. Click Next.
Review the Source Table information and click Finish.
Click Close after successfully importing the data.
Now repeat steps 4-8 and import into the data the
Let's review what was done in the previous steps for this first recipe. In steps 1 and 2 we created a new Tabular Model project and selected the option to import data. Then in steps 3 and 4 we selected the source data type of Excel and chose the file to import that included a list of the States in the USA. During step 5 we selected how the data source can be accessed via user security. In step 6, we reviewed the import process and started loading the data. In step 7 we were able to see that the data was successfully imported. Then you repeated the process to import the Famous Landmarks file. You now have two data tables loaded into your model that Tabular mode can use.
Tabular models are built using four main principles: tables, measures, columns, and relationships:
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.
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:
True / False.
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.
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:
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.
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.