Throughout the remainder of this book, you will get to know Reporting Services by exploring a number of sample reports. These reports will be based on the business needs of a company called Galactic Delivery Services (GDS). To better understand these sample reports, here is some background on GDS.
GDS provides package-delivery service between several planetary systems in the near galactic region. It specializes in rapid delivery featuring same-day, next-day, and previous-day delivery. The latter is made possible by its new Photon III transports, which travel faster than the speed of light. This faster than light capability allows GDS to exploit the properties of general relativity and actually deliver a package on the day before it was sent.
Despite GDS’s unique delivery offerings, it has the same data-processing needs as any more conventional package-delivery service. It tracks packages as they are moved from one interplanetary hub to another. This is important not only for the smooth operation of the delivery service, but also to allow customers to check on the status of their delivery at any time.
In order to remain accountable to its clients and to prevent fraud, GDS investigates every package that is lost en route. These investigations help to find and eliminate problems throughout the entire delivery system. One such investigation discovered that a leaking antimatter valve on one of the Photon III transports was vaporizing two or three packages on each flight.
GDS stores its data in a database called Galactic. Figure 3-20 shows the portion of the Galactic database that stores the information used for package tracking. The tables and their column names are shown. A key symbol in the gray square next to a column name indicates that this column is the primary key for that table. The lines connecting the tables show the relations that have been created between these tables in the database. The key symbol at the end of the line points to the primary key column used to create the relation. The infinity sign, at the opposite end of the line to the key symbol, points to the foreign key column used to complete the relation. (The infinity sign looks like two circles or a sideways 8.)
Figure 3-20: The package tracking tables from the Galactic database
Each relation shown in Figure 3-20 is a one-to-many relation. The side of the relation indicated by the key is the “one” side of the relation. The side indicated by the infinity sign is the many side of the relation. For example, if you look at the line between the Customer table and the Delivery table, you can see that one customer may have many deliveries.
You may wish to refer back to these diagrams as we create sample reports from the Galactic database. Don’t worry if the diagrams seem a bit complicated right now. They will make more sense as we consider the business practices and reporting needs at GDS. Also, our first report examples will contain only a few tables and the corresponding relations, so we will start simple and work our way up.
Every business needs a personnel department to look after its employees. GDS is no different. The GDS personnel department is responsible for the hiring and firing of all the robots employed by GDS. This department is also responsible for tracking the hours put in by the robotic laborers and paying them accordingly. (Yes, robots get paid at GDS. After all, GDS is an equal-opportunity employer.)
The personnel department is also responsible for conducting annual reviews of each employee. At the annual review, goals are set for the employee to attain over the coming year. After a year has passed, several of the employee’s co-workers are asked to rate the employee on how well they did in reaching those goals. The employee’s manager then uses the ratings to write an overall performance evaluation for the employee and establish new goals for the following year.
Figure 3-21 shows the tables in the Galactic database used by the personnel department. Notice that the Rating table has key symbols next to both the EvaluationID column name and the GoalID column name. This means that the Rating table uses a composite primary key that combines the EvaluationID column and the GoalID column.
Figure 3-21: The personnel department tables from the Galactic database
The GDS accounting department is responsible for seeing that the company is paid for each package that it delivers. GDS invoices its customers for each delivery completed. The invoices are sent to the customer and payment is requested within 30 days.
Even though GDS delivers its customers’ packages at the speed of light, those same customers pay GDS at a much slower speed. “Molasses at the northern pole of Antares Prime,” was the analogy used by the current Chief Financial Droid. Therefore, GDS must track when invoices are paid, how much was paid, and how much is still outstanding.
Figure 3-22 shows the tables in the Galactic database used by the accounting department. You will notice that the Customer table appears in both Figure 3-20 and Figure 3-22. This is the same table in both diagrams. It is shown in both, because it is a major part of both the package tracking and accounting business processes.
Figure 3-22: The accounting department tables from the Galactic database
In addition to all of this, GDS must maintain a fleet of transports. Careful records are kept on the repair and preventative maintenance work done on each transport. There is also a record of each flight that a transport makes and of any accidents and mishaps involved.
Maintenance records are extremely important, not only to GDS itself, but also to the Federation Space Flight Administration (FSFA). Without proper maintenance records on all its transports, GDS would be shut down by the FSFA in a nanosecond. You may think this is an exaggeration, but the bureaucratic androids at the FSFA have extremely high clock rates.
Figure 3-23 shows the transport maintenance tables in the Galactic database.
Figure 3-23: The transport maintenance tables from the Galactic Database