Timing R Function Calls

Colin Gillespie shows off an R package for benchmarking:

Of course, it’s more likely that you’ll want to compare more than two things. You can compare as many function calls as you want with mark(), as we’ll demonstrate in the following example. It’s probably more likely that you’ll want to compare these function calls against more than one value. For example, in the digest package there are eight different algorithms. Ranging from the standard md5 to the newer xxhash64 methods. To compare times, we’ll generate n = 20 random character strings of length N = 10,000. This can all be wrapped up in the single function press() function call from the bench package:

Click through for an example involving hashing algorithms.

Linear Programming in Python

Francisco Alvarez shows us an example of linear programming in Python:

The first two constraints, x1 ≥ 0 and x2 ≥ 0 are called nonnegativity constraints. The other constraints are then called the main constraints. The function to be maximized (or minimized) is called the objective function. Here, the objective function is x1 + x2.

Two classes of problems, called here the standard maximum problem and the standard minimum problem, play a special role. In these problems, all variables are constrained to be nonnegative, and all main constraints are inequalities.

That post spurred me on to look up LINGO and see that it’s actually still around.

From pandas to Spark with koalas

Achilleus tries out Koalas:

Python is widely used programming language when it comes to Data science workloads and Python has way too many different libraries to back this fact. Most of the data scientists are familiar with Python and pandas mostly. But the main issue with Pandas is it works great for small and medium datasets but not so good on big-data workloads. The challenge now becomes to convert the existing pandas code to pyspark code. This is just not straight forward and has a lot of performance hits if python UDFs are used without much care.

Koalas tries to address the first problem ie lessen the friction of learning different APIs to port their existing Pandas code to Pyspark. With Koalas, we can just directly replace the existing pandas code with Koalas. As far as the performance goes, there are no numbers yet as it is still in the initial phase of the project. But this definitely looks promising though.

Read on for some initial thoughts on the product, including a few gotchas.

Storing Large Images in Power BI

Chris Webb shows us how to store a large image in Power BI:

Jason Thomas and Gerhard Brueckl have both blogged on the subject of storing images as text inside a Power BI dataset:

Since they wrote those posts, however, Power BI has added the ability to set the Data Category property on measures as well as columns in tables. This means it is now possible to have the output of a DAX measure displayed as an image in a Power BI report and this in turn opens up a lot of new possibilities – including the ability to work around the maximum size of a text value that can be loaded into Power BI (see my previous blog post for more details) and therefore work with larger images.

I don’t understand why they make this so complicated. I have a Grafana dashboard widget that I show in a Power BI dashboard and have it scaled way down so it fits in under 32K. I appreciate Chris’s answer but that’s a lot of work to show an image.

Creating Containers and Volumes

Grant Fritchey continues a dive into containers. First up is running a Docker container:

Let’s break this down a bit so you know what you just did. The two ‘-e’ statements are setting environment variables. The first is accepting Microsoft’s end user license agreement, EULA. The second is setting the SA password. By default, we’re running a Developer Edition of SQL Server here. If you want to, you can change to a version that you have a specific license for using the MSSQL_PID environment variable. Documentation for that is located here.

Next is using volumes:

Now, let’s create a new container, but, let’s use the same volume:
docker run -e 'ACCEPT_EULA=Y' ` -e 'SA_PASSWORD=$cthulhu1988' ` -p 1450:1433 ` --name DockerDemo19 ` -v sqlvol:/var/opt/mssql ` -d mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu

What happens next is marvelous. 

It’s an exciting time to get into containers and if you’re feeling a little trepidatious, they’re containers—the worst thing you can do is mess one up and then you just blow it away and start over.

Debugging DAX Calculations

Imke Feldmann has a debugger measure for DAX:

This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 

Just have in mind, that this only works for standalone CALCULATE-functions and not for those who are nested in other functions (who modify the evaluation context).

This looks to be quite useful.

New Query Store Functionality in 2019

Erin Stellato is excited about SQL Server 2019 CTP 3.0:

Friends, CTP 3.0 dropped today, and it includes some changes for Query Store in SQL Server 2019!  I am so excited!!  I’ve downloaded it and have WideWorldImporters installed and have a lot of testing planned, but if you’re impatient, guess what?  The documentation is already updated!  If you check out the ALTER DATABASE SET page you will see that Query Store now has a new option for QUERY_CAPTURE_MODE: CUSTOM.  For those of you with ad hoc workloadsthis will help.

Read on to see how it can help.

Quick Hits on Managed Instance Backup / Restore

Jovan Popovic has some pieces of advice for backing up and restoring databases on Azure SQL Managed Instances:

Managed Instance takes automatic backups (full backups every week, differential every 12 hours, and log backups every 5-10 min) that you can use to restore a database to some point of time in past within the retention period, restore accidentally deleted database. For more information, see Automated backups. Managed Instance also enables you to restore a database from a backup file placed on Azure Blob Storage, backup a database to Azure Blob Storage. Managed Instance currently don’t support backup retention longer than 35 days, but you can use backups to blob storage as an alternative.

If you are experiencing some issues with any backup or restore operation, the following troubleshooting steps might help you to identify the issue.

Click through for those hints.

Using Filtered Indexes

Monica Rathbun fills us in on filtered indexes:

What is a filtered index?
Simply it’s an index with a where clause. It is an optimized non clustered index that can be narrowed down in scope to better fit a subset of data. Example being date ranges, years, non NULLs or specific product types.

I wish filtered indexes were better than they are because they can solve some interesting problems but get stuck on parameterized queries.

Exploratory Data Analysis with inspectdf

Laura Ellis continues a dive into Exploratory Data Analysis, this time using the inspectdf package:

I like this package because it’s got a lot of functionality and it’s incredibly straightforward to use. In short, it allows you to understand and visualize column types, sizes, values, value imbalance & distributions as well as correlations. Better yet, you can run each of these features for an individual data frame, or compare the differences between two data frames.

I liked the inspectdf package so much that in this blog, I’m going to extend my previous EDA tutorial with an overview of the package.

There are some interesting functions which make EDA easier, so check it out.


May 2019
« Apr