Best way to overcome lack of joins

FAQ
#1

Hi.
I have started to evaluate metabase and one of the first things I saw is the lack of joins.
I read the article bout it.
What’s the best way around it?
Should I denormalize my data using ETL tool?
Doesn’t make any sense to do so. It will create huge amount of work.

My database is relational. some of the fields with foreign keys constraints and some just ID as a reference.

Doing ETL is a no go for me.
But I also think it’s basic to allow people to see country names and not memories the ids meaning.

Thanks

Filters & use of table aliases
#2

Most of the time you don’t need joins - in your example of Country, you’d define the Country ID as a foreign key with the country name displayed in its place.
The ideal scenario is a star schema, but if you don’t want to do any ETL, you can use views or SQL. I’ve bee creating questions from 5-10 tables using a mixture of SQL and views depending upon the complexity of the data.

#3

Thank you. but im not getting you.

you’d define the Country ID as a foreign key with the country name displayed in its place

Did you mean in my origin database or in metabase…/
Can you please explain?
Thanks

#4

In metabase. Take a look at the sample database, In the orders table, there’s a column called ProductID that relates to the product table.

#5

Hi @Avi!

To start with the more specific part of your question:

To add to what Andrew already said I think the GIF in this @metabase tweet demonstrates it quite well:

In general skimming all the GIF’s in the @metabase twitter feed with most of the recent ones marked #TipTuesday is highly recommended!

Then onto:

Good :slight_smile: … I guess you are referring to the So about those joins blog post?

Under the “In the meantime” heading there’s actually a link to that you can do all the native SQL queries that you must do (if you’re the one among your colleagues who already understand JOINs).

I think it’s also put quite well why they choose to hide JOINs so much with this further down:

…The easiest release valve for this is to just slap three joins in every query. Once you go down this road, well then, yes, Join Support is a must-have in any analytics tool you use. However, by making queries that an end user would naturally like to ask require joins, your data model is implicitly saying you don’t want end users to ask their own self-serve questions. We think this is quite limiting.

Finally a few pointers to related topics here: Is it possible to create a metric with join table? and Create questions from multiple tables (SQL Join?)

#6

@AndrewMBaines, @jornh . Thank you. I think thats good anough for me.
Thanks!

#7

Another possible solution: Multiple databases in one question without a data warehouse- YES YOU CAN!

You could just create views in a linked database. Then there’s no need to change your source database. Nor do you need any special rights on the source.

1 Like
#8

I am trying to understand how to handle multiple data sources with this above commentary

Hoping to migrate from Yellowfin BI where we have rather complex tables

Example - Marketing
I have 4 core data sources and 5 tables, I need to present in a table where the purpose of the the table is to show traffic, leads and cost per acquisition

Centralised date filters, measured by the Calendar Month

Pulling Marketing Reports for

  • SugarCRM for Enquiries (Cases) which then connect to contacts, linked to the below by grouped date on utm_source and date fields
  • SugarCRM for Converted opportunities connect to contacts via Contact ID
  • Google Analytics
  • Google Adwords from Stitchdata to a local Pregres
  • Facebook ads from Stitchdata to a local Pregres

What would be your recommended method?

#9

Anyone used Dremio?

I feel this could assist in getting everything in a more useable format

#10

Hi @dwaynet
There’s a request open on getting this driver (go and vote - click the :+1: on first post):

1 Like