Database based on EAV model

Will i be able to use metabase for generating reports since my application(developed using magento 1.9.2 CE) database is based on EAV model . The data is scattered in multiple tables and to fetch one single report multiple joins needs to used in mysql query

Hmm, EAV ... new to me ... I had to look that up (found this in context of Magento): How data migration works | Adobe Commerce

This diagram really gives me :exploding_head: if we are talking reporting: (but how horrible of course depends on the size and complexity of your EAV model)

Short answer: No not if your requirement is static reports out of the box

Longer answer: It depends - as there are various ways around it, depending on what you prioritize. Here are a few:

  • You can add views directly on your database to pre-join.
  • Use native SQL JOIN queries in Metabase (and let others build their questions on those)
  • If some user navigation across joins can be tolerated you can exploit FK/PK links
  • Do ETL from your EAV modeled data (OLTP) to a database set up for Analytics (OLAP). I actually happen to have looked a bit into an upcoming task myself with this towards a Magento 2 system. Here our plan is to do ETL with a tool called StreamSets which I successfully plugged into Magento’s REST API. It seemed like it mostly abstracted away some custom attributes in the model I have to pull. It looks like something similar is the case for EAV (at least in Magento 2): Redirecting…

Longer term (but right now it might be far out?) it sounds like this feature is what you are asking for:

What’s on the short term radar (marked for possible inclusion in v0.30) and would be IMO interesting if the need isn’t purely static reports but one click navigation can be tolerated is:

There! You asked a pretty broad question ... and you got a long winded answer/rant :smile: