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 Invoke-ProcessAsDatabase and Invoke-ProcessTable cmdlet.
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.