Press "Enter" to skip to content

Curated SQL Posts

RStudio Connect

Published 2017-02-13 by Kevin Feasel

Jen Underwood discusses RStudio Connect:

RStudio officially introduced the newest product in RStudio’s product lineup: RStudio Connect. RStudio Connect is a new publishing platform for R that allows analytics users to share Shiny applications, R Markdown reports, dashboards, plots, and more. This release adds an improved user experience for parameterized R Markdown reports, simple button-click publishing from the RStudio IDE, scheduled execution and distribution of reports, and more security policies include hybrid data connections. Essentially RStudio Connect eases enterprise deployment scenarios.

Between what Microsoft is doing with its old Revolution Analytics holdings and what RStudio is doing, this is a great time to be an enterprise R customer.

Comments closed

Temporal Tables

Published 2017-02-13 by Kevin Feasel

Alex Grinberg has a tutorial on temporal tables, including combining temporal tables with In-Memory OLTP:

Although the process of converting an In-Memory Optimized OLTP table to a system-versioned table is similar, there are some differences that we need to cover and demonstrate in this section.

You need to be aware of some specific details when converting the in-memory optimized table to the system-versioned table

Read on for those specifics.

Comments closed

Troubleshooting SSRS 4xx Errors

Published 2017-02-13 by Kevin Feasel

Jeff Pries explains how to troubleshoot various Reporting Services configuration errors:

After installing SQL Server Reporting Services (SSRS), are you receiving an Error 404, Error 400, “Invalid Request” error, or “Bad Connection” error on first visiting the SSRS web portal (the error message seems to vary based on version, browser, and whether accessing via http/https or /reports vs /reportserver) ?

I’ve run into this a few times so I’m listing the steps I’ve used to fix it.  For me, the root cause of this error has been the SSRS Configuration Wizard automatically configuring SSRS to use HTTPS, but assigning an invalid machine SSL Certificate.  The fix is to self-generate a new and valid SSL certificate for the SSRS website to use.

Jeff then provides step-by-step instructions.

Comments closed

Scaling Up R

Published 2017-02-13 by Kevin Feasel

Ginger Grant explains how to use SQL Server R Services to take advantage of server resources instead of running from your local machine:

Microsoft’s R Server contains some specialized functions which are not part of the standard CRAN R installation. One of the ScaleR functions, RxInSqlServer will allow code to be processed on the server from the client. To make this work, you must have R Server and R Client installed. If you are doing a test on a local machine, you will need both R Client and R Server installed on that computer.

Click through for a script which walks you through the process.

Comments closed

Blocked Process Report: monitorLoop

Published 2017-02-13 by Kevin Feasel

Michael Swart explains what the “monitorLoop” attribute is on the blocked process report:

You won’t find too much explanation about that field in the official documentation but I believe I know what it means.

The blocked process report is closely tied to deadlock detection and it’s generated by the same process as the deadlock monitor. If you remember, the deadlock monitor runs frequently looking for deadlocks (which are just blocking chains in a circle). It runs every couple seconds when there are no deadlocks, and if it detects any, it runs a bit more frequently. Each time it runs it’s called a monitor loop. The monitorLoop is just a number that starts at zero when the server restarts and increments by one each time the monitor runs.

Read on for more details.

Comments closed

Always Encrypted Data And SSMS

Published 2017-02-13 by Kevin Feasel

Justin Goodwin shows how to view Always Encrypted data from within Management Studio:

I am now able to view the encrypted data. Why is this?

Because I am connecting to the database from the database server, I have access to the Encryption Certificate that was generated. I can verify this by opening certmgr.msc and browsing to Personal -> Certificates:

Justin then goes on to show what happens when you don’t have access to the certificate and how to provide access to another machine.

Comments closed

More Dynamically Changing Shapes In Power BI

Published 2017-02-10 by Kevin Feasel

Koen Verbeeck has a follow-up post regarding dynamic shape changing in Power BI:

Yesterday I published the blog post Dynamically Changing Shapes in Power BI, which seemed to be quite popular in social media. It showed a lot of people were struggling with this issue and that some people had also found (alternative) solutions for it. In the comments of that blog post, you can find a solution proposed by Jason Thomas (blog | twitter). Jason has quite the Power BI & SSRS knowledge and he has found a cool trick to solve our changing images problem. In this blog post, I’m going to explain the solution step-by-step.

Click through to see the solution.

Comments closed

High-Compression JSON With vNext

Published 2017-02-10 by Kevin Feasel

Jovan Popovic gives an example of 25x compression of JSON data using a clustered columnstore index in vNext:

CLUSTERED COLUMNSTORE INDEXES provide extreme data compression in SQL Server and Azure SQL Database. With NVARCHAR(MAX) support in CCI indexes you can use them on your JSON data stored is database and get high 25x compression. Therefore, CCI is a perfect solution if you need to store a large volume of JSON data in your SQL Database.

ContosoDW database is publicly available for download, so you can use this database and the script below to re-create this table and try this in your environment.

I’m curious whether this will also apply to non-JSON data.

Comments closed

Finding Disproportionate Outliers With DAX

Published 2017-02-10 by Kevin Feasel

Rob Collie uses DAX to find disproportionate sentiment on a per-group basis:

My wife loves to travel AND she loves data, so it’s no surprise that she showed me the infographic in question.

“Oh come on, California residents don’t visit the Philippines more often than any other country.”  That was my first reaction.  (Mexico is the overwhelming #1 destination, basically for every state… because Cancun.  And because there are so many Mexican-Americans).

“Ah…  but California residents DO visit the Philippines disproportionately more often than they ‘should,’ according to national averages!”  That was my dawning second realization (and confirmed by the fine print of the Orbitz article, even though the article’s title suggests otherwise.)

So, how do we do that in DAX?  Pretty simply, actually.

I’m mentally working out whether this could be useful in anomaly detection.

Comments closed

Spark Data Frame Checkpoints

Published 2017-02-10 by Kevin Feasel

Jean Georges Perrin introduces checkpoints on Spark data frames:

Basically, I use a checkpoint if I want to freeze the content of my data frame before I do something else. It can be in the scenario of iterative algorithms (as mentioned in the Javadoc) but also in recursive algorithms or simply branching out a data frame to run different kinds of analytics on both.

Spark has been offering checkpoints on streaming since earlier versions (at least v1.2.0), but checkpoints on data frames are a different beast.

This could also be very useful for a quality control flow:  perform operation A, and if it doesn’t generate good enough results, roll back and try operation B.

Comments closed