Rally Software

 View Only
Expand all | Collapse all

Using the LOOKBACK API in PowerBI to pull historical data for snapshots

  • 1.  Using the LOOKBACK API in PowerBI to pull historical data for snapshots

    Posted Nov 18, 2021 10:42 AM

    I've been digging everywhere and I would like to know if there is a way to use the Lookback API to pull data as of a certain date using a power query rather than creating an app to do it?

    I need to use this data in power BI and I cannot figure out how to do it so that I can get the proper information about sprints/releases at certain points in time.

    Thank you

  • 2.  RE: Using the LOOKBACK API in PowerBI to pull historical data for snapshots

    Broadcom Employee
    Posted Nov 24, 2021 11:03 AM
    Hi Patricia.

    The documentation of the Lookback API can be found here. You may want to specifically check out the section "Querying By Date", which explains how you filter for a given date using the __At param, or for a date range using either one or both of $gt and $lt params.

    As far as Power BI:  Personally, I'm not familiar with Power BI and didn't use it. However, our Lookback API is a REST API. I found this page that shows an example of how to use REST APIs using Power BI. I am uncertain how helpful this page is, cause as said not familiar with Power BI. I don't know how you may be able to build an API object library in that environment, but you can always invoke the full HTTP string and make a direct HTTP call. Postman can be a handy tool to develop these queries if you need to.

    Here is an example of a http call to our Lookback API. In this example we are asking for all snapshots of a user story identified by US239 which are after (greater than) 08-08-2020.  This is meant to show you how the full HTTP string looks like. If you can't find a library for these objects with Power BI then you shall be able to form such strings and invoke them over HTTP:

    https://rally1.rallydev.com/analytics/v2.0/service/rally/workspace/<workspace object id>/artifact/snapshot/query.js?find={"FormattedID":"US239","_ValidFrom":{"$gt":"2020-08-08T00:00:00.000Z"}}&fields=["ScheduleState","_SnapshotDate","_SnapshotNumber"]&hydrate=["ScheduleState"]&compress=true&sort={ "_ValidFrom": 1 }

    I hope this helps somewhat.

    Please let us know.