Long time to ‘Refresh’ data for a dynamically generated data source

[sohananahid]

Hi all: Good day! I am developing a dashboard to show different maintenance events info. I pull data from a database table for the events. To get details info for each event, I connect to an API [in api portal] that I can pass an event ‘Id’ and get the details info for that event. I dynamically build a details table for each event ‘Id’.  I have set time frame for pulling data from the first source [DB table] starting from January 01 2020 since there are events data for past few years in the DB. But even that while refresh data for the second source[ details table] in my desktop env, it takes almost 1.5 hours for the refresh process.

1. Does it get better when we publish the dashboard in the server? [haven’t yet published it in our org server]
2. Is there a way to make the refresh process faster?  Many thanks in advance. 🙂

[mahoneypat]

It seems inefficient to make a web call for each event id.  How many events are you doing that for?  Is there a different API call you can make that will get all events in one shot (still passing the date paramaters in the call)?  Even if there is a row limit, you could still get the data in multiple calls with pagination, and probably save a lot of refresh time.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[sohananahid]

Hi @mahoneypat : Thanks for your response.

The Api definition says it can take a max of 50 ids.

How do I now change my parameter and function to call the api? So far I have a parameter, 'Event Id' that is of type ‘text’ and I pass a sample value. Then I created a function on this parameter that I invoke on to create the details table dynamically.

Do I select List of values to create the parameter? Then how does the function know to send 50 different ids at a time?

Many thanks in advance.

[v-shex-msft]

Hi @sohananahid,

You can't use query parameter to send a list of 50 values.
The option 'list of value' and 'query' mean the selection range of current value, it does not mean you can choose multiple values and send them at the same time.

I'd like to suggest you create a text type query parameter and input id list which concatenate with 'comma' delimiter.

Regards,

Xiaoxin Sheng

[mahoneypat]

Can you show the example syntax they provide when providing more than 1 ID in the call?  I assume it is a comma separated list, but please confirm.  How many IDs will you need to search on?  If >50, you will have to do it in 50 id chunks (which is trickier but doable).

Please also look at the API documentation to see if there is a way to get all the data (e.g., instead of "?ids=...").  Are there other methods to more efficiently get all the data you want (e.g., ?status=).  Can you share a link to the API documentation, so I can take a look directly?

Regards,

Pat

[sohananahid]

Hi @mahoneypat : At the API portal- I haven't found any example to specify multiple ids. To test, I passed 2 ids [comma separated] and it returned the details info. 

As mentioned earlier, they say max of 50 ids to pass. Below is spec fo the parameters for 'Id'

"parameters": [

          {

            "name": "ids",

            "in": "query",

            "description": "Genscape unique id for each maintenance event. This parameter will allow, at maximum, 50 values.",

            "required": true,

            "type": "string"

          },

The link to the api portal is: https://developer.genscape.com/docs/services/natgas-notices-maintenance/operations/GetMaintenanceEve...

Many thanks in advance.

[mahoneypat]

I wasn't able to look that the API documentation w/o an account to sign in.  You can build your string of EventIDs with another query (you won't be using a parameter).  Below is some example M code to show you how to go from a column of numbers (event IDs) in a table and turn it into a comma separated text list.  You would then just reference that query to concatenate into your API call.  Hopefully you have <= 50 event IDs to string together.  If you have more, we'll have to break it into chucks of 50 and make multiple calls.  It would be much better if you could find out there was a more efficient API method to call instead of by Event ID (is there a support person you could contact to ask?).

To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = List.Numbers(10, 25,2),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    Custom1 = Text.Combine(#"Changed Type"[Column1], ",")
in
    Custom1

The key part is the Custom1 step where it refers to the a single column in the previous step (which returns a list of those values, which are concatenated with a comma separator).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[sohananahid]

Hi @mahoneypat : Thanks for your response.

1. Yes, it needs a login account for the API portal.

2, There are more than 50 event IDs to handle

3. There is a separate API to get all the event summary info, which I use first to get all the events (which gives me the event Ids).  But I need the 2nd Api (event details) to get some info like location_id that the 1st Api doesn't have.  The event Id is the primary key to join them together.

4. These are the 2 Apis available.

Thanks a lot for your help.