Tabular modeling with SQL server 2016 analysis services cookbook : expert tabular modeling techniques for building and deploying cutting-edge business analytical reporting solutions / Derek Wilson.

Format:

Book

Author/Creator:

Wilson, Derek, author.

Language:

English

Subjects (All):

SQL server.

Client/server computing.

Relational databases.

Database management.

Physical Description:

1 online resource (362 pages) : color illustrations

Edition:

1st edition

Place of Publication:

Birmingham : Packt, 2017.

System Details:

text file

Biography/History:

Wilson Derek: Derek Wilson is a data management, business intelligence and predictive analytics practitioner. He has been working with Microsoft SQL Server since version 6. 5 and with Analysis Services since its initial version. In his current role he responsible for the overall architecture, strategy, and delivery of Business Intelligence, analytics, and predictive solutions. In this role, he is focused on transforming how companies leverage data to gain new insights about their customers and operations to drive revenue and decrease expenses. He has over 17 years of experience in information technology leading and driving architectural solutions across enterprises. Over his career, he has been part of IT services, business units, and consulting organizations, which provides him with a unique perspective on how to communicate the value of technology to business leaders. He is a local chapter leader for the Houston SQL PASS Organization. You can connect with him on his blog at www. derekewilson. com or www. cdoadvisors. com.

Summary:

Expert tabular modeling techniques for building and deploying cutting-edge business analytical reporting solutions About This Book 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 Who This Book Is For This book is for SQL BI professionals and Architects who want to exploit the full power of the new Tabular models in Analysis Services. Some knowledge of previous versions of Analysis services would be helpful but is not essential. 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 In Detail 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. Style and approach This book takes a pract...

Contents:

Cover

Copyright

Credits

About the Author

About the Reviewer

www.PacktPub.com

Customer Feedback

Table of Contents

Preface

Chapter 1: Introduction to Microsoft Analysis Services Tabular Mode

Introduction

Learning about Microsoft Business Intelligence and SQL Server 2016

Understanding tabular mode

Learning what's new in SQL Server 2016 tabular mode

Modeling

Instance management

Scripting

DAX

Importing sample datasets

Getting ready

How to do it…

How it works…

Understanding basic concepts

Tables

Columns

Measures

Relationships

Hierarchies

Chapter 2: Setting up a Tabular Mode Environment

Installing and configuring a development environment

Installing Visual Studio 2015

Installing SQL Server Data Tools (SSDT)

Interacting with SQL Server Data Tools

Configuring a workspace server

There's more…

Configuring SSAS project properties

Chapter 3: Tabular Model Building

Adding new data to a tabular model

Adding a calculated column

Adding a measure to a tabular model

Changing model views

Renaming columns

Defining a date table

Creating hierarchies

Understanding and building relationships

Getting ready.

How to do it…

Creating and organizing display folders

Deploying your first model

Browsing your model with SQL Server Management Studio

Browsing your model with Microsoft Excel

Chapter 4: Working in Tabular Models

Opening an existing model

Importing data

Modifying model relationships

Modifying model measures

Modifying model columns

Modifying model hierarchies

Creating a calculated table

Creating key performance indicators (KPIs)

Modifying key performance indicators (KPIs)

Deploying a modified model

Chapter 5: Administration of Tabular Models

Managing tabular model properties

[Changing data backup locations]

Changing data backup locations

Changing DirectQuery mode

Changing workspace retention

Changing workspace server

Managing perspectives

Adding a new perspective

Editing a perspective

Renaming a perspective

Deleting a perspective

Copying a perspective

Managing partitions

Creating a Partition

Editing a partition

Processing partitions

Managing roles

Creating Admin role

Creating a Read role

Creating a read and process role.

Creating a process role

Editing roles

Managing server properties

Managing Analysis Services memory

Chapter 6: In-Memory Versus DirectQuery Mode

Understanding query modes

Understanding in-memory mode

Advantages of in-memory

Limitations of in-memory

Understanding DirectQuery mode

Advantages of DirectQuery

Limitations of DirectQuery mode

Creating a new DirectQuery project

Configuring DirectQuery table partitions

Testing DirectQuery mode

Chapter 7: Securing Tabular Models

Configuring static row-level security

Configuring dynamic filter security

Chapter 8: Combining Tabular Models with Excel

Using Analyze in Excel from SSMS

Connecting to Excel from SQL Server Data Tools

Using PivotTables with tabular data

Using Slice, Sort, and Filter

Using the timeline filter with pivot tables

Analyzing data with Power View

Importing data with Power Pivot

Modeling data with Power Pivot

Adding data to Power Pivot

Moving Power Pivot models to the enterprise

Moving Power Pivot to SSAS via Management Studio

Moving Power Pivot to SSAS via SQL Server Data Tools

Chapter 9: DAX Syntax and Calculations.

Introduction

Understanding DAX formulas

Using the AutoSum measure in Visual Studio

Creating calculated measures

Creating calculated columns

Using the IF function

Using the AND function

Using the SWITCH function

Using the CONCATENATE function

Using the LEFT Function

Using the RELATED function

Using the RELATEDTABLE function

Using EVALUATE in DAX queries

Filtering based on a value

Filtering a related table

Using ALL to remove filters

Using ALL to calculate a percentage

Using the SUMMARIZE function

Adding columns to the SUMMARIZE function

Using ROLLUP with the SUMMARIZE function

Chapter 10: Working with Dates and Time Intelligence

Creating a date table in Visual Studio

Using the CALENDAR function

Modifying the date table with the YEAR function

Modifying the date table to include month data

How it works….

There's more…

Using the NOW and TODAY functions

Using the DATEDIFF function

Using the WEEKDAY function

See also…

Using the FIRSTDATE function

Using the PARALLELPERIOD function

Calculating Year over Year Growth

Using the OPENINGBALANCEMONTH function

Using the OPENINGBALANCEYEAR function

Using the CLOSINGBALANCEMONTH function

Using the CLOSINGBALANCEYEAR function

Using the TOTALYTD function

Chapter 11: Using Power BI for Analysis

Getting started with Power BI desktop

Adding data to Power BI reports

Visualizing the crash data with Power BI

Editing visualization properties in Power BI

Adding additional visualizations to Power BI

How it to do it…

Adding a slicer to Power BI

Using analytics in Power BI

Index.

Notes:

Includes index.

Description based on online resource; title from PDF title page (ebrary, viewed March 2, 2017).

ISBN:

9781786461506

1786461501

OCLC:

973022390