Subscribe to this blog for news and announcements about Altru.

Changes With OData In Version 4.96 Of Altru

We have started rolling out a recent change to the OData Query feature in Altru, and while it will mostly have a positive impact on our users, we have identified a scenario that will require a minor adjustment to existing custom reports that were authored in a certain way.

If you find technical details boring, please feel free to jump down to the FAQ section to learn more about how this might impact you.

The Change


When OData Query for Altru was first implemented, the BI landscape was less mature than it is today, and the OData Protocol was still relatively new. OData supports two formats for representing the resources (Collections, Entries, Links, etc) it exposes:
·         the XML-based Atom format
·         the JSON format.  
When we first started building our OData Query feature, end-user research gave us confidence that Tableau, Excel (including PowerPivot) would be the tool of choice for most users.  These applications were built to use the XML-based Atom format, so we built our feature in a way that defaulted this format, and in turn would work seamlessly with them.  Over time, as additional reporting and BI tools gained prominence and market share, we found that the better long-term answer for supportability would be to fully implement the OData protocol and default the JSON format.  To accomplish this, we had to make exceptions within our code to still use Atom for Tableau and Excel, but the default is now JSON.  The biggest benefit of this is that our OData Query feature now works out-of-the-box with many more applications that support the OData v4 protocol, chief among them is Microsoft Power BI.  

FAQ

What has changed?

The OData Query feature was updated to default the JSON format as a opposed to the XML-based Atom format.

Why was this change made?
This change allows us to better support additional reporting and BI tools, such as Microsoft Power BI.


What changes do I need to make if I use Tableau or Excel?
None.  This change was mindful of existing Tableau and Excel reports that use an OData Query link as a data source.  These will continue to work without the need for manual intervention.

When is manual intervention required?
In the event that you are programmatically using an Altru OData Query link in a custom report or standard tool other than Tableau or Excel that supports the XML-based Atom format, you will need to make a minor edit to allow for continued function of your report or tool.  

How do I make the changes necessary to allow my reports to continue running?
The easiest way to make this change is add a header to your request setting the User Agent to Excel:
 
User-Agent="EXCEL"

This will ensure that the XML-based Atom format is used, and your report or tool will continue to behave as it did prior to the update.  Note: Please contact the appropriate software vendor or IT professional for assistance with making the actual change in your customization.
 
Though we're not aware of any reporting & BI tools today beyond Excel and Tableau that support OData in the XML-based Atom format, in the event others achieve mainstream usage, we will consider adding them to list of applications that receive this format by default. 

With the recent changes, the OData Query Link mechanism does not use the "Content Type" headers to determine format; only the User Agent (of EXCEL, TABLEAU, and POWER PIVOT). If the agent is anything different, the endpoint will return JSON format.
 
For reference, an OData Query Link endpoint looks like this:
 http://localhost/CRM_SP_DEV/ODataQuery.ashx?databasename=BBInfinity&AdHocQueryID=99d13009-288c-4cc6-b416-caf5642644de
 
The below steps provide guidance on how to receive the ATOM XML format for uses outside of Excel, Tableau, and PowerPivot.  Regardless of the language chosen, the concepts are the same. This is an example in C# in a hosted environment:
 
Principles:
 
  1. The Content Type is used only when forcing the ATOM XML format. Otherwise the content type can be left blank.
  2. XML responses leverage a user agent of EXCEL, TABLEAU, or POWER PIVOT.
    1. For EXCEL - the first request returns the Metadata. The second returns the payload/results.
    2. For TABLEAU the first TWO requests return Metadata (one for the designer, one for the results), and the third request gets the data.
  3. If you force the User Agent to "EXCEL", for example, you will have to make TWO requests: one for the metadata and one for the results.
 
                var request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = "GET";
                request.UserAgent = "EXCEL";  // This is what Excel sends the web server
                request.UseDefaultCredentials = true; // Windows Auth in hosted environment
 
                // Since our User Agent is EXCEL, we need two responses.
     // RESPONSE 1: METADATA.
                using (var response = request.GetResponse())
                {
                    using (var stream = response.GetResponseStream())
                    {
                        var reader = new StreamReader(stream);
 
                        // Write out all the data.
                        while (!reader.EndOfStream)
                        {
      // THIS GETS METADATA!!!!
                            Console.WriteLine(reader.ReadLine());
                        }
                    }
                }
 
      // RESPONSE 2: RESULTS
                using (var response = request.GetResponse())
                {
                   using (var stream = response.GetResponseStream())
                    {
                        var reader = new StreamReader(stream);
 
                        // Write out all the data.
                        while (!reader.EndOfStream)
                        {
      // THIS GETS RESULTS!!!!
                            Console.WriteLine(reader.ReadLine());
                        }
                    }
                }
 
Posted by Kayla Clark on Jun 6, 2017 4:46 PM America/New_York

Leave a Comment

Log in to post a comment.

To confirm, we should use a header called "UserAgent", not the standard "User-Agent"? And no closing quote?
  • Posted Thu 08 Jun 2017 10:00 AM EDT
Following up on Vince's comment: I'm running into errors when using UserAgent, so I've moved over to User-Agent, and added a closing quote. I'm getting XML tag structure, but the file has no query entry results about 50% of the time I try (usually alternating between success and empty results). Our shows have therefore disappeared from our website, along with links to purchase tickets. Digital signage in the building is empty of show times, and other staff resources to monitor ticket sales and group itineraries don't have that information.

Please let us know if there's something that will keep results from being empty half the time - our theater's operation has almost ground to a halt today as we investigate how to successfully get XML data from Altru.
  • Posted Wed 14 Jun 2017 12:34 PM EDT
I can also confirm that the correct request is "User-Agent=Excel", but like Derrick said, I only get results about 50% of the time. Even when I do get results, my custom code still fails due to some kind of change to the structure of the XML. I decided to give up on it and rewrite my code to parse the JSON data instead.
  • Posted Thu 15 Jun 2017 09:14 AM EDT
Thanks for the confirmation Joe!  I am also getting results 50% of the time utilizing XML.  Not sure why I can't authenticate utilizing JSON:

$headers = array('Content-Type: application/json', 'Authorization: Basic ' . base64_encode('BLACKBAUDHOST\username:password'));
  • Posted Fri 16 Jun 2017 02:02 PM EDT
I added a "refresh" to web pages I'm using to just refresh once, which seems to correct getting the results literally 50% of the time.  I still think this should be looked into by the Product Team, especially if customers have paid for custom sites relaying OData.
  • Posted Fri 16 Jun 2017 03:56 PM EDT
I didn't have any trouble authenticating with JSON. I just didn't add the "User-Agent..." and it simply returned the JSON data. I believe my content type is still "text/xml" as before.
  • Posted Fri 16 Jun 2017 05:04 PM EDT
Hey All, 
We updated the post with more information. If you are still having trouble, please contact support so that we can assist you further! 
  • Posted Mon 19 Jun 2017 10:56 AM EDT
I confirmed that I could get what I needed by doing two server roundtrips, discarding the first result and parsing the second one. But then I threw in the towel and changed my common logic to handle the JSON. This made for a tumultuous day, but it's behind me now and the logic actually is faster than the xml decoding it replaced.
  • Posted Wed 21 Jun 2017 10:09 AM EDT