Book description
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 practical, recipe-based approach where each recipe lists the steps to address or implement a solution. You will be provided with several approaches to creating a business intelligence semantic model using analysis services.
Publisher resources
Table of contents
-
Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
- Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
- Credits
- About the Author
- About the Reviewer
- www.PacktPub.com
- Customer Feedback
- Preface
- 1. Introduction to Microsoft Analysis Services Tabular Mode
- 2. Setting up a Tabular Mode Environment
-
3. Tabular Model Building
- Introduction
- 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
- Creating and organizing display folders
- Deploying your first model
- Browsing your model with SQL Server Management Studio
- Browsing your model with Microsoft Excel
-
4. Working in Tabular Models
- Introduction
- 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
- 5. Administration of Tabular Models
- 6. In-Memory Versus DirectQuery Mode
- 7. Securing Tabular Models
-
8. Combining Tabular Models with Excel
- Introduction
- Using Analyze in Excel from SSMS
- Connecting to Excel from SQL Server Data Tools
- Using PivotTables with tabular data
- 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 to SSAS via Management Studio
- Moving Power Pivot to SSAS via SQL Server Data Tools
-
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
-
10. Working with Dates and Time Intelligence
- Introduction
- 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
- Using the NOW and TODAY functions
- Using the DATEDIFF function
- Using the WEEKDAY function
- 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
- 11. Using Power BI for Analysis
Product information
- Title: Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
- Author(s):
- Release date: January 2017
- Publisher(s): Packt Publishing
- ISBN: 9781786468611
You might also like
book
40 Algorithms Every Programmer Should Know
Learn algorithms for solving classic computer science problems with this concise guide covering everything from fundamental …
book
Hands-On Business Intelligence with DAX
Implement business intelligence (BI), data modeling, and data analytics within Microsoft products such as Power BI, …
book
Windows Server 2019 Cookbook - Second Edition
Efficiently manage and administer enterprise environments using Microsoft Windows Server 2019 Key Features Leverage Windows Server …
book
PySpark Cookbook
Combine the power of Apache Spark and Python to build effective big data applications About This …
