IT Connect
Your connection to information technology at the UW

Help: Reports and Analytics

This page helps users resolve the most common questions about working with reports, visualizations, and cubes available in the BI Portal or UW Profiles. If your question isn’t answered here, please write help@uw.edu.

How do I check if I have EDW access to use reports, visualizations, and cubes?

Not sure if you have access to Enterprise Data Warehouse (EDW) data and tools? Or want to find out what kind of access you have? Any UW employee can use ASTRA to view their EDW access.

  1. Navigate to ASTRA
  2. Click on “Show Me My Authorizations“.
  3. Login using your UW NetID and password
  4. ASTRA will display a list of authorizations:

Check ASTRA authorization

  1. Look under the Application column – you have access to EDW data, reports, cubes, and visualizations if you see EDW listed. The data you can access is governed by the role to which you have been assigned. In the example above, the user has been assigned to the EDW Administrator role. Note: you may have been granted access to more than one role.
  2. If you need to request new or different access to EDW, visit the Request Access page.

For more information on EDW security, visit the About Data Security page. To see what data you can access via your role, reference the Security Access and Roles Matrix.

How do I connect to reports and data from off campus?

You must set up a Virtual Private Network (VPN) to access reports, cubes, visualizations or Enterprise Data Warehouse data from off campus. UW provides this secure remote connection through Husky OnNet. This service is provided at no additional cost to UW students, faculty and staff as part of the Basic Bundle of services covered under the Technology Recharge Fee. To access the service, go to the Husky OnNet UWare page.

How do I troubleshoot issues with running reports?

Some of the most common issues users report regarding report-running are:

  • Logging In

To access cubes or reports, you will need to access the UW NetId domain using your UW NetID username and password.

See the Logging In frequently asked question

  • Data Unavailable

When running a report, you may encounter an error message that resembles the image below.

Error message

Cryptic? Yes. Helpful? Not really. But the bottom line is that a database needed by the report isn’t available and one of two things is happening:

  • The database is being loaded with data
  • The database is unavailable due to maintenance or an unexpected outage

You can check for both situations using the Data Availability Summary, as described below.

  • What does “Database is Loading” mean?

The Last Loaded Column usually shows the the date and time that the database is loaded. If the database is loading (usually at night or early morning) , you’ll see the “Loading…” message in the Last Loaded column of the Data Availability Summary.

  • Is Data Current?

The Load Frequency is the scheduled frequency of the database being uploaded, therefore (Excluding Legacy HEPPS Databases) if the frequency is daily, the Last Loaded date should never be , greater than two days old. For the the Legacy HEPPS, data loads were frozen post Workday release in 2017. No database is loaded more than once a day.

  • How can I tell if I have access to a Report (Report padlock)?

When attempting to run a report instead of the report running a graphic appears with a padlock over it indicating the report is not accessible to the users Astra Role.

  • Why are there all null values in one of my columns?

When a report runs, but there are fields in which all the values of a column are null, either all the values for a particular run are null or the user does not have access to that particular data item.

To check if you have full or partial access to a BIPortal Report, select the “Security Information” tab. It indicates which Astra roles have Full Access, Partial Access and the Astra Role that you have.

  • Alerts

Check the Alerts section in the right column of the Decision Support home page to see if an outage message has been posted. The message will list the databases that are unavailable along with an estimated time they should be available again. If you don’t see a message there, please send an email to help@uw.edu to let us know about the issue you’ve encountered.

What browsers are supported with BI Portal reports?

If you are using Internet Explorer for your web browser and are having trouble loading a BI Portal report, we recommend that you try a different browser.

The browsers we currently support include:

  • Firefox
  • Chrome

How do I get access to use a cube?

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.

How do I connect to and use a cube?

Download this PDF guide for a quick reference!

  • If these simplified instructions don’t work for you, please read below for more detailed instructions.

Access
Before connecting to a data cube, you must have access to the EDW. Learn if you already have access or how to request it.

Help

  • When logging in, make sure you enter “yournetid” without the quotes and replacing yournetid with your UW NetID
  • If you have questions, please send them to help@uw.edu with “data cube question” in the subject line.

Detailed Connection Steps:

Please note, your specific connection steps are determined by your workstation based on:

  • Your network connection
  • Your analysis tool and version

Network Connections:

Basically, users connecting to enterprise data cubes use one of two connection types:

  • A trusted UW NetID domain
  • Another network that is not trusted on the UW NetID domain, examples:
    • Your personal laptop or desktop
    • Another campus domain that is not on the UW Netid domain

Your Analysis Tool and Version:

You can think of a data cube like a database. As a result, you need an analysis tool to connect to and use the cube. The two most common analysis tools are:

  • Microsoft Excel
  • Tableau

Some users have the Office Suite installed directly on their desktops. Other users login using Microsoft Office 365 (o365). As a result, we provide instructions for the different ways to connect:

Connect to Analysis Services with Tableau:

Before you connect to a cube with Tableau, we recommend you read Tableau’s help page on connecting to Analysis Services with Tableau:

  1. Under the Connect pane, select More, then Microsoft Analysis Services.
  2. Next, enter the cube connection, and choose Windows Authentication
  3. Now you’re connected to the cube server! Choose the Database and Cube you want to explore.

Connect to Analysis Services with Excel 2016 (o365):

Note: These instructions use the Financial Activity cube as an example. The same basic steps apply to all data cubes.

  1. Under the Data tab, select From Database, then From Analysis Services.
  2. Next, the Data Connection Wizard will open. First, consider your network connection:
    1. Working Remotely?
      • You must connect to the UW network through a VPN to connect to the cube server. We recommend using the Husky OnNet VPN supported by UW-IT. You can read about Husky OnNet, download it, and more on IT Connect: Husky OnNet.
      • Since you are not on the UW Domain, you cannot connect to the cube using Windows authentication. Instead, you’ll have to explicitly enter your UW Netid and password. Under the “Log on credentials”, select “Use the following User Name and Password”.
      • Type “netid\” followed by your UW Netid for your “User Name”. Then enter your netid password, and choose next. 
    1. On UW Domain?
      • If you are on the UW Domain or are connecting to a workstation on the UW Domain, then you can use Windows Authentication to connect to the cube.
  1. Select the database from the drop-down, then select the cube from the list in the list below the drop-down
  2. Enter your own File name, Description and Friendly Name, or keep the default. Select Finish to save this Office Database Connection (.odc file)
  3. Select OK to place the pivot table in the default location

Connect to a Cube with Excel 2016:

Note: These instructions use the Financial Activity cube as an example. The same basic steps apply to all Enterprise Data Warehouse (EDW) cubes.

  1. Open a new blank workbook in Excel
  2. Under the Data tab, select From Other Sources, then From Analysis Services
  3. In the Data Connection Wizard window,
    • Server name : cubes.uw.edu
    • Log on credentials
      • Use Windows Authentication if you log into your computer with your UW NetID credentials (or if you used the RunAs command to open Excel).
        • You may need to select the Use the following User Name and Password option when you connect and enter your own UW Netid and password as shown below.
      • Mac Virtual Windows Connection
        • When you connect through a Mac Virtual Windows connection, you will need to enter your own UW Netid and password as shown below.
  4. Select the database from the drop-down, then select the cube from the list in the list below the drop-down
  5. Enter your own File name, Description and Friendly Name, or keep the default. Select Finish to save this Office Database Connection (.odc file)
  6. Select OK to place the pivot table in the default location
  7. Select fields from the Pivot Table Field List panel on the right side, by clicking on the checkboxes in the upper portion of the panel, or by dragging them to the four lower boxes

Congratulations! You’re connected.

Note – You only need to perform the connection steps once per cube. Excel will save this connection in your Connection list and/or directly in your worksheet for your next visit. When opening a new workbook, select the existing connection under the Data tab, then Existing Connections.

Still Can’t Connect? – Try RunAs

If you’re still having trouble connecting, you can try opening Excel using your UW NetID credentials using the RunAS command.

Your permission to access the EDW is associated with your UW NetID. When you connect to the EDW through Excel or any other tool, you do so with your UW NetID username and password. Follow the instructions in the Basic Steps to Connect to Data and Reports section to learn how to use the RunAs command to open Excel with your UW NetID credentials.

Why is cube showing weird or erroneous results? Is it broken?

A data cube displays data in two groupings: facts and dimensions.

  • Facts: These are the data elements that can be counted. Things like the a count of research awards, or the sum of budget expenditures.
  • Dimensions: Some people refer to these as the “slicers” or “breakdowns”. These are the data elements that allow you see a fact sliced by a particular dimension. For example, you can look at the count of research awards by Department using the Financial Organization Dimension.

However, it’s very important to know that not all Facts can be sliced by all Dimensions. This makes sense conceptually. In the ResearchAdminData cube for instance:

  • You can see a count of research proposals sliced by the proposal ids because all proposals have proposal ids.
  • You cannot see a count of awards sliced by the proposal ids in the proposals dimensions because not all awards have proposal ids. Conceptually, and in the data, the facts don’t have a relationship to that dimension.

When a user joins a fact to a dimension that doesn’t have any underlying relationship, the cube will return erroneous results, like a table full of duplicated values. This doesn’t mean the cube is broken, it just means those two elements can’t be joined.

Our partners in ORIS put together this handy document to demonstrate this concept:

Facts and Dimensions Handout

How do I get started with Tableau?

To learn more about Tableau, including how to get a license, please check out the Tableau pages on this site: Data Visualizations (Tableau)

 

How do I find out what one of my reports, visualization, or cubes values mean?

  1. First check Knowledge Navigator, while not every term is yet defined, if it is, it will have the institutional definition, lineage, and usage.
  2. Further information can be requested from the:
    1. EDW team via help@uw.edu;
    2. Academic Data Management Office via StudentData@uw.edu
    3. Academic Personnel (AHRIS) via acadsim@uw.edu
    4. Management Accounting and Analysis – (geosims) via gccrhelp@uw.edu
    5. Research Awards (ORIS) via grantrpt@u.washington.edu

How do I make suggestions for changing an existing report or requesting a new report?

Send the recommendation to help@uw.edu, specifically request it to be routed to EDW-BI. Include a description of the report, how and how often it would be used, what other staff would be interested in using it, and provide contact. information.

What training opportunities are available?

Our office does not currently have any resources dedicated to hosting in-person trainings. If you would like more training on Excel or other data tools, we recommend you explore the following:

Last reviewed September 20, 2019