How do I connect to a Data Cube via Excel?


Connecting to a Data Cube on Excel

Connection 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:
  2. Do one of the following:
    If... Then...
    A template is available...
    1. Download the template to your computer. A popup may also display to prompt you to open the file
    2. 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. 

    A screenshot where the following selections are highlighted: "Data" tab, "From Other Services" button, and "From Analysis Services" selection.
    Selecting "From Analysis Services" in Excel 2016
    Excel 365

    Select the Data tab, then Get Data, then From Database, and then From Analysis Services.

    A screenshot where the following selections are highlighted: "Data" tab, "Get Data" button, "From Database" selection, and "From Analysis Services" selection.
    Selecting "From Analysis Services" in Excel 365
  3. Field Description
    Server Name Enter cubes.uw.edu
    Log on credentials

    Use Windows Authentication.

    • 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.

 

Issues connecting to the cube from computers from computers not on the UW-Seattle domain?

If you are from UW-Bothell or UW-Tacoma and connot connect to a cube template, there are two possible solutions:

  1. Use the RunAs command. For instructions, refer to How do I connect to the EDW from a remote machine? 
  2. Create your own template, as described in the procedure below.

Creating a Cube Template

Note: This procedure uses the Finance Cube FY 2025 as an example data source, but these steps can be applied to any of the available cubes.

  1. Log in to Husky OnNet.
  2. Download the template from the applicable resource page. Note: Only certain roles have access to these pages. If you do not have access, but think you should, refer to Request Permission and Access Data.
  3. Open the Excel file and click Enable Editing

Protected View warning banner with Enable Editing button circled in red 

  1. Click Enable Content.

Security Warning banner with Enable Content button circled in red 

  1. Navigate to the data tab and then click Data > Properties.
    Thumbnail of FY 2025 data sheet showing Data Properties

The Connection Properties window displays.

  1. Click the Definition tab. 
    Connection Properties dialog box with Definition tab opened
  2. Replace the text in the Connection string field with the following and then click OK.

Provider=MSOLAP.8;Persist Security Info=True;Initial Catalog=UW Finance Cube FY 2025;Data Source=cubes.uw.edu;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2

Note: If you are not connecting to UW Finance Cube FY 2025, replace “UW Finance Cube FY 2025” with the exact name of the applicable cube. If needed, you can refer to the screenshot in step 11 for a list of cube names. 
Connection Properties dialog box with Definition tab opened and Connection string value highlighted

  1. Click Yes if you receive the following warning message.
    Thumbnail of warning message dialog box
  2. Click OK if you receive the following error message.
    System error dialog box

The Analysis Services Connection window displays.

  1. Enter your UWNetID credentials using this format for your User ID: “netid\YourNetID”. Click Next.
    Analysis Services Connection dialog box showing Analysis server option selected, cubes.uw.edu entered for the Server, and User ID and password entered
  2. Select the applicable cube from the list (for example, UW Finance Cube FY 2025) and then click Finish.
    Analysis Services Connection dialog box showing list of databases to select
  3. Save the file to your computer. 
  4. Open this file any time you need to access the cube.

Note: If you receive this error when you reopen the file and refresh the data (or ad new fields to your pivot tables), click OK then repeat Steps 10 through 12 to resolve the issue.

System error dialog box