Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'm setting up refreshes of Power BI datasets through XMLA end points. I get the commands to work and data in the dataset is updated in Power BI service - but the time stamp for "Refreshed" does not update, neither when you look at it in the list of datasets nor when looking at "Data updated" in a report that is connected to that dataset.
Do I have to include something in the script to update the refresh date as well?
Current script is:
{
"refresh": {
"type": "full",
"applyRefreshPolicy": true,
"effectiveDate": "12/31/2021",
"objects": [
{
"database": "Dataset Name"
}
]
}
}
I have not found a solution to the initial question (how to update the Last refresh Date-time shown in Power BI service when running refreshes through XMLA-endpoints), but have taken a few steps on the way to a work around and thought I better share it if it might help someone:
The refresh timestamp set by the xmla-endpoint triggered refresh is possible to fetch through the xmla-endpoints by using this command:
$xmlResult = invoke-ascmd -Server "powerbi://api.powerbi.com/v1.0/myorg/Workspace Name" -InputFile $InputFile -Credential $Credential
Where $InputFile points to a xmla-file containing the following:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions/>
<Properties>
<PropertyList>
<Catalog>Dataset Name</Catalog>
</PropertyList>
</Properties>
</Discover>
and $Credential is created like this:
$userName = "myemail@outlook.com"
$password = ConvertTo-SecureString -String "password" -AsPlainText -Force
$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $userName, $password
The return result that ends up in $xmlResult is an xml where you can find LAST_SCHEMA_UPDATE (which is the last refresh date-time stamp that show in Power BI service) and LAST_DATA_UPDATE - which is the date-time stamp that the xmla-triggered refresh ran. Extracting that could potentially be useful for some cases. We however abondoned the idea and exluded our budget queries from incremental refresh to be able to use the built in refresh functionality to be able to trigger subscriptions sent out on data refresh and benefit from the overview you get in the service.
Yes, sorry, you mentioned that already. Do you have access to the tenant audit logs? They have more detailed refresh statistics, and they don't care what initiated the refresh.
Have you considered querying the Power BI API Refresh history? That will (in a roundabout way) give you the refresh progress status.
That's the way we've solved it previously - fetching refresh meta data through API and initiating refreshes through API-calls. The issue that I run into now is that when the refresh is triggered through an XMLA-endpoint (which we do to be able to alter how incremental refreshes behave) then the information available through API (and visible in the service) is not updated. So the original question was if there is a way to update that API data through xmla-endpoints, by adding some tag to the json-file we pass as input to the invoke-command or some other way?
Since I can't seem to find a way to do that I'm now working to extract the Last refresh timestamp through XMLA as well - that will solve our ETL-based refresh triggers, but still won't show the correct refresh time stamps in Power BI Service.
In my reports I never rely on that "Refreshed Date" - imho it is meaningless as you can (and will) see lots of scenarios where the service is refreshing stale data over and over. Instead I use a date field from my actual data , ie "Last Modified Date" to indicate the report freshness.
Thanks for your input, @lbendlin!
I agree from an end user perspective - In reports we normally show a Refresh date from the actual data. The issue is mainly from an administration point of view. It's quick and convenient to just go to a workspace list of datasets and get an overview of all datasets and check their latest refresh dates, ongoing refreshes and eventual failures. Using XMLA we don't see running refreshes, nor failures or updated Refreshed timestamp in that list.
Even more troublesome for us is that we use power shell scripts to refresh the data and fetch the last refreshed timestamp through an API call to compare that timestamp with indicators from our ETL-process for the data warehouse to make sure refreshes only start when all data for that dataset is available. When the Refresh date never updates our current scripts will always consider the dataset in need of a refresh.
Not only is it pointless, but our end users are regularly confused by the "Data updated" display in the top panel of the app. It appears to be updated when the app has been updated. However, updating the app has nothing to do with updating the data. Do you happen to know of a way to hide this indicator in apps? Like you, I prefer to see the timeliness of the data in the report itself.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.