Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Help on deciding the proper Architecture for BI Deployment

Hello everyone


I'm in need on some experience here.


I'm evaluating different architectures for a new BI deployment using Microsoft Stack. I will build a big datawarehouse ,I will create reports in PowerBI desktop and publsih them in PowerBI service in order to build dashboards.


Just to give some sizing idea, my principal facts table has 100MM rows, and 15 dimensions (start schema).


Im analyzing two scenarios:


1) Onpremise

1- SQL Server wher I will create the DW

2- Tabular SQL Server Analysis Services with the semantic model (import)

3- PowerBI Desktop accesing direct query to Tabular Model for creating the reports

4- PowerBI Gateway for onpremise access

5- PowerBI Service Dashboards and Reports (reports created with the powerbi desktop) 


2) Azure

1- Azure SQL Datawarehouse

2- PowerBI Desktop accessing to azure SQL DW through Direct Query and create semantic model.

3- PowerBI Service With Reports from the PowerBI desktop.


I know Scenario 1 (onpremise) work on heavy load. I've tested it before and have great experience. The second scenario seems simpler (Azure DW - PowerBI) It is also simpler for users to create their own reports, but I'm little bit concerned about perfomance. I've read everywhere that Azure SQL DW is super fast,it uses columns store, bla bla...but It is not SSAS, so every time it needs a number, it will send a query. 


In Scenario 2, I'm not planning to incude the new Azure Analysis Service, because it is in preview, and because it is expensive. 


I'll appreciate your suggestings and if you had deployed something similar in real life, would love to hear about it.





German Viera

Re: Help on deciding the proper Architecture for BI Deployment

Hi @viera00

I haven’t deployed the above model in my environment. However, I prefer to option 2 which is easier to manage, and to make performance better after connecting to Azure SQL Data warehouse in Power BI Desktop through DirectQuey mode, I would recommend you review the following articles to improve your Azure SQL Data warehouse query.

Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Re: Help on deciding the proper Architecture for BI Deployment


Just wanting to know if you were able to make a decision on this and how you proceeded with this as I am in similar space and not able to find much info on your scenario 1. 


Please give more details on this one.

Helper II
Helper II

Re: Help on deciding the proper Architecture for BI Deployment

Hello Neha


I've decided to go with Scenario 1. Much more performant with the data I used.


It requires SQL Server and SSAS onpremise. I user PowerBI just as a visualization tool.


What details would you like to know/understand?







German Viera

Helpful resources


Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors