CHAPTER
14
Additional Star Schema Designs
This chapter provides some additional star schema designs for common functions of the organization, such as inventory management analysis, purchase order analysis, logistics/shipment analysis, work effort analysis, and financial analysis.
Each of the Universal Data Model chapters (Chapters 3 through 9) have corresponding data analysis or star schema designs that can help analyze data. Chapter 2, which covers parties, provides constructs that are used in many of the star schemas such as CUSTOMER, SUPPLIER, INTERNAL ORGANIZATION, FACILITY, and so on. The following illustrates the corresponding data model chapter for the star schemas defined in this chapter.
Chapter 3, which includes inventory management models, corresponds to the inventory management star schema of this chapter.
Chapter 4, which includes purchase order and purchase order item models, corresponds to the purchase order star schema of this chapter.
Chapter 5, which includes shipment models, corresponds to the shipment star schema of this chapter.
Chapter 6, which includes work effort models, corresponds to the work effort star schema of this chapter.
Chapter 7, which includes invoicing models, corresponds to the customer invoices star schemas in chapter 12, Figures 12.1-12.4.
Chapter 8, which includes accounting and budgeting models, corresponds to the financial analysis star schema of this chapter.
Chapter 9, which includes human resources models, corresponds to the human resources star schemas in Chapter 13, Figures, 13.1, and 13.2.
For each star schema, common questions are provided that are useful starting points for discussions of data warehouse requirements. Template measures and dimensions are provided in each diagram, and the questions may be used as sample data warehouse questions to facilitate discussions about what data warehouse users may need.
Figure 14.1 provides a template star schema design to analyze inventory items. The design assumes that the enterprise manages inventory by part and that a part may be a raw material, subassembly, or finished good, as shown in Chapter 3 (V1:3.10b). If parts are not tracked and the enterprise only manages their goods, then substitute GOOD for PART. The information for this star schema can be extracted from a combination of entities in Chapter 3 and Chapter 5.
Figure 14.1 Inventory management star schema-based on information in the product and shipments data models.
The geo level and org levels in the dimensions provide for flexible hierarchies of geographies and organization rollup structures. For instance, the geo level may indicate city, state, or country, or it may be changed at a later date to state, country, continent. The level_org_type columns were explained in Figure 13.2 only they are now applied to the INTERNAL ORGANIZATIONS instead of the ORGANIZATIONS. The org levels may be department, division, and parent company, and they could also change as time goes by. This convention is used for subsequent star schema designs in this chapter.
Figure 14.1 is designed to handle the following types of questions:
- How much inventory does the enterprise have of different types of parts (or goods if the enterprise just tracks goods)?
- How much has been committed for use?
- How can the enterprise optimize inventory levels and store what is needed and where it is needed? How can the enterprise forecast what we need?
- What is the status of inventory in various locations? How much inventory has been shipped, received, and issued over time for various locations and for various types of goods?
- How much inventory of what types of parts has been scrapped in various facilities over time?
- What is the count and item valuation of inventory items?
- What are the trends over time for inventory balances and needs for inventory by various facilities and internal organizations?
- What are the inventory costs by facility and by internal organization?
Figure 14.2 provides a template star schema design to analyze purchase orders. The information from this design is sourced from implementations of the PURCHASE ORDER and PURCHASE ORDER ITEM entities described in Chapter 4.
Figure 14.2 Purchase order star schema-based on information in the order chapter.
The purchase order star schema is designed to handle the following types of questions:
- What types of products are being ordered by various internal organizations?
- How many purchase orders of what types of products and for how many items occur over time?
- What are the average prices for different types of products from different suppliers?
- What types of discounts have been offered by various suppliers for different types of products?
- What suppliers have offered the best prices for different products over time?
- How prompt are various suppliers, as measured by the average number of days later than they have promised for their purchase orders?
- What are the expected lead times for various products, as measured by the history of the average number of days that it took for various products to be delivered?
- What buyers are more effective at negotiating better prices and discounts for various products?
- How many purchase orders are back-ordered for various types of products by different suppliers?
- Which cost centers have had the greatest purchase order amounts allocated to them?
- What is the breakdown by product of the amounts spent within each cost center?
Figure 14.3 provides a star schema to analyze shipments and help organizations analyze their logistics operations. The data that is analyzed is sourced mainly from the SHIPMENT ITEM entity and its related information. A key benefit to this shipment analysis star schema design is that it provides the ability to analyze all types of movements of goods including customer shipments, purchase shipments, transfers, drop shipments, and any other SHIPMENT TYPE. The star schema allows the enterprise to assess the following questions:
- How well are shipment delivery expectations being met?
- How many damaged or rejected shipments are being received or delivered (via the quantity_damaged and quantity_rejected measures)?
- How many shipments are late by which carriers? (A late shipment occurs when the shipment received date is greater than the promised order shipment date.)
- How many shipments are rejected for which types of goods for which carriers?
- How many shipments are damaged for which types of goods for which carriers?
- What have been the freight charges (freight_amount measure) for various types of shipments for various carriers to and from various locations?
- Which people responsible for the shipments have been most effective at keeping the freight costs lower and minimizing rejected shipments?
Figure 14.3 Shipments star schema–based on the information in Chapter 5.
Figure 14.4 provides a work effort analysis star schema to analyze how effective various work efforts are, such as production runs, repair efforts, consulting engagements, internal projects, or any other type of work efforts as described in Chapter 6. The work effort star schema is sourced from the WORK EFFORT entity and its related information.
Figure 14.4 Work effort star schema-based on information in the work effort chapter.
The types of questions that are meant to be answered by this data analysis design are the following:
- How successful were our work efforts based on on-time, on-budget performance?
- What was the performance for various types of parties such as for project managers and/or workers?
- What was the average amount of time spent on various work efforts? (Keep in mind that work efforts were defined at multiple levels. For instance, they may be plans, projects, phases, activities, tasks, and so on.)
- Which facilities have had the best record of keeping their labor and materials costs down for various types of work efforts?
- How have actual labor, material, and total costs compared to the estimated costs for these items?
- How have costs for various types of work efforts changed over time?
- How long do various types of work efforts take to complete by facility and by the project manager?
- How many work efforts for what purposes have been completed at which facilities?
Figure 14.5 provides a template star schema design to analyze accounting transactions from Chapter 8 and is based on the summarized ACCOUNTING TRANSACTION instances. The ACCOUNT_BALANCES star schema allows balance sheet and income statement account analysis by GENERAL_LEDGER ACCOUNTS, by the INTERNAL ORGANIZATIONS that are being analyzed, by the LOCATIONS (for various country and continent analysis), and over TIME_BY_MONTH.
Figure 14.5 Financial analysis star schema—based on information from Chapter 8, and specifically accounting transactions.
This financial analysis star schema answers questions such as these:
- How do actual revenues compare to planned revenues?
- How can one graphically depict the financial performance of various business units (an example type of org level) over time?
- How well are various departments (an example org level) controlling their costs against their allocated budgets?
- What significant variances occurred for income and expense accounts (GENERAL LEDGER ACCOUNTS) from period to period that may flag possible business concerns?
- What trends are apparent in the area of accounts receivable (a type of general ledger account)? What indications do these trends have for increasing or decreasing the expense of sales (another type of general ledger account)?
- How can cash flow trends be used to predict how much capital is available for business expansion?
- What trends exist over time in key financial ratios such as debt to equity, current assets, and liquidity ratio? (These can be calculated via formulas applied to various general ledger accounts and could also be included as additional measures even though the information is derivable with the current star schema.)
This chapter has provided some examples of Universal Data Models for jump-starting the design of data marts for inventory management analysis, purchase order analysis, logistics analysis, work effort analysis, and financial analysis.
Many other star schema designs may apply to enterprises, and this chapter has provided a sampling of some of the more common designs as well as common questions that may be used to facilitate data warehouse and data mart requirements sessions.
Appendix C provides a listing of tables and columns for star schemas, including the ones in this chapter. The full-blown CD-ROM contains SQL scripts to implement these star schemas.