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());
                        }
                    }
                }