Reference no: EM13908955
Description:
Part 1 - Prototype Data Warehouse
a) Use Dimensional Modelling Technique to design a prototype data warehouse using the data on invoices for spending over £1,000 in the Department for Work and Pensions (https://data.gov.uk/dataset/financial-transactions-data-dwp ) for the Period 2012-2013 (April 2012 to April 2013 inclusive). Negative values in the AMOUNT column represent refunds and as such are valid and must be kept. For refunds, amounts of £250 or more are valid.
b) Use SSIS perform ETL on the invoice data to populate the data warehouse, in doing so you should demonstrate the use of SSIS filters/expressions and SQL where appropriate.
c) Create an OLAP cube from your data warehouse and perform the following analyses:
1. Show the top three Sub-Categories and the total amount spent on these for the year and for each of the four quarters e.g. April-June, July-September, October-December and January-April.
2. Show the top five expense areas and the totals amount spent on these for the year and for each of the two half-years April-September and October-April.
3. Show the four suppliers who refunded the most money and the totals they repaid for the year and for each of the four quarters April-June, July-September, October-December and January-April.
d) On paper, design a simple dashboard for your data warehouse.
This should be accompanied by a 1500-word report which describes/evaluates the creation and use of your data warehouse. Your report should include the following:
i. A description of the source data used.
ii. An explanation of any design decisions you have made.
iii. An explanation of your ETL process, highlighting any particular problems you had to overcome and including examples of any SQL queries you have used.
iv. A relationship diagram or database schema.
v. A description of how the analyses were performed, illustrating the results of the analyses with screenshots.
vi. Excel screenshots of your dashboard design and the reasons for your design choices.
All parts
The report should be appropriately referenced and should include the appropriate tables of contents, tables of figures and tables of tables. Any figures should have titles and identification labels. The document should also be spellchecked and grammar-checked.
|
Prepare the journal entry to charge overhead costs
: Prepare the journal entry to charge overhead costs to the Goods in Process Inventory account and to record any variances.
|
|
Determine the average investment in accounts receivable
: Pretty Lady Cosmetic Products has an average production process time of forty days. Finished goods are kept on hand for an average of fifteen days before they are sold. Determine the average investment in accounts receivable, inventories, and account..
|
|
Show that the expected service time of customers
: View the customers in (a) as a single type of customer with Poisson arrivals of rate λ = J, λi and with a service density J, (λi/λ)μi exp(-μix). Show that the expected service time is ρ/λ.
|
|
What is the value of martell minings stock
: Martell Mining Company's ore reserves are being depleted, so its sales are falling. Also, because its pit is getting deeper each year, its costs are rising. As a result, the company's earnings and dividends are declining at the constant rate of 8% pe..
|
|
Design a simple dashboard for your data warehouse
: Use SSIS perform ETL on the invoice data to populate the data warehouse, in doing so you should demonstrate the use of SSIS filters/expressions and SQL where appropriate - Create an OLAP cube from your data warehouse and perform.
|
|
What factors that led to the downfall of failing business
: I am in need of some help composing a 4-5 page report regarding a failing business and a business that is currently very accomplished. Specifically, one outling what factors
|
|
Seconds of elapsed time to execute
: Suppose that a certain program takes 500 seconds of elapsed time to execute. Out of these 500 seconds, 280 seconds is the CPU time and the rest is I/O time. What will be the I/O time?
|
|
Stock is expected to pay a year-end dividend
: A stock is expected to pay a year-end dividend of $2.00, i.e., D1 = $2.00. The dividend is expected to decline at a rate of 5% a year forever (g = -5%). If the company is in equilibrium and its expected and required rate of return is 15%, The company..
|
|
How have violent injuries affected a community?
: How have violent injuries affected a community?
|