Best Power BI Solution for Large Datasets

[jahlawat]

Hi,

We have Databricks to transform big data and then save the data table (csv) in Blob Storgae with tables onwards 20 million rows. Currently, team was connecting Power BI directly with Blob Storage which means import connection and huge under-performance. Once developed, then would deploy the solution to Power BI Service. I have come across few options to handle large datasets optimally with both pros and cons. Can someone please give their feedback on optimal solution with performance and cost considerations:

Other Assumptions:

1. Data refresh once a day only

2. Data to be analysed only for preceding 15 months

3. No of users' concurrency - upto 20 at a time

Solutions under consideration:

1. Azure Analysis Services: Using Visual Studio, connect to Blob storage to get the data, prep and build tabular models. Then deploy the models in AAS and consume through Power BI.
2. Power BI Premium: Use Power BI premium to connect directly to Blob storage and perform data prep, modelling and visualization directly in Power BI Premium. Key consideration is that PBP now supports upto 100gb datasets, up from 10gb recently.
3. Azure Databricks: As Databricks support direct connection, I wish to understand if it will be a viable option to connect directly with Power BI?

It will be my first major independently Power BI Solution, so thanks in advance for your feedback.

[jahlawat]

Hi Amit

Thanks for your reply. Though these links explain the overview and their differences but I have few further questions in relation to my challenge from these links:

1. Power BI Premium: It does allow upto 100GB datasets but this is only applicable to modelled, processed and compressed data (pbix files); hence this feature can only be helpful after deployment to Power BI Service.

For development in Power BI Desktop (on my local machine), Power BI Desktop would connect to Blob Storage and still would bring the data to my local machine for processing (blob storage only offers in-memory connection). Hence, using my machine's RAM which won't be able to handle processing larger datasets (in5 GBs). Please confirm if my understanding is incorrect here and if so, then how Power BI Premium would help with this local machie processing challange?

2. Azure Analysis Service: In this option, Visual Studio woud connect to Blob Storage to get data, model it and then deploy to AAS as cubes. When Power BI (even Pro) connects to AAS, it would be send the query to AAS cube to process, hence processing is done in AAS server now, instead of my machine. Also, AAS would offer scale out option to handle large size cubes. Hence, this option should not impact my local machine while using Power BI Desktop for processing the data.

I believe similar process would work for Azure Synapse Analytics (cloud DWH). If my assumption is correct, then can you please help me to select between AAS and ASA? 

Thanks

[amitchandak]

@jahlawat , please refer to this. It compares first 2

https://blog.coeo.com/power-bi-premium-vs-azure-analysis-services

https://ssbipolar.com/2018/12/09/choosing-between-power-bi-premium-and-azure-analysis-services/

You can consider Azure Synapse as thrid option, refer: https://www.be-terna.com/sites/5ebfc39e232fb906a3dfc34b/assets/5f3fb90d232fb95d6db09df0/power-bi-pro...