A Fine Slice Of SQL Server
Gilbert Quevauvilliers wants to see what that error was again:
I have been recently using the Power BI PowerShell modules to get some information from the Power BI APIs to store the data.
I ran into an issue where something was not working and I found a great cmdlet which allowed me to view the Power BI errors that I had been querying via PowerShell.
Read on for more details and a demonstration.
Comments closedRita Fainshtein looks at a stacked bar graph:
Let’s look at what motivated our choice and whether it is indeed a graph that will convey accurate messages to those who read it.
Here are some reasons why this type of visualization is preferred:
1. The graph on the right is one of the “recommended” graphs provided by tools (both Excel and Power bi).
2. It seems logical: there is a height comparison between case managers, clear separation between client types and the graph looks colorful and appealing.
But behind this, there are some major problems with the visual. Read on to learn what those are and for one alternative visual which can be better.
Comments closedImke Feldmann executes a Power BI REST endpoint call from Azure SQL Database:
For Azure SQL Databases there is a very cool new preview feature: “sp_invoke_external_rest_endpoint “. This function allows you to call certain Microsoft API endpoints directly from within your Azure database and write that data back into a table for example.
With that, you can for example create a stored procedure that can be triggered from Power Automate. This is ideal for larger datasets that would require long and slow “apply-to-each” rounds or cumbersome bulk-upload-workarounds.
I was struggling with the authentication when using a system assigned managed identity (“service principal”). Thanks to Davide Mauri for telling me how to fill in the parameters for the DATABASE SCOPED CREDENTIALS to make this work for Power BI:
Click through to see that answer, as well as a demonstration of the entire process.
Comments closedChris Webb explains an ambiguity:
This query connects to the TripPin OData feed (which is public, so you’ll be able to try this yourself) and filters the People table so it only gets the rows where the FirstName column equals “Angel”. If you paste this query into the Advanced Editor in Power Query Online to create a dataflow, you’ll see that the filter shows the “Might Fold” step indicator:
Read on to see what that indicator looks like, as well as an explanation of how Power Query doesn’t always know whether or not something is foldable.
Comments closedMarc Lelijveld talks to the outside world:
Let’s imagine you’re running a (fictive) company, and you’re short on data & analytics experts. Therefore, you decide to in hire expertise, and they will help you build your Power BI reports. As an employee of this organization, you rather have them starting sooner than later. But… if you need to request accounts for them at your IT organization, this might take weeks, if not a month to properly setup and run through this process. But what alternatives do you have?
In this blog I will further elaborate on the important things you should think about when working with Externals in Power BI. This blog is based on the session I’ve presented at the Dutch Power BI community day and at SQL Bits 2023 on the same topic together with my colleague Odeta Jankaitienė.
Click through for some of the important decisions you’ll need to make along the way.
Comments closedGilbert Quevauvilliers presses the big red button:
I have found that sometimes there are other systems that are loading data, and once they are complete they then want to refresh the Power BI Dataset.
Another way to do this is to use Power Automate, in which a system or user can request a HTTPS URL and once called that will then refresh the Power BI dataset.
I explain how to do this in the steps below.
Click through to see how to set up that job.
Comments closedSoheil Bakhshi has a recommendation for us:
However, managing RLS roles can be challenging if you have a large number of users or if your user base changes frequently. You need to manually assign each user account to one or more roles, which can be time-consuming and error-prone. Moreover, if a user changes their position or leaves the organisation, you must update their role membership accordingly.
This is where Security Groups become handy.
Soheil explains why and then gives us a step-by-step guide on what we can do to use security groups instead.
Comments closedChris Webb shares some insight:
Recently we announced an important new optimisation for DirectQuery datasets: the ability to run (some) of the queries generated by a single DAX query in parallel. You can read the blog post here:
A few of us on the Power BI CAT team have tested this out with customers and seen some great results, so I thought I’d write a post illustrating the effect this optimisation can have and explaining when it can and can’t help.
Chris has examples of great success, as well as not-so-great success and utter failure, and explains the why behind each outcome.
Comments closedDany Hoter shares some tips on creating star schema models with Azure Data Explorer:
Relationships between DQ tables are created as M:M by default. This is not a problem and even recommended with single direction.
Read on for several tips. What’s interesting as I read this is just how radically different the advice is for ADX utilization versus Power BI utilization, such as using strings to join dimensions to facts. That would be heresy in a Kimball-style model and is a common cause for slow-down in Power BI. Yet that’s the recommendation here for working with ADX, unless I’m misunderstanding Dany’s post.
Comments closedMarco Russo and Alberto Ferrari bundle things together:
In Power BI you can specify the unique identifier of a column value by using another column or another set of columns. This feature is currently used by the Fields Parameter feature in Power BI, but it may also be used for other purposes in a model. However, there are several limitations – such as the incompatibility with MDX queries – that reduce one’s ability to use Group By Columns property in many scenarios, so it cannot be used with Excel as a client.
Read on to learn more about how grouping works in Power BI and some of the limitations.
Comments closed