https://linen.dev logo
Join Slack
Powered by
I've built a model in Meltano to model questions/r...
# announcements
a
I've built a model in Meltano to model questions/responses from Survey Monkey surveys. This works fine, and I can query the data and make some simple visualisations. I'd now like to create a bar chart which shows the number of times each option in each question has been selected. I have an aggregate for the count of responses (using response_id), but that doesn't let me break it down by question. So I created another aggregate which is a count on question_id. However, when I select both of these aggregates in the UI it seems to create an extra bar line for each aggregate. I think I want single lines but just for them to be grouped behind the scenes. Is this possible?
y
I am sorry if I did not understand your data, but if you have: questions(q_id, ... ... ) responses(r_id, q_id, ...) user_responses(.. option selected for each question ..) You can just join the three and group by the question (name) and response (name) and then count the user responses for each one
a
Thanks @yannis_roussos , I'll try that when I'm back at my computer!
y
It can be done for sure, it just depends on the tables you generate with your dbt Transformations. Just remember that you can join multiple tables in a design for a Meltano Table. If you get stuck, ping me and we can check it together. You can use as an example the Transforms and Meltano Models we have built for analyzing Gitlab Data: Gitlab Transforms: https://gitlab.com/meltano/dbt-tap-gitlab Gitlab Models: https://gitlab.com/meltano/model-gitlab You can check for example in the Topic how we join Issues with Projects and Milestones to do something similar for counting Stats per Issue or per Project or per Milestone: https://gitlab.com/meltano/model-gitlab/blob/master/models/gitlab.topic.m5o https://gitlab.com/meltano/dbt-tap-gitlab/
The other way around, is to generate during the Transform step a pre-joined table with everything required in there and just use that in your Meltano Model. A pretty nice example is how we gather everything required to generate reports for user activity in gitlab in one table (as there is the need to do some SQL kung fu in there): https://gitlab.com/meltano/dbt-tap-gitlab/blob/master/models/transform/gitlab_stats_per_user.sql and then just build a Meltano Table on top of it: https://gitlab.com/meltano/model-gitlab/blob/master/models/gitlab_stats_per_user.table.m5o
The idea in this second example is that you just build a denormalized table with all the dimensions and metrics that you want and then let the user decide what they want to select to group by and aggregate using Meltano Analyze. You can also check the result in our puplic instance: https://meltano.meltanodata.com/analyze/model-gitlab/gitlab/gitlab_user_stats_per_milestone
a
Thanks @yannis_roussos! I have joined the three tables and can view the count of responses to each question
I guess what I'm really asking is more of a visualisation question. Basically, what I'd ideally like is a grouped bar chart. Very roughly, something like this
What I have now looks like this
Note that in the above screenshot, the first two bars at the top correspond to the same question. They are just different answers (one person chose 'Name 1' and another person chose 'Name 3'). For readability, it would be awesome to have these bars grouped into bars with a single question title ('At first glance...') on the left then two bars next to it - one for name 1, and one for name 3
Let me know if this doesn't make sense or is/isn't possible
I guess I'd like it to look similar to https://meltano.meltanodata.com/analyze/model-gitlab/gitlab/gitlab_user_stats_per_milestone when more than one aggregate is selected, but in my case it's really only one aggregate (the number of responses).
y
Oh I get it now! Yes, as we don't support this pivoting operation in the UI right now, this needs a new transformed table that will take the aggregates for each answer and add them as a column in the new table with the question as the key (main group by attribute). As you wrote, that is very similar to what I have done for gathering the various aggregates for users in the Gitlab transformations.
a
Ah yes that makes sense, thanks again @yannis_roussos !
I guess it will be harder for me to do something similar in this case, as I don't know what the choices for each question can be ahead of time (there could be many possible options) so unless I can dynamically generate the aggregates then I think this might not be possible
y
Yes you are right and I don't think that the crosstab function of Postgres can help in this case https://www.postgresql.org/docs/9.6/tablefunc.html
We have to add support for a pivot at some point in Meltano. This operation is so much easier in normal code than in SQL (you just keep a couple of attributes and then switch the others). But we have so many other features that we have to add first, so this could take a while
a
No worries, this isn't urgent! Out of interest, do you have a roadmap somewhere listing the features you plan to develop?
y
At the moment we iterate very quickly with 1 week milestones, so there is no roadmap set in stone, just our wider mission/vision and the persona we are focusing on first: https://www.meltano.com/docs/roadmap.html For what's happening during the few next releases, you can check our issues list and filter by milestone: https://gitlab.com/meltano/meltano/issues Most things set for a Friday's milestone are released on next Monday's Release or the release after that.
2 Views
Open in Slack
PreviousNext
Powered by