Press "Enter" to skip to content

Author: Kevin Feasel

Guids in Persisted Calculated Columns

Slava Murygin shows us some odd things which occur when you try to make a persisted calculated column out from a UNIQUEIDENFITIER data type:

This post is for you in case you decide to use Uniqueidentifier column in your table and then you think about including it into a Persisted calculated column.
You also might see it useful if you like weird or funny SQL Server behavior.

Slava has an Azure feedback item and it looks like someone tested the behavior in SQL Server 2019 and it works as you’d expect, so this must have been fixed sometime between then and now.

Comments closed

SSMS Tips

Taiob Ali has a few tips for us around using SQL Server Management Studio:

Opening SSMS as a different user

I have two Active Directory Accounts. Once I use to login to my workstation and everything except working inside Databases. For that, I needed to open the SQL Server Management Studio (SSMS) with my admin account. I created an instance of runas application on my desktop to do that. Saves me a few clicks and type in my user name and password every time I launch SSMS. Text of the target.

Click through to see how that works. This is quite useful when you’re working in multiple environments or across multiple domains, especially if you slap on the /netonly parameter.

Comments closed

Copying Measures between Power BI Datasets

Tomaz Kastrun walks us through copying measures between datasets and files in Power BI:

Measures in Power BI document are DAX based aggregations for slicing and dicing the tabular datasets using evaluate and filter context and and data relationships. Short hand, measures are DAX formulas stored in Power BI as part the dataset.

For showing how easy (once you know) is to copy measures between different dataset, I have created a sample Power BI with diamonds dataset (also available on Github).

I’d call the process convoluted. It does work, but it seems like Power BI should have an easier method.

Comments closed

Changes in the R foreach Package

Hong Ooi announces some changes to the foreach package in R:

This post is to announce some new and upcoming changes in the foreach package.

First, foreach can now be found on GitHub! The repository is at https://github.com/RevolutionAnalytics/foreach, replacing its old home on R-Forge. Right now the repo hosts both the foreach and iterators packages, but that may change later.

There are also some changes to the package itself, so read on for those.

Comments closed

Building a Cache in ksqlDB

Michael Drogalis shows how to build a materialized cache to reduce the load on your Kafka Streams servers:

There are a lot of ways that you can introduce a materialized cache into your architecture. One such way is to leverage ksqlDB, an event streaming database purpose-built for stream processing applications. With native Kafka integration, ksqlDB makes it easy to replicate the pattern of scaling out many sets of distributed caches.

Let’s look at how this works in action with an example application. Imagine that you have a database storing geospatial data of pings from drivers at a ridesharing company. You have a particular piece of logic that you want to move out of the database—a frequently run query to aggregate how active a territory is. You can build a materialized cache for it using ksqlDB.

The tutorial starts you from “grab the Docker container” and takes you through the process.

Comments closed

SQL Server Life Hacks

Kevin Hill has a few life hacks for us:

Since I am an independent consultant here under the Dallas DBAs name, I am frequently asked to come in and quickly find out why the server is so slow. Sometimes by existing customers, sometimes out of nowhere. Many of these times I cannot make any “permanent” or “lasting” changes, so my go-to is to run sp_whoisactive as a temporary stored procedure (code in link). Works the same, nothing left behind, no corporate policies violated.

Read on for another useful tip.

Comments closed

Fun with Binding Redirects in .NET

Nick Craver has some advice if you see binding redirect problems:

You’re probably here because of an error like this:

Could not load file or assembly ‘System.<…>, Version=4.x.x.x, Culture=neutral, PublicKeyToken=<…>’ or one of its dependencies. The system cannot find the file specified.

And you likely saw a build warning like this:

warning MSB3277: Found conflicts between different versions of “System.<…>” that could not be resolved.

Whelp, you’re not alone. We’re thinking about starting a survivors group. 

Read on for Nick’s advice here. This is particularly tricky with F# and especially when you use type providers, as that can easily lead to a version mismatch in FSharp.Core. I’ve spent way too much time tracking those down.

Comments closed

Removing Duplicates with Power Query

Matt Allington wants to remove duplicate records while retaining the last version of the record using Power Query:

Today I was helping a customer with a problem that seemed quite simple on the surface.  She had a data table containing historical customer sales orders (each customer has many orders on different dates).  The objective was to filter this table in Power Query and just load one record for each customer – the one that was the last order date.  To illustrate the problem more clearly, I have adapted the scenario using the Adventure Works database.

Click through for the demo, and also read the comments as there are a lot of interesting attempts to solve the problem there as well.

Comments closed

Tips for Power BI Paginated Reports

Jonathan Jones has five tips to help you work with Power BI Paginated Reports:

Don’t use the Visual Designer, write your queries out first

As tempting as it can be to drop and drag fields and parameters into your dataset, I would not recommend it.  It makes it harder to repeatedly test, share the logic in the query, and see the results. Instead, write the script, whether it be in DAX or SQL.  Writing the queries is a faster process that gives you a lot more control over your queries.  If you don’t understand SQL or DAX to a proficient enough level, don’t panic, you could start the query with the visual designer and then convert it to the script.

When you’re running your DAX queries, don’t use the dataset query designer in the report builder, use DAX studio. DAX studio gives you the ability to efficiently query, format, and test DAX. As an experience it’s much faster and easier to show other people. If you are using SQL queries you could use SQL Server Management studio.

As a practice, when you’re trying to test the data, it’s important to get the data right in your queries before you place that data in your report.

Read on for the rest of the tips.

Comments closed

Uploading Multiple RDL Files to SSRS

Stuart Ainsworth shows us how to push several SSRS reports at a time:

My QA folks have a problem; development has been working on migrating a bunch of reports from a legacy 3rd-party application to SQL Server Reporting Services. Development has finished their “sprint” (*cough* waterfall), and handed over 52 reports to QA, and told the to load them into their QA server. The problem? The web interface only loads 1 report at a time.

Me, being the DevOps guy that I am, thought “well, that’s just silly; there has to be a way to upload all of the reports at once, rather than clicking on some silly buttons over and over again”.

Read on to see what Stuart did next.

Comments closed