How to leverage memsql for reports that combining multiple tables

We using memsql columnar-store database.
We create pipeline for each “even-type” in our backend architecture and we create a dedicated table for it (we call it “source table”). so each pipeline writes to one table on mem

We have different business reports for customers that consist data from multiple source tables (not necessary tables that we can join because some fields can be existed on one table and some not)

So the only way to overcome this is to create huge and complicated queries which consists UNION functions making things much hard to maintenance and understand

What would be best practice to maintain and keep such architecture which will allow us to create multiple reports on multiple source tables and to reduce query complexity.

  1. one way we thought is doing dedicated views per report. but we are not sure if we shall go that way. we need to create external ETA’s that will add more and more data to that view (a daily process)
    what’s your thoughts regarding this? is that the way?

  2. second way just to manipulate tremendous queries with UNION functions
    what we do today. we dont like this way because it run complexity on run-time on query time

I want to mention that those reports dont need to be updated on real-time. we can have buffer (hours) from one update to another


Hi Idan,

You’re on the right track , 1 pipeline to 1 source table (or raw table).
Querying a raw table is not the best approach though because it will grow fast, your queries will slow down and it does not provide you scalability.
I would use stored procedures to grab that data from the raw tables and transform it into different tables. Ideally, fact and dimension tables are better suited for reporting purpose.



Hi @Franck thanks for your direction.
Fact and dimension tables are terms in mem?

beside of that - can you elabore or ref me to some example? It shooting here in the air trying to find our way:)