Explain the difference between the Natural key, Durable key and a Surrogate key .
Discuss the benefits of using surrogate keys and durable keys in dimensional designs by an example.
3. SCD solutions
Suppose that the account dimension table was designed as follows to record each account of customers at the bank system. The account number is usually the natural key of the account dimension. However, the credit card account ID 8765 has been stolen, a new account ID 9877 has been created with a new surrogate key. The association of the same account has been lost. There are two different surrogate keys and account numbers for the same account holder Alex Smith. The account holder Alex Smith may request to change his home address for a new location to Bundoora. What is your solution to link the account history with current account ID? You need to use your learnt knowledge to re-design the account dimension with a different structure. ( Use the Response Editor to create a table as your need).
4. Case Study Subway Chain
Subway is a well-known world’s largest chain of hamburger fast food restaurants. The major corporation’s revenues come from the sales in company-operated restaurants and the rent, royalties and fees paid by the franchisees. The following is some of key business processes of the company.
Business Primary Activities
Subway primarily sells fast foods including hamburgers, cheeseburgers, chicken, French fries, breakfast items, soft drinks, milkshakes and desserts, etc. Each food item has a unique code across all restaurants in the company. The management needs to monitor and analyze the foods popularity for developing new types of foods.
Subway purchases/orders raw materials and products from its suppliers. These materials include beef, chicken, milk, cheese, fresh vegetables and semi-cooked foods, etc. The procurement has made a number of agreements with local suppliers that allow the chain restaurants to have the supplies and materials at a discounted price, ultimately reducing costs of their businesses.
The mass-production process requires each restaurants chain to have a distribution network to carry the food to every restaurant. Warehouses store enormous amounts of everything a restaurant needs, including foods, paper products and cleaning supplies. A computer keeps track of what the restaurants have and should have on hand. The distribution canter ships the necessary items on a regular schedule instead of waiting a request from the restaurant. Warehousing and distribution is centralized management. The management needs to analyze the demand frequency, locations and restaurants performance.
The Subway's managers want to further improve their business performance and seek opportunity of expansion. They wants to monitor the restaurant sales to develop new products, variety of foods and new services; reduce the cost at each stage of the supply chain management including the material orders, supplier delivery, inventory management and distribution to local member restaurants to achive maximal benefits.
Your task is to
- Identify Subway's Corporation business processes and draw a bus matrix in order to build an enterprise data warehouse for their business intelligence. The bus matrix should include the identified business processes and shared dimensions between those business processes. (Use the editor's tool bar to create a table for your bus matrix),
- Model the Procurement business process for raw materials/product with a star-schema. Identify the appropriate fact type and grain with related dimensional tables. Identify the special dimeson in the design. (You can use the computer software tool to draw the diagram, save the file for uploading or Draw the diagram on the paper and take a photo to upload the image to the answer box below.
The Procurement supply chain provides the backbone not only to all the logistics but the whole Subway supply chain management. Procurement involves a wide range of activities from negotiating contracts to issuing purchase requisitions and purchase orders (POs) to tracking receipts and authorizing payments. The materials will be delivered to the centralised warehouse in a local city for production.
The following list gives you a better sense of common analytic requirements:
What materials can attract the best deal associated with the suppliers? Do we have the longest agreement period with preferred suppliers?
How many suppliers supply the same materials? Are there suppliers’ prices variance?
How are those suppliers performing? What is the suppliers’ fill rate? On-time delivery performance? Late deliveries outstanding? Percent back ordered?
Which suppliers have the highest rejection rate based on receipt inspection?
How many days that the ordered materials can arrive at the warehouse? What is the life cycle of the procurement activities from issuing purchase requisitions and purchase to authorizing payment and tracking receipts?
What payment method is the most preferable way to pay suppliers? What is the commission rate to our procurement staff?
5. Special dimension design
Suppose that there is a desire within Perfect Property for improved access to market information. The business analysts are particularly interested in leveraging market information to assist buyers and vendors in providing estimates of selling price ranges for different property types in the area. Therefore, the analysts need to access area’s demographic information frequently. The demographic dimension attribute values may be often changing. The demographic data would be loaded at different times to the rest of the property data.
Assume that the property dimension is very large and each property row is very long as described in table 2. You are required to
Re-design the property dimension using one of dimension management techniques we have learned in the lecture.
6. ETL subsystems
We have 34 subsystems to support the 4 steps of the ETL process.
For each of the following subsystems, identify which major step of 4 steps of ETL process, the subsystem belongs to. Fill in the underline spaces by the name of steps.
- Change Data Capture System .
- Multi-Valued Dimension Bridge Table.
- Audit Dimension
- AssembleSorting System
- Problem Escalation System
OLAP tools are categorized according to the architecture used to store and process multi-dimensional data. There are three main categories:
List two types of OLAP tools and explain their features on data structures, the architecture, storage and the query performance with the comparisons between the two.