Design and implement a simple data warehouse environment with star schema, data warehouse structure

You are a BA that is employed by a multinational professional services ([login to view URL] ) firm on the basis of some work you did in ICT394 as a student. You have been contracted out by your employer to a company that sells a large range of random items, called Omnibenchmark Pty Ltd.

You have been tasked with creating a data warehouse so Omnibenchmark can analyse their sales in order to assist with their management decision making, in particular, they wish to be to answer the following questions:

- Total of sales of each product by Year/Month/Week/Day

- Total sales of products by supplier by Year/Month/Week/Day

- Total sales by branch by Year/Month/Week/Day

- Total sales of product by branch by Year/Month/Week/Day

- Total sales by sales clerk by Year/Month/Week/Day

- Total sales by product by sales clerk by Year/Month/Week/Day

- Total sales by product type by sales clerk by Year/Month/Week/Day

You have been given access to three data sources from which you will need to draw your data. Below are the sources and the tables within them that are/may be of interest to you. These are discussed below (Primary Key, Foreign Key):

Source 1: Human Resources Database

The Human Resources (HR) database contains data about the people who are employed by Omnibenchmark. You have been granted access to a VIEW that includes the data you may need for the various analyses required. It only includes details of employees currently employed by Omnibenchmark in a Sales-related position. The view has been supplied to you as a text file.

The VIEW is called viewEmployee and has the following columns:

viewEmployee (EmpID, LastName, FirstName, DateStarted).

Source 2: Inventory System Database

The Inventory System maintains stock levels for all products sold by Omnibenchmark as well as the details of the Supplier. The data are stored in a proprietary system to which you have not been granted access. However, an export of two of the tables has been performed for you and supplied as an Excel file. The two tables are the Product table and the Supplier table. They are structured as below:

PRODUCT (ProductNum, ProductName, SupplierNum, ProductType)

SUPPLIER (SupplierNum, SupplierName, Country)

Source 3: Sales Database

The Sales database is an oracle database (luckily for you, it’s hosted on [login to view URL] and owned by SICT394AssignTL – you have SELECT privileges on these tables)

SALE (SaleID, ClerkID, CustomerID, SaleDate,)

SALE_LINE_ITEM (SaleID, LineID, ProductID, ProductPrice, Quantity)

SALE_CLERK (Clerk_ID, Clerk_Name, BranchID)

CUSTOMER (CustomerID, FamilyName, GivenName, Address, Suburb, State, PostCode)

PRODUCT (ProductID, ProductName)

BRANCH (BranchID, BranchPostCode)

What you have to do:

Task 1: Based on the list of questions the client wants answered (see above), discuss what you see as being the most appropriate level of granularity for your data warehouse. Your discussion will need to explain why you have made this choice, and why the alternatives have been discarded. This should take not more than one (1) page.

Task 2: Based on your answer to Task 1, design a Star Schema that will support the analyses as listed above.

Task 3: Create the data warehouse structure on arion:

- Create the Dimension tables, but only include the primary key (i.e., there is no need to create the other columns in the dimensions tables, just the primary key column)

- Create the Fact table(s) which should contain the following:

o Foreign key columns (and constraints) referring to the Dimension tables

o Measure columns

Aptitudini: Administrare baze de date, Administrare, Redactare raport, Redactare documentație de cercetare

Vezi mai multe: data warehouse implementation steps, star schema example for university, star schema examples, data warehouse design example, data warehouse example diagram, building a data warehouse: with examples in sql server, data warehouse example, star schema example with data, design implement graphical application displays slot machine java, implement simple chat red5 server audio video, design implement record management system, implement simple blog website, good design websites simple, sql load data star schema, insert data star schema, assignment data warehouse star schema, star schema examples data warehouse, data warehouse implementation using star schema, create star schema data warehouse, star schema diagram data warehouse

Despre angajator:
( 2 recenzii ) Singapore, Singapore

ID Proiect: #20049951

8 freelanceri licitează în medie 53$ pentru acest proiect


1. I am an expert in Design and implement a simple data warehouse environment. I read your project description and I am sure that I can handle your project. 2. Also an expert in Academic writing, research reports, Mai multe

%bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% SGD în 1 zi
(141 recenzii)

Hello! I have briefly read the description on design-implement-simple-data-warehouse/ development project, and I can deliver as per the requirements however I need us to discuss for more clarity on the details Mai multe

%bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% SGD în 1 zi
(87 recenzii)

Dear Sir/Mam, I have recently done a Data warehousing and mining project, in which we have done the tasks of gathering data from different text files, normalization of data with latest Data Intelligence tools and much Mai multe

%bids___i_sum_sub_35%%project_currencyDetails_sign_sub_36% SGD în 7 zile
(6 recenzii)
%bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% SGD în 1 zi
(0 recenzii)

If all the requirement is given then can able to complete its development in 2 days. Using the below join we can merge the snowflake joins to one logical table(dimension) and then join the logical table(dimension ) wi Mai multe

%bids___i_sum_sub_35%%project_currencyDetails_sign_sub_36% SGD în 2 zile
(0 recenzii)

Dear Hiring Manager, I am an Oracle Certified Database Professional (OCP). I have complete masters in computer science. I am a database professional with 15 years’ experience. My background includes Sql, MySQL, Pl Sql, Mai multe

%bids___i_sum_sub_35%%project_currencyDetails_sign_sub_36% SGD în 7 zile
(0 recenzii)

Accounts Payable Processes & Management, Invoices/ Payment [login to view URL] company's vendor/suppliers relations by continuously corresponding with vendors via phone, email or written [login to view URL] Mai multe

%bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% SGD în 1 zi
(0 recenzii)

Oracle Database Administrator & Business Development Manager Planning, Designing & Systems Development Supervisor • Analysis, Design, Build & Document, Transition, Developing & Finally Testing For In-House Developmen Mai multe

%bids___i_sum_sub_35%%project_currencyDetails_sign_sub_36% SGD în 7 zile
(0 recenzii)