book

Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

Name: Practical DMX Queries for Microsoft SQL Server Analysis Services 2008
Author: Art Tennick
ISBN: 9780071748674

by Art Tennick

September 2010

Intermediate to advanced

304 pages

6h 2m

English

McGraw-Hill

Read now

Unlock full access

Cover Page
Practical DMX Queries for Microsoft® SQL Server® Analysis Services 2008
Copyright Page
Contents
Acknowledgments
Introduction
Chapter 1 Cases Queries
Examining Source DataFlattened Nested Case TableSpecific Source ColumnsExamining Training DataExamining Specific CasesExamining Test CasesExamining Model Cases OnlyExamining Another ModelExpanding the Nested TableSorting CasesModel and Structure ColumnsSpecific Model ColumnsDistinct Column Values 1/2Distinct Column Values 2/2Cases by Cluster 1/4Cases by Cluster 2/4Cases by Cluster 3/4Cases by Cluster 4/4Content QueryDecision Tree CasesDecision Tree ContentTime Series CasesSequence Clustering Cases 1/2Sequence Clustering Cases 2/2Neural Network and Naïve Bayes CasesOrder By with TopSequence Clustering Nodes 1/2Sequence Clustering Nodes 2/2
Chapter 2 Content Queries
Content QueryUpdating Cluster CaptionsContent with New CaptionChanging Caption BackContent ColumnsNode TypeFlattened ContentFlattened Content with SubquerySubquery ColumnsSubquery Column AliasesSubquery Where ClauseIndividual Cluster AnalysisDemographic AnalysisRenaming ClustersQuerying Renamed ClustersClusters with Predictable ColumnsNarrowing Down ContentFlattening Content AgainSome Tidying UpMore Tidying UpLooking at Bike BuyersWho Are the Best Customers?How Did All Customers Do?Decision Tree ContentDecision Tree Node TypesDecision Tree Content ColumnsFlattened ColumnHoning the ResultJust the Bike BuyersTidying UpVBA in DMXAssociation ContentMarket Basket AnalysisNaïve Bayes ContentNaïve Bayes Node TypeFlattening Naïve Bayes ContentNaïve Bayes Content Subquery 1/2Naïve Bayes Content Subquery 2/2
Chapter 3 Prediction Queries with Decision Trees
Select on Mining Model 1/6Select on Mining Model 2/6Select on Mining Model 3/6Select on Mining Model 4/6Select on Mining Model 5/6Select on Mining Model 6/6Prediction QueryAliases and FormattingNatural Prediction JoinMore DemographicsNatural Prediction Join BrokenNatural Prediction Join FixedNonmodel ColumnsRanking ProbabilitiesPredicted Versus ActualBike Buyers OnlyMore DemographicsChoosing Inputs 1/3Choosing Inputs 2/3Choosing Inputs 3/3All Inputs and All CustomersSingletons 1/6Singletons 2/6Singletons 3/6Singletons 4/6Singletons 5/6Singletons 6/6New CustomersNew Bike-Buying CustomersA Cosmetic TouchPredictHistogram() 1/2PredictHistogram() 2/2
Chapter 4 Prediction Queries with Time Series
Analyzing All Existing SalesAnalyzing Existing Sales by CategoryAnalyzing Existing Sales by Specific Periods—Lag() 1/3Analyzing Existing Sales by Specific Periods—Lag() 2/3Analyzing Existing Sales by Specific Periods—Lag() 3/3PredictTimeSeries() 1/11PredictTimeSeries() 2/11PredictTimeSeries() 3/11PredictTimeSeries() 4/11PredictTimeSeries() 5/11PredictTimeSeries() 6/11PredictTimeSeries() 7/11PredictTimeSeries() 8/11PredictTimeSeries() 9/11PredictTimeSeries() 10/11PredictTimeSeries() 11/11PredictStDev()What-If 1/3What-If 2/3What-If 3/3

Chapter 5 Prediction and Cluster Queries with Clustering
Cluster Membership 1/3Cluster Membership 2/3Cluster Membership 3/3ClusterProbability() 1/2ClusterProbability() 2/2Clustering ParametersAnother ClusterProbabilityCluster Content 1/2Cluster Content 2/2PredictCaseLikelihood() 1/3PredictCaseLikelihood() 2/3PredictCaseLikelihood() 3/3Anomaly DetectionCluster with Predictable Column 1/3Cluster with Predictable Column 2/3Cluster with Predictable Column 3/3Clusters and Predictions
Chapter 6 Prediction Queries with Association and Sequence Clustering
Association Content—Item SetsAssociation Content—RulesImportant RulesTwenty Most Important RulesParticular Product ModelsAnother Product ModelNested TablePredictAssociation()Cross-Selling Prediction 1/7Cross-Selling Prediction 2/7Cross-Selling Prediction 3/7Cross-Selling Prediction 4/7Cross-Selling Prediction 5/7Cross-Selling Prediction 6/7Cross-Selling Prediction 7/7Sequence Clustering Prediction 1/3Sequence Clustering Prediction 2/3Sequence Clustering Prediction 3/3
Chapter 7 Data Definition Language (DDL) Queries
Creating a Mining StructureCreating a Mining ModelTraining a Mining ModelStructure CasesModel CasesModel ContentModel PredictSpecifying Structure HoldoutSpecifying Model ParameterSpecifying Model FilterSpecifying Model Drill-throughTraining the New ModelsCases—with No Drill-throughCases—with Drill-throughStructure with HoldoutSpecifying Model Parameter, Filter, and Drill-throughTraining New ModelUnprocessing a StructureModel Cases with Filter and Drill-throughClearing Out CasesRemoving ModelsRemoving StructuresRenaming a ModelRenaming a StructureMaking BackupsRemoving the Backed-up StructureRestoring a BackupStructure with Nested Case TableModel Using Nested Case TableModel Training with Nested Case TablePrediction Queries with Nested Cases 1/2Prediction Queries with Nested Cases 2/2Cube—Mining StructureCube—Mining ModelCube—Model TrainingCube—Structure CasesCube—Model ContentCube—Model Prediction
Chapter 8 Schema and Column Queries
DMSCHEMA_MINING_SERVICES 1/2DMSCHEMA_MINING_SERVICES 2/2DMSCHEMA_MINING_SERVICE_PARAMETERS 1/2DMSCHEMA_MINING_SERVICE_PARAMETERS 2/2DMSCHEMA_MINING_MODELS 1/3DMSCHEMA_MINING_MODELS 2/3DMSCHEMA_MINING_MODELS 3/3DMSCHEMA_MINING_COLUMNS 1/3DMSCHEMA_MINING_COLUMNS 2/3DMSCHEMA_MINING_COLUMNS 3/3DMSCHEMA_MINING_MODEL_CONTENT 1/5DMSCHEMA_MINING_MODEL_CONTENT 2/5DMSCHEMA_MINING_MODEL_CONTENT 3/5DMSCHEMA_MINING_MODEL_CONTENT 4/5DMSCHEMA_MINING_MODEL_CONTENT 5/5DMSCHEMA_MINING_FUNCTIONS 1/3DMSCHEMA_MINING_FUNCTIONS 2/3DMSCHEMA_MINING_FUNCTIONS 3/3DMSCHEMA_MINING_STRUCTURES 1/2DMSCHEMA_MINING_STRUCTURES 2/2DMSCHEMA_MINING_STRUCTURE_COLUMNS 1/3DMSCHEMA_MINING_STRUCTURE_COLUMNS 2/3DMSCHEMA_MINING_STRUCTURE_COLUMNS 3/3DMSCHEMA_MINING_MODEL_XML 1/2DMSCHEMA_MINING_MODEL_CONTENT_PMMLDMSCHEMA_MINING_MODEL_XML 2/2Discrete Model Columns 1/5Discrete Model Columns 2/5Discrete Model Columns 3/5Discrete Model Columns 4/5Discrete Model Columns 5/5Discretized Model ColumnDiscretized Model Column—MinimumDiscretized Model Column—MaximumDiscretized Model Column—Mid ValueDiscretized Model Column—Range ValuesDiscretized Model Column—SpreadContinuous Model Column—Spread
Chapter 9 After You Finish
Where to Use DMXSSRSSSISSQLXMLAWinforms and WebformsThird-Party SoftwareCopy and Paste
Appendix A Graphical Content Queries
Content QueriesGraphical Content Queries in SSMSClustering ModelTime Series ModelAssociation Rules ModelDecision Trees ModelGraphical Content Queries in Excel 2007Data Mining RibbonTable Tools/Analyze RibbonGraphical Content Queries in BIDSOpening the Adventure Works SolutionReverse-Engineering the Adventure Works DatabaseAdventure Works Database in Connected ModeViewing ContentTracing Generated DMXExcel Data Mining Functions
Appendix B Graphical Prediction Queries
Prediction QueriesSSMS Prediction QueriesSSRS Prediction QueriesSSIS Prediction QueriesControl FlowData FlowSSAS Prediction QueriesBuilding a Prediction QueryClustering Prediction QueriesTime Series Prediction QueriesAssociation Prediction QueriesDecision Trees Prediction QueriesExcel Prediction QueriesExcel Data Mining Functions
Appendix C Graphical DDL Queries
DDL QueriesSSAS in BIDSExcel 2007/2010SSIS in BIDS
Index

Content preview from Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

Chapter 9After You Finish

Where to Use DMX

Throughout this book, you’ve been using SSMS to write your DMX queries and display the results. It’s unlikely that your users will have SSMS—indeed, it’s not recommended for end users as it’s simply too powerful and potentially dangerous. This chapter presents some alternative software and methods for getting DMX query results to the end user.

SSRS

SSRS can generate simple DMX prediction queries for you, but you may want some of the more sophisticated queries (for example, content and cases queries) that you’ve seen in this book. You will need an SSAS connection to do this. To use your own DMX, click the Command Type DMX, then the Design Mode button on the toolbar, while in Query Designer in SSRS. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft SQL Server 2008 Reporting Services, 3rd Edition

Publisher Resources

ISBN: 9780071748667

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Practical DMX Queries for Microsoft SQL Server Analysis Services 2008

by Art Tennick