Using BigQuery repeated / nested fields in Power BI

1
votes

Problem: When connecting Power BI to a BigQuery table (using the native BQ connector) with repeated / nested fields, these fields do not appear in Power BI for use in report creation.

Example: Using a Public BigQuery data set called bigquery-public-data:samples, there is a table called github_nested which has repeated fields such as payload.pages.action (see below)

BigQuery repeated / nested fields

However, when connecting to this BigQuery table using Power BI I only get a handful of fields (see below)

Power BI Report canvas

As I understand, this is because the Power BI Big Query connector doesn't support repeated / nested or record fields.

Question: Is there any workaround to have all columns / fields in a BigQuery table (regardless of whether they are repeated / nested / records) available for use when building Power BI reports, specifically using a live connection over the native Power BI Big Query connector?

I do have write permissions in BigQuery so creating views on top of these tables with repeated / nested fields is possible if required as part of a solution.

One potential workaround might be to create a view in Big Query which un-nests repeated fields and then connect Power BI to said view. Open to any workaround suggestions.

Any help would be greatly appreciated.

2
google-bigquerypowerbi
It seems like this is not supported, There is a FR here but didn't gain much traction so far. – Guillem Xercavins
Thanks @GuillemXercavins. Yes, I've also read that the Power BI BigQuery connector doesn't support repeated / nested columns, however, I'm wondering if there are any workaround which might enable reporting off of these columns? For example, one idea might be to create a view in Big Query which un-nests repeated fields and then connect Power BI to said view. Open to any workaround suggestions. – Matt Laz
Yes, that might be the best option – Guillem Xercavins
Thanks @GuillemXercavins, however I'm not sure exactly how to do this or if this is indeed the best approach. This is why I have asked the questions, to see if anyone might have successfully done this before or if someone with more knowledge of BigQuery than myself might be able to assist. – Matt Laz

2 Answers

2
votes

Power BI does not have (as of 29/10/2018) a native Big Query connector, it just uses a generic ODBC connector. I would advise not to use it for production, current limitations are :

  • GCP billing accounts are not supported if your billing project is different than your dataset project
  • Google Service Accounts are not supported
  • Nested and repeated fields are not supported
  • Requests are not optimized (bad finops)
  • Dataset linked to Google Sheets through Big Query are not supported
  • Some data types are not correctly handled
  • Requests cannot be manually edited (using BQ Standard SQL for exemple)

Learn more in this article (i am the author): https://medium.com/@remy_david/which-bi-tool-for-big-query-d9eb838ff7ad

0
votes

Example schema of products-and-stocks table:

[
  {
    name: 'timestamp',
    type: 'TIMESTAMP'
  }, {
    name: 'data',
    type: 'RECORD',
    mode: "REPEATED",
    fields: [
      {
        name: 'itemId',
        type: 'STRING'
      }, {
        name: 'prodId',
        type: 'STRING'
      }, {
        name: 'name',
        type: 'STRING'
      }, {
        name: 'stockA',
        type: 'INTEGER'
      }, {
        name: 'stockB',
        type: 'INTEGER'
      }, {
        name: 'stockQ',
        type: 'INTEGER'
      }, {
        name: 'stockD',
        type: 'INTEGER'
      }, {
        name: 'stockE',
        type: 'INTEGER'
      }, {
        name: 'weight',
        type: 'INTEGER'
      }, {
        name: 'size',
        type: 'RECORD',
        fields: [
          {
            name: 'length',
            type: 'INTEGER'
          }, {
            name: 'width',
            type: 'INTEGER'
          }, {
            name: 'height',
            type: 'INTEGER'
          }
        ]
      }, {
        name: 'cnt',
        type: 'INTEGER'
      }, {
        name: 'cntInBox',
        type: 'INTEGER'
      }, {
        name: 'lifetime',
        type: 'INTEGER'
      }, {
        name: 'barcode',
        type: 'STRING',
        mode: 'REPEATED',
      }, {
        name: 'dateStockUpdate',
        type: 'TIMESTAMP'
      }, {
        name: 'label',
        type: 'STRING'
      }, {
        name: 'comment',
        type: 'STRING'
      }, {
        name: 'commentPack',
        type: 'STRING'
      }, {
        name: 'skuBox',
        type: 'INTEGER'
      }, {
        name: 'snRuleRegularExpression',
        type: 'STRING'
      }
    ]
  }
]

Create flattening query in the BigQuery and save it as a View.

SELECT 
  `timestamp`, 
  repeated.*, 
  repeated.size.length as `size_length`, 
  repeated.size.width as `size_width`, 
  repeated.size.height as `size_height`, 
  ARRAY_TO_STRING(barcode, ", ") as barcodesFlat 
FROM 
  `my-project-id.my-dataset.products-and-stocks`
CROSS JOIN UNNEST(`data`) as repeated

Import this view into Power BI.