How to merge result sets between two independent sources

[djouallah]

I have two sources, Actual And Budget, with a common dimension "state', I want to merge the results of the two Queries, is this possible in Malloy, otherwise, is there a different approach

see code reproducible code here

https://github.com/djouallah/malloy/blob/main/GalaxySchema.malloy

[lloydtabb]

Yes. You are thinking in the dimensional artifact world of traditional reporting. In this world, you build dimensional fact tables and then join them.

At Looker we invented what is often being called the "Modern Data Stack" where the data doesn't have to be transformed before it can be queried. This has two benefits

1. Queries can be run live against a transactional database so the information you get back is real time (because you are running SQL queries directly against the database)
2. You don't have to decide how you want to dimesionalize the data before you start asking questions.

Modeling data this way radically simplifies the data modeling process.

Basically, you start with a transactional table (say orders) and add all the interesting calculations you can think of against this table (revenue, gross margin, etc). Next you join in all the interesting data you can think of (product tables, user tables, product attribute tables). Because of the way Malloy works, no join can effect the base calculations so you are free to dimensionalize by anything.

You now have a real time view of your entire data world.

Joining in forcasts can be done by taking two queries and joining the results together.

To see the value of Malloy I would first start by working with a transactional table.

Malloy isn't great against a pre-dimensionalized star schema. Malloy wasn't designed to do this.

The Iowa Liquor data set is an interesting place to start.

https://github.com/looker-open-source/malloy/blob/main/samples/iowa/iowa.malloy

If you hop on our Slack channel, we'd be glad to schedule something. We're thinking when we are further along we might start Malloy Webinars. Is that something that would be interesting?

[lloydtabb]

For reference: https://das42.com/insights/the-new-data-stack-fivetran-snowflake-and-looker/

Slack Community: https://join.slack.com/t/malloy-community/shared_invite/zt-upi18gic-W2saeFu~VfaVM1~HIerJ7w

[djouallah]

Thanks

"Joining in forcasts can be done by taking two queries and joining the results together." how ?
as far as I can see, Malloy works only with 1 base Table, that's very problematic as in my daily use cases, it is not uncommon to work on 3 and more independent base Tables

[lloydtabb]

Queries can be joined and used in lots of ways in Malloy including join two or more query results together.

Any query can be used as a source. For example:

https://github.com/looker-open-source/malloy/blob/main/samples/names/names.malloy

Any query can be dynamically joined into a source in a single query for example..

https://github.com/lloydtabb/malloy_examples/blob/main/imdb/movie_complex.malloy#L11

Two query or more queries can be joined to gether in

query: merged is from(->query1) {
   join_one: q2 is from(->query2) on ...
   join_one: q3 is from(->query3) on ...
   project: *, q2.*, q3.*
}

[djouallah]

@lloydtabb thanks That make sense, wish it was auto generated by the system though and not manually defined, but thanks again