A Fine Slice Of SQL Server
The thing that got me excited about the Power BI Enhanced Refresh API, and which inspired me to start this series of posts, was the fact that for the first time it gives you a way of cancelling Power BI dataset refreshes. In this last post in the series I’ll show you how you can add an Action to your Power Automate custom connector to cancel a refresh and how you can use it in a Power Automate Flow.
Click through to see how.
Comments closedErik Svensen needs to build a date:
Sometimes it’s the little things that can help you minimize the number of steps in your Power Queries.
Here is another example that might help you.
Let’s imagine your data contains two columns with year and the month name and you want to create a date column.
Read on for a clever function modification to generate a date from these two fields.
Comments closedJeffrey Wang wraps up a series on the EvaluateAndLog function:
This is the fourth and the last article on the EvaluateAndLog function. While DAX developers were excited about the prospect of printing the values of a portion of a DAX expression, many of the early adopters of the function were puzzled by a mysterious message they ran into from time to time: “EvaluateAndLog function is not executed due to optimization.” Another question on people’s mind is “While it’s great to be able to see the values of a sub-expression used in my calculation, is there a way for me to see the values of other expressions which are not needed to calculate the final result?” I will provide answers to both questions in this post.
Click through to understand why you might get this message and what it means.
Comments closedTomaz Kastrun has a script for us:
Determining and checking who has access to a particular workspace in your organisation can be time consuming task. You can always retrieve the list of workspaces and access the list of all users with PowerShell cmdlet Get-PowerBIWorkspace.
The results of this cmdlet gives you a great way to get to valuable information easy and fast. And the following script can do just that.
Click through for the script, which also excludes personal workspaces. Tomaz then provides a bonus script creating a graph of how users and workspaces connect.
Comments closedScenario: You use SSIS to load data for on-prem BI solution. As a last step of the ETL pipeline, you want to refresh a Power BI dataset. There’s quite a bit of misinformation on the Internet about how to do this, hence this blog.
Read on to see how you can do this, supposing that you’re hosting in a Premium-Per-User or Premium workspace.
Comments closedChris Webb wants to know the lore:
In the last post in this series I showed how to get the status of an individual Power BI dataset refresh. In this post I’ll show how to get the history of a dataset’s refreshes and save it to a file in OneDrive for Business – useful if you want to do some analysis on refresh performance or the causes of failures.
This is one of the most straightforward things to do with the Power BI Enhanced Refresh API , especially once you’ve already got a custom connector created in the way I’ve shown in the previous posts in this series. You just need to add a new Action to your custom connector, fill in the General section:
Click through to see what to do.
Comments closedChris Bailiss dives into some join problems in Power BI against Snowflake and SQL Server:
Power BI generates SQL that performs very poorly against Snowflake (and possibly other database platforms) when joining nullable columns, e.g. in the “Merge Queries” action in the Power BI query editor.
Specifically, the SQL generated prevents the use of the “hash join” physical join algorithm that has been one of the main algorithms used for decades by database engines when executing joins between large tables. This forces database engines to fall-back to using very inefficient physical joins (e.g. loop/cartesian join) that can perform orders of magnitude slower.
The reason why Power BI generates different SQL for non-nullable for nullable columns in a join is explained in the “Problem Part 2” section below.
Read the whole thing—and there’s a lot of good information in there.
Comments closedPaul Turley shapes the youth of data:
Power BI is a new tool and dimensional modeling is an old idea. One of the challenges is that, like other modern self-service analytics products on the market, Power BI doesn’t force self-service data jockeys to transform their data before reporting with it. If you want to import a big, wide spreadsheet full of numbers and create charts in a Power BI report, knock yourself out. But, the solution won’t scale and you will inevitably run into walls when you try to make future enhancements. Similar problems arise from importing many tables from different sources and transactional systems. Several tables all chained together with creative mashups and relationships present their own set of problems. The first iteration of such an effort is usually a valuable discovery method and learning experience. Great… treat it as such; take notes, make note of the good parts and then throw it away and start over! In Fredrick Brooks’ “The Mythical Man Month“, he cites that for most engineering projects, the first six attempts should be abandoned before the team will be prepared to start over and complete the work successfully. He was a chemical engineer before working for IBM; and hopefully, our methods in the data engineering business are more effective then his 6-to-1 rule. But, this makes the case the prototypes and proof-of-concept projects are a critical part of the learning path.
The tools don’t make the rules.
Unless you’re talking about the lambda architecture, in which case that’s kind of accurate. But we’re not talking about that here.
Comments closedMatt Allington has a new project:
Every now and then when I have a Power BI project of interest to me, I like to create a video of the end to end process of building a new report. This allows me to share some “warts and all” real-world examples of how to go about building a Power BI report. It gives me a chance to show some concepts (such as creating functions and extracting multiple pages from websites) but also to show that these things are seldom smooth and error free.
Click through for a video demonstration of website data extraction and combination in a Power BI report.
Comments closedGilbert Quevauvilliers doesn’t want to waste money:
I recently was assisting a customer with their Power BI licensing and what I found is that in some instances they were having licenses for Power BI Pro and Power BI Premium Per User.
By going through their licenses and assigning the correct license I was able to save the customer approximately 20% on their Power BI licensing costs per month. And over a year this adds up to quite a bit!
This does look to be more confusing than it really ought to be. I’m not sure of any reason why you would want to have Pro + Premium at the same time, so that state should be unrepresentable.
Comments closed