Press "Enter" to skip to content

Day: June 2, 2016

Why Have A Date Dimension

Published 2016-06-02 by Kevin Feasel

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.

Comments closed

Don’t Panic

Published 2016-06-02 by Kevin Feasel

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).

Comments closed

Thinking Docker

Published 2016-06-02 by Kevin Feasel

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?”

Comments closed

Full-Text PDF Search

Published 2016-06-02 by Kevin Feasel

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.

Comments closed

Retention Analytics

Published 2016-06-02 by Kevin Feasel

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.

Comments closed

Power BI Custom Visuals Course

Published 2016-06-02 by Kevin Feasel

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.

Comments closed

SQL Server 2016 Features

Published 2016-06-02 by Kevin Feasel

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…

Comments closed

2016 Licensing Changes

Published 2016-06-02 by Kevin Feasel

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.

Comments closed