tailieunhanh - Database Modeling & Design Fourth Edition- P36

Database Modeling & Design Fourth Edition- P36: Database technology has evolved rapidly in the three decades since the rise and eventual dominance of relational database systems. While many specialized database systems (object-oriented, spatial, multimedia, etc.) have found substantial user communities in the science and engineering fields, relational systems remain the dominant database technology for business enterprises. | 162 CHAPTER 8 Business Intelligence The design of a star schema for the scheduling process begins by determining the granularity. The most detailed scheduling table in the operational system has a record for each cost center applicable to manufacturing each job. The users in the scheduling department are interested in drilling down to this level of detail in the data warehouse. The proper level of granularity in the star schema for scheduling is determined by the job number and the cost center. Next we determine the dimensions in the star schema for the scheduling process. The operational scheduling system tracks the scheduled start and finish date and times as well as the actual start and finish date and times. The estimated and actual hours are also stored in the operational scheduling details table along with a flag indicating whether the operation completed on time. The scheduling team must have the ability to group records by the scheduled and actual start and finish times. Also critical is the ability to group by cost center. The dimensions of the star schema for scheduling are the scheduled and actual start and finish date and times and the cost center. The job number must also be included as a degenerate dimension to maintain the proper granularity in the fact table. Figure reflects the decisions on the dimensions appropriate for the scheduling process. The scheduling team is interested in aggregating the estimated hours and also the actual hours. They are also very interested in examining trends in on-time performance. The appropriate measures for the scheduling star schema include the estimated and actual hours and a flag indicating whether the operation was finished on time. The appropriate measures for scheduling are reflected in Figure . Cost Center Sched Start Date Sched Start Time Sched Finish Date Sched Finish Time Scheduling Detail dd job number fk cost center id fk sched start date id fk sched start time id fk sched finish date id fk sched