A Fine Slice Of SQL Server
Shane O’Neill makes a discovery:
…if there is enough data to import this into the database & use T-SQL then you can bet that’s what I’m going to do! It’s what it was designed for, I’d find it easier, and it’s probably going to be faster after you hit a certain threshold.
However, if it’s small sets and the effort of importing the data is going to slow you down and break your flow…
Well, that doesn’t have to be the case anymore.
Read on to see joins in action.
Comments closedCedric Charlier shows how to perform time zone conversions with the M language in Power Query:
Everything is fine … except if I share my code with someone from another time zone. The function
DateTimeZone.ToLocalis relying on regional settings and in that case my conversion should always be from UTC to “Brussels time”.I didn’t find any other way to ensure that I’m always converting from UTC to “Brussels time” than implementing the conversion by myself. That’s the goal of the following function
Looks like there may not be a nice “convert to a different time zone” here like lubridate::with_tz() does in R.
When I have an issue with tempdb filling up the first thing that I usually do is try to figure out exactly what the space has been allocated to.
You can quickly figure out what process has the most space allocated by using a quick query against dm_db_session_space_usage.
SELECT session_id, database_id, user_objects_alloc_page_count + internal_objects_dealloc_page_count AS TotalAllocatedPages FROM sys.dm_db_session_space_usage ORDER BY TotalAllocatedPages DESCBut what if you can see that there aren’t any pages allocated to sessions? What could be taking up all the space? Well let’s have a little look and see exactly where those pages are allocated.
Click through to see David’s results and explanation.
Comments closedMy account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.
Steve shows us where you can go to disable this if you want, as well as places where you can see what content has been shared.
Comments closedEugene Meidinger hunts down where those local Power BI credentials live:
With SSIS, you have to be careful to export the SSIS files without any sensitive information included. But what about Power BI? If you save the .PBIX files on OneDrive, can you be exposing yourself to a security risk?
Looking at things, it looks like credentials for data sources are stored globally, so one wouldn’t expect them to be in the .pbix files.
Read on as he does some more sleuthing and discovers the answer.
Comments closedTyler Rinker digs into ggplot2’s geoms and aesthetics:
I thought it my be fun to use the geoms aesthetics to see if we could cluster aesthetically similar geoms closer together. The heatmap below uses cosine similarity and heirarchical clustering to reorder the matrix that will allow for like geoms to be found closer to one another (note that today I learned from “R for Data Science” about the seriation package [https://cran.r-project.org/web/packages/seriation/index.html] that may make this matrix reordering task much easier).
It’s an interesting analysis of what’s available within ggplot2 and a detailed look at how different geoms fit together with respect to aesthetic options.
Comments closedSusan Li has a series on multi-class text classification in Python. First up is analysis with PySpark:
Our task is to classify San Francisco Crime Description into 33 pre-defined categories. The data can be downloaded from Kaggle.
Given a new crime description comes in, we want to assign it to one of 33 categories. The classifier makes the assumption that each new crime description is assigned to one and only one category. This is multi-class text classification problem.
* Input: Descript
* Example: “STOLEN AUTOMOBILE”
* Output: Category
* Example: VEHICLE THEFT
To solve this problem, we will use a variety of feature extraction technique along with different supervised machine learning algorithms in Spark. Let’s get started!
Then, she looks at multi-class text classification with scikit-learn:
The classifiers and learning algorithms can not directly process the text documents in their original form, as most of them expect numerical feature vectors with a fixed size rather than the raw text documents with variable length. Therefore, during the preprocessing step, the texts are converted to a more manageable representation.
One common approach for extracting features from the text is to use the bag of words model: a model where for each document, a complaint narrative in our case, the presence (and often the frequency) of words is taken into consideration, but the order in which they occur is ignored.
Specifically, for each term in our dataset, we will calculate a measure called Term Frequency, Inverse Document Frequency, abbreviated to tf-idf.
This is a nice pair of articles on the topic. Natural Language Processing (and dealing with text in general) is one place where Python is well ahead of R in terms of functionality and ease of use.
Comments closedJohn Mount shows a few techniques for legible function chaining with R:
The dot intermediate convention is very succinct, and we can use it with base
Rtransforms to get a correct (and performant) result. Like all conventions: it is just a matter of teaching, learning, and repetition to make this seem natural, familiar and legible.
My preference is to use dplyr + magrittr because I really do like that pipe operator. John’s point is well-taken, however: you don’t need to use the tidyverse to write clean R code, and there can be value in using the base functionality.
Comments closedNext we need to create a stored procedure that will accept JSON text as a parameter and insert it into the table. Two important points here:
JSON text must use the NVARCHAR(MAX) data type in SQL Server in order to support the JSON functions.
The OPENJSON function is used to convert the JSON text into a rowset, which is then inserted into the previously created table.
The whole process is quite easy; check it out.
Comments closedMark Wilkinson continues his Bash for the Powershell developer series:
An expression is a statement that evaluates to either true or false. In our case we are just checking if the
$hour_of_dayvariable is less than 12 and greater than or equal to 5. When comparing numeric values you must use the same operators you are familiar with from PowerShell:-lt-gt-le-ge-ne. When comparing string values you have to use operators you might be more used to from other languages:==!=and both values must be enclosed in double quotes[ "string1" == "string2" ].
With just the things Mark has shown so far, you can begin to build helpful scripts.
Comments closed