Skip to main content

Creating reports directly from SQL?

Introduction

Holistics doesn't support creating reports directly from a SQL query, but instead asking you to define "models" and "datasets" before creating reports. If you have experience working with BI tools with a "SQL to charts" approach (e.g Redash, Chartio), you might find this approach unfamiliar.

This post talks more about why, and offers you a workaround if you still prefer SQL to charts experience.

Why we follow modeling-based approach

In a "SQL to charts" BI tool, you simply write and run a SQL query, select some chart types and save it as a report. While relatively straightfoward, the downsides of this approach are:

  • Fixed Reporting: Non-technical users cannot customize their own reports without knowing SQL.
  • SQL definitions sprawl: As reports increase, reports definitions get duplicated all over the place.

Holistics takes a different approach by introducing a semantic/modeling layer in between. While it takes more setup time, the benefits of this approach are:

  • Self-service: Non-technical users can build their own reports without relying on data teams.
  • Central definitions: All business logic are centralized and organized in one place.

Still, how can I create reports from a SQL query?

If you have some complex reporting logic that the available Datasets cannot satisfy, you can package your SQL transformation in a Transform Model, and create a reporting widget on top of that model by following these steps:

  1. Go to Data Modeling page.
  2. Click + Create → Add Data Model from Data Transform
  3. Write your SQL transformation and save it as a Transform Model
  4. Add relationships if needed
  5. In the Data Model UI, click Explore and create a Dataset
  6. From the Dataset explore, drag and drop to build your visualization
  7. Turn that into a report


Let us know what you think about this document :)