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

Answer 1

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);