BigQuery Connector - repeated records type not supported

[yan]

I reported this before, in September, to no avail although the bug got accepted:

http://community.powerbi.com/t5/Issues/BigQuery-connector-no-support-for-nested-data/idc-p/247825#M1...

All modern columnar-based datastore encourage to use the structure/array datatype to reduce bytes processed and increase performance.

Even Azure DW supports SQL.ARRAY 

As we change our data model accordingly, Power BI is likely not the right tool for us anymore. Looker, Tableau and Qlik all do support nested fields...

As per the original report, can someone give us the status of CRI 46724430, and provide at least a rough timeframe when this would be supported.

This would be required for Desktop and Service.

Thanks.  

[v-jiascu-msft]

Hi @yan,

This issue has been fixed. Please try the latest version January 2018.

Best Regards,

Dale

[yan]

@v-jiascu-msft Nothing got fixed. A RECORD datatype either displays as a JSON string, as illustrated in your screenshot, or is skipped.

On simple arrays (not repeated), PBI should parse the JSON and create the underlying columns as per the schema:

More worrying is that some/many columns are not rendered at all or converted to incorrect datatype.

See the repository column in your screenshot for example, it resolves incorrectly to null where BigQuery shows:

Actually, out of the 200+ fields in the github_nested table (see Schema in the table details), PBI only renders 8 with most having incorrect content.

Not close to be fixed to me.

Current version of the connector does not support selecting the billing project nor allow custom SQL like in other connectors. Tableau supports this since last year.

I understand this is still in beta but hopefully PBI will catch up quickly.

MSFT should really build upon the native API instead of a custom BigQuery ODBC driver, which is embedded in PBI and has restrictions on the  datatypes. This is also the recommendation of Google for new integrations.

PBI has the logic and functionality to parse multi-level JSON files or HTML already - so this connector should leverage that.

[mike_honey]

+1 from me - very disappointing. Power BI's string representation of the JSON obviously looses every attribute name - we just get a series of "v" and "f" nodes, as shown in the screenshot posted by @v-jiascu-msft above. That's a really poor effort that should not have passed any level of testing or QA.

[iliya9]

This is a huge problem if you want to extract nested data from a widely used data warehouse solution.

[JHob]

Has there been a resolution for this?  I am trying the SIMBA ODBC connector but am getting the same issue