Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

Home >
Browse >
Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

Authors: Art Tennick

Published: September 2010

eISBN: 9780071748674 0071748679 | ISBN: 9780071748667

Contents

Acknowledgments

Introduction

Chapter 1 Cases Queries

Examining Source Data

Flattened Nested Case Table

Specific Source Columns

Examining Training Data

Examining Specific Cases

Examining Test Cases

Examining Model Cases Only

Examining Another Model

Expanding the Nested Table

Sorting Cases

Model and Structure Columns

Specific Model Columns

Distinct Column Values 1/2

Distinct Column Values 2/2

Cases by Cluster 1/4

Cases by Cluster 2/4

Cases by Cluster 3/4

Cases by Cluster 4/4

Content Query

Decision Tree Cases

Decision Tree Content

Time Series Cases

Sequence Clustering Cases 1/2

Sequence Clustering Cases 2/2

Neural Network and Naïve Bayes Cases

Order By with Top

Sequence Clustering Nodes 1/2

Sequence Clustering Nodes 2/2

Chapter 2 Content Queries

Content Query

Updating Cluster Captions

Content with New Caption

Changing Caption Back

Content Columns

Node Type

Flattened Content

Flattened Content with Subquery

Subquery Columns

Subquery Column Aliases

Subquery Where Clause

Individual Cluster Analysis

Demographic Analysis

Renaming Clusters

Querying Renamed Clusters

Clusters with Predictable Columns

Narrowing Down Content

Flattening Content Again

Some Tidying Up

More Tidying Up

Looking at Bike Buyers

Who Are the Best Customers?

How Did All Customers Do?

Decision Tree Content

Decision Tree Node Types

Decision Tree Content Columns

Flattened Column

Honing the Result

Just the Bike Buyers

Tidying Up

VBA in DMX

Association Content

Market Basket Analysis

Naïve Bayes Content

Naïve Bayes Node Type

Flattening Naïve Bayes Content

Naïve Bayes Content Subquery 1/2

Naïve Bayes Content Subquery 2/2

Chapter 3 Prediction Queries with Decision Trees

Select on Mining Model 1/6

Select on Mining Model 2/6

Select on Mining Model 3/6

Select on Mining Model 4/6

Select on Mining Model 5/6

Select on Mining Model 6/6

Prediction Query

Aliases and Formatting

Natural Prediction Join

More Demographics

Natural Prediction Join Broken

Natural Prediction Join Fixed

Nonmodel Columns

Ranking Probabilities

Predicted Versus Actual

Bike Buyers Only

More Demographics

Choosing Inputs 1/3

Choosing Inputs 2/3

Choosing Inputs 3/3

All Inputs and All Customers

Singletons 1/6

Singletons 2/6

Singletons 3/6

Singletons 4/6

Singletons 5/6

Singletons 6/6

New Customers

New Bike-Buying Customers

A Cosmetic Touch

PredictHistogram() 1/2

PredictHistogram() 2/2

Chapter 4 Prediction Queries with Time Series

Analyzing All Existing Sales

Analyzing Existing Sales by Category

Analyzing Existing Sales by Specific Periods—Lag() 1/3

Analyzing Existing Sales by Specific Periods—Lag() 2/3

Analyzing Existing Sales by Specific Periods—Lag() 3/3

PredictTimeSeries() 1/11

PredictTimeSeries() 2/11

PredictTimeSeries() 3/11

PredictTimeSeries() 4/11

PredictTimeSeries() 5/11

PredictTimeSeries() 6/11

PredictTimeSeries() 7/11

PredictTimeSeries() 8/11

PredictTimeSeries() 9/11

PredictTimeSeries() 10/11

PredictTimeSeries() 11/11

PredictStDev()

What-If 1/3

What-If 2/3

What-If 3/3

Chapter 5 Prediction and Cluster Queries with Clustering

Cluster Membership 1/3

Cluster Membership 2/3

Cluster Membership 3/3

ClusterProbability() 1/2

ClusterProbability() 2/2

Clustering Parameters

Another ClusterProbability

Cluster Content 1/2

Cluster Content 2/2

PredictCaseLikelihood() 1/3

PredictCaseLikelihood() 2/3

PredictCaseLikelihood() 3/3

Anomaly Detection

Cluster with Predictable Column 1/3

Cluster with Predictable Column 2/3

Cluster with Predictable Column 3/3

Clusters and Predictions

Chapter 6 Prediction Queries with Association and Sequence Clustering

Association Content—Item Sets

Association Content—Rules

Important Rules

Twenty Most Important Rules

Particular Product Models

Another Product Model

Nested Table

PredictAssociation()

Cross-Selling Prediction 1/7

Cross-Selling Prediction 2/7

Cross-Selling Prediction 3/7

Cross-Selling Prediction 4/7

Cross-Selling Prediction 5/7

Cross-Selling Prediction 6/7

Cross-Selling Prediction 7/7

Sequence Clustering Prediction 1/3

Sequence Clustering Prediction 2/3

Sequence Clustering Prediction 3/3

Chapter 7 Data Definition Language (DDL) Queries

Creating a Mining Structure

Creating a Mining Model

Training a Mining Model

Structure Cases

Model Cases

Model Content

Model Predict

Specifying Structure Holdout

Specifying Model Parameter

Specifying Model Filter

Specifying Model Drill-through

Training the New Models

Cases—with No Drill-through

Cases—with Drill-through

Structure with Holdout

Specifying Model Parameter, Filter, and Drill-through

Training New Model

Unprocessing a Structure

Model Cases with Filter and Drill-through

Clearing Out Cases

Removing Models

Removing Structures

Renaming a Model

Renaming a Structure

Making Backups

Removing the Backed-up Structure

Restoring a Backup

Structure with Nested Case Table

Model Using Nested Case Table

Model Training with Nested Case Table

Prediction Queries with Nested Cases 1/2

Prediction Queries with Nested Cases 2/2

Cube—Mining Structure

Cube—Mining Model

Cube—Model Training

Cube—Structure Cases

Cube—Model Content

Cube—Model Prediction

Chapter 8 Schema and Column Queries

DMSCHEMA_MINING_SERVICES 1/2

DMSCHEMA_MINING_SERVICES 2/2

DMSCHEMA_MINING_SERVICE_PARAMETERS 1/2

DMSCHEMA_MINING_SERVICE_PARAMETERS 2/2

DMSCHEMA_MINING_MODELS 1/3

DMSCHEMA_MINING_MODELS 2/3

DMSCHEMA_MINING_MODELS 3/3

DMSCHEMA_MINING_COLUMNS 1/3

DMSCHEMA_MINING_COLUMNS 2/3

DMSCHEMA_MINING_COLUMNS 3/3

DMSCHEMA_MINING_MODEL_CONTENT 1/5

DMSCHEMA_MINING_MODEL_CONTENT 2/5

DMSCHEMA_MINING_MODEL_CONTENT 3/5

DMSCHEMA_MINING_MODEL_CONTENT 4/5

DMSCHEMA_MINING_MODEL_CONTENT 5/5

DMSCHEMA_MINING_FUNCTIONS 1/3

DMSCHEMA_MINING_FUNCTIONS 2/3

DMSCHEMA_MINING_FUNCTIONS 3/3

DMSCHEMA_MINING_STRUCTURES 1/2

DMSCHEMA_MINING_STRUCTURES 2/2

DMSCHEMA_MINING_STRUCTURE_COLUMNS 1/3

DMSCHEMA_MINING_STRUCTURE_COLUMNS 2/3

DMSCHEMA_MINING_STRUCTURE_COLUMNS 3/3

DMSCHEMA_MINING_MODEL_XML 1/2

DMSCHEMA_MINING_MODEL_CONTENT_PMML

DMSCHEMA_MINING_MODEL_XML 2/2

Discrete Model Columns 1/5

Discrete Model Columns 2/5

Discrete Model Columns 3/5

Discrete Model Columns 4/5

Discrete Model Columns 5/5

Discretized Model Column

Discretized Model Column—Minimum

Discretized Model Column—Maximum

Discretized Model Column—Mid Value

Discretized Model Column—Range Values

Discretized Model Column—Spread

Continuous Model Column—Spread

Chapter 9 After You Finish

Where to Use DMX

SSRS

SSIS

SQL

XMLA

Winforms and Webforms

Third-Party Software

Copy and Paste

Appendix A: Graphical Content Queries

Content Queries

Graphical Content Queries in SSMS

Clustering Model

Time Series Model

Association Rules Model

Decision Trees Model

Graphical Content Queries in Excel 2007

Data Mining Ribbon

Table Tools/Analyze Ribbon

Graphical Content Queries in BIDS

Opening the Adventure Works Solution

Reverse-Engineering the Adventure Works Database

Adventure Works Database in Connected Mode

Viewing Content

Tracing Generated DMX

Excel Data Mining Functions

Appendix B: Graphical Prediction Queries

Prediction Queries

SSMS Prediction Queries

SSRS Prediction Queries

SSIS Prediction Queries

Control Flow

Data Flow

SSAS Prediction Queries

Building a Prediction Query

Clustering Prediction Queries

Time Series Prediction Queries

Association Prediction Queries

Decision Trees Prediction Queries

Excel Prediction Queries

Excel Data Mining Functions

Appendix C: Graphical DDL Queries

DDL Queries

SSAS in BIDS

Excel 2007/2010

SSIS in BIDS

Index