Data has always been a key success of any business. Thanks to advances in software, processing and storage technology data is now more abundant than ever. Businesses can collect and store data from internal systems and mash up external data to new insights about their business. One of the challenges is wrangling the data into a manner that is useful to your organization. Microsoft’s SQL Server Analysis Services running in tabular mode allows you to quickly model your data to build business intelligence solutions that will enable your organization to make better decisions.
This book is designed to walk you through the necessary steps to learn the fundamentals of tabular modeling. It uses a public dataset that recorded all crashed in the State of Iowa. Using this dataset, you will design, build, and modify a tabular model. If you are an experienced developer this book can be a great reference to fill gaps in areas, you may not have used. Each recipe can stand alone and show you how to implement a specific feature. If you are a new business intelligence developer and have never used Analysis Services. Start from the beginning of the book and walk through the recipes. Each chapter is designed to build on the knowledge learned in the prior chapters. If you follow all of the recipes in the book you will build a complete solution to help further your understanding from collecting data, modeling, enhancing and visualizing information. You should then be comfortable transferring your knowledge from the examples and recipes in this book and apply the concepts to your own business data and challenges.
Chapter 1 , Introduction to Microsoft Analysis Services Tabular Mode, introduces SQL Server 2016 and Microsoft’s Business Intelligence. You will learn about tabular modeling and the basic concepts that are used to build a solution. You will also review the new features that were released in SQL Server 2016.
Chapter 2, Setting up a Tabular Mode Environment, shows you how to install and configure SQL Server Analysis Services in tabular mode. In addition, you will install and configure Visual Studio 2015 and SQL Server Data Tools. Once setup you will learn how to configure your tabular model project.
Chapter 3 , Tabular Model Building, begins your foundational knowledge of tabular mode. You will begin by adding data to a model, create relationships between tables and then create a calculated column and measure. Finally, you round out the model with hierarchies and folders and deploy the model to the server.
Chapter 4, Working in Tabular Models, expands on the initial model and shows how to make modifications to existing and deployed model. In addition, you will learn how to create and modify Key Performance Indicators (KPIs).
Chapter 5, Administration of Tabular Models, examines how to manage and modify your model’s properties. You will learn about perspectives, data partitions, user roles, and server properties.
Chapter 6, In-Memory Versus DirectQuery Mode, shows examples of the two choices in data storage and processing options. You then learn how to configure DirectQuery mode and the advantages and limitations of its use.
Chapter 7, Securing Tabular Models, details the different ways to implement security in a tabular model using both row level and dynamic security. The recipes in this chapter show how to create and modify security on your model.
Chapter 8, Combining Tabular Models with Excel, explores the various ways to leverage Microsoft Excel when designing and building a tabular model. You will explore data in Excel directly from Visual Studio when building a solution. In addition, you will connect to your model from Excel and use Power View and Power Pivot to explore the model.
Chapter 9, DAX Syntax and Calculations, explains the basics of Data Analysis Expressions (DAX) and how DAX is used to enhance a tabular model. Recipes are given on several of the more commonly used DAX formulas and how to filter data in your queries.
Chapter 10, Working with Dates and Time Intelligence, details how to create and define a Date table that the model will use for date and time based functions. Then you will explore common date functions to enhance your model to make it easy for your users to leverage the model.
Chapter 11, Using Power BI for Analysis, shows how to connect to the completed model and create reports. Recipes in this chapter detail how to create and modify visualizations and bring them together to create a dashboard.
To run the recipes in this book you will need the following software:
Virtual Machine Software
Windows Server 2012
SQL Server 2016 Developer Edition
Microsoft Excel 2016
Microsoft Power BI Desktop
This book was written primarily for developers who want to better understand how to build BI solutions using Microsoft SQL Server Analysis Services running in tabular mode. If you are a new to Analysis Services running in tabular mode. This book will walk you through developing a complete BI solution. If you are an experienced BI developer, then you can use this book as a reference to review what you already know or skip ahead to the recipes that you need additional information to implement. If you are a business user you can use this book to better understand how to leverage Excel and Power BI to build business solutions.
In this book, you will find several headings that appear frequently (Getting ready, How to do it, How it works, There's more, and See also).
To give clear instructions on how to complete a recipe, we use these sections as follows:
This section tells you what to expect in the recipe, and describes how to set up any software or any preliminary settings required for the recipe.
This section usually consists of a detailed explanation of what happened in the previous section.
This section consists of additional information about the recipe in order to make the reader more knowledgeable about the recipe.
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "Create a new user for JIRA in the database and grant the user access to the jiradb database we just created using the following command:"
A block of code is set as follows:
Total_Fatalities_GT2_MajorInjuries := SUMX( FILTER(CRASH_DATA_T, CRASH_DATA_T[MAJINJURY]>2), CRASH_DATA_T[FATALITIES] )
Any command-line input or output is written as follows:
mysql -u root -p
New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "Select System info from the Administration panel."
Feedback from our readers is always welcome. Let us know what you think about this book-what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail [email protected], and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for this book from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
You can download the code files by following these steps:
Log in or register to our website using your e-mail address and password.
Hover the mouse pointer on the SUPPORT tab at the top.
Click on Code Downloads & Errata.
Enter the name of the book in the Search box.
Select the book for which you're looking to download the code files.
Choose from the drop-down menu where you purchased this book from.
Click on Code Download.
You can also download the code files by clicking on the Code Files button on the book's webpage at the Packt Publishing website. This page can be accessed by entering the book's name in the Search box. Please note that you need to be logged in to your Packt account.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR / 7-Zip for Windows
Zipeg / iZip / UnRarX for Mac
7-Zip / PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/Tabular-Modeling-with-SQL-Server-2016-Analysis-Services-Cookbook. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from https://www.packtpub.com/sites/default/files/downloads/TabularModelingwithSQLServer2016AnalysisServicesCookbook_ColorImages.pdf.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books-maybe a mistake in the text or the code-we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at [email protected] with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
If you have a problem with any aspect of this book, you can contact us at [email protected], and we will do our best to address the problem.