Press "Enter" to skip to content

Day: January 25, 2019

Building Credit Scorecards

Published 2019-01-25 by Kevin Feasel

Andre Violante uses SAS to build credit scorecards and analyze credit data:

For this analysis I’m using the SAS Open Source library called SWAT (Scripting Wrapper for Analytics Transfer) to code in Python and execute SAS CAS Action Sets. SWAT acts as a bridge between the python language to CAS Action Sets. CAS Action Sets are synonymous to libraries in Python or packages in R. The one main difference and benefit is that the algorithms within these action sets have been highly parallelized to run on a CAS (Cloud Analytic Services) server. The CAS server is a distributed in-memory engine where I can do all my heavy lifting or computations. The code and Jupyter Notebook are available on GitHub.

Click through for the analysis.

Comments closed

Disentangling Nested Functions

Published 2019-01-25 by Kevin Feasel

Brent Ozar takes us through a realistic but nasty scenario:

When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch.
However, there are some cases where I just can’t work around a perfect storm of anti-patterns. To understand why, let’s take an example. I’ve kept the general idea the same, but I’ve rewritten the entire example in the Stack Overflow database to protect the innocent.

I’ve seen cases similar to what Brent has. Developers understand encapsulation and minimizing code repetition, so they naturally want to do that with SQL, but the optimizer eventually gives up and picks a terrible plan. DRY is great for application code and normalization, but unfortunately, it’s not always great for T-SQL.

Comments closed

Automating Azure SQL Database Log Analytics

Published 2019-01-25 by Kevin Feasel

Joey D’Antoni shares a Powershell script which automates deployment of Log Analytics for Azure SQL Database:

However, I’m working with a customer who is building their own service based on Azure SQL Database, and I have fully automated their database deployment process. I wanted to take this a few steps further and add the SQL Analytics step as part of our deployment. This was harder than I expected it to be—the code samples in the books online post above weren’t working in my environment. And furthermore, once I got it working, I was having failures in my Azure Automation Runbook once I got the code running in the PowerShell ISE (I was having issues using VS Code on my Mac).

Joey takes us through the problems and provides a working script.

Comments closed

Query Store Bugfixes

Published 2019-01-25 by Kevin Feasel

Erin Stellato has a roundup of Query Store bugfixes as of January 2019:

The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade.  As of this writing, the current CU for SQL Server 2016 SP2 is CU5, and for SQL Server 2017 it is CU13.  Many times we see fixes that make it into a SQL Server 2017 CU ported back to a SQL Server 2016 build.  Interestingly enough, there are some Query Store fixes in 2016 CUs that are not in 2017 CUs.  I don’t know if that’s because the issues do not exist in 2017, or if it’s just that they have been fixed yet in 2017.  I’m planning to update this post if the fixes are added down the read.  So here we go, in descending CU order…

This post is a great reason to keep those SQL Server instances up to date.

Comments closed

Multi-File Power BI

Published 2019-01-25 by Kevin Feasel

Marc Lelijveld shows us how to break up a single Power BI desktop file into several:

Normally a Power BI Desktop file (PBIX) contains your queries, data model, and reports (visualization). Looking at a multi-file strategy, we split this up into two (or more) files.

The first file only contains the queries and data model. The second file is directly connected to the first one (direct query) and reads the data model. The file it selves includes all report content like visualizations, booksmarks and everything related to that. By working this way, you will be able to build multiple reports based on the same dataset.

Click through for a demonstration.

Comments closed