Datasets vs. Ad hoc Queries

The key difference is that a Dataset persists its data, while an Ad hoc Query returns temporary results. Persisting the data means you do not query the database every time you want it, which reduces load, especially when many people want the same data and would otherwise run the same query repeatedly. One Dataset can serve what would otherwise be many Ad hoc Queries.

Persisted data can also be much broader and deeper. An Ad hoc Query is well suited to a narrow slice (say a month or quarter of sales), but pulling several years of data each time you want it would be slow. A Sales Dataset spanning many years gives you and your users instant access, and because the data is indexed, aggregating thousands or millions of rows is fast. The trade-off is latency: a Dataset's data is only as current as its last refresh. You can refresh on demand or on a schedule, and historical data can be pulled once and never refreshed.

Persistence also provides a single source of truth. Instead of wondering which report someone ran to get a number, everyone references the same verified Dataset, and reports built on it always render its most recent refresh, so results stay consistent.

Datasets are required to create Dashboard or Comparison Board Reports. Ad hoc Query results have a more limited use.