This assignment deals with multidimensional querying in a data warehouse (Advent
ID: 3866848 • Letter: T
Question
This assignment deals with multidimensional querying in a data warehouse (AdventureWorkdsDW)
The finance department wants to be able to project future financing details. The department manager sent you a request for a rolling 3 month average on past financials. Develop the SQL query code used to explore the database tables and write a query that retrieves finance amounts from "FactFinance" in the AdventureWorkdsDW database and returns those amounts organized by month and showing a 3-month rolling average.
Table Name - FactFinance
Table Columns - FInanceKey, Date Key, Organization Key, DepartmentGroupKey, ScenarioKey, AccountKey, Amount, Date
Explanation / Answer
Command to create the table is as follows:
Create table FactFinance
( FInanceKey INT (6),
DateKey varchar(10) NOT NULL ,
Organization Key VARCHAR(10),
DepartmentGroupKey VARCHAR(15),
ScenarioKey VARCHAR(15),
AccountKey VARCHAR(15),
Amount INT (6),
Date date (6),
PRIMARY KEY (FInanceKey)
);
Command to explore the database tables:
Show tables;
Command to view database table:
Select* from FactFinance
Query that retrieves finance amounts from "FactFinance" in the AdventureWorkdsDW database and returns those amounts organized by month and showing a 3-month rolling average is as follows:
SELECT avg(amount) as Financeamount
FROM FactFinance
WHERE timestamp >= now()-interval 3 month order by month(Date);