Press "Enter" to skip to content

Author: Kevin Feasel

Good Practices for Power BI Development

Published 2022-10-26 by Kevin Feasel

Reza Rad shares some thoughts with us:

DAX is the language of writing calculations in Power BI. We use DAX to write calculations such as year-over-year change and percentage, or percentage of the total or rank of customers by their yearly revenue. Writing calculations in DAX takes time, and you may likely need to re-use a calculation in multiple reports.

Creating copies of the PBIX file every time for reusing the calculation is not ideal. The better approach is to have a shared dataset created by DAX calculations and then create thin reports with live connections to the shared Power BI dataset. Using a shared dataset ensures that all the reports are using the same DAX calculations. If a change is needed, it is only needed in the shared dataset. Maintaining a solution like this would be much easier.

Click through for a dozen or so recommendations.

Comments closed

Enabling SSH Remoting with Powershell 7

Published 2022-10-26 by Kevin Feasel

Patrick Gruenauer makes a remote connection:

With Powershell 7, we can enable and use SSH Remoting. Unlike WinRM, SSH is more popular because it is a more familiar technology and is also available on other platforms. In this post I will show you how to easily enable SSH on PowerShell 7 with a few lines of code.

Click through to see what you need to install on the machine and how to configure everything.

Comments closed

Find When a Table was Dropped

Published 2022-10-26 by Kevin Feasel

Andrea Allred does some sleuthing:

Say you have a user come to you and they dropped a table sometime yesterday, but they don’t remember when and now they need it back. You could start the restore process and roll through logs until you see the drop and then restore to the hour before or you could run this super cool query to get the time the table was dropped.

Click through to see when and even which user did it—assuming you don’t have everybody going through a connection pooled account.

Comments closed

Contrasting INSERT INTO and SELECT INTO

Published 2022-10-26 by Kevin Feasel

Chad Callihan embraces the power of AND:

Data can be inserted into one temp table from another a couple of ways. There is the INSERT INTO option and the SELECT INTO option.

Are you devoted to one option over the other? Maybe you’re used to one and never experimented with the other. Let’s test each and compare performance to find out which is more efficient.

Both of these are useful, though Chad does mention a performance improvement with SELECT INTO. I tend to prefer INSERT INTO for “structured” scenarios because it lets me define the shape of the output table. When I don’t care what the shape is—for example, when I just need some data one time to perform an analysis—then I prefer SELECT INTO for its simplicity.

Comments closed

Splitting Strings with KQL

Published 2022-10-25 by Kevin Feasel

Robert Cain splits the baby:

In databases, we often find columns that are stored in a hierarchy structure, not unlike a file path on your drive. For example, in the Microsoft Logs sample database the Perf table stores its counter path this way: \\computername\Memory\Available MBytes.

It would be helpful to have a way to easily break this path out into its individual parts. KQL provides us a way of doing this using the split function.

Check out examples of how you can perform splitting.

Comments closed

Pattern Learning in Amazon SageMaker

Published 2022-10-25 by Kevin Feasel

Vishaal Kapoor, et al, take us through an example of pattern learning in Amazon SageMaker:

Pattern learning automatically analyzes your data and surfaces textual constraints that may apply to your dataset. For the example with phone numbers, pattern learning can analyze the data and identify that the vast majority of phone numbers follow the textual constraint [1-9][0-9]{2}-[0-9][4]. It can also alert you that there are examples of invalid data so that you can exclude or correct them.

In the following sections, we demonstrate how to use pattern learning in Data Wrangler using a fictional dataset of product categories and SKU (stock keeping unit) codes.

Read on for the scenario.

Comments closed

Getting Row Counts for Different DBMS Platforms

Published 2022-10-25 by Kevin Feasel

Brendan Tierney wants rowcounts:

A little warning before using these queries. They may or may not give the true accurate number of records in the tables. These examples illustrate extracting the number of records from the data dictionaries of the databases. This is dependent on background processes being run to gather this information. These background processes run from time to time, anything from a few minutes to many tens of minutes. So, these results are good indication of the number of records in each table.

Click through for examples in Oracle, MySQL, Postgres, SQL Server, and Snowflake. Though the SQL Server one does need a GROUP BY clause because it’s a sum of the partitions’ rows.

Comments closed

Tips for Getting Technical Help Online

Published 2022-10-25 by Kevin Feasel

Grant Fritchey provides some tips:

One thing you see a lot is that people, for whatever reason, will absolutely not simply state what the actual problem is. You’ll get stuff like “What SQL Server internal behavior prevents dynamically naming local variables?” And you’re left scratching your head, why on earth would someone want to dynamically name local variables? Only to find out, after lots of comment & discussion, they thought that you needed to rename variables when changing values.

When you get stuck, take a moment to describe the problem, but aim for the root of the problem, not peripheries. “I want to do X. I’m doing X. It looks like this. The results are this when they should be that. What am I doing wrong?” That’s the “magic” formula.

Grant provides several helpful tips for increasing the likelihood that someone will help you find the right answer.

I want to note two quick things here, though. The first is that, quite often, people don’t even know what they intend to ask. In the cited bit above, this quite often comes from basic confusion cascading into specific “I’m stuck here” questions. This leads to foundational misunderstandings between people and frustrates the question-and-answer process.

The second thing I’d note is, be clear in your writing. The biggest tip I have here is to make sure your sentences and ideas flow. In other words, within a paragraph or section, try to keep thoughts related to the key topic and avoid asides or rambling. The reason for this is that your inner monologue can make sense of a variety of asides, but a reader without access to your thoughts will struggle to follow along. Furthermore, it is common to add bits and pieces to a half-finished post, fleshing out details. Before posting, make sure that it makes reading sense. Quite often, people will write a post and then fill in additional details before submission. A common result of this is that you might add some information intended to clarify a point which you actually introduce later in the post.

Comments closed