Homework 12. OLAP
In this homework we focus on learning the basic skills of exploring data cubes. Optional tasks also allow you to practice design of cubes.
You have been assigned to a sales team in a sales region of AdventureWorks company. Your task is to keep track of how the region is meeting its sales goals.
Task 1. From “Adventure Works” cube, determine the top 5 sales reasons for most internet orders (Internet order quatity) and highest “Internet Gross Profit Margin” for your region. Make sure your report includes: Sales territory, Sales reason category, Sales reason, Internet Order Quantity, and Internet Gross Profit Margin.
As a solution, you should submit the resulting report (e.g. table) and the method used to query the database.
Tip 1. SQL Server Manager’s MDX query composer or cube browser and Excel allow you to easily and visually explore the cubes structure.
Task 2. The managers are also interested in finding how the sales revenue has been growing year-to-year. Your task is to produce a report showing the following measures for each fiscal year tracked by the analytical database’s cube “Adventure Works”:
- Sales territory
- Fiscal year
- Sales amount
- Revenue goal
- Revenue status indicator
- Revenue trend indicator
- Revenue increase from previous year
The analytical database (SQL Server Analysis Services) is hosted in sandstorm.cs.ut.ee OLAP database “Adventure Works DW 2008R2”. You can connect to the database using SQL Server Manager (installed on sandstorm.cs.ut.ee – you can connect there via Remote Desktop), Microsoft Excel, Microsoft Power BI or any other XMLA or ODBC client which supports MDX queries. Note that sandstorm.cs.ut.ee is only accessible from UT intranet. Thus you need to establish a VPN connection to UT network when working from remote (see for instructions). Note that you might need to specify domain controller name DOMENIS while logging into sandstorm.cs.ut.ee.
As a solution, you should submit both the resulting report (e.g. table) and the query (MDX or DAX) used to query the database.
Tip 2. You can access KPI value, goal, trend, and status using functions KPIValue, KPIGoal, KPITrend, and KPIStatus with KPI name specified as a string type argument to the function.
Tip 3.In MDX, you can override the general selectors for individual measures by specifying a crossjoin with a different member of the dimension. Crossjoin operations are defined in braces with comma separated list of crossjoined measures or dimension members. For example, an expression “(KPIValue("Operating Profit"), [Date].[Fiscal Year].&[2008])” will select the KPI “Operating Profit” value for fiscal year “2008”.
Tip 4. You can use function ParallelPeriod to select parallel hierarchy members (e.g. shifted time periods).
Task 3. The analytical database also includes some data mining models. In particular, models in the “Customer Mining” structure were used to produce a corresponding “Mined Customers” cube. Using the cube browser, Excel’s SQL Server Analysis Services data connection or Power BI, find the customer cluster that brought the largest internet gross profit in fiscal year 2009. Submit both the answer and the procedure used to reach the answer.
Task 4. From “Adventure Works” cube, determine whether the salesmen (Employee dimension) offering higher discounts also bring in higher profits. Note you need to work with measure group “Reseller Sales” in order to answer this question.
Task 5. While exploring the cube you might have noticed that some measure groups contain measures that are not affected by all of the dimensions of the cube. In SQL Server Analysis Services this is due to the fact that each measure group is based on a fact table. This means the cube actually is based on multiple fact tables. This can be confusing for end users. In order to resolve such confusion, the cubes offer different perspectives, which limit the users’ view to dimensions and measures that are related. Study the perspectives and try to describe, which employee groups are the perspectives oriented to.
Optional task 1. Set up your own AdventureWorks OLAP database. You can download the pre-built AdventureWorks database files from . Modify the “Adventure Works” cube by adding a new database role that can only access data about “North America” region. Describe how you did it.
Optional task 2. Create a new cube for HW11 data from Tax Authority. Answer HW11 tasks 1) and 2) using this cube.