Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

by Derek Wilson
Released January 2017
Publisher(s): Packt Publishing
ISBN: 9781786468611

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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.

    Table of contents

    1. Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
      1. Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
      2. Credits
      3. About the Author
      4. About the Reviewer
      5. www.PacktPub.com
        1. Why subscribe?
      6. Customer Feedback
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Sections
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        5. Conventions
        6. Reader feedback
        7. Customer support
          1. Downloading the example code
          2. Downloading the color images of this book 
          3. Errata
          4. Piracy
          5. Questions
      8. 1. Introduction to Microsoft Analysis Services Tabular Mode
        1. Introduction
          1. Learning about Microsoft Business Intelligence and SQL Server 2016
          2. Understanding tabular mode
          3. Learning what's new in SQL Server 2016 tabular mode
            1. Modeling
            2. Instance management
            3. Scripting
            4. DAX
        2. Importing sample datasets
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Understanding basic concepts
          1. Tables
          2. Columns
          3. Measures
          4. Relationships
          5. Hierarchies
      9. 2. Setting up a Tabular Mode Environment
        1. Introduction
        2. Installing and configuring a development environment
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Installing Visual Studio 2015
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Installing SQL Server Data Tools (SSDT)
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Interacting with SQL Server Data Tools
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Configuring a workspace server
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        7. Configuring SSAS project properties
          1. Getting ready
          2. How to do it...
          3. How it works...
      10. 3. Tabular Model Building
        1. Introduction
        2. Adding new data to a tabular model
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Adding a calculated column
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Adding a measure to a tabular model
          1. How to do it...
          2. How it works...
        5. Changing model views
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Renaming columns
          1. How to do it...
          2. How it works...
        7. Defining a date table
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Creating hierarchies
          1. How to do it...
          2. How it works...
        9. Understanding and building relationships
          1. Getting ready
          2. How to do it...
          3. How it works...
        10. Creating and organizing display folders
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Deploying your first model
          1. Getting ready
          2. How to do it...
          3. How it works...
        12. Browsing your model with SQL Server Management Studio
          1. How to do it...
          2. How it works...
        13. Browsing your model with Microsoft Excel
          1. How to do it...
          2. How it works...
      11. 4. Working in Tabular Models
        1. Introduction
        2. Opening an existing model
          1. How to do it...
          2. How it works...
        3. Importing data
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Modifying model relationships
          1. How to do it...
          2. How it works...
        5. Modifying model measures
          1. How to do it...
          2. How it works...
        6. Modifying model columns
          1. How to do it...
          2. How it works...
        7. Modifying model hierarchies
          1. How to do it...
          2. How it works...
          3. There's more...
        8. Creating a calculated table
          1. How to do it...
          2. How it works...
          3. There's more...
        9. Creating key performance indicators (KPIs)
          1. How to do it...
          2. How it works...
        10. Modifying key performance indicators (KPIs)
          1. How to do it...
          2. How it works...
        11. Deploying a modified model
          1. How to do it...
          2. How it works...
          3. There's more...
      12. 5. Administration of Tabular Models
        1. Introduction
        2. Managing tabular model properties
          1. Changing data backup locations
          2. Changing DirectQuery mode
          3. Changing workspace retention
          4. Changing workspace server
        3. Managing perspectives
          1. Getting ready
          2. How to do it...
            1. Adding a new perspective
            2. Editing a perspective
            3. Renaming a perspective
            4. Deleting a perspective
            5. Copying a perspective
        4. Managing partitions
          1. How to do it...
            1. Creating a Partition
            2. Editing a partition
            3. Processing partitions
          2. How it works...
        5. Managing roles
          1. Getting ready
          2. How to do it...
            1. Creating Admin role
            2. Creating a Read role
            3. Creating a read and process role
            4. Creating a process role
            5. Editing roles
          3. There's more...
        6. Managing server properties
          1. How to do it...
        7. Managing Analysis Services memory
          1. How to do it...
          2. How it works...
      13. 6. In-Memory Versus DirectQuery Mode
        1. Introduction
          1. Understanding query modes
          2. Understanding in-memory mode
            1. Advantages of in-memory
            2. Limitations of in-memory
          3. Understanding DirectQuery mode
            1. Advantages of DirectQuery
            2. Limitations of DirectQuery mode
        2. Creating a new DirectQuery project
          1. How to do it...
          2. How it works...
        3. Configuring DirectQuery table partitions
          1. How to do it...
          2. How it works...
        4. Testing DirectQuery mode
          1. How it works...
      14. 7. Securing Tabular Models
        1. Introduction
        2. Configuring static row-level security
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Configuring dynamic filter security
          1. Getting ready
          2. How to do it...
          3. How it works...
      15. 8. Combining Tabular Models with Excel
        1. Introduction
        2. Using Analyze in Excel from SSMS
          1. How to do it...
          2. How it works...
        3. Connecting to Excel from SQL Server Data Tools
          1. How to do it...
          2. How it works...
        4. Using PivotTables with tabular data
          1. Using Slice, Sort, and Filter
          2. How to do it...
          3. How it works...
        5. Using the timeline filter with pivot tables
          1. How to do it...
          2. How it works...
        6. Analyzing data with Power View
          1. How to do it...
          2. How it works...
          3. There's more...
        7. Importing data with Power Pivot
          1. Getting ready
          2. How it works...
        8. Modeling data with Power Pivot
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Adding data to Power Pivot
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Moving Power Pivot models to the enterprise
        10. Moving Power Pivot to SSAS via Management Studio
          1. How to do it...
          2. How it works...
        11. Moving Power Pivot to SSAS via SQL Server Data Tools
          1. How to do it...
          2. How it works...
      16. 9. DAX Syntax and Calculations
        1. Introduction
        2. Understanding DAX formulas
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        3. Using the AutoSum measure in Visual Studio
          1. How to do it...
          2. How it works...
        4. Creating calculated measures
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Creating calculated columns
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Using the IF function
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Using the AND function
          1. How to do it...
        8. Using the SWITCH function
          1. How to do it...
          2. How it works...
          3. There's more...
        9. Using the CONCATENATE function
          1. How to do it...
          2. How it works...
          3. There's more...
        10. Using the LEFT Function
          1. How to do it...
          2. How it works...
          3. There's more...
        11. Using the RELATED function
          1. How to do it...
          2. How it works...
          3. There's more...
        12. Using the RELATEDTABLE function
          1. How to do it...
          2. How it works...
        13. Using EVALUATE in DAX queries
          1. How to do it...
        14. Filtering based on a value
          1. Getting ready
          2. How to do it...
          3. How it works...
        15. Filtering a related table
          1. How to do it...
          2. How it works...
        16. Using ALL to remove filters
          1. How to do it...
          2. How it works...
        17. Using ALL to calculate a percentage
          1. Getting ready
          2. How to do it...
          3. How it works...
        18. Using the SUMMARIZE function
          1. How to do it...
          2. How it works...
        19. Adding columns to the SUMMARIZE function
          1. Getting ready
          2. How to do it...
          3. How it works...
        20. Using ROLLUP with the SUMMARIZE function
          1. How to do it...
          2. How it works...
      17. 10. Working with Dates and Time Intelligence
        1. Introduction
        2. Creating a date table in Visual Studio
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Using the CALENDAR function
          1. How to do it...
          2. How it works...
        4. Modifying the date table with the YEAR function
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Modifying the date table to include month data
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Using the NOW and TODAY functions
          1. How to do it...
          2. How it works...
        7. Using the DATEDIFF function
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        8. Using the WEEKDAY function
          1. How to do it...
          2. How it works...
          3. There's more...
          4. See also...
        9. Using the FIRSTDATE function
          1. How to do it...
          2. How it works...
          3. There's more...
        10. Using the PARALLELPERIOD function
          1. How to do it...
          2. How it works...
          3. There's more...
        11. Calculating Year over Year Growth
          1. How to do it...
          2. How it works...
        12. Using the OPENINGBALANCEMONTH function
          1. How to do it...
          2. How it works...
        13. Using the OPENINGBALANCEYEAR function
          1. How to do it...
          2. How it works...
        14. Using the CLOSINGBALANCEMONTH function
          1. How to do it...
          2. How it works...
        15. Using the CLOSINGBALANCEYEAR function
          1. How to do it...
          2. How it works...
        16. Using the TOTALYTD function
          1. How to do it...
          2. How it works...
      18. 11. Using Power BI for Analysis
        1. Introduction
        2. Getting started with Power BI desktop
          1. How to do it...
          2. How it works...
        3. Adding data to Power BI reports
          1. How to do it...
          2. How it works...
          3. There's more...
        4. Visualizing the crash data with Power BI
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Editing visualization properties in Power BI
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Adding additional visualizations to Power BI
          1. Getting ready
          2. How it to do it...
          3. How it works...
        7. Adding a slicer to Power BI
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Using analytics in Power BI
          1. Getting ready
          2. How to do it...
          3. How it works...

    Product information

    • Title: Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
    • Author(s): Derek Wilson
    • Release date: January 2017
    • Publisher(s): Packt Publishing
    • ISBN: 9781786468611