Press "Enter" to skip to content

Author: Kevin Feasel

Cumulative Values in Power BI

Published 2021-06-23 by Kevin Feasel

Matt Allington has a video for us:

The table on the left above shows the change in head count in each department, and is to be populated by the manager. But when it comes to reporting, we really need to know the total change in headcount as a number for each year, not just the first year the change occurred (as shown in the table to the right, above).

There are different ways to solve this problem, but I decided to do it using a combination of Power Query and DAX. 

Click through for the video solution.

Comments closed

Incremental Refreshing in Power BI Premium Per User

Published 2021-06-23 by Kevin Feasel

Gilbert Quevauvilliers continues a series on migrating from Azure Analysis Services to Power BI Premium Per User:

Welcome to the fourth instalment of my series on migrating AAS to PPU.

In this blog post I am going to cover the differences, and pros and cons to incremental refreshing when migrating from AAS to PPU.

The reason for incremental refreshing is that I do not have to reload the entire fact table every day when new data arrives.

This also allows for new data to be updated quicker.

And finally, it also uses less resources (memory and CPU) when refreshing the datasets.

Read on to see how it works in both platforms.

Comments closed

Trimming Strings with Powershell

Published 2021-06-23 by Kevin Feasel

Patrick Gruenauer shows off the Trim series of methods in Powershell:

In PowerShell it’s easy to manipulate strings, especially to cut off some characters from a string. These three methods are your friends when it comes to trimming: Trim, TrimStart and TrimEnd. In this blog post I will show you how to use PowerShell to manipulate strings and cut off something at the beginning and at the end. I will also try to give you some useful practical examples in hand to convince you that it’s worth to learn something about string manipulation. Let’s hop in.

Click through for examples of these three methods and how they do more than removing whitespace.

Comments closed

Fun with Database Corruption

Published 2021-06-23 by Kevin Feasel

Chad Callihan isn’t in production:

When you’re not expecting it, database corruption is bad for your database server (and probably your own personal health). Database corruption can be fun though when you’re the one doing the corrupting. Let’s take a look at corrupting a database and see what we can learn.

Click through to see what kind of madness you can pull off and how to fix it. And if you get really excited about database corruption, check out Steve Stedman’s Database Corruption Challenge.

Comments closed

FGCB_ADD_REMOVE Latch

Published 2021-06-22 by Kevin Feasel

Paul Randal looks at a particular latch:

Most latch class names are tied directly to the data structure that they protect. The FGCB_ADD_REMOVE latch protects a data structure called an FGCB, or File Group Control Block, and there will be one of these latches for each online filegroup of each online database in a SQL Server instance. Whenever a file in a filegroup is added, dropped, grown, or shrunk, the latch must be acquired in EX mode, and when figuring out the next file to allocate from, the latch must be acquired in SH mode to prevent any filegroup changes. (Remember that extent allocations for a filegroup are performed on a round-robin basis through the files in the filegroup, and also take into account proportional fill, which I explain here.)

Read on to understand what can cause this particular latch to become a bottleneck in your system.

Comments closed

Planning a Restore Strategy

Published 2021-06-22 by Kevin Feasel

Jonathan Kehayias inverts the paradigm:

Do you know how long it takes to RESTORE each database from a FULL backup? Does your run book include critical configuration options like Instant File Initialization or backup compression that can impact how long it takes to restore a database? Does the database have an excessively large transaction log file that will require zero initialization upon creation if the existing database files are not there? Does the database use transparent database encryption (TDE) which will prevent it from being able to instant initialize the size of the data files for the database?

Until you test your RESTORE time for a FULL backup, you don’t know if you can meet your RTO utilizing backups or not, and you may need one of the previously mentioned HA/DR features in SQL Server. However, in an absolute worst case scenario where recovery can only occur from backups, it is still important to know how long each step of the process is going to take so that you can set expectations appropriately. Your minimum recovery time is going to at least be the time required to restore the most recent FULL backup.

Click through for a lot of great advice in this vein.

Comments closed

Returning 0 Instead of BLANK in DAX

Published 2021-06-22 by Kevin Feasel

Marco Russo and Alberto Ferrari want to see zeroes in specific circumstances:

What makes this specific product interesting is that the product had sales in 2007, no sales in 2008 and it started selling again in 2009. Its behavior is different than the other products. Indeed, for most of these products one can argue that they start to produce sales when they were introduced in the market. Their behavior is quite intuitive: no sales up to a given point in time, then they start selling. We want to highlight this specific product because it shows a gap in sales when it was already present on the market. For other products, we are happy to blank them until their first sale. By doing this, we show gaps when they are real, and we avoid showing non-relevant information, that is products that could not produce sales because they were not even available to sell.

Read on to see how they do this.

Comments closed

Exporting Large Tables from Power BI Desktop

Published 2021-06-22 by Kevin Feasel

Nick Edwards shows us the way:

Have you ever wanted to export a table from Power BI Desktop into Excel just to make sure the DAX you’ve written is performing as expected but ran into this error message: “Data exceeds the limit – Your data is too large. Some data sampling may occur”?

Most probably this has occurred because you’ve got more than 30,000 rows of data in your table that you’re trying to export. In the example shown below I’ve actually got 30,001 rows of data – a row containing column headers plus 30,000 rows of actual data.

Click through to see how you can get around this limitation.

Comments closed

Declarative MLOps with Ludwig

Published 2021-06-21 by Kevin Feasel

Jacqueline Cardoso announces a new version of Ludwig:

Ludwig abstracts away the complexity of combining all these disparate systems together through its declarative approach to structuring machine learning pipelines. Instead of writing code for your model, training loop, preprocessing, postprocessing, evaluation, and hyperparameter optimization, you only need to declare the schema of your data as a simple YAML configuration:

I’ve long been a fan of declarative approaches to problem-solving, so I’m going to need to dig into this a bit.

Comments closed