Odata - Excel - Power BI: follow up from bbcon session

Anyone left with some questions? Let's have a brain dump thread. 


So here goes on my brain dump. 

Odata is just a query you can export at-will into a variety of formats without logging into Altru. The most common use case is Excel. If Excel requests the data, the odata host in Altru returns data formatted for Excel. The data looks a bit different for Tableau. But Tableau is another popular data tool that can handle Altru odata. If you don't send a user agent in the request like Excel or Tableau does, the odata is returned as json formatted data. Web developers will appreciate that. The data is very easy to parse and use in other applications.


As soon as you get data from Altru using the odata feed (Excel will do this via Power Query), the resulting table is imported into a worksheet. Power Query handles the data request and will refresh the table. The real difference between a regular table and a table created by Power Query is the data is dynamic. The number of rows, values, and even the headers can change. You can then use this dynamic table as a source for other worksheets in your workbook. Be it pivot tables or other queries in Excel, you can have various Power Query sourced tables in the workbook that have some relationships you can build. You can even pull in data from other workbooks or applications and start building a cross-platform dashboard.


Something to be mindful of are your headers and unique keys/columns. Be consistent on naming conventions and format. Be it constituent or member ID, revenue ID, or the sales order record; you need a way to match across multiple tables in Excel. Once the data feeds are setup, you can have them refresh on an interval or when you open the workbook. Something to consider are permissions. If you share the workbook, the person opening the workbook must have a Blackbaud ID login with the required rights to run that query in Altru. While Excel stores the connection info, it does not store the username and password for a Power Query connection. Less privileged users will not be able to refresh the data in the workbook. They will see the data from when it was last refreshed by the workbook owner.