Index
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X
A
action discovery, 185
Action property, 279, 288–289
actions
database roles, 432–433
defining, 184
drillthrough, 185, 187–189
implementing, 187–189
regular types, 184–185
report, 285–286
SSIS packages, 432, 434
Actions tab (Cube Designer), 145
Active X Script Task (SSIS), 84
ActiveX scripting, 65, 84
Administrators role (SSAS), 452
ADO.NET, 80
Aggregate transformation, 93
AggregateFunction property, 145, 174
Aggregation Design Wizard, 222–225, 228–230
Aggregation Manager, 222
aggregations
Aggregation Design Wizard support, 223–225,
228–230
applying to data regions, 302
backing up, 466
defined, 221
designing partition, 228–232
migrating cubes and, 56
monitoring usage, 231–232
overview, 221–222
processing SSAS objects, 387
setting options, 224–225
storage models, 209–210
Usage-Based Optimization Wizard support, 225–227,
230–231
AggregationUsage property, 223–224
AllowAdHocOpenRowsetQueries property, 510
AllowConfigurationChanges property, 348
allowed set, 457
AllowedBrowsingFolders property, 467
AllowedProvidersInOpenRowset property, 510
AllowSessionMiningModels property, 509
ALTER MINING STRUCTURE statement (DMX), 500
AMO (Analysis Management Objects), 359, 369
ampersand (&), 193, 313
Analysis Management Objects (AMO), 359, 369
Analysis Services. See SSAS (SQL Server Analysis
Services)
Analysis Services Processing Task (SSIS), 84, 374
Anonymous access, 35
ART (Auto-Regression Test), 246
Associated Measure Group property, 180
association model (DM), 254, 256–257
Association Rules algorithm (DM), 246–247, 262,
492–494, 510
AttributeHierarchyEnabled property, 146, 153
AttributeHierarchyVisible property, 146, 153
attributes
Analysis Services changes, 51
defined, 50
dimension properties, 153, 155–156, 456
in cubes, 50
mapping data mining structures, 253–254
report model, 326
securing, 454, 457
Audit transformation, 92
Authentication Mode configuration setting, 9
Autoexists behavior, 457
AutoIncrement string literal, 421
Auto-Regression Test (ART), 246
AverageOfChildren function, 173
AVG aggregate function, 302
axes, query, 194
B
BackColor property, 320–321
Backup Database dialog box, 466–467
BackupDir property (SSAS), 44, 467
backups, scheduling, 466–472
Basic Authentication mode, 35
BIDS (Business Intelligence Development Studio)
Aggregation Design Wizard, 222–225, 228–230
AMO support, 357
creating packages, 71
creating SSIS projects, 75–77
Cube Designer, 139, 144–146, 148–149, 160–161
database roles, 454, 461
deploying SSAS objects, 359–362
description, 4
Dimension Designer, 152, 158–160
Dimension Wizard, 150–155
implementing source control, 470
object support, 75
partition support, 206, 211
prediction queries, 498–499
processing SSAS objects, 373–378
query parameters, 310
Relational Report Models, 324–325
Reports Project Wizard, 269–270
SQL Server 2005 support, 4
SSIS package support, 342, 430
SSRS support, 4, 397–400, 402–403
Usage-Based Optimization Wizard, 222, 225–227,
230–231
viewing KPIs, 180
viewing package status, 109–110
Visual Studio support, 4
BIDS Solution Explorer, 454–455
binding
changing partition, 206–207
datasets to parameters, 312
query, 206–207
table, 206
breakpoints (debugging), 122–124
Browser role (SSRS), 522
built-in function expressions, 320
BuiltinAdminsAreServerAdmins property, 452
Bulk Insert Task (SSIS), 84
Business Intelligence Development Studio. See BIDS
(Business Intelligence Development Studio)
business scorecard, 179
C
CacheMode property, 391
caching. See proactive caching
calculated members, 194–196, 198–199
Calculations tab (Cube Designer), 145, 195
CanGrow property, 279
CanShrink property, 279
cases
defined, 237
in dating mining, 239, 244, 246
nested tables and, 254–255
Cell Data tab (Solution Explorer), 456
cell security, 459–460, 463–464
Character Map transformation, 92
Chart data region (SSRS), 297, 299
charts
hiding in reports, 285
in reports, 278, 280, 282–283
lift, 488–490
profit, 488–490
CheckpointFileName property, 105
checkpoints, 66, 103–107
CheckpointUsage property, 105
Children function (MDX), 194–196
choose function, 319
circular references, 342
classification matrix, 491–492
ClearCache command (XMLA), 482
CLUSTER_COUNT parameter (DM), 262
clustering
Analysis Services, 14, 16–18
in data mining, 246, 264–265
Clustering algorithm (DM), 246, 262, 494–495, 501, 510
code element, 320–321
Collation Settings screen, 9
Color property, 276–277, 319–320
column charts, 119
columns
hiding in reports, 284–288
unhiding in reports, 288
Command events, 475
COMPLEXITY_PENALTY parameter (DM), 262
Conditional Split transformation, 92, 116
conditions
control flow expressions and, 319–320
Evaluation operation properties, 112–113
Logical operation properties, 113
Configure Error Output window, 117
Configured Value property, 341
Connection Manager, 79–81
connection string property (SSAS), 453
connections. See package connections
ConnectionString property, 540
constraints
creating, 113–115
defined, 83, 122
Evaluation operation properties, 112–113
in control flow, 109–113
Logical condition properties, 113
containers
defined, 83
DisableEventHandlers property, 122
event handler support, 122
overview, 85–87
SSAS objects as, 373
Content Manager (SSRS), 522, 535–536
continuous variables
data mining algorithms, 245–246
measuring values, 239
verifying randomness, 240
control flow
debugging, 122–124
error handling with event handlers, 120–122
overview, 83–88
precedence constraints in, 109–113
transaction support, 104
control flow containers. See containers
control flow expressions, 319–320
control flow objects
connecting with precedence, 110–113
defining, 83–88
TransactionOption property, 103
viewing package status, 109–110
Copy Column transformation, 92
COUNT aggregate function, 170, 302
CREATE MEMBER statement (MDX), 196
CREATE MINING MODEL statement (DMX), 500
CREATE MINING STRUCTURE statement (DMX), 500
CreateDeploymentUtility property, 348–349
CreateQueryLogTable property, 226
credentials
managing data source, 539–542
running scheduled reports, 407
SSAS support, 510
CSV rendering extension (SSRS), 271, 284
Cube Designer (BIDS)
associating dimensions with measure groups, 160–161
Count aggregate function, 170
creating cubes, 139
modifying cubes, 144–146, 148–149
testing database role security, 461
cube dimension, 50, 146
Cube Structure tab (Cube Designer)
AggregateUsage property, 223
dimension properties, 146
functionality, 145
measure groups, 169
query logging, 227
Cube Wizard (SSAS), 139–144
cubes. See also SSAS cubes
OLAP, 238, 244, 261
UDM, 261
virtual, 51
Cubes tab (Solution Explorer), 455, 460
Current Time Member property, 180
CurrentMember function (MDX), 194
D
Data Conversion transformation, 92
Data Definition Language (DDL), 500
data flow
control flow constraints and, 111
creating, 95–98
defined, 83
error handling, 116–122, 124–126
identifying issues with data viewers, 118–120
transaction support, 104
viewing package status, 109–110
Data Flow (SSIS), 60, 66
data flow destination adapters. See destination adapters
data flow source adapters. See source adapters
Data Flow Task (SSIS), 84
data flow transformations. See transformations
data latency, 212, 534
Data Manipulation Language (DML), 500
data mining (DM)
algorithm parameters, 262–264
algorithms and usage, 245–247
case definitions, 254–255
control flow tasks, 84
data preparation, 238–243
defined, 237
nested tables, 254–255
OLAP cubes, 238, 244, 261
prediction clause, 253–254
project lifecycle, 237–238
SSAS support, 192
techniques, 237
text mining, 246
typical business questions, 237
UDM cubes, 261
understanding processing, 391–392
Data Mining Designer, 250, 261, 498
Data Mining Extensions. See DMX (Data Mining
Extensions)
Data Mining Model Training destination adapter, 90
data mining models
association, 254, 256–257
creating, 263
creating queries and reports, 498–506
data sources, 244
defined, 237, 391
dimensions in, 261, 265
forecasting, 255, 258–259
securing, 508–517
sequence, 255, 257–258
SSAS permissions, 454, 511
SSAS processing support, 372, 391–393
testing accuracy, 488–497
Data Mining Query Task (SSIS), 84
Data Mining Query transformation, 94
data mining structures
defined, 391
mapping attributes, 253–254
SSAS permissions, 454, 511
SSAS processing support, 372–373, 391–393
Data Mining Viewers, 250–252, 259
Data Mining Wizard, 249
data paths, 116
Data Protection API (DPAPI), 431
Data Pump (DTS), 60, 65
Data Reader source adapter, 89–90
data regions
applying aggregates, 302
assigning datasets, 297
creating, 297, 302–305
defined, 280, 297
filtering data, 300–301
grouping data, 299
nesting, 299
sorting data, 301–302
Data Source designer, 132
data source view. See DSV (data source view)
Data Source View Wizard, 133
Data Source Wizard, 131
data sources
creating datasets from, 290–293
creating for report model, 324–325
data mining models, 244
data-driven subscriptions and, 417
defining for SSAS objects, 131–133, 136–137
defining for SSIS projects, 78–82
managing credentials, 539–542
private, 290
query parameters, 309
restricting access to, 397
shared, 290, 539–540
SSAS permissions, 454, 510–511
SSRS item-level roles, 521–522
Data Sources tab (Solution Explorer), 455
Data Transformation Services. See DTS (Data
Transformation Services)
Data Viewer configuration window, 119
data viewers
Data Mining Viewers, 250–252, 259
identifying data flow issues, 118–120
data warehouses, 169
database dimension, 146
Database Engine
authentication mode settings, 9
collation settings, 9
failover instances, 16
instance name and, 7
SQL Server 2005 component, 1–2
SSMS support, 3
Database Engine collation settings, 9
database roles
assigning, 432–435
creating, 454–455, 461–463
overview, 453
setting, 39–41
SSAS support, 510–511
testing security, 461
databases. See also msdb system database
backing up, 471–472
migrating, 74, 468
Oracle, 324, 417
Report Model Wizard support, 325–326
repository, 27
restoring, 467–468
SSAS support, 372, 454
SSMS support, 470
DataDir property (SSAS), 44
DataReader destination adapter, 90
Dataset action, 184
dataset parameters
BIDS support, 310
configuring, 310–311
creating, 316
mapping report parameters, 309
datasets
assigning to data regions, 297
binding to parameters, 312
creating from data sources, 290–296
filtering data, 300–301
grouping data, 299, 301
nesting data regions, 299
query parameters, 309–311
report parameters, 316–317
sorting data, 301–302
DataType property, 174
date formatting, 277–278
db_dtsadmin role, 434
db_dtsltduser role, 434–435
db_dtsoperator role, 434
DDL (Data Definition Language), 500
Debug configuration, 398
debugging
control flow, 122–124
data viewer support, 119
viewing package status, 110
DebugLocal configuration, 398
Decision Trees algorithm (DM), 245, 262, 499, 501, 510
defining dimension relationships process, 158
DELETE statement (DMX), 500
denied set, 457
deploying
SSAS objects, 359–366
SSIS packages, 346–354
SSRS on Internet, 36
SSRS reports, 397–406
Deployment Wizard, 359, 362–366, 369–370
DeploymentOutputPath property, 348
Derived Column transformation, 92, 94–95, 116
destination adapters
creating, 90, 97
data flow tasks and, 89
error path support, 116
detail view (Model Designer), 326
digital signatures, 435
Dimension Data tab (Solution Explorer), 456–459
Dimension Designer (BIDS), 152, 158–160, 186
Dimension Processing destination adapter, 90
Dimension Usage tab (Cube Designer), 145, 160, 170
Dimension Wizard (BIDS), 150–155
dimensions
assigning to cubes, 153–154, 156–157
associating to measure groups, 158
creating, 150–152, 154–155
Cube Wizard support, 139–144
database, 146
defined, 50
in cubes, 50, 146
in data mining models, 261, 265
migrating, 56
modifying attributes, 153, 155–156
properties supported, 146
scripting definitions, 470
SSAS changes, 51
SSAS processing support, 372–373
SSAS security, 454, 456–459
top-level security, 56
translating, 186, 189–190
tuples and, 193
user hierarchies and, 158–162
Dimensions tab (Solution Explorer), 455
directed approach (data mining), 237
DisableEventHandlers property, 122
disaster recovery plans, 466–472
Discover events, 475, 477
Discover Server State events, 475
discrete variables, 238, 245
DisplayFolder property, 145, 159, 174, 180
Distinct Count aggregation function, 169, 172
Distinct Count measure, 169, 172, 177–178
Distributed Transaction Coordinator. See DTC
(Distributed Transaction Coordinator)
DM. See data mining (DM)
DML (Data Manipulation Language), 500
DMX (Data Mining Extensions)
DDL statements, 500–501
prediction queries, 498–499, 502–506
query designer, 291
SSRS support, 290
DontSaveSensitive protection level, 431, 435
DPAPI (Data Protection API), 431
drillthrough actions
defined, 185
implementing, 187–189
setting security, 460, 464–465
DROP MINING MODEL statement (DMX), 500
DROP MINING STRUCTURE statement (DMX), 500
DSV (data source view)
creating, 133, 137
creating for report model, 324–325
creating keys and table relationships, 133–135
Cube Wizard support, 139
data mining models, 244, 247–248
fact tables, 169
selecting, 325
DTC (Distributed Transaction Coordinator), 103–104
DTExec tool, 432, 438, 441–443
DTExecUI tool, 438–443, 445
DTS (Data Transformation Services)
control flow tasks, 84
executing and managing, 61–62
migrating to SSIS, 62–64, 67–68
post-migration steps, 64–66
SSIS differences, 60–61
DTS Migration Wizard, 61
dtsx package extension (SSIS), 64, 334, 346, 349, 431
DTUtil utility
SSIS package deployment, 346, 349, 351–353
SSIS package security, 435
dynamic property (DTS), 64
E
control flow tasks, 83, 85
data mining algorithms, 247
package connections and, 78
SSRS support, 29, 412
embedded functions, 321
Enable Trace Stop Time property, 474
Enabled property, 146
EncryptAllWithPassword protection level, 431–432, 435
EncryptAllWithUserKey protection level, 431, 435
encryption
DTUtil support, 435
for backup files, 467
SSIS packages, 429, 432, 435–437
encryption keys
RSCM support, 28–29
rskeymgmt.exe support, 31
SSRS support, 36–38
EncryptionLevel property, 435
EncryptSensitiveWithPassword protection level,
431–432, 435
EncryptSensitiveWithUserKey protection level, 430–431,
435
entities, 326
environment variable configuration type (SSIS), 336,
338, 341–342
equal sign (=), 313
error handling. See also troubleshooting
data flow, 116–122, 124–126
for measure groups, 171
for tasks, 109–115
processing SSAS objects, 377
error logging, 43–44
Error Output property, 118
error paths
adding, 124–126
defined, 116
handling data flow errors, 116–118
SSIS support, 66
ErrorCode system variable, 122
ErrorConfiguration property, 171
ErrorDescription system variable, 122
EstimatedCount property, 224
EstimatedRows property, 171, 224
ETL process, 60, 212
Evaluation operation properties, 112–113
event handlers, 66, 120–122
events
Command, 475
Discover, 475, 477
Discover Server State, 475
Notification, 475
Progress, 475
Query, 475, 478
Security Audit, 475
SSRS security, 528
tracing, 474–477
Excel rendering extension (SSRS), 271, 284, 533
Excel source adapter, 89
Execute Command Task (SSIS), 84
Execute DDL Task (SSIS), 84, 366
Execute DTS 2000 Package Task, 62, 65, 84
Execute Package Task (SSIS), 85
Execute SQL Task (SSIS), 85
ExecutionStartTime variable, 92
Exists function (MDX), 458
Export Column transformation, 92
EXPORT statement (DMX), 500
exporting data, 72–75
expressions, report properties, 318–322
F
fact tables, 169–170, 206, 458
failover instances, 16
FailPackageOnFailure property, 105
feature packs, reviewing, 16
field expressions, 319
File System Task (SSIS), 85
File Transfer Protocol. See FTP (File Transfer Protocol)
FILTER clause, 309
filtering data
data-driven subscriptions, 417
in data regions, 300–301
query parameters, 309, 311
report models, 326
Flat File source adapter, 89, 118
flight recorder (SSAS), 43, 478–480
font properties, 276
FontFamily property, 276
FontSize property, 276
FontStyle property, 276
FontWeight property, 276
footers in reports, 278, 281–282
For Loop Container (SSIS), 86
ForEach Loop Container (SSIS), 86–87
forecasting model (DM), 255, 258–259
FormatString property, 145, 174, 196
FROM clause (MDX), 194
FTP (File Transfer Protocol), 78, 83, 85
FTP Task (SSIS), 85
Full control role (SSAS), 41
Fuzzy Grouping transformation, 94
Fuzzy Lookup transformation, 94
G
Goal property (KPI), 179
graphical objects in reports, 278, 280
GROUP BY statement, 136
grouping data, 299, 301
H
headers in reports, 278
Hidden property, 285
HIDDEN_NODE_RATIO parameter (DM), 262
hierarchies
creating, 162
defined, 50
dimension relationships and, 158–162
migrating, 56
modifying, 163
navigating, 194–195
histograms, 119
HISTORICAL_MODEL_COUNT parameter (DM), 495
HISTORICAL_MODEL_GAP parameter (DM), 495
HOLAP (Hybrid Online Analytical Processing), 171,
210–211, 214
HTML color strings, 276
HTML rendering extension (SSRS), 271, 284, 533
HTTP (Hypertext Transfer Protocol), 313
HTTP format, 533
Hybrid Online Analytical Processing (HOLAP), 171,
210–211, 214
hyperlinks, 279, 288–289
Hypertext Transfer Protocol (HTTP), 313
I
ID property (SSAS), 453
IgnoreUnrelatedDimensions property, 170–171
IIF function, 319
IIS (Internet Information Server), 4, 26–28
Image rendering extension (SSRS), 271, 284
Image Wizard, 279–280
images in reports, 278–280, 285
Import and Export Wizard (SQL Server), 72–75
Import Column transformation, 92
IMPORT statement (DMX), 500
importing data, 72–75
inheritance, permissions and, 454
INSERT INTO statement (DMX), 500
installation
advanced options, 14–19
for DTS, 61
for SQL Server 2005, 4–13
prerequisites, 5
service packs, 14–16, 19–20
instance names, 7–8, 16, 52
Integration Services. See SSIS (SQL Server Integration
Services)
interactive reports, 284–286
Internet Information Server. See IIS (Internet Information
Server)
IP addresses, 17
IsAggregatable property, 153
itemset, 246
J
JOIN clause, 136
Jump to Bookmark action, 286
Jump to Report action, 285
Jump to URL action, 286
K
Kass, Steve, 239
Key Performance Indicators. See KPIs (Key Performance
Indicators)
KeyColumns property, 153
KPIGoal function (MDX), 181
KPIs (Key Performance Indicators)
creating, 179, 181–183
Cube Designer support, 145
Discover events and, 475
MDX support, 192
post-migration consideration, 57
properties, 179–180
viewing, 180
KPIs tab (Cube Designer), 145
KPIStatus function (MDX), 181
KPITrend function (MDX), 181
KPIValue function (MDX), 181
L
Lachev, Teo, 411
Lag function (MDX), 195
LastChild function, 173
LastNonEmpty function, 173
least privilege, principle of, 397
Left function (VBA), 501
licensing requirements, 3
lift charts, 488–490
Linear Regression algorithm (DM), 245, 510
linked reports, 530–531, 535–5387
List data region (SSRS), 297
Log\File property, 43
Log\FlightRecorder\Enabled property, 43
Log\FlightRecorder\TraceDefinitionFile property, 43
Log\QueryLog properties, 43–44
Log\QueryLog\CreateQueryLogTable property, 43
Log\QueryLog\QueryLogConnectionString property,
44
Log\QueryLog\QueryQueryLogSampling property, 44
Log\QueryLog\QueryQueryLogTableName property,
44
LogDir property (SSAS), 44
logical AND statement, 111, 113
Logical condition properties, 112
logical OR statement, 111, 113
Logistic Regression algorithm (DM), 245, 510
Lookup transformation, 92, 116
M
Management/Legacy folder, 62
market basket analysis, 246
Markov chains, 246
Matrix data region (SSRS), 297–299
matrix reports
classification matrix, 491–492
creating, 269–270
hiding items, 285
MaxConcurrentPredictionQueries property, 510
MaxIdleSessionTimeout property, 476
MAXIMUM-STATES parameter (DM), 262
MDX (Multidimensional Expressions)
Analysis Services Migration Wizard and, 54
common functions, 194–195
creating calculations, 192–197
creating queries, 192–198
defining business metrics, 167
filtering queries, 309
OLAP support, 291
PrevMember function, 179
SQL Server Profiler support, 473
SSAS security, 457–459
SSRS support, 290
viewing KPIs, 181
MDX Query Builder, 269, 273, 291–293
measure groups
creating, 142, 169–172, 174–177
Cube Designer support, 145
Cube Wizard support, 142
dimension relationships, 158, 160–162
fact tables and, 206
partition support, 205–209, 215–220
properties, 171
renaming, 143
scripting definitions, 470
SSAS processing support, 372–373
MeasureExpression property, 174
measures
calculated members and, 196
creating, 173–174, 176–177
defined, 50
in cubes, 50
measure group requirements, 170, 173
Members function (MDX), 195–197
Membership tab (Solution Explorer), 455
Merge Join transformation, 93
Merge transformation, 93
Message Queue Task (SSIS), 85
metadata
backing up, 466
copying, 367
cube storage model, 209
Cube Wizard support, 139
KPI properties, 180
Microsoft .NET Framework, 4–5, 277–278, 320
Microsoft Clustering Services. See MSCS (Microsoft
Clustering Services)
Microsoft Excel, 181, 184
Microsoft_Association_Rules property, 510
Microsoft_Clustering property, 510
Microsoft_Decision_Trees property, 510
Microsoft_Linear_Regression property, 510
Microsoft_Logistic_Regression property, 510
Microsoft_Naive_Bayes property, 510
Microsoft_Neural_Network property, 510
Microsoft_Sequence_Clustering property, 510
Microsoft_Time_Series property, 510
migration process
DTS, 61
SSAS, 50–58
SSIS, 60–66
Migration Wizard (SSAS), 52–56, 58–60, 468
MINIMUM_PROBABILITY parameter (DM), 262
MINIMUM_SUPPORT parameter (DM), 262
mining models. See data mining models
Mining Structure tab (Solution Explorer), 455
mining structures. See data mining structures
missing values in data mining, 239
Mixed Mode authentication, 9
Model Designer, 326
model tree view (Model Designer), 326
models. See data mining models
MOLAP (Multidimensional OLAP)
backup considerations, 467
defined, 372
proactive caching, 212–215, 382
storage modes, 152, 171, 210
MSCS (Microsoft Clustering Services), 16–18
msdb system database
DTUtil support, 352
manipulating packages, 62
securing packages, 432–435, 444
storing packages, 61, 346, 444
MSMQ, 85
MSOLAP_NODE_SCORE attribute, 494
Multicast transformation, 93, 116
Multidimensional Expressions (MDX), 54, 167, 192–197
Multidimensional OLAP. See MOLAP (Multidimensional
OLAP)
multidimensional queries, 291–293
My Reports (SSRS), 522, 530–531
N
Naive Bayes algorithm (DM), 245, 247, 262, 510
Name property, 145–146, 153, 159, 418
NameColumn property, 153
named calculations, 135
named queries, 135–136, 324
named sets, 196–197, 199–200
nested tables, 239, 244, 254–255
nesting data regions, 299
Neural Network algorithm (DM), 245, 262, 510
nodes, 16
Notification events, 475
NotifyTableChange command (XMLA), 384
Null delivery channel, 412
number formatting, 277–278
O
object definitions, backing up, 470–471
ODBC, 417
OLAP (Online Analytical Processing)
data mining support, 392
MDX support, 192, 291
SSAS Report Models, 324
SSAS support, 129, 192, 372–373
SSRS support, 411
OLAP cubes
data mining support, 238, 244, 261
SSRS support, 411
OLE DB
adding connections, 80
data source support, 132
SSAS support, 57
SSRS support, 417
OLE DB Command transformation, 94
OLE DB destination adapter, 90
OLE DB source adapter, 89
OnError event handler, 121–122
OnExecStatusChanged event handler, 121
OnInformation event handler, 121
Online Analytical Processing. See OLAP (Online
Analytical Processing)
OnPostExecute event handler, 121
OnPostValidate event handler, 121
OnPreExecute event handler, 121
OnPreValidate event handler, 121
OnProgress event handler, 121
OnQueryCancel event handler, 122
OnTaskFailed event handler, 122
OnVariableValueChanged event handler, 122
Openquery function, 510
Openrowset function, 510
Oracle databases, 324, 417
OrderBy property, 153
OrderByAttribute property, 153
outliers in data mining, 239
OverwriteDataSources property (SSRS), 271, 398
P
Package Configuration Wizard, 335–342
Package Configurations Organizer, 335, 341–342
package connections
connection strings, 334
data flow source adapters, 89
defining, 78–82
security and, 429
Package Deployment Utility (SSIS), 346–349
Package designer (BIDS), 71, 83, 87
Package Installation Wizard, 349–351
Package Migration Wizard, 62–65
Package Roles editor, 434
PackageName variable, 92
PackagePassword property, 432
packages. See SSIS packages
page layouts, 271
PageHeight property, 272
PageWidth property, 272
parallel processing, 58
ParallelPeriod function (MDX), 195
parameters. See dataset parameters; query parameters;
report parameters
Parent function (MDX), 194–195
Parent KPI property, 180
parent package variables, 336
Partition Processing destination adapter, 90
partition slice query, 208
Partition Wizard, 206, 208
partitions
considerations, 205–206
defined, 205
designing aggregations on, 221–232
measure group, 206–209
reasons for, 205
remote, 467
scripting definitions, 470
selecting storage modes, 205, 209–211, 215–220
SSAS processing support, 372–373
Partitions tab (Cube Designer), 145, 206, 208, 211
passwords
SSAS permissions, 510
SSIS packages, 430, 432
PDF rendering extension (SSRS), 271, 274, 284, 533
Percent Sampling transformation, 93, 239
PerfMon (Windows Performance Monitor), 480–482,
484
performance. See also KPIs (Key Performance Indicators)
report time-out, 535
tracking for SSAS cubes, 473–482
Windows Performance Monitor, 480–482, 484
performance counters, 480
PERIODICITY_HINT parameter (DM), 263
permissions
for SSRS item-level roles, 521–522, 524–526
for SSRS system-level roles, 528–530
SSAS support, 453–454, 459–460, 510–511
perspectives
implementing, 186–187, 190–191
virtual cubes and, 51
Perspectives tab (Cube Designer), 145, 187
Pivot Table Services client (SSAS), 57
Pivot transformation, 93
polling notification, scheduled, 384–386, 388–390
Precedence Constraint Editor, 112
precedence constraints. See constraints
prediction clause in data mining, 253–254
prediction queries, 498–499, 501–506, 510
Prediction Query Builder, 498–499, 501
PredictTimeSeries function (DMX), 501
PrevMember function (MDX), 179, 194
primary keys, 133–135, 206
private data sources, 290
proactive caching
MOLAP support, 210, 212–215, 382
post-migration considerations, 58
processing SSAS objects, 380–386, 388–390
ROLAP support, 210, 214, 382
understanding, 212–215, 218–220
ProactiveCaching property, 152, 171
probabilities in data mining, 245
Process database role (SSAS), 41
Process property (SSAS), 453
ProcessingMode property, 171
Production configuration, 398
profit charts, 488–490
Progress events, 475
projects
adding SSIS packages, 76
creating in BIDS, 75–77
creating SSRS, 269–272
defining SSAS data sources, 131–133, 136–137
defining SSIS data sources, 78–82
lifecycle for data mining, 237–238
report models, 324–328
properties
color, 276–277
Cube Designer support, 145
data mining models, 508–510
dataset, 290
date formatting, 277–278
dimension support, 146, 153, 155–156
DTExecUI support, 440–441
dynamic, 321–322
Evaluation operation, 112–113
extending report, 318–322
font, 276
KPI, 179–180
Logical condition, 112
measure group, 171
measures, 174
modifying report, 536–538
number formatting, 277–278
SSAS support, 42–44, 51, 360, 453
SSIS support, 340–341, 343–344, 347
SSRS support, 270–272, 276–278, 281, 286–289,
398–399
trace, 473–474, 478
Proprietary action, 184
ProtectionLevel security setting, 429–432
Publisher role (SSRS), 522
Q
queries
data mining, 498–506
DMX support, 291
DSV support, 135–136
MDX support, 192–198
multidimensional, 291–293
named, 135–136, 324
partition slice, 208
prediction, 498–499
timeouts, 397
WMI support, 85
query axes, 194
query binding, 206–207
Query events, 475, 478
query logging, 43–46, 226–227
query parameters
creating in datasets, 309–311
defined, 309
report parameter differences, 311
QueryLog properties, 226–227
QueryLogConnectingString property, 226
QueryLogSampling property, 226
QueryLogTable property, 226–227
R
Raise Change Event property, 334
RDL (Report Definition Language), 418, 533
read actions, 432, 434
Read Contingent permission (SSAS), 459–460
Read definition role (SSAS), 41
Read permission (SSAS), 459
Read property (SSAS), 453
Read/Write permission (SSAS), 460
ReadDefinition property (SSAS), 453
Reader role (SSIS), 434
Recordset destination adapter, 90
registry entry configuration type (SSIS), 336, 340–342
Relational Database Engine. See Database Engine
relational databases. See databases
Relational OLAP. See ROLAP (Relational OLAP)
Relational Report Models, 324
relationship types, dimensions by, 158–162
remote partitions, 467
rendering formats, 271, 417–423, 533
Report Builder (SSRS)
action support, 184
creating datasets, 290–293
creating models, 324–331
deploying reports, 397, 401
permissions and, 522
report cache, 533–534
Report Definition Language (RDL), 418, 533
Report Designer (SSRS)
deploying reports, 398, 405–406
developing report objects, 276–280
modifying report properties, 274
Report Manager (SSRS)
creating shared schedules, 408
creating SSRS item-level roles, 522–523, 526–527
creating SSRS system-level roles, 529, 531
data-driven subscriptions, 418
deploying reports, 397, 400
modifying report properties, 536–538
SSAS Report Models, 324
Report Model Project template, 269, 401
Report Model Wizard, 325–328
report models
creating, 324–331
creating on SSAS cubes, 326–327
Report Model Wizard, 325–326
types, 324
working with projects, 326
report parameters
adding, 311
binding datasets, 312
creating, 314–317
defined, 309
exposing to users, 311
hiding, 314
in URLs, 313–314
mapping query parameters, 310
query parameter differences, 311
working with defaults, 312–313
Report Project Wizard, 269–270
Report Server
managing security, 528
report cache and, 534
storing credentials, 539
validating properties, 533
viewing properties, 528
Report Server Project template, 269
Report Server Project Wizard, 269–270
Report Server Script (.rss) file, 33
Report Wizard, 269, 272
ReportID, 534
reporting actions, 185
Reporting Services. See SSRS (SQL Server Reporting
Services)
Reporting Services Configuration Manager. See RSCM
(Reporting Services Configuration Manager)
reports. See also SSRS (SQL Server Reporting Services)
assigning parameters, 309–317
extending properties with expressions, 318–322
hiding columns, 284–288
hiding rows in, 284–285
interactive, 284–286
leveraging code element, 320–321
linked, 530–531, 535–538
managing execution, 533–535
matrix, 269–270, 285, 491–492
scheduled, 407–414
setting history options, 535
snapshots, 397, 411, 535
tabular, 269–270
unhiding columns, 288
ReportServerTempDB database, 410, 534
repository databases, 27
restartability (implementing for), 105–107
restoring databases, 467–468
ROLAP (Relational OLAP)
proactive caching, 214, 382
storage modes, 152, 171, 210
Role Designer tab (Solution Explorer), 455
Role ID property (SSAS), 453
roles. See also database roles
report model, 326
server, 39–40
SSAS supported, 41, 452
SSIS supported, 434
SSRS item-level, 521–527
SSRS system-level, 528–532
rolling back tasks, 107
rolling back transactions, 107–108
rolling migration, 66
Row Count transformation, 92, 118
Row Sampling transformation, 93, 239
RowNumber aggregate function, 302
rows, hiding in reports, 284–285
Rowset action, 185
rs.exe tool, 29, 33–34
RSCM (Reporting Services Configuration Manager)
command-line tools, 29–33
scale-out deployment, 33
server setup and management, 25–29
rsconfig.exe tool, 29–31
rskeymgmt.exe tool, 29, 31–33
running totals, 302–305
RunningValue aggregate function, 302
S
SAC (Surface Area Configuration) tool, 407
SaveCheckpoints property, 105
scatter plots, 119
SCD (Slowly Changing Dimension) transformation, 94
scheduled polling notification, 384–386, 388–390
scheduled reports, 407–414
scheduling backups, 466–472
scheduling SQL Server Agent jobs, 447
schema information, 429
scope assignments, 196
Script Component transformation, 94, 123
Script Task (SSIS), 85
Secure Socket Layer (SSL), 35
SecureConnectionLevel property, 35
security
cell, 459–460, 463–464
copying metadata and, 367
creating roles, 454–455
data mining models, 508–517
dimension data, 456–459
drillthrough, 460, 464–465
for SSAS architecture, 452–454
implementing for SSAS cubes, 461–465
perspectives and, 187
SSIS packages, 429–435
SSRS item-level roles, 521–527
SSRS system-level roles, 528–532
testing database roles, 461
top-level dimensions, 56
Security Audit events, 475
SELECT INFO statement (DMX), 500
SELECT statement
cautions using, 269
changing partition slice, 207
DMX support, 500, 510
DSV support, 135–136
in MDX queries, 193–194
prediction queries and, 498–500
processing SSAS objects, 372, 377
WHERE clause, 136, 309
SELECT TABLESAMPLE clause (T-SQL), 239
Send Mail Task (SSIS), 85, 122
Sequence Clustering algorithm (DM), 246, 262, 494–
495, 510
Sequence Container (SSIS), 86, 104, 107
sequence model (DM), 255, 257–258
server roles, 39–40
Service Account screen, 8–9
service packs, installing, 14–16, 19–20
sessions, 476
Set Aggregation Options page, 225
shared data sources, 290, 539–540
SharePoint Server (Microsoft Office), 397
side-by-side migration, 52
Simple Mail Transport Protocol (SMTP), 85, 412
Simple Object Access Protocol (SOAP), 34
Slowly Changing Dimension (SCD) transformation, 94
SMTP (Simple Mail Transport Protocol), 85, 412
snapshots, report, 397, 411, 535
SOAP (Simple Object Access Protocol), 34
Sort transformation, 93
sorting data, 301–302
source adapters
creating, 95–96
data flow tasks and, 89
defining, 89
error path support, 116
package connections as, 78
Source property, 174
SourceName system variable, 122
SPAM filters, 247
SPLIT_METHOD parameter (DM), 262
SQL authentication, 39
SQL Server 2005
choosing installation details, 7–11
core components, 1
data-driven subscriptions, 417
DTS support, 60–62
installation files, 2
installing components, 11–13
invoking installation, 4–7
licensing requirements, 3
management and development tools, 3–4
primary editions, 2
proactive caching notification, 383
SAC tool, 407
SSIS deployment, 346–347
SQL Server 2005 Management Studio. See SSMS (SQL
Server 2005 Management Studio)
SQL Server Agent
DTUtil support, 352, 438
enabling, 445
executing SSIS packages, 445–448
running deployment scripts, 366
scheduling jobs, 447–448
scheduling SSAS backups, 468–469
scheduling SSRS reports, 407
SSMS support, 444
SQL Server Analysis Services. See SSAS (SQL Server
Analysis Services)
SQL Server configuration type (SSIS), 337–342, 344
SQL Server destination adapter, 90
SQL Server Integration Services. See SSIS (SQL Server
Integration Services)
SQL Server Mobile destination adapter, 90
SQL Server Native Client, 4–5
SQL Server Profiler, 473
SQL Server Reporting Services. See SSRS (SQL Server
Reporting Services)
SQLServerStorage protection level, 431, 435
SSAS (SQL Server Analysis Services). See also data mining
architecture changes, 50–51
BIDS support, 4, 75
cell security, 459–460, 463–464
clustering, 14, 16–18
collation settings, 9
control flow tasks, 84
Cube Wizard, 139–144
datasets, 291–293
designing partition aggregations, 221–232
dimension data security, 456–459
drillthrough security, 460, 464–465
editing server-level properties, 42–44
FILTER clause, 309
instances and, 7–8
measure group partitions, 205–209
migrating databases, 468
Migration Wizard, 52–56, 58–60, 468
proactive caching, 212–215
program files location, 17
query strings, 290
renaming instances, 8
scheduling backups, 466–472
selecting partition storage modes, 209–211
service account settings, 1
setting database roles, 39–41
setting server roles, 39–40
setting up query logging, 44–46
SQL Server 2005 component, 1–2
SSMS support, 3, 470–471
SSRS support, 417
testing database role security, 461
tracing instances, 473–480, 482–483
user security architecture, 452–454
Windows Performance Monitor, 480–482, 484
XMLA scripting, 470–471
SSAS cubes
creating, 139–144, 147–148
creating data source views, 133–136
creating data sources, 131–133
creating KPIs, 179–183
creating measure groups, 169–172, 174–177
creating measures, 173–174, 176–177
creating report models, 326–327
defined, 50
defining actions, 184–185, 187–189
dimension data security, 456–459
implementing perspectives, 186–187, 190–191
implementing security, 452–465
localizing with translations, 186, 189–190
manipulating dimensions, 150–154, 156–157
MDX support, 192–197
migrating, 51–52, 56
modifying, 144–146, 148–149
proactive caching, 58
scheduling backups, 466–472
scripting definitions, 470
SSAS changes, 51
tracking performance, 473–482
user hierarchies and dimension relationships,
158–162
SSAS objects
BIDS support, 359–362, 374–378
Deployment Wizard, 359, 362–366, 369–370
proactive caching, 380–386, 388–390
processing data mining objects, 391–393
processing dimensions incrementally, 386–387
processing options, 372–373
SSMS support, 378–380
Synchronize Database Wizard, 359, 367–371
understanding dependencies, 373–374
XMLA script support, 359, 365–366, 379, 387–388
SSAS Report Models, 324
SSIS (SQL Server Integration Services)
automatic processing, 374
BIDS support, 4
constraint support, 111
creating projects in BIDS, 75–77
data mining support, 245
data preparation, 239
enabling restartability, 105–107
event handler support, 120–122
migrating to, 60–68
partition support, 206
purpose, 23
SQL Server 2005 component, 1–2
SSMS support, 3, 433
SSRS support, 417
text mining support, 246
transaction support, 103
SSIS Package Store, 351–352, 444
SSIS packages
adding to projects, 76
configuring checkpoints, 103–107
configuring transactions, 103–107
control flow containers, 85–88
control flow tasks, 83–85, 87–88
creating, 72–76, 334–342
creating configurations, 335–342
data flow adapters, 89–90
data viewer support, 119
debugging, 122–124
defined, 71
defining connections, 78–82
deploying, 346–354
dtsx extension, 64, 334, 346, 349
DTUtil utility, 346, 349, 351–353
dynamic properties, 343–344
enabling configurations, 335
encrypting, 436–437
executing, 438–448
handling data flow errors, 116–122, 124–126
identifying status, 109–115
implementing transactions, 107–108
leveraging two-pass configurations, 342
Package Deployment Utility, 346–349
Package Installation Wizard, 349–351
restartability, 103
securing, 429–436
SSISDeploymentManifest extension, 349
SSMS support, 444–448
testing execution, 87–88
transformations, 91–95
understanding configurations, 334
understanding deployment, 346–347
SSISDeploymentManifest extension, 349
SSL (Secure Socket Layer), 35
SSMS (SQL Server 2005 Management Studio)
Aggregation Design Wizard, 222–225, 228–230
AMO support, 357
creating packages, 71
creating report models, 326
creating shared schedules, 408
database roles, 454
description, 3
DTS support, 61–62
partition support, 206, 211
prediction queries, 498–499
running deployment scripts, 366
scheduling backups, 466–472
SQL Server 2005 support, 3
SSAS Report Models, 324
SSAS support, 39, 373, 378–380
SSIS packages, 433, 444–448
SSRS support, 397, 400, 403–404, 418–421
Usage-Based Optimization Wizard, 222, 225–227,
230–231
viewing KPIs, 181
SSMS Object Explorer, 56, 433, 470
SSRS (SQL Server Reporting Services)
adding report objects, 278–280
applying dataset filters and groups, 297–302
applying subscriptions to reports, 412
assigning parameters, 309–317
BIDS support, 4, 397–400, 402–403
command-line tools, 29–34
creating datasets, 290–296
creating report schedules, 407–414
creating report subscriptions, 397, 412, 414–415
defining data-driven subscriptions, 417–423
defining report actions, 285–286
deploying reports, 397–406
extending reports, 318–322
IIS support, 4
implementing actions for, 184
installation options, 10
installing multiple instances, 18–19
instance name and, 7
Internet deployment, 36
item-level roles, 521–527
linked reports, 530–531, 535–538
managing data sources, 539–542
managing encryption keys, 36–38
managing report execution, 533–535
manipulating reports, 272–274, 281–283
modifying properties, 270–272, 274, 281, 286–289
My Reports space, 530–531
objects in BIDS, 75
prediction queries, 502
Report Builder, 184, 324–331, 397, 401
report cache, 533–534
Report Designer, 274, 276–280, 405–406
Report Manager, 397, 400, 408, 418
Report Project Wizard, 269–270
report snapshots, 397, 411, 535
server setup and management, 25–29
SQL Server 2005 component, 1–2
SSL communication support, 35
SSMS support, 3, 397, 400, 403–404, 408, 418–421
system-level roles, 528–532
toggling object visibility, 284–285
viewing KPIs, 181
Web farm environment, 14
StartItem property (SSRS), 271, 398
Statement action, 185
Status Indicator property, 180
Status property (KPI), 179
storage modes for partitions, 205, 209–211, 215–220
StorageMode property, 152, 171, 211
subscriptions, report
creating, 397, 412, 414–415
defining data-driven, 417–423
SSRS item-level roles, 521–522
SUM aggregate function, 302
Surface Area Configuration (SAC) tool, 407
switch function, 319
Synchronize command (XMLA), 367–369
Synchronize Database Wizard, 359, 367–371
System Administrator role (SSRS), 529
System User role (SSRS), 529
system variables, 122
T
table binding, 206
Table data region (SSRS), 297–299
table relationships, 133–135
Table/Matrix Properties windows, 284–285
tables
hiding items, 285
nested, 239, 244, 254–255
tabular reports, 269–270
TargetDataSourceFolder property (SSRS), 271, 398
TargetReportFolder property (SSRS), 271, 398
TargetServerURL property (SSRS), 271, 399
tasks
defined, 83
defining transaction settings, 103–104
DisableEventHandlers property, 122
error handling, 109–115
implementing transactions, 107–108
overview, 83–85
rolling back, 107
SSRS item-level roles, 521–522
SSRS system-level roles, 528–529
Term Extraction transformation, 94, 246
Term Lookup transformation, 94, 246
test set (data mining), 239, 241–243
testing
data mining model accuracy, 488–497
database role security, 461
SSAS post-migration, 57–58
staging reports, 397, 401
text boxes in reports, 278–279
text mining, 246
Time Series algorithm (DM), 246, 263, 495, 510
time, functions navigating, 195
Timeout property, 290
time-outs, report, 535
Tkachuk, Richard, 457
toggling object visibility, 284–285
TopCount function, 197
tracing SSAS instances, 473–480, 482–483
training set (data mining), 239, 241–243
TransactionOption property, 103–104, 107
transactions
configuring settings, 103–107
defined, 103
implementing, 107–108
rolling back, 107–108
Transact-SQL, 239, 290, 500
transformations
creating, 90
data flow tasks and, 89
data mining support, 245
overview, 91
selecting, 91–94
text mining support, 246
using, 94–96
translations
dimension, 186, 189–190
localizing cubes, 186
Translations tab (Cube Designer), 145, 186
Trend Indicator property, 180
Trend property (KPI), 180
troubleshooting
circular references, 342
data flow issues, 118–120
flight recorder and, 478
tuples, 192–193, 196
Type property, 171
U
UCase function (VBA), 501
UDL (Universal Data Link), 65
UDM (Unified Dimensional Model), 51, 244
UDM cubes, 261
UNC (Universal Naming Convention), 412, 418
undirected approach (data mining), 237
Unified Dimensional Model (UDM), 51, 244
Union All transformation, 93
Universal Data Link (UDL), 65
Universal Naming Convention (UNC), 412, 418
Unpivot transformation, 93
UPDATE statement (DMX), 500
Upgrade Advisor tool, 50
upgrading SSAS, 50–59
URL action, 185
URLs in report parameters, 313–314
Usage property, 153
Usage-Based Optimization Wizard, 222, 225–227,
230–231
V
ValidateExternalMetadata property, 90
Value property
built-in function expressions, 320
control flow expressions, 319–320
KPIs, 179
text boxes, 279
variables
defined, 237
measuring values, 238–239
parent package, 336
query parameters, 309
report parameters, 309
system, 122
VB.NET, 85
VBA (Visual Basic for Applications), 501
virtual cubes, 51
virtual directories, 26, 35
Virtual SQL Server name, 17
Visible property, 145–146, 174
Visual Basic for Applications (VBA), 501
Visual Studio, 4
Visual Total property, 459
W
Web farms, 14
Web Service Description Language (WSDL), 33
Web Service Task (SSIS), 85
Web services, 85
Weight property, 180
WHERE clause
data duplication and, 385
partition slice queries, 208
query parameters, 309
SELECT statement support, 136, 309
Windows Admin role (SSIS), 434
Windows Authentication
purpose, 9
SQL Server Agent, 448
SSAS support, 39, 508
SSIS support, 342, 431, 441, 444
SSRS support, 35
Windows DTC. See DTC (Distributed Transaction
Coordinator)
Windows Management Instrumentation (WMI), 473
Windows Performance Monitor (PerfMon), 480–482,
484
Windows Scheduler, 352
WITH clause, 194
WMI (Windows Management Instrumentation), 473
WMI Data Reader Task (SSIS), 85
WMI Event Watcher Task (SSIS), 85
WMI queries, 85
write actions, 432, 434
Write property (SSAS), 453
Writer role (SSIS), 434
WSDL (Web Service Description Language), 33
X
XML file configuration type (SSIS), 336–338, 340,
342–344
XML rendering extension (SSRS), 271, 284
XML source adapter, 89
XML Task (SSIS), 85
XMLA scripts
ClearCache command, 482
deploying SSAS objects, 359, 365–366
NotifyTableChange command, 384
processing SSAS objects, 379, 387–388
scripting SSAS objects, 470–471
Synchronize command, 367–369
© Microsoft. All Rights Reserved.