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 sign up to receive updates about the BI Webinar Series?

Enterprise Reporting and Analytics hosts monthly webinars on Zoom to help users understand the data products we support. These webinars are free and open to the Enterprise Data User Community. The steps below allow you to join a UW Group. Once you’re in the group, you’ll begin receiving monthly invitations.

Attend the Webinar to:

  • Learn about tools available on the BI Portal
  • Learn about the UW Tableau Service
  • See demonstrations of how users like you use data tools at UW

To begin receiving invitations to the BI Webinar Series.

  1. Go to the UW-IT BI Enterprise Data User Community page on UW Groups.
  2. Click ‘Join this group’

  1. After joining the group, you will begin receiving invitations to attend webinar events!

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 BI Portal header 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.

What if those basic steps don’t work while I’m working from home and/or connected to a private network?

Sometimes you cannot connect to the cube server even if you’re using Husky OnNet (F5–Big IP Edge Client VPN). You may see an error that looks like this:

This could happen for two reasons:

  • You are working from home and connected to a private network, like your personal wifi network
  • You are working from campus, or using remote desktop to connect to a computer on campus, but your department has configured a private network that is not “on the UW domain” and is not trusted by the UW network.

If you don’t know what “networks” are, that’s okay! We have a workaround that allows you to open your Excel workbook using your netid credentials. This allows you to access our cubes server, and get connected. Here are the steps:

Use the Husky onNet VPN to connect to the network:

  • If you’re not already using the Husky OnNet (F5–Big IP Edge Client VPN) you can download it here: Download Husky onNet
  • After you install the VPN service you should connect. You’ll be prompted to enter your netid credentials.
  • Once you’re connected, you should see a window that looks like this:

Set Up Instructions to tell your Excel to “run as” your “netid user”

In this section you’re going to “build” some instructions to tell your computer to open excel using your netid credentials. Once you’ve done this, you’ll be able to connect directly to the cube and begin using it. There are a few steps to this process, but fear not! It’s a one-time process. Once you’ve set up the command, you won’t have to worry about setting it up again. Let’s get started:

Open Notepad and begin “building” your command

Notepad is a very simple app that comes pre-installed with the Windows operating system. Open the Windows search box (Cortana) and type “notepad”.

After you open Notepad, copy and paste the following string of text: RunAs /netonly /user:netid\

Next, add your personal netid to the end of the string so that it looks like: RunAs /netonly /user:netid\mynetid

Find where the Excel application lives on your computer

The next step is to find where your Excel application lives on your computer. To do this open the Windows search again, and type “excel.exe”

You should see a window that looks like this. You want to make sure this is the “Excel.exe” file. Click the option to “Open File Location”.

This should open a file explorer that looks like this:

Excellent work! You found where the Excel application lives on your computer! Now click the “file location” at the top and highlight the file location. Copy (Ctrl+c) the location. In this example it is: “C:\Program Files\Microsoft Office\root\Office16”

Please note: your excel location may be different than mine, so you cannot just copy/paste the line above. You have to find your Excel’s location, and use that!

Go back to Notepad again and paste the file location to finish “building your RunAs command”

Go back to notepad for the next steps:

  1. In your notepad, type a space after your netid and paste the file location, so that it becomes: RunAs /netonly /user:netid\mynetid “C:\Program Files\Microsoft Office\root\Office16”
  2. At the end of that string, and just inside the quote type: \excel.exe
  3. Now your total command should be something like: RunAs /netonly /user:netid\mynetid “C:\Program Files\Microsoft Office\root\Office16\excel.exe”
  4. You’re almost there! Type enter and then add the word “pause”, so that your RunAs command looks like this:

Save your RunAs command so that you can “run it” anytime you want!

Congratulations! You’re done building your RunAs command, you now just need to save it. We want to save it a very specific way. Here are the steps:

  1. In Notepad, click “File”, then “Save As”. We recommend saving your RunAs file to your Desktop.
  2. Then give it a name, and add “.bat” to the end of the name. This suffix is a “file extension”, it asserts that the file type should be “.bat” or batch. It’s important that you do not save the file as the default “.txt”. Here, I named my file “excel.bat”
  3. Finally, update the “Save as Type” default to “All Files”. Then click Save. It should look like this:

Now open the file, enter your password, and your done!

You should now have a file on your Desktop that looks like this:

If you double click and open the file you’ll be prompted with a window where you can enter your netid password. Please note, as you’re typing the window won’t display your password (or give you any feedback at all really). After you type your password, press Enter.

After you press enter, Excel should launch. Although it looks like normal Excel, there’s one key difference, you’ve now opened Excel by embedding your Netid credentials! Now you can connect to the cube server as if you were physicaly on campus and on the netid domain!

The basic steps for connecting to and using the cube are detailed above: Connect to and Use Cubes

Some Important things to know about this RunAs command:

You do need to run this “excel.bat” file anytime you want to use the cube. For instance, if you save your workbook on your Desktop, then want to open it again, you’ll need to run the .bat file and enter your password again. Here are some tips:

  • Find the .bat file where you saved it, and run it. Enter your password
  • When Excel launches, choose “Open” then navigate to your saved workbook.
  • This will open your saved workbook with your existing pivot and because you’re connected with your netid, you can start working!
  • If you build your first pivot and you’re satisfied with it, and you want to create a second pivot on another tab, you don’t have to run the “excel.bat” file again! Instead, click an empty cell. Go to the data tab at the top, click “Existing Connections”. A window will open like the one below. Choose the connection you want, then click “Open”. This will create a new connection in the workbook and allow you to build another pivot!

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 October 11, 2021