A P P E N D I X A

  

431
Setting Up Adventure Works
Through most of this book, I work with a demonstration data set called AdventureWorks. It’s a
multidimensional data warehouse and Analysis Services project based on a fictional bicycle company,
which has both retail and Internet sales.
I really enjoy working with this data set. It’s true that it’s a bicycle store, and as I’ve often
commented, “just about wholly unrelated to anything anyone does.” However, although it may not be
related to the business of various SSAS users, it is something that just about everyone can understand.
If you’ve downloaded sample databases from Microsoft before, you’ve gotten them from
Microsoft.com. A few years ago, Microsoft moved all the samples to www.codeplex.com, their open source
community, so they would be covered by the open source licensing there.
The sample databases are located at the following site:
www.codeplex.com/MSFTDBProdSamples
Click the Downloads link in the top-right corner. Then download
SQL2008.AdventureWorks_All_Databases—either x64 or x86, depending on the architecture you’re
running. (There’s a .zip file that has the same contents.) Then run the MSI on your SQL Server (Figure
A-1).
 Note CodePlex uses a Silverlight applet for the download prompt. So if you try to download from a locked-down
server, the link won’t do anything. To enable it, add
*.codeplex.com
to your trusted sites. Alternatively, download
the file from a desktop PC and copy it to the server.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
APPENDIX A  SETTING UP ADVENTURE WORKS
432

Figure A-1. Installing the samples

in place on the server.
To build the cubes necessary for reviewing some of the examples in the book, you’ll need to open
the OLAP projects and build the cubes (these were not automatically created by the wizard). Open
Tools\Samples\AdventureWorks Analysis Services Project. You’ll see two folders, enterprise and
standard. Choose the folder that matches the edition of SQL Server Analysis Services you have installed.
In that folder, you’ll find an Adventure Works.sln file; double-click that to open the solution in BIDS.
After you have the solution open in BIDS, you’ll need to make two changes. First, in the Solution
Explorer, double-click the AdventureWorks data source, and enter the server name for where you
installed the AdventureWorks DW database. Next, right-click on the solution (the topmost Adventure
Works in bold), and select Properties. Click Deployment in the list on the left. Find the Server entry
under Target and change that to the server name you want to deploy the cubes to when you process
them. Click OK.
Deploy the project, process the cubes (see Chapter 8), and you’re all set!

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
A P P E N D I X B

  

435
Data-Mining Resources
Books
Data Mining with SQL Server 2005, by ZhaoHui Tang and Jamie MacLennan (Wiley, 2005)
Data Warehousing, Data Mining, & OLAP, by Alex Berson and Larry Dubov (McGraw-Hill, 2007)
Delivering Business Intelligence with Microsoft SQL Server 2005, by Brian Larson (McGraw-Hill, 2006)
Foundations of SQL Server 2005 Business Intelligence, by Lynn Langit (Apress, 2007)
Web Sites
SQLServerDataMining.com:

www.sqlserverdatamining.com

304–305
creating data source view, 281
creating views in AdventureWorks, 278–
281
Data Mining Extensions, 303–310
Data Mining Model Designer, 289–303
predicting accessory buyers, 308–310
preparing data warehouse, 278–281
processing, DMX, 305–306
viewing mining model, 306–308
AccessoryCampaign DSV
creating, 281
creating data-mining model, 283
account attributes, assigning, 254
Account dimension, 137, 138
AdventureWorks project, 138, 139
browsing, 139
unary operators, 256
account intelligence, 254–255
account business intelligence, 139
choosing Standard/Enterprise SSAS, 45
unary operators, 255
Action Designer, 268, 269
action type, 269
actions, 34–35, 251, 265–270
drill-through actions, 265, 266
executing as part of process, SSIS, 345–
348
general actions, 268, 269
invoking via Excel, 266, 267

148
AdventureWorks schema, 120
AdventureWorks solution
creating data-mining model, 282
DSV from, 9, 10
Affected Objects section, Change Settings dialog,
336
aggregate functions, MDX, 241–245
Avg function, 241–242
TopCount function, 242–244
Aggregation Design Wizard
creating aggregations, 361
Review Aggregation Usage screen, 362
Set Aggregation Options screen, 363, 364
Specify Object Counts screen, 362, 363
Aggregation Designer, 71, 72, 361
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 INDEX
438
aggregation, OLAP
setting Unary Operator on parent attribute,
139
types of, 31–32
AggregationPrefix property, measure groups, 185
aggregations
additive measure, 32
Analysis Services, 359–367
attributes, 226
CALCULATE statement, 186
combining numerical data, 183

attribute relationship design, 353
authentication, 348–349
authorization, 348, 349–352
autoexists feature, 351
building attribute relationship in dimension,
151
CALCULATE statement, 186
combining numerical data, 183
connecting to database, 85
creating project, 82–84
cubes, 89–90
AMO design warnings, 135–136
automating cube processing, 339
BIDS, 60
processing, 333–335
data source views, 88–89
data sources, 87–88
connections from OLAP to Excel, 375, 389
data-mining structures, 90
DBA tasks, 333–348
design, 352–359
dimensions, 90, 353
example showing power of, 188
executing actions as part of process, SSIS,
345–348
facts, 183
hardware requirements, 41–42
importing database into new project, 86
interacting with SSAS, 75
key performance indicator, 11

synchronizing SSAS databases, 202–204
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 INDEX
439
task configuration, 346
unified dimensional model, 46–49
user-friendly interface for, 373
using DMX development environment, 303
using Excel writeback, 319
VertiPaq mode, 311, 320
Windows Integrated Authentication, 348
Analysis Services data table
building Excel report, 122
Analysis Services databases
deploying projects, 195
Analysis Services objects
processing with XMLA queries, 340
scheduling processing of, 343–345
SQL Server Agent, 344–345
Analysis Services Processing Task
scheduling OLAP maintenance, 215,
216
Analysis Services Project
creating data sources, 102
Analysis Services Server
connecting SSMS to, 366
Analyze tab
pivot charts, Excel 2007, 388
Ancestor function, MDX, 239
ancestors and descendants, 240

aggregation design, 362
attribute relationships, 150–156
creating dimension with, 152–156
creating from data source view, 149
creating tool tip with dimension attribute, 21
design, SSAS, 353–355
dimension types, 138
dimensions, 21
dimensions, SSAS, 148–156
editing full collection of properties for, 81
MDX notation, 222
mismatching types, 138
OrderBy property, 156
OrderByAttribute property, 156
parent-child attribute in dimension, 157
properties, 156
Select Dimension Attributes page, 129, 130
setting, 129
showing all attribute values for dimension,
150
attribute-store processing job, 336
authentication
Analysis Services, 348–349
credentials, 101
user authenticating against Web application,
349
Windows Integrated Authentication, 348
authorization
Analysis Services, 348, 349–352
double-hop problem, 348

Attribute Designer, 354
background, 8
browser, 12, 192
creating Analysis Services Project, 82–84
creating cubes, 170–178
creating data sources, 102
creating dimension, 127
creating DSV, 107–109
cube browser, 13
cube design wizard, 63
cube structures in SSAS, 59
data sources in Solution Explorer in, 98
database solutions, 82–86
deploying projects to development server
from, 195
dimension design, 69
KPI designer in, 12
measure groups, 182, 184
measures, 182
Measures pane, 182
methods for creating time tables, 160
New Project Wizard, 8
opening SSAS database, 84–85
as part of project, 85–86
panes, 76–79
Partitions tab, 356, 357
Perspective Designer, 271
processing Analysis Services objects from,
208–214
Properties pane, 80–82

147
browsers
BIDS browser, 12, 192
dimension browser, 139
Build tab properties
project properties dialog, 197
business intelligence see BI
Business Intelligence Development Studio see BIDS
Business Intelligence Wizard, 251, 252
ByAttribute/ByTable methods
processing dimensions, 353
 C
caching
pre-calculation, Analysis Services, 359
ProactiveCaching property, dimensions, 141
CALCULATE statement, 186
processing Analysis Services objects, 207, 208
calculated dimension members
calculated measures, 185
calculated measures, 33–34, 185–193, 251
calculated dimension members, 185
Calculations tab, Cube Designer, 259–262
creating, 190–193
query-scoped calculated measure, 236
session-scoped calculated measure, 236
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 INDEX
441
calculated members, creating, 186
Calculation Tools section, Cube Designer, 187, 262

SQL Server Reporting Services, 410–418
Children function, MDX, 234, 240
classification predictions algorithm, 276, 277
Clear Writeback Data action, 313
Client Initiated option, notifications
storage properties, dimensions, 142
Clustering algorithm, 276
Codd, E.F., 7
CodePlex
setting up Adventure Works, 431
Color Expressions section, Cube Designer
creating calculated measure, 191
Column Content Specify dialog
creating data-mining model, 287
composite key, 169
conditional formatting for values
pivot tables, Excel 2007, 383, 384
Config.ini file
changing location, 54
configsettings file, SSAS, 202
configuration files for SSAS solution, 201
Configuration Manager button, Properties dialog,
196
configuration properties
Specify Configuration Properties screen, 200,
201
Connect to Database dialog
opening SSAS database, 84, 85
Connection Manager
creating data sources, 103

Dimension Usage tab, 314
enabling/disabling Excel writeback, 313
key performance indicators, 263–265
named sets, 262
perspectives, 270–272
translations, 272–273
Cube Editor, Dimensions pane, 81
opening Dimension Editor from, 82
Cube menu
creating calculated member, 186
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
 INDEX
442
cube specific tabs, BIDS 2008, 77
cube structures, SSAS, 59–65
cube structure, 63–64
data source view (DSV), 61–62
data sources, 61
dimensions, 64
mining structures, 65
Cube Wizard
Create an empty cube option, 170
creating cubes, 170
Generate tables in the data source option,
170
Select Creation Method page, 174
Select Existing Dimensions page, 173, 176
Select Measure Group Tables page, 170, 171,
174, 175
Select Measures page, 171–172, 175, 176

relating measures and dimensions, 179–
181
selecting dimensions, 173, 176
folders for, 57
from pivot tables to dimensional processing,
4
hierarchies, 25
identifiers (members), 16
linking data and parameters, 16
managing Analysis Services, 89–90
measure groups, 63, 184–185
selecting measure group tables, 170–171,
174, 175
measures, 17, 18, 27–31, 63, 182–184
creating skeleton dimensions and
measures, 170
relating measures and dimensions, 179–
181
selecting measures, 171–172, 175, 176
metadata locations, 55
partition folders, 57–58
performance management, SSAS, 352–371
Process dialog, 208
processing
Analysis Services, 206, 207, 333–335
automating, 339
reprocessing, 340
scheduling, 343
relational databases and, 18
schemas, 18–20