Friday, December 19, 2014

Big Power BI Updates!

Today Microsoft announced some very big updates to Power BI that will make it more like the product I have been hoping for since it was first announced.

First off the new site has launched in Preview mode with lots of info on all of these new features.

Also today we got the Preview download of the Power BI Designer that allows for the design and viewing of Power BI solutions in a standalone application. Before you get too excited this first Preview release of the Power BI Designer only does Power Query and Power View functions, no Power Pivot or Power Map yet (hopefully those will be added in future releases). You can use existing Power Pivot models as a source, but you
will not be able to update those Power Pivot models until a future release. I think that this new application will finally give us the unified development environment we have been looking for to do Power BI solutions.

Also added today to Power BI is the Preview release of connecting to onsite SQL Server Analysis Services Tabular models. This is a Preview release as well, but can be accessed from the existing Power BI online tools and the new Power BI Designer.

Power BI Dashboards was also added to the feature list along with a few new visualizations (combo charts, fill maps, gauges, tree maps and funnel charts). A bunch of new data sources were also added with built-in support: Github, Salesforce, SendGrid, Zendesk, Dynamics CRM, and Marketo.

And the Power BI app for Apple iPad was announced that it would be available in the Apple AppStore sometime today (12/19). There was still not a link to go directly to the Apple AppStore as of late this afternoon, so this may not show up until after the new year since Apple is shutting down AppStore updates for the holidays very soon.

Reading between the lines a bit on all of this it looks like Power BI is being positioned as a full standalone application that will no longer require Excel 2010/2013, Office 365 or even SharePoint (on-prem or online). I think that this is exactly what Power BI needs to become a much more accepted application in the Enterprise and I for one am very excited about the possibilities now!

Sunday, December 7, 2014

SQLSaturday #362 - Austin #sqlsataustin #sqlsat362

For my first speaking event of 2015 I'll be presenting "Capture Change and Apply It with CDC & SSIS" at SQLSaturday #362 in Austin, TX on January 31st @ 9am. Looking forward to going to a city that I have wanted to visit for many years and going back to the area where I was born (Fort Hood, TX). My family moved from Texas when I was only a few weeks old as my Dad completed his service in the Army and went back into the private sector. I'm going to be flying in early on Friday, January 30 so that I can do some sight seeing for the day.

If you are going to be in the Austin area, it would be great to see you! To register for SQLSaturday #362, just click on this link and I'll see you there!

Sunday, November 9, 2014

PASS Summit 2014 #summit14 #sqlfamily

I'm back home after an amazing week in Seattle for PASS Summit 2014. Love visiting Seattle, especially when there are lots of #SQLFamily there to enjoy it with!

I got into Seattle on Sunday afternoon via train from Portland. Was the first Amtrak train that I have ever been on and was a nice relaxing ride from Seattle to Portland. I guess I thought that I would see more of the coast on the train, didn't realize that it was going more inland, still some great scenary.

The week itself went by really fast with PASS meetings on Tuesday and the conference itself starting on Wednesday. My presentation on SSDT, "What Version of SSDT do I Need?" was on Wednesday afternoon, so it was good that I got that over with early in the week. I presented this session in the first set of Lightning Talk sessions with Brent Ozar, Luke Jian, Mark Vaillancourt, Tim Radney and Craig Purnell. Was great to share the spotlight with all of these speakers in a full room and we got through all of our presentations with time for questions at the end! Tim, Craig and I were all in the same Lightning Talk session at PASS Summit 2013 as well.

Below is the updated version of my slides, with a change to the table of SSDT versions, correcting a small oversight on my part.

With the presentation out of the way I was able to enjoy the other sessions and parties throughout the week and see as many of my #SQLFamily as possible. Overall the week went very well and I'm already looking forward to PASS Summit 2015 back in Seattle on October 27 - 30!

Thanks again to everyone that attended my session and I'm looking forward to an even better 2015!

Saturday, October 25, 2014

SQLSaturday Oregon & PASS Summit 2014 #sqlsat337 #Summit2014

Next week I'll be leaving for SQLSaturday #337 Oregon and PASS Summit 2014 where I will be speaking at both!

SQLSaturday #337 Oregon will be my first trip to Portland, so I'm looking forward to seeing a new city and speaking to the Oregon SQL Server community. My session will be "Date and Time Dimensions for Every Day and Time in No Time" at 1:15pm in Room 204 (keep your eye on the schedule page or Guidebook just in case it changes). I love giving this Date Dimension talk as it is something that every Data Warehouse needs and everyone hates setting them up, so I help with a single script and some advice. If you are going to be in the Portland area on Saturday, November 1st, please make sure you register and I'll see you next Saturday!

After SQLSaturday #337 I'll take the Amtrak from Portland to Seattle on Sunday for my favorite conference, PASS Summit 2014! PASS Summit is a yearly SQL Server conference that has thousands of SQL Server professionals and speakers from across the world. For the second PASS Summit in a row I'll be presenting a session. After attending this amazing conference yearly since 2007 I have become very involved in the SQL Server community and love that I now get to be part of speakers/volunteers that make this event great. I'll be presenting "What Version of SSDT do I Need?" at 3pm on Wednesday, November 5th in Room 606. I'll also be volunteering at the PASS Community Zone, so make sure to stop by and say hi!

Looking forward to seeing everyone in Portland and Seattle in a week and hanging out with my #SQLFamily at both events!

Thursday, September 11, 2014

SQLSaturday #300 and Other Posts

I'll hopefully be flying out of Denver first thing Friday morning for SQLSaturday #300 in Kansas City, assuming we don't get too much snow (currently only forecasting .2", so not too worried). Love visiting with all of my KC #SQLFamily each year and happy that I can get out there early Friday to do the BBQ Crawl! I'll be presenting my "Capture Change and Apply It with Change Data Capture and SSIS" (I really need to shorten that title) talk at 2:15pm on Saturday.

I was happy to contribute my first blog post to the RevGen Partners brand new UpShift blog this week, SQL Server Development Tools. Check it out if you are curious about what versions of SSDT work with what in the different versions of SQL Server.

I also had my review of Microsoft BI posted to the TrustRadius site as well today, "Why is Microsoft BI Right for Me?". I tried to present as fair of a review as I could based on my 10+ years of experience with the tools.

Monday, August 25, 2014

Denver SQL Server User Group August Meeting

It was great to present at the Denver SQL Server User Group again after a year plus on my birthday, last Thursday (8/21). I'm up in front doing the introductions and running the meetings every month, so no need to also present there all of the time too. :)

I presented a 30 minute version of the Date Dimension talk that I have done in many different forms over the last year (including a 10 minute version at PASS Summit 2013). Below is the link to download the slides and zip file with the SQL script to create a Date Dimension in seconds.

Thanks again to everyone that attended the meeting, and looking forward to seeing you all again at the September meeting on 9/18. Also, don't forget the Denver SQLSaturday is coming up on 9/20 and we only have around 30 open registrations left before we start up the waitlist. Also there are 3 great pre-cons on 9/19 that only cost $125 for a full day of training. Go to to register for both!

Sunday, July 27, 2014

Mile High Tech Con - Demo/Slides #MHTechCon

Thank you to everyone that attended my "Capture Change and Apply it with Change Data Capture and SSIS" session at Mile High Tech Con on Saturday. I have uploaded the zip file that has both the demo solutions and the slides.

Thanks to Marc Beacom and all of the other volunteers at the Mile High Tech Con, was a great event and glad that there will be another technical event here in the Denver area each year!

Monday, June 30, 2014

Mile High Tech Con #MHTechCon

My speaking calendar is really filling up! :)

I have been selected to speak at the Mile High Tech Con that will be held on July 24-26 at the Auraria Campus in Denver, Colorado! I will be presenting "Capture Change and Apply it With Change Data Capture & SSIS" on Saturday, July 26 @ 1pm. This is the first time this event has been put on in Denver and has 3 days of great content, Thursday and Friday are paid, longer sessions and Saturday is all free sessions. If you are in Denver that week, please check it out and if you use the promo code 14SK_SteveWake when you register for Thursday and/or Friday and save some money!

Looking forward to seeing everyone at this event in a few weeks!

Wednesday, June 25, 2014

Speaking at PASS Summit 2014! #Summit14

I am honored and thrilled to announce that I was selected for the 2nd year in a row to present a session at the PASS Summit! PASS Summit is an annual conference put on by PASS (Professional Association of SQL Server) with thousands of attendees from across the world, this year it is being held on November 4 - 7 in Seattle, WA. There will be over 200 different sessions presented by speakers from the community and Microsoft on a wide range of SQL Server topics in 5 different tracks. If you are interested in attending, please use this link to register and be sure to use the code UNSUMF59 when you register to get $150 off your registration fee (if you use it by this Friday 6/27 you will save a total of $450 off the full registration fee).

I will be doing a Lightning Talk (short 10 minute presentation) titled "What Version of SSDT do I Need?", which is a small part of the "Building a Better Workstation" presentation that I have already presented a couple of times. I'm sure that it will be a fun session as there are currently a lot of questions/issues with what versions of SSDT work with what in each version of SQL Server.

Looking forward to seeing everyone in Seattle in November!

Saturday, June 14, 2014

BIG PASS and Next Week

Last Thursday evening I had the pleasure to present virtually to the BIG PASS group in Irvine, CA. BIG PASS is run by Mickey Stuewe (blog | Twitter) whom I have meet at the last 2 PASS Summits and multiple other PASS events over the years, was a great time presenting to the group and would love to do it again in the future. I presented my "Building a Better Workstation" session for the first time and to be honest it ran longer than I expected, just so much good stuff to talk about and some great questions from the audience as well. As promised, below is a link to download the slides for this presentation so that you can use all of the links provided in the slides.

Next week will be very busy for me! On Wednesday evening I will be doing presentation for the Denver .NET User Group called "Business Intelligence for the .NET Developer". Looking forward to talking to this group and talking about how my career changed from being a .NET Developer to a BI Consultant and how I think others could do the same (if they want to). If you are in the downtown Denver area on Wednesday evening around 5:30pm MT, please register and stop by!

Update: 6/19/2014 - Thanks to those that attended the Denver .NET group last night, below are the slides from that presentation:

On Thursday morning I will be doing the FREE Pragmatic Works webinar series "Training on the T's" again @ 9am MT/11am ET. I will be presenting the "Building a Better Workstation" session and try my best to get it into the 45 minute timeframe! This is a free webinar, so register here if you are available at 9am MT/11am ET on Thursday. If you can't attend at that time there will be a recording of the whole session posted at a later time and I will update this post with that link once it is available. Looking forward to doing another session for this great series!

Update 6/19/2014 - Thanks to everyone that attended my Pragmatic Works "Training on the T's" session "Building a Better Workstation" the slides are the same as in the first attachment above in this post, so feel free to download them. The video link for this should be posted in the next few days, I'll post that link here as soon as I get it.

Then on Thursday evening will be the Denver SQL Server User Group June meeting at 5:30pm MT in the Microsoft DTC office (7595 Technology Way, Suite 400, Denver, CO 80237). Andy Vold (Twitter) will be presenting "SQL 2014 In-Memory OLTP – What, Why, and How" and Chris Shaw (blog | Twitter) will be presenting "What is the Best Disaster Recovery Solution?". For more details on both of these sessions and our group, please go to All of our meetings are FREE and we try to make as much time for networking as possible at every meeting.

Friday I'm planning on attending my first TDWI meeting at the June Denver meeting. Should be a good meeting with Laura Reeves presenting "Data: The Real Power Behind Today’s Technology". If you are interested in attending this meeting on Friday from 1pm - 4pm MT at Innovation Pavilion (9200 E Mineral Ave, Centennial, CO 80112) make sure you register at this link for this FREE event.

Overall a very busy week for me, but that is way better than just sitting around bored! :)

Tuesday, June 10, 2014

PowerPoint Export for SSRS Reports

Recently I put together a review of options available to export SSRS reports to PowerPoint. For those that have used SSRS for a while you know that it supports many different export formats including PDF, Excel, CSV, XML, TIFF, HTML and more recently Word and Atom data feeds. From those formats you can handle most capabilities that users are looking for, but a major hole still exists for use in PowerPoint. Since PowerPoint is part of the Microsoft Office suite you would think that Microsoft would want to cover all of the bases, especially after they added Word as an export format with SSRS 2008R2. But even as of SSRS 2014 there is still no native support to export to PowerPoint in SSRS. The below evaluation was meant to be as exhaustive as possible, but I'm sure there are some other ways that it can be done with automation or other means that I missed as it gets to the point where it just more and more steps to get it done.

After I created this documentation I did find that it is possible to use the Atom data feed to create a Power View report using that feed and then take advantage of the native PowerPoint export that is built into Power View. There is some documentation on this on TechNet, but this option requires SharePoint 2010 Enterprise with SSRS in integrated mode and Excel 2013 to put it all together. There are also some capabilities in Word/PowerPoint to embed Objects that reference the SSRS reports, but this is very similar to the LiveWeb option and I had a bunch of issues getting it work on my test system, so I chose not to mention it in the options below.

If you have found any other ways to get SSRS reports exported to PowerPoint, please post in the comments below and share it! There is a Microsoft Connect ticket that was created back in 2007 to add PowerPoint as a native export that is still active, if you want to see if we can get it added make sure to vote it up!


Custom Renderer
SSRS Excel
Easy to Install/Configure
Ease of Upgrading with SSRS
Report Changes Necessary
Pages on Separate Slides

* Assuming renderer includes all capabilities
+ Lower rating when there is a cost ($ and/or man hours) separate from existing SSRS license

3rd Party SSRS Rendering Extensions
There are 2 possibilities that I found for this option, Aspose.Slides and LiveWeb. Aspose.Slides is a full SSRS rendering extension that works with all current versions of SSRS and adds multiple formats of PowerPoint export to ReportViewer (as shown in screenshot below):

Aspose.Slides supports all types of reports (tables, matrix, charts, graphs, etc.) that can be built in SSRS and will work with Report Builder. Some formatting of the reports will need to be taken into consideration to paginate properly in the PowerPoint exports, not that different than what has to be thought about for PDF export (make sure to setup landscape, margins, etc.). The install is very easy and for most installs does everything automatically even with multiple instances on the same computer. There is a cost associated with this as it is provided by Aspose, it is hard to tell which pricing structure will work best as is depends on the number of developers and servers they would plan to roll it out on. This page on Aspose’s site shows the pricing at a high level. This is the only option that will provide full PowerPoint export capabilities out of the box without extra steps.

LiveWeb is a freeware add-on for PowerPoint that allows you to embed a browser window in any PowerPoint slide. With this add-on a PowerPoint slide can be setup to point to a specific report on the SSRS server using the URL access built-in to SSRS to render the report inside of the slide. Because this is a browser embedded in the slide there will be no pagination as SSRS when accessed via a browser will assume that you do not want to see pagination and will make the whole report available by scrolling up and down in the browser window. This does also provide the capability to interact with the report within even the PowerPoint when it is in slide show mode, but if this is just being used to show static slides and each page is expected to be on a separate slide, this option will not work for that scenario. The install is very easy and works with all versions of PowerPoint from 2007 and forward. No changes will be required for the SSRS reports to work with this add-on.

Build Custom SSRS Rendering Extension

SSRS was built with the ability to extend/enhance all parts of the product by writing custom .NET code to do whatever the user would like if it is not something provided out of the box. While these extensions to SSRS can be very easy to write for some parts of the product, it can be extremely difficult for others. I have written a custom security extension for SSRS in the past and it only took a day or two to fully code and test. When I researched this for writing a custom rendering extension (Microsoft considers anything that shows the SSRS report in another form a renderer, even if it is only for export) the MSDN article specifically states that it is difficult and “requires you to implement hundreds of classes, interfaces, methods, and properties.” The other major issue with writing a custom rendering extension is there is no guarantee that the extension you write for SSRS 2012 will work with SSRS 2014 or any other future version as Microsoft is always changing the API for SSRS and those changes may break what previously worked fine. I saw this first hand with the security extension that I wrote originally for SSRS 2005, that had to be re-written for SSRS 2008R2. With custom rendering extensions the code has to know how to handle anything someone will put into an SSRS report, so that is what makes it that much more difficult to write. Because these extensions are written in a .NET language (C# or VB.NET) it will require a full license for Visual Studio as the BIDS (Business Intelligence Development Studio) and SSDT (SQL Server Developer Tools) do not include the capabilities to create C# or VB.NET projects. With Visual Studio 2013 it is possible to use the Express version that is free for this kind of development.

Use included export formats (PDF, Word, Excel, CSV, XML, TIFF, HTML)

It is possible to use the export formats that come with SSRS and still allow them to be shown in PowerPoint. Most of these options will require multiple steps to complete and may require manual intervention to complete successfully, but they will not cost anything extra in dollars to implement. The easiest to setup is using the Word export that is new with SSRS 2008R2, with this export you can easily copy/paste the Word version into a PowerPoint deck or even use the embedding of Word documents directly into PowerPoint (Word exports may have issues with pagination depending on the report and how it is formatted). With both the Word and Excel exports it would be possible for SSRS to be setup to automatically export the required reports to a file share on the network on a pre-defined schedule and then have the links to those files already setup in the PowerPoint decks and only require someone to open those decks and refresh them to get the latest version of the data from the reports. The other export formats supported by SSRS are not very helpful for embedding in PowerPoint. While it would seem that the TIFF (image) format would work, as it is just an image and it does generate an image per page, the issue I found in testing is that PowerPoint does not understand how to handle multiple pages in a TIFF image when importing it into a slide, so only the first page is imported. It would be possible to use another application to separate this single TIFF file by page, but that would be an extra step/process (freeware Image Magick can do this split via command line).

Monday, June 2, 2014

Pick Me! :) #NomCom #PASSVotes

I am running for one of the seats on the PASS Nomination Committee (NomCom) to help select the candidates for this year's PASS Board of Directors election later this year and to help streamline the election process. As a member of the NomCom I want to make sure that I represent each person that is part of the SQL Server/PASS community, so please take a few minutes to go to my profile page and then vote for me when you receive the email on Tuesday, June 3rd from PASS!

Over the years I have increased my involvement with PASS as a volunteer in many ways, which are highlighted below:
As I have increased my involvement with PASS as a volunteer I have enriched myself and others in ways that I would never have imagined. I was recognized by my peers last year with the PASS Outstanding Volunteer award for November 2013, which was an amazing acknowledgement from them. I saw the opportunity to run for the PASS NomCom as my next step in volunteering for the PASS community.

If you would like to see me represent each of you on the PASS Nomination Committee, please vote for me from June 3 - June 6 using the email you receive from PASS! I look forward to helping each of you have a voice in this very important process for the PASS community. If you have any specific questions for me or that you want to make sure I ask to the candidates, please reach out to me through email, Twitter or LinkedIn.

Wednesday, May 28, 2014

June Presentations

I have a few presentations that I'll be doing in June that I wanted to highlight here in case anyone is in the area or attending these meetings.

First up on June 12th, I'll be doing a new session that I created "Building a Better Workstation". This session is a collection of a bunch of my lessons learned trying to figure out the best strategy for working with OSs and SQL Server installs on workstations and laptops over the years. I will be doing this presentation remotely for the BIG PASS group run by Mickey Stuewe (blog | Twitter).

On June 18th, I'll be doing another new session titled "Business Intelligence (BI) for the .NET Developer" for the Denver .NET Meetup Group. This session will help .NET developers and anyone that is just starting out in IT know what Business Intelligence is and some ways that you may not of even thought of for getting into BI. Should be a fun session that I really hadn't thought of putting together until Andreas Taber (blog) requested I present at his group. I will be doing this presentation in person.

On June 19th, I'll be doing another presentation as part of Pragmatic Works' Free Training on the T's. This time I'll be doing the "Building a Better Workstation" session that I will do the week before for BIG PASS. Always happy to do a presentation as part of this free training series, will be strange now doing it as an employee of another company though.

Further out in my calendar, I have been selected to speak at SQLSaturday #312 in Sacramento, CA on July 12th. I don't know what session I'll be presenting yet, but as soon as I find out, I'll let you all know! This will be my first time in Sacramento, so looking forward to spending a couple of days out in that part of CA with the #SQLFamily.

Monday, May 26, 2014

SSDT-BI for VS 2013 Update

Not too long after I posted on my blog about the release of SSDT-BI for Visual Studio 2013 the download was pulled with a message that it would be fixed in a few days. After a few weeks, the updated download was made available on Friday. Not sure what the issues were with the original version, as I was not yet using it regularly (not lucky enough to work with any client going to SQL 2014 yet).

If you did grab that download when it was available the first time, you should upgrade to this newer version. The update did not fix any of the backwards compatibility issues with SSIS that I highlighted in my original post. Microsoft is saying that they have fixed the issue that was causing these breaks with backwards compatibility so that newer versions of SSIS and SSDT-BI will work with each other going forward. Based on how that was stated it does not mean that they will be able to fix the issues with 2008R2 and 2012 not working in latest versions of SSDT-BI, just that newer SSDT-BI releases should work with SSIS 2014 and later going forward.

Friday, May 23, 2014

S3OLV May 2014 Meeting

Thanks to Jason Brimhall (Twitter - Blog) and Stacia Misner (Twitter - Blog) I had the opportunity to present a session to the SQL Server Society of Las Vegas (or S3OLV, love that acronym) last night. I presented my newly renamed "Date and Time Dimensions for Every Day and Time in No Time" talk, which I used to do as a Doctor Who themed presentation for the 50th Anniversary last year. Session went fine and as mentioned at the end of the session I wanted to make sure that my scripts to create your own date dimension in SQL and slides are available to everyone that wants to use them. Hopefully these will give you a good start on getting a date dimension up and running for your data warehouse. If you have any really cool updates that you find for the script or errors that I have missed, please let me know so that I can share it with others as well!

Friday, May 16, 2014


Seems like only a little while ago that I posted on this blog about moving on to work at Pragmatic Works, but it has now been over 2 years! It has been an amazing 2 years and probably the most challenging time in my entire career. Over the last 2 years I've had the chance that I think many would love to have working with some of the best in the SQL community and doing some really cool stuff for many different clients.

I decided recently that I was having some issues with the amount of travel that I was doing, especially to the East coast. While it is great that I have had the opportunity to see my Dad twice in the last 6 months because of travel to the East coast (he lives in Philadelphia) it is very draining on me and my family. So, I started looking for something local in Denver. I wanted to stay in consulting, if possible as I really love to see different client environments on a regular basis that challenge me to learn with each new client. I also needed to work for a company that understands being involved in the SQL community and backs it up with more than just words.

With that criteria I was able to get a new consulting position with RevGen Partners! I know a bunch of people that currently work and previously worked for them, so it was easy for me to judge how good of a company they are from them. I'm starting with them Monday and looking forward to starting a new adventure. I'm also hoping that I can keep up with all of my friends/co-workers at Pragmatic Works especially the ones that I will see at other SQL events in the future. Thanks to Brian, Adam, Mike and Bradley and many others for an amazing 2 years and I know that I will be seeing each of you again.

Monday, April 28, 2014

SQLSaturday 285 Atlanta - #sqlsatatl

SQLSaturday #285 in Atlanta is this Saturday! I will be attending #SQLSATATL for the first time and presenting my "SSIS 2012: More Than Just a Pretty UI" session at 2:30pm in the ETL Room #211 (please check the Schedule page on the website or the Guidebook app just in case it changes). This is one of my favorite sessions, since it only has 2 slides, the rest is all demos! If you haven't seen the new features added in SSIS 2012, this session will give you a demo of as many of them as I can get through in the time allocated.

If you are going to be in the Atlanta area this Saturday, you should register and attend this FREE event that is put on by volunteers with speakers from all over. SQLSaturdays are always a great event to learn new stuff and do a lot of networking with your fellow peers! Also, don't forget to stop by the sponsor area and see what they have to offer, without these awesome sponsors we would not be able to put on these great events.

See you in Atlanta!

Friday, April 18, 2014

Power Map Updates

Microsoft just announced on the Power BI blog that the April update for Power Map is available for all Office 365 subscribers. This update adds the capability to add sound to any Power Map videos you export out of Power Map. They have also added some Geocoding updates that are detailed on the blog post as well.

The biggest change that is mentioned is that the Power Map Preview that has been available as a separate download for Excel 2013 will no longer expire on May 31, 2014! They plan to put out an updated version of this standalone add-in that will not expire in May. They are still recommending that this add-in not be used in production environment, but this does give you the capability to use Power Map without an Office 365 account in Excel 2013. Not sure what this means feature wise for Power Map Preview, my guess is that they will not keep it up-to-date, for that you will have to get an Office 365 subscription (highly recommended by me, as I have the Home subscription that allows for 5 desktop installs along with 5 tablet and the new iPad versions of Excel, Word and PowerPoint).

Accelerate Your Insights

If you missed the live stream of the SQL Server 2014 launch event in San Francisco earlier this week, it is now available for on-demand viewing on Microsoft's Accelerate Your Insights page. Great overview of the new data platform and BI updates that are all part of this new release of SQL Server and other platform updates. Interesting news that came out of this event was the re-branding of PDW (Parallel Data Warehouse) as APS (Analytics Platform System) as it now is not limited to just hosting a PDW region on the appliance but also a Hadoop region on the same appliance. This should help to sell these appliances even more as you can use the same appliance for both "un-structured" and structured data and be able to do analysis across both regions using the same tools we are used to including Excel.

The biggest feature that is being focused on for SQL Server 2014 is having In-Memory available on the entire platform and the huge performance improvements that brings. In-Memory was first introduced in SQL Server 2012 but was a read-only index, now it can be clustered and fully update-able, which makes it a huge change to how you can access the data in the databases. Looking forward to working with clients to enable these features as it really does push SQL Server to the forefront of enterprise databases.

Note: If you have any issues with the videos not streaming smoothly (as I had) you can just right click on the video window and save the MPEG-4 video files locally.

Thursday, April 10, 2014

SQLSaturday #297

SQLSaturday #297 in Colorado Springs is this coming Saturday! If you haven't registered yet, why not? It's a free event that has the best SQL Server professionals traveling from all over to present and attend this awesome event. If you able to make into Colorado Springs by Saturday, you should be at the event! I will be presenting "Capture Change and Apply it With Change Data Capture & SSIS" at 3:15pm (keep your eyes on the schedule page or use the Guidebook app to track any changes to the schedule). I'm looking forward to seeing everyone this Saturday, make sure you register and support all of the sponsors of this event when you are there.

Below are some more details from Chris Shaw's blog about the food drive that is also being done as part of the event:
What we are asking is that when you come to the event on April 12th that you bring with you food items that you would like to donate the more items that you bring the better your odds are in taking home one of these awesome SWAG items. We will soon be sending everyone who is signed up more details on individuals can participate in our food drive. You don’t have to bring any food items to be registered for the giveaways and there will be opportunities to participate in our Data-Rodeo for additional chances to win. Why a Data-Rodeo you ask? This year’s theme in going to be centered around the old west, our event will be taking place in a historical building that is over 100 years old, in the heart of the oldest part of town.
See y'all on Saturday!

Wednesday, April 2, 2014

SSDT-BI for Visual Studio 2013

Microsoft quietly released SSDT-BI for Visual Studio 2013 as a separate download a couple of days ago: No blog post or anything yet that goes through all of the features, so my assumption is it just adds the same functions that SSDT-BI for Visual Studio 2012 does for the latest version of Visual Studio (at least until later this year when Visual Studio 2014 is released). I also assume that this adds support for SQL Server 2014 BI, same as the previous updates for SSDT have done now for Visual Studio 2012 and 2013.

Just for review, SSDT-BI is for SSAS, SSIS and SSRS development. For database projects, they have now built that functionality back into all versions of Visual Studio 2013 (available as a separate download for Visual Studio 2012) and can be updated via Visual Studio 2013 directly. For more updates on SSDT, make sure you follow the SQL Server Data Tools Team blog.

Update: At about the same time I posted this Kasper de Jonge posted about SSDT-BI for Visual Studio 2013. Biggest disappointment is that there is NO backwards compatibility with SSIS 2012 or SSIS 2008R2 projects, so if you are only doing SSIS development on previous versions of SQL Server, don't jump on this upgrade just yet. No explanation was given for this incompatibility or if it will be fixed in a future update. Just keeps getting more and more complicated.

Wednesday, March 26, 2014

SSDT SQL Server 2014 Update

Microsoft has announced that a new version of SSDT for Visual Studio 2012 is now available for download and that an update for Visual Studio 2013 will be released through the update tools built into VS 2013. This new version adds support for SQL Server 2014, which will be available next Tuesday (April 1st). Along with support for SQL Server 2014 there are some new features in SSDT, including:

  • Filtering Data when using View Data - filter the rows shown and even hide columns along with applying aliases to columns and sorting the data by columns
  • Azure Integration - Add and connect to Azure databases without needing to setup firewall rules or other complicated network setup
  • Change T-SQL Query Connections - Change the database that the query window is current attached to so that you can execute the same query against another database without having to disconnect and connect

There are few other new features that I have not mentioned, make to check out the SQL Server Data Tools Team blog for more details!

This release is for the data tools portion of SSDT only, SSDT-BI for SSAS, SSIS and SSRS development will be released separately at a later date.

Tuesday, March 25, 2014

Encryption in SQL Server (Part 4)

Finally, the last part in my Encryption in SQL Server series that I started at the end of 2013! If you have not been following along, please see Part 1, Part 2 and Part 3 before reading this post.

Now that you have data in your databases encrypted and hopefully you have automatic backups setup against these databases, you are probably wondering how you can restore these backups while maintaining the encryption. If you are restoring to the same server and you have not made any changes to the Service Master Key (SMK) it will all work seamlessly.

If however you are restoring the backup or attaching a database with encryption to another server that does not have the same SMK you will need to take a couple of extra steps to get the encrypted data visible and encrypt new data working. Keep in mind that the SMK is the key that is generated at the time the SQL Server instance is installed on the server and you have no control over how that key is created. This SMK is used as part of the whole encryption setup on the server, so you may think that all is lost, but Microsoft has put in place some commands that will still allow you to access the encrypted data and encrypt new data into that restored/attached database.

After restoring/attaching the database to the new instance, run the commands below in order.

Restore Service Master Key (SMK), but only if this instance has no other encryption currently setup on it. If you do have encryption already on this instance, just skip this step and move on to restoring the DMK and modifying it to use the SMK (based on backup sample shown in Part 1):
    FROM FILE = 'C:\localhost_SMK.smk'
    DECRYPTION BY PASSWORD = 'ADa329wopkj*&ER.slkqksl';
 Restore the Database Master Key (DMK):
RESTORE MASTER KEY FROM FILE = 'C:\localhost_AdventureWorks_DMK.dmk'             DECRYPTION BY PASSWORD = 'U982LKJOWlkslpq&^@#lskjnkxOPx.w'                     ENCRYPTION BY PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn'
Next we want to alter the DMK to use the SMK for encryption, otherwise the DMK will have to opened every time we want to do any encryption/decryption:
    DECRYPTION BY PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn';
Now we need to create the Asymmetric and Symmetric Keys that we created previously in Part 2, in the same way they were created previously as we could not back up these since they were not created in certificates (if you did decide to create these keys with certificates and you have the back ups, then you can just restore the certificates):
 (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'AdventureWorks_AsymKey')
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'AdventureWorks_SymKey')
    CREATE SYMMETRIC KEY AdventureWorks_SymKey
        WITH KEY_SOURCE = 'I82xskoiw820KOW>282kxow',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = 'w,xi292XWE82S92iqoxl*&23'
        ENCRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey; 
It is important that you use the same KEY_SOURCE and IDENTITY_VALUE when re-creating the Symmetric Key as this and the Asymmetric Key were used to do the initial encryption of the back-up and if these values are not the same the decryption of the previous data will not work.
Once all of these steps are complete you will be able to use the same commands we used in Part 3 to verify that the decryption works correctly:
SELECT Phone_encrypt, Phone,
CONVERT(nvarchar(25),DecryptByKeyAutoAsymKey(AsymKey_ID('AdventureWorks_AsymKey'), NULL, Phone_encrypt, 0)) AS 'CustomerPhone_decrypted' FROM Person.Contact;
SELECT Phone_encrypt, Phone, CONVERT(nvarchar(25),DecryptByKey(Phone_encrypt))) AS 'CustomerPhone_decrypted' FROM Customer.Contact; 
CLOSE SYMMETRIC KEY AdventureWorks_SymKey;
With these steps and the previous posts in this series you should be able to take full advantage of encryption in SQL Server and have full control of that encrypted data on any of your servers/instances. With all of the data breaches that are occurring today it is even more important for you to make sure any sensitive information you have in your database is protected and this is a great way to do it in SQL Server without causing a major impact to you existing systems.

Tuesday, March 18, 2014

SQL Server 2014 RTM Announced

Microsoft officially announced today on the SQL Server Blog that SQL Server 2014 will be released on April 1st! Always find it funny that Microsoft will release products on April Fools Day, but to them I guess it is just the beginning of another month and quarter for them. For those that are just getting upgraded to SQL Server 2012, now you will already be one version down after April 1st.

In past years this release would have been called SQL Server 2012 R2, but thankfully they have decided to drop the R2 (Release 2) naming on SQL Server products (it is still being used on Windows Server and other server products). See the official blog entry for a list of the new features, mostly just enhancements to the features added in SQL Server 2012. The best new feature that I see is the enhancement to In-Memory ColumnStore Indexes making them updatable! There is also the feature of being able to backup right to Azure, which is great for providing instant off-site backups for your databases.

If you want to be notified of the official release, click here and fill out the form.

Tuesday, March 11, 2014

Upcoming SQLSaturdays

Sorry for the lack of posts over the last few weeks, it has been very busy, but I will get the last post in the encryption series up very soon.

Wanted to spread the word about a few SQLSaturdays that are coming up, which I will be at!

SQLSaturday #295 - Las Vegas, NV (4/5/14)

This is the first SQLSaturday in Las Vegas and while I did not get one my sessions selected to present, I will still be in Vegas that weekend thru Wednesday (4/9/14). Was a great excuse to take some time to visit Vegas, which I haven't been back to in around 10 years. Looking forward to seeing how the Vegas crew puts on a first time event and meet up with all of the #SQLFamily in a very fun city! If you are going to be at SQLSaturday #295, I'll see you there!

SQLSaturday #297 - Colorado Springs, CO (4/12/14)

Glad to see the Colorado Springs SQLSaturday back on the schedule again after taking a break for a year. Also very happy to be presenting at the event again this year. I'll be presenting my CDC with SSIS 2012 session "Capture Change and Apply It With Change Data Capture & SSIS" later in the afternoon (schedule is always possible to change, so make sure to check beforehand for exact time and room). The organizers are going to do a food drive at this event as well, to help out the local food banks which have been very stressed after all of the wild fires last year. The group down in the Springs always puts on a great event, so please register to attend this event if you are going to be in the Colorado Springs area on 4/12!

SQLSaturday #285 - Atlanta, GA (5/3/14)

The Atlanta SQLSaturdays are typically the biggest in the country (maybe even the world), so I'm very honored to be speaking at this event for the first time! Looking forward to spending some time in Atlanta, instead of just transferring from one flight to another at the airport. I'll be presenting my review of the new features in SSIS 2012 session "SSIS 2012: More Than Just a Pretty UI" in the afternoon. As mentioned above, make sure to check the schedule on this page to verify when and what room I'll be presenting in. If you are going to be in Atlanta on 5/3, register for this great event with over 50 sessions!

Best thing about all 3 of these events, they are all FREE! These events are held throughout the world and put together by community volunteers that want to share with everyone the SQL Server technology that we all love. Also be sure to talk to the sponsors at each event as it would be impossible to put on these events without their support. For those in the Denver area, we are working on the announcement for our 4th SQLSaturday in the coming weeks!

Friday, February 14, 2014

PASS BI Virtual Chapter Presentation February 2014

On Thursday, February 13th I had the pleasure to present my "Master the Date Dimension Like a Time Lord" presentation for the PASS Business Intelligence Virtual Chapter. Was a great group of attendees that had lots of questions, which hopefully I was able to answer to their satisfaction. Also had a few attendees that mentioned they were also big Doctor Who fans as well, which always makes the presentation fun to do when there are those that get the references to the show.

I have presented this session multiple times before, but I keep changing it as I go based on comments I receive. So, since the content changes, below is the link to download the latest version of the slides and the script to build the Data Dimension table as I demo in the presentation.

If you come up with something that you think is really cool added to this script, please send it to me and I can pass on your updates to others as I present this again in the future.

Thanks again to all of those that attended. If you missed the session there should be a recording of the session available soon, and as soon as I get word that it is posted I will update this post with that link.

3/1/2014 - Recording of the session has been posted on the PASS DW/BI VC YouTube page.

Monday, January 27, 2014

SQLSaturday #271 Albuquerque

Saturday I had the privilege to present a session at SQLSaturday #271 in Albuquerque, NM. This was the 2nd year in a row that I have presented at the SQLSaturday in Albuquerque and I love going back each time because Keith Tate, Meredith Ryan and the whole ABQSQL crew do a great job putting on this event. This year the event was in a different location that was pretty amazing looking and the spaces they made available were great too. My session was at the very beginning of the day at 8am, so I had to make sure I didn't party too much the night before at the Speaker/Volunteer party. Speaking of which, that party was held at a local micro brewery and we even had a room that with a glass wall so that we could see all of the beer vats. Beer was great and I even shared this with my wife and son who traveling with me to this event to make it into a mini-vacation from Thursday - Sunday.

Was awesome to spend a few days in ABQ this time as I drove in for last year's event on Friday and left right as the event finished on Saturday. This time with the family we were able to go up to the Sandia Peak Tramway, visit Old Town Albuquerque and Petroglyph National Monument. All great places to visit and always nice to learn about another area of the country!

Back to the SQLSaturday event itself, things started a bit slow (it is a Saturday morning so this is expected), but quickly there were over 100 attendees going to sessions throughout the day. Since my session was first thing I expected that I would have a lower attendance, and while I only had 10 attendees in the room they all commented to me afterwards that they enjoyed it and learned something new, so that makes it all worthwhile! Here is a link to download the file that all of the content from my "Master the Date Dimension Like a Time Lord" session. For those that were not at SQLSaturday #271 and may have seen this presentation in the past, I have updated it to make the script to generate the date dimension for both SQL 2008 and SQL 2012. I also took sometime to clean up some of the code to be more SQL centric instead of Oracle that it was originally written for. I will continue to tweak this script and the presentation over time, so keep your eyes here for future updates to the scripts!

Thanks again to the Albuquerque SQL group and I look forward to coming back again in the future!

Thursday, January 23, 2014

Encryption in SQL Server (Part 3)

Now that we have the database setup for encryption as shown in Parts 1 and 2 of this series, it is time to actually encrypt the data in the database and view the data in decrypted form.

The great thing about all of these encryption capabilities being built into the SQL Server engine is that the commands that you use to encrypt/decrypt the data is very easy, in fact it is using the same commands you are already using to with some minor differences.

Before updating the data a new column that is in VARBINARY datatype will be required, since that is the datatype required for encrypted values:
ALTER TABLE Person.Contact
   ADD Phone_encrypt VARBINARY(128);
The length of the VARBINARY column will vary based on the length of the data you are encrypting and the type of algorithm being used. To find out what length of VARBINARY will work best for you, it is a good idea to setup a test where you populate the maximum length value of the column you are going to encrypt and do the encryption into a VARBINARY(MAX) column and use the DATALENGTH function to find out what the length of encrypted data comes to. Hopefully you are not encrypting BLOB data or other very large columns, in most cases a VARBINARY(128) or VARBINARY(256) will cover you, but setup the test as described if you have any doubts as you don't want to truncate the encrypted data.

The command to populate the new encrypted value column is a simple UPDATE command that you are already used to:
UPDATE Person.Contact SET Phone_encrypt = EncryptByKey(Key_GUID('AdventureWorks_SymKey'), Phone);

CLOSE SYMMETRIC KEY AdventureWorks_SymKey;
This will replace the values that are in the column specified in the UPDATE command with the encrypted version of that value using the Symmetric Key. Depending on the amount of data in your table and the specs of the computer/server this operation should not take very long to complete, in most cases no longer then it takes to perform that same UPDATE command without the encryption.

Notice the OPEN and CLOSE commands that are wrapped around the UPDATE, those are required as you need to make the Symmetric Key that you are using for encryption available and this is done by opening it. For good practices and security is best to then close the key after you have completed the work that you need to do.

Now that you have encrypted the data in that column, lets decrypt the data and see if comes back as the same value that you originally encrypted.

There are a couple of different ways to write the SELECT statement that does the decryption, first is using the Asymmetric Key directly:
SELECT Phone_encrypt, Phone,
CONVERT(nvarchar(25),DecryptByKeyAutoAsymKey(AsymKey_ID('AdventureWorks_AsymKey'), NULL, Phone_encrypt, 0)) AS 'CustomerPhone_decrypted' FROM Person.Contact;
And the other way is to open the Symmetric Key as was done above to perform the UPDATE and use it to do the decryption:
SELECT Phone_encrypt, Phone, CONVERT(nvarchar(25),DecryptByKey(Phone_encrypt))) AS 'CustomerPhone_decrypted' FROM Customer.Contact;
CLOSE SYMMETRIC KEY AdventureWorks_SymKey;
Either way you do it you notice that you will have to convert the decrypted value returned into a datatype that you can use, as both the DecryptByKeyAutoAsymKey and DecryptByKey will return a VARBINARY datatype. The DecryptByKeyAutoAsymKey function is very useful in views as it doesn't require the OPEN/CLOSE statements.

It is also worth mentioning the GRANT statements that will be required to give the appropriate rights to be able to see this decrypted data for users.
Notice that CONTROL has to be granted to the Asymmetric Key and only VIEW DEFINITION is required on the Symmetric Key. These are the lowest grants necessary for the keys to work for any user needing to execute either of the SELECT commands shown above. The decryption functions will not work unless the appropriate grants are issued on BOTH the Asymmetric and Symmetric Keys.

Now that we have encrypted data in the database and verified that the encryption works by decrypting that same data via a SELECT statement I will use the 4th and final part of this series to show you how to restore backups of a database that contains encrypted data on another server and still be able to decrypt the values.