Press "Enter" to skip to content

Author: Kevin Feasel

Analysis Of Fantasy Sports Using Spark

Published 2016-06-10 by Kevin Feasel

Jordan Voiz knows how to get to my heart:

Although the data involved is not large in volume, the types of data processing, data analytics, and machine-learning techniques used in this area are common to many Apache Hadoop use cases. So, fantasy sports analytics provides a good (and fun) use case for exploring the Hadoop ecosystem.

Apache Spark is a natural fit in this environment. As a data processing platform with embedded SQL and machine-learning capabilities, Spark gives programmatic access to data while still providing an easy SQL access point and simple APIs to churn through the data. Users can write code in Python, Java, or Scala, and then use Apache Hive, Apache Impala (incubating), or even Cloudera Search (Apache Solr) for exploratory analysis.

Baseball was my introduction to statistics, and I think that fantasy sports is a great way of driving interest in stats and machine learning.  I’m looking forward to the other two parts of this series.

Comments closed

Don’t Ignore .NET Framework 3.5 Just Yet

Published 2016-06-10 by Kevin Feasel

Shaun J. Stuart notes that database mail apparently still requires .NET Framework 3.5:

However, I did run into one issue later on regarding database mail. I was able to configure it correctly and was even able to send a test email through SSMS without any errors. Unfortunately, the email was never delivered. Additionally, the database mail logs did not show database mail starting or attempting to send the message. I checked with my Exchange administrator and he said he never saw the SQL Server connecting to the mail server.

A check of the database mail table in msdb (sysmail_allitems) showed my messages sitting there with a status of Unsent.

After troubleshooting for a couple of hours and getting nowhere, we installed in the .Net Framework 3.5 and suddenly database mail started working.

This, hopefully, is a bug.  But not getting e-mail alerts you’re expecting to receive can be a scary scenario.

Comments closed

Generating Bulk Import Files

Published 2016-06-10 by Kevin Feasel

Cody Konior shows us how to create a SQL Server bulk insert format file using Powershell:

Don’t forget there are a few minor security considerations:

  • Your login needs Administer Bulk Operations permission.
  • Your AD account needs access to the file (and possibly delegation enabled for remote shares)
  • Or if using an SQL login the database engine service account needs access to the file.

Anyway now that we have the data in table format without worrying about ordering or duplicate column names, we can much more easily manipulate it and store it into the database.

I remember creating a couple of these by hand, and that was no fun.  I never created enough to get the hang of the syntax or to want to automate the process, but at least I know where to look if I ever have to do this again.

Comments closed

MDX Keyword Colors In SSDT

Published 2016-06-09 by Kevin Feasel

Shabnam Watson notes that MDX keyword coloring is available in SQL Server Data Tools for Visual Studio 2015:

The MDX keywords in a cube script were not properly changing colors in the SQL Serve Data Tools (SSDT) Preview version for Visual Studio 2015. (14.0.60316.0). (See my previous post on this problem here.)

The different keyword colors make it easier to write, organize and read an MDX script inside a cube.

Looks like they squashed that bug.

Comments closed

Views Within Elastic Query

Published 2016-06-09 by Kevin Feasel

Grant Fritchey shows how to build Elastic Query views to obscure underlying table names:

Creating a view, or any other query, that joins across databases using Elastic Query works just fine. However, if you want to mask things using a view, you might need to get a little creative in how you implement Elastic Query. The good news is, Elastic Query is somewhat, shall we say, elastic in how you set it up. More so than it immediately appears.

Interesting.

Comments closed

In-Memory And Temporal Table Properties

Published 2016-06-09 by Kevin Feasel

Slava Murygin has updated his script to include memory-optimized and temporal tables:

There is also new column called “Temporal”. It indicates if a table is System Versioned or it is Historical repository. Row_Count number for Historical repository indicates number of changes done to the main table. That option is applicable only for SQL Server 2016.

I’ve updated only “Ctrl-F1” button, which returns list of tables within a database. Here is the link to the full script archive:https://drive.google.com/open?id=0B5yWoyX1eEWqZ3FJUnNHZm80bzQ

Check out his script.

Comments closed

More Licensing Notes

Published 2016-06-09 by Kevin Feasel

Joey D’Antoni has additional information on licensing for SQL Server 2016:

SQL Server 2016 launched last week to great reviews and with a ton of great new features. I have been working with this version for well over a year now and extremely happy to see it hit RTM and be broadly adopted. So as DBAs it always sucks when you get excited about new features, only to find out the price changed, or vendor “O” made that feature a cost option. So what’s new with SQL Server 2016 licensing? (you won’t this as a session title at any upcoming SQL Server events).  Well first the good news—SQL Server 2016 is the same price and 2012 and 2014 (roughly $6800 core for Enterprise Edition). That’s definitely good news—Microsoft gave us a bunch of new functionality and didn’t raise the price. Additionally, if you see my below post on what is in Standard Edition, they added a lot of functionality there, too.

But we know finance and marketing employees have jobs to do as well, and there is no way they were letting a major version release happen without some changes. So let’s take a look at the one’s Denny Cherry (b|t) and I could glean out of the licensing guide. Please download and read for yourself.

There are a couple of interesting nuances that you’ll want to read up on.

Comments closed

Changing The SSRS Display Language

Published 2016-06-09 by Kevin Feasel

Regis Baccaro shows how to change the user language in SSRS 2016:

Changing it to Spanish and refreshing the browser changed the SSRS user language to Spanish without me having to add a language at all.

To change the user language of SSRS 2016 you need only to change the Formatting Region setting from the control panel – nothing else !

The issue I tend to have with this is that different tools tend to behave differently when you start changing the format settings.  I recall Excel being particularly finicky about it.

Comments closed

New Diagnostic Queries

Published 2016-06-09 by Kevin Feasel

Glenn Berry has a new update of his diagnostic queries, focused around SQL Server 2014 and 2016:

This month, I have several improvements in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. One new feature is a series of six dashes after each query to make them easier to parse by people who use PowerShell to run the queries and collect the results. I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will be doing that pretty soon.

If you don’t already use Glenn’s queries, I highly recommend them as a starting point in tracking down issues.

Comments closed