CODEWITHSUNDEEP
sqlpowerbiazure-synapse
elfisher
elfisher

Reputation: 1230

Using PowerBI to visualize large amounts of data on a SQL Data Warehouse

I have a SQL DW which is about 30 GB. I want to use PowerBI to visualize this data, but I noticed PowerBI desktop only supports file size up to 250MB. What is the best way to connect to PowerBI to visualize this data?

Upvotes: 0

Views: 508

Answers (2)

Boris Lipschitz
Boris Lipschitz

Reputation: 9516

You could also create a query (power query - M) that retrieves only the required data level (ie groups, joins, filters, etc). If done right the queries are translated to tsql and only limited amount of data is downloaded into power bi designer

Upvotes: 0

Matt Usher
Matt Usher

Reputation: 1325

You have a couple of choices depending on your use case:

  1. Direct query of the source data
  2. View based aggregations of the source data

Direct Query

For smaller datasets (think in the thousands of rows), you can simply connect PowerBI directly to Azure SQL Data Warehouse and use the table view to pull in the data as necessary.

View Based Aggregations

For larger datasets (think millions, billions, even trillions of rows) you're better served by running the aggregations within SQL Data Warehouse. This can take the shape of view that is creating the aggregations (think sales by hour instead of every individual sale) or you can create a permanent table at data loading time through a CTAS operation that contains the aggregations your users commonly query against. This latter CTAS operation model is a simple select with filter operation for the user (say Aggregated Sales greater than today - 90 days). Once the view or reporting table is created, you can simply connect to PowerBI as you normally would.

The PowerBI team has a blog post - Exploring Azure SQL Data Warehouse with PowerBI - that covers this as well.

Upvotes: 3

Related Questions