Database Modeling & Design Fourth Edition- P35: 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. | Data Warehousing 157 Figure Example of a snowflake schema for a data warehouse T Select a Business Process Determine Granularity Choose Dimensions identify Measures more business processes else Figure Four step dimensional design process Kimball and Ross 2002 158 CHAPTER 8 Business Intelligence XYZ Widget Company Wish List 1. What are the trends of our various products in terms of sales dollars unit volume and profit margin 2. For those products that are not profitable can we drill down and determine why they are not profitable 3. How accurately do our estimated costs match our actual costs 4. When we change our estimating calculations how are sales and profitability affected 5. What are the trends in the percentage of jobs that ship on time 6. What are the trends in productivity by department for each machine and for each employee 7. What are the trends in meeting the scheduled dates for each department and for each machine 8. How effective was the upgrade on machine 123 9. Which customers bring the most profitable jobs 10. How do our promotional bulk discounts affect sales and profitability Looking over the wish list you begin picking out the business processes involved. The following list is sufficient to satisfy the items on the wish list. Business Processes 1. Estimating 2. Scheduling 3. Productivity Tracking 4. Job Costing These four business processes are interlinked in the XYZ Widget Company. Let s briefly walk through the business processes and the organization of information in the operational systems so we have an idea what information is available for analysis. For each business process we ll design a star schema for storing the data. The estimating process begins by entering widget specifications. The type of widget determines which machines are used to manufacture the widget. The estimating software then calculates estimated time on each Data Warehousing 159 machine used to produce that particular type of widget. Each machine is .
