Release: BI Cubes Infrastructure Upgrade Saves you Time and Frustration!

September 22, 2021

UPDATE: The Business Intelligence (BI) team in UW-IT Enterprise Reporting and Analytics (ERA) has completed our cubes infrastructure upgrade.

Here is what you need to know:

  1. Now when you connect to “cubes.uw.edu” you can access the following cubes:
    • Financial Activity 2007-2021
    • ResearchAdminData
    • UW Spend
    • UW Admissions
    • BI Usage
  2. We will be retiring our old cubes in October; currently they can still be accessed on edwbissas1.s.uw.edu server.

If you connect to and query data cubes, or work on a team that partners with the BI Team to support cubes on the BI Portal, the latest upgrade brings you many benefits! Click below to read more:

For Cube Users: Improved User Experience

Finance Cube Users:

For users of the UW Financial Activity Cubes (2007 – 2019) this upgrade also brings you several benefits.

  • We are aligning the 2007 – 2021 cube models. After this work is complete, when you connect to the current biennium cube (2019/2021) or an older cube (2007-2017), you’ll be able to work with the same data elements in a consistent model!

If you already have a finance cube connection to an Excel workbook, you may experience an interruption, and have to re-enter your connection information. If you experience any problems, please don’t hesitate to write us at help@uw.edu!

Finance Cube FAQ:

  1. Why are you only including the 2019 and 2021 finance cubes in this release?
    • We are aligning the cube models or “schemas” for the 2007-2021 finance cubes, and we will release these to the new production environment iteratively over the coming weeks.
    • It takes time to test these cubes, so we’re releasing them as they’re available and tested. We did not want to hold off on one “big-bang” release, but rather want to make these tools available as they’re ready.
    • If you need to access the 2007-2017 Biennium Financial Activity cubes before July 30, you can still connect to them through this server: “edwbissas1.s.uw.edu”
    • Here are the steps to follow:
      • Open Excel and choose the data tab at the top:
        1. Click the “Data” tab, then choose “Get Data”, then “From Analysis Services”
        2. If you don’t see those options based on your Excel version, then alternatively, click “Data”, choose “From Other Sources”, then “From Analysis Services”
        3. Click “Next”
        4. You should see a dropdown in the Wizard with the 2007 – 2019 Biennium Cubes
        5. Choose the “Financial Accounting Biennium <Year>” option , then click “Finish”
      • If you have any trouble connecting write us at help@uw.edu and someone from my team will help get you set up!
  2. When will the Financial Activity Cube for the 2021 Biennium be ready?
    • We were able to complete the testing for the 2021 cube and include it in our initial release on July 14.
    • We originally targeted July 30, 2021 for the release date for the Financial Activity Cube Biennium 2021. However, based on user feedback we prioritized an earlier release.
  3. Are you changing the finance cube perspectives?
    1. We will keep the “Financial Accounting Office” perspective
    2. We are removing the “Decision Support Center” perspective
      1. If you previously had a connection to this perspective, you will experience an error. You will need to update your connection.
      2. You can do this by going to: Data> Properties (Queries and Connections)>Definition tab
      3. Update the “Command Text”:
        1. From: “Decision Support Center”
        2. To: “Model”
  4. How do I connect to the 2021 Biennium Financial Activity Cube?
    1. To create a new cube connection, please follow these steps:
      1. Open Excel
      2. Click the “Data” tab, then choose “Get Data”, then “From Analysis Services”
      3. If you don’t see those options based in your version of Excel, then alternatively, click “Data”, choose “From Other Sources”, then “From Analysis Services”
      4. For the server name please enter “cubes.uw.edu”
        1. If you log into your computer with your netid and password choose “Use Windows Authentication”
          1. If you log in with some other username and password choose “Use the following username and password”, then type:
            1. User Name: NETID\yourUWNetid
            2. Password: Your netid password
        2. Click “Next”
        3. You should see a dropdown in the Wizard with the “Financial Activity Biennium 2021” Cube, choose that one.
      5. Finally, choose the “Financial Accounting Biennium 2021” option , then click “Finish”

RAD Cube Users:

Create a new RAD connection

To create a new connection to the RAD cube, please follow these steps:

  1. Open Excel
  2. Click the “Data” tab, then choose “Get Data”, then “From Analysis Services”
  3. If you don’t see those options based in your version of Excel, then alternatively, click “Data”, choose “From Other Sources”, then “From Analysis Services”
  4. For the server name please enter “cubes.uw.edu”
    1. If you log into your computer with your netid and password choose “Use Windows Authentication”
      1. If you log in with some other username and password choose “Use the following username and password”, then type:
        1. User Name: NETID\yourUWNetid
        2. Password: Your netid password
    2. Click “Next”
    3. You should see a dropdown in the Wizard with the “Research Admin Data” Cube, choose that one.
  5. Finally, choose the “Model” option , then click “Finish”

Update an existing RAD connection

If you have an existing connection to the RAD cube, you will need to update it. When you refresh your connection, you may see an error like this:

To resolve this error:

  1. Open you workbook and click into the pivot table area
  2. Click Data
  3. Then Properties (in Queries and Connections)
  4. You’ll see three tabs there, choose “Definition”
  5. In Command Text, you should see “RAD”
  6. Delete “RAD” and type “Model”
  7. Then click OK, then you’ll get a prompt saying “are you sure?” and click “Yes”

What you’ve done is update your “connection properties”. Once you update the connection you should be able to refresh the data, and you should not have to update this connection again!

If you have any trouble with these instructions, please write us at help@uw.edu with “RAD Cube Connection Issues”

Admissions Cube Users:

For UW Admissions Cube users, here’s the first thing you need to know:

No more RunAs “your netid” to connect!

We released the UW Admissions cube on a technology platform that made it unnecessarily hard for our users to get connected to the UW Admissions Cube. Many dedicated early adopters patiently powered through the connection steps while we guided them over Zoom. Those days are behind us. Now to connect to the UW Admissions cube you only need three things:

  1. An analysis tool like Excel or Tableau
  2. An EDW data access role that has sufficient access
  3. Be on the UW network, either physically connected, or through a VPN connection like Husky OnNet

This also means you no longer have to connect to the “edwbissas1.s.uw.edu\tabular” server! Instead, simply go to cubes.uw.edu and you’ll connect to the UW Admissions or any other cube you have access to run!

UW Spend Cube:

In this cutover we also released the new UW Spend Cube. You can read more about the cube and how to get connected here:

For BI Partner Teams: Latest Software and Hardware

If you partner with our team to support data tools on the BI Portal, you need to know about some of the changes we’ve made and how they impact our shared work!

Latest SQL Server and SSAS Versions

With this tech refresh, we’ve made the following upgrades:

  • Migrated to Windows Managed Virtual Machines (VMs) for all of our BI infrastructure
  • Upgraded to the latest software versions:
    • SQL Server 2019
    • SSAS 2019

Tabular Conversion:

Additionally, for all of our BI Portal Cubes, we’ve converted the models from OLAP (multi-dimensional) models to Tabular models. We invested in converting our models to tabular for 3 reasons:

  1. The Future: Microsoft is moving away from multi-dimensional models and is promoting tabular models.
  2. Azure: Tabular models are a prerequisite for hosting cubes on Microsoft Azure Analysis Services (AAS). We don’t currently have a plan for migrating to Azure, but we are in the early phases of exploring a move.
  3. Power BI: Tabular models can be data sources for Power BI reports and dashboards. We don’t currently have a plan for implementing Power BI, but we are exploring the platform.

BI Usage Cube

We’ve redesigned and rebranded the “EDWAdminMart” cube as the “BI Usage Cube”. We made the following changes:

  1. For performance reasons, we reduced the scope of data to a rolling 5 year window
  2. We removed several little-used measures around “new users”
  3. We updated labels to be more user friendly:
    1. “RS” becomes “Reports”
    2. “AS” becomes “Cubes”

The BI Usage Cube is designed for the BI team and our partner organizations to use to analyze BI Portal tool usage. If you have any questions about how to connect to and use it, please write help@uw.edu with “BI Usage Cube” in the subject line.

How does this impact you?

If we partner to support reports or cubes in the BI Portal, you might be impacted by this upgrade. If you are planning any changes to cubes that we partner to support, then we ask you to consider upgrading to the latest software versions to avoid compatibility issues.

Questions?

Whether you’re a BI Portal user or a partner development team, if you have any questions about this tech refresh, please write help@uw.edu with “BI Portal Infrastructure Upgrade” in the subject line.