Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.