Automated Pipeline Activity: Scheduling Dataset Requests via API

[jkrumb]

Hello everyone, I am looking for a way, through the data pipeline, to retrieve the last refresh time for each scheduled dataset.

I’m able to retrieve the Bearer token and list the datasets in a workspace. Then, I use a Foreach activity containing the condition @equals(item()?['isRefreshable'], true) to filter and get only the semantic models that are scheduled for refresh.

From this, I store the following values in a variable:

{
"id": "@{item()?['id']}",
"name": "@{item()?['name']}"
}

 

In the second Foreach activity, I use a Web activity with the relative URL:

 

@concat('v1.0/myorg/groups/ID-WORKSPACE/datasets/', json(item()).id, '/refreshes?$top=1')

 

This allows me to retrieve the latest refresh of each dataset in the workspace.

Now, I would like to extract:

• The id and name from the first loop,

• As well as the refreshType (when it is "Scheduled"),

• The endTime,

• And the status,

  All in a single row so I can store this data in a data warehouse.

[burakkaragoz · Accepted solution]

Hi @jkrumb ,

To get everything in a single row, you can do the following inside your second Foreach loop (the one calling /refreshes?$top=1):

1. After the Web activity, add a Set Variable or Append Variable activity.
2. In that activity, construct a JSON object like this:

{
  "id": "@{item().id}",
  "name": "@{item().name}",
  "refreshType": "@{activity('YourWebActivityName').output.value[0].refreshType}",
  "endTime": "@{activity('YourWebActivityName').output.value[0].endTime}",
  "status": "@{activity('YourWebActivityName').output.value[0].status}"
}

1. Append this object to an array variable.
2. At the end of the pipeline, you can write this array to a data warehouse using a Copy activity or Stored Procedure.

⚠️ Make sure to handle cases where value[0] might be null (e.g., no refresh history yet) to avoid runtime errors.

Let me know if you want help with the full pipeline JSON or error handling logic.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[burakkaragoz · Accepted solution]

Hi @jkrumb ,

To get everything in a single row, you can do the following inside your second Foreach loop (the one calling /refreshes?$top=1):

1. After the Web activity, add a Set Variable or Append Variable activity.
2. In that activity, construct a JSON object like this:

{
  "id": "@{item().id}",
  "name": "@{item().name}",
  "refreshType": "@{activity('YourWebActivityName').output.value[0].refreshType}",
  "endTime": "@{activity('YourWebActivityName').output.value[0].endTime}",
  "status": "@{activity('YourWebActivityName').output.value[0].status}"
}

1. Append this object to an array variable.
2. At the end of the pipeline, you can write this array to a data warehouse using a Copy activity or Stored Procedure.

⚠️ Make sure to handle cases where value[0] might be null (e.g., no refresh history yet) to avoid runtime errors.

Let me know if you want help with the full pipeline JSON or error handling logic.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[jkrumb · Accepted solution]

Thank's you for your return,

I had to make a change, but now everything's working fine — I'm getting the information I wanted. Thank you very much for your help!

Modification:

{
"id": "@{json(item())?['id']}",
"name": "@{json(item())?['name']}",
"refreshType": "@{first(activity('Myactivity').output.value)?['refreshType']}",
"endTime": "@{first(activity('Myactivity').output.value)?['endTime']}",
"status": "@{first(activity('Myactivity').output.value)?['status']}"
}

Exemple Output :

{
"variableName": "final",
"value": "{\n \"id\": \"0xxx-xxxc-xxxx-xxxxx-xxxxxx\",\n \"name\": \"MON RAPPORT 1\",\n \"refreshType\": \"OnDemand\",\n \"endTime\": \"2024-09-17T09:52:58.823Z\",\n \"status\": \"Completed\"\n}\n\n\n"
}

{
"variableName": "final",
"value": "{\n \"id\": \"0xxx-xxxc-xxxx-xxxxx-xxxxxx\",\n \"name\": \"MON RAPPORT 2\",\n \"refreshType\": \"Scheduled\",\n \"endTime\": \"2025-05-26T07:10:24.75Z\",\n \"status\": \"Completed\"\n}\n\n\n"
}