Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX

Name: Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX
Brand: Wrox
Price: 37.99 EUR
Availability: OnlineOnly

Sivakumar Harinath Ronald Pihlgren Denny Guang-Yeu Lee John Sirmon Robert M. Bruckner(Autor*in)

Wrox (Verlag)

Erschienen am 2. Oktober 2012

1176 Seiten

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-118-22343-7 (ISBN)

37,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für PDF mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Personen

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

Inhalt (PDF)

Systemvoraussetzungen

Als PDF speichern Als Link merken