Power Query (Part of Excel)--Hooking It Up to a Carleton Data Source

Owned by Richard Goerwitz
Last updated: Oct 31, 2018
3 min read

This document, which can't be comprehensive, provides a couple of examples of how one might hook Excel up to a Colleague or data warehouse data source using Power Query.

Overview

Power Query is a very handy tool for downloading, massaging, and stitching together data from multiple sources.  It takes the place, to some extent, of traditional ETL (Extract Transform Load) tools.  And it automates what used to be a repetitive and time consuming task old-time Excel users are familiar with, of downloading data, renaming and moving columns, removing bad data, and generally making it usable.  Power Query not only automates the download itself, but also the steps the user formerly had to apply (by hand) afterwards.

Access issues (that is, the question of whether a given user has permission to access a given data source) lie outside the scope of this document.  If you want access to data, contact the people responsible for maintaining that data.  For example, if you want access to Colleague academic record information, contact the Registrar and ask them for permission to do so.  Then ask them who to contact in ITS for actual permissions to the relevant systems, and submit an appropriate ticket to the ITS Helpdesk requesting access.

Contents

Product Details

Power Query is build into Excel for Windows.  No download is needed.  A stand-alone tool based on the same technology is also available as PowerBI Desktop.

Documentation:  https://docs.microsoft.com/en-us/power-query/.

Who To Call

If you have trouble using Power Query, call the ITS Helpdesk and ask them to create a data warehouse ticket, even if the data source you are trying to connect to is not the data warehouse, and even if your primary question or problem centers on Excel.

Connecting to Colleague or the SQL Back End to the Data Warehouse

To connect Power Query to data in Colleague, specifically, to the Colleague Operational Data Store, or to connect it to the relational-database part of the data warehouse, the steps are identical.

Find the Data menu in Excel, click the Get Data icon, and select "From Database" - "From SQL Server Database":

Once you've selected SQL Server Database as you data source, you must enter a server name.  What you enter here will vary:

  • To extract data from the Colleague Operational Data Store, enter collreports.ads.carleton.edu as your server and CollODS as your database
  • To extract data from the data warehouse's SQL store, enter dwsqldb-2016.ads.carleton.edu\production as your server and MasterDimensions as your database

You must be on Carleton's network (directly, or via VPN) in order to connect to these and most other data sources.  And you will need to be using a Windows machine.

To connect to data warehouse cubes (rather than sheet-like tables of record-level data), you would follow steps slightly different from those offered above.

First you would find the Data menu in Excel, click the Get Data icon, and select "From Database" - "From Analysis Services."  Then you would supply dwcubes.ads.carleton.edu\production as your server, and then pick a database from the list (make sure to click the dropdown):

Selecting Tables

Whether taking data from Analysis Services or SQL Server, Power Query will return with a list, from which you may select item(s).  It the case of Analysis Services, you are selecting analytical cubes.  In the case of SQL Server you are selecting tables (you can select more than one if you check the select multiple items checkbox on the relevant menu).

If you are taking data from Analysis Services, once you've selected a cube, you'll get a pivot table.  If you are using SQL Server, once you've checked all needed data tables, Excel will download data for you, and you may start joining it.

Alternatively, you may supply a SQL query on the menu where you specified a server and database, and pre-join the data in SQL:

For more information on using Power Query, contact the ITS Helpdesk and ask them to file a data warehouse ticket for you, even if you are not querying the data warehouse.

Richard Goerwitz
First published:  October 2018