IT Connect

Information technology tools and resources at the UW

Analyze Data Using Cubes


What is a Cube?

A cube is a data analysis tool that allows quick and easily manipulation of data. A cube can be used to answer business questions, such as “How much money was spent, by fiscal year, by organization, by budget, by expense object?”

Why is it called a cube and how does a cube answer this question?

  • Let’s use the example question above – How much money was spent BY fiscal year, BY organization, BY budget, and BY expense object?. Each of those BY clauses is a dimension.
  • Because a cube elicits the notion of 3-dimensionality, the word cube was adopted to represent data organized to facilitate exploration by any number of dimensions.
  • A data cube has one other major component, called a fact or a measure. These terms are used interchangeably. The “How much” portion of the question is what is being measured. Cubes can also contain counts, percentages, averages, etc.
  • A cube provides a way of storing data that allows users to get answers to ‘how much’ or ‘how many’ questions, sliced by the dimensions of the user’s choice.


Using Cubes

Cubes can be accessed using Microsoft Excel PivotTables. You can also open a cube with other software such as Tableau. UW’s central Business Intelligence environment uses Microsoft Analysis Services to build and manage cubes.


Using Cubes on a Mac

The Mac operating system does not support online analytical processing (OLAP) technology. SQL Server Analysis Servers (SSAS) work only in Windows Integrated authentication, which is not supported by the Mac operating system.

You can connect to an EDW cube on your Mac if you are using a remote connection to a PC on the UW Network or if you are running an application that allows you to run Microsoft Windows virtually.

Remote PC Connection

Once you connect to a UW PC, follow the regular directions in the Connect to Cubes page on how to connect to EDW cubes using Microsoft Excel.

Virtual Microsoft Windows

There are a number of products that allow you to install Windows on a separate partition of your Mac hard drive. You can get more information about applications in this Macworld article.
Note: You will need to have Microsoft Excel installed in your virtual Windows environment.

Follow the directions found in the Connect to Cubes page. In Step 4, enter your UW NetID and password in the Connect to Database Server.


Getting Access To Cubes

You do not need to request new access to use cubes if you already have access to run EDW reports and query data. If you need to request access, you can do so at the Request Access page.


List of Available Cubes
Cube Database Cube Server (Production) Cube Name Cube Contents and
Refresh Schedule
EDWAdminMart cubes.uw.edu EDWAdminMart
  • EDW report usage statistics since 2008
  • Cube refresh is daily
EDWAdminMart cubes.uw.edu OLAPAdminMart
  • EDW cube usage statistics since 2010
  • Cube refresh is daily
FinancialActivity2015* cubes.uw.edu Fin Activity Biennium 2015
  • 2015 Biennium to date
  • Cube refresh is daily
FinancialActivity2013* cubes.uw.edu Fin Activity Biennium 2013
  • 2013 Biennium to date
  • Cube refresh is daily through 8/10/2015, then frozen
FinancialActivity2011* cubes.uw.edu Fin Activity Biennium 2011
  • 2011 Biennium to date
  • Cube refresh is daily through 7/31/2013, then frozen
FinancialActivity2009* cubes.uw.edu Fin Activity Biennium 2009
  • 2009 Biennium to date
  • Cube refresh is daily through 7/29/2011, then frozen
FinancialActivity2007* cubes.uw.edu Fin Activity Biennium Minus1
  • Full 2007 Biennium
  • No cube refresh required as biennium is closed
ResearchAdminData cubes.uw.edu ResearchAdminData
  • Research Administrative Proposal, Award, and Expenditure data for sponsored research from 2006
  • Cube refresh is daily

Table 1. Currently available cubes.
* The Financial Activity cubes reflect transactions from the EDW ODS BudgetActivityDetail dataset.


Connect to and Use the Cubes

Connect to Cubes Guide

You can also watch the following videos to learn how to connect to the cube and get started using it.

Length Title Description

video

5:57
1. Prepare Excel to connect to an EDW cube
  • Learn why you access EDW using your UW NetID credentials
  • Create file to automate opening Excel
  • Run the automated file to open Excel with your UW NetID

video

1:30
2. Connect Excel to the Research Administration Data Cube
  • Create a data source connection
  • Connect to the cube
  • Prepare pivot table environment

video

7:24
3. Explore Research Administration data using Excel pivot tables and the Research Cube
  • Slice and dice Research Awards data by organization, time, sponsor, and more
  • Create a chart that changes on the fly as you slice and dice