Question 2

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 has100 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 Companys data does not have any unique identifiers. Also, customers with multiple insurance policies have duplicate records. Each spreadsheet repeats the customers 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 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

Question 2

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 has100 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 Companys data does not have any unique identifiers. Also, customers with multiple insurance policies have duplicate records. Each spreadsheet repeats the customers 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 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?