Power BI with Transaction Data source in Google big Query

[brookbracewell3]

Evening all.

We have our data warehouse in big query, and have a massive transaction table with milions of items recorded every month. I don't think I want to use direct query due to the pricing structure with GCP, and I also have a requirement for a low grain (customer, date,time,product,discount) meaning I can't aggregate and have a single source for rolling up reporting.

While we have power BI premium, I think the data is just too bloody big.  Has anyone got suggestions on how to approach this?

#GoogleBigQuery #Transactions #Billion #Billions

[v-rongtiep-msft]

Hi @brookbracewell3 ,

I understand that you have a large and complex data warehouse in BigQuery and you want to use Power BI to query and visualize it. There are some best practices that can help you optimize your performance and cost when working with Power BI and BigQuery.

Some of the best practices are:

• Choose the right connector: Power BI offers a native connector for BigQuery that supports both Import and DirectQuery modes.
• Filter early: It’s always recommended to filter your data in the early stages of your query or as early as possible. This can help you reduce the amount of data that Power BI needs to process and display.
• Partition your tables: If possible, partition your tables in BigQuery by date or another column that can help you prune partitions when querying. This can help you improve the performance and reduce the cost of scanning large tables in BigQuery.
• Use a star schema: A star schema is a data model that consists of one or more fact tables and several dimension tables that are related through foreign keys. A star schema can help you simplify your data model and improve the performance of your queries in Power BI.

Best practices when working with Power Query - Power Query | Microsoft Learn

Solved: Best Practice of Google BigQuery in Power BI: how ... - Microsoft Fabric Community

BigQuery Best Practices | Google Cloud - Community (medium.com)

Best practices for using Power BI to query and visualize Azure Data Explorer data - Azure Data Explo...

How to Get Your Question Answered Quickly 

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brookbracewell3]

The solutions I am looing for (potentially) are around xmla end point partition loading, and the best tolling to do this. Maybe using combined data sets allowing my massive customer dimension to be loaded seperatetly to my massive fact table- I am already "best practised up" on the schema