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'

Hi. I have sent the form definition in a private message to you and @hugh_sun.

Thank you.

1 Like

Hi, @hugh_sun :slightly_smiling_face: I hope you received the form definition I sent in August. Still not able to correctly output data using the ODK connector. The Submissions table returns all the fields in the entire form and all the child tables just return one field, the id field.
If i am not doing it right, I hope you could point me to the right direction. Thanks so much in advance.

Cheers,
Vinia

Hello Vinia, unfortunately I am no longer the manager for our team this year. A new manager will take my place and his name is Sam Levy. He will introduce himself here when he has the chance and will be your correspondent from now on. Thanks so much!

1 Like

Hi, @Vinia_Marciano My name is Sam and I am taking over as project manager for our team! We had a high turnover so most of our developers are new to ODK and we just started the process of onboarding. This is at the top of our priority list though! I will get back to you by the end of the week at the latest with an estimate of how long this will take us to address! Thank you so much for being so patient with us and we are looking forward to working with you more :slight_smile:

4 Likes

Thank you for your response, Hugh! Hope to still see you around. :slightly_smiling_face: Take care!

Cheers,
Vinia

1 Like

Hi, Sam! Warm welcome to you. Thank you for your update and for giving this a priority. Very much appreciated :slight_smile:
Good luck and hope to hear from you soon. The connector is really a great project (and ODK of course :slight_smile: ) and thanks so much to all of you for working on it.

All the best,
Vinia

1 Like

Hi! Wanted to check in on the expected timeline. It's a little bit of a hectic time as we are starting school and onboarding onto the project haha. I am estimating that it takes us about 3 weeks to finish a condensed onboarding and fully investigate this issue and add changes! If there are any time constraints you are under please let me know!

1 Like

Hi @samlevy Sam!
I hope you're doing well. Would just like to ask if you removed the ODK connector from the GDS platform? Is it just for the time being? (I hope! :slightly_smiling_face:)
Hope to hear from you soon! :slightly_smiling_face:

Cheers and take care
Vinia

@Vinia_Marciano Hi Vinia!
It is just for the time being! I made a mistake deploying a new version of the connector rather than updating the current one and we have to go through the process of resubmitting it to google :man_facepalming:t2:

This is the issue on out GitHub which has updates on the issue: https://github.com/UDub-Impact/OData-Connector/issues/27

And this is a temporary link to access the connector: https://datastudio.google.com/u/0/datasources/create?connectorId=AKfycbxMsmDvxShWuf1jctvN8B_4BKzrTXCkx1sAj2cplioeOpWgD7inx0jsnAEflDJSYuckTA

So so sorry for the confusion! We have also finished onboarding and are working on your issue! I will add more updates here as I get them!

2 Likes

Hi @samlevy Sam! Glad to hear that! I hope everybody's settled and well. Thank you for the updates and for the link. :slightly_smiling_face:

Good luck and cheers,
Vinia

1 Like

Hi @Vinia_Marciano Vinia!

Wanted to follow up again. We are still waiting for Google to republish the connector but we fixed the issue with the repeat tables along with some smaller bugs we discovered along the way. I am attaching a link to a temporary connector: https://datastudio.google.com/u/0/datasources/create?connectorId=AKfycbwvx0abeqBvCjqk_bhik1trRQ0--X_r58E2C6ncApjVLdU09bA3mTi26MixsFZXUwU with those changes (note that it will not be the one published).

I ran it using a submission I created using your form definition and it appeared to work for me but I wanted to give it to you so you could try it as well! Let me know if you run into any problems! Apologies it took longer than I anticipated!

2 Likes