Use Microsoft SQL Server Analysis Services (SSAS) to perform OLAP operations on a cube called TS located in a multidimensional database called Time Series that has been defined based on the tables in the database given by the description below (see Assignment#5 instructions for the steps to log into Microsoft SQL Server Analysis Services (SSAS) then locate the Time Series database and the TS cube):

Tb_Supplier(Supp_ID, Name, City, State)
Tb_Consumer(Con_ID, Name, City, State)
Tb_Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging)Tb_Offers(Supp_ID, Prod_ID, Quantity, Price)
Tb_Requests(Con_ID, Prod_ID, Quantity, Price)
Tb_Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price) Tb_Date(DateKey, DateYear, DateMonth, DateMonthName, DateWeek,

DateDayOfMonth, DateDayOfWeek, FiscalWeek, IsWeekEnd,

IsHoliday, HolidayName)
Tb_Time(TimeKey, TheHour, IsLunchHour, IsBusinessHour, ShiftNumber)

The dimensions of the cube are: Tb_Supplier, Tb_Consumer, Tb_Product, Tb_Date and Tb_Time.

Measure groups are: Tb_Offers, Tb_Requests and Tb_Transactions.
 

We define the following dimension hierarchies within the dimensions

     

Tb_Supplier dimension:
SH hierarchy: State > City > Name

Tb_Consumer dimension:
CH hierarchy: State > City > Name

Tb_Product dimension:
HNP hierarchy: Product_Packaging > Name
HNLC hierarchy: Product_Category > Product_Line > Name

In addition, there are several hierarchies defined on the Tb_Date dimension. Tb_Date dimension:

HMOFY hierarchy: HMQY hierarchy: HQOfY hierarchy:

Date Month Of Year > Date Month Name > Date Month > Date Key

Date Year > Date Quarter > Date Month > Date Key

Date Quarter Of Year > Date Quarter > Date Month > Date Key

There are no hierarchies defined on the Tb_Time dimension.

 

Given the TS cube located in the TimeSeries database solve the following queries:

1. What are the comparative sales for each product in category transportation (on rows) by months of year during 2019 (on columns) (replace with zeros where nulls, format for money type)?

2. Total sales during each quarter (on rows) of 2019 vs quarters of 2018 and net one year growth (on columns) (use PARALLELPERIOD())?

3. Quantity of milk sold in each consumer city (on rows) during first quarter of 2019 vs first quarter of 2018 (on columns) (use PARALLELPERIOD())?

4. Monthly YTD quantities (on rows) sold of each product line (on columns) in transportation during 2019 (use PERIODSTODATE())?

5. Monthly YTD quantities (on rows) sold of each product line (on columns) in transportation during 2019 (use YTD())?

6. Quantity of milk consumed in Chicago during each of the first 7 month of 2018?

7. Daily quantity of milk sold during January 2019, as well as MTD and last 10 days total quantities?

8. Quantity of milk sold in each consumer city (on rows) during first 5 months of 2019 vs first 5 months of 2018 (on columns)?

Answer rating: 100% (QA)

The queries provided require specific SQL Server Analysis Services SSAS MDX Multidimensional Expressions statements to retrieve the desired informatio... View the full answer