D

data. See also data flow sources; data flow transformations; data sources; splitting data

analyzing available, 101

archiving OLTP, 28–29

cleaning before transferring to UDM, 99

consolidating and cleansing for data marts, 31–32

evaluating requirements for business needs, 107–109

inserting from text file to table or view, 179, 180

latency and, 18, 19, 31

normalizing, 238, 241

read-only OLAP, 53

sources for business intelligence, 26–29

splitting, 208–210, 224–225, 227, 230

transferring with Import/Export Wizard, 252–254

using Excel spreadsheet, 196

working with text file, 197–200

data cleansing, 31–32

Data Conversion transformation, 210, 212

data definitions for reports

about, 557–558

updating report definitions, 654

Data Flow Designer tab, 143, 145, 146

data flow destinations, 240–245

Data Mining Model Training, 240, 470

DataReader, 240

Dimension Processing, 241

Excel, 241

Flat File, 241–242

OLE DB, 242

Partition Processing, 242

Raw File, 242

Recordset, 242

SQL Server, 242–243

SQL Server Mobile, 243

data flow sources, 194–202

DataReader source, 195–196

Excel source, 196

Flat File source, 197–200

OLE DB source, 200–202

Raw File source, 202, 203

XML Source, 202, 204

Data Flow tab

appearance for Load DimBatch data flow task, 281

appearance for Load Inventory Fact data flow task, 286

appearance for Load ManufacturingFact data flow task, 284

data flow tasks, 278–279, 281, 284

Data Flow Toolbox

areas of, 194–195

data flow destinations, 240–245

data flow sources, 194–202

data flow transformations, 202–240

types of items contained in, 154

data flow transformations, 202–240

Aggregate, 203–204, 205

Audit, 204–206

Character Map, 206–208

Conditional Split, 208–210

Copy Column, 210, 211

Data Conversion, 210, 212

Data Mining Query, 210–212, 213

Derived Column, 212, 214

Export Column, 212, 215

Fuzzy Grouping, 213–218, 219

Fuzzy Lookup, 218–219

Import Column, 220

Lookup, 220–222

Merge, 222, 223

Merge Join, 222, 224

modifying data flow segments with, 154–155

Multicast, 223, 225

OLE DB Command, 223–224

Percentage Sampling, 224–225, 227

Pivot, 226

Raw Count, 226–227

Row Sampling, 227, 230

Script Component, 227–230, 231

Slowly Changing Dimension, 230–234

Sort, 235–236

Term Extraction, 236–238

Term Lookup, 238, 239

Union All, 238, 240

Unpivot, 238, 241

uses for, 202

data flows. See also data flow destinations; data flow sources; data flow transformations

about, 144–146

data flow segments, 154–155

data flow tasks, 143

defining tasks in event handler control flow, 147

flow path arrows, 154

flow path arrows connecting tasks, 154

merging, 222, 223, 224, 238, 240

splitting based on percentage, 224–225, 227

splitting into multiple outputs, 208–210

data loads, 30

data marts. See also designing data marts; dimensions

about, 29

adding second fact table to, 275–276

attributes in, 37, 104–105, 111

cleaning data before transferring to UDM, 99

completed Manufacturing schema, 114

connecting to flat files as data source, 281–282

creating database in SQL Server Management Studio, 115–117

data consolidation and cleansing for, 31–32

data load and latency of, 30–31

data warehouses vs., 30

defined, 30, 44

defining OLAP cube from existing database, 286–294

designing, 99–106

dimension and hierarchy design in, 103–104

dimensions and, 32–34

hierarchies in, 37–39

measures and, 32

populating dimensions with Integration Services, 155–162

populating fact tables in, 277–286

providing OLE DB connection for OLTP databases, 99

required for OLAP systems, 53

saving prediction join query tables for, 542

slowly changing dimension of, 230–234

star and snowflake architecture for, 105–106

UDM and situations requiring, 96–99

using Business Intelligence Development Studio to create, 129–138

data mining. See also data mining models; Data Mining Wizard

algorithms for analysis of discretized values, 492

association with, 466–467

classification as benefit of, 463–464

creating testing data set, 522, 523

cube slice for, 485, 494

data column usage in, 485

data preparation for, 470

defined, 16, 458

defining problems for, 469–470

deploying model for, 471

DMX queries in deployment of, 471

examining Microsoft Clustering algorithms, 509–512

filtering test data set, 521

finding detailed data in Microsoft Mining Content Viewer, 504–505

making sense of transactions with, 458–462

Microsoft Association algorithm, 476–477

Microsoft Clustering algorithm, 474–476

Microsoft Decision Trees algorithm, 472–473

Microsoft Naïve Bayes algorithm, 473–474

Microsoft Neural Network algorithm, 480–481

Microsoft Sequence Clustering algorithm, 477–478,517

Microsoft Time Series algorithm, 479–480,517

organizational levels and, 17

probability predictions, 468

raw data views with Microsoft Mining Content Viewer, 499

regression in, 464–465

reviewing Naïve Bayes algorithm, 505–509

segmentation in, 465–466

selecting data columns for, 484

sequence analysis, 467–468

training data mining model, 240, 470

training model, 240

using the Mining Accuracy Chart, 520–521

validation in, 470–471

viewers for algorithms, 498–499

viewing data for Microsoft Association algorithm, 513–517

viewing Decision Trees with Microsoft Tree Viewer, 500–504

Data Mining Expression. See DMX queries

Data Mining Model Training destination, 240, 470

data mining models

creating queries for, 534–544

deploying, 471

prediction join queries for, 537, 539–544, 546

singleton queries for, 534–537, 538, 539

specifying column usage for, 490, 491

training, 240, 470

Data Mining Query task, 180, 181

Data Mining Query transformation, 210–212, 213

Data Mining Wizard

Completing the Wizard page, 493, 495

Select Case Level Columns page, 488, 490

Select Source Cube Dimension page, 488, 489

Select the Case Key page, 488, 489

Select the Data Mining Technique page, 488

Select the Definition Method page, 487–488

Slice Source Cube page, 494

Specify Columns' Content and Data Type page, 490, 491

Specify Mining Model Column Usage page, 490,491

data model generation, 659–660

Data Output tab (Table Properties dialog box), 588

data preparation for data mining, 470

data processing systems of Maximum Miniatures, 21–23

data providers for Report Server, 561–562

data regions

chart, 611–612

list, 597–598

matrix, 587–588

table, 569

types of, 569

data series in pivot charts, 740, 741

Data Source Configuration Wizard

Choose a Data Source Type page, 711, 712

Choose Your Data Connection page, 711, 712

Choose Your Database Objects page, 713, 714

opening and using, 711

Save the Connection String to the Application Configuration File page, 713

data source view

function of, 288–289

language used to define expressions in, 289–290

viewing diagrams in, 289

Data Source View Design tab, 289, 290

data sources

analyzing available data in, 101

authenticating while accessing, 288

business intelligence, 26–29

client connections with AdomdConncction, 675

connecting data marts to flat file, 281–282

creating shared report, 633–636

data source credentials for reports, 650–651

defining for local reports, 711–715

Excel access for pivot tables and charts, 719

as part of Reporting Services data definition, 557, 558

pivot chart, 719, 736

pivot table, 719, 720, 722, 723

selecting for PivotTable and PivotChart Wizard, 720, 721

UDM, 54

Data Sources page (Role Design tab), 405, 406

Data tab (Chart Properties dialog box), 618, 620

data types

converting column, 210

using decimal, 122

data viewers

attaching to data flow, 262–263

illustrated, 263

data warehouses, 30

database images, 603

databases

checking integrity for packages, 163–165

downloading sample book, 278

multiple partitions for, 353–359

partitions storage options, 359–363

rebuilding indexes for, 168–169

recovery mode for nontransactional, 116

removing historical entries from, 166

reorganizing indexes for, 169, 170

sending data flow to SQL Server Mobile, 243

setting permissions for, 404–414

shrinking, 169–170

UDMs and legacy, 98

updating statistics for, 170–171

used by Report Server, 564

data-driven subscriptions, 655–656

data-led discovery, 15

DataReader destination, 240

DataReader source, 195–196

datasets

about, 558

processing dataset records in charts, 611–612

date functions, 251

day-to-day goals, 16, 17

debugging Integration Services packages

setting breakpoints, 260–261

viewing package state, 261–262

decimal data type, 122

decision makers

designing data marts to meet needs of, 100–101

determining who are, 4–5

low-level, 19

mid-level, 18–19

types of decisions made, 16–17

decision making

effective decisions, 5–6

feedback information and, 9–11

goals in, 7–9, 11

keys to effective, 6–11

levels of organization and, 16–19

who participates in, 4–5

Decision Tree tab (Microsoft Tree Viewer), 500–502

default locations of files, 82

default members

creating in tuples, 383–385

setting time dimension as, 390

Define Formula dialog box, 670

Define Relationship dialog box, 312

deleting mining model column mapping, 522, 524, 525

delivery options for subscriptions, 654

Dependency Network tab (Microsoft Tree Viewer), 502–504

Dependency Network viewer

for Microsoft Association algorithm, 515–516

for Microsoft Naïve Bayes algorithm, 505–506

deploying

all project contents or single reports, 631

Analysis Services project, 323

Business Intelligence Development Studio projects, 324–330

data mining model, 471

Integration Services packages, 265–268

Report Model, 663

reports to Report Server, 629–631

Deploying database page (Analysis Services Deployment Wizard), 335

Deployment Progress window, 325

Derived Column transformation, 212, 214

Descendents function, 401–402

descending sort order in Report Manager, 636–637

Design the Table page (Report Wizard), 574

Designer window

about, 73–75

selecting MDI Environment for, 79–80, 81

designing data marts, 99–114

analyzing available data, 101

attributes, 104–105, 111

creating in SQL Server Management Studio, 114–129

dimensions and hierarchies, 103–104

finding decision makers' needs, 100–101

specifying measures in, 102–103, 109–110

star or snowflake schema for, 105–106, 107

detail bands, 569

detail values in cube, 46

Details Grouping dialog box, 585, 586

Details tab (Configure SSIS Logs dialog box), 256

device information parameters

CSV format, 694–695

defined, 693

Excel, 695

HTML formats, 696–697

image formats, 697–698

MHTML, 698

PDF format, 699

XML formats, 699–700

Diagram pane (Query Builder), 201–202

digital dashboard, 342

Dimension Data page (Role Design tab), 408–409, 410–412

Dimension Design tab, 307

Dimension function, 451

dimension members

calculating to return value from previous year, 416

default member in tuples, 383–385

returning current, 397

selecting for Excel pivot tables, 727, 729

specifying shortcuts with, 380–382

time as default, 390

Dimension Processing destination, 241

dimension tables. See also tables

adding to Manufacturing data mart schema, 112

attribute columns in, 38

creating in SQL Server Management Studio, 117–121

identifying, 292

illustrated, 296

options for populating time dimension in, 136

selecting in Slowly Changing Dimension Wizard, 231, 232

storing dimensions in, 35

time-related dimensions and, 113

Dimension Usage tab, 311

dimensions. See also dimension members; dimension tables; Slowly Changing Dimension Wizard

about, 306

changing key attribute, 308–309

combining sets from two, 439–440

created by Cube Wizard, 294

data marts and, 32–34

data mining, 314

defined, 103

defining temporary calculated member of Measures, 437–439

designing for data marts, 103–104, 110–111

dragging and dropping into query, 427

expanding to view attributes and hierarchies, 292

fact, 312

granularity of, 295–297

locating cells in hierarchies, 379–380

managing, 307

many-to-many, 314

measure groups and, 295, 296

parent-child, 313

populating with data marts, 155–162

processing, 323

query dimensions for MDX SELECT statements, 426–430

reference, 314

relating to measure groups, 308–311

removing from pivot table, 328, 329

returning current member of, 397

role playing, 313–314

sending data flow to process, 241

slicer, 430–432, 433

slowly changing, 133, 314–319

storing in dimension tables, 35

supported in MSX SELECT statements, 445

time periods within, 133

time-related, 113

tuples, 378

types of, 311–314

Dimensions page (Role Design tab), 409

DimProduct table, 126

disconnected result sets, 676

DISTINCTCOUNT aggregate function, 445

distributed installations of Reporting Services, 566–567

DMX (Data Mining Expression) queries

about, 180, 181, 544

creating in SQL Server Management Studio, 550–552

DMX language and, 90

FROM clause, 546

ORDER BY clause, 547

PREDICTION JOIN clause, 546

prediction query syntax for, 545–547

SELECT clause for, 545

using in data mining, 471

using on data flow, 210–211

WHERE clause, 547

DMX Query Builder, 599–600

dockable windows, 77

downloading

book's files, 155

sample databases and files, 278

dragging and dropping

cube name to MDX SELECT statements, 425

drag-and-drop programming for Integration Services, 147–148

hierarchies and measures to pivot charts, 738

multiple data flow tasks onto Control Flow Designer tab, 278, 281, 284

drill down data

decision making uses for, 17, 19

enabling for reports, 573, 575, 591

drillthrough actions, 350, 351–352

DTS (Data Transformation Services)

about, 142

executing DTS package for SQL Server 2000, 180–182

migrating current packages from SQL Server 2000, 264–265

running without migrating, 264

DTS Server Windows service, 268