Question 1)
Read the paper provided with the assignment: "Development of traditional Chinese medicine clinical data warehouse for medical knowledge discovery and decision support" by Xuezhong Zhou, Shibo Chen, Baoyan Liu, Runsun Zhang, Yinghui Wang, Ping Li, Yufeng Guo, Hua Zhang,
Zhuye Gao and Xiufeng Yan, published in Artificial Intelligence in Medicine, Volume 48, Issues 2-3, February-March 2010, Pages 139-152.
(1) Based on the entities listed on the conceptual view on figure 3, provide a star schema for the fact "treatment"
(2) Explain how the approach proposed in the paper is different from one that would be for western medicine
(3) Summarise the differences between the ETL process in the paper and that in the scenario
Question 2)
You are required to design a data warehouse schema for recording the history of rental services of XYZ's customers (not including the customers who have ordered or picked up cars but have not returned their rental cars). The schema needs including the necessary data about the demonstration. The fact table should contain four kinds of data dimensions:
customer information, car information, store information and time. For each data dimension, you need to consider a reasonable hierarchy (e.g., time dimension - Year, Month, Week, Day; Geography - Country, State (Province), City, St.). More specifically, you need to answer the following four questions:
(1) Proceed to a source analysis: list the all facts and dimensions of the enterprise
(2) Provide a data profile to describe the source data (read "carRentalDataSource.xlsx" to answer this question)
(3) Chose a model (star, snowflake or constellation) and discuss why it is an appropriate choice
(4) Draw a diagram to show your proposed data warehouse schema
Question 3)
Create a database that implements the proposed data warehouse schema. A flat data source can be found in the attached "carRentalDataSource.xlsx" file. You can choose them as your source data. Note that some data in the file may not be correct. Your fact table should only include the data you described in Question 2 (3).
For this question you need to include in your report the following:
- An SQL script used to generate your database
- A print out of your database in MSSQL (use your surname to name the database and the tables, e.g. sitbon_table1)
Question 4)
Assume the data warehousing system is centralized, and implemented in the environment of Microsoft SQL Server 2012. As we discussed in the introduction, each store has its own local database. Therefore, you can think the source data include both the operational databases
for ordering and stores' local databases. After a customer returned a car successfully, the system should create a record for this customer and save the record into the central data warehouse.
It is essential to the business to maintain qualified data in the data warehouse. Therefore, you are required to design an ETL process for this question. You are required to decide an application (ETL or ELT) first and then discuss the advantages and/or disadvantages of your proposed application . You also need to define ETL functions to clearly understand what data the data warehouse has and what data the data warehouse does not have.
Question 5)
The board of XYZ expects the data warehousing system to provide some functions to analyse their customers in order to improve their management and services. In this assignment, you are required to design data cubes for the following two issues:
(1) Knowing the customers for targeted advertising. The board would like to know which type of customers (age or gender) are more interested in which types of cars (type)
(2) Car recommendations to customers. The board hopes to provide a new service to customers to help them choosing a car (type) based on customers geography (city) information and time (Month).
For each issue, you are required to define one data cube only using SQL Server Data Tools for Business Intelligence. You also need to name the data cubes using your surname followed by the real data cube name (e.g., "SITBON cube1"), deploy it and then print out the result in
your report. The discussion of the two results is also necessary.