Exam Reviews

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.

• By Patricia Pickup
• 01/01/2000

I 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 be applied.

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 requirement listed.

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 it is.

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 are.

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.

Hands-on

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.
• Indexing.
• 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.