Press "Enter" to skip to content

Curated SQL Posts

Conditional Visibility in Power BI Paginated Reports

Published 2021-07-20 by Kevin Feasel

Sabrina Jordan has a clever solution to a common customer request:

How many times have you struggled to find the happy medium between a report that looks phenomenal when printed, but has everything a user might need in an Excel export? I recently built a beautiful paginated report with groupings separated by white space for easy readability – but the user wanted to export the results to Excel, and the format prevented them from sorting or filtering the report contents. Power BI Report Builder has a couple features that can allow you the best of both worlds, with a few simple tricks. By the end of this tutorial, you will have created two Tablix, set up conditional visibility based on report render format, and set conditional sizing on the Excel Tablix (using hidden charts!) to prevent blank pages.

Click through for the solution.

Comments closed

Customized Per-User Default Values in Power BI

Published 2021-07-20 by Kevin Feasel

Marco Russo and Alberto Ferrari lay out a challenge:

Tabular offers the built-in feature of hiding rows of data from specific users. For example, you can create a set of security rules to let a store manager see only the sales of their store. This works fine if your goal is to secure data, which means preventing access to data that a user is not expected to see.

Another common requirement is to be able to select by default, for a store manager, their sales. With that said, store managers can see the data of other stores, but they need to explicitly request it. In other words: by default, the store manager sees the sales of their store only. By using a slicer, they can choose a different combination of stores.

Read on for the solution, but be sure to read the warnings Marco and Alberto include near the end of the article.

Comments closed

Power BI Anti-Patterns

Published 2021-07-20 by Kevin Feasel

Paul Turley begins a new series:

It occurred to me that we have put so much effort into promoting best practices and proper design that there is far less information about how to create bad reports and data models. In that light, the purpose of this article is to talk about what to do if you want things to go poorly and make sure your projects fail – if not immediately, then sometime in the future – and if not for you then for whoever inherits the work that you have done.

I welcome your comments and thoughts about the worst practices you have seen in Power BI. Post your ideas in the comments below. What examples have you encountered of sure-fire ways to make projects go badly?

Paul has six starter anti-patterns; click through for the set.

Comments closed

Log Shipping Configuration with Powershell

Published 2021-07-20 by Kevin Feasel

Lee Markum needs to do log shipping in bulk:

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Click through for the next steps in the process, including a well-timed Get-Help call.

Comments closed

Filtering Extended Events with Actions

Published 2021-07-20 by Kevin Feasel

Grant Fritchey gives us a technique to filter extended events:

Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don’t have to be collected in order to put them to work filtering your Extend Events.

Read on to see how.

Comments closed

Parameterizing ADF Pipelines

Published 2021-07-19 by Kevin Feasel

Reitse Eskens continues a series on learning Azure Data Factory:

In my previous blog I created the integration runtimes, and the linked services. However, we need to create new datasets. If you remember, and I don’t blame you if you don’t, the dataset I created contained a reference to a table. That’s nice, but this time we don’t want just one table, we want a number of tables.

Click thorugh to check it out.

Comments closed

Managing Spatial Data in Azure

Published 2021-07-19 by Kevin Feasel

Rolf Tesmer takes us through the different Azure services which offer some ability to work with spatial data:

Every now and then you come across a use-case where you need to do something with spatial data, and you need to do it in the cloud (Azure, of course)! Up until that very point you maybe didn’t know, or perhaps even care, much about the intricacies of spatial data assets, let alone how the heck you were going to store it, process it, and query it, without making a mess of your current data stack.

Well, if you’re that person, then I say welcome to this blog post!

Click through for a fairly lengthy list, including Rolf’s comments on each. Also note the one big omission from the list as far as data platform products.

Comments closed

The Downside Risk of Index Hints

Published 2021-07-19 by Kevin Feasel

Chad Callihan explains why you should be careful before deploying code which uses index hints:

This might be good enough…for now. The potential issues with index hints can be more about the future than the present. You might come along later on and think “why not use an index to cover the whole query?” We can add the index:

But if our query is still written to include the index hint (in a stored procedure for example) the new index is not going to matter. The old index is still forced to be used. Even if something better comes along, you’re going to need to modify the query in addition to adding the better index. If an index was added for a completely separate query but would also be an improvement for the query in question, it’s also not going to get by the index hint.

Click through for additional problems which can crop up as you use index hints. This isn’t a big argument against using them at all, but rather understanding (and remembering!) where you do use them and making sure that’s communicated well to the entire team, including future you.

Comments closed