Repeats with Google Data Studio Connector

Hi! Great job on the connector! Thank you so much!

Just a few questions..
When I try to load only the parent table 'Submissions', the list of fields shows all the fields in the form. That means that the connection loaded all the tables, including the child tables. If I use the score card to count the number of submissions (distinct count __id field), it gives me the correct value. However, I have no idea how to make the scorecard count the number of records in a certain repeat child table because it does not include the KEY or the id fields for the child tables.

Also, when I try to create another data source that connects to the child table, the list of fields only returns the --Submission-id field of the child table. My dashboard can show the number of households correctly but I don't know how it can calculate the total population because the other repeat tables mess up the count.

Assistance will be very much appreciated. Thank you so much!

Cheers,
Vi

Hello Vinia, I would be happy to help you with the issue, but unfortunately right now our developers are taking a rest (we are a student organized club in the University of Washington, and we take a break during the summer). I am sorry if this causes any inconvenience on your part!!!
If you can describe your issues with some more details on the GitHub repo issues here (maybe include some images as well), it would be great! We will come back to you in the fall!

1 Like

Thanks for chiming in, @hugh_sun! Let's keep the conversation going here for now and we can file issues as we identify code changes that would be helpful.

When you load the Submissions table, you should only get the root table fields. Did you access the connector by clicking on the link @hugh_sun shared above? When you configured each data set, did you see a dropdown with each available table? See the screenshot here for what you should see. I want to make sure you're using the latest version.

It sounds like you have a parent household table and a child table that represents people. You'd like to get a total count of all people across all households, is that right? Have you tried to do a count (NOT count distinct) on any field in the child table (e.g. name)? My understanding is that as long as each row is unique that should work.

For working with repeats, you'll probably also be interested in looking into blended data (Google reference, useful article).

I think you can do what you need with what the connector does today. However, it would be useful to have the familiar KEY available through the connector. I've filed an issue at https://github.com/UDub-Impact/OData-Connector/issues/21.

1 Like

Thank you for your reply. No problem :slight_smile: Enjoy your break!

I will also post some information in this thread for easy tracking.

Thanks and cheers,
Vinia

1 Like

Hi Hélène,

Thank you for your response. Sharing with you some more info and screenshots.
First, here's the connector from GDS:
image

And here are some info about my case.. I have a form with a number of child tables. But for the purpose of illustrating my issue, I will use only 3 tables from my form. My form has 3 levels => (1) Structures, (2) Households, (3) Members
image
In the image above, you will see that i made separate connections and did not use the Blend Data option. I will explain why later.
My parent table, Submissions, contain information about the structures. And to each structure, one or more households may be related or associated. And to each household, one or more members may be related. The household is a repeat under the structure (Submissions) and the member table is repeat under household. When I connect to the the Submissions table, here's what I get:
image

No problem. Correct number or count of rows/submissions or structures. But when I view the fields, here's what I get:
image

677 is actually the number of fields in the entire form, including the child tables. Btw, my form has about 30 child tables. And I have no problem processing and visualizing them in Qlik Sense. Going back, it seems that the parent table or data source is referencing a flat table. But it doesn't include the data. All fields of the form but data from the Submissions/parent table only. And when I create a report based on that first table, with Count and Count Distinct, here's what I get. The second column is actually a field in the Members table. I just tried to check if the table will return some values. But it did not. So maybe the table did not import the data from the child tables, only the fields. I made the wrong assumption in my first post. I thought the connection to the Submissions table also included the data from the child tables.

The first column is a field/dimension in the Submissions table and the second column is a field in the members child table. I am not sure what is being calculated in the last column when I tried to use the Count Distinct aggregation.
Moving on to the second table, the household table:
image

Again, the count of rows seem correct. And again, this is a repeat under Submissions.
However, it only returns one field which is the Submissions ID for the repeat. It doesn't have the PARENT_KEY that can be used to link it to the parent table. Just this single field. While all the other fields are in the first data source.
image
And when I try to create a report based on this table, this is what I get:

The same goes with my Members table. The data source gives the correct count during configuration. But when I create a report, it returns 0. Also with only one dimension. Only the Submission ID field in the Members child table.

I hope I was able to explain the issue clearly. If you need more information, kindly let me know.

Thanks and cheers,
Vinia

Thanks so much for that detailed writeup, @Vinia_Marciano.

This really makes it sound like there's something about your schema that the connector is unable to process correctly. We've done some amount of verification with a single repeat but none with nested or multiple repeats. Would it be possible for you to share your form definition for troubleshooting?

1 Like

Yes. but can I share it privately?
Thank you.

Yes, you can send it to me and @hugh_sun in a private message. I can’t make any promise on timeline but we will try to look into it.

2 posts were split to a new topic: Validate erorr: Cannot handle function 'selected'