Analysis SQL

已完成 已发布的 Jun 29, 2014 货到付款
已完成 货到付款

Part 1:

Develop two data markets; Share dimensions across data markets; Handle semi-additive measures.

Requirements:

You are asked to use the Foodmart database (file: [url removed, login to view]) that you should first import in SQL Server Management Studio to develop a sales data market in SQL Server BI Development Studio.

The first data market is about the sales business process. Your fact table should be about the sales for 1998 (be careful: not just December 1998). As dimensions, please include:

• Product (be careful: product dimension is “snowflaked”)

• Time (time by date)

• Customer (don’t include the attributes Mi, Address2, …, Address4).

• Promotion

• Store

You may wish to include several of the available attributes for the dimensions and also to introduce hierarchies, when possible. As measures in the fact table, please include:

• Store Sales

• Unit Sales

The second data market is about the inventory business process. Your fact table should be about the inventory for year 1998. As dimensions, please include:

• Product

• Time (time_by_day)

• Store

• Warehouse (be careful: warehouse dimension is “snowflaked”)

You may wish to include several of the available attributes for the dimensions and also to introduce hierarchies, when possible. As measure in the fact table, please include:

• Units ordered

Here we make the assumption that after products being ordered (from the supplier), the delivery may not be always successful. Therefore, orders may be repeated over time. This way, you can treat “Units ordered” as a semi-additive measure. Please explain how you can (re-)define1 “Time by Date” as dimension of type “Time” and which Aggregate Function for averaging do you have to use for the “Units ordered” measure.

Deliverables

As deliverables for Part 1, please make:

• A brief report with screen-shots and short description of the steps you followed.

• One example of a screenshot from the “Browser” for the sales and one for the inventory data market (please pick just the time dimension).

--------

Part 2: Perform OLAP operations such as roll-up/drill-down/slice; Connect MS SQLServer with MS Excel.

Requirements

You are asked to use the “Sales” cube that you created in Part 1 and generate Excel charts for the following OLAP reports and answer the questions listed below:

1. Profit with respect to Gender and Marital Status. Profit can be defined according to Store Sales (or alternatively according to Unit Sales).

• Question(s): Is Marital Status a more important factor for Males or Females with respect to profit?

2. Profit with respect to all Store Cities in USA and the 4 quarters of year 1998.

• Question(s): Which Store City had the largest profit for the fourth quarter of 1998?

3. Profit with respect to Promotions in Store State ‘OR’ of USA. Exclude ‘No promotion’ from the result.

• Question(s): Which promotion resulted to the largest Profit? Please provide your comment about your answer to the last question.

4. Profit with respect to Customers State Province in USA and their Marital Status for the four quarters of year 1998.

• Question(s): In which State (or States) is Profit largest in the fourth Quarter of 1998; and is this happening both for ‘Married’ and ‘Single’ customers or only for one of these categories? How could you interpret this conclusion about the found state (or states), assuming that the fourth quarter of the year includes the pre Christmas (data is up to November) shopping period? Drill-down to the level of Customer City for the found state (or states) and check whether this conclusion (i.e., comparison between ‘Married’ and ‘Single’ in Q4) holds for all or some of the cities.

Deliverables

As deliverable for Part 2, please make:

• Excel charts showing the results of the OLAP queries.

------------------

For part 3 and 4 please look at the uploaded PDF document.

Budget can be extended to max. $ 50

Excel 微软 SQL 服务器 SQL

项目ID: #6128352

关于项目

5个方案 远程项目 活跃的Jul 2, 2014

授予:

abdellatif81

Hello, I am IT engineer with European diploma since 2005 Certified as Technical specialist in Microsoft SQL Server databases BI, i have more than 8 years in IT projects with 5 years working around databases and BI 更多

$130 USD 在3天内
(11条评论)
4.9

有5名威客正在参与此工作的竞标,均价$66/小时

acronymsolutions

Hi, I have gone through your requirements and understand Thoroughly. I am a DBA at CMM-5 Level company since August 2008.I am working on MySql,SQL ,SQl Server,Oracle database, I can do your job confidently. I am very 更多

$50 USD 在2天内
(10条评论)
4.3
omprakash1981

I am good in sql queries. I have done many project using C# and sql server. I have also done a data mining project using sql server

$15USD 在1天里
(0条评论)
0.0