Power BI Tips: Google BigQuery in DirectQuery Mode

Power BI offers two different modes (Import and DirectQuery) to connect Google BigQuery. For more details about data connectivity modes, check the Cittabase’s blog. There are a few best practices to enhance the performance of the reports which are using DirectQuery mode. This blog will help you to enhance performance with the following steps.

Data type

Make sure that the data type same in both Power BI and BigQuery. It may differ in some cases. Power BI automatically detects data type based on first 1000 rows. If DateTime type column uses T separator, Power BI converts as text. However, it works fine with space separator.

Unused fields

Avoiding unused columns will make a good impact on the performance. For example, a table with 50 columns whereas only 5 columns required. Sending queries to a table with 5 columns is way better than a table with 50 columns.

Filters

There are a few things consider when it comes to filters.

• Apply default filter – Each query returns up to 1 million rows. It will through an error if it retrieves more than 1 million rows. Having some default filters might prevent from this error
• Single Select – Try to avoid multi-select. Power BI will send a query for every single click, it will increase load in the backend. At times, we might need multi-selection. Cases like this, Enable the apply button. Power BI won’t send any query until you hit the apply button. To enable this option, File -> Options and Setting -> Options -> Query Reduction
• Edit Interaction – Disable interaction between visual if they don’t want to be cross highlighted. At least disable interaction between slicers.

DAX functions

Having multiple DAX function in a measure will increase the refresh time especially Time intelligence functions. Creating many measures also increase the refresh time. So, try to reduce calculated measures which are using more or complex DAX functions.

Referential Integrity

Enabling Assume Referential Integrity selection in Edit relationship dialogue will improve the query efficiency. It will use INNER JOIN rather than OUTER JOIN. There are a few necessary requirements for Assume Referential Integrity,

• Values in the From column in the relationship is never NULL or BLANK.
• For each value in the From column should have a matching value in the To column

The above steps will give a better result. Limiting visuals also reduce the number of queries that will be sent to the backend. Please reach out Cittabase for more information.