Best Practice of Google BigQuery in Power BI: how to set up the data model? Big table vs star schema

[zakkyang]

Hi all,

I got a question about the best practice to connect the bigquery using a direct query source in Power BI. Simply put, I can use a big query already joined with dimension tables and fact tables. Imagine this is a customer clicking table for some links. The following is the column names:

• date
• session_id
• customer_id
• customer_segment1
• customer_segment2
• customer_segment3
• action_flag1
• action_flag2
• action_flag3
• revenue

It is just a simplified table that including only one customer_segment column.

Ideally,  in Power BI the model can be star shape to make it simple for DAX. So if it is so, should be at least has three tables below.

Dimensional Tables:

Customer Table:

• customer_id
• customer_segment1
• customer_segment2
• customer_segment3

Date Table:

• date

Fact Table:

• session_id
• customer_id
• revenue
• customer_id
• session_id
• action_flag1
• action_flag2
• action_flag3

What will you consider to build the data model in Power BI?

Alternative 1: use bigquery as a direct query for the data source. Directly use the big table for analysis.

Alternative 2: use bigquery to customize for each table of the 3. And use Power BI model to join them as a star shape.

What do you think?

Thanks.

[v-kelly-msft · Accepted solution]

Hi @zakkyang ,

Below limitations need to be considered first:

1. If you are using a large data set such as a public data set offered by BigQuery, then it will take a lot of time to load it in Power BI.
2. Security is an issue as there is no two-factor authentication. You can just sign in and load your data in Power BI.  

Then check the guide below:

https://hevodata.com/learn/bigquery-to-power-bi/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!