MMIS642 ASSIGNMENT 1 MMIS642 ASSIGNMENT Data Warehouse and Sales Analysis Strategies for Enterprise Decision - Making Student 1/30/2015 MMIS642 ASSIGNMENT 2 You are the vice president of marketing for a nation - wide appliance manufacturer with three production plants. Describe any three different ways you will tend to analyze your sales. What are the business dimensions for your analysis? If I am the Vice President of marketing for a company with three production plants, my first and obvious dimension would be the “Product” . Analysis of sales must include analysis by breaking the sales down by dealers, hence “Dealer” , is another important dimension for analysis. As an Appliance manufacturer, I would want to know how sales break down along customer demographics. I would want to know who is buying appliance and in what quantities, so “Customer demographics” would be another useful business dimension for analysis. How do the customers pay for the appliances? What effect does financing for the purchases have on the sales? These questions can be answered by including the “method of payment” as another dimension for analysis. There are three production plants means “distribution and transportation ” are also the important dimensions for analysis. All the business dimensions involve time management importantly hence “Time” is also the factor for analysis. You are the manager for the data warehouse project at a retail chain with stores all across the country and users in every store. How will you ensure that all the details necessary to decide on the DBMS are gathered during the requirements phase? Write a memo to the Senior Analyst directly responsible to coordinate the requirements definition phase. BUSINESS MEMO DATE : February 16, 2015 TO : Senior Analyst MMIS642 ASSIGNMENT 3 FROM : Manager RE : Data storage specifications to decide on the DBMS and examine the impact of business requirements on the selection of the DBMS and on estimating storage for the data warehouse. Please ensure following business requirements are met in the requirement analysis of the DBMS. Broadly, the following elements of business requirements affect the choice of the DBMS: Level of User Experience: If the users are totally inexperienced with database systems, the DBMS must have features to monitor and control runaway queries. On the other hand, if many of your users are power users, then they will be formulating their own queries. In this case, the DBMS must support an easy SQL - type language interface. Formulate your questionnaire to check user experience level properly. Types of Queries: The DBMS must have a powerful optimizer if most of the queries are complex and produce large result sets. Alternatively, if there is an even mix of simple and complex queries, there must be some sort of query management in the database software to balance the query execution. Need for Openness: The degree of openness depends on the back - end and front - end architectural components and those, in turn, depend on the business requirements. Data Loads: The data volumes and load frequencies determine the strengths in the areas of data loading, recovery, and restart. Metadata Management: If your metadata component does not have to be elaborate, then a DBMS with an active data dictionary may be sufficient. Let your requirements definition reflect the type and extent of the metadata framework. MMIS642 ASSIGNMENT 4 Data Repository Locations: Is your data warehouse going to reside in one central location, or is it going to be distributed? The answer to this question will establish whether the selected DBMS must support distributed databases. Data Warehouse Growth: Your business requirements definition must contain information on the estimated growth in the number of users, and in the number and complexity of queries. The growth estimates will have a direct relation to how the selected DBMS supports scalability. You also need to estimate the storage sizes for the following in the requirements definition phase: Data Staging Area: Calculate storage estimates for the data staging area of the overall corporate data warehouse from the sizes of the source system data structures for each business subject. Figure the data transformations and mapping into your calculation. For the data marts, initially estimate the staging area storage based on the business dimensions and metrics for the first data mart. Overall Corporate Data Warehouse: Estimate the storage size based on the data structures for each business subject. You know that data in the data warehouse is stored by business subjects. For each business subject, list the various attributes, estimate their field lengths, and arrive at the calculation for the storage needed for that subject. Data Marts : Conformed, Independent, Dependent, or Federated. While defining requirements, you create information diagrams. A set of these diagrams constitutes a data mart. Each information diagram contains business dimensions and their attributes. The information diagram also holds the metrics or business measurements MMIS642 ASSIGNMENT 5 that are meant for analysis. Use the details of the business dimensions and business measures found in the information diagrams to estimate the storage size for the data marts. Begin with your first data mart. Multidimensional Databases: These databases support OLAP or multidimensional analysis. How much online analytical processing (OLAP) is necessary for your users? The corporate data warehouse or the individual conformed or dependent data mart supplies the data for the multidimensional databases. Work out the details of OLAP planned for your users and then use those details to estimate storage for these multidimensional databases. You have recently been promoted to administrator for the data warehouse of a nationwide automobile insurance company. You are asked to prepare a checklist for selecting a proper vendor tool to help you with the data warehouse administration. Make a list of the functions in the management and control component of your data warehouse architecture. Use this list to derive the tool - selection checklist. If I am Administrator for the data warehouse of a nationwide automobile insurance company my checklist for selecting a proper vender is as given below: • Preliminary complete list of affected user groups prior to interviews • Preliminary complete list of legacy data sources prior to interviews • Data warehouse implementation team identified • Data warehouse manager identified • Interview leader identified • Extract programming manager identified MMIS642 ASSIGNMENT 6 • End user groups to be interviewed identified • Data warehouse kickoff meeting with all affected end user groups • End user interviews • Marketing interviews • Finance interviews • Logistics interviews • Field management interviews • Senior management interviews • Six - inch stack of existing management reports representing all interviewed groups • Legacy system DBA interviews • Copy books obtained for candidate legacy systems • Data dictionary explaining meaning of each candidate table and field • High - level description of which tables and fields are populated with quality data • Interview findings report distributed • Prioritized information needs as expressed by end user community • Data audit performed showing what data is available to support information needs • Data warehousing design meeting • Major processes identified and fact tables laid out • Grain for each fact table chosen MMIS642 ASSIGNMENT 7 • Choice of transaction grain Vs time period accumulating snapshot grain • Dimensions for each fact table identified • Facts for each fact table with legacy source fields identified • Dimension attributes with legacy source fields identified • Core and custom heterogeneous product tables identified • Slowly changing dimension attributes identified • Demographic minidimensions identified • Initial aggregated dimensions identified • Duration of each fact table (need to extract old data upfront) identified • Urgency of each fact table (e.g. need to extract on a daily basis) identified • Implementation staging (first process to be implemented...) • Block diagram for production data extract (as each major process is implemented) • System for reading legacy data • System for identifying changing records • System for handling slowly changing dimensions • System for preparing load record images • Migration system (mainframe to DBMS server machine) • System for creating aggregates • System for loading data, handling exceptions, guaranteeing referential integrity MMIS642 ASSIGNMENT 8 • System for data quality assurance check • System for data snapshot backup and recovery • System for publishing, notifying users of daily data status • DBMS server hardware • Vendor sales and support team qualified • Vendor reference sites contacted and qualified as to relevance • Vendor on - site test (if no qualified, relevant references available) • Vendor demonstrates ability to support system startup, backup, debugging • Open systems and parallel scalability goals met • Contractual terms approved • DBMS software • Vendor sales and support team qualified • Vendor team has implemented a similar data warehouse • Vendor team agrees with dimensional approach • Vendor team demonstrates competence in prototype test • Ability to load, index and quality assure data volume demonstrated • Ability to browse large dimension tables demonstrated • Ability to query family of fact tables from 20 PCs under load demonstrated • Superior performance and optimizer stability demonstrated for star join queries MMIS642 ASSIGNMENT 9 • Superior large dimension table browsing demonstrated • Extended SQL syntax for special data warehouse functions • Ability to immediately and gracefully stop a query from end user PC • Extract tools • Specific need for features of extract tool identified from extract system block diagram • Alternative of writing home - grown extract system rejected • Reference sites supplied by vendor qualified for relevance • Aggregate navigator • Open system approach of navigator verified (serves all SQL network clients) • Metadata table administration understood and compared with other navigators • User query statistics, aggregate recommendations, link to aggregate creation tool • Sub - second browsing performance with the navigator demonstrated for tiny browses • Front end tool for delivering parameterized reports • Saved reports that can be mailed from user to user and run • Saved constraint definitions that can be reused (public and private) • Saved behavioral group definitions that can be reused (public and private) • Dimension table browser with cross attribute sub - setting • Existing report can be opened and run with one button click MMIS642 ASSIGNMENT 10 • Multiple answer sets can be automatically assembled in tool with outer join • Direct support for single and multi - dimension comparisons • Direct support for multiple comparisons with different aggregations • Direct support for average time period calculations (e.g. average daily balance) • STOP QUERY command • Extensible interface to HELP allowing warehouse data tables to be described to user • Simple drill - down command supporting multiple hierarchies and no hierarchies • Drill across that allows multiple fact tables to appear in same report • Correctly calculated break rows • Red - Green exception highlighting with interface to drill down • Ability to use network aggregate navigator with every atomic query issued by tool • Sequential operations on the answer set such as numbering top N, and rolling • Ability to extend query syntax for DBMS special functions • Ability to define very large behavioral groups of customers or products • Ability to graph data or hand off data to third - party graphics package • Ability to pivot data or to hand off data to third - party pivot package • Ability to support OLE hot links with other OLE aware applications • Ability to place answer set in clipboard or TXT file in Lotus or Excel formats MMIS642 ASSIGNMENT 11 • Ability to print horizontal and vertical tiled report • Batch operation • Graphical user interface user development facilities • Ability to build a startup screen for the end user • Ability to define pull down menu items • Ability to define buttons for running reports and invoking the browser • Consultants • Consultant team qualified • Consultant team has implemented a similar data warehouse • Consultant team agrees with the dimensional approach • Consultant team demonstrates competence in prototype test As the data acquisition specialist, what types of metadata can help you? Choose one of the data acquisition processes and explain the role of metadata in that process. Exercise #5 pg. 221 Data Warehousing Text If I am the data acquisition specialist the metadata types by the functional areas in the data Warehouse which could help me are: 1. Data acquisition 2. Data storage 3. Information delivery Data Acquisition In Data Acquisition, the data warehouse processes relate to the following functions: MMIS642 ASSIGNMENT 12 ➢ Data extraction ➢ Data transformation ➢ Data cleansing ➢ Data integration ➢ Data staging As tools are used for the various data warehouse processes, metadata gets recorded as a byproduct. The data acquisition process Data Extraction has following roles in the process Data Extraction ➢ Data on source platforms and connectivity ➢ Layouts and definitions of selected data sources ➢ Definitions of fields selected for extraction ➢ Criteria for merging into initial extract files on each platform ➢ Rules for standardizing field types and lengths ➢ Data extraction schedules ➢ Extraction methods for incremental changes ➢ Data extraction job streams MMIS642 ASSIGNMENT 13 What is a factless fact table? Design a simple STAR schema with a factless fact table to track patients in a hospital by diagnostic procedures and time. Exercise #3 pg. 248 Data Warehousing Text The Factless Fact Table Apart from the concatenated primary key, a fact table contains facts or measures. Let us say we are building a fact table to track patients in a hospital by diagnostic procedures and time. For analyzing patients in a hospital, the possible dimensions are Patient, Visit, Diagnostic procedure, consumption of drugs, material and Time. Patient Key Visit Key Procedure Key Time Key Drugs Key Material Key PATIENT FACTS Patient Key Patient No. Patient Name Address Contact PATIENT Visit Key Visit No. Patient Name Address Contact VISIT Procedure Key Procedure No Type Result Remarks PROCEDURE Time Key Date Day of Month Month Year TIME Drug Key Drug Name Mfg Date Exp Date Qty DRUGS Material Key Name Type Qty MATERIAL