Press "Enter" to skip to content

Month: May 2018

Getting Month Names Given Numbers In DAX

Published 2018-05-21 by Kevin Feasel

Philip Seamark shows how to convert month numbers into names in Power BI:

The most common solution I see offered is along the lines of a SWITCH statement that lists 12 conditions (one for each month).   This works, but can also be done using existing functions.

While DAX lacks a dedicated function to convert a number to a text version, such as DATENAME in T-SQL, we can get there in two functions using DATEVALUE wrapped in a FORMAT.

To demonstrate, I will create a simple table with 13 values (1 through 13) using the following calculated table.

This creates a single column table with 13 rows.

Read on for the rest of the story.

Comments closed

Overlaying Visuals In Power BI

Published 2018-05-21 by Kevin Feasel

Annie Xu gives us two methods for being able to jump between two visuals in the same space:

Disconnected Table method:

This method is more towards PowerBI modelers. Basically, the idea is to have a Field in a independent table (no relationship to other tables) as Slicer with your measure choice and then create a measure using SELECTEDVALUE function to have the measure dynamically switch referring measures based on the choice made on the slicer.

Click through for both methods.

Comments closed

JSON Data In SSIS

Published 2018-05-21 by Kevin Feasel

Stacia Varga shows a few methods for handling JSON data in SQL Server Integration Services:

And then I had to write about it in my book Introducing Microsoft SQL Server 2016 (which is free to download) when JSON support was added to SQL Server 2016. But I still didn’t have clients using JSON. It was interesting to me that I could use SQL Server to work with JSON data, but it was still theoretical to me rather than practical.

Therefore, I never thought much about how I would handle it in SQL Server Integration Services (SSIS). I just didn’t have a reason.

Until now. This seems to be the year that I am bumping into JSON left and right. It’s everywhere!

Read on for those methods as well as Stacia’s recommendation.

Comments closed

Exporting Table Details With Powershell

Published 2018-05-21 by Kevin Feasel

Andy Levy helps an end user understand a database schema:

This week I had a user come to me asking about how fields were defined on a few tables he was using in writing some reports. Long story short, he’s been tasked with writing some new reports and updating existing ones, but he doesn’t have visibility to the database itself so he’s left with the “ok, let’s try this” approach and then reading error messages to debug when things go sideways. Very tedious.

I asked him for a list of the tables he was most interested in, and while he worked on that I set to work on (of course) a quick & dirty PowerShell script to collect the data he needed – field names, types, and whether they’re nullable.

Ideally these analysts would have data model documentation, but it’s not an ideal world.

Comments closed

Choose Your Hadoop File Format

Published 2018-05-18 by Kevin Feasel

Alex Woodie explains three of the most common Hadoop file formats:

You have many choices when it comes to storing and processing data on Hadoop, which can be both a blessing and a curse. The data may arrive in your Hadoop cluster in a human readable format like JSON or XML, or as a CSV file, but that doesn’t mean that’s the best way to actually store data.

In fact, storing data in Hadoop using those raw formats is terribly inefficient. Plus, those file formats cannot be stored in a parallel manner. Since you’re using Hadoop in the first place, it’s likely that storage efficiency and parallelism are high on the list of priorities, which means you need something else.

Luckily for you, the big data community has basically settled on three optimized file formats for use in Hadoop clusters: Optimized Row Columnar (ORC), Avro, and Parquet. While these file formats share some similarities, each of them are unique and bring their own relative advantages and disadvantages.

Read the whole thing.  I’m partial to ORC and Avro but won’t blink if someone recommends Parquet.

Comments closed

Using The Map Function In R

Published 2018-05-18 by Kevin Feasel

Nicolas Attalides on using purrr:

The best place to start when exploring the purrr package is the map function. The reader will notice that these functions are utilised in a very similar way to the apply family of functions. The subtle difference is that the purrr functions are consistent and the user can be assured of the output – as opposed to some cases when using for example sapply as I demonstrate later on.

My considered belief is, Always Be Purrring.  H/T R-bloggers

Comments closed

Storing An Encrypted Password In The Solr Configuration File

Published 2018-05-18 by Kevin Feasel

Jon Morisi shows us how to store an encrypted password in Solr’s configuration file, rather than storing the password in plaintext:

The config file has a lot of options, in short this is where you configure a database connection string and reference your jdbc jar file. Full details are here.  By default any of the examples that come with the Solr distribution use a plain text username and password.  This can be potentially viewed from the front end:
http://hostname:8983/solr/ > Select Collection from the drop-down > Click data Import > expand configuration
Obviously we do not want to store our username and password in plain text.  The config file includes an option to encrypt the password and then store the key in a separate file.

Storing passwords in plaintext is a classic mistake that I see far too often.  And then when someone checks in that config file to a public GitHub repo…

Comments closed

T-SQL Join Delete

Published 2018-05-18 by Kevin Feasel

Steve Stedman walks us through a bit of T-SQL proprietary syntax:

1
2
3
DELETE t2
FROM [dbo].[Table1] t1
INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id;

Names have been changed to protect the innocent.

In the above delete statement which table will have rows deleted from it?

A: Table1

B: Table2

C: Both Table1 and Table2

D: Neither Table1 and Table2

Got it in one.  I like having this syntax available to me when I need it, even though it’s not ANSI standard.

Comments closed

Installing OpenSSH Server: Windows 10 Edition

Published 2018-05-18 by Kevin Feasel

Anthony Nocentino shows us how to install OpenSSH server on Windows 10 update 1803:

So in yesterday’s post we learned that the OpenSSH client is included with the Windows 10, Update 1803!  Guess, what else is included in this server, an OpenSSH Server! Yes, that’s right…you can now run an OpenSSH server on your Windows 10 system and get a remote terminal! So in this post, let’s check out what we need to do to get OpenSSH Server up and running.

First, we’ll need to ensure we update the system to Windows 10, Update 1803. Do that using your normal update mechanisms.

With that installed, let’s check out the new Windows Capabilities (Features) available in this Update, we can use PowerShell to search through them.

Anthony goes through the steps for configuration, so check that out.

Comments closed

Updating A Table Using Change Data Capture Without Downtime

Published 2018-05-18 by Kevin Feasel

Robert Blackburn takes us through the steps of updating a table which uses Change Data Capture without taking a downtime window:

Steps

  1. Stop jobs that process CDC (SSIS).

  2. Inside a transaction with isolation level serializable: Alter Table schema and create temporary CDC table

  3. Copy old CDC rows to new table excluding dup rows (based on [__$seqval])

  4. Disable old (original) CDC table (schema is outdated). Will drop table

Click through for the rest of the steps and an example script.

Comments closed