Evidence is a free self-hosted business intelligence system using Markdown and SQL. It connects to your data warehouse and helps you build interactive business reports using Markdown. It uses SQL queries to fetch data that you can display in your reports visually. You can create charts, graphs or even simply display the data in tabular format corresponding to a custom SQL query.
You can even build a custom page by changing the comp nests and use different data visualization schemes to display the data in a presentable manner. For now, it supports BigQuery, Snowflake, and PostgreSQL as data warehouses to connect to. But in later updates there will be option to use MySQL, MSSQL and even SQLite as data sources.
Evidence supports custom tempting as well and gives you freedom to define your own styles. You can pass values from SQL queries to some other block, use loops, and even render Markdown syntax dynamically. You can create as many pages you want and pin them on the homepage.
Key Features of Evidence Business Intelligence System:
- Easy Install: A lot of self-hosted tools come with a lengthy and complex setup. But Evidence here supports easy installation and everything is set from a simple NPX template.
- Charts and Graphs: It lets you present the data you fetch using SQL queries as charts and graphs. It supports line, scatter, area, histogram, and bubble charts that you can create using the various columns on your data table.
- Multiple Database Support: For now there is built-in support for connecting to data warehouses like Google BigQuery, Snowflake, and Postgres. MySQL, CSV, MS SQL, and SQLite integration will be there in the coming updates.
- Some Programming Like Features: Evidence supports loops, conditional blocks, parameterized pages, tests and more. There is a specific syntax for doing different things, and you can learn all that using the tutorial it comes with.
- Real-time Page Updates: When you edit a page in Markdown and save changes, the changes automatically appear in the browser. If the page was open already, then it automatically refreshes it to show the changes.
- Host on Netlify: The Evidence website or project that you build after creating Markdown reports can be hosted as a static site. You can host it on Netlify for free by addign database credentials as environment variables.
Installing Evidence Locally:
The installation process is easy and you just need a Linux server or PC in order to start. Just make sure that your PC has the following packages already.
- Git
- NodeJS
- PostgreSQL Server
If you want to use other data warehouse sources, such as Google BigQuery, then you can do that. But for this post, I will be using PostgreSQL for the demonstration.
So, just open up the terminal and type the following command. It will create an Evidence project for you.
npx degit evidence-dev/template my-project
Just cd into the project folder and install all the Node modules. To do that, execute the following command.
npm install
Run the following command, open browser, and type http://localhost:3000 in the address bar and main homepage of Evidence will show up. You can see that in the screenshot below.
npm run dev
Now, the installation is successful and you can now start creating the reports. But before that, you have to configure database connection. For that, you just go to the “.evidence” folder in the project and open the “database.config.json” file with any text or code editor. Since I am using PostgreSQL in this post, so add the database credentials as shown in the screenshot below.
Come back to the project folder and open the “evidence.config.json” file. Here change the “Database” variable and enter “postgres” since we are using PostgreSQL. For Snowflake and BigQuery, enter “snowflake” and “bigquery” respectively.
Now, at this point, all the configuration is done. You can now start creating Markdown pages with SQL queries embedded in them to render them in the browser and see the results.
Using Evidence as a Business Intelligence System:
If you are familiar with Markdown syntax, then you can easily create a page using SQL here.
In the project, you go to the “src/pages” folder. Here you can create your Markdown pages and use their name in the base URL(localhost:3000 in this case). There is a test page there already called “firstquery.md” and you can start editing it to fit your needs.
Now, replace the content of the Markdown file with your own. To add a SQL query on the page, you use the following syntax. Paste this snippet anywhere in the Markdown and see the results in the browser. It will automatically reload the page for you to show to the changes.
```Any Name for the Data
SQL_Query Here
```
Now, you can take this to a new level by addign some charts or graph. For example, if you want to display the graph using the data you have fetched from SQL, use this syntax. It will create a simple line chart on the webpage, but you can use other type of charts.
<LineChart
data={data.NameOfData}
x=date
y=complaints
/>
There are a lot of other things you can do, and they are all listed on the official documentation page. You can see that document here and then simply build your report page accordingly. An example report page created using Evidence I have added at the beginning of this article.
In this way, you can use this simple and powerful business intelligence system with easy. All you have to do is use simple Markdown syntax information with SQL to create interactive reports or webpages. It doesn’t stop just here, as you can host the project as a static site on Netlify. The deployment guide is there in the documentation and you can go through that to configure the Netlify workflow for it.
Closing thoughts:
Indeed there are many business intelligence systems out there but Evidence here is quite unique one. Here it has almost all the features that you will need to connect to a database and create interactive reporting documents or webpages. The best part is that you can use it to create a whole website and even host that for free. For now, the supported database warehouses are less by in the coming updates there will be support for more.
