Understand How to Analyse OLTP Databases - IT Assignment Help

Assignment Task:

Task:

Purpose of Assignment 

• Understand how to analyse OLTP databases 
• Understand how to design an OLAP Database Schema 
• Develop SQL script using appropriate techniques to perform ETL process 

Specifications and Instructions 

In year 2012, Models Sales Company had  MySQL database management systems (OLTP database, salemodels) to store their transactional data related to their customers, products, and sales transactions. Later they bought company that run a same business in Pacific region. That company has MSSQL database saleAU_NZ.

Now, the Sales department manager wants to integrate sales data stored in the two databases into a single database to help them have a central access to all data and perform data analysis. Therefore, they have decided to implement a data warehouse for this purpose. Assume that you have been employed as a database analyst for the company and you are assigned to carry out the following tasks to design and populate a data warehouse for the company.

Please note that saleAU_NZ was implemented on MSSQL management studio and salemodels was implemented using MySQL

Fortunately, the same schema is designed for both salemodels and classicmodels, as shown in Figure 1y, only slight different is in the tables name. Please check.

The Profitability (P) of a product can be defined as follows:

P = [Order Details].PriceEach– Products.buyPrice

If P > 0, the product makes profit; If P < 0, the product makes loss.

Important Totals for Fact table:

[TotalPrice]=(od.QuantityOrdered* od.PriceEach) 

[TotalProfit]= (od.QuantityOrdered * (od.PriceEach - p.buyPrice))

[TotalPossibleProfit] =(od.QuantityOrdered * (p.MSRP - p.buyPrice)) 

(p- Product table, o- OrderTable, od -Order Detail Table)

The manufacturer's suggested retail price (MSRP) is the price that a product's manufacturer recommends it be sold for at point of sale.

Please note that same product sold price is different in different order details, as different discounts for the same product was run in the different time frames.

Please be mindful that total sales of a product is equal to the total Quantity from all of the orders. The total number of products sold/bought refers to how many different ProductIDs are sold/bought.

Tasks T1 OLAP Design

The sales department has requested you to design a data warehouse so that reports can be generated quickly. A list of sales reports that are required by the department are: 

1. Monthly sales and profit/loss report for each product line. For each product line, display its name, the number of products in that category, the total number of sales of all the products in that category, and the total profits/loss generated so far. Rank the line based on the total profits/loss generated in descending order.
2. Monthly report on the most profitable employees. For each employee, display its name, city, country, the total number of customers he/she supported, the total number of payments from his/her customers processed. Rank the employees based on the number of profits generated in descending order.
3. Summary report on the total sales based on City. For each city, display its name, country, the total number of products sold, the total number of product category sold, the total number of customers who live there and the total sales of product sold. Rank the city based on the total sales of products sold.
4. Monthly report on the customers who have bought the most. For each customer, display the customer’s name, the total number of products bought, the total number of product categories bought, the total sales of products bought and the total profits/loss generated so far. Rank the customer based on the total profits/loss generated in descending order.  

The company is very cautious so they want you to propose a DW design using each of the DW schemas (star, snowflake and fact constellation). Select two out of 3, and for each schema,

1. draw the detailed OLAP ERD design
2. list a sales report, not mentioned above, that you think is useful for the company and that highlights the advantages of using this schema
3. provide details on the tables and attributes that are ignored and explain why
4. write the advantages and disadvantages of your proposed design with respect to meeting the requirements.

T2 Data Warehouse selection

Choose one of the proposed schemas from the previous task providing a clear rationale on why you chose it. You must present a clear discussion on evaluation and selection criteria. This may include problem specific suitability, drawbacks with other schemas for this problem etc.  

T3 ETL process.

Create your OLAP database based on your chosen schema then extract, transform and load data from both databases to your OLAP database.

1. record differences between two DBs and record which settings you need to do to export DB. Record all you changes and corrections for sql file that you make
2. Use MSSQL for both DB.

All data in the OLAP database must meet the following criteria. 

1. All data from all tables of the source databases must correctly appear in your OLAP database.    
2. All PKs and FKs must be valid.  

This IT Assignment has been solved by our IT Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.