INST 525 – Business Intelligence and Data Warehousing --- Final Project/Exam Ple

Question

INST 525 – Business Intelligence and Data Warehousing

--- Final Project/Exam

Please read the following case for data modeling of a data warehouse first:

Banks offer many products. In addition to the traditional checking and saving accounts, there are CDs, IRAs, mortgages, auto loans, home equity loans, credit cards, and even insurance or investment services. While each of these products can be profitable t the bank, many of them carry a high level of risk.

Because of the nature of their products, banks are well motivated to gather lots of information about their customers. Even if your mortgage is not with the bank, they will still find out how much principal you owe, and whether you make your payment on time. This information will influence decisions about whether you should be offered a home equity loan or yet another credit card. It can also be used to improve the effectiveness of the marketing efforts.

Speaking with a Marketing director for a regional bank with about 45 branches across three states. The bank employs some sophisticated marketing techniques, but is interested in streaming its process by building a data warehouse. What the bank wants are:

a.What bank really doing is selling the bank’s products: Accounts. There are several major categories of accounts, with several types of accounts in each category, e.g. deposit account is one category. Checking and saving are two types in the same category.

b.Bank wants to look at all the characteristics of a customer, including the combination of accounts he or she has and the Balances. But, it is not easy now for the bank to look at the history of the accounts over time

c.“Customer” here is really referring to an Account holder. A household may be composed of several people holding many different accounts. What the bank looks at in making decisions is recorded on at the household level about things like income level, education level, number of children, etc.

d.Like to have a history of the demographic characteristics of the household over time if looking at the account history

Above has given a lot of information about what is important to the bank’s marketing effort. Given the overview of the products and customers, and how the marketing activities are used to sell more products to internal and external customers, the design of the data mart will need to meet the following requirements:

Maintain history on each individual account

Associate each account with a household customer

Provide detailed profiles of customers that reflect changes over time

Track the status of accounts over time

The grain should not be at the customer level, but at the account level. The household dimension is used to relate multiple accounts. Note, that this is not done via a relationship from account to household; instead, The FACT table maintains the link. Each account is related to the household it is a part of via the fact table. This method allows accounts to move in and out of households over time, so that we can capture the changes to a household.

Associated with each household is a much richer set of attributes related to Demographic information. It will be periodically updated by the bank, and be told that capturing its history is important. Therefore, it is opted to split these demographic out into a separate dimension, though logically it may be considered household information.

The account dimension contains the attributes related to an individual product held by a customer. There are about half a million accounts, so this will be a fairly large dimension. Product information has therefore been split out into its own dimension.

For the simplicity of bank cross-selling analysis, build an account-level fact table with. The only fact is to track Account Balance. The fact table will include keys to household, month, demographic, status, account, and product.

Assignment/deliverable instruction:

1.Design a logical schema of this data warehouse based on above story, using Erwin or MS Visio or other modeling tools

2.Convert this logical schema into Physical schema (at this level, all the data types have been defined and reflected in the schema)

3.Submit both schema in Moodle with some descriptions

4.Prepare PPT to illustrate how you come out the solution(s), and ready to present on class on Dec. 10. (this part does not need to submit in Moodle)

5.Option: you may generate DDL if you have database installed locally.

For Erwin: steps

1.Start New – Logical

2.Select Model from the top manual bar, and pick “Subject Area …”, name it as Data Mart as following:

The following are tables for your reference.

Note: you may put Data Type as text or Varchar instead of “Short Text.”

pleace send me answers

Explanation / Answer

The age of Turbo C++ all over a protracted time agone, together with the utilization of .h once header files. For the new C++, we have a tendency to don't use <iostream.h>, we have a tendency to instead use <iostream> and once the headers add a mistreatment namespace std;. we have a tendency to not got to add the .h. For native C headers, we have a tendency to add a c before the header, as an example, <stdio.h> in C++ becomes <cstdio> and <string.h> becomes <cstring> ( there's conjointly a brand new header <string> in C++, don't combine it with <cstring> ) , and so on.

Also, void main() isn't any longer accepted per the C++ commonplace. you want to use int main(). you'll be able to use void main() on Turbo C++, however not on newer versions. you want to use int main() and even have a come zero; ( most new compilers are going to be okay though you skip the come 0 in main() )

Also, some headers like <conio.h> ( therefore no mistreatment getch() or clrscr() ) and <dos.h> don't seem to be pat of the quality, therefore do not use them. Here's an inventory of normal libraries

Let Maine show you straightforward Program.

#include<iostream>
using namespace std; // do not forget this
int main()

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.