none
Tactical Querying from Project Online with OData & Power BI

    Pertanyaan

  • When querying tasks from Project Online in Power BI, typically the first two lines are these:

    Source = OData.Feed(#"PWA Site URL" & "/_api/ProjectData/[en-us]",null, [Timeout=#duration(0, 0, 5, 0)]),
        Tasks_table = Source{[Name="Tasks",Signature="table"]}[Data]

    The shortfall with that is that it calls every task from every project, and that gets messy in terms of time when there are dozens of projects in the site.

    Is it possible to fine tune this initial query such that fewer projects and/or fewer fields are called in at the onset?

    Thanks for your help!


    JFitch

    05 September 2018 3:24

Semua Balasan

  • Hello,

    It sure is - it can be changed to match your reporting requirements very easily. It looks like you might be using a Power BI report pack for Project Online or have you built this from a scratch?

    What filtering do you want? Just tasks for a handul of Projects? Or just milestones? Or just tasks tagged with a certain customer field etc.? Eitherway, you can use Power Query Editor to do this filtering and it will update the query and use query folding to run this server side.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    05 September 2018 6:43
    Moderator
  • Hey Paul!

    I used the code from the report pack to build my own reports. I'm not a coder by trade but have learned how to reverse engineer a great many things. :)

    Anyway, there are several cases where I just need to query one or two projects, rather than the dozens that are in Project Online. So, if there's a way to just query those projects and/or records rather than the entire site (just to filter 90% out in the next step), it would stand to reason that the query would run faster.

    Thanks for helping out practitioner who likely has more enthusiasm than skill.


    JFitch

    05 September 2018 21:37
  • Hello,

    If you need to only include one or two projects / tasks in the whole report, when in the Power Query editor open the queries, for example Projects, find the Project Name column, click the down arrow on the column header then change the project selection to just the projects you need, you will then see a new applied step for filtered rows. Repeat this for the other queries in the report. Some tables might only have the ProjectId - if it is always going to be just these 2 projects, best to use the ProjectId column for the filtering.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    06 September 2018 7:58
    Moderator
  • Thanks for the response. I will try that approach.

    Follow up question: Does that help with the query time or am I running a query that brings in a ton of records only for to immediately dismiss them? 


    JFitch

    13 September 2018 0:08
  • Power BI will perform Query Folding so it will try and reduce the amount of data brought down. You can check if query folding is being applied using the Query Editor - if you right click on the query and can select View Native Query, then folding is being applied at that step.

    Ben Howard [MVP] | web | blog | book | downloads | P2O

    13 September 2018 12:32
    Moderator