Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

by Art Tennick
Released September 2010
Publisher(s): McGraw-Hill
ISBN: 9780071748674

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

250+ Ready-to-Use, Powerful DMX Queries

Transform data mining model information into actionable business intelligence using the Data Mining Extensions (DMX) language. Practical DMX Queries for Microsoft SQL Server Analysis Services 2008 contains more than 250 downloadable DMX queries you can use to extract and visualize data. The application, syntax, and results of each query are described in detail. The book emphasizes DMX for use in SSMS against SSAS, but the queries also apply to SSRS, SSIS, DMX in SQL, WinForms, WebForms, and many other applications. Techniques for generating DMX syntax from graphical tools are also demonstrated in this valuable resource.

  • View cases within data mining structures and models using DMX Case queries
  • Examine the content of a data mining model with DMX Content queries
  • Perform DMX Prediction queries based on the Decision Trees algorithm and the Time Series algorithm
  • Run Prediction and Cluster queries based on the Clustering algorithm
  • Execute Prediction queries with Association and Sequence Clustering algorithms
  • Use DMX DDL queries to create, alter, drop, back up, and restore data mining objects
  • Display various parameters for each algorithm with Schema queries
  • Examine the values of discrete, discretized, and continuous structure columns using Column queries
  • Use graphical interfaces to generate Prediction, Content, Cluster, and DDL queries
  • Deliver DMX query results to end users

Download the source code from www.mhprofessional.com/computingdownload

Table of contents

  1. Cover Page
  2. Practical DMX Queries for Microsoft® SQL Server® Analysis Services 2008
  3. Copyright Page
  4. Contents
  5. Acknowledgments
  6. Introduction
  7. Chapter 1 Cases Queries
    1. Examining Source Data
    2. Flattened Nested Case Table
    3. Specific Source Columns
    4. Examining Training Data
    5. Examining Specific Cases
    6. Examining Test Cases
    7. Examining Model Cases Only
    8. Examining Another Model
    9. Expanding the Nested Table
    10. Sorting Cases
    11. Model and Structure Columns
    12. Specific Model Columns
    13. Distinct Column Values 1/2
    14. Distinct Column Values 2/2
    15. Cases by Cluster 1/4
    16. Cases by Cluster 2/4
    17. Cases by Cluster 3/4
    18. Cases by Cluster 4/4
    19. Content Query
    20. Decision Tree Cases
    21. Decision Tree Content
    22. Time Series Cases
    23. Sequence Clustering Cases 1/2
    24. Sequence Clustering Cases 2/2
    25. Neural Network and Naïve Bayes Cases
    26. Order By with Top
    27. Sequence Clustering Nodes 1/2
    28. Sequence Clustering Nodes 2/2
  8. Chapter 2 Content Queries
    1. Content Query
    2. Updating Cluster Captions
    3. Content with New Caption
    4. Changing Caption Back
    5. Content Columns
    6. Node Type
    7. Flattened Content
    8. Flattened Content with Subquery
    9. Subquery Columns
    10. Subquery Column Aliases
    11. Subquery Where Clause
    12. Individual Cluster Analysis
    13. Demographic Analysis
    14. Renaming Clusters
    15. Querying Renamed Clusters
    16. Clusters with Predictable Columns
    17. Narrowing Down Content
    18. Flattening Content Again
    19. Some Tidying Up
    20. More Tidying Up
    21. Looking at Bike Buyers
    22. Who Are the Best Customers?
    23. How Did All Customers Do?
    24. Decision Tree Content
    25. Decision Tree Node Types
    26. Decision Tree Content Columns
    27. Flattened Column
    28. Honing the Result
    29. Just the Bike Buyers
    30. Tidying Up
    31. VBA in DMX
    32. Association Content
    33. Market Basket Analysis
    34. Naïve Bayes Content
    35. Naïve Bayes Node Type
    36. Flattening Naïve Bayes Content
    37. Naïve Bayes Content Subquery 1/2
    38. Naïve Bayes Content Subquery 2/2
  9. Chapter 3 Prediction Queries with Decision Trees
    1. Select on Mining Model 1/6
    2. Select on Mining Model 2/6
    3. Select on Mining Model 3/6
    4. Select on Mining Model 4/6
    5. Select on Mining Model 5/6
    6. Select on Mining Model 6/6
    7. Prediction Query
    8. Aliases and Formatting
    9. Natural Prediction Join
    10. More Demographics
    11. Natural Prediction Join Broken
    12. Natural Prediction Join Fixed
    13. Nonmodel Columns
    14. Ranking Probabilities
    15. Predicted Versus Actual
    16. Bike Buyers Only
    17. More Demographics
    18. Choosing Inputs 1/3
    19. Choosing Inputs 2/3
    20. Choosing Inputs 3/3
    21. All Inputs and All Customers
    22. Singletons 1/6
    23. Singletons 2/6
    24. Singletons 3/6
    25. Singletons 4/6
    26. Singletons 5/6
    27. Singletons 6/6
    28. New Customers
    29. New Bike-Buying Customers
    30. A Cosmetic Touch
    31. PredictHistogram() 1/2
    32. PredictHistogram() 2/2
  10. Chapter 4 Prediction Queries with Time Series
    1. Analyzing All Existing Sales
    2. Analyzing Existing Sales by Category
    3. Analyzing Existing Sales by Specific Periods—Lag() 1/3
    4. Analyzing Existing Sales by Specific Periods—Lag() 2/3
    5. Analyzing Existing Sales by Specific Periods—Lag() 3/3
    6. PredictTimeSeries() 1/11
    7. PredictTimeSeries() 2/11
    8. PredictTimeSeries() 3/11
    9. PredictTimeSeries() 4/11
    10. PredictTimeSeries() 5/11
    11. PredictTimeSeries() 6/11
    12. PredictTimeSeries() 7/11
    13. PredictTimeSeries() 8/11
    14. PredictTimeSeries() 9/11
    15. PredictTimeSeries() 10/11
    16. PredictTimeSeries() 11/11
    17. PredictStDev()
    18. What-If 1/3
    19. What-If 2/3
    20. What-If 3/3
  11. Chapter 5 Prediction and Cluster Queries with Clustering
    1. Cluster Membership 1/3
    2. Cluster Membership 2/3
    3. Cluster Membership 3/3
    4. ClusterProbability() 1/2
    5. ClusterProbability() 2/2
    6. Clustering Parameters
    7. Another ClusterProbability
    8. Cluster Content 1/2
    9. Cluster Content 2/2
    10. PredictCaseLikelihood() 1/3
    11. PredictCaseLikelihood() 2/3
    12. PredictCaseLikelihood() 3/3
    13. Anomaly Detection
    14. Cluster with Predictable Column 1/3
    15. Cluster with Predictable Column 2/3
    16. Cluster with Predictable Column 3/3
    17. Clusters and Predictions
  12. Chapter 6 Prediction Queries with Association and Sequence Clustering
    1. Association Content—Item Sets
    2. Association Content—Rules
    3. Important Rules
    4. Twenty Most Important Rules
    5. Particular Product Models
    6. Another Product Model
    7. Nested Table
    8. PredictAssociation()
    9. Cross-Selling Prediction 1/7
    10. Cross-Selling Prediction 2/7
    11. Cross-Selling Prediction 3/7
    12. Cross-Selling Prediction 4/7
    13. Cross-Selling Prediction 5/7
    14. Cross-Selling Prediction 6/7
    15. Cross-Selling Prediction 7/7
    16. Sequence Clustering Prediction 1/3
    17. Sequence Clustering Prediction 2/3
    18. Sequence Clustering Prediction 3/3
  13. Chapter 7 Data Definition Language (DDL) Queries
    1. Creating a Mining Structure
    2. Creating a Mining Model
    3. Training a Mining Model
    4. Structure Cases
    5. Model Cases
    6. Model Content
    7. Model Predict
    8. Specifying Structure Holdout
    9. Specifying Model Parameter
    10. Specifying Model Filter
    11. Specifying Model Drill-through
    12. Training the New Models
    13. Cases—with No Drill-through
    14. Cases—with Drill-through
    15. Structure with Holdout
    16. Specifying Model Parameter, Filter, and Drill-through
    17. Training New Model
    18. Unprocessing a Structure
    19. Model Cases with Filter and Drill-through
    20. Clearing Out Cases
    21. Removing Models
    22. Removing Structures
    23. Renaming a Model
    24. Renaming a Structure
    25. Making Backups
    26. Removing the Backed-up Structure
    27. Restoring a Backup
    28. Structure with Nested Case Table
    29. Model Using Nested Case Table
    30. Model Training with Nested Case Table
    31. Prediction Queries with Nested Cases 1/2
    32. Prediction Queries with Nested Cases 2/2
    33. Cube—Mining Structure
    34. Cube—Mining Model
    35. Cube—Model Training
    36. Cube—Structure Cases
    37. Cube—Model Content
    38. Cube—Model Prediction
  14. Chapter 8 Schema and Column Queries
    1. DMSCHEMA_MINING_SERVICES 1/2
    2. DMSCHEMA_MINING_SERVICES 2/2
    3. DMSCHEMA_MINING_SERVICE_PARAMETERS 1/2
    4. DMSCHEMA_MINING_SERVICE_PARAMETERS 2/2
    5. DMSCHEMA_MINING_MODELS 1/3
    6. DMSCHEMA_MINING_MODELS 2/3
    7. DMSCHEMA_MINING_MODELS 3/3
    8. DMSCHEMA_MINING_COLUMNS 1/3
    9. DMSCHEMA_MINING_COLUMNS 2/3
    10. DMSCHEMA_MINING_COLUMNS 3/3
    11. DMSCHEMA_MINING_MODEL_CONTENT 1/5
    12. DMSCHEMA_MINING_MODEL_CONTENT 2/5
    13. DMSCHEMA_MINING_MODEL_CONTENT 3/5
    14. DMSCHEMA_MINING_MODEL_CONTENT 4/5
    15. DMSCHEMA_MINING_MODEL_CONTENT 5/5
    16. DMSCHEMA_MINING_FUNCTIONS 1/3
    17. DMSCHEMA_MINING_FUNCTIONS 2/3
    18. DMSCHEMA_MINING_FUNCTIONS 3/3
    19. DMSCHEMA_MINING_STRUCTURES 1/2
    20. DMSCHEMA_MINING_STRUCTURES 2/2
    21. DMSCHEMA_MINING_STRUCTURE_COLUMNS 1/3
    22. DMSCHEMA_MINING_STRUCTURE_COLUMNS 2/3
    23. DMSCHEMA_MINING_STRUCTURE_COLUMNS 3/3
    24. DMSCHEMA_MINING_MODEL_XML 1/2
    25. DMSCHEMA_MINING_MODEL_CONTENT_PMML
    26. DMSCHEMA_MINING_MODEL_XML 2/2
    27. Discrete Model Columns 1/5
    28. Discrete Model Columns 2/5
    29. Discrete Model Columns 3/5
    30. Discrete Model Columns 4/5
    31. Discrete Model Columns 5/5
    32. Discretized Model Column
    33. Discretized Model Column—Minimum
    34. Discretized Model Column—Maximum
    35. Discretized Model Column—Mid Value
    36. Discretized Model Column—Range Values
    37. Discretized Model Column—Spread
    38. Continuous Model Column—Spread
  15. Chapter 9 After You Finish
    1. Where to Use DMX
      1. SSRS
      2. SSIS
      3. SQL
      4. XMLA
      5. Winforms and Webforms
      6. Third-Party Software
      7. Copy and Paste
  16. Appendix A Graphical Content Queries
    1. Content Queries
    2. Graphical Content Queries in SSMS
      1. Clustering Model
      2. Time Series Model
      3. Association Rules Model
      4. Decision Trees Model
    3. Graphical Content Queries in Excel 2007
      1. Data Mining Ribbon
      2. Table Tools/Analyze Ribbon
    4. Graphical Content Queries in BIDS
      1. Opening the Adventure Works Solution
      2. Reverse-Engineering the Adventure Works Database
      3. Adventure Works Database in Connected Mode
      4. Viewing Content
    5. Tracing Generated DMX
    6. Excel Data Mining Functions
  17. Appendix B Graphical Prediction Queries
    1. Prediction Queries
    2. SSMS Prediction Queries
    3. SSRS Prediction Queries
    4. SSIS Prediction Queries
      1. Control Flow
      2. Data Flow
    5. SSAS Prediction Queries
    6. Building a Prediction Query
      1. Clustering Prediction Queries
      2. Time Series Prediction Queries
      3. Association Prediction Queries
      4. Decision Trees Prediction Queries
    7. Excel Prediction Queries
    8. Excel Data Mining Functions
  18. Appendix C Graphical DDL Queries
    1. DDL Queries
    2. SSAS in BIDS
    3. Excel 2007/2010
    4. SSIS in BIDS
  19. Index

Product information

  • Title: Practical DMX Queries for Microsoft SQL Server Analysis Services 2008
  • Author(s): Art Tennick
  • Release date: September 2010
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071748674