Press "Enter" to skip to content

Month: February 2021

Visualizing SQL Server Graph Tables via TGF

Published 2021-02-12 by Kevin Feasel

Louis Davidson shows how you can visualize data stored in SQL Server graph tables:

Each node object has its own surrogate key values that start at 0, so if you are going to use the code for more than one node at a time, you have to make the surrogate values unique for the TGF file (see the last blog on importing for more details on that). In the code I make a temp table to stage the objects, so if you have > 1 node, the second set of keys need to start off where the previous ones left off. So the code uses an identity column, and joins to that identity column by schema, table, and edgeId, outputting the unique key:

Read on to see how Louis translates the data into the right format for visualization.

Comments closed

Splitting SQL Server Drives on Modern SANs

Published 2021-02-12 by Kevin Feasel

Chris Taylor checks up on some older advice:

Back in the day, “when I was a lad“, the recommendation for SQL Server was to split your data, logs and tempdb files onto separate drives/luns to get the most out of your storage. Jump forward to 2021, is this still relevant and should I be splitting my SQL Server drives on to separate luns on new SAN storage? A question which is often asked not just by customers as well as their 3rd party managed service providers / hosting companies. This question can also be along the lines of, “Why can’t we just put everything on a C:\ because the backend is all on the same lun“. This is slightly different as they’re questioning the drive lettering more than creating separate luns but still relevant to this topic.

Click through to learn what Chris has found.

Comments closed

Calculation Groups and Role-Playing Dimensions

Published 2021-02-12 by Kevin Feasel

Martin Schoombee takes a look at using calculation groups with role-playing dimensions:

The 2019 release of Analysis Services (compatibility level 1500) brought about a new feature called Calculation Groups, which makes it easier to apply the same logic to multiple measures without the need to duplicate code. Each calculation group represents an entity (table) with attributes (columns) and attribute values (calculation items), and because of this implementation it can be used to deal with role-playing dimensions as well.

Click through to see how it works, as well as some gotchas to keep in mind.

Comments closed

Azure SQL Database Startup Time

Published 2021-02-12 by Kevin Feasel

John McCormack has a tip for us:

The traditional methods used for to find the start up time for SQL Server don’t work in Azure SQL DB.

I searched high and low to find this and thought I’ve got to share, and hopefully make it search engine friendly. A traditional google or bing search wasn’t bringing up the best way to find this out. I saw a lot of complicated queries to pull data, convert it and estimate start up time using functions and all kinds of magic. 

Click through for the one-liner script.

Comments closed

Power BI Model Documentation

Published 2021-02-12 by Kevin Feasel

Marc Lelijveld reflects on an overlooked part of development:

I strongly believe that it is key to describe everything that you have built as part of your Power BI model. As we all have a hate-love relationship with documenting our work, the external tool that I build to document your Power BI solutions could come in useful. In particular everything you add and only exists in your model is important to describe properly. This documentation is key once you share your data model with others and they try to understand the goal of a measure, column or anything else. Other than that, documentation also comes in useful if you handover your solution to your colleague or client.

Read on to see what you can currently document.

Comments closed

Plotting Multiple Plots in R using map and ggplot

Published 2021-02-11 by Kevin Feasel

Sebastian Sauer gives us a quick solution to plotting one graph per variable:

Say we have a data frame where we would like to plot each numeric variables’s distribution.

There are a number of good solutions outthere such as this one, or here, or here.

When I read this, my first thought was along the lines of, “Why not use facets or something like cowplot?” But then it clicked that this is per-variable plotting, whereas faceting requires you choose a variable and see the plots based on that variable’s distinct values..

Comments closed

Image Sizing in RMarkdown Documents

Published 2021-02-11 by Kevin Feasel

The Jumping Rivers team shares some insight on image creation:

In this series of posts we’ll consider the (simple?) task of generating and including figures for the web using R & {knitr}. Originally this was going to be a single post, but as the length increase, we’ve decided to separate it into a separate articles. The four posts we intend to cover are

– setting the image size (this post)
– selecting the image type, PNG vs JPEG vs SVG
– including non-generated files in a document
– setting global {knitr} options.

Read on for the first post in the series.

Comments closed

More Number Series Generators

Published 2021-02-11 by Kevin Feasel

Itzik Ben-Gan continues a series:

This is the second part in a series about solutions to the number series generator challenge. Last month I covered solutions that generate the rows on the fly using a table value constructor with rows based on constants. There were no I/O operations involved in those solutions. This month I focus on solutions that query a physical base table that you pre-populate with rows. For this reason, beyond reporting the time profile of the solutions like I did last month, I’ll also report the I/O profile of the new solutions. Thanks again to Alan Burstein, Joe Obbish, Adam Machanic, Christopher Ford, Jeff Moden, Charlie, NoamGr, Kamil Kosno, Dave Mason, John Nelson #2 and Ed Wagner for sharing your ideas and comments.

Read on for three more solutions, as well as a re-evaluation of the solutions in the first article.

Comments closed