New Feature: OData and PowerPivot

Published
Have you heard about this new functionality in our latest release of Altru called OData?  Questions I hear around OData include what is it and how can it help me?  OData stands for Open Data Protocol which allows you to consume, share, and manipulate data from Altru using queries. If you are a Microsoft Excel user, you can use OData as a data source with PowerPivot.  PowerPivot is a free extension to Excel 2010 and 2013 that dramatically extends the powers of normal Excel and Pivot Tables. Here are my top 5 hints for using OData and PowerPivot:

Hint #1 – You can refresh/run your query from Excel without opening Altru first! Nope, that’s not a typo with OData and PowerPivot you can refresh/run your query from Excel without opening Altru. Instead of opening Altru, running your query and then exporting it to Excel, the OData link allows you to go into Excel first and refresh/run your query from there! Once you have created your initial connection and specific pivot table, chart or graph, you won’t need to open Altru to refresh your results. The only time you would need to go into Altru is to create a new query.

Hint #2 - You can link multiple different query types. This is the unique power behind using OData and PowerPivot. You can link multiple queries that are different into a single PowerPivot session allowing you to work with your data in ways we cannot in Altru. For example, let’s say you want to create report on giving to your annual fund by constituency code. You can create a constituent query with the output of Constituent name, Lookup ID, and constituency codes. In a separate revenue query, you would filter on gifts made to your annual fund and output Constituent Lookup ID, date, and application amount. Once you attach your two queries using their OData links into PowerPivot you can match up the queries using the relationship linking tool and the Constituent Lookup ID. Other Common fields to use between different query types include Lookup ID for constituent records, Sales Order IDs, and Revenue IDs. Finally, once you have your data in PowerPivot to show your data by constituency code!

You don't have to use multiple queries to use OData and PowerPivot. You can just use a single query if you want. PowerPivot will allow you filter and sort your data how you need too. For instance, with PowerPivot you can sort revenue data by Month and/or Year.

Hint #3 - OData links are only available for Ad-hoc Queries but you can still use PowerPivot with other Exports. ODatalinks are not generated from our existing canned reports, selections or exports.  If you are using the Excel or CSV format of an existing report or export, you could look at using PowerPivot features by linking to the location where you save your report files.  By using PowerPivot instead of the normal PivotTable feature, you can bypass using functions like VLOOPUP.

Hint #4 - Check the data in your queries and make sure it is accurate. Especially if you are pulling revenue totals, you should be careful of the data you are pulling. You don't want to over or under estimate totals so should consider pulling non aggregated totals from your queries. You can always add up data once you are in PowerPivot or Tableau.

Hint #5 - If you don't know how to use PowerPivot, search the net! It does helps to have some advance knowledge of Microsoft Excel 2010 and Microsoft Excel 2013. Altru Customer Support will assist you with getting data from Altru into Excel, but once the data is secured it will be up to you to create your own pivot tables, charts and graphs. There are some great resources on YouTube that can get you through the basics of using PowerPivot. Some great one's I used included the "PowerPivot for the Data Analyst" series by Bill Jelen.

 
News ARCHIVED | Blackbaud Altru® Tips and Tricks 10/01/2014 10:37am EDT

Leave a Comment

Check back soon!

Share: