Press "Enter" to skip to content

Day: March 3, 2022

Making Visual Elements Glow in Excel

Vincent Granville does some marketing:

I explain here how to do it in Excel. But the principle is general. You can produce this type of visualization with other tools. There are many features in Excel that allow you to generate marketing-style pictures. I never really used them, as the result can be cheesy. If overused, you end up with material that looks like advertising from a Casino, or like the “old world wide web”, where blinking fonts and documents with neon colors were popular. But I recently decided to give it a try again, using extreme moderation. I believe my experiment was successful. I will leave it to the reader to have a final say about it.

It turns out a lot less gaudy than I originally imagined.

Comments closed

Building a Recommender in Spark

Avinash Sooriyarachchi makes a recommendation:

There has been an exponential increase in the volume and variety of data at our disposal to build recommenders and notable advances in compute and algorithms to utilize in the process. Particularly, the means to store, process and learn from image data has dramatically increased in the past several years. This allows retailers to go beyond simple collaborative filtering algorithms and utilize more complex methods, such as image classification and deep convolutional neural networks, that can take into account the visual similarity of items as an input for making recommendations. This is especially important given online shopping is a largely visual experience and many consumer goods are judged on aesthetics.

In this article, we’ll change the script and show the end-to-end process for training and deploying an image-based similarity model that can serve as the foundation for a recommender system. Furthermore, we’ll show how the underlying distributed compute available in Databricks can help scale the training process and how foundational components of the Lakehouse, Delta Lake and MLflow, can make this process simple and reproducible.

Click through for the process.

Comments closed

Understanding Plans: Seeks and Scans

Erik Darling made me rhyme. First up, data retrieval via seek:

People. People complain. People complain about SQL Server. That’s probably why I get paid to deal with it, but whatever.

One complaint I get to hear week in and week out is that SQL Server isn’t using someone’s index, or that there are too many index scans and they’re slow.

That might actually be a composite of like twelve complaints, but let’s not start counting.

Erik gives us three reasons why we might not see a seek. But wait, there’s more!

I’m not sure why scans got such a bad rap. Perhaps it’s a leftover from the Bad Old Days© when people worried about logical fragmentation and page splits.

What I mean to say is: scans are often treated with a level of revulsion and focus that distracts people from larger issues, and is often due to some malpractice on their part.

This is true–scans aren’t inherently bad and Erik gives us a better mental model to work with.

Comments closed

Conditional Formatting with the New Power BI Desktop Formatting Pane

Gilbert Quevauvilliers puts a feature’s face on a milk carton:

I am sure everyone can agree that the new formatting pane is an awesome change.

But at the same time, I have found it a challenge to find settings with the new format pane.

In this blog post I will show you to find the conditional formatting which appears to have gone missing in the new format pane?

Click through to find out.

Comments closed

Scoring Azure ML Models in Azure Synapse Analytics

Alex Aleksandrov shows off the PREDICT operator:

We can use Synapse for many activities. We can use it not only for ingesting, querying, storing and visualising data, but for developing machine learning models as well. Of course, one can say that doing data science is another functionality of this platform and this is definitely true. However, in this article, I would like to show you that instead of using Python, one can use T-SQL for doing predictions.

Click through to see how.

Comments closed

The IN Operator in DAX

Marco Russo and Alberto Ferrari are making a list and checking it twice:

The IN operator in DAX is useful in multiple scenarios to check whether an expression belongs to a list of values. It is oftentimes used along with the anonymous table constructors. IN is syntax sugar for the CONTAINSROW function. Just like CONTAINSROW, IN can be used with multiple columns at once although that syntax is not so common.

Click through to see how you can use IN in your work.

Comments closed

Restoring a TDE-Enabled Database Backup to another SQL Server

Tom Collins has a backup to restore:

I have a SQL Server with TDE enabled and the user databases are TDE configured. I need to take a backup and restore the TDE enabled database to another SQL Server Instance . Could you take me through the steps  including prerequisites?

The answer is yes. And Tom is so kind as to show the answer rather than giving a flippant response, which is my modus operandi.

Comments closed

Auditing Logins and Finding Unused Tables with XESmartTarget

Gianluca Sartori continues a series on XESmartTarget. First up is a process to audit successful logins:

Sometimes you are not interested in the individual events captured by a session, but you want to extract some information from a series of events, by grouping and aggregating them. This is the case of events that happen very often, like, logon events, that can help you validate your story.

Imagine that you inherited a big, busy and chaotic SQL Server instance, with lots of databases and logins. One of the things that you probably want to do is track which logins are active and which ones are not and can be safely disabled.

Once you have that in place, how about unused objects?

The previous recipe showed you how to capture data from Extended Events sessions, summarize it in memory and then save it to a table in SQL Server, merging with any existing rows. This comes extremely handy when the events are not useful individually, but when the story is told by the aggregation of all the events.

Another possible problem that can be solved with the same technique is finding unused objects in the database. It looks like a trivial problem, but it’s not.

Read on to see how you can solve both of these issues.

Comments closed