A Fine Slice Of SQL Server
Naiden Borimechkov builds a moving average:
We begin by populating a custom dataset containing real historical time series data for the top 1000 stocks in the US stock market for the last 2 years. We need the closing prices as well as the percentage changes for each of the stocks.
After creating a basic line chart, Naiden incorporates some DAX to build up the rolling averages of results.
Comments closedPrathy Kamasami shares a use case for notebooks in Microsoft Fabric:
If you are a consultant like me, you know how hard it can be to access Power BI Admin API or Service Principal. Sometimes, you need to see all the workspaces you have permission for and what’s inside them. Well, I found with MS Fabric, we can use notebooks and achieve it with a few steps:
Read on for an enumeration of those four steps, as well as detailed instructions for each.
Comments closedDAX is a powerful tool in the hands of a Power BI developer. Using simple DAX formulas, you can not only compute interesting metrics but also customize the behavior of Power BI visuals. In this article, we use DAX to control the range of charts to obtain more coherent visualizations.
Read on to see how.
Comments closedChris Webb shares some thoughts:
Rather than blog about the tool itself – there’s no point repeating Nikola’s post – I thought it would be good to answer a question someone asked me later that day about Tabular Editor and which I’m definitely going to be asked about DAX Optimizer, namely:
This looks great, but it’s expensive and it’s hard for me to get sign-off to use third-party tools like this. Why doesn’t Microsoft give me something like this for free?
Chris shares his personal opinions on the matter. My opinion on it, as someone who has worked with Microsoft products for a long time and never for Microsoft, is that Microsoft needs to play a balancing act. They build products and tools with the intention of third parties extending them, whether by opening up APIs or creating an explicit extensions marketplace (like we see in Azure Data Studio and Visual Studio Code). If they go and take the best bits of these third party products, then that third party marketplace dries up quickly. On the other side of the coin, depending on third parties can’t always cut it. For example, Azure Data Studio used to have an awful execution plan viewer and the answer was “use SentryOne Plan Explorer instead.” That wasn’t a great solution either for Azure Data Studio (and today, I don’t know if the extension is even still around), so the pushback was firm: a good execution plan reader needs to be a core part of any first-class SQL Server developer tool from Microsoft.
Chris has plenty to say on the topic as well.
Comments closedGilbert Quevauvilliers finishes up a series:
In the final part of my series, I am going to be creating the Power BI DirectLake dataset and report from my tables that I had previously loaded into the lakehouse.
In this series I am going to show you all the steps I did to have the successful outcome I had with my client where I created the dataset (measures and fields) and the Power BI report.
Click through for links to the prior posts, as well as a walkthrough on creating a DirectLake asset in Power BI.
Comments closedKristyna Hughes looks at column encodings:
Power BI encoding is a powerful optimizing option that is often overlooked because it’s not visible in neither the Power BI Desktop tool nor in Power BI Service. Natively, the VertiPaq engine in Power BI investigates all columns in the data model and determines how it can store that data most efficiently. To achieve maximum compression, the VertiPaq engine starts by encoding each column which determines the method of compression applied to that column. There are a couple types of encoding – value and hash.
Read on to learn the difference, as well as how to push your columns to use a specific type of encoding.
Comments closedA few months ago, while scrolling through posts on social media, one of them immediately grabbed my attention! It was about a new tool, called DAX Optimizer, which promised to identify and remove performance bottlenecks in your DAX formulas. For all of us dealing with optimizing Power BI reports on a day-to-day basis, that was a huge promise (and one I was impatiently waiting to see in action).
One important note is that this is not a free tool, as Nikola mentions. Read on for more thoughts about how it works, what it picks up, and whether it’s a good fit for your environment given the price.
Comments closedReza Rad looks at a new feature:
Have you ever considered exporting your entire Power Query Editor project as a single object? Have you thought about what benefits this would bring for you? Things such as version control and team development can be on the horizon, bringing the ability to migrate between tools and services easily. Fortunately, such functionality exists, called the Power Query Template. In this article and video, I will explain what this is, how it works, and the importance of such a feature.
Reza is, on the whole, quite pleased with it.
Comments closedKurt Buhler and Stepan Resl give you a card:
When a user arrives at your report, they should be able to answer their most important questions in a few seconds. To do this, we typically put the most critical information in the top-left of the report (where we often look first). This information should provide a high-level overview, whereas additional details should be placed at the bottom of the report, behind interactions, or on later pages.
An effective and popular way to call attention to important numbers in Power BI is by using cards and KPI core visuals.
Read on for several examples and a breakdown of how they work best.
Comments closedGerhard Brueckl makes the call:
Microsoft Fabric has a lot of different components which usually work very well together. However, even though Power BI is a fundamental part of Fabric, there is not really a tight integration between Data Engineering components and Power BI. In this blog post I will show you an easy and reusable way to query the Power BI REST API via Fabric SQL in a very straight forward way. The extracted data can then be stored in the data lake e.g. to create a history of your dataset refreshes, the state of your workspaces or any other information that is provided by the REST API.
Click through for a list of operations, followed by the code you’ll need to pull this off.
Comments closed