Building Credit Scorecards

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.

Disentangling Nested Functions

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.

Power BI Secure Embed

Patrick LeBlanc has a new Guy in a Cube video:

In this video, Patrick looks at the new Power BI Secure Embed. If you have been using Publish to Web internally, you need to look at this feature as it is the perfect replacement that adds a secure option for easy, frictionless, embedding.

The video weighs in at about 7 minutes; check it out.

Automating Azure SQL Database Log Analytics

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.

Query Store Bugfixes

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.

Multi-File Power BI

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.

Categories

January 2019
MTWTFSS
« Dec Feb »
 123456
78910111213
14151617181920
21222324252627
28293031