Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX
Erschienen am 2. Oktober 2012
1176 Seiten
978-1-118-22343-7 (ISBN)
Beschreibung
Understand Microsoft's dramatically updated new release of its premier toolset for business intelligence
The first major update to Microsoft's state-of-the-art, complex toolset for business intelligence (BI) in years is now available and what better way to master it than with this detailed book from key members of the product's development team? If you're a database or data warehouse developer, this is the expert resource you need to build full-scale, multi-dimensional, database applications using Microsoft's new SQL Server 2012 Analysis Services and related tools.
Discover how to solve real-world BI problems by leveraging a slew of powerful new Analysis Services features and capabilities. These include the new DAX language, which is a more user-friendly version of MDX; PowerPivot, a new tool for performing simplified analysis of data; BISM, Microsoft's new Business Intelligence Semantic Model; and much more.
* Serves as an authoritative guide to Microsoft's new SQL Server 2012 Analysis Services BI product and is written by key members of the Microsoft Analysis Services product development team
* Covers SQL Server 2012 Analysis Services, a major new release with a host of powerful new features and capabilities
* Topics include using the new DAX language, a simplified, more user-friendly version of MDX; PowerPivot, a new tool for performing simplified analysis of data; BISM, Microsoft's new Business Intelligence Semantic Model; and a new, yet-to-be-named BI reporting tool
* Explores real-world scenarios to help developers build comprehensive solutions
Get thoroughly up to speed on this powerful new BI toolset with the timely and authoritative Professional Microsoft SQL Server 2012 Analysis Services with MDX.
Weitere Details
Weitere Ausgaben
Personen
Sivakumar Harinath is a senior program manager with theSQL Server Analysis Services team.
Ronald Pihlgren is a senior tester with the SQL ServerAnalysis Services team.
Denny Guang-Yeu Lee is a principal program manager withthe SQL Customer Advisory Team.
John Sirmon is a senior program manager with the SQLServer Customer Advisory Team at Microsoft.
Robert M. Bruckner is a principal architect with the SQLServer Reporting Services team.
Inhalt
- Intro
- Professional Microsoft® Sql Server® 2012 Analysis Services with MDX and DAX
- Contents
- Introduction
- Who This Book is For
- What This Book Covers
- How This Book is Structured
- What you Need to USE This Book
- Conventions
- Source Code
- Errata
- P2P.Wrox.Com
- Part I: Introduction
- Chapter 1: Introduction
- BISM Multidimensional Mode
- A Closer Look at Data Warehousing
- Key Elements of a Data Warehouse
- Fact Tables
- Dimension Tables
- Dimensions
- Cubes
- The Star Schema
- The Snowflake Schema
- Inmon Versus Kimball?-?Different Approaches
- Business Intelligence Is Data Analysis
- BISM Tabular Mode
- SQL Server Analysis Services 2012
- The Business Intelligence Semantic Model
- Data Model
- Query Language
- Data Access Technology
- Summary
- Part II Designing Multidimensional BISM
- Chapter 2: A First Look at Multidimensional BISM
- Development, Administrative, and Client Tools
- Upgrading to Analysis Services 2012
- Using SQL Server Data Tools to Build Analysis Services Multidimensional Applications
- Creating a Project Using SQL Server Data Tools
- The Solution Explorer
- The Properties Window
- The Output Window
- Creating an Analysis Services Database Using SQL Server Data Tools
- Creating a Data Source
- Creating a Data Source View (DSV)
- Creating a Cube Using the Cube Wizard
- Deploying and Browsing a Cube
- Using SQL Server Management Studio
- The Object Explorer Pane
- Querying Using the MDX Query Editor
- Summary
- Chapter 3: Understanding MDX
- What Is MDX?
- MDX Concepts
- Measures and Measure Groups
- Hierarchies and Hierarchy Levels
- Members
- Cells
- Tuples
- Sets
- MDX Queries
- SELECT Statement and Axis Specification
- FROM Clause and Cube Specification
- Subselect Clauses
- WHERE Clause and Slicer Specification
- Slicer Dimension
- WITH Clause, Named Sets, and Calculated Members
- Named Sets
- Calculated Members
- Ranking and Sorting
- MDX Expressions
- MDX Operators
- Arithmetic Operators
- Set Operators
- Comparison Operators
- Logical Operators
- Special MDX Operators?-?Curly Braces, Commas, and Colons
- MDX Functions
- MDX Function Categories
- Set Functions
- Crossjoin
- NONEMPTYCROSSJOIN and NONEMPTY
- Filter and Having
- Member Functions
- Numeric Functions
- Dimension Functions, Level Functions, and Hierarchy Functions
- String Manipulation Functions
- Other Functions
- MDX Scripts
- MDX Script Execution
- CALCULATE Statement
- Cube Space
- AUTO EXISTS
- Cell Calculations and Assignments
- Recursion
- Freeze Statement
- Restricting Cube Space/Slicing Cube Data
- SCOPE Statement
- CREATE and DROP SUBCUBE
- Using EXISTS
- Using EXISTING
- Using SUBSELECT
- Parameterized MDX Queries
- MDX Comments
- Summary
- Chapter 4: Data Sources and Data Source Views
- Data Sources
- Data Sources Supported by Analysis Services
- .NET Versus OLE DB Data Providers
- .NET Framework Data Providers
- OLE DB Data Providers
- The Trade-Offs
- Data Source Views
- DSV Wizard
- DSV Designer
- Adding/Removing Tables in a DSV
- Specifying Primary Keys and Relationships in the DSV
- Customizing Your Tables in the DSV Designer
- Data Source Views in Depth
- Diagrams
- Data Source View Properties
- Different Layouts in DSVs
- Validating Your DSV and Initial Data Analysis
- Multiple Data Sources Within a DSV
- Summary
- Chapter 5: Dimension Design
- Working with the Dimension Wizard
- Working with the Dimension Designer
- Attributes
- Attribute Relationships
- User Hierarchies
- Browsing the Dimension
- Sorting Members of a Level
- Optimizing Attributes
- Defining Translations in Dimensions
- Creating a Snowflake Dimension
- Creating a Time Dimension
- Creating a Parent-Child Hierarchy
- Summary
- Chapter 6: Cube Design
- The BISM Multidimensional Mode
- Creating a Cube Using the Cube Wizard
- Browsing Cubes
- Cube Dimensions
- Relationship Types
- No Relationship
- Regular Relationships
- Fact Relationships
- Many-to-Many Relationships
- Data Mining Relationships
- Referenced Relationships
- Browsing Reference Dimensions in Excel
- Measures and Measure Groups
- Calculated Members
- Calculated Measures
- Querying Calculated Measures
- Creating Perspectives
- Creating Translations
- Browsing Perspectives and Translations
- Summary
- Chapter 7: Administration and Management
- Administration Using SQL Server 2012 Tools
- Managing Analysis Servers
- Managing Analysis Services Objects
- Database Creation
- Processing Analysis Services Database Objects
- Processing a Cube
- Processing a Dimension
- Managing Partitions
- Managing Assemblies
- Backup and Restore
- Detach and Attach
- Synchronization
- Managing Security
- Server and Database Roles
- Enabling or Disabling Features
- Online Mode
- Programmatic and Advanced Administration
- Analysis Management Objects (AMO)
- Processing Analysis Services Databases
- Back Up and Restore
- Adding Assemblies to Analysis Services
- PowerShell and Analysis Services
- Resource and Activity Monitoring
- HTTP Connectivity to Analysis Services
- Analysis Services and FailOver Clustering
- Summary
- Chapter 8: Advanced Dimension Design
- Custom Rollups
- Enhancements to Parent-Child Hierarchies
- Unary Operators
- Specifying Names of Levels in a Parent-Child Hierarchy
- Using Properties to Customize Dimensions
- Ordering Dimension Members
- The All Member, Default Member, and Unknown Member
- Error Configurations for Processing
- Storage Mode
- Grouping Members
- Dimension Intelligence Using the Business Intelligence Wizard
- Account Intelligence
- Time Intelligence
- Dimension Intelligence
- Server Time Dimension
- Dimension Writeback
- Summary
- Chapter 9: Advanced Cube Design
- Measure Groups and Measures
- Adding and Enhancing Dimensions
- Fact Dimensions
- Many-to-Many Dimensions
- Data Mining Dimensions
- Role-Playing Dimensions
- Adding Calculations to Your Cube
- Key Performance Indicators (KPIs)
- KPI Creation
- KPIs in Depth
- Using ADOMD.NET to Query KPIs
- Drillthrough
- Actions
- Action Types
- Action Target Types
- URL Action
- Browse URL Action in the Cube Browser
- Report Actions
- Drillthrough Action
- Adding Intelligence to the Cube
- Semiadditive Measures
- Currency Conversion
- Working with Partitions
- Building a Local Partition
- Building a Remote Partition
- Storage Modes and Storage Settings
- Building Aggregations
- The Aggregation Design Process
- Usage-Based Optimization
- Real-Time Cubes
- Long Latency Scenario
- Caching After Data Change
- Caching Using Timed Updates
- Average Latency Scenario
- Caching with MOLAP Storage Option
- No Latency Scenario
- Real-Time ROLAP Storage Option
- Defining Security
- Cell Writeback
- Cell Writeback Prerequisites
- Writeback Statement
- Update Nonleaf Cell Value Using Allocation
- Equal Allocation
- Weighted Allocation
- Incremental Allocation
- Cautions
- AMO Warnings
- Design Experience
- Dismissing Warnings
- Warnings Designer
- Summary
- Chapter 10: Designing Mulitdimensional BISM for Performance
- Optimizing Multidimensional BISM Design
- Fine-Tuning Your Dimensions
- Choosing the Right Key Attribute
- Avoiding Unnecessary Attributes
- Turning Off Optimization for Rarely Used Attributes
- Turning Off AttributeHierarchy for Member Properties
- Defining Relationships between Attributes
- Fine-Tuning Your Cube
- Fact Table ? Measure Groups or Partitions
- Optimizing Reference Dimensions
- Many-to-Many Dimensions
- Partitions
- Merging Partitions
- Partition Slices
- Partition Slices and ROLAP
- A Partition Slice Example
- Distinct Count Partitioning
- Optimizing for Processing
- Creating Partitions to Speed Up Processing
- Choosing Small and Appropriate Data Types and Sizes
- SQL Server and Analysis Services Installations
- Optimizing a Relational Data Source
- Avoiding Excessive Aggregation Design
- Using Incremental Processing When Appropriate
- Parallelism during Processing
- Identifying Resource Bottlenecks
- Designing Aggregations
- Understanding Aggregations
- Creating Aggregations
- Applying Aggregation Design
- Usage-Based Aggregation Design
- Aggregation Design Options
- Designing Efficient Aggregations Using Hints
- Relationships between Attributes
- Properties Controlling Attributes and Aggregation Design
- Managing Aggregation Designs
- Scalability Optimizations
- Configuring Server Configuration Properties
- Scaling Out
- Scaling Up
- Handling Large Dimensions
- Summary
- Chapter 11: Optimizing Query Performance
- How OLAP Enhances Performance
- The Calculation Model
- MDX Scripts
- Scope and Assignments
- Dimension Attribute Calculations
- Session and Query Calculations
- Query Execution Architecture
- Analysis Services Engine Components
- Stages of Query Execution
- Query Evaluation Modes
- Cell-by-Cell Mode
- Subspace Computation
- Performance Analysis and Tuning Tools
- SQL Server Profiler
- Analysis Services Trace Events
- Performance Monitor
- Task Manager
- SQL Server Management Studio
- SQL Server Data Tools
- Server Properties
- Analyzing Query Performance Issues
- Understanding FE and SE Characteristics
- Common Solutions for Slow Queries
- Large Storage Engine Requests
- Several Storage Engine Requests
- Formula Engine-Intensive Query
- Query Optimization Techniques
- Using NON EMPTY on Axes
- Using Non Empty for Filtering and Sorting
- Using SCOPE Versus IIF and CASE
- Auto Exists Versus Properties
- Member Value Versus Properties
- Move Simple Calculations to Data Source View
- Features Versus MDX Scripts
- Scale Out with Read-Only Database
- Writeback Query Performance
- Summary
- Chapter 12: Data Mining, Multidimensional BISM, and Data Mining Add-ins for Office 2010
- The Data Mining Process
- Topic Area Understanding
- Data: Understand It, Configure It
- Choose the Right Algorithm
- Train, Analyze, and Predict
- Real-World Applications
- Fraud Detection
- Increasing Profits in Retail
- Data Mining in the NBA
- Data Mining in Call Centers
- Data Mining Algorithms in SQL Server Analysis Services 2012
- Microsoft Decision Trees
- Microsoft Naïve Bayes
- Microsoft Clustering
- Microsoft Sequence Clustering
- Microsoft Association Rules
- Microsoft Neural Network
- Microsoft Time Series
- Microsoft Linear Regression
- Microsoft Logistic Regression
- Working with Mining Models
- Relational Mining Model
- OLAP Mining Models
- Analyzing the Cube with a Data Mining Dimension
- Using Data Mining with Office 2010
- Table Analytics
- Analyze Key Influencers
- Detect Categories
- Fill from Example
- Forecast
- Highlight Exceptions
- Shopping Basket Analysis
- Scenario Analysis
- Data Mining Tools
- Explore Data Wizard
- Clean Data: Outliers and Re-label Wizards
- Sample Data Wizard
- Classification Model
- Model Accuracy
- Classification Matrix
- Visio Add-in
- The Decision Tree Shape
- The Cluster Shape Wizard
- The Dependency Shape Wizard
- Summary
- Chapter 13: SQL Integration Services and SQL Server Reporting Services with Multidimensional BISM Models
- SQL Server Integration Services
- Creating an Integration Services Project
- Creating Integration Services Packages for Analysis Services Operations
- The Execute DDL Task
- Processing an Analysis Services Object
- Loading Data into an Analysis Services Partition
- Deploying the Integration Services Project
- Integration Services Tasks for Data Mining
- Automating Execution of SSIS Packages
- Monitoring SSIS Package Executions
- SQL Server Reporting Services
- Report Designer, Report Builder
- Report Definition Language
- Report Wizard
- Report Server
- Creating a Report on a Relational Database
- Connecting and Deploying to a Report Server
- Creating a Report on a Multidimensional Model
- Designing Your Analysis Services Report
- Enhancing Your Analysis Services Report
- Enhancing Your Report Using Extended Properties
- Custom Aggregates
- Summary
- Chapter 14: Securing Multidimensional BISM
- Securing Your Source Data
- Securing Your Dimension Data
- A Scenario Using Dimension Security
- The User-Role Approach
- The Access-Role Approach
- The Member Property Approach
- The Security Measure Group Approach
- The External Function Approach
- Securing Your Cube Data
- Scenario Using Cell Security
- Summary
- Part IV: PowerPivot and Tabular BISM
- Chapter 15: Self-Service Business Intelligence and Introduction to PowerPivot
- SQL Server 2012
- Self-Service Business Intelligence
- PowerPivot: Microsoft's Implementation of SSBI
- PowerPivot Applications
- PowerPivot for Excel
- PowerPivot for SharePoint
- The Analysis Services Engine in VertiPaq Mode
- Summary
- Chapter 16: A First Look at Tabular BISM
- Tabular Mode Projects in SSDT
- Setting Up a Tabular Instance of Analysis Services 2012
- Creating a Tabular Project Using SQL Server Data Tools
- Workspace Server and Deployment Server
- Importing Data
- The Tabular Designer
- The Model Menu
- Working with Tables in the Designer
- Working with Columns in the Designer
- Relationships
- Adding Calculations to Your Tabular Model
- Calculated Columns
- Measures
- Browsing the Model
- Modeling and Deploying
- Administering Your Tabular Model Using SSMS
- Summary
- Chapter 17: Enhancing Your Tabular BISM
- Sourcing Data for Your Model
- Refining Your Tabular Model
- Changing the Model
- Adding a New Table
- Modifying an Existing Table
- Hiding and Deleting Tables, Columns, and Measures
- Creating a Date Table
- Creating and Managing Relationships
- Column Operations
- Filtering and Sorting
- Configuring for PowerPivot and Power View
- Enhancing Your Model with Hierarchies
- Creating a Date Hierarchy
- Making Use of Time Intelligence Functions
- Creating a Geography Hierarchy
- Creating a Product Hierarchy by Combining Columns from Different Tables
- Creating Parent/Child Hierarchies
- Enhancing Measures
- Building Explicit Measures with DAX
- Implicit Measures
- Change Measure Properties
- Mimicking the Multidimensional Model
- Building Semi-Additive Measures
- Creating KPIs
- Creating Perspectives
- Creating Partitions
- DirectQuery Partitions
- Processing a Tabular Database
- Creating and Applying Security Roles
- Understanding Security Roles
- Row Filters
- Summary
- Chapter 18: Introduction to DAX
- Sample Data
- DAX Fundamental Concepts
- DAX Syntax
- DAX Data Types
- Calculated Columns and Measures
- Row Context and Filter Context
- DAX Operators and Blank Values
- DAX Function Categories with Examples
- Scalar DAX Functions
- Statistical DAX Functions
- Other DAX Functions
- RELATED and RELATEDTABLE
- LOOKUPVALUE
- EARLIER and EARLIEST
- VALUES and DISTINCT
- FILTER
- CALCULATE, ALL, and ALLSELECTED
- Time Intelligence Functions
- Context Information Functions
- DAX as a Query Language
- DAX and MDX
- Summary
- Chapter 19: Advanced Topics in DAX
- Parent-Child Hierarchies
- Cross-Filtering with Many-to-Many Relationships
- Filtering
- Cross-Filtering
- Cross-Filtering with DAX
- Multiple Relationships Between Tables
- Multiple Instances of the Table
- Multiple Relationships Between Two Tables
- Time-Based Analysis
- Moving Average
- Opening and Closing Balance
- Non-Aggregatable Columns
- Summary
- Chapter 20: Analyzing Multidimensional and Tabular BISMs In Excel
- Analyzing Data in Excel 2010
- Analyzing Data Using Pivot Tables
- Creating a Pivot Table from a Tabular Model Using Analysis Services Data
- Creating a Pivot Table from a Multidimensional Model
- Updating SSAS Connection Information
- Analyzing Data in Pivot Tables
- Filtering in Pivot Tables
- Drilling Down to Detailed Data
- Analyzing Multiple Measures
- Custom Grouping
- Organizing Attributes in the PivotTable Field List
- Number Formatting
- Highlighting Exceptions
- Viewing Member Properties
- Sorting Data
- Filtering Data
- Style and Design
- Excel 2010 Conditional Formatting
- Perspectives and Translations
- Key Performance Indicators
- Named Sets
- Sheet Data Reports
- Cube Functions in Excel 2010
- Pivot Charts
- Local Cubes
- Excel Services
- Summary
- Chapter 21: PowerPivot for SharePoint
- SharePoint 2010
- Excel Services
- Comparing Excel and Excel Services
- Excel Services and PowerPivot
- Key Servers in PowerPivot for SharePoint
- SharePoint Web Front End (WFE)
- SharePoint Application Servers (App Servers)
- SharePoint Content Databases (Content dBs)
- Key Services in PowerPivot for SharePoint
- Analysis Services Engine Service
- PowerPivot Mid-Tier Service
- Connectivity
- Data Refresh
- Services Architecture Workflow Scenarios
- Excel Client Upload to SharePoint
- Excel Services Rendering
- Excel Services Server Action
- What's New
- New Administrative Capabilities
- Setup Improvements
- PowerPivot for SharePoint
- Installation and Configuration
- New Server Installation
- Existing Farm Installation
- Scale Out Implementations
- Scale Out SQL
- Homogeneous Scale Out
- Heterogeneous Scale Out
- Verifying Your PowerPivot for SharePoint Setup
- Publishing Your Excel Workbooks
- Viewing Workbooks in PowerPivot Gallery
- Viewing Workbooks in Excel Services
- Optional Setup Steps
- Configuring File Size Limits
- Turning Off the External Data Warning on Data Refresh
- Integrating Reporting Services
- BI Appliances
- Summary
- Part V: Advanced Topics with Tabular Bism and Integration with Power View
- Chapter 22: Introduction and Configuration of Power View
- Reporting Services Power View
- Power View
- Reporting Services
- Reporting Services Add-in
- Reporting Services Service Application
- PowerPivot for SharePoint
- Installing Reporting Services for SharePoint
- SharePoint Site Settings
- Preparing Model Connection for Power View Tutorial
- Deploying the FAA Flight Data Model
- Creating SharePoint Image Library for FAA Airline Images
- Publishing FAA Workbook Directly to PowerPivot Gallery
- Creating Data Source Connections for Power View
- BI Semantic Model (BISM) Connection File
- Report Data Source (RSDS) Connection
- Configuring Data Source Connections
- Connecting to PowerPivot Workbooks
- Connecting to a BISM Connection File
- Connecting to an RSDS
- Authentication Scenarios
- Comparison and Trade-offs
- Kerberos Delegation with BISM or RSDS
- BISM Connection and RS Service Account
- RSDS and Stored Windows Credentials
- RSDS with Impersonation and EffectiveUser
- Summary
- Resources
- Chapter 23: Visual Analytics with Power View
- Introduction to Power View
- Visual Analytics with Power View
- Getting Started with Power View
- Creating a New Power View Report
- Opening an Existing Power View Report
- Introduction to the Power View Design Experience
- Creating a Table Visualization
- Converting Visualizations
- Sorting Inside Charts
- Expanding Visualizations
- Filtering in Views
- Multiple Views
- Saving Reports
- Permissions for Power View
- Visualizations and Interactivity
- Tile Visualizations
- Highlighting in Visualizations
- Matrix
- Slicers
- Filters
- Card, Callout Views
- Zooming in Charts
- Scatter and Bubble
- Animated Timeline Charts
- Refreshing Data in a Power View Report
- Presenting and Exporting in Power View
- Reading and Presentation Mode
- Printing Views
- PowerPoint Export and Interactivity
- Tips and Tricks for Power View
- Summary
- Resources
- Index
