Final Project Guidelines and Rubric

Overview The final project for this course is a two-part project: an executive presentation and a technical proposal. The final project presents a detailed scenario regarding the merger of two insurance companies. For the project, the student is positioned as the chief information officer (CIO) and is asked to lead an initiative to merge the data infrastructures of both insurance companies into a single consolidated data warehouse. For the first part of the project, the student prepares an executive presentation to outline the project, its importance, and its scope. For the second part, the student prepares a technical proposal that outlines in greater detail how the data from both organizations will be unified into a data warehouse. The project is divided into three milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final submissions. These milestones will be submitted in Modules Two, Four, and Six. The Final Project will be submitted in two parts: Part I in Module Eight and Part II in Module Nine. In this assignment you will demonstrate your mastery of the following course outcomes:

 Assess the effectiveness of data warehouses and database management systems in supporting specific business goals and decision making

 Design architectures for data warehouses that address business problems and increase business opportunity

 Construct plans for implementing a data warehouse that meet stakeholder needs and business specifications

 Analyze constraints and opportunities associated with integrating data from various systems into a data warehouse

 Develop strategies for ensuring data integrity, accuracy, and quality by employing appropriate data scrubbing and integration techniques

Prompts Scenario Refer to the following scenario for the background and basis for your data warehouse design: Imagine you are the chief information officer (CIO) for Virtual World Insurance Company, an organization located in San Diego, California. It provides auto insurance coverage to more than 100,000 customers across the United States and currently has 100 employees. Virtual World Insurance Company has recently acquired Maxon Insurance Company, located in Ontario, Canada. Maxon Insurance Company has 10 employees and provides auto insurance to 10,000 customers in Canada. As a result of this merger, the chief executive officer (CEO) has asked you to look at a data warehouse as a viable solution for merging both information technology (IT) infrastructures. After doing research, you decide to create a data warehouse that will combine the customer information from both companies into one centralized location.

Maxon Insurance Company does not have a relational database. In fact, the company currently stores its data in multiple data sources. As a result, Maxon Insurance Company’s data does not have any unique identifiers. Also, customers with multiple insurance policies have duplicate records. Each spreadsheet repeats the customer’s demographic information. Each insurance company utilizes a distinct customer relationship management (CRM) system. The CRM systems are used to keep a record of all customers and any communications that are sent to customers. The CRM systems tie into an in-house billing system that is used to bill for insurance premiums, insurance deductibles, and any other billable items. To manage organizational operations, each company uses a different enterprise resource planning (ERP) system. The ERP systems are used to manage human resources (hires, terminations, etc.), payroll, budgeting, accounting, and fixed assets. To streamline operations and reduce maintenance costs, all data systems (ERP, CRM, billing, etc.) will need to be consolidated into a data warehouse. This will avoid duplicated information and data redundancy. Prompt I: Executive Presentation Prior to creating the technical proposal for the data warehouse, the CEO would like you to present to the C-level executives the concepts of a data warehouse. The purpose of the presentation is to discuss the viability of creating a data warehouse and providing justification to allocate resources to complete this project. Given your research, how viable an option is creating a data warehouse? What evidence exists to support the decision to merge the existing IT infrastructures into a data warehouse? What are the key potential issues and the key goals that the data warehouse needs to meet? What are some potential issues you might face in merging the two infrastructures? Prepare a presentation that discusses:

I. Concepts of a Data Warehouse: Assess the value of using a data warehouse. Specifically, discuss the types of information that a data warehouse should include and how the information is organized. Why is a data warehouse the preferred solution? What value would a data warehouse add to a business?

II. Integration of Data Sources: Discuss in detail the sources of information that can be integrated from the various operational areas of the organization. For example, while some information is housed in Excel spreadsheets, sales and marketing data may be in a CRM (customer resource management).

III. Pros and Cons:

a) Cost and Return on Investment: How is the cost of a data warehouse worth the investment? What type of information can a data warehouse provide that would make the cost more acceptable? How will the organization benefit from a data warehouse? Are there any negative consequences of having a data warehouse? Which specific operational areas will feel the benefits?

b) Required Resources: What are the costs associated with a data warehouse? Will any additional staff be required to maintain and support the data warehouse? Be sure to explain the importance of each resource you identify.

c) Informational Value: How can the information in a data warehouse add value to the organization? What specific business opportunities could be illuminated and how would the use of a DBMS help solve business problems?

d) Limitations: What are some functions that a data warehouse cannot perform? How scalable is a data warehouse? How can the organization overcome these obstacles to ensure data quality? Support your conclusions.

IV. Key Business Considerations: Address some of the business-related considerations. Some considerations include: Prior to investing in a specific data warehouse, what type of hardware and/or software will you consider? Will you hire a consultant to help with the implementation process? What is required prior to moving data to a data warehouse? Will there be necessary training? Support your conclusions.

V. Closing statement: Summarize the overall presentation with care. This is your closing statement, the last message to your audiences and your last chance to convince them of the value of a data warehouse for solving their business problems.

Prompt II: Technical Proposal Having successfully explained the value of designing a warehouse to facilitate the merger between Virtual World Insurance Company and Maxon Insurance, you are now responsible for creating the full-fledged proposal. Your proposal must include your architecture and a technical plan for implementation that highlights potential difficulties. It is important that you communicate in a manner that can be understood by executives, but can also be understood by members of your IT group to plan for future implementation. The challenge will be balancing audience-appropriate communication with adhering to the technical nature of your task. Remember to include all of the necessary aspects of a data warehouse and to attend to potential issues, both common aspects and those unique to your organization. Your technical proposal must attend to the following critical elements:

I. Introduction: Provide an introduction that lays the groundwork for your proposal and tells the audience both what the point of the proposal is and how it will benefit the organizations.

II. Data Warehouse Architecture:

a) Architecture Design: Provide a clear visualization of the architecture, showing the important aspects that will allow for integration of organizational information.

b) Architecture Defense: Explain the architecture that you have designed and the reasoning behind the choices you have made. What approach did you take in designing your architecture (for example, did you follow a top-down or bottom-up approach or did you incorporate strategies from multiple approaches)? How will your architecture address business problems? Be sure to provide support from relevant sources or examples.

c) Database Management System (DBMS): Provide your justification and rationale for the DBMS that you select. Discuss the DBMS tools that you considered. Why was the DBMS you selected the best choice for the organization in terms of supporting decision making and aligning to the business goals?

III. Implementation Plan: a) Timeline: Include a reasonable timeline for implementation. Considerations include: Is there sufficient time between milestones? What

milestones and key deliverables will be required to complete the data warehouse from start to finish?

b) Resources: What resources will be required for implementing the warehouse? Will you use your local IT department or an external vendor? What are the approximate costs for this project? Why are the resources you have identified necessary? Provide examples to support your claims.

c) Training: Propose a logical training plan for employees. Be sure to specify the level of training needs for various positions and explain your reasoning.

d) Security Policy: Craft a policy for maintaining security that meets organization needs. Considerations include, but are not limited to: Who will have access to the data warehouse? Who will you work with to determine access rights for users? Will employees have access to the records from both companies?

IV. Data Integration and Scrubbing:

a) Data Integrity: How will you combine date fields with various formats (i.e., MMDDYYYY vs. DDMMYYYY)? What other data issues will need to be addressed?

b) Primary Key(s): What will you use as a unique identifier to combine the records? What primary keys, foreign keys, and indexes will you need to create?

c) Customer Data: Once the data is merged into the data warehouse, how will you be able to differentiate customers from Virtual World Insurance Company and customers from Maxon Insurance Company?

d) Duplicate Data: How will you eliminate duplicate records in the database to ensure data quality?

Milestones Milestone One: Data Warehouse Pros and Cons Analysis In Module Two, you will submit your data warehouse pros and cons analysis. Review the scenario for the final assessment. Using the scenario, develop a pros and cons analysis of implementing a data warehouse. Include the following elements: 1) cost and return on investment (ROI), 2) required resources, 3) informational value, and 4) limitations. This milestone is graded with the Milestone One Rubric. Milestone Two: Implementation Plan In Module Four, you will submit your implementation plan. Review the scenario for the final assessment. Using the scenario, develop this portion of the project plan. To meet requirements you should include four aspects in the implementation plan: 1) timeline, 2) resources, 3) training, and 4) security policy. This milestone is graded with the Milestone Two Rubric. Milestone Three: Data Integrity and Scrubbing Portion In Module Six, you will submit your data integrity and scrubbing portion of the plan. Review the scenario for the final assessment. Using the scenario, develop this portion of the project plan. To meet requirements you will need to address the four aspects of this subsection of the proposal, which are as follows: 1) data integrity, 2) primary key(s), 3) customer data, and 4) duplicate data. This milestone is graded with the Milestone Three Rubric.

Final Submission: Executive Presentation and Technical Proposal In Module Eight, you will submit the first part of your Final Project, the Executive Presentation, which should outline the project, its importance, and its scope. It should be a complete, polished artifact containing all of the critical elements of the final product. It should reflect the incorporation of feedback gained throughout the course. This submission will be graded using Final Project Rubric I. In Module Nine, you will submit the second part of your Final Project, the Technical Proposal, which should outline in greater detail how the data from both organizations will be unified into a data warehouse. It should be a complete, polished artifact containing all of the critical elements of the final product. It should reflect the incorporation of feedback gained throughout the course. This submission will be graded using Final Project Rubric II.

Deliverables

Milestone Deliverables Module Due Grading

1 Data Warehouse Pros and Cons Analysis

Two Graded separately; Milestone One Rubric

2 Implementation Plan Four Graded separately; Milestone Two Rubric

3 Data Integrity and Scrubbing Portion

Six Graded separately; Milestone Three Rubric

Final Project Submission: Executive Presentation

Eight Graded separately; Final Project Rubric I

Final Project Submission: Technical Proposal

Nine Graded separately; Final Project Rubric II

Final Project Rubric I Guidelines for Submission: Your presentation does not have to be in the form of a PowerPoint; however, if you use another tool (such as Prezi), you must prepare a notes page with your intended speech or you must record yourself presenting your visual presentation. Remember that an effective presentation contains main points and visuals, but the meat of the presentation is in your verbal communication. The presentation should be approximately 8–12 slides with notes indicating intended speech or a recording of yourself giving your presentation. Remember, this is a professional presentation to high-level executives, so spelling errors or unprofessional elements could result in a less-than-favorable outcome for your proposal.

Critical Elements Exemplary (100%) Proficient (90%) Needs Improvement (70%) Not Evident (0%) Value

Concepts of a Data Warehouse

Meets “Proficient” criteria and assessment of value includes support from relevant sources and comparable examples

Provides a logical assessment of the use and purpose of a data warehouse with particular focus on the information housed in a warehouse and potential for business solutions and decision making

Provides an assessment of the use and purpose of a data warehouse, but lacks detail on the information housed in a warehouse and potential for business solution and decision making

Does not provide an overview of the use and purpose of a data warehouse

15

Integration of Data Sources

Meets “Proficient” criteria and discussion digs deep into the specifics of the operational/functional areas that are the sources of information

Discusses the sources of information that must be integrated from the various operational areas of the organization with accurate detail and specificity regarding the place each holds in the organizational structure

Discusses the sources of information that must be integrated from the various operational areas of the organization, but lacks accuracy or lacks detail and specificity regarding the place each holds in the organizational structure

Does not discuss the sources of information that must be integrated from the various operational areas of the organization

15

Cost and Return on Investment

Meets “Proficient” criteria and analysis of cost versus value is centered on the particular business environment of the scenario

Provides a detailed and logical analysis of the cost of integrating systems versus the value of a data warehouse

Discusses the cost of integrating systems versus value of a data warehouse, but lacks necessary detail for a comprehensive analysis

Does not discuss the cost of integrating systems versus the value of a data warehouse

10

Required Resources

Meets “Proficient” criteria and takes the analysis a step further by expounding on the opportunities that may be presented by attaining such resources

Accurately analyzes the particular costs, labor, equipment, and other resources that may be required and explains the importance of each

Analyzes the particular costs, labor, equipment, and other resources that may be required, but not accurately, or the importance of each is not explained accurately

Does not analyze the particular costs, labor, equipment, and other resources that may be required

10

Informational Value

Meets “Proficient” criteria and provides specific details and examples that highlight the value of data warehouses for business contexts

Accurately analyzes the business opportunities and problem-solving capabilities of the information that can be housed in a data warehouse specific to the scenario

Analyzes the business opportunities and problem- solving capabilities of the information that can be housed in a data warehouse, but not accurately or specifically to the scenario

Does not analyze the business opportunities and problem- solving capabilities of the information that can be housed in a data warehouse

15

Data Limitations

Meets “Proficient” criteria and support includes relevant examples and quality sources

Describes common methods for overcoming possible limitations of integrating, scaling, and ensuring data quality with support

Describes methods for overcoming possible limitations of integrating, scaling, and ensuring data quality, but description is not comprehensive or lacks relevant examples

Does not describe methods for overcoming possible limitations of integrating, scaling, and ensuring data quality

10

Key Considerations

Meets “Proficient” criteria and defense of claims is particularly well qualified with logic driven from business context, real- world examples, and scholarly/professional sources

Determines key business considerations from the scenario and research and supports claims with examples and sources that highlight relevance to increasing business opportunity

Provides key business considerations but lacks supportive sources and examples that highlight relevance of considerations to increasing business opportunity

Does not provide key business considerations

15

Closing Statements

Meets “Proficient” criteria and summary of presentation highlights the value of a data warehouse in a manner that would lend to an acceptance (in other words, it is persuasive)

Summarizes the overall presentation with attention to audience and emphasis on the value of a data warehouse

Summarizes the overall presentation, but does not emphasize the value of a data warehouse or does not cater to the audience

Does not summarize the overall presentation

5

Articulation of Response

Submission is free of errors related to citations, grammar, spelling, syntax, and organization, and is presented in a professional and easy-to- read format

Submission has no major errors related to citations, grammar, spelling, syntax, or organization

Submission has major errors related to citations, grammar, spelling, syntax, or organization that negatively impact readability and articulation of main ideas

Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas

5

Earned Total 100%

Rubric Annotations

Term Context for Instructor/Definition

Digs deep into the specifics of the operational/functional areas

Should dig really deep into the different functions in the organization, such as accounting, financial, HR, and so on

Opportunities Fringe benefits/unintended benefits

Provides specific details and examples that highlight the value of data warehouses for business contexts

Well-detailed discussion regarding “business opportunities and value”—specifically, this needs to point to the specific operations in the organization, such as accounting, finance, marketing, sales, HR, and so on.

Final Project Rubric II Guidelines for Submission: Your technical proposal should be logically organized with all of the key elements of a professional proposal. There are several types of proposals (click here for general guidelines for writing professional proposals), so you must work to cater yours to your specific content and audience. Your proposal must include a visual representation of your data warehouse architecture design, as well as properly cited sources where appropriate. Submission lengths will vary.

Critical Elements Exemplary (100%) Proficient (90%) Needs Improvement (70%) Not Evident (0%) Value

Introduction Meets “Proficient” criteria and introduction is particularly well articulated with specific examples and logical identification of key business factors

Submission includes an introduction that lays the groundwork for the proposal by articulating the business context and problems at hand

Submission includes an introduction that lays the groundwork for the proposal, but lacks detail around the business context and problems at hand

Submission does not include an introduction that lays the groundwork for the proposal

5

Data Warehouse Architecture: Design

Meets “Proficient” criteria and is creatively represented or unique in comparison with other designs

Warehouse design is organized and clear, and comprehensively indicates aspects of the organizational information that will be integrated

Warehouse design is not clear, is not organized, or does not comprehensively indicate all necessary aspects of the organizational information that will be integrated

Does not include a warehouse design

11.25

Data Warehouse Architecture:

Architecture Defense

Meets “Proficient” criteria and supports defense with real- world examples and scholarly sources

Logically defends architecture design choices and approach with examples and relevant sources

Provides reasoning behind architecture design choices and approach, but does not defend with examples and relevant sources or defense overlooks relevant factors

Does not provide reasoning behind architecture design choices and approach

11.25

Data Warehouse Architecture:

Database Management System

(DBMS)

Meets “Proficient” criteria and defense includes relevant examples and sources that provide particularly strong support

Defends the selection of DBMS tools in terms of effectively meeting organizational needs with logical arguments and sources of support

Defends the selection of DBMS tools, but not in terms of organizational needs or without logical argument or sources for support

Does not defend the selection of DBMS tools

22.5

Implementation Plan: Timeline

Meets “Proficient” criteria and timeline detail is focused around the key deliverables required to complete the warehouse or is exceptionally well defined in terms of milestone needs

Crafts a reasonable timeline for implementation

Crafts a timeline, but the timeline is not reasonable

Does not craft a timeline 5.62

Implementation Plan: Resources

Meets “Proficient” criteria and specific examples pertain to the individual organization

Identification of necessary resources is defended with specific examples and relevant explanations

Identification of necessary resources is defended, but lacks detail or explanations and examples are not relevant

Does not defend identification of necessary resources

5.62

Implementation Plan: Training

Meets “Proficient” criteria and training plan is catered to both organizations or is exceptionally well planned

Proposes a logical training plan for implementation that includes the reasoning behind the level of training needs for various positions

Proposes a training plan, but lacks detail around level of training needed or plan is not entirely logical for implementation

Does not propose a training plan

5.62

Implementation Plan: Security Policy

Meets “Proficient” criteria and policy meets organizational needs to the point of being ready for implementation

Submission includes a security policy that considers permission levels and access rights, and meets organizational needs

Submission includes a security policy that considers permission levels and access rights, but the policy does not meet organizational needs

Submission does not include a security policy that considers permission levels and access

5.62

Data Integration and Scrubbing: Data

Integrity

Meets “Proficient” criteria and methods described are the best methods for ensuring data integrity for the given scenario and specific issue

Articulates the correct methods for combining data fields with various formats to ensure data is not lost or compromised

Articulates methods for combining data fields with various formats, but methods are not correct for ensuring data is not lost or compromised

Does not articulate methods for combining data fields with various formats

5.63

Data Integration and Scrubbing: Primary

Keys

Meets “Proficient” criteria and identified keys and indexes are the most appropriate for each of their designated purposes within the data warehouse

Articulates appropriate primary keys, foreign keys, and indexes for creation that will ensure a clear and accurate warehouse

Articulates primary keys, foreign keys, and indexes necessary, but not all will ensure a clear and accurate warehouse

Does not articulate primary keys, foreign keys, and indexes necessary

5.63

Data Integration and Scrubbing: Customer

Data

Meets “Proficient” criteria and articulated methods are the most appropriate given the accompanying explanation, accompanying scenario, and integration issues that have been identified in the proposal

Articulates plausible methods for differentiating customer data from each company after data is merged

Articulates methods for differentiating customer data from each company after data is merged, but not all methods are plausible, or necessary detail is left out of explanation

Does not articulate methods for differentiating between customer data from each company after data is merged

5.63

Data Integration and Scrubbing: Duplicate

Data

Meets “Proficient” criteria and identified strategies are the most appropriate given the accompanying explanation, accompanying scenario, and integration issues that have been identified in the proposal

Articulates valid, plausible strategies for eliminating duplicate records and ensuring data quality and accuracy

Articulates strategies for eliminating duplicate records and ensuring data quality and accuracy, but not all strategies are valid or plausible

Does not articulate strategies for eliminating duplicate records to ensure data quality and accuracy

5.63

Articulation of Response

Submission is free of errors related to citations, grammar, spelling, syntax, and organization, and is presented in a professional and easy-to- read format

Submission has no major errors related to citations, grammar, spelling, syntax, or organization

Submission has major errors related to citations, grammar, spelling, syntax, or organization that negatively impact readability and articulation of main ideas

Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas

5

Earned Total 100%

Rubric Annotations

Term Context for Instructor/Definition

Defends architecture design choices and approach

Additional context for consideration (in terms of the approach aspect of this critical element): Will this be a comparison of the different approaches? Top down vs. bottom up? Student chooses one and follows it through?

Reasonable The timeline points of interest are spaced in a realistic manner without unnecessary lapse time.

Ready for implementation Includes sufficient detail and covers necessary considerations for immediate implementation in the organizations listed. There are no obvious barriers to immediate implementation of the policy for security (once the warehouse is constructed, of course).

Best methods Most appropriate for each given integration problem. For example, the proposed method for solving the issue of MMDDYYYY versus DDMMYYYY is not only a method that will work, but also is the most straightforward and appropriate method for that specific issue.

Designated purposes At minimum, each primary key (for example) accurately and succinctly acts as the unique identifier for whatever it is representing.

Most appropriate Not only are all of the methods realistic and possible for differentiating customer data, but the methods discussed fit within the scenario provided, are fully explained and defended as the most appropriate given any restraints or issues that have been identified, or are the most appropriate given the expertise of the subject matter expert who is evaluating the submission (the instructor).