Help: Reports and Analytics

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:
   1. Look under the Application column – you have access to EDW data, reports, cubes, and dashboards 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.

In addition to ASTRA steps described above, there are other ways to explore your access, which are outlined on this page in our wiki: Manage Access.

Note: You must be logged into Husky OnNet (VPN) in order to access this video. For more information, refer to Husky OnNet.

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.

Join Academics Group

Join Finance Group

Join Research Group

To join a group, click on “Join this group”

You can view all of our upcoming events in the Enterprise Reporting and Analytics (ERA) Trumba calendar.

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:

• Learning Technologies Workshops
• Professional and Organizational Development: Classes and Workshops

There are a number of different ways to connect to the EDW. Click the following links to learn more:

• Microsoft SQL Server Management Studio (SSMS)
• Excel
• Tableau
• ODBC Connection and/or Microsoft Access
• Excel Using a Mac Operating System
• Microsoft Visual Studio/Business Intelligence Development Studio (BIDS)

You must set up a Virtual Private Network (VPN) to access reports, cubes, dashboards 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.

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.

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?

When attempting to run a report if you see a pink caption as shown in the image below, it indicates that the report is not accessible to the user’s Astra Role. Go to the Interpretation Tab to check permissions and instructions on how to requess access.

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.

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

A data cube is a powerful analysis tool, which allows you to quickly and easily slice and dice data across multiple dimensions, in a single view. To learn more about cubes, including their benefits over traditional analysis tools, such as reports, refer to the What is a Cube introductory video.

Note: You must be logged into Husky OnNet (VPN) in order to access this video. For more information, refer to Husky OnNet.

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.

Access for cubes is managed the same as data in the Enterprise Data Warehouse (EDW) and/or BI Portal.

• If you already have access to this data, you do not need to request new access for cubes. To determine if you have EDW access, refer to How do I check if I have EDW access to use reports, dashboards, and cubes?
• If you do not already have access, refer to Request Access to Reports, Analytics, and Data for instructions on requesting access.

The following steps outline how to connect to a cube. If you would prefer to receive this information in an alternate format, we also have a simplified Quick Reference Guide (PDF format) and a Getting Started with Cubes: How to Get Connected video.

1. Make sure you have access to the EDW. For more information, refer to the How do I get access to a cube? question above.
2. Connect to the UW Network:
   

   If you are working…	Then…
   On campus…	You are already connected to the UW Network. No action is required.
   Remote…	Connect to the UW Network via a Virtual Private Network (VPN), such as Husky OnNet. For more information, refer to Husky OnNet.

3. Select an analysis tool and connect.
   Accessible Accordion

   Microsoft Excel (2016 or 365)

   Accessible Accordion

   Connect WITH a template or base workbook

   A template is an Excel Workbook with an established connection to a specific cube; it already has a pivot table (which is what allows you to work with the cube); and it has been populated with one measure.

   1. Refer to the Templates/Base Workbooks section of the following resource pages for available templates:
      • UW Finance Cube Resources
      • ResearchAdminData Cube Resources
      • UW Admissions Cube Resources
      • UW Spend Cube Resources
      • BI Usage Cube Resources
   2. Do one of the following:
      

      If…	Then…
      A template is available…	Download the template to your computer. A popup may also display to prompt you to open the file. Open the workbook and explore. If prompted for authentication, enter your UW NetID and password.
      A template is not available…	Follow the steps under Connect WITHOUT a template or base workbook below.

   Connect WITHOUT a template or base workbook

   1. Open Microsoft Excel on your computer and start a new blank workbook.
   2. Display the Data Connection Wizard:
      

      If using…	Then…
      Excel 2016	Select the Data tab, then From Other Sources, and then From Analysis Services.
      Excel 365	Select the Data tab, then Get Data, then From Database, and then From Analysis Services.

   3. Field	Description
      Server Name	Enter cubes.uw.edu.
      Log on credentials	Select Use Windows Authentication. Tip: If this does not work, select Use the following User Name and Password, and then enter the following: User Name: Enter netid\your UW NetID Password: Enter your password.Populate the Connect to Database Server window as follows and then click Next.

   4. Select the cube:
      

      If using…	Then…
      Excel 2016	From the database dropdown, select the applicable cube, then select Model, and then click Finish.
      Excel 365	From the database dropdown, select the applicable cube, and then click Finish.

      A dialog to import the data should display.

   5. Select where you want the table to be located on the worksheet (for example, cell A1) and then click OK.
      You are now connected to the cube. You only need to perform these connection steps once per cube. To access this connection when opening a new workbook, select the Data tab, and then click Existing Connections.

   Power BI Desktop

   1. Open Power BI Desktop.
      
   2. On the Home ribbon, select Get Data, then Analysis Services.
      

      

       

   3. Populate the SQL Server Analysis Services database window as follows and then click OK.
      

      Field/Button	Description
      Server	Enter cubes.uw.edu.
      Radio button	Select Connect live.

       

   4.  On the Windows tab of the Access SQL Server Analysis Services window, select Use my current credentials, and then click Connect.
   5. In the left pane of the Navigator window, expand the cube you want to select, select the model or view (if applicable), and then click OK.
      You are now connected to the cube. You only need to perform these connection steps once per cube. To access this connection when opening a new workbook, select the Data tab, and then click Existing Connections.
      For more information, refer to Connect to Analysis Services tabular data in Power BI Desktop.

   Tableau

   1. Open Tableau Desktop.
   2. From the “To a Server” section, select More, and then Microsoft Analysis Services.
      

      

       

   3. On the Microsoft Analysis Services window, select the following, and then click Sign In:
      1. Select Server and enter cubes.uw.edu into the text field.
      2. Select Windows Authentication.
   4. Now that you are connected to the server, choose the Database and Cube you want to explore.
      

      

      The next time you open Tableau and choose Microsoft Analysis Services, it will default to these connection details.

Note: The above instructions assume you are using a computer that is connected to your UW NetID environment. If this is not the case (for example, you are trying to connect from your personal computer), you may see an error that looks like this:

In this case, refer to How do I open my query tool using UW NetID credentials? for instructions on executing a RunAs command to use your UW credentials.

1. Make sure you are connected to the cube. For more information, refer to How do I connect to a cube?
2. Refer to the following resource pages for guidance (including videos) on using the specific cubes:
   • UW Finance Cube Resources
   • ResearchAdminData Cube Resources
   • UW Admissions Cube Resources
   • UW Spend Cube Resources
   • BI Usage Cube Resources

Note: Only certain roles have access to these pages. If you do not have access, but think you should, refer to Request Access to Reports, Analytics, and Data.

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

• Measures: These are the data elements that can be counted. Things like 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 Measures 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 measures don’t have a relationship to that dimension.

When a user joins a measure 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 the Office of Research and Information Services (ORIS) put together this handy document to demonstrate this concept: RAD Cube Facts and Dimensions.

Note: The above document from ORIS uses the term “facts” instead of “measures”. In this case, the terms “facts” and “measures” are interchangeable.

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

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:
   • EDW team via help@uw.edu;
   • Academic Data Management Office via StudentData@uw.edu
   • Academic Personnel (AHRIS) via acadsim@uw.edu
   • Management Accounting and Analysis – (geosims) via gccrhelp@uw.edu
   • Research Awards (ORIS) via grantrpt@u.washington.edu
   • Workday Reporting: UW Connect Finance.