You are previewing Microsoft Tabular Modeling Cookbook.
O'Reilly logo
Microsoft Tabular Modeling Cookbook

Book Description

Over 50 tips and tricks for analytical modeling using Business Intelligence Semantic Models with SQL Server 2012 and PowerPivot

  • Develop tabular models for personal use

  • Learn about the modeling techniques which are required to overcome commonly encountered problems and master advanced modeling scenarios

  • Understand the techniques required to promote personal models to corporate enterprise environments and manage the models in a corporate environment

  • Learn tips and tricks for querying tabular models and learn how to access the data within them for dynamic reporting including the development of interactive workbooks

In Detail

Business Intelligence Semantic Models (BISM) is a technology that is designed to deliver analytical information to users through a variety of mechanisms that include model structure, definition, and design. This book demonstrates how to create BISM models so that information can be presented to users in an intuitive and easy-to-use format. Once the model is defined, we also show you how it can be managed and maintained so that the data in it remains current and secure.

Microsoft Tabular Modeling Cookbook is an all-encompassing guide to developing, managing, creating, and using analytical models using the Business Intelligence Semantic Model (BISM). This title covers a range of modeling situations and common data analysis related problems to show you the techniques required to turn data into information using tabular modeling.

Microsoft Tabular Modeling Cookbook examines three areas of tabular modeling: model development, model management and maintenance, and reporting. This book is a practical guide on how to develop semantic models and turn business data into information. It covers all phases of the model lifecycle from creation to administration and finally reporting. It also shows you how to create models which are designed to analyze data.

All sections of BISM modeling from development to management and finally reporting are covered. The sections on development examine a wide range of techniques and tricks required to build models, including moving data into the model, structuring the model to manipulate the data, and finally the formulas required to answer common business questions; all of these are discussed in this book in detail.

Finally, the book examines methods of reporting on the data within the model, including the creation of data-driven workbooks and reports for a powerful end user experience.

Table of Contents

  1. Microsoft Tabular Modeling Cookbook
    1. Table of Contents
    2. Microsoft Tabular Modeling Cookbook
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Getting Started with Excel
      1. Introduction
      2. Creating the model
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      3. Managing the appearance of tables and fields
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      4. Using tabular relationships to filter data
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      5. Adding fields to tables
        1. Getting ready
        2. How to do it…
        3. How it works…
      6. Linking fields between tables
        1. Getting ready
        2. How to do it…
        3. How it works…
      7. Creating model calculations
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
    9. 2. Importing Data
      1. Introduction
      2. Importing data as text
        1. Getting ready
        2. How to do It…
        3. How it works…
        4. There's more...
      3. Importing data from databases
        1. Getting ready
        2. How to do It…
        3. How it works…
        4. There's more...
      4. Managing connections and tables
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      5. Using data feeds
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
    10. 3. Advanced Browsing Features
      1. Introduction
      2. Sorting data
        1. Getting ready
        2. How to do it…
        3. How it works…
      3. Creating hierarchies for drilldown interaction
        1. Getting ready
        2. How to do It…
        3. How it works…
        4. There's more...
      4. Summing aggregates and row iteration
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      5. Parent-child hierarchies
        1. Getting ready
        2. How to do It…
        3. How it works…
        4. There's more…
      6. Creating and using Key Performance Indicators
        1. Getting ready
        2. How to do It…
        3. How it works…
        4. There's more...
      7. Role playing dimensions and relationships
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      8. Building ratios
        1. Getting ready
        2. How to do It…
        3. How it works…
        4. There's more…
    11. 4. Time Calculations and Date Functions
      1. Introduction
      2. Calculating running totals – totals to date
        1. Getting ready
        2. How to do it...
        3. How it works…
      3. Month, quarter, and year to date aggregations
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      4. 445 dates and irregularities
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Last year and prior period values
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more…
      6. Relative Time – pivoting around measures
        1. Getting ready
        2. How to do it...
        3. How it works…
      7. Moving averages and last n averages
        1. Getting ready
        2. How to do it...
        3. How it works…
        4. There's more…
    12. 5. Applied Modeling
      1. Introduction
      2. Grouping by binning and sorting with ranks
        1. Getting ready
        2. How to do it…
        3. How it works…
          1. Discretizing sales
        4. There's more...
      3. Defining many-to-many relationships
        1. Getting ready
        2. How to do it…
        3. How it works…
      4. Using the last non-empty function for stock data
        1. Getting ready
        2. How to do it…
        3. How it works…
      5. Performing currency calculations
        1. Getting ready
        2. How to do it…
        3. How it works…
      6. Allocating data at different levels
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
    13. 6. Programmatic Access via Excel
      1. Introduction
      2. Connecting pivot tables and Slicers
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      3. Using cube functions
        1. Getting ready
        2. How to do it…
        3. How it works…
      4. Working with worksheet events and VBA
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      5. Managing the Slicer through VBA
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
    14. 7. Enterprise Design and Features
      1. Introduction
      2. Restoring a workbook to Analysis Services
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      3. Importing models into SQL Server Data Tools
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      4. Developing models in SQL Server Data Tools
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      5. Securing data with roles
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      6. Implementing dynamic security
        1. Getting ready
        2. How to do it…
        3. How it works…
      7. Creating perspectives
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
    15. 8. Enterprise Management
      1. Introduction
      2. Deploying models from SSDT
        1. Getting ready
        2. How to do it…
        3. How it works…
      3. Deploying models with the Deployment Wizard
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      4. Creating and managing partitions
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      5. Processing the data
        1. Getting ready
        2. How to do it…
        3. How it works…
      6. DirectQuery and real-time solutions
        1. Getting ready
        2. How to do it…
        3. How it works…
    16. 9. Querying the Tabular Model with DAX
      1. Introduction
      2. Retrieving data from a single table
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      3. Using projection to combine data from different tables
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      4. Restricting data with filters and where conditions
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
      5. Deriving tables and selecting top n records
        1. Getting ready
        2. How to do it…
        3. How it works…
        4. There's more...
    17. 10. Visualizing Data with Power View
      1. Introduction
      2. Creating a Power View report
        1. Getting ready
        2. How to do it…
        3. How it works...
        4. There's more...
      3. Creating and manipulating charts
        1. Getting ready
        2. How to do it…
      4. Using tiles (parameters)
        1. Getting ready
        2. How to do it…
        3. How it works...
        4. There's more...
      5. Using and showing images
        1. Getting ready
        2. How to do it…
        3. There's more...
      6. Automating the table fields with default field sets
        1. Getting ready
        2. How to do it…
      7. Working with table behavior and card control
        1. Getting ready
        2. How to do it…
        3. How it works...
      8. Using maps
        1. Getting ready
        2. How to do it…
        3. How it works...
        4. There's more...
      9. Using multiples (Trellis Charts)
        1. Getting ready
        2. How to do it…
        3. How it works...
        4. There's more...
    18. A. Installing PowerPivot and Sample Databases
      1. Installing PowerPivot
      2. Creating the database
    19. Index