Microsoft® SQL Server® 2012 Analysis Services: The BISM Tabular Model

Microsoft® SQL Server® 2012 Analysis Services: The BISM Tabular Model

by Alberto Ferrari Marco Russo and Chris Webb
Released July 2012
Publisher(s): Microsoft Press
ISBN: 9780735670099

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

Build agile and responsive Business Intelligence solutions

Analyze tabular data using the BI Semantic Model (BISM) in Microsoft SQL Server 2012 Analysis Services—and discover a simpler method for creating corporate-level BI solutions. Led by three BI experts, you’ll learn how to build, deploy, and query a BISM tabular model with step-by-step guides, examples, and best practices. This hands-on book shows you how the tabular model’s in-memory database enables you to perform rapid analytics—whether you’re a professional BI developer new to Analysis Services or familiar with its multidimensional model.

Discover how to:

  • Determine when a tabular or multidimensional model is right for your project

  • Build a tabular model using SQL Server Data Tools in Microsoft Visual Studio 2010

  • Integrate data from multiple sources into a single, coherent view of company information

  • Use the Data Analysis eXpressions (DAX) language to create calculated columns, measures, and queries

  • Choose a data modeling technique that meets your organization’s performance and usability requirements

  • Optimize your data model for better performance with xVelocity storage engine

  • Manage complex data relationships, such as multicolumn, banding, and many-to-many

  • Implement security by establishing administrative and data user roles

    •

    Table of contents

    1. Microsoft® SQL Server® 2012 Analysis Services: The BISM Tabular Model
    2. Dedication
    3. Foreword
    4. Introduction
      1. Who Should Read This Book
        1. Assumptions
      2. Who Should Not Read This Book
      3. Organization of This Book
      4. Conventions and Features in This Book
      5. System Requirements
      6. Code Samples
        1. Installing the Code Samples
      7. Acknowledgments
      8. Errata & Book Support
      9. We Want to Hear from You
      10. Stay in Touch
    5. 1. Introducing the Tabular Model
      1. The Microsoft BI Ecosystem
        1. What Is Analysis Services and Why Should I Use It?
        2. A Short History of Analysis Services
        3. The Microsoft BI Stack Today
        4. Self-Service BI and Corporate BI
      2. Analysis Services 2012 Architecture: One Product, Two Models
        1. The Tabular Model
        2. The Multidimensional Model
        3. Why Have Two Models?
        4. The Future of Analysis Services
      3. Choosing the Right Model for Your Project
        1. Licensing
        2. Upgrading from Previous Versions of Analysis Services
        3. Ease of Use
        4. Compatibility with PowerPivot
        5. Query Performance Characteristics
        6. Processing Performance Characteristics
        7. Hardware Considerations
        8. Real-Time BI
        9. Client Tools
        10. Feature Comparison
      4. Summary
    6. 2. Getting Started with the Tabular Model
      1. Setting Up a Development Environment
        1. Components of a Development Environment
          1. Development Workstation
          2. Development Server
          3. Workspace Database Server
        2. Licensing
        3. Installation Process
          1. Development Workstation Installation
          2. Development Server Installation
          3. Workspace Database Server Installation
      2. Working with SQL Server Data Tools
        1. Creating a New Project
        2. Configuring a New Project
          1. Default Properties Wizard
          2. Project Properties
          3. Model Properties
          4. Options Dialog Box
        3. Importing from PowerPivot
        4. Importing a Deployed Project from Analysis Services
        5. Contents of a Tabular Project
      3. Building a Simple Tabular Model
        1. Loading Data into Tables
          1. Creating Measures
          2. Creating Calculated Columns
        2. Working in the Diagram View
          1. Creating Relationships
          2. Creating Hierarchies
        3. Deployment
      4. Querying a Tabular Model in Excel
        1. Connecting to a Tabular Model
          1. Browsing a Workspace Database
          2. Connecting to a Deployed Database
          3. Using PivotTables
          4. Using Slicers
          5. Sorting and Filtering Rows and Columns
          6. Using Excel Cube Formulas
      5. Querying a Tabular Model in Power View
        1. Creating a Connection to a Tabular Model
        2. Building a Basic Power View Report
        3. Adding Charts and Slicers
        4. Interacting with a Report
      6. Working with SQL Server Management Studio
      7. Summary
    7. 3. Loading Data Inside Tabular
      1. Understanding Data Sources
      2. Understanding Impersonation
      3. Understanding Server-Side and Client-Side Credentials
      4. Working with Big Tables
      5. Loading from SQL Server
        1. Loading from a List of Tables
        2. Loading Relationships
        3. Loading from a SQL Query
        4. Loading from Views
      6. Opening Existing Connections
      7. Loading from Access
      8. Loading from Analysis Services
        1. Using the MDX Editor
        2. Loading from a Tabular Database
      9. Loading from an Excel File
      10. Loading from a Text File
      11. Loading from the Clipboard
      12. Loading from a Reporting Services Report
        1. Loading Reports by Using Data Feeds
      13. Loading from a Data Feed
      14. Loading from SharePoint
      15. Loading from the Windows Azure DataMarket
      16. Choosing the Right Data-Loading Method
      17. Understanding Why Sorting Data Is Important
      18. Summary
    8. 4. DAX Basics
      1. Understanding Calculation in DAX
        1. DAX Syntax
        2. DAX Data Types
        3. DAX Operators
        4. DAX Values
      2. Understanding Calculated Columns and Measures
      3. Calculated Columns
      4. Measures
        1. Editing Measures by Using DAX Editor
        2. Choosing Between Calculated Columns and Measures
      5. Handling Errors in DAX Expressions
        1. Conversion Errors
        2. Arithmetical Operation Errors
        3. Empty or Missing Values
        4. Intercepting Errors
      6. Common DAX Functions
        1. Aggregate Functions
        2. Logical Functions
        3. Information Functions
        4. Mathematical Functions
        5. Text Functions
        6. Conversion Functions
        7. Date and Time Functions
        8. Relational Functions
      7. Using Basic DAX Functions
      8. Summary
    9. 5. Understanding Evaluation Context
      1. Evaluation Context in a Single Table
        1. Filter Context in a Single Table
        2. Row Context in a Single Table
        3. Working with Evaluation Context for a Single Table
      2. Understanding the EARLIER Function
      3. Understanding Evaluation Context in Multiple Tables
        1. Row Context with Multiple Tables
        2. Understanding Row Context and Chained Relationships
        3. Using Filter Context with Multiple Tables
        4. Understanding Row and Filter Context Interactions
          1. Using Row Context and CALCULATE
          2. Understanding Row Context and Inactive Relationships
        5. Modifying Filter Context for Multiple Tables
        6. Final Considerations for Evaluation Context
      4. Summary
    10. 6. Querying Tabular
      1. Tools for Querying Tabular
      2. DAX Query Syntax
      3. Using CALCULATETABLE and FILTER
      4. Using ADDCOLUMNS
      5. Using SUMMARIZE
      6. Using CROSSJOIN, GENERATE, and GENERATEALL
      7. Using ROW
      8. Using CONTAINS
      9. Using LOOKUPVALUE
      10. Defining Measures Inside a Query
        1. Test Your Measures with a Query
      11. Parameters in DAX Query
        1. Using DAX Query in SQL Server Reporting Services
      12. Querying by Using MDX
        1. Using DAX Local Measures in MDX Queries
        2. Drillthrough in MDX Queries
      13. Choosing Between DAX and MDX
      14. Summary
    11. 7. DAX Advanced
      1. Understanding CALCULATE and CALCULATETABLE Functions
        1. Evaluation Context in DAX Queries
        2. Modifying Filter Context by Using CALCULATETABLE
        3. Using FILTER in CALCULATE and CALCULATETABLE Arguments
        4. Recap of CALCULATE and CALCULATETABLE Behavior
      2. Control Filters and Selections
        1. Using ALLSELECTED for Visual Totals
        2. Filters and Cross Filters
          1. Using ISFILTERED and ISCROSSFILTERED
          2. Using FILTERS and VALUES
          3. FILTERS and ALLSELECTED
          4. Using HASONEVALUE and HASONEFILTER
        3. Maintaining Complex Filters by Using KEEPFILTERS
      3. Sorting Functions
        1. Using TOPN
        2. Using RANKX
        3. Using RANK.EQ
      4. Statistical Functions
        1. Standard Deviation and Variance by Using STDEV and VAR
        2. Sampling by Using the SAMPLE Function
      5. Summary
    12. 8. Understanding Time Intelligence in DAX
      1. Tabular Modeling with Date Table
        1. Creating a Date Table
          1. Generating Dates in a SQL Query or View
          2. Creating a Date Table in Excel and Importing It in the Tabular Model
          3. Importing a Date Table from DateStream on DataMarket
        2. Defining Relationship with Date Tables
          1. Separating Time from Date
          2. Managing Date Granularity
        3. Duplicating the Date Table
        4. Setting Metadata for a Date Table
      2. Time Intelligence Functions in DAX
        1. Aggregating and Comparing over Time
          1. Year-to-Date, Quarter-to-Date, and Month-to-Date
          2. Periods from the Prior Year
          3. Difference over Previous Year
          4. Browsing Data by Using a Period Table
          5. Querying Time Intelligence–Based Measures
        2. Semiadditive Measures
          1. ClosingBalance and OpenBalance
          2. Updating Balances by Using Transactions
      3. Summary
    13. 9. Understanding xVelocity and DirectQuery
      1. Tabular Model Architecture in Analysis Services 2012
      2. In-Memory Mode and xVelocity
        1. Query Execution in In-Memory Mode
        2. Row-Oriented vs. Column-Oriented Databases
        3. xVelocity (VertiPaq) Storage
        4. Memory Usage in xVelocity (VertiPaq)
          1. Data Memory Usage
          2. Processing Memory Usage
          3. Querying Memory Usage
        5. Optimizing Performance by Reducing Memory Usage
          1. Reducing Dictionary Size
          2. Choosing Data Type
        6. Understanding Processing Options
          1. What Happens During Processing
          2. Available Processing Options
      3. Using DirectQuery and Hybrid Modes
        1. DirectQuery Mode
        2. Analyzing DirectQuery Mode Events by Using SQL Profiler
        3. DirectQuery Settings
        4. Development by Using DirectQuery
      4. Summary
    14. 10. Building Hierarchies
      1. Basic Hierarchies
        1. What Are Hierarchies?
        2. When to Build Hierarchies
        3. Building Hierarchies
        4. Hierarchy Design Best Practices
        5. Hierarchies Spanning Multiple Tables
      2. Parent/Child Hierarchies
        1. What Are Parent/Child Hierarchies?
        2. Configuring Parent/Child Hierarchies
          1. Building a Basic Parent/Child Hierarchy
          2. Handling Empty Items
        3. Unary Operators
          1. How Unary Operators Work
          2. Implementing Unary Operators by Using DAX
      3. Summary
    15. 11. Data Modeling in Tabular
      1. Understanding Different Data Modeling Techniques
        1. Using the OLTP Database
      2. Working with Dimensional Models
        1. Working with Slowly Changing Dimensions
        2. Working with Degenerate Dimensions
        3. Using Snapshot Fact Tables
      3. Computing Weighted Aggregations
      4. Understanding Circular Dependencies
      5. Understanding the Power of Calculated Columns: ABC Analysis
      6. Modeling with DirectQuery Enabled
      7. Using Views to Decouple from the Database
      8. Summary
    16. 12. Using Advanced Tabular Relationships
      1. Using Multicolumn Relationships
      2. Banding in Tabular
      3. Using Many-to-Many Relationships
      4. Implementing Basket Analysis
      5. Querying Data Models with Advanced Relationships
      6. Implementing Currency Conversion
      7. Summary
    17. 13. The Tabular Presentation Layer
      1. Naming, Sorting, and Formatting
        1. Naming Objects
        2. Hiding Columns
        3. Organizing Measures
        4. Sorting Column Data
        5. Formatting
          1. Formatting Columns
          2. Formatting Measures
          3. Language and Collation
      2. Perspectives
      3. Power View–Related Properties
        1. Default Field Set
        2. Table Behavior Properties
      4. Drillthrough
      5. KPIs
      6. Summary
    18. 14. Tabular and PowerPivot
      1. PowerPivot for Microsoft Excel 2010
        1. Using the PowerPivot Field List
          1. Using Implicit Measures
          2. Creating Measures
        2. Understanding Linked Tables
      2. PowerPivot for Microsoft SharePoint
      3. Using the Right Tool for the Job
      4. Prototyping in PowerPivot, Deploying with Tabular
      5. Summary
    19. 15. Security
      1. Roles
        1. Creating Database Roles
        2. Membership of Multiple Roles
      2. Administrative Security
        1. The Server Administrator Role
        2. Database Roles and Administrative Permissions
      3. Data Security
        1. Basic Data Security
        2. Testing Data Security
          1. Testing Roles by Using Excel
          2. Testing Roles by Using Connection String Properties
          3. Testing Roles by Impersonating Users
        3. Advanced Row Filter Expressions
          1. Filtering on Multiple Columns
          2. Filtering and Table Relationships
          3. Filtering and Calculated Columns
          4. Using a Permissions Table
      4. Dynamic Security
        1. DAX Functions for Dynamic Security
        2. Implementing Dynamic Security by Using CUSTOMDATA
        3. Implementing Dynamic Security by Using USERNAME
      5. Advanced Authentication Scenarios
        1. Connecting to Analysis Services from Outside a Domain
        2. Kerberos and the Double-Hop Problem
      6. Monitoring Security
      7. Summary
    20. 16. Interfacing with Tabular
      1. Understanding Different Tabular Interfaces
      2. Understanding Tabular vs. Multidimensional Conversion
      3. Using AMO from .NET
      4. Writing a Complete AMO Application
        1. Creating Data Source Views
        2. Creating a Cube
        3. Loading a SQL Server Table
        4. Creating a Measure
        5. Creating a Calculated Column
        6. Creating Relationships
        7. Drawing Some Conclusions
      5. Performing Common Operations in AMO with .NET
        1. Processing an Object
        2. Working with Partitions
      6. Using AMO with PowerShell
      7. Using XMLA Commands
      8. CSDL Extensions
      9. Summary
    21. 17. Tabular Deployment
      1. Sizing the Server Correctly
        1. xVelocity Requirements
          1. Disk and I/O
          2. Memory
          3. CPU
          4. Fault Tolerance
          5. Load Balancing
        2. DirectQuery Requirements
      2. Automating Deployment to a Production Server
      3. Table Partitioning
        1. Defining a Partitioning Strategy
        2. Defining Partitions for a Table in a Tabular Model
        3. Managing Partitions for a Table
      4. Processing Options
        1. Available Processing Options
          1. Process Add
          2. Process Clear
          3. Process Data
          4. Process Default
          5. Process Defrag
          6. Process Full
          7. Process Recalc
        2. Defining a Processing Strategy
          1. Transactions
          2. Process Full of a Database
          3. Process Full of Selected Partitions and Tables
          4. Process Data or Process Default of Selected Partitions and Tables
          5. Process Add of Selected Partitions
          6. Choosing the Right Processing Strategy
        3. Executing Processing
          1. Process Database
          2. Process Table
          3. Process Partition
      5. Processing Automation
        1. Using XMLA
          1. Executing from the Command Line (ASCMD)
          2. Executing from SQL Server Agent
          3. Executing from SQL Server Integration Services (SSIS)
        2. Using AMO
        3. Using PowerShell
        4. Using SSIS
      6. DirectQuery Deployment
        1. Define a DirectQuery Partitioning Strategy
        2. Implementing Partitions for DirectQuery and Hybrid Modes
        3. Security and Impersonation with DirectQuery
      7. Summary
    22. 18. Optimizations and Monitoring
      1. Finding the Analysis Services Process
      2. Understanding Memory Configuration
      3. Using Memory-Related Performance Counters
      4. Understanding Query Plans
        1. Understanding SUMX
        2. Gathering Time Information from the Profiler
      5. Common Optimization Techniques
        1. Currency Conversion
        2. Applying Filters in the Right Place
        3. Using Relationships Whenever Possible
      6. Monitoring MDX Queries
      7. Monitoring DirectQuery
      8. Gathering Information by Using Dynamic Management Views
      9. Summary
    23. A. DAX Functions Reference
      1. Statistical Functions
      2. Table Transformation Functions
      3. Logical Functions
      4. Information Functions
      5. Mathematical Functions
      6. Text Functions
      7. Date and Time Functions
      8. Filter and Value Functions
      9. Time Intelligence Functions
    24. Index
    25. About the Authors
    26. Copyright

    Product information

    • Title: Microsoft® SQL Server® 2012 Analysis Services: The BISM Tabular Model
    • Author(s): Alberto Ferrari Marco Russo and Chris Webb
    • Release date: July 2012
    • Publisher(s): Microsoft Press
    • ISBN: 9780735670099