Press "Enter" to skip to content

Day: October 5, 2021

Working with Wide Data in R

Published 2021-10-05 by Kevin Feasel

Andrew Collier works with some wide data:

The concept of “wide data” is relative. In some domains 100 columns is considered “wide”, while in others that’s perfectly normal and you’d need to have thousands (or tens of thousands!) of columns for it to be considered even remotely “wide”. The data that we work with at Fathom Data generally lies in the first domain, but from time to time we do work on data that is considerably wider.

This post touches on a couple of approaches for dealing with that sort of data. We’ll be using some HCRIS (Healthcare Cost Report Information System) data, which are available for download here. Specifically, we’ll be working with an extract from the hcris2552_10_2017.csv file, which contains “select variables in flat shape”.

Click through for one example which has 1700 columns. H/T R-Bloggers.

Comments closed

Adding Columns to a Published Data Model with TMSL

Published 2021-10-05 by Kevin Feasel

Kristyna Hughes wants to update an existing schema:

Goal of this demo: Update a Power BI model schema by adding a column to the data model without opening a PBIX file and ensure the scheduled refresh still works.

Why would this be useful? Updating the schema in the desktop tool requires an entire refresh of the data model which can take a while if your model is large. Also, app developers could systematically add new elements to existing data models using a formulaic XMLA script through SSMS, saving your report designers time when new fields need to be added.

Read on for limitations, as well as the process.

Comments closed

Starting SSMS with a Different Windows Login

Published 2021-10-05 by Kevin Feasel

Jack Vamvas does a thing I wish we could do innately in SQL Server Management Studio:

I  am logged onto my desktop with my primary Active Directory ID.  I need to log onto a SQL Server with SQL Server Management Studio (SSMS) with another Active Directory ID – the alternative ID has   valid  SQL Server security privileges on the target SQL Server. 

How can I log onto the target SQL Server with the alternative login  , remain on my desktop and use the alternative Active Directory ID ?

By the way, if you need to connect to a remote domain but your machine isn’t a member of that domain, add the /netonly flag to Jack’s answer. I’ve had to do this before when VPN’d into a network with a laptop not registered on that domain. Another tip is that, if you do this a lot, you might want to create a Windows shortcut which includes the full command.

Comments closed

Automating Data Collection with Extended Events

Published 2021-10-05 by Kevin Feasel

Ed Pollack continues a series on extended events:

While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.

This article dives into one method for collecting and retaining all event data for a specific set of events. A system like this is highly customizable and provides a solid starting point for Extended Events development. This should be viewed as a collection of puzzle pieces; individual pieces can be adjusted as needed to produce a monitoring solution that fits the needs of a given situation, even if it is vastly different from what is demonstrated here.

Read on for the process. Shredding XML isn’t pretty, but the good news is that with a setup like this, you only need to do it once…unless you need to change it later, so get it right the first time and bam, problem solved.

Comments closed

Sharing Short Code Examples

Published 2021-10-05 by Kevin Feasel

John McCormack lays out the parameters for this T-SQL Tuesday:

T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?

What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.

Click through for two of John’s.

Comments closed

UDFs and STRING_AGG

Published 2021-10-05 by Kevin Feasel

Erik Darling has a bone to pick with STRING_AGG():

If you’re like me and you got excited by the induction of STRING_AGG into the T-SQL Lexicon because of all the code odd-balling it would replace, you were likely also promptly disappointed for a few reasons.

Read on for one post which covers all of those reasons. Even with that disappointment, I’m still happy with STRING_AGG() on the whole, myself. There are some extra steps it’d be nice to eliminate in certain circumstances, but 60% of the time, it works every time.

Comments closed