By introducing Azure Synapse Analytics in late 2019, Microsoft created a whole new perspective when it comes to data treatment. Some core concepts, such as traditional data warehousing, came under more scrutiny, while various fresh approaches started to pop up after data nerds became aware of the new capabilities that Synapse brought to the table.
Not that Synapse made a strong impact on data ingestion, transformation, and storage options only — it also offered a whole new set of possibilities for data serving and visualization!
Therefore, in this series of blog posts, I will try to explore how Power BI works in synergy with the new platform. What options we, Power BI developers, have when working with Synapse? In which data analytics scenarios, Synapse will play on the edge, helping you to achieve the (im)possible? When would you want to take advantage of the innovative solutions within Synapse, and when would you be better sticking with more conventional approaches? What are the best practices when using Power BI — Synapse combo, and which parameters should you evaluate before making a final decision on which path to take.
Once we’re done, I believe that you should get a better understanding of the “pros” and “cons” for each of the available options when it comes to integration between Power BI and Synapse.
Special thanks to Jovan Popovic from Microsoft, who helped me understand key concepts and architecture of Synapse
After we laid a solid theoretical background and got more familiar with Azure Synapse Analytics in the previous part of the series, I believe it’s the right moment for Power BI to jump on the stage.
Synapse for Power BI Developers
As already stressed in the previous article, Microsoft made Synapse Analytics a one-stop-shop for all your data-related tasks. That being said, one would expect tight integration between the Synapse and Power BI, as Power BI is (rightly) considered as an ultimate tool to articulate data and serve your users with various business insights.
And, Synapse in synergy with Power BI, and vice versa, live to that high expectations!
Before we dive deep into each of the different layers of integration between these two, let me briefly walk you through the scenarios when you, as a Power BI developer, might want to consider using Synapse:
- DirectQuery — I’ve already written about DirectQuery in detail, explaining what it is, and in which scenarios you should consider using DQ. As you may recall from that article, DirectQuery is by no means an optimal choice when it comes to report performance, as each visual will generate at least one query to a data source. However, in specific circumstances, you will have no other option than to use DirectQuery, and Synapse can be your friend in those situations! We will examine later on a real example of how this works in reality…
- Security — If you think of Power BI authentication through Azure Active Directory (AAD), which is also a recommended standard for Synapse, you can easily imagine a scenario where security for Power BI solutions is being handled on a data-tier level within Synapse. This is especially important for Power BI solutions that rely on the DirectQuery option, as you may still want to implement Row-Level-Security when using Import mode
- Data shaping — Power BI has its own set of tools for performing data shaping — most noticeable — Power Query, Dataflows, and DAX calculated columns and tables. However, none of these can be compared to enterprise-scale capabilities for data transformation and preparation within Synapse. For starters, Synapse comes with Azure Data Factory, as probably the most powerful tool for data orchestration tasks. That means that you can perform whole data shaping on the source side, so your Power BI reports can benefit from using ready-made solutions
- Single source of truth — You’ve probably heard this sentence many times if you’re working with data. Essentially, that’s the ultimate goal we all aim to achieve. Since Synapse represents, from one of its perspectives, the legitimate successor of Azure SQL Data Warehouse, you can take advantage of this, and use data from Synapse for your Power BI reports being confident that the data comes from the centralized and approved location
#azure-synapse-analytics #azure #power-bi #data-science #data
