Auto-refresh option for queries

One-time queries show you a snapshot

As an analyst, you can run different kinds of queries in Workplace Analytics, including person, meeting, group-to-group, and person-to-group queries to get a snapshot of workplace behavior.

You can run a query as a one-time event where you set it up, run it one time, and get the results. To visualize workplace patterns uncovered by this query, you can load the results into a data analysis tool, such as Power BI or Excel.

Query results, especially when viewed in a data analysis tool, can uncover dynamic patterns. These patterns evolve over time because the workplace behavior of employees evolves over time. To isolate one instance of evolving workplace behavior over time, you can use the applicable query multiple times, on a regular schedule. To do this, you can use the auto-refresh feature for Workplace Analytics queries.

Create a query with the auto-refresh option

Note

Generally, an organization can have a maximum of 20 auto-refresh queries. For more information, see Maximum number of auto-refresh queries.

To create an auto-refresh query

  1. In Queries, select a query type, such as Person query.

  2. Name the query and add an optional description.

  3. Select a time range, such as one month or one year:

    Setting auto-refresh for a Workplace Analytics query

    Note

    Auto-refresh is available only for the predefined time ranges in the Time period list. If you select Custom range, the Auto-refresh option is unavailable.

  4. Select Auto-refresh.

  5. Select Run.

Stop auto-refresh

Follow these steps only for a query that has already run and for which auto-refresh is currently in effect.

  1. In Workplace Analytics, go to Queries > Results.

  2. In the query's row, select the ellipsis.

  3. In the More options menu, select Turn off auto-refresh:

    Turn off auto-refresh

Start or restart auto-refresh

Follow these steps only for a query that has already run and for which auto-refresh is not currently in effect.

  1. In Workplace Analytics, open the Queries > Results page.

  2. In the row of the query, select the ellipsis.

  3. In the More options menu, select Turn on auto-refresh:

    Turn on auto-refresh

Alternatively, you can renew an auto-refresh query in the notifications panel in Workplace Analytics.

Auto-refresh details

Basic behavior

  • When you create the query, you run it for the first time. As it runs, it uses data from the exact date range that you defined.
  • The query automatically runs again, once for every time period that you selected (such as week or month). Each run coincides with the date on which Workplace Analytics refreshes mail and calendar data from Microsoft Exchange.
  • Each time the query runs automatically, its date range advances by one unit of the time period that you selected (such as week or month). That is,both its start date and its end date shift shift to a later date by the same amount of time.

Auto-refresh stop, restart, and expiration

  • You can start or stop auto-refresh for a query at any time. See Stop auto-refresh and Start or restart auto-refresh.
  • The auto-refresh option of a query expires after two months. This expiration period applies after your initial setting of the auto-refresh option and also after each renewal of the auto-refresh option.

The Is Recurring column on the Queries > Results page shows whether auto-refresh is currently turned on for the query represented by that row:

Turn off auto-refresh status

  • After auto-refresh expires for a query, you can renew it. See Start or restart auto-refresh.
  • Deleting a query for which auto-refresh is turned on stops all future auto-refresh runs of that query.

Maximum number of auto-refresh queries

Generally, an organization can have a maximum of 20 auto-refresh queries. If your organization has already reached this limit and you need a new auto-refresh query, you have the following options:

  • Delete an existing auto-refresh query or turn auto-refresh off for a query (on the Queries > Results page). Then, create your new auto-refresh query.
  • Contact Workplace Analytics to request additional auto-refresh queries. To do this, follow the instructions in the "Workplace Analytics Support" row under Get support.

System turns off a query's auto-refresh option

Two circumstances can cause Workplace Analytics to turn off the auto-refresh option for a query:

Both of these circumstances present opportunities for admins and for analysts to respond, as described in the following sections:

Columns missing from organizational data

Admins regularly upload files that contain organizational data. In one of the upload steps, they choose whether to append the new organizational data to the existing data, or to replace the existing organizational data with the new data.

If the admin chooses "replace," they can retain the existing data schema or use a new one. Introducing a new data schema can cause a schema mismatch with the original schema. This happens, for example, when the new schema is missing one or more columns that were present in the old schema.

If a query that is set to auto-refresh uses a column that is missing in the new schema, Workplace Analytics turns the auto-refresh option for this query to off.

Excluded or hashed columns

While uploading organizational data, admins can set report options in ways that affect the inclusion and display of data columns. For example, they can set a column to "Exclude from report" (which omits the column) or to "Hash in report" (which masks the true value with a meaningless value).

If a query set to auto-refresh uses a column that becomes excluded or hashed, Workplace Analytics turns the auto-refresh option for this query to off.

Options for admins

If either of these schema-mismatch situations arises, after you finish mapping fields, Workplace Analytics shows a warning message about your "upload has certain issues that might affect execution of the auto refresh queries."

If you see this message, go to If expected columns are missing or excluded for more information.

Options for analysts

If either of these schema-mismatch situations arises, Workplace Analytics notifies you in various ways. You can then change the affected auto-refresh queries to run them again or access the results of the query's most recent completed run.

  • In Results, a red "stopped" icon shows in the Status column when a query has been stopped. A yellow icon shows for non-auto refresh queries that, as a result of the schema changes, also cannot run.

  • In the Select filters and Organizational data sections of a query-authoring page, the current filters and the output columns that are no longer present are shown:

    Filters and missing columns

    In many cases, you can still manually run the queries for which auto-refresh has been turned off, but if columns are missing, the query's results will not contain the attributes that were removed.

View query results with the auto-refresh option

In Analyze > Queries > Results, find your query in the results table, which includes the following:

  • The results (a .csv file) of the latest weekly run. You can select Download to download an archived file of these results.
  • The date of when the query last ran.
  • An icon that indicates it as a query with the auto-refresh option.

Auto-refresh query results in Power BI

On the Results page, you can copy a link to the query results and then use it in Power BI.

  1. To the right of the query's row, select Copy link:

    Copy a query's results link

  2. Select Copy. The Get results link dialog box displays the word "Copied."

  3. In Power BI, on the Home tab, select Get Data, and then select OData feed.

  4. In the OData feed dialog box, paste the link that you copied into the URL field:

    OData feed in Power BI

  5. Select OK.

  6. Enter your client credentials, and then select Connect.

After you do this, Power BI maintains a connection to this query in Workplace Analytics. In the future, to visualize the query's current results, open the Workplace Analytics project in Power BI and select Refresh.

View, download, and export query results