Help on deciding the proper Architecture for BI Deployment

[viera00]

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.

Regards,

GV

[NehaVageriya]

Hi, 

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.

[viera00]

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?

Regards

GV

[v-yuezhe-msft]

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.

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-statistics

Thanks,
Lydia Zhang