User.events custom bucket for faster dashboards — is data copied or moved?

[gauravpayghan]

have a dashboard on the user.events table. Each event has ~35 fields, but my dashboard uses only 3. Queries get slow on wider timeframes because they scan the full default bucket. I want a custom bucket with just the data my dashboard needs, and point the dashboard at it for faster loads.

Questions:

1. When I assign user.events to a custom bucket via OpenPipeline, is the data replicated (kept in default too) or moved/routed so it only lives in the new bucket?
2. Is assignment forward-looking only (existing records stay in default_user_events, no backfill)? If so, what's the recommended cutover?
3. Can I store only a subset of fields (3 of 35), or does assignment always route the whole record?
4. For a lean, fast dashboard dataset, is a custom bucket best — or metric extraction / bizevents the better performer?

(Simply I want to load the data faster on the dashboard from user events table data, recommend best practice.)

Thanks in advance for any best practices or examples!

[m3tomlins]

On your first question, the OpenPipeline storage assignment would not duplicate the record. I would just store the record in the new custom bucket you created and NOT be added to the default bucket. The DQL on the dashboard would execute with your user permissions to query across all storage buckets (custom and default) to find the records.

I see a couple options to help performance:
 - make your custom bucket defined as not just data, but data + queries with retention on the typical timeframe used by the dashboard
 - create a scheduled workflow that queries the default user.events (and 3 attributes) every 1 minute and re-ingest those into a custom bucket (with data and query retention)...but this duplicates data and costs more.
 - ensure your dashboard does not have default wildcard (*) in the DQL ...if you use variables from DQL and/or $variables with (*) in the tiles ...and narrow the default timeframe also. Anything to reduce the query cost on the first opening of the dashboard 
 - depending on the attributes and dashboard design, you might consider extracting metrics from the user.events and using the metrics on the dashboard? then you might only query deeper into the custom user.events records by exception, to filter on a specific user
 - a wild idea: periodically create this whole dashboard as static data with no interactive DQL. Query once to get the data for a huge static dataset and load that into the dashboard .json -> deploy that to the tenant? (this seems cumbersome to me)

[dannemca]

• When I assign user.events to a custom bucket via OpenPipeline, is the data replicated (kept in default too) or moved/routed so it only lives in the new bucket?
  • Data is moved to the new/custom bucket.
• Is assignment forward-looking only (existing records stay in default_user_events, no backfill)? If so, what's the recommended cutover?
  • Yes, the data start to happen in the new/custom bucket once you create the pipeline. The cutover will depend on your needs... if your dashboard show last 30 days, keep both buckets in your DQL filter, and remove the default one after 30 days.
• Can I store only a subset of fields (3 of 35), or does assignment always route the whole record?
  • You can create rules in your openpipeline to store only the fields you need, but remember that the field you left will not be stored anywhere.
• For a lean, fast dashboard dataset, is a custom bucket best — or metric extraction / bizevents the better performer?
  • Metric data should load faster in dashboards, so do consider to use metric extraction you are displaying numeric data only.

Depending on the data you are displaying, making it as metrics will make the dashboard to perform better, for sure.