One weird trick to use date dashboard filter on your custom SQL in Metabase

After participating a high-pressure sales meeting for one of the major BI vendors I stumbled upon an open source solution that covers 99.9% of our business use cases. When I evaluated this possibility several years ago there were no open source players in the field. Much to my surprise we found Metabase! From their site:

Metabase is the easy, open source way for everyone in your company to ask questions and learn from data.

We opted to self-host and had a test instance deployed within a few hours. The time from install to first dashboard was incredibly fast. The major benefit to this is that none of our data ever leaves our internal network.

Once our initial read-only dashboards were deployed we had additional reporting requests such that the user could analyze data over a period. No problem, there is a date filter available at the dashboard level. Unfortunately, most of my reporting is done using custom SQL and I couldn't seem to get these filters to work no matter what I tried. The documentation was vague on this point and however I tried to write a query I would get "Invalid Field" when trying to apply the dashboard filter to the card.

The resolution is simple. In your question create a field filter based on the date/time field that you want to filter by, in our case "CREATED_AT".

Question setup for date range filtering

By creating the field filter like this you allow the dashboard filters to inject their own SQL as appropriate for the type of filter you have. Allowed date/time filters currently include:

  • Month and Year - Like January, 2019
  • Quarter and Year - Like Q1, 2016
  • Single Date - Like January 1, 2019
  • Date Range - Like January 1, 2019 - January 8, 2019
  • Relative Date - Like "the last 7 Days" or "this week"
  • All Options (gives the dashboard user every option listed above)

Add the question you created to your dashboard, go into edit mode, and click "Add a Filter". Choose "Time" then choose the type of filter (listed above) you want to apply. In our example we used "All Options". Finally drop down the time field you want to use in each card.

Save your dashboard and enjoy your new dashboard filter. If this has been helpful please feel free to drop me a note and let me know!

AWS Users Group Meeting 5/17/2018: Doing things fast with ElastiCache

Last week I attended the local AWS users group meet-up. This was the first time with this group and naturally I was a little apprehensive. Luckily the pizza was amazing, the group was welcoming and the presentation was well executed and informative. Great job Scott! I've already got the next meet-up on my calendar.

ElastiCache is hosted/managed Redis and Memcached by Amazon. This allows developers to immediately use these technologies without provisioning and configuring servers. One negative though is that it must be used within Amazon's VPC (Virtual Private Cloud) and cannot be secured except for with instance level access lists.

For the demonstration our presenter, Scott, wrote a simple web application (in Ruby) that allowed for Widgets to be built by workers and then processed. Finally a CEO dashboard broke out a bunch of statistics about the work done, both today and historically.

Naturally in the small scale demo (< 10,000 widgets) the system ran great! Everyone was happy and the CEO could get their statistics. Enter a huge number of widgets. Suddenly worker productivity plummets. It takes tens of seconds to create a widget, over a minute to load the process screen and the CEO dashboard times out with an error. Now we have an upset CEO. Let's solve this by caching. By storing simple counts and last created widgets in ElastiCache Scott was able to simply fix both the worker and process pages (once the cache was warmed-up). The CEO dashboard proved to be a little more interesting as it required much more data in order for the cache to warm up. Once it was loaded (~3 minutes for a large number of widgets) then the CEO dashboard was once again happy.