Database Reference
In-Depth Information
We have selected techniques to present in this chapter in order to demonstrate the solution
for the most common challenges that students bring up in class. These challenges involve the
ability to ensure that all items are counted in all warehouses even if none are currently available
and the ability to perform data audits when building data warehouses.
In our first example, we will demonstrate the usage of query unions to overcome the con-
straints of the reporting package. Our second example will use query joins to create relationships
between data elements that were not defined in the model.
NOTE
If you want some help with the files and typing involved in this chapter, go to
www.ibmpressbooks.com/title/9780132656757
and download the supplements.zip file
from the Downloads section under
More Information
.
Yo u r customer wants a report to view the inventory at each location to help with the monthly
audit. The modeler defined a relationship such that products will be listed only if they are avail-
able at the warehouse location. The report should have a list of all the products in the ficticious
Great Outdoors Company catalog, not just the items that are in inventory. This will help track the
items that are available and the ones that are not available for distribution.
The report will prompt the user for a month and year. The prompt will also allow the report
consumer to view reports for all warehouses or for a specific warehouse.
After the prompt, the title will display the warehouse location. The products available will
be placed into sections by the product types. The detail lines will contain a column for the prod-
uct, one for the stated inventory, and one final column to allow for inventory audit staff to write
the number on hand.
The final report should match what's shown in Figure 4.1.
The model is designed so that you see inventory on hand at a given location because the mod-
eler created an inner join between the facts (inventory numbers) and the dimensions (product
information).
It will not allow us to also see the items that are not in inventory.
Instead of asking the modeler to change the model, we are going to create two queries: one
that gives us inventory on hand and one that gives us all the products. By doing the union, we will
be showing all products whether or not they are on hand.
This is a common report query challenge that students describe in class. The problem is an
inability to report on certain items or events that did not happen (i.e., there was no sale, or there
were no events).
