Database Reference
In-Depth Information
At an absolute minimum, you need the information found in the sales table. now, let's look at what else to
include.
In addition to the sales information, you need descriptors of each sale. These descriptors are also referred to
as
dimensional attributes
. Start by reviewing the other tables associated with the sales table; then determine
whether the data inside these tables are a Must Have, a nice to Have, or a not needed item. You can use the
four-quadrant prioritizing technique we discussed earlier in this chapter (Figure
3-2
).
The following steps take you through each of the tables and outline facts that help determine what to
include:
1.
Examine the dbo.Stores and dbo.Titles tables. These tables include information
about which books are being sold to which stores. Therefore, both stores and titles
need to be included. These tables provide a great deal of value and are easy to
implement. These are easily determined to be a Must Have.
2.
Examine the dbo.Publishers table. Because this fictitious company happens to
wholesale books from many publishers, we also need the publisher information.
The publisher's information is easy to obtain from this table and is of great use.
Therefore, dbo.Publishers is categorized as a Must Have.
3.
Examine the dbo.Authors table. Information about which authors write which books
may not be as important to the sales reports as the name of the titles may be, but
it would be nice to know. Therefore, you might consider including information about
the authors as well. This information is easy to obtain and provides somewhat useful
information. Therefore, dbo.Authors should be classified as nice to Have.
4.
Examine the dbo.Employee table. For some reason, the database records information
about the employees known to work at various publishers. This seems to have little
to do with the event of making a sale. The information is easy to obtain but of little
value. As such, dbo.Employee qualifies as not needed.
5.
Examine the dbo.Jobs table. Oddly enough, the database has information about
which jobs are held by publisher employees. Exactly how this information was
obtained is unimportant. What is important is that it is not necessary to our data
warehouse design. So, let's classify dbo.Jobs as a not needed item.
6.
Examine the dbo.TitleAuthors table. It tracks the royalty percentages given to various
authors in the dbo.TitleAuthors table and the order in which each author is listed on
each book in this same table. Royalties may have little to do with making a sale, and
we doubt that this company—which is acting as a middleman wholesaler—even
finds it useful. The order in which the authors' names appear can have an impact
on the sales of the book, because only the first author's name may appear in certain
listings. The important factor to consider is that this table represents a many-to-
many relationship between dbo.Titles and dbo.Authors. Therefore, dbo.TitleAuthors
is required to enable our solution to map the relationship between these tables
effectively. We must classify this table as a Must Have item.
7.
Examine the dbo.RoySched table. The royalty schedule table, dbo.RoySched, tracks
ranges of royalties based on the amount of books sold. As more books are sold,
the royalties to the authors increase. Although this influences profit, our BI solution
