BI Architecture with Synapse

[Swares]

Hi guys,

I've been reading about Azure Synapse but never had the opportunity yet to work with it, so I had a meeting with a Microsoft Partner representative that gave me a quick overview on Synapse and the pricing calculator. There some questions I would like to ask / share, and build on that, which might be useful for more IT folks out there.

1. My background is SQL Server, so I always worked on-premises, using SSAS cubes as a semantic layer where all my Measures were defined. In the new architecture it seems we are shifting all that to Power BI premium models, and SSAS will probably disappear. To keep that vision in mind, and being aware that some projects only require 50 -150 users, I don't see any use for Power BI Premium capacity and maybe Power BI Premium Per User might do the trick keeping costs in check. I'm not sure but it's probably an option.

2. I don't understand quite honestly what is the concept of DEV/QA vs Production in the new cloud models. For a production environment, a dedicated instance might be the way to go when you want to promote self-service BI in a organization with adequate performance, since it turns the Serverless model costs impossible to predict at the start of a project and more oriented for others purposes, probably a lower DWU instance in Pay as You Go, might be the best option for a DEV/QA environment.

I'm not sure what you guys are planning for DEV/QA and would like to hear your strategies to mitigate costs.

3. Azure Synapse seems to unlock the most scenarios and I would like to see what approaches you guys are planning for your architecture, high level overview of course.

Best regards

[#2]

@Swares I can answer on the PBI part, but someone else will have to chime in wrt Synapse...

So Power BI is a superset of SSAS, with all the advantages of a cloud SaaS to boot. Power BI datasets become your new semantic model best friend, with either Power BI reports or Excel as the front end for your users. Most customers are indeed going for Power BI Premium but depending on what you need, the per-user options (either Pro or Premium) might be enough. Pro means that users can build reports on your models, even hosted in a non-premium workspace, so if you don’t think there are premium features you’ll use then you might not need it.

hope that helps, thanks!

Will

[#3]

Hi @Swares, let me address questions 2 and 3.

Question 2:

Ideally, your QA/DEV/PROD environments should be functionally equal. That is the only way to be 100% sure that your code will run and behave the same way in all 3 environments. Please note that serverless and dedicated SQL pool have T-SQL syntax that is not 100% overlapping.

If you are using syntax that is the same for both dedicated and serverless SQL pool, while acknowledging risk mentioned above, your approach of using serverless SQL pool for development and dedicated SQL pool for production makes sense. For serverless SQL pool, you can estimate costs by examining queries, data queries process and take number of users or query executions into calculation. And to stay on the safe side, you can limit spendings - please check serverless SQL pool cost control article https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/data-processed.

Question 3:

Azure Synapse is analytics service that brings together data integration, enterprise data warehousing, and big data analytics. As such it is meant to be central place for all your analytics. You can check a number of architectures here Browse Azure Architectures - Azure Architecture Center | Microsoft Docs by filtering "Azure Synapse" or "serverless SQL pool" for example.

Please let me know if there are additional questions.