CST2355 – Database Systems: Assignment 1
CST8276 - Advanced Database Topics
Assignment 5: Assessing Business Intelligence and Data Warehouse Designs (10%)
This assignment relates to the following Course Learning Requirements:
CLR 5 - Explain the process of creating a data warehouse in support of Business Intelligence applications
CLR 6 - Build database systems that directly support internationalization and globalization
Objectives of the Assignment
Create an Assessment Report for the draft, high level data warehouse design created by the company’s Data Architect. The Data Architect presented the diagram which illustrates the components (key entities), a
ows showing the direction of the data flows, the distinction of the data source(s), the main Data Warehouse and the BI environment which consists of data (two reporting datasets) and reporting consumers.
Take the time to go through the Data Warehousing Tutorial -- Data Warehousing Tutorial (tutorialspoint.com) before beginning this Assignment. It is important for you to have a good base understanding of what a data warehouse architecture consists of.
Reference Materials
Data Warehouse Design – Inmon vs Kimball Architecture & InfoSol Blog
How to Create a Data Warehouse (dataschool.com)
Business Intelligence and Data Warehouse Design
Below is the high level data warehouse design showing a proposed Data Warehouse architecture. The company’s Data Architect presented this for assessment.
Instructions
You are tasked with creating the Assessment Report for the proposed DW architecture diagram above. The following list provides a description of each component shown in the diagram above.
IBM AIX Server. This is one or more hardware servers.
Windows Server. This is one or more IBM hardware servers with a Windows operating system.
External Systems. These are applications developed in Java and .Net.
ETL. This is a service that aggregates the data from its input, transforms the data doing summation and counts, then outputs the data in a specific form.
Data Warehouse. This is a database for heavy query processing. The tables in the data warehouse are fact tables. Dimension tables are not included in the design..
Business Intelligence. This represents the tools and database used to support the reporting and analytic tools.
Users. These are the reporting and analytic users who will access the BI database. They can be people or possibly APIs.
Tables in Data Warehouse Database:
Policy. Table that includes all the insurance policies the company offers.
Coverage. Table that includes the coverages available for the insurance policies.
Enforce. Table that includes when policies are in play for a given claim.
Risk Exposure. Table containing the potential loss the company can experience. Data in this table is sliced, diced, aggregated, summed, and counted.
Customer. Table of information on Customers.
Risk. Table of the potential risks all customers may face.
Risk Priority. Table that prioritizes the risks all customers may face.
Life Phase. Table that includes which stage in life the customer is at. AKA going to school, graduation, first job, ma
iage, house purchase, career change, and retirement.
Tables in BI Database (data mart – dimensional model with Dimension and Fact tables)
Risk Summary – a summarized star schema for the Risk data
Policy Summary – as summarized star schema for the Policy data
To complete this assignment, review the materials from Model 12 and 13.
Use the draft Data Warehouse architecture design shown above. Break down your assessment into the following four(4) sections:
Data extraction. The effectiveness of data being extracted to be used by users, other systems, and other components within the designed system.
Data management. The effectiveness of data access and data movement.
Hardware scalability. The ease of data administration when adding more hardware to improve system performance.
Data processing. The performance of moving, processing, and summarizing data for business intelligence and user consumption.
Under each section include:
At least ONE guideline/principle
A guideline improves the success of an activity or approach. How could one using ETL during extraction to improve the success and effectiveness of ETL? That “how” is a guideline/principle.
The name/title of the guideline/principle
Why that guideline/principle is important for the design; and
How the design follows or does not follow that guideline/principle
Hypothesize about at least ONE clear design decision that is observed in the diagram. Omission of information in the diagram is not a design decision.
Specify why that design decision is co
ect/inco
ect based on the guideline/principle
Avoid These Common Mistakes
Need further info, information not there, e
or in design. When assessing a design, you need to assess what is there. Not what is not there. Don’t assume the designer made a mistake. What decision did they make and WHY?
Assuming the Implementation. Don’t assume HOW the design is going to be implemented and base your assumption on that implementation. We call that ‘solutioning’. This assessment is NOT dependent on the final technical tooling solution. Implementation assumptions can include Cloud, on/off-premises, databases, and software used. This design can be implemented in various ways, even with VB Script and text files. So, don’t make implementation assumptions for this assessment.
Being vague. Always be specific on WHAT, HOW, and WHY. WHAT is the guideline/principle. HOW is it being or not being followed according to the evidence you see in the diagram. WHY is the design effective or not effective based on your WHAT and HOW.
Activities are Guidelines. Activities (often called ‘tasks’) are NOT guidelines/principles. Activities are implementation steps. For example, “Testing at each phase of design” is not a guideline/principle. “Testing the core assumptions using prototypes at each phase of design to ensure co
ectness and reduce rework” is a guideline/principle.
PDF Submission. Submit your report as a PDF. To achieve a higher score please review this assignment’s ru
ic. Please include your name, your student number, section, and the name of the assignment.
Submission Requirements.
To submit this assignment, submit your file as a PDF File, using the assignment upload tool in Brightspace. To access this, navigate to the Activities/Assignments link in the left-hand sidebar, and select Assignment 5 - Assessing Business Intelligence and Data Warehouse Designs
Please note: Algonquin College has a policy regarding Plagiarism. The consequences are immediate zeroes for assignments and potentially an instant fail for the whole course. It is only natural for students to work together to complete assignments.
DO NOT PLAGIARIZE each other’s work.
Assignment 5 Grading Ru
ic (10%)
Table 1 - Assignment 5 Grading Ru
ic
Exemplary
Accomplished
Developing
Incomplete
Criteria
3
2
1
0
Data Extraction
Each guideline is clearly titled and included a concise description of what that guideline is. All guidelines included concise explanations to why they’re important and how the design follows or doesn’t follow them.
Each guideline is clearly titled and included a vague description of what that guideline is. All guidelines included explanations of their importance to the design and how they’re either being or not being followed.
Each guideline is titled and included a difficult to follow description of how it is either being or not being followed.
Not completed
OR
Excluded extractions for users or other system components in the design
Data Management
Each guideline is clearly titled and included a concise description of what that guideline is. All guidelines included concise explanations to why they’re important and how the design follows or doesn’t follow them.
Each guideline is clearly titled and includes a vague description of what that guideline is. All guidelines included explanations of their importance to the design and how they’re either being or not being followed.
Each guideline is titled and included a difficult to follow description of how it is either being or not being followed.
Not completed
OR
Excluded data access or data movement
Hardware Scalability
Each guideline is clearly titled and included a concise description of what that guideline is. All guidelines included concise explanations to why they’re important and how the design follows or doesn’t follow them.
Each guideline is clearly titled and includes a vague description of what that guideline is. All guidelines included explanations of their importance to the design and how they’re either being or not being followed.
Each guideline is titled and included a difficult to follow description of how it is either being or not being followed.
Not completed
Data Processing
Each guideline is clearly titled and included a concise description of what that guideline is. All guidelines included concise explanations to why they’re important and how the design follows or doesn’t follow them.
Each guideline is clearly titled and includes a vague description of what that guideline is. All guidelines included explanations of their importance to the design and how they’re either being or not being followed.
Each guideline is titled and included a difficult to follow description of how it is either being or not being followed.
Not completed
End of Assignment
1
IBM
Windows Serve
AIX Serve
Data Warehouse
External
Systems
| POLICY RISK
ETL COVERAGE RISK_EXPOSURE RISK_LEVEL
INFORCE CUSTOMER LIFE_PHASE Busi ness
Intelligence
v POLICY_SUMMARY
RISK_SUMMARY
¥
Users