#protip - If your chart's query contains join you should probably base it off a view.

I'm in the middle of a big refactor of our charts and views.  One of the things I'm observing is that we have an explosion of charts that have extremely complicated queries in them.  Many times those queries are 98% the same as some other chart's query.  Often as not there's very complex business logic being replicated all over the place.

It seems to me that a golden rule to prevent this kind of chaos would be that if you find yourself putting a JOIN in a chart's query you should probably stop immediately and make a view, then base the chart on the view.

I've just refactored over 30 charts to all be based on one common view.

So much Zen!

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Jason Glover
    • 20+ years of SOLID experience in Product and Development. Managing teams, projects and programs. SaaS entrepreneur.
    • Jason_Glover
    • 5 mths ago
    • Reported - view

    ps.  I'm keen to hear any counter views.

    Reply Like
  • No pun intended on the counter views I'm sure. :)

    Your development pattern resembles Periscope's data team's very closely. I don't have a counter view, just an addendum. Internally we use a system of hierarchy to help us become agnostic of the field name or logic changes in our underlying data sources. We can then adapt the data model as necessary to keep our charts humming while replacing the internals.

    At the current moment, we have a few levels of 3 levels of abstraction:

    View: These are as basic as select * from table. They can be easily updated as the underlying data changes and prevent us from having everything break when these changes occur.

    Staging: These are the building blocks of the data model. They help define relationships such as linking two ids from two data sources. They also roll-up individual KPI so that the calculation can be adjusted within one view.

    Report: Multiple staging tables are brought together to create tables with many metrics around key dimensions, which we call _summary, or tables with similar metrics over time.

     

    Then on the chart building front, we use the report > staging > view heuristic to ensure we are joining as little as possible to get all the fields we need for a chart.

    Reply Like
Like2 Follow
  • 2 Likes
  • 5 mths agoLast active
  • 2Replies
  • 108Views
  • 2 Following