A Fine Slice Of SQL Server
Chris Webb wants to track query concurrency when loading data into Power BI:
Most of the time I’ve spent looking at Power Query Query Diagnostics data, I’ve been looking at data for a single query. Over the past few days though I’ve spent some time investigating what this data shows for all the queries that are executed for a single dataset refresh. To help me do this I wrote the following M function:
Click through for the function, as well as ways of visualizing the results.
Comments closedGilbert Quevauvilliers has an interesting solution to a common problem:
I had a requirement where the client wanted the capability to decide how much data to load from a SQL Server Query (TSQL). This was so that they could limit the dataset returned, as at times they did not need all the data.
So below I demonstrate how to achieve this.
NOTE: This will be slightly advanced because I had to manually add some code in the Advanced Editor in Power Query.
Maybe it’s because of the number of times I had to do this with Reporting Services, but this seems like it should be a lot easier than it is.
Comments closedPaul Turley continues a series on doing Power BI the right way:
When designing a new data model, this is typically the first thing I do… For every fact table and for each large dimension table, I create a measure that returns the record count for that table. Users normally think about their data in business terms (like sums, ratios and averages) and not about how many records there are in a table. Record counts are a convenient sanity check for record completeness and distribution across groups; and may also be a good indicator for model size and performance.
Paul takes several passes at the problem, getting a bit deeper into it each time.
Comments closedMatthew Roche clues us in on what’s coming for Power BI:
The Power BI team at Microsoft publishes a “release plan,” which is essentially the public product roadmap. Anyone can use it to understand what new capabilities and improvements are planned, and when they’re expected to be released.
One challenge with the official release plan comes from the fact that it is a set of online documents, and that for each “release wave” there is a new set of docs – it’s not always clear where to look for the latest information on a given feature.
But thanks to Alex Powers, this is a lot clearer now. Click through to learn how.
Comments closedMartin Schoombee continues a series on Power BI automation:
Deploying the report is seemingly straight-forward, but there are some risks we need to consider:
– What should we do if the report already exists?
– If the dataset exists, what should we do if there are other reports that use this (shared) dataset?The last item is a bit of an edge-case that we’ll have to dive deeper into, but let’s look at the basic cmdlet first.
Click through to see how, as well as some thoughts on those risks.
Comments closedGinger Grant walks us through two methods of integrating Power BI and Azure Synapse Analytics:
From within Synapse you have the ability to access a Power BI workspace so that you can use Power BI from within Synapse. Your Power BI tenant can be in a different data center than the Azure Synapse Workspace, but they both must be in the same Power BI Tenant. You can use Power BI to look at any data you wish, as the data you use can be from any location. When this blog was written, it was only possible to connect to one Power BI workspace from within Azure Synapse. In order to run Power BI as shown here, first I needed to create a Linked Service from within Synapse.
Read on for more.
Comments closedChris Webb gives us an update on Power Query:
If you’re familiar with the topic of query folding in Power Query, you’ll know that the View Native Query right-click option in the Applied Steps pane of the Power Query Editor can be used to show the native query that is run against the data source. You may also know that there are some data sources where query folding does take place but where View Native Query remains greyed out.
Read on to see which sources are now available and to see an example of this in action.
Comments closedMartin Schoombee continues a series on automating Power BI deployments:
There are two things to keep in mind when creating workspaces in an automated fashion:
– The workspace may already exist. When you delete a workspace, it isn’t really deleted…only its status changes to “deleted”. That’s great if you want to restore a workspace you’ve accidentally deleted, but your code will have to account for it or it will fail.
– Whether you are creating or restoring a workspace, you have to provide an account to be the new administrator in the workspace. This ensures that the workspace is accessible, and by design a workspace cannot exist without an administrator.
Read on for the code.
Comments closedMatthew Roche points out an API update:
Back in August I highlighted the new dataflows PowerShell script repo on GitHub. These scripts provide an accelerated starting point to working with the dataflows REST APIs and to automate common dataflows tasks.
This week the dataflows team has released two new REST APIs for managing dataflows transactions (think “refresh”) and a new parameterized PowerShell script to make using the APIs easier.
Read on to see how you can use these new APIs to trigger a dataset refresh once a dataflow refresh has completed.
Comments closedMarc Lelijveld provides us insights:
Getting more insights in the telemetry of your Power BI dataset is always interesting. Especially if you share your Power BI dataset for self-service purposes to build new reports on top of your managed dataset, you might want to know who is actually using in and what queries are they executing against your datasets.
Besides that, there might be a whole lot of other valid use cases where you want to have more insights in currently running sessions on your dataset. An example where I recently ran into, was canceling a Power BI Dataset refresh. As there is no button in the Power BI Service to do this, I had to find a different way to do this.
Read on to see how, using DAX Studio.
Comments closed