Press "Enter" to skip to content

Curated SQL Posts

Downloading Power BI Reports from a Workspace

Shabnam Watson has a helpful script for us:

You can use PowerShell to download all of your PBI reports in a workspace all at once without having to go through the PBI service UI one at a time. As an added bonus, you may notice that downloading a report with PowerShell is faster that downloading it through the PBI Service UI.

This script is useful for admins to take backups of reports deployed to PBI Service. It can be easily extended to loop through all/several workspaces. It is also useful for developers to take a backup of their report before publishing a new version.

Click through for the script.

2 Comments

Using sp_PressureDetector to Find Resource Constraints

Erik Darling has started a new series:

If you go to open a new tab in SSMS and you hear a creaking sound, it might be time to revisit some of your hardware choices.

But that’s a problem for future you. Your problem is right now.

– How to check it: sp_PressureDetector
– What to look for: Stuck queries, Resource Usage, Wait Stats

By default, it’ll look at both CPU and Memory counters. If you don’t know what’s going on, just hit F5.

Read on to see what it looks like for a server hitting memory limits and for a server hitting CPU limits.

Comments closed

Visualizing Parallelism in Power Query Diagnostics Data

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 closed

Measuring Advertising Effectiveness

Layla Yang and Hector Leano walk us through measuring how effective an advertising campaign was:

At a high level we are connecting a time series of regional sales to regional offline and online ad impressions over the trailing thirty days. By using ML to compare the different kinds of measurements (TV impressions or GRPs versus digital banner clicks versus social likes) across all regions, we then correlate the type of engagement to incremental regional sales in order to build attribution and forecasting models. The challenge comes in merging advertising KPIs  such as impressions, clicks, and page views from different data sources with different schemas (e.g., one source might use day parts to measure impressions while another uses exact time and date; location might be by zip code in one source and by metropolitan area in another).

As an example, we are using a SafeGraph rich dataset for foot traffic data to restaurants from the same chain. While we are using mocked offline store visits for this example, you can just as easily plug in offline and online sales data provided you have region and date included in your sales data. We will read in different locations’ in-store visit data, explore the data in PySpark and Spark SQL, and make the data clean, reliable and analytics ready for the ML task. For this example, the marketing team wants to find out which of the online media channels is the most effective channel to drive in-store visits.A

Click through for the article as well as notebooks.

Comments closed

Polymorphism in GraphQL

Dan Freeman takes us through the concept of polymorphism as it applies to GraphQL:

In APIs (and in domain modeling in general) it’s common to want to represent fields that may point to one (or more) of several different types of object, a.k.a. polymorphism. In GraphQL’s type system, this can be accomplished with either a union or an interface, depending on whether the objects in question are expected to have anything in common.

What’s not always obvious to newcomers to GraphQL, though, is how to best handle that data on the receiving end, when you need to tell what concrete type of object you’re dealing with.

It’s interesting to see how this is handled in GraphQL versus object-oriented languages.

Comments closed

Passing Parameters from Power Query to SQL Server

Gilbert 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 closed

Validating Data Model Results in Power BI

Paul 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 closed

Using Query Store over the Plan Cache

Erik Darling has a dream:

I used to think the plan cache was so cool.

– You can find queries that aren’t good there
– Plans are full of details (and XML)
– Supporting DMVs give you extra insights about resource usage

But most of the time now, I’m totally frustrated with it.

It clears out a lot, plans aren’t there for some queries, and the plans that are there can be very misleading.

Can you really tell someone what their worst performing queries are when everything in there is from the last 15 minutes?

No.

Read on for what’s nice about Query Store, as well as a few fixes which need to be there before it’s really useful. I’ve used Query Store in big environments to good effect (though our DBAs had to rewrite the cleanup processes because they’re bad) and I’ve had to turn it off in medium-sized environments running 2016 because it was harming performance. It’s a great concept and reasonable implementation with a few too many sharp edges.

Comments closed