SYMBOLS
1:M. See one-to-many relationships
A
accessing
data warehouses
for applications, 89-90
for database administrators, 87
for users, 88-89
database for Reporting Services, 204-205
transforming logic into, 146
address information, 277-278
administrators (Analysis Services), security, 171-172
ADOMD.NET, 334-337
aggregates (OLAP), 12-13
aggregations for VLDBs (very large
databases), 348
algorithms for data mining, 57-59, 313, 321
Analysis Services, 42
administrator security, 171-172
architecture of, 42-43
availability, 47
data mining features, 55, 309-314
adding to Web applications, 334-337
architecture for, 56-58
Association Rules algorithm, 330-334
creating mining structure, 323-329
Data Mining dimension, 329-330
data sources for, 321
deployment, 337-338
dimensional modeling for, 314-319
ETL process, 319-321
managing, 59
predictions, 339
reprocessing mining models, 338-339
segmentation with clusters, 322
Sequence Clustering algorithm, 339
Database Synchronization, 376-377
development environment, 44-45
discretization feature, 320
managing, 45-46
missing dimension members, 237-238
partitioned cubes, 362-366
managing, 372-375
scalability, 47
security, 45
slowly changing dimensions (SCDs), 263-266
processing, 267-268
translations, 307
UDM (Unified Dimension Model), 46-47
very large database support, 48
Analysis Services databases, 141-145. See also
cubes (OLAP)
architecture of, 143-144
backing up, 174-175
creating, 148-151
cube creation, 151-157
cube processing, 173-174
cube setup, 161-167
deployment, 169-172
dimension setup, 157-162
on-time shipments analysis, 145-147
relational tables versus, 275
in scorecard applications
KPIs (key performance indicators), 288-290
many-to-many relationships, 285-286
parent-child dimensions, 282-285
perspectives, 286-288
security, 303-305
setup, 281-282
security, 169
source code control, 172
types of data, 148
updating views, 173
Analysis Services Deployment Wizard, 170-171
Analysis Services queries, parameters for, 292
application access to data warehouses, 89-90
architecture
of Analysis Services, 42-43
of Analysis Services databases, 143-144
for data mining, 56-58
of Reporting Services, 48-49
Association Rules algorithm, 330-334
adding to dimension tables, 80
candidate attributes, listing, 20
data warehouses, 6
relationships between, 160-162
setting up dimensions, 157-162
auditing data quality, 26
with row counts, 239
authentication, 33
for Analysis Services, 45
Windows authentication, 87
automated processing, VLDBs (very large
databases), 375
availability
Analysis Services, 47
SQL Server database engine, 34
B
backing up
Analysis Services databases, 174-175
data warehouses, 93-95
real-time business intelligence, 395
Reporting Services, 211
scorecard applications, 306
slowly changing dimensions (SCDs), 268
VLDBs (very large databases), 371-372
breakpoints, debugging packages, 41
browser (cube), 156-157
Business Intelligence (BI), defined, 1-2. See
also real-time business intelligence
Business Intelligence (BI) projects
business-value based approach, 23-24
failures, reasons for, 24-27
Business Intelligence (BI) Development
Studio, 30
as Analysis Services development
environment, 44-45
creating data warehouses, 96
data mining, 57
online mode, 176
package design, 37
project mode, 177
report design, 49
business involvement in BI projects, 25
business keys, 18-19
adding to dimension tables, 78-79
preventing bad source data, 240-241
translating into surrogate keys, 117, 119-120, 229-231, 261-263
business process for data warehouse
dimensional model, 66-67
Business Scorecard Manager, 307
C
calculated measures, 164-167
calculations, adding reports, 192-193
canceling long-running reports, 211
candidate attributes, listing, 20
candidate measures, listing, 21
case (data mining), 331
changing dimensions. See slowly changing dimensions (SCDs)
Chart reports, 200
charts, adding to reports, 52, 295-296
checkpoints, 136-137
CIF (Corporate Information Factory), 24
client-initiated notification, 391-392
Cluster Diagram view, 325-326
Cluster Discrimination view, 328-329
Cluster Profiles view, 326-327
clustered indexes
for dimension tables, 79
for fact tables, 84
clustering, 34
clusters (data mining)
creating mining structure, 323-329
renaming, 327
reprocessing mining models, 339
segmentation with, 322
views, 325-329
code, adding to reports, 212
columns
converting to rows, 121-123
creating, 123-124
complex relationships. See relationships
Conditional Split transform, 40, 225
confidence level (fuzzy matching), 221
configuration settings, package deployment, 128-133
configuring packages, 41
conformed dimensions, 24
connection managers, 106-107
constraints
data warehouse and, 113
resource constraints, 348
control flow (packages), 37-38, 108
dimension loading, 109-110
converting columns to rows, 121-123
Corporate Information Factory (CIF), 24
cost-benefit analysis of BI projects, 27
credentials for database access, 204
cube browser, 156-157
cubes (OLAP), 10-11. See also Analysis Services databases
additive measures, 146-148
creating, 151-157
designing for real-time business intelligence, 384-385
loading data into, 155-156
partitioning, 362-366
managing cube partitions, 372-375
perspectives, 286-288
relationships with dimensions, 163-164
in scorecard applications
security, 303-305
setup, 281-282
setting up, 161-167
time intelligence, adding, 266
translations, 307
D
dashboard applications. See scorecard applications
data, loading into cubes, 155-156
data destinations, support for, 39
data flow (packages), 37-40, 109
dimension loading, 109-111
for inferred members, 234
inspecting, 115
legitimately missing members, 238
VLDBs (very large databases), 358-362
data integration process, 99-103
granularity of data, 103-105
with Integration Services, 105-106
connection managers, 106-107
dimension loading, 108-116
Excel spreadsheet loading, 119-125
fact table loading, 116-120
multiple file loading, 125-127
package deployment, 128-134
package operation in production, 135-137
package security, 134
source control, 128
data marts, data warehouses versus, 9
adding to Web applications, 334-337
architecture for, 56-58
Association Rules algorithm, 330-334
creating mining structure, 323-329
Data Mining dimension, 329-330
data sources for, 321
deployment, 337-338
dimensional modeling for, 314-319
ETL process, 319-321
in Integration Services, 59-60, 340
managing, 59
predictions, 339
reprocessing mining models, 338-339
segmentation with clusters, 322
Sequence Clustering algorithm, 339
Data Mining dimension, 329-330
Data Mining Extensions (DMX), 58, 334-337
Data Mining Query transformations, 59
data modeling. See dimensional modeling
data quality, 215-216
auditing with row counts, 239
dimensional modeling, 218-219
error handling, 241-242
in ETL process, 216-218
merging data, 220-229
missing dimension members, 229-238
need for in BI projects, 25-26
null values, 243
out-of-range values, 243
of packages, 41
preventing bad source data, 240-241
unexpected values, 243
zero values, 243
data source view (DSV), 149-151
data sources
data integration process, 99-103
connection managers, 106-107
dimension loading, 108-116
Excel spreadsheet loading, 119-125
fact table loading, 116-120
granularity of data, 103-105
with Integration Services, 105-106
multiple file loading, 125-127
package deployment, 128-134
package operation in production, 135-137
package security, 134
source control, 128
detecting changes in source data, 260-261
preventing bad source data, 240-241
for reports, 192
support for, 39
Data Transformation Services (DTS). See
Integration Services
data truncation errors, 228-229
data types, matching source and destination, 121
Data Viewer, 115
data warehouses, 5
backing up, 93-95
building reports against, 182
constraints and, 113
creating with BI Development Studio, 96
data integration process, 99-103
connection managers, 106-107
dimension loading, 108-116
Excel spreadsheet loading, 119-125
fact table loading, 116-120
granularity of data, 103-105
with Integration Services, 105-106
multiple file loading, 125-127
package deployment, 128-134
package operation in production, 135-137
package security, 134
source control, 128
data marts versus, 9
data quality, 215-216
auditing with row counts, 239
dimensional modeling, 218-219
error handling, 241-242
in ETL process, 216-218
merging data, 220-229
missing dimension members, 229-238
null values, 243
out-of-range values, 243
preventing bad source data, 240-241
unexpected values, 243
zero values, 243
deployment, 90-91
design of, 5-7
dimension tables, building, 78-83
dimensional modeling for, 66
business process focus, 66-67
granularity, 67-68
identifying dimensions, 68-73
identifying measures, 73-77
ETL (extraction, transformation, and
loading), 8-9
for data mining, 319-321
fact tables, building, 83-86
feeding to e-commerce systems, 313
importing data for real-time business
intelligence, 393-394
maintenance plans, 92-93
naming standards, 82
OOD (object-oriented design) in, 186
problems with real-time business
intelligence, 380
proactive caching as solution, 380-382
restoring, 95
security, 87
application access, 89-90
database administrator access, 87
user access, 88-89
slowly changing dimensions (SCDs), 245-249
Analysis Services and, 263-266
backups, 268
changing dimension tables for, 256-257
deployment, 267
detecting changes in source data, 260-261
processing in Analysis Services, 267-268
Slowly Changing Dimension
transformation, 253-259
translating business keys to surrogate keys, 261-263
Type 1 SCD, 249
Type 2 SCD, 250-252
for wide dimension tables, 263
as solution to ERP (enterprise resource
planning) systems, 64-66
Time dimension, 7
UDM (Unified Dimensional Model)
versus, 144
VLDBs (very large databases), 343-349
aggregations, 366-367, 374-375
automated processing, 375
backing up, 371-372
cube partitions, 362-366, 372-375
data flow, 358-362
Database Synchronization, 376-377
fact tables in, 349-351
initial loading, 368
large dimension support, 367-368
partitioned tables, 352-357, 368-372
partitioned views, 376
data-driven subscriptions, 54, 208
database access for Reporting Services, 204-205
database administrator access to data
warehouses, 87
database engine (SQL Server), 31
availability, 34
job scheduling, 33
management of, 32-33
scalability, 35
security, 33-34
very large database support, 35
Database Engine Tuning Advisor, 32
database servers, deploying Reporting Services on, 209-210
Database Synchronization (Analysis Services), 376-377
datasets in Reporting Services, 51
DateDiff function, 320
dates, loading slowly changing dimensions
(SCDs), 267. See also time information
debugging packages, 41
degenerate dimensions, 76, 167-168
deleted members, slowly changing dimensions
(SCDs) and, 255
deleting
cube partitions, 374
partitions, 370
deploying
Analysis Services databases, 169-172
data mining models, 337-338
data warehouses, 90-91
Reporting Services, 209-210
reports to server, 193
slowly changing dimensions (SCDs), 267
SQL Server components, 31
WSS (Windows SharePoint Services), 302-303
Deployment Wizard, 170-171
Derived Column transforms, 123-124
descriptive attributes. See attributes
designing
aggregations for VLDBs (very large
databases), 366-367, 374-375
dimensional data models, 19-20
packages, 37
control flows, 37-38
data flows, 38-40
debugging, 41
partitioned cubes, 362
partitioned tables, 352-353
differential backups, 94
dimension loading, 108-116
dimension members. See inferred members;
missing dimension members
dimension modeling in VLDBs (very large
databases), 349-351
dimension table loading (data warehouses), 8
dimension tables
building for data warehouses, 78-83
changing for slowly changing dimensions
(SCDs), 256-257
cube creation, 151-157
rebuilding, 255
dimension views, relationships with fact views, 149-151
candidate attributes, listing, 20
candidate measures, listing, 21
for data mining, 314-319
data quality, 218-219
for data warehouse projects, 6, 66
business process focus, 66-67
granularity, 67-68
identifying dimensions, 68-73
identifying measures, 73-77
design process, 19-20
E/R modeling versus, 19
hierarchies, 15
measures
grouping into fact tables, 21-22
loading test data, 22
for real-time business intelligence, 382-383
for reports, 184-189
scorecard applications, 275-281
snowflake design, 16-17
star design versus, 17-18
star design, 16
snowflake design versus, 17-18
surrogate keys, 18-19
synonyms, 20
dimensions (data warehouses), 6
adding hierarchies to, 158-159
changing dimensions. See slowly changing dimensions (SCDs)
conformed dimensions, 24
Data Mining dimension, 329-330
degenerate dimensions, 76
fact dimensions, 167-168
identifying for data warehouse dimensional
model, 68-73
large dimension support, 367-368
relationships between, 75-76
relationships with cubes, 163-164
setting up, 157-162
Time dimension, 7
discretization feature (Analysis Services), 320
discriminating between clusters, 328-329
display formats for measures, 162-163
display values for attributes, 158-159
distributed partitioned views (DPVs), 35, 376
DMX (Data Mining Extensions), 58, 334-337
DPVs (distributed partitioned views), 376
drilling down (OLAP), 12
drilling down reports, 52, 200-201
drilling through reports, 52, 201-202
drop outdated cache property (proactive
caching), 388
DSV (data source view), 149-151
DTS (Data Transformation Services). See
Integration Services
E
e-commerce systems, feeding data warehouse
data to, 313
E/R modeling, dimensional modeling versus, 19
elapsed time, measuring, 276-277
enabling proactive caching, 384-385
encryption, 34
end-user reporting, 53-54
with Report Builder, 212
Enterprise Data Warehouse, 24
enterprise resource planning (ERP) systems
data warehouse as solution to, 64-66
problems with, 63-64
ER (entity-relationship) modeling in OLTP, 2
ERP (enterprise resource planning) systems
data warehouse as solution to, 64-66
problems with, 63-64
error handling, data quality issues, 241-242
ETL (extraction, transformation, and
loading) process, 8-9. See also data
integration process
for data mining, 319-321
data quality, 216-218
Slowly Changing Dimension transformation, 253-259
VLDBs (very large databases), 347-348
ETLM (extraction, transformation, loading, and
managing) process, 8
event notifications for importing data into data
warehouse, 394
Excel spreadsheet loading, 119-125
Execute SQL tasks, 114-115
executing packages. See running packages
expressions (Reporting Services), adding to
reports, 192-193
extracting IIS log file information, 313
F
fact dimensions, 167-168
fact tables, 6. See also dimensional modeling
building for data warehouses, 83-86
cube creation, 151-157
degenerate dimensions, 76
grain, 20
data integration process, 103-105
mixing granularities, 68
grouping measures into, 21-22
loading into data warehouses, 8, 116-120
snapshots, 189
updated facts, 269-270
in VLDBs (very large databases), 349-351
without numeric measures, 278
fact views, relationships with dimension views, 149-151
failures of BI projects, reasons for, 24-27
file shares, sending reports to, 208
filegroups, 346-347
creating, 354-355
mapping to partitions, 355
marking as read-only, 370
foreign key constraints on partitioned tables, 357
FrontPage, 307
full backups, 93
full processing, 173
full-text search in SQL Server, 60
fully additive measures. See additive measures
Fuzzy Grouping transform, 40, 59, 220
Fuzzy Lookup transform, 59, 220
G
gaps, avoiding in partition ranges, 364
geographical information, 277-278
in data warehouse dimensional model, 70-71
data integration process, 103-105
for data warehouse dimensional model, 67-68
of fact tables, 20
of measures, 21-22
mixing in fact tables, 68
grouping
measures into fact tables, 21-22
numbers into ranges, 320
groups in reports, 197-198
H
hierarchies, 15
adding to dimensions, 158-159
naming conventions, 160
history, storing in data warehouses, 7. See also
slowly changing dimensions (SCDs)
HOLAP (Hybrid OLAP), 14
I
identity columns, 78
IIS logs, extracting information, 313
importing data
into data warehouse for real-time business
intelligence, 393-394
from Excel, 119-125
incremental loading in real-time business
intelligence, 382
incremental processing, 174
indexed views on partitioned tables, 357
indexes
in cube partitions, 366
dimension tables, adding to, 79
fact tables, adding to, 84
on partitioned tables, managing, 368-369
partitioning, 356
inferred members, 219, 233-236
in real-time business intelligence, 383
Inmon, Bill, 24
inspecting data flow (packages), 115
instances of database engine, 31
integrating data. See data integration process
Integration Services, 35-36
data integration process, 101-102, 105-106
connection managers, 106-107
dimension loading, 108-116
Excel spreadsheet loading, 119-125
fact table loading, 116-120
multiple file loading, 125-127
package deployment, 128-134
package operation in production, 135-137
package security, 134
missing dimension members, 217
package design, 37
control flows, 37-38
data flows, 38-40
debugging, 41
packages
configuring, 41
data quality, 41
deploying, 41
executing, 42
interactive reports, 51-52
invoking packages. See running packages
iterative approach to Business Intelligence (BI)
projects, 23-24
J-K
jobs, scheduling, 33
joining multiple source tables, 116-117
joins, Lookup transformation versus, 361-362
key performance indicators (KPIs), 42
adding to reports, 293-295
defining, 288-290
Kimball, Ralph, 24
L
languages, localization of reports, 213
legitimately missing members, 236-238
level of detail. See granularity
linked reports, 298
Linked Reports feature, 203-204
List reports, 199-200
loading
data into cubes, 155-156
test data, 22
localization of reports, 213
log files (IIS), extracting information, 313
Log Parser utility, 314
logging packages, 137
logic, transforming into additive measures, 146
logical data model, creating, 149-151
long-running reports, canceling, 211
Lookup transformation, 118-120, 229-231, 261-263, 359-362
M
M:M. See many-to-many relationships
maintenance plans, 32
for data warehouses, 92-93
Management Studio. See SQL Server
Management Studio
managing
Analysis Services, 45-46
data mining, 59
database engine, 32-33
Reporting Services, 54-55
manifest, 133
many-to-many relationships, 281
setup, 285-286
mapping partitions to filegroups, 355
Matrix reports, 198-199
MDX (Multidimensional Expressions)
measure groups, 151
measures (data warehouses), 6
calculated measures, 164-167
candidate measures, listing, 21
display formats, 162-163
fact tables without, 278
granularity. See granularity
grouping into fact tables, 21-22
identifying for data warehouse dimensional
model, 73-77
measuring success of BI projects, 27
merging
cube partitions, 373-374
data, data quality issues, 220-229
message queues for importing data into data
warehouse, 394
Microsoft Association algorithm, 313
Microsoft Clustering algorithm, 313, 322
Microsoft Office 2007, 307
Microsoft Office Business Scorecard Manager, 307
Microsoft Office FrontPage, 307
Microsoft Operations Manager (MOM), 32
mining structure, creating, 323-329
mirroring, 34
missing dimension members, 217
data quality issues, 229-238
MOLAP (Multidimensional OLAP), 14
real-time business intelligence and, 381
ROLAP versus, 384-386
MOM (Microsoft Operations Manager), 32
multidimensional databases, problems with
real-time business intelligence, 380
proactive caching as solution, 380-382
Multidimensional Expressions (MDX)
Multidimensional OLAP (MOLAP), 14
real-time business intelligence and, 381
ROLAP versus, 384-386
multiple file loading, 125-127
multiple languages for reports, 213
multiple source tables, joining, 116-117
N
naming
connection managers, 108
data warehouses, 82
hierarchies, 160
nested tables (data mining), 331
.NET code, adding to reports, 212
network ID, as report parameter, 197
normalized data models in OLTP, 2
notification methods for proactive caching, 390-392
Notification Services, 61
in real-time business intelligence, 397
null values, data quality issues, 243
numbers, grouping into ranges, 320
numeric formats for measures, 162-163
numeric measures. See measures
O
object-oriented design (OOD) in data
warehouses, 186
Office Web Components (OWC), adding
PivotTable views to scorecard portal site, 300-302
OLAP (On-Line Analytical Processing), 10-11.
See also Analysis Services databases
cube processing, 11
performance, 12-14
querying databases, 11-12
OLTP (On-Line Transaction Processing), 2-4
OLTP databases, reporting against, 182-184
On-Line Analytical Processing. See OLAP
On-Line Transaction Processing (OLTP), 2-4
on-tine delivery analysis, 164-166
on-tine shipments analysis, 142, 145-147
one-to-many relationships, 279
online mode (BI Development Studio), 176
OOD (object-oriented design) in data
warehouses, 186
out-of-range values, data quality issues, 243
overlaps, avoiding in partition ranges, 364
OWC (Office Web Components), adding
PivotTable views to scorecard portal site, 300-302
P
packages, 36
configuring, 41
data integration process, 101-102, 106
data quality, 41
designing, 37
control flows, 37-38
data flows, 38-40
debugging, 41
dimension loading, 108-116
executing, 42
production operation, 135-137
as repeatable, 113-115
security, 134
testing, 112-113
variables in, 126
parameters
adding to reports, 195-197
for Analysis Services queries, 292
in reports, 298
parent-child dimensions, 280. See also
self-referencing dimensions
creating, 282-285
parsing IIS log files, 313
partially additive measures. See semi-additive measures
partition functions, creating, 353-354
partition schemes, creating, 355
partitioned cubes, 362-366
managing, 372-375
partitioned tables, 35, 346-352
creating, 353-357
designing, 352-353
managing, 368-372
restrictions, 357
partitioned views (PVs), 376
partitions
Analysis Services, 48
for proactive caching, 388-390
in real-time business intelligence, 382
rolling partitions in real-time business
intelligence, 395-396
Perfmon. See System Monitor
performance (OLAP), 12-14
permissions, 34
perspectives, 286-288
pipeline architecture (Integration Services), 40
PivotTable views, adding to scorecard portal
site, 300-302
planning data warehouse deployment, 90-91
polling (notification method), 392
for importing data into data warehouse, 393
for proactive caching, 392
populating cube partitions, 372-373
prediction query builder (SQL Server
Management Studio), 335-336
predictions with data mining models, 339
preventing bad source data, 240-241
primary keys in partitioned tables, 350
proactive caching, 43, 380-382
enabling, 384-385
notification methods, 390-392
operational overview, 385-386
tuning, 386-390
process. See business process
processing
cube partitions, 372-373
cubes (OLAP), 11
product recommendations. See Association Rules algorithm
profitability analysis, 142, 166
project mode (BI Development Studio), 177
projects (Integration Services), 37
PVs (partitioned views), 376
Q
quality of data. See data quality
querying OLAP databases, 11-12
Quick Start exercises
adding
KPIs to reports, 293-294
parameters to reports, 195-196
reports with web parts, 298-299
connection manager setup, 107-108
creating
Analysis Services cubes, 152-153
Analysis Services partitions, 364-366
association rules data mining model, 332-333
cluster data mining model, 323-324
data flow configuration, 110-111
defining KPI, 288-290
dimension table creation, 81-82
Execute SQL tasks, 114-115
implementing inferred members, 234-236
merging
cube partitions, 373
lists with fuzzy matching, 222-227
partitions for proactive caching, 389-390
prediction query builder usage, 335-336
processing file sets, 126-127
read access to data warehouse views, 88-89
redirecting failing lookup data, 230-231
report design, 190-191
Shipment Details fact dimension creation, 167-168
Slowly Changing Dimension transformation, 253-254
subscribing to reports, 206-207
Unpivot transformation, 122
R
ranges, grouping numbers into, 320
RDL (Report Definition Language) files, 190
read access to data warehouses, 88-89
real-time business intelligence, 379
backing up, 395
dimensional modeling for, 382-383
importing data into data warehouse, 393-394
loading data directly, 396
Notification Services, 397
proactive caching
enabling, 384-385
notification methods, 390-392
operational overview, 385-386
tuning, 386-390
problems with multidimensional databases
and data warehouses, 380
proactive caching as solution, 380-382
rolling partitions, 395-396
rebuilding dimension tables, 255
recovery model setting, changing, 91
referential integrity (RI) in data warehouses, 86
Relational OLAP (ROLAP), 14
MOLAP versus, 384-386
relational tables, Analysis Services databases
versus, 275
between attributes, 160-162
between cubes and dimensions, 163-164
between dimension and fact views, 149-151
between dimensions, 75-76
many-to-many, 281
setup, 285-286
one-to-many, 279
renaming clusters (data mining), 327
repeatable packages, 113-115
replication, 34
report caching, 205
Report Definition Language (RDL) files, 190
Report Explorer web part, 299
report models, 53
Report Viewer web part, 297
Reporting Services, 48, 179-184. See also reports
accessing reports, 50-51, 193-195
adding
calculations to reports, 192-193
code to reports, 212
new reports, 210
parameters to reports, 195-197
architecture of, 48-49
backing up, 211
canceling long-running reports, 211
changes to reports, 210
charts, 52
database access, 204-205
datasets, 51
deploying, 209-210
reports to server, 193
dimensional modeling for, 184-189
drilling down reports, 200-201
drilling through reports, 201-202
end-user reporting, 53-54
interactive reports, 51-52
localization, 213
managing, 54-55
Report Builder, 212
report subscriptions, 54
reports in scorecard applications, 290-291
adding charts, 295-296
adding KPIs, 293-295
parameters for Analysis Services
queries, 292
as server-based, 181-182
snapshots, 270
subscribing to reports, 205-208
summary information, 197-198
types of reports, 198-200
web parts for adding reports to scorecard
portal site, 297-300
Reporting Services Configuration Manager, 55
reports, 179-184. See also Reporting Services;
scorecard applications
adding
calculations to, 192-193
code to, 212
new reports, 210
parameters to, 195-197
to scorecard portal site, 297-300
against OLTP databases, 182-184
backing up, 211
building on relational tables versus Analysis
Services databases, 275
canceling long-running reports, 211
changes to, 210
charts in, 52
database access, 204-205
deploying to server, 193
dimensional modeling for, 184-189
drilling down, 200-201
drilling through, 201-202
integrating into Web sites, 194
interactive reports, 51-52
linked reports, 298
localization, 213
in scorecard applications, 290-291
adding charts, 295-296
adding KPIs, 293-295
parameters for Analysis Services
queries, 292
security, 202-204
shared data sources, 192
snapshots, 270
summary information, 197-198
types of, 198-200
reprocessing data mining models, 338-339
resource constraints, 348
restartable packages, 136-137
restoring data warehouses, 95
restrictions on partitioned tables, 357
RI (referential integrity) in data warehouses, 86
ROLAP (Relational OLAP), 14
MOLAP versus, 384-386
role-playing dimensions, 163
roles
Analysis Services security, 45, 169
package security, 134
rolling partitions in real-time business
intelligence, 395-396
Row Count transform, 239
row counts, auditing data quality, 239
rows, converting columns to, 121-123
running packages, 42
in production, 135
in transactions, 136
S
scalability
Analysis Services, 47
SQL Server database engine, 35
SCDs (slowly changing dimensions), 8, 245-249
Analysis Services and, 263-266
backups, 268
changing dimension tables for, 256-257
deployment, 267
detecting changes in source data, 260-261
processing in Analysis Services, 267-268
Slowly Changing Dimension transformation, 253-259
translating business keys to surrogate keys, 261-263
Type 1 SCD, 249
Type 2 SCD, 250-252
for wide dimension tables, 263
scheduling jobs, 33
scope of package variables, 126
scorecard applications, 271-275
Analysis Services database
KPIs (key performance indicators), 288-290
many-to-many relationships, 285-286
parent-child dimensions, 282-285
perspectives, 286-288
security, 303-305
setup, 281-282
backing up, 306
Business Scorecard Manager, 307
dimensional modeling, 275-281
FrontPage, 307
Microsoft Office 2007, 307
Reporting Services reports, 290-291
adding charts, 295-296
adding KPIs, 293-295
parameters for Analysis Services
queries, 292
WSS (Windows SharePoint Services), 295-297
adding PivotTable views to site, 300-302
adding reports to site, 297-300
creating team site, 296-297
deployment, 302-303
security, 305
scripting in SQL Server Management Studio, 268
security
in Analysis Services, 45, 169, 303-305
for Analysis Services administrators, 171-172
for data warehouses, 87
application access, 89-90
database adminstrator access, 87
user access, 88-89
packages, 134
Reporting Services, 55, 202-204
SQL Server database engine, 33-34
WSS (Windows SharePoint Services), 305
segmentation with clusters (data mining), 322
self-referencing dimensions, 185, 280. See also
parent-child dimensions
semi-additive measures, 7, 75, 189
Sequence Clustering algorithm, 339
sequence numbers, detecting changes in source data, 260
sequence of dimension members, changing, 284-285
server-based solution, Reporting Services as, 181-182
servers, deploying reports to, 193
Service Broker, 61
shared data sources for reports, 192
silence interval (proactive caching), 386
silence interval override (proactive caching), 387
similarity rank (fuzzy matching), 221
Slowly Changing Dimension transformation, 253-259
slowly changing dimensions (SCDs), 8, 245-249
Analysis Services and, 263-266
backups, 268
changing dimension tables for, 256-257
deployment, 267
detecting changes in source data, 260-261
processing in Analysis Services, 267-268
Slowly Changing Dimension transformation, 253-259
translating business keys to surrogate keys, 261-263
Type 1 SCD, 249
Type 2 SCD, 250-252
for wide dimension tables, 263
snapshot isolation for proactive caching, 393, 396
of reports, 270
snowflake design, 16-17
in data warehouse dimensional model, 69-70
slowly changing dimensions (SCDs) and, 261
star design versus, 17-18
source control
Analysis Services databases, 172
data integration process, 128
spreadsheet loading, 119-125
SQL Profiler, 32
Analysis Services and, 45
SQL Server
components of, 29-30
deploying, 31
data warehouses, changing default settings, 91
full-text search, 60
maintenance plans for data warehouses, 92-93
Notification Services, 61
Service Broker, 61
SQL Server Agent, 33
SQL Server Analysis Services. See Analysis Services
SQL Server database engine, 31
availability, 34
job scheduling, 33
management of, 32-33
scalability, 35
security, 33-34
very large database support, 35
SQL Server Destination, 112
SQL Server Integration Services (SSIS). See
Integration Services
SQL Server Management Studio, 29
Analysis Services and, 45-46
prediction query builder, 335-336
Reporting Services and, 54-55
scripting actions, 268
SQL Server notification, 391
SQL Server Reporting Services. See Reporting Services
SSIS (SQL Server Integration Services). See
Integration Services
staging database (data warehouses), 8
snowflake design versus, 17-18
storage of data warehouse files, 91
strategy. See scorecard applications
subscriptions to reports, 54, 205-208
subtotals in reports, 197-198
success of BI projects, measuring, 27
summary information in reports, 197-198
support (data mining), 330
surrogate keys, 18-19
adding to dimension tables, 78
in partitioned tables, 350
referential integrity (RI) and, 86
translating business keys into, 117-120, 229-231, 261-263
synchronization, Database Synchronization, 376-377
synonyms in dimensional data models, 20
System Monitor, 32
T
team site (WSS), creating, 296-297
test data, loading, 22
testing
calculating measures, 167
packages, 112-113
subscriptions, 207
Time dimension (data warehouses), 7
in cubes, 154
relationships in, 160-162
building dimension table for, 83
in data warehouse dimensional model, 71-73
measuring elapsed time, 276-277
time intelligence, adding to cubes, 266
time stamps, detecting changes in source data, 260
transaction sequence numbers, detecting
changes in source data, 260
transaction systems, OLTP (on-line transaction
processing), 2-4
transactions
running packages in, 136
snapshot isolation level, 393, 396
transformations, 40
fuzzy matching transforms, 220-228
missing dimension members, 217
translations in Analysis Services, 307
triggers, detecting changes in source data, 260-261
truncation errors, 228-229
tuning with proactive caching, 386-390
Type 1 SCD, 249
Type 2 SCD, 250-252
U
UBO (Usage Based Optimization) Wizard, 374
UDM (Unified Dimension Model), 46-47, 144
unexpected values, data quality issues, 243
unique key constraints, 79
in partitioned tables, 350
Unpivot transforms, 40, 121-123
updated facts, 269-270
updating views for Analysis Services
databases, 173
Usage Based Optimization (UBO) Wizard, 374
user access to data warehouses, 88-89
user ID, as report parameter, 197
user-defined types, 80
V
variables in packages, 126
versioning data warehouses, 92
very large databases (VLDBs). See VLDBs (very large databases)
views
for clusters (data mining), 325-329
creating for data warehouses, 85
DSV (data source view), 149-151
partitioned views (PVs), 376
updating for Analysis Services databases, 173
VLDBs (very large databases), 343-349
aggregations, designing, 366-367, 374-375
automated processing, 375
backing up, 371-372
cube partitions, 362-366
managing, 372-375
data flow, 358-362
Database Synchronization, 376-377
fact tables in, 349-351
initial loading, 368
large dimension support, 367-368
partitioned tables, 352
creating, 353-357
designing, 352-353
managing, 368-372
restrictions, 357
partitioned views, 376
W-Z
Web applications, adding data mining to, 334-337
web parts, 275
adding reports to scorecard portal site, 297-300
Web sites, integrating reports, 194
Windows authentication, 33, 87
Windows Management Instrumentation (WMI), 394
Windows SharePoint Services (WSS), 273, 295-297
adding PivotTable views to site, 300-302
adding reports to site, 297-300
creating team site, 296-297
deployment, 302-303
security, 305
XMLA (XML for Analysis), 43
XMLA messages, 391-392
zero values, data quality issues, 243