Put a smile on your face and provide your DBA with all the necessary info about SQL queries generated by Power BI

Image for post
Image for post
https://www.pexels.com/photo/cute-dog-in-bright-shirt-on-light-background-5733428/

Once upon a time, your old scary DBA walked into your office with a red-colored face and asked you furiously: “What the hell you’ve done with your Power BI report?! It killed all of our workloads!!!”

You knew why he was angry, even before he started screaming at you. It’s because of “that” report which uses DirectQuery because your users wanted “near real-time” data…

I’ve already written why you should reconsider using DirectQuery, and in which scenarios (maybe) it makes sense to go that path.

However, it appeared that your DBA is not that “bad” as you might think. He offered to help you in optimizing your most exhaustive SQL queries, and he just wants you to send him those…


Have you ever wondered what makes Power BI so fast and powerful when it comes to performance? Learn on a real-life example about data model optimization and general rules for reducing data model

Image for post
Image for post
https://www.pexels.com/photo/aperture-blur-downtown-focus-339379/

Have you ever wondered what makes Power BI so fast and powerful when it comes to performance? So powerful, that it performs complex calculations over millions of rows in a blink of an eye.

In this article, we will dig deep to discover what is “under the hood” of Power BI, how your data is being stored, compressed, queried, and finally, brought back to your report. …


Learn how to handle one of the most common business requests in multiple different ways!

Image for post
Image for post
Image Source

What are my best-selling products? Which customers spend the most money on my services? Which stores sold the most units of our luxury brand? Answering these, and many more similar questions are one of the most common requests when creating Power BI reports.

In order to enable proper business decisions based on answers to the questions specified above, we need to identify the respective values and sort them accordingly.

DAX offers two different functions for dealing with sorting — RANKX and TOPN. As you can intuitively conclude, the TOPN function can help you identify, let’s say, the top 5 most selling products, or top 10 customers who bought the most items last year. …


Check how the latest improvements to Synapse Serverless SQL pool can help you create one of the most popular Power BI transformations in a more optimal way!

Image for post
Image for post
https://unsplash.com/photos/r7zjJ63kAPU

Despite my planning not to write any “serious” blog posts till the end of the year (I just wanted to create some entertaining stuff), a recent announcement from Microsoft that Serverless SQL Pool within Synapse now supports a whole new set of T-SQL functions forced me to change my initial plans and play around with the latest stuff.

Honestly, I was curious to test how PIVOT works in the synergy between Power BI and Synapse, as this is one of the most used transformations in Power BI.

Before you proceed reading, maybe you would like to check the article where I explained what is Synapse Analytics, and what kind of benefits it brings to a Power BI table. …


Quick intro to the latest Microsoft data governance solution, and how it integrates with Power BI

Image for post
Image for post
https://unsplash.com/photos/VGOiY1gZZYg

If you’ve carefully followed the latest data news, you should have noticed that Microsoft made two important announcements a few weeks ago: the first, Azure Synapse Analytics became GA, while the other also caused a lot of interest: by introducing Azure Purview as the evolution of Azure Data Catalog, Microsoft proclaimed that this new solution will serve as a unified data governance and data management platform.

At the moment of writing, Purview is still in preview, so we can expect that specific features will evolve over time, and that some new features will find their place under the Purview umbrella.

As someone who spends most of my time working with Power BI, I was curious to explore how Purview looks from the perspective of a Power BI professional. …


Check out this handy trick to apply a background image automatically to all your report pages!

Image for post
Image for post
Image by author

Images are one of the most powerful tools to enhance your Power BI reports and present the data story in a more meaningful and “colorful” way (please don’t take this “colorful” meaning literally). And, when it comes to working with images, Power BI offers you a whole range of capabilities — some of them I’ve examined in this article.

Upload background image

Adding an image as a background for your report page is fairly simple. I’ve downloaded this photo from Unsplash and, in literally a few clicks, I would set this photo as my report page background:


There are multiple ways to keep your costs under control in the Serverless SQL pool. You can even use good old SSMS to measure the query metrics

Image for post
Image for post
https://www.pexels.com/photo/person-holding-black-ceramic-pig-coin-bank-3943723/

If you follow my blog series on different levels of integration between Power BI and Azure Synapse Analytics, you should have already understood the key components and concepts behind Synapse Analytics. You could have also noticed that Synapse brings a whole bunch of possibilities to handle various analytic workloads in one single place.

However, it’s not the current state of the product that I admire, but its general direction and rapid development in multiple different areas.

One of the features that were “shady”, to say at least, was the cost management in Synapse Analytics Serverless pool. Up until recently, it was hard to measure the costs of a specific workload, let alone a single query itself! There was generic information that the Serverless SQL pool will cost you starting from 5$/TB of processed data, but there was no deeper insight or ways to measure and manage the costs. …


Are you jealous because Power BI Service gives you a nice overview of Usage metrics? No need, because you can get similar thing on your Report Server too!

Image for post
Image for post
Photo by Miguel Amutio on Unsplash

Building Power BI reports is a cool thing, I won’t argue about it! And I am pretty sure that each time we build a report, we firmly believe that this report is a “real deal” and that it will be consumed by a large majority of our users.

However, reality is often completely opposite! There are many reports that are being regularly run in the beginning, but after a while, they are starting to become a “dead wood”. …


In the final part of the series, learn how to leverage SQL API for Cosmos DB and Serverless SQL pool within Synapse Analytics to seamlessly integrate non-relational data from Cosmos DB in Power BI reports!

Image for post
Image for post
https://unsplash.com/photos/Q1p7bh3SHj8

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.


How much would you pay if you use Power BI in a combo with Synapse Analytics? I’ve put Power BI & Synapse Analytics Serverless SQL pool to an ultimate benchmarking test and here are the results!

Image for post
Image for post
Image by author

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.

About

Nikola Ilic

Data Mozart — Don’t let data make noise, create music from it | data-mozart.com | https://www.linkedin.com/in/nikola-ilic-data-mozart/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store