CRM Analytics Direct Data for Google BigQuery Tips and Considerations

Tips for Working with Direct Data for Google BigQuery

To improve the performance of dashboards based on BigQuery data, it’s important to optimize their query times. Queries that run faster reduce the possibility of reaching query concurrency limits. Applying sound dashboard design principles is also helpful in ensuring that BigQuery-related dashboards run smoothly.

For optimized query times, keep these tips in mind.

• Work with your BigQuery administrator to optimize the BigQuery warehouse and data for analytic queries.
• To make it easier for users to create queries, consider creating SQL views of the BigQuery data for your users.
• Consider creating materialized views in BigQuery for most frequently run queries.
• For larger tables, create filtered views that focus on subsets of data.
• Run the dashboard inspector to monitor and track query times.
• To optimize query times:
  • Select only the columns needed for your visualization.
  • Apply filters to limit the number of rows.
  • Avoid grouping on high-cardinality fields.
  • Avoid using the contains operator. Instead, use the equals operator.

Apply these design principles to dashboards based on Google BigQuery data.

• Limit the number of queries on a single dashboard page. Instead, spread queries across multiple pages.
• To minimize the number of queries being executed, reuse the same query in multiple widgets whenever possible.

Direct Data for Google BigQuery Considerations

Keep these behaviors in mind when working with CRM Analytics Direct Data for Google BigQuery.

• Row-level security isn’t applied on BigQuery data. So, users with access to a live dataset can view and explore all data rows in the associated Google BigQuery table or view.
• Dashboard widgets based on a live dataset appear blank to users without access to that dataset.
• The following features aren’t available with Direct Data for Google BigQuery:
  • Drill, Focus, and View Data Table actions on lenses. As a workaround, add filters manually.
  • Data-blended queries
  • Downloads as CSV or Excel files
  • Dataset actions
  • Notifications, subscriptions, and CRM Analytics Watchlists
  • Search insights on data
  • Compact Form v1.0
  • Security predicates and inherited sharing rules
  • Migration via change sets
• You can’t use live datasets as sources in dataflows or recipes.
• You can’t run join operations on live datasets.

Keep these limitations in mind when working with CRM Analytics Direct Data for Google BigQuery.

• Experience Cloud does not support widgets that use live connectors, such as Snowflake, BigQuery, or Data 360.
• Not all dataset functions are available in direct data compared to CRM Analytics datasets. Specifically, the Percentage of Group, Rank Within Group, and Running Total functions and all of the time series functions aren’t available.
• The dashboard repeater widget doesn’t support dashboard filters. This widget displays date values with both date and time, instead of just the date.
• Bar charts don’t support conditional formatting.
• Because there isn’t a stored dataset for direct data, users can’t change labels in the Show Fields modal.
• Advanced custom SQL queries containing subqueries or Common Table Expressions (CTE) don’t work as expected.

For query limits, see CRM Analytics Direct Data Query Limits.