Press "Enter" to skip to content

Day: September 18, 2020

Correlation and Predictive Power Score in Python

Published 2020-09-18 by Kevin Feasel

Abhinav Choudhary looks at two methods for understanding the relationship between variables:

dataframes while working in python which is supported by the pandas library. Pandas come with a function corr() which can be used in order to find relation amongst the various columns of the data frame. 
Syntax :DataFrame.corr() 
Returns:dataframe with value between -1 and 1 
For details and parameter about the function check out Link 
Let’s try this in action. 

Read on to see how it works, how to visualize results, and where Predictive Power Score can be a better option.

Comments closed

The Power of AUC

Published 2020-09-18 by Kevin Feasel

John Mount takes a deeper look at Area Under the Curve:

I am finishing up a work-note that has some really neat implications as to why working with AUC is more powerful than one might think.

I think I am far enough along to share the consequences here. This started as some, now reappraised, thoughts on the fallacy of thinking knowing the AUC (area under the curve) means you know the shape of the ROC plot (receiver operating characteristic plot]. I now think for many practical applications the AUC number carries a lot more information about the ROC shape than one might expect.

Read on for the explanation.

Comments closed

Connecting to an API with Username and Password in Power Query

Published 2020-09-18 by Kevin Feasel

Gilbert Quevauvilliers has a challenge:

In the blog post I am going to show you the steps that I took to get data from the XE.COM API which uses a username and password to log into the API

You might be thinking that I could put in the username and password when I used the Web Connector.

My challenge is that I wanted to create a function that I could pass through multiple currencies to the API. And in order to do that I wanted to store the details within the function.

Read on to see how Gilbert solves this.

Comments closed

Working with Keys and Certificates using dbatools

Published 2020-09-18 by Kevin Feasel

Mikey Bronowski concludes a series on using dbatools to replace SQL Server Management Studio functionality:

There are multiple security-related objects that are not easily accessible via SQL Server Management Studio. The first one would be Service Master Key, if exists, can be seen under the master database. Luckily, dbatools can help us to take a backup.

Click through for the details. If you’ve missed any of Mikey’s posts, here is the full list of posts.

Comments closed

Optimize for Unknown with Inline Table-Valued Functions

Published 2020-09-18 by Kevin Feasel

Koen Verbeeck hits on a strange case:

Turns out SQL Server used a plan with a hash join in the fast query, and a nested loop in the slow query. Due to SQL Server also wildly using incorrect estimates, the nested loops performs really poorly. Quite similar to parameter sniffing with stored procedures. Erik Darling has written a great article about it: Inline Table Valued Functions: Parameter Snorting.

The thing is, in contrast to scalar functions or multi-statement table-valued functions, the iTVF should have better performance because it will be expanded into the calling query. This way, SQL Server can use “more correct” estimates and create a plan for each different parameter. Well, today was not that day.

Read on for details on how Koen performed troubleshooting and the solution.

Comments closed

Understanding the Size of a Power BI File

Published 2020-09-18 by Kevin Feasel

Lazaros Viastikopoulos explains that finding the size of a Power BI file isn’t quite as easy as you’d think:

As you can see from the above screenshot, the size of the PBIX file is 1.2 MB (1,238 KB) by looking at the size in file explorer. Also, when we look at all the files we are ingesting into Power BI, the total size comes to 2.8 MB. However, when we ingest data into Power BI which is processed to memory, it can be bigger than the PBIX file size due to some compression that occurs and various other elements which again, are not displayed through the disk.

So, when we are getting various errors notifying us that our Power BI file is too large and we see that it is under the 1GB restriction on disk, it can leave us scratching our head. But, as I mentioned above due to compression and various other elements, once data is processed to memory it can be larger then what we see on disk, but still below the total size of the data source we are ingesting due to the compression.

Click through for information on two tools you can use to determine the in-use size.

Comments closed