Making Sense of Data
The Data Warehouse exam challenges traditional notions of data management and pushes you to excel in your SQL 7.0 knowledge.
suspect if you’re considering this exam, you’ll tackle it
after trying at least one of the other two SQL Server exams,
70-028, System Administration, and 70-029, Implementing
a Database Design. Those have been around longer and are
required for the MCDBA certification. The data warehousing
exam is an elective.
Still, the knowledge you gain in preparing yourself for
those tests will stand you in good stead for this one
too. For instance, for the SQL Admin exam, you need to
understand filegroups and how they can be used to affect
SQL performance, backup and recovery strategies, and roles
and ways of implementing security.
Tip: I recommend this exam
for MCDBAs. You’ll find that it builds on your work from
the two required SQL exams while providing a unique perspective.
When studying for the other tests,
make sure to include the topic of replication. The actual
details of replication aren’t as important as the knowledge
of the different types of replication and when each should
Rating: “The focus of this exam
is analysis of data warehousing. Unlike
most other Microsoft exams, it focuses
on technology and analysis that isn’t
specific to Microsoft’s implementation.”
Designing and Implementing
Data Warehouses with Microsoft SQL Server
Number of questions
in reviewed version of exam:
100 in the beta; about half that on
3 hours for beta; expect around 90 minutes
for live exam.
Live exam expected
Who should take
it? Counts as elective credit
toward MCSE, MCSD, and MCDBA.
prepare you? Course 1502—Designing
and Implementing a Data Warehouse with
SQL Server; 5 days, instructor-led.
Analyzing Business Requirements
Many of the questions on this exam
are wordy. Easily a fifth of the skills it measures begin
with the word “analyze,” so perhaps that should be expected.
Each question is independent of previous questions. Don’t
get me wrong. This exam isn’t like 70-100, the MCSD core
Analyzing Requirements test, where you might be required
to read a single long case study before answering a dozen
questions pertaining to that business situation. Portions
of the questions, however, often are repeated.
For example, a typical question begins
by describing the computing environment for some fictional
company, let’s say, Pizza, Inc. Perhaps most of the Pizza,
Inc. employees are travelers who carry laptops and connect
to the company network once or twice a day, or maybe the
company has five locations connected by T1 lines. Pizza,
Inc. needs a data warehouse or a data mart or some type
of OLAP solution. A paragraph of supporting details describes
that need. Related requirements could include a high need
for security or for speedy user access. In addition, Pizza,
Inc. may have some problems or restrictions, such as a
narrow timeframe for loading data into the data warehouse
or an inability to upgrade the network hardware.
The exam offers a proposed solution;
you’ll need to check off which requirements were met.
You should select all that apply even if that means every
The description of the company environment
and the company’s needs may be exactly the same for the
next several questions. In this case, the proposed solution
will be different and will meet a different set of requirements.
Don’t be confused by this—read the questions carefully
to make sure requirements really are the same. As much
as this exam tests your knowledge of data warehousing
principles and techniques, it also checks your reading
comprehension and ability to analyze.
Tip: Don’t assume that the
solution doesn’t meet the requirements just because it’s
not an OLAP solution. Sometimes the suggested solution
uses standard T-SQL rather than a cube or pivot table.
The key to these questions is knowing
what the trade-offs are for any solution. Not only do
you need to know the types of replication, but also when
one type is better than another. When is MOLAP better
than ROLAP? Is it faster? Is it more secure? Does it use
fewer resources? Is it easier to implement? Is it more
likely to guarantee database consistency?
Most of these questions have more to do with data warehousing
than with SQL Server. You might come across questions
about relational databases or OLAP products in general
that aren’t specific to Microsoft’s implementation of
them in SQL 7.0. That’s because SQL Server isn’t the only
relational database these questions can apply to, nor
is it the only OLAP tool out there.
Tip: Other OLAP tools may
not give you the flexibility that SQL Server does to choose
your OLAP architecture (MOLAP, ROLAP, or HOLAP) or to
change it, but the benefits and disadvantages of one architecture
over another are the same no matter whose implementation
SQL Server 7.0 Database Implementation
Although it’s handy to know the difference
between various types of normalized forms, it’s even more
important to clearly understand the differences among
schemas: normalized, de-normalized, star, and snowflake
(see “Database Theory”). Each is appropriate in certain
situations, so you need to know what those situations
Microsoft’s MCP Web site lists about
50 or so specific skills measured by exam 70-019. Of these,
three specifically mention SQL statements. Make sure you
understand the types of statements that are frequently
used in OLAP applications, such as SELECT statements that
aggregate data with the COMPUTE BY or ROLLUP operators.
It isn’t enough to know that SQL has this pseudo-OLAP
functionality; to ace these questions, you need to know
the exact syntax. If you passed the Implementing a Database
Design exam, you should have no problem with this part
of the test.
Actually, if you passed Implementing
Databases for SQL 6.x.—SQL is SQL—you should have no problem
with this part. One surprise to me was the dearth of coverage
on Multidimensional Extensions to SQL (MDX), which are
new with SQL 7.0. Although MDX isn’t explicitly mentioned
by the exam guidelines as a skill measured by this exam,
I “read into” some of the other skills listed. After all,
MDX is definitely within the topic of data warehousing,
and it’s a significant topic in course 1502, which is
recommended as preparation for this exam.
You should know how to use the following
methods to connect to and manipulate a data source:
- OLE DB
- ODBC (Open Database Connectivity)
- ADO (ActiveX Data Objects)
- ADO MD (ActiveX Data Objects Multidimensional)
And it’s not enough for you to have a theoretical knowledge
of how to connect to a database, such as understanding
the relationship between OLE DB and ADO. You also need
to know the exact syntax. That knowledge can’t really
be tested with just the language of SQL Server, T-SQL;
it requires Visual Basic. In spite of VB’s ubiquity in
the Microsoft world in general, I was taken aback by the
strong presence of VB code on the exam. After all, VB
doesn’t get listed as one of the measured skills or as
a prerequisite. In many organizations, developers are
either “front-room,” specializing in VB, or “back-room,”
specializing in SQL Server. I’d advise you to review VB
programming before going to the test center. Be prepared
to evaluate brief code snippets of 10 or 12 lines and
figure out whether or not they’ll connect you to and allow
you to manipulate a data source.
Tip: Be familiar with the
object hierarchies for the APIs listed above. For example,
which of the above APIs includes the object cellset? What
object is the parent of cellset? Which APIs listed above
have the connection object? If you can’t answer these
types of questions, you have more studying to do.
For the exam, you should also be familiar with the object
hierarchy for OLAP Services, also known as Decision Support
Objects or simply DSO.
As with most Microsoft exams, hands-on
experience is the best teacher. If you currently have
an OLTP database, create one or more star schema from
that database and an OLAP cube from those schema. Or use
the sample PUBS database that comes with SQL Server. If
you create only sample OLAP applications, however, you
may overlook performance, which is often an issue with
OLAP applications. Make sure you know how to optimize
query and data loading speeds, a central issue. Here are
some techniques that can be used to optimize speed:
- Use of aggregate tables can have the totals by some
dimension and may use the same dimension tables as the
lower grain fact tables.
- Set no logging option for bulk copy or for “select
into” SQL statements.
For the exam’s design aspects, be familiar
with SQL 7.0’s data diagramming tool. I don’t necessarily
mean simply how to use it, but rather how to read the
diagrams and what they mean.
Data warehousing has been a popular topic in the IT trade
press for some time, and there’s a reason. I would recommend
this exam for anyone with the title of SQL Server DBA
or SQL Server developer, regardless of whether you’re
implementing data warehouses. In fact, I probably would
recommend it more for those who don’t have OLAP experience.
Studying and preparing will give those with experience
only with OLTP systems a new perspective on the rules
for good design.