Own your business intelligence reports with evidence.dev

Ever felt trapped by traditional BI tools? We did too. That's why we're thrilled to share our experience with evidence – a tool that lets you build business intelligence dashboards using good old SQL and Markdown.

Highlights
00:00 Introduction
00:48 Features and capabilities of evidence
01:31 Portability and open source nature of evidence
02:52 Examples and use cases
04:17 Component reference and data sources
05:35 Build process and deployment
06:04 Code examples and dynamic routes
07:08 Adding static information to dashboards
08:13 Intended use

Links
- Evidence Website: https://evidence.dev/
- Evidence Examples: https://evidence.dev/examples
- Evidence Docs: https://docs.evidence.dev/
- Markdown Basics: https://www.markdownguide.org/basic-syntax/
- SQL Tutorial: https://www.w3schools.com/sql/
- DuckDB: https://duckdb.org/

Steffen: 0:00

If you want to create a business intelligence board as a developer, evidencedev is a great choice.

Dominik: 0:09

Yeah, so actually we are developers, right, but we are also business owners and sometimes we need to find a way to visualize data, to do like business intelligence, to create dashboards, to visualize something that we might not see Otherwise if we just had the raw data right.

Dominik: 0:27

And we've always been trying out different tools and using different services, paying for tools that let you create dashboards through their own UI, with their own query language or own, like custom way to build these dashboards or widgets or whatever, and we were in the need for something like that again. A while ago, we stumbled upon a tool called evidence or evidencedev, and this titles itself as business intelligence, as code, and what you can do with it is you write markdown files so that's something most developers, or web developers at least, are familiar with and you can write your SQL queries directly in the markup and also use, like MDX or in this case, it's like swelled components right in your markdown and have it render like these beautiful graphs that are then created with a library by an Apache library eCharts it's called, so it's like a really powerful library. You can do a lot of things with it and, yeah, this is a tool that we really like so far and we have made some, like some, good experience with that right.

Steffen: 1:31

Absolutely. What I really like about this approach is just that it feels like I'm not locked into their approach. What do I mean? Like we have used business analytics intelligence tools before, like these dashboard tools, where then you get a custom interface where you can drag and drop certain widgets and then many times you have to either use their own query language or data connectors and stuff like that to create these charts, and if you want to move somewhere else with that data and with those visualizations, that's really hard, if not impossible.

Steffen: 2:08

I think we created once created like a huge dashboard for a company, and when we moved away from this tool, we basically lost all of that stuff that we created with evidence. That's simply not the case, because you are writing my sequel queries or other queries, like in other languages, and can directly work with the tools, and you just create kind of definitions and markdown and so on, and this this feels like much more portable, and so I had an easier time signing up to this approach. At the same time, it's also open source, which is great. Very much looking forward to their additional services in the future. But yeah, I didn't find much bad to say about this. I was just like super excited about this.

Dominik: 2:50

Yeah, totally. And if we look on the website, they have a lot of examples here as well, like live apps and code for different dashboards that you can have and, for example, this like internal KPI portal where you see, okay, you have like these numbers with like trends, you have graphs, but you can also have links. So this looks a little bit like notion, right. So it's like a real markdown fire that you can also link pages from one page to the other, Also like even use like these or link elements of your graphs to other pages and so on. So this is quite powerful. It is not real time, right. So it is not that your dashboard has a direct connection to your database, but you have to run a build step at which it just creates a statically rendered site and then you can deploy that somewhere. Well, on the one hand, this is maybe bad if you want real time data. It's usually pretty nice to have that, because then, once you know that your site has been built with the queries, you are not like causing stress on the database anymore and you know that the queries are just working right. This is also pretty nice, maybe for linking. We can show this example this weekly business. So they have like this data table here and then in here they link these rows of the table to other documents where you can then also use like the URL params for manipulating or adjusting the queries based on the data that is in the params right. So that is pretty cool and pretty nice and we are really happy.

Dominik: 4:17

So far in the docs you can see also like component reference, you can see a list of all the components and, as I said, it's based on each charts and there's also like a component where you can have a custom each chart and then you just write a little bit of JavaScript or like some configuration arrays also directly in your markdown. So that's also pretty nice. You said there's not a lot or there's nothing that we really don't like about it. Right, there's one downside if you have like multiple data sources, currently you can only connect via one data source and these are currently the supported data sources. So we have BigQuery, snowflake, redshift, postgres, post. So you know we're German.

Dominik: 4:57

We don't really know how to pronounce all of that Postgres, QL, right A, Microsoft SQL Server, MySQL, SQLite, DuckDB, NCSV and ParketteFight. That's currently what they support. They have, however, a next branch where they have a new adapter where you can combine any of these data connectors. So that will be actually for people who do a lot of BI and who need multiple data sources. This will then be the way to go.

Dominik: 5:22

We also found out about DuckDB right here, so maybe we'll have another podcast or session about DuckDB, because that's also a really nice database, a memory database that you can use to perform analytical queries on large data sets. So what about building? Maybe we can say a little bit about that as well. So we, as I said, it's not real time, you have to statically build it. So we integrated the build process onto our CI CD server and we just do like a daily or like four builds daily, like every six hours. We just build a new static version of this page and then push it to a static web server and then can view our reports there in the browser.

Steffen: 6:01

So if we look at some code, sure.

Dominik: 6:03

So yeah, if we look at some of the examples this is one of their example from the homepage, where you also have like the code linked. It's basically behind the surface, it's swell kit, where you can just have like a pages directory and in these pages you define like your URL, your endpoints, kind of, and in here you can directly, by just saying like defining something as a code block either like this or like like this, to get more syntax highlighting. This is what we do usually and then you just define the queries in here and if you then search for daily KPIs, you can see, for example, you can also nest or combine these queries and then use this directly in markdown, either here as a text field, or you can use it here inside of these charts in the data attribute.

Dominik: 6:53

Yeah and that's basically a stair, dynamic routes, as I said, for like dates you can use like the page params object for that to access these and use those in the queries as well. So that's really some powerful stuff that you can do with it.

Steffen: 7:08

Yeah, I personally also like just the pure ability to add static things to these analytic pages, right? For example, for our VRT's tool, we created this dashboard where I then inserted manually the release dates and release versions, which were then added to the graphs, so we would know what kind of effect a feature change might have had on the churn rate, for example.

Steffen: 7:36

So, and as it's so easy to to update these markdown files over time. It doesn't have to be this one time static, perfect dashboard, but you can develop it over time. You can add data, you can add notes and so on by just like committing to the markdown file. I think that makes it so portable, kind of, and easy to use.

Dominik: 7:56

Yeah, usually you're caught like in these widgets and then you have like OK, I create a new widget here and then here, and these are more like reports and it's HTML or like with markdown. It's HTML, but you can write anything in markdown that you would in an HTML page, right, so that's really really powerful and yeah yeah.

Steffen: 8:13

However, it's not meant to be used as an analytics dashboard, right? Like you're not meant to be using data analytics from Google Analytics or stuff like that to then create your custom dashboard. It's meant for business intelligence, where you query a database in regards to your data lake, whatever, like where you have all your product data stored, your customer signups and stuff like that. That's what it's meant for. I'm not even sure there are connectors to connect that to Google Analytics and stuff like that. Maybe they are most likely there, at least companies or startups who do that. I'd be very interested in learning about those. If you know any of these tools to load data from, let's say, google Analytics via a connector to query it, just like with my sequel or similarly, let us know in the comments down below.

Siegfried, deploy!

Own your business intelligence reports with evidence.dev

Listen to this podcast on