MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making

Preview (14 of 44 Pages)

100%

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 1 preview image

Loading page ...

MMIS642 ASSIGNMENT1MMIS642 ASSIGNMENTData Warehouse and Sales Analysis Strategies for Enterprise Decision-MakingStudent1/30/2015

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 2 preview image

Loading page ...

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 3 preview image

Loading page ...

MMIS642 ASSIGNMENT2You are the vice president of marketing for a nation-wide appliance manufacturer withthree production plants.Describe any three different ways you will tend to analyze yoursales. What are the business dimensions for your analysis?If I am the Vice President of marketing for a company with three production plants, my firstand obvious dimension would be the“Product”. Analysis of sales must include analysis bybreaking the sales down by dealers, hence“Dealer”, is another important dimension foranalysis. As an Appliance manufacturer, I would want to know how sales break down alongcustomer demographics. I would want to know who is buying appliance and in whatquantities, so“Customer demographics”would be another useful business dimension foranalysis. How do the customers pay for the appliances? What effect does financing for thepurchases have on the sales? These questions can be answered by including the“method ofpayment”as another dimension for analysis. There are three production plants means“distribution and transportation” are also the important dimensions for analysis. All thebusiness dimensions involve time management importantly hence“Time”is also the factorfor analysis.You are the manager for the data warehouse project at a retail chain with stores allacross the country and users in every store. How will you ensure that all thedetailsnecessary to decide on the DBMS are gathered during the requirements phase? Write amemototheSeniorAnalystdirectlyresponsibletocoordinatetherequirementsdefinition phase.BUSINESS MEMODATE: February 16, 2015TO: Senior Analyst

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 4 preview image

Loading page ...

MMIS642 ASSIGNMENT3FROM: ManagerRE: Data storage specificationsto decide on the DBMS and examine the impact ofbusiness requirements on the selection of the DBMS and on estimating storage for the datawarehouse.Please ensure following business requirements are met in the requirement analysis of theDBMS.Broadly, the following elements of business requirements affect the choice of theDBMS:Level of User Experience:If the users are totally inexperienced with databasesystems, the DBMS must have features to monitor and control runaway queries. Onthe other hand, if many of your users are power users, then they will be formulatingtheir own queries. In this case, the DBMS must support an easy SQL-type languageinterface. Formulate your questionnaire to check user experience level properly.Types of Queries:The DBMS must have a powerful optimizer if most of the queriesare complex and produce large result sets. Alternatively, if there is an even mix ofsimple and complex queries, there must be some sort of query management in thedatabase software to balance the query execution.Need for Openness:The degree of openness depends on the back-end and front-endarchitectural components and those, in turn, depend on the business requirements.Data Loads:The data volumes and load frequencies determine the strengths in theareas 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 requirementsdefinition reflect the type and extent of the metadata framework.

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 5 preview image

Loading page ...

MMIS642 ASSIGNMENT4Data Repository Locations:Is your data warehouse going to reside in one centrallocation, or is it going to be distributed? The answer to this question will establishwhether the selected DBMS must support distributed databases.DataWarehouseGrowth:Yourbusinessrequirementsdefinitionmustcontaininformation on the estimated growth in the number of users, and in the number andcomplexity of queries. The growth estimates will have a direct relation to how theselected DBMS supports scalability.You also need to estimate the storage sizes for the following in the requirements definitionphase:Data Staging Area:Calculate storage estimates for the data staging area of the overallcorporate data warehouse from the sizes of the source system data structures for eachbusiness subject. Figure the data transformations and mapping into your calculation.For the data marts, initially estimate the staging area storage based on the businessdimensions and metrics for the first data mart.Overall Corporate Data Warehouse:Estimate the storage size based on the datastructures for each business subject. You know that data in the data warehouse isstored 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 thatsubject.Data Marts: Conformed, Independent, Dependent, or Federated.While definingrequirements, you create information diagrams. A set of these diagrams constitutes adatamart.Eachinformationdiagramcontainsbusinessdimensionsandtheirattributes. The information diagram also holds the metrics or business measurements

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 6 preview image

Loading page ...

MMIS642 ASSIGNMENT5that are meant for analysis. Use the details of the business dimensions and businessmeasures found in the information diagrams to estimate the storage size for the datamarts. Begin with your first data mart.Multidimensional Databases:These databases support OLAP or multidimensionalanalysis. How much online analytical processing (OLAP) is necessary for your users?The corporate data warehouse or the individual conformed or dependent data martsupplies the data for the multidimensional databases. Work out the details of OLAPplanned for your users and then use those details to estimate storage for thesemultidimensional databases.You have recently been promoted to administrator for the data warehouse of anationwide automobile insurance company. You are asked to prepare a checklist forselecting 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 datawarehousearchitecture.Usethislisttoderivethetool-selectionchecklist.If I am Administrator for the data warehouse of a nationwide automobile insurance companymy 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: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 7 preview image

Loading page ...

MMIS642 ASSIGNMENT6•End user groups to be interviewed identified•Data warehouse kickoff meeting withall 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: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 8 preview image

Loading page ...

MMIS642 ASSIGNMENT7•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 fieldsidentified•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: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 9 preview image

Loading page ...

MMIS642 ASSIGNMENT8•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: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 10 preview image

Loading page ...

MMIS642 ASSIGNMENT9•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 blockdiagram•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 tinybrowses•Front end tool fordelivering 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: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 11 preview image

Loading page ...

MMIS642 ASSIGNMENT10•Multiple answer sets can be automatically assembled in tool with outer join•Direct support for single and multi-dimension comparisons•Direct support formultiple 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 touser•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: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 12 preview image

Loading page ...

MMIS642 ASSIGNMENT11•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 todefine 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 testAs the data acquisition specialist, what types of metadata can help you? Choose one ofthe data acquisition processes and explain the role of metadata in that process.Exercise #5 pg. 221 Data Warehousing TextIf I am the dataacquisition specialist the metadata types by the functional areas in the dataWarehouse which could help me are:1. Data acquisition2. Data storage3. Information deliveryData AcquisitionIn Data Acquisition, the data warehouse processes relate to the following functions:

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 13 preview image

Loading page ...

MMIS642 ASSIGNMENT12➢Data extraction➢Data transformation➢Data cleansing➢Data integration➢Data stagingAs tools are used for the various data warehouse processes, metadata gets recorded as abyproduct. The data acquisition process DataExtraction has following roles in the processData 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: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making - Page 14 preview image

Loading page ...

MMIS642 ASSIGNMENT13What is a factlessfact table? Design a simple STAR schema with a factless fact table totrack patients in a hospital by diagnostic procedures and time.Exercise #3 pg. 248 Data Warehousing TextThe Factless Fact TableApart from the concatenated primary key, a fact table contains facts or measures. Let us saywe are building a fact table to track patients in a hospital bydiagnostic procedures and time.For analyzing patients in a hospital, the possible dimensions are Patient, Visit, Diagnosticprocedure, consumption of drugs, material and Time.Patient KeyVisit KeyProcedure KeyTime KeyDrugs KeyMaterial KeyPATIENT FACTSPatient KeyPatient No.Patient NameAddressContactPATIENTVisit KeyVisit No.Patient NameAddressContactVISITProcedure KeyProcedure NoTypeResultRemarksPROCEDURETime KeyDateDay of MonthMonthYearTIMEDrug KeyDrug NameMfg DateExp DateQtyDRUGSMaterial KeyNameTypeQtyMATERIAL

Preview Mode

This document has 44 pages. Sign in to access the full document!

Report

Study Now!

Document Details

Related Documents

DevOps and Its Importance Study Notes

Veeam Certified Engineer VMCE-V12

Modules 1 - 3 Basic Network Connectivity and Commu

Notes CCNA

CCNA 200-301 Portable Command Guide 5th Edition 20

CCNA 200-301 Portable Command Guide 5th Edition 22

CCNA 200-301 Portable Command Guide 5th Edition 36

Foundations of Novell Networking Section 26

Modulo 7 DHCPv4

Build and troubleshooting Integration Challenge

Company

Explore

Study Tools