Database Reference
In-Depth Information
Figure 3-23.
Planning the data warehouse
Our naming conventions may be different from what you are accustomed to, but every company will have
their own preferences. Whatever the preferences are, your documentation should reflect this.
In Exercise 3-3, you examined each table in the pubs database and determined which columns to include
and which to omit. These can now be listed in the worksheet (Figure
3-23
).
After the objects are listed, it can be helpful to list their description, source, source datatype, and destination
datatype as well. We have found these columns to be quite useful when building the data warehouse. In fact, we
use this list to build the data warehouse in Chapter 4.
We can then continue to list the objects in the SSIS ETL project, the SSAS cubes project, and the SSRS reports
project. The list does not have to be perfect. It is simply a way of getting started. It has been our experience that
we always miss something in the initial documentation anyway. We recommend that you use the spreadsheet
as the initial documentation and update it as you find omissions and mistakes. Later, at the end of the solution
development cycle, you can update the formal document that was created using Microsoft Word. Another, better
option is to get a technical writer on your team who can update this to the formal document as you go. It is this
formal version that is submitted to your client along with other documents throughout the course of your project
such as your initial contract, changes to your contract if there are any, technical notes, observations, billing
documents, any specific items requested by the client to be submitted, and any thank-you messages. For more
information about formal documentation, see Chapter 19.
Implementation
Once you have formulated a plan of action, you must perform that action. In the case of a SQL Server BI solution,
that means creating a data warehouse and several Visual Studio projects. As we saw in Chapter 2, one Visual
Studio solution can hold many projects and even solution documents. So, perhaps the first thing to do is to create
a solution in Visual Studio and add our documents to it. Let's do that now.
