View long term retained data

Article
03/12/2024

You can view retained data from an advanced find query or by creating a Power Automate flow.

To view retained data in an environment requires the system administrator security role or other security role membership that includes organization scope read privileges to the table.

Grant privileges to view retained data

Imagine an auditor requires access to long term data retained for the accounts table. To provide the auditor access, a Power Platform admin creates a new role, for example a role named LTRAccounts Access Role and grants organization scope read privilege to the accounts table. Then add the auditor's Power Platform user account to the security role. When the auditor's job is complete, it's a best practice to remove the auditor from the security role.

For more information about creating and editing Dataverse security roles, go to Create or edit a security role to manage access.

View retained data using edit filters from a model-driven app

Note

You can't save or export the the view query results of the retained data for sharing with others. To share retained data, create a cloud flow to view Dataverse long term retained data.
Edit filters doesn't retrieve table row attachments. To view attachment data, create a flow. More information: Create a cloud flow to view Dataverse long term retained data

Sign into Power Apps, play an app that includes a table that has retained data.
Open the view you want. From the view, select Edit filters.
Select Change to retained data.
Select the tables and search filters you want, and then select Apply. The retained data is displayed in the read-only grid. If you need to retrieve long term data from multiple related tables, such as the account table, which has an associated retained case table, first use advanced find retrieve the retained case row. Then use the Casenumber column and use advanced find to retrieve the account row that contains the case number.

More information: Advanced find in model-driven apps

View retained data using a flow

Create a Power Automate cloud flow to create an Excel file of the retained data from a FetchXML query and send as an email attachment. More information: Create a cloud flow to view Dataverse long term retained data

Note

If the retained data includes attachments from the annotation table, the returned value is a base64 representation of the file. Large files might cause the cloud flow action to time-out or to exceed its output message size limit.

To workaround this behavior, use the Web API to perform the export action ExportRetainedData using Azure Functions or other custom development options.

Limitations for retrieval of retained data

These restrictions are enforced by Dataverse for each environment:

Up to five users can query and retrieve retained data at the same time.
Up to 100 queries per day are allowed for each environment.
Any single request from advanced find, Power Automate cloud flow, or Dataverse OData public API is considered as one query.
Queries are allowed on one table at a time. Joins and aggregation functions aren't allowed. Consider options with Microsoft Fabric for complex queries and Power BI options. More information: View retained data with Microsoft Fabric
Retained data includes lookup data. Lookup values in the table are denormalized with ID and name value.

View retained data with Microsoft Fabric

You can view the active (live) and inactive (long term retained) application data in Dataverse using Microsoft Fabric. To do this, link your Dataverse environment to Fabric. More information: Link your Dataverse environment to Microsoft Fabric and unlock deep insights.

When your long term retention policy is run successfully, you can access the active and inactive Dataverse data. The limitations applied to retrieval of retained data don't apply to this mode of access.

You can explore the data with SQL endpoint and query Dataverse data with SQL and generate views in Fabric. You can also create Power BI reports. More information: Work with Dataverse data and generate Power BI reports

The Dataverse table column msft_datastate can be used to filter the data with the SQL WHERE clause:

Inactive application data: WHERE msft_datastate=1
Active (live) application data: `WHERE msft_datastate=0 or msft_datastate=NULL'

Known issues

Personal views of retained data

Saving the query results of the retained data as a personal view isn't supported. Although users can save the query results of the retained data as a personal view, the view doesn't return results.