How do I handle bigquery nested structures in Yellowfin?

Hi Steve,

Thanks for reaching out to support with your question.

I don't spend much time in BigQuery and haven't seen nested tables before, , maybe you can provide screenshots of setup and expected vs observed behavior here? I think this is apt documentation -

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

Looks like "unwrap" can be called via select query; usually functionality like this would be handled by the JDBC driver, and I would think you could achieve this functionality with a freehand query or an api call... if you don't see support for nested BigQuery tables in Source setup, substitution etc. or maybe it's not working when you try to implement? it sounds like that functionality could be lacking. I can get a bug or enhancement to devs depending on what it looks like. Logs and a timestamp of a report change / run could be helpful as well.

Would you be able to provide a compressed copy of your logs folder for analysis? You can upload to our FTP https://ftp.yellowfin.bi using the "send files" option, just reply here with a filename and any relevant timestamps

Thanks,
Eric

Hi Eric,

Maybe another way to ask the question is to ask about granularity.  When you unnest a nested table there is a granularity problem. It is best described with an example.

If there is a one to many relationship between 2 tables (like orders and order lines) and they have a mandatory join, how do I get the right answer from YF if I try to sum a column in the orders table? 

orders

order lines

Joined tables

The correct answer for sum(metric1) is 21

since there is a mandatory join and if I sum metric1 I will get 42 - which is wrong.

• select sum(metric1) from orders join orderlines on orders.order_no orderlines.order_no

Is there a way to get YF to give me the right answer?

one way to write the sql to get the right answer is:

• select sum(metrict1) from (select distinct order_no,metric1 from orders join orderlines on orders.order_no orderlines.order_no)

The joined results are also similar to a de-normalized table.  Does YF deal with metrics at different grains in a de-normalized table?

Steve

Hi Steve,

So my objective would be to get this replicated and submitted to devs if these calculations aren't working as expected, if that's what it sounds like is happening?

This sounds related exclusively to "unnest" function, or is there any other way I can get a "one to many" setup outside of Google BigQuery, maybe built with Audit Content or Tutorial Data? If not I'll need to make an access request to for this type of setup for testing.

I'm not an implementation / content creation specialist, but if calculations aren't working as expected when accessed via UNNEST / array type setup, I would probably try to work around by "offloading" this function, perhaps with a virtual table or function in BigQuery itself, basically find some way to give YF a real table / column to work with. That said this type of system configuration and content creation question may be better handled by a consultant, do you know whether your organization have any ES time with us available?

Thanks,

Eric

Hi Steve,

Just wanted to check in to see if you had a chance to review my reply at this time.

Thanks,

Eric

Hi Steve,

I'm going to go ahead and mark this question as Answered due to inactivity at this time. Feel welcome to reach out in the future.

Thanks,

Eric