Why Have A Date Dimension

Thomas LeBlanc discusses reasons for having a date dimension in a data warehouse:

The date dimension can also contain columns for Weekend versus Weekday, Holiday and month markers like 2014-10 or by quarter like 2014-Q1. All these can be computed once in the dimension table and used at will by query writers. They now do not have to know how to use T-SQL functions or concatenate substrings of “CASTed” date columns.

Then, when the DimDate is related to various Fact tables and processed into an OLAP cube, the measures and aggregations are displayable side by side through the DimDate dimension which is now considered a Conformed Dimension. The slicing and dicing of data has just been made a whole lot easier.

I’d go a step further and say that every instance should have access to a tally table and a date table.

Don’t Panic

Robert Davis describes how he recovered from accidentally deleting a production-necessary LUN:

That’s right, I did it. I was deleting unused LUNs and the focus somehow jumped from the LUNs for the one database onto a LUN for another database. I blame an errant mouse-click. As soon as I deleted the LUN, I realized the name was wrong. The LUN held the log file for a production database.

Fortunately for me, the database was in an Availability Group so after recreating the LUN and attaching it, I restored the database from the read-only secondary.

The biggest disservice you can do to yourself in a situation like this is to panic.  Stop, think it through, and act once you have a plan.  Even better, plan ahead:  try one of these disasters in a non-production environment and see how the team reacts, what you need to do to bring it back up, and how you can improve next time (which might be in prod).

Thinking Docker

Matt Smith thinks about Docker and SQL Server:

At one of my local user groups, Suffolk Devs, back in Sept 2015, Richard Vickerstaff done a talk on Docker. One of the things I took away was this was very dev friendly, Richard was honest in the fact he has yet to deploy to production in this manner and the general feedback from the room at the time was no one else had – this doesn’t mean no-one has since of course, but you start to get the feeling the DBAs back home wouldn’t be happy. The other thing was the dev nature of it, for example, when your developing, you don’t want to be held back by “rules” that protect data, it is after all, development, your not going to have production data in your dev environment, right? So, if you don’t “pin” your storage to a persistent path, it’ll get purge when you stop your docker image. Can you image if you forgot to set the production config correctly and come a reboot all your data disappears? I can already here my friendly DBA screaming.

I think that within a few years, non-Dockerized SQL Server will be considered quaint.  “You mean you had to install and configure it each time?”

Full-Text PDF Search

Jon Morisi shows how to use Full-Text Search to read PDF files:

Faced with this very issue, I decided to setup a local SQL Server Full-Text Search.
Some of the cool things Full-Text Search will give you, over and above, a standard search include the following:

  • One or more specific words or phrases (simple term)
  • A word or a phrase where the words begin with specified text (prefix term)
  • Inflectional forms of a specific word (generation term)
  • A word or phrase close to another word or phrase (proximity term)
  • Synonymous forms of a specific word (thesaurus)
  • Words or phrases using weighted values (weighted term)
In order to get stared with the setup, it’s important to know that the Full-Text Search architecture relies on filters for searching various file types.  This is important for this example because the PDF filter is not installed by default.  So, for starters, we need to go download and install the PDF ifilter(PDFFilter64Setup.msi).

Up until I read this blog post, I had no idea that full-text search could index PDFs, so that’s very interesting.

Retention Analytics

Patrick LeBlanc shows collegiate retention data using Power BI:

Partnering with Stetson University, I am happy to share the first of many Power BI Higher Education Analytics solutions. This solution shows student persistence, retention, and graduation patterns, leveraging BANNER as the data source. Year-over-over retention and graduation rates can be filtered to allow deeper examination of trends at the college and major level. Additional views, including retention and graduation rate tables by major and ethnicity, are included within the report solution.  The entire solution with documentation can be downloaded here.

The following image shows the first view within the report: overall persistence, retention, and graduation rates by year of first time student cohort. This report allows users to quickly show institutional retention and graduation trends across time, with the option to filter the view to show only specific colleges and/or majors.

This also serves as a Power BI demo, in case you’re hurting for good examples.

Power BI Custom Visuals Course

Devin Knight is starting a free course on custom visuals in Power BI:

Welcome to an exciting new FREE class that I am launching today!  Over the next year (that’s right year!) I will be releasing one module a week detailing how to work with all of the Power BI visuals available in the Custom Visuals Gallery.  You might ask why am I doing this?  Well The Microsoft Power BI team and the Power BI Community, through the Custom Visuals Gallery, have expanded the data visualization capabilities of Power BI drastically but unfortunately has provided little and in some cases no direction on how to use these the new features.  These Custom Visuals are designed by Microsoft on occasion but more often then not the Power BI Community has put in a lot of hard work to provide these great new features for everyone to use.  My thought is if the Power BI Community is willing to design and publish these without asking individuals for payment then I would love to provide training on these features to you for free as well.

This sounds like a nice course.  Good on Devin for doing this.

Upgrading To RTM

John Sterrett walks us through upgrading an RC of SQL Server 2016 to RTM:

Please read the release notes. There actually might be a few things you need to complete before upgrading to GA especially if you are using Stretch Databases.

Everyone, should validate if they need to apply KB 3138367. msvcr120.dll should be version 12.0.40649.5 or higher.

This is a nice walkthrough with a lot of screen shots, making it easy to follow.

SQL Server 2016 RTM On Azure

Jens Vestergaard points out that SQL Server 2016 RTM is now available as an Azure VM:

As of today, the latest release of SQL Server is available as a Virtual Machine on Microsofts Azure Platform.
In a matter of minutes you’ll be able to try out all the new featuresthat was added.

If you can’t provision a local server and have Azure credits, this is another way to use SQL Server 2016.

SQL Server 2016 Features

James Serra gives us several methods for obtaining SQL Server 2016 and some of the new features:

Today is the day: SQL Server 2016 is available for download!  You can download all the versions(enterprise, standard, web, express with advanced services, express, developer) of SQL Server 2016 now if you have a MSDN subscription, and you can also create an Azure VM right now that includes SQL Server pre-installed with one of the versions (enterprise, standard, web, express).  Lastly, you can also experience the full features through the free evaluation edition (180 days) or the developer edition(you have to sign in to Visual Studio Dev Essentials, a free developer program, before you can download the developer edition).

Even though it’s a day old now, it’s not too late to grab a copy…

2016 Licensing Changes

Brent Ozar points out a couple of EULA changes with SQL Sever 2016, including sending telemetry results automatically:

Before 2016, you had to manually opt-in by checking a checkbox during installation.

With SQL Server 2016, there’s no checkbox – you’re opted in by default.

I’m actually a huge fan of app telemetry – sending crash reports and usage data back to the application developers in order to help make the app better. I want developers to know how I use their apps, because I want them to improve the parts of the app that I use the most. Heck, I’d be fine if SSMS turned on the microphone while I worked, and then did sentiment analysis. (They would see a very high number of four-letter words tied to the term “IntelliSense.”)

I’m generally fine with sending telemetry results, but I also think the option to disable this should be easier than a registry setting.

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930