I am wondering if anyone has any good documentation or advice on how to use the data in Baserow (self-hosted with stand-alone postgresql) for reporting using e.g. Tableau. I am interested in using Tableau for visualizing content in baserow and I’ve connected directly to Baserow’s postgres instance. It works well for displaying individual tables but it doesn’t work too well with linked and referenced tables as I cannot use ordinary joins as I would usually on the database side. Any advices?
Hi @flipzz, I don’t have any experience with Tableau or similar software, but I might be able to help you. As you have already noticed, every Baserow table will have a related PostgreSQL table. The table name sound like database_table_10, where 10 is the table id, so they’re a bit more difficult to understand. Every column in the table follows a similar structure where it’s named after the field id, field_20 for example. Every “Link to table” relationship in Baserow is a many to many relationship in PostgreSQL and the relations are stored in a separate table named database_relation_30, where 30 is the relationship ID.
The schema of your Baserow database is stored in separate tables.
database_table: contains all tables that exist in Baserow
id: The unique ID of the table, this is the same id as in the database_table_{id} tables.
name: The name of the table
database_field: contains all the fields of all the tables that exist in Baserow
id: The unique ID of the field.
name: The name of the field, this is unique per table.
table_id: A foreignkey that indicates in which table the field is.
database_linkrowfield: contains all the “Link to table” fields.
field_ptr_id: A foreignkey that indicates which field it is.
link_row_table_id: The related table where the field has a relationship with.
link_row_related_field_id: The related field in the related table (can be null if no reversed field is created)
link_row_relation_id: A unique ID shared between both this field and the related field. This is also the name of the database_relation_{id} table that holds the many to many relations.
I’m not sure if it’s possible in Tableau, but if you want to do a join between two tables, you need to find the “Link to table” field that holds the relationship. Figure out what that ID of the field is, extract the link_row_relation_id and link_row_table_id and use that information to do a join with the database_table_{link_row_table_Id} via database_relation_{link_row_relation_id}.
To further expand the original post, the challenge with using business intelligence software, e.g. Tableau, with Baserow is that the schema is non-descriptive; specifically, the names of the columns are stored separately. For a non-technical user or report writer, the expectation is that the table seen in Baserow is represented accordingly. So instead of
SELECT field_{id} FROM database_table_{id}
It would be something like
SELECT first_name FROM contact
Of course, this challenge is the result of having an extensible and flexible database, the very reason why we like the platform.
One solution that comes to mind is to use the data in database_table and database_field tables to dynamically create a view/virtual table for every table with the field name as actual column names. The complication would be that with every addition/deletion/modification of a column, the view would have to be recreated.
Has anyone else attempted to complement Baserow with a reporting/business intelligence tool?