UDB8QKGm

11)
2Since there is a small number of data sources judging based off the the example from the CSV file we could build our own architecture (set of scripts) for extracting the data. The data is also only updated on a daily level, so the amount for the load isn’t as great as it could be if it was, for instance, hourly. In order to increase data access I would opt for cloud storage – but I would be downloading the data to physical storage as well in case the server is unavailable, since it is stated that the API server has reliability issues. I believe together with live backup it is reliable enough to store data in cloud, but never only on the server. ETL vendors offer 1GB/sec, but I believe that we don’t need such high speeds for dealing with a data warehouse for the data from the example.
3
4Adding date dimension to a data warehouse
5CREATE TABLE dbo.Dates (
6 DateID int NOT NULL IDENTITY(1, 1),
7 [Date] datetime NOT NULL,
8 [Year] int NOT NULL, 
9 [Month] int NOT NULL,
10 [Day] int NOT NULL,
11 [QuarterNumber] int NOT NULL,
12 CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED (DateID)
13)
14DECLARE @StartDate datetime
15DECLARE @EndDate datetime
16
17--- assign values to the start date and end date we 
18-- want our reports to cover (this should also take
19-- into account any future reporting needs)
20SET @StartDate = '01/01/2005'
21SET @EndDate = '31/12/2007' 
22
23-- using a while loop increment from the start date 
24-- to the end date
25DECLARE @LoopDate datetime
26SET @LoopDate = @StartDate
27
28WHILE @LoopDate <= @EndDate
29BEGIN
30 -- add a record into the date dimension table for this date
31 INSERT INTO Dates VALUES (
32  @LoopDate,
33  Year(@LoopDate),
34  Month(@LoopDate), 
35  Day(@LoopDate), 
36  CASE WHEN Month(@LoopDate) IN (1, 2, 3) THEN 1
37   WHEN Month(@LoopDate) IN (4, 5, 6) THEN 2
38   WHEN Month(@LoopDate) IN (7, 8, 9) THEN 3
39   WHEN Month(@LoopDate) IN (10, 11, 12) THEN 4
40  END 
41   
42 )  
43 
44 -- increment the LoopDate by 1 day before
45 -- we start the loop again
46 SET @LoopDate = DateAdd(d, 1, @LoopDate)
47END
48
49IF EXISTS (
50 SELECT * 
51 FROM sysobjects 
52        WHERE type = 'U' 
53  AND ID = OBJECT_ID('[dbo].[Dates]') 
54)
55DROP TABLE [dbo].[Dates]
56
572. 
58A) 
59
60SELECT SUM (AP Amount)
61	FROM (Report_August_17) GROUP by expense area
62B) 
63SELECT SUM (AP Amount)
64	FROM (Report_August_17) GROUP by expense area AND expense type
65
663.
67I would create a pivot table in excel, from organized data sets. Creating a pivot table in excel is straight-forward.