Traveling, Talking, and a PASS Attack Have Happened

Well, this fall has been crazy. As you may have noted from a couple of my previous posts, link to speaking and pass attack, I have been busy this fall. With multiple speaking engagements and additional travel for work, I have been on the road more than I have been home. Rather than handling each event independently, I will give some synopsis here.

SQL Saturday #235 – New York City 2013

This was my first trip to New York City which is kind of ironic since I was born in Avoca, New York and lived there for five years. I spoke on Building BI Solutions with Excel 2013. During the presentation, we had an energetic conversation about pushing more of the BI work to business users. My take is that those users know how to interpret the data better than the technology staff. Ironically, they are doing it already thanks to the prolific “Export to Excel” functionality available in most BI tools. The event was a lot of fun to participate in.

SQL Saturday #238 – Minnesota 2013

This was the first SQL Saturday in Minnesota that I did not have a lot of other responsibilities beyond speaking. I felt a little guilty, so I brought my sixteen year old son, Alex, and he volunteered in my place. Turns out he did awesome and had a great time as well. I spoke in back to back sessions to open up the day. The first session, Beyond Power Pivot – Building Tabular Model Solutions, covered the development of tabular model databases using SQL Server Data Tools and the “enterprise“ features only available in Analysis Services. The next session was “Power to the User: a Look at Power BI”. This session was a lot of fun and is a whirlwind demo of the Power BI features in Excel 2013. This was a huge event with around 400 attendees and 8 tracks. Kudos to the organizing committee for a job well done.

PASS Summit 2013

Summit this year was a good event. Due to a myriad of work related crisis, I was unable to enjoy the conference as much as I had hoped. We discussed the role of the user groups in SQL Saturdays during an organizer’s meeting. I hope that we begin to see the control of these events go back to user groups rather than treated as unrelated to user groups. As the event progressed, I was able to attend a few sessions. I am still asking for the single button install of SharePoint BI, but for now I will live with the 80 page install doc. One thing I like was having the vent in a warm location. That was definitely more enjoyable than a cold, damp Seattle fall. I still look forward to the next Summit and hope to see many of you there next year.

SQL Saturday #237 – Charlotte BI Edition 2013

Immediately following Summit was this SQL Saturday. There were a lot of big time speakers that came from the Summit and they still let me talk. This session was on using Power Pivot and Power Query to test ETL processes built with SSIS. The best part was when my computer died during the demo. When I got it restarted, an error message came up on SSIS and a number of people in the room gasped. We all knew that I would not be able to fix that right away, some moved on with half of the demo working. It all turned out okay as I had screenshots in the deck to show the changes. Overall, it was a good event and gave me a chance to see some peers one more time.

CodeMastery – Atlanta

This was the first of two CodeMastery events within a week of each other. This was a lot of fun for me as I was the keynote speaker and had the opportunity to speak about the Changing World of Business Intelligence. Having been trained as a preacher, this was the closest to that type of speaking I have done in some time. We had a good, interactive audience for this event. I also presented on Power BI at this event, once again introducing a lot of people to what Microsoft is doing with BI these days.

CodeMastery – Minneapolis

Back home in Minneapolis, I participated in this CodeMastery as a speaker as well. At this event, I once again discussed the power of Power BI and Microsoft’s strategy. We had about 80 at this midweek event which was great.

Live 360 – Orlando

At Live! 360, I had the opportunity to speak at two of the 360 events – Modern Apps Live! and SQL Server Live! This event was a lot of fun to participate in. I had the opportunity to speak five times between the two events. The part that made all of this interesting was losing my harddrive that weekend. I had to rebuild demos and recover presentations all week long. Through it all I was able to get all but one presentation’s demo up and running. Funny thing though, I tweaked the demo, and it worked partly during the presentation. Nice job by 1105 to put this together. I look forward to being at the Vegas event in March.

CodeMastery – Boston

This event was also supposed to have happened by now, however, it was rescheduled to next week. If you are in the Boston area at the time, register for the event and join us for some free training. Hopefully, I will see you there.

As I wrap up this post, I have to say a big “Thank You” to my family who has had to put up with a lot of travel over the past few weeks. Without their love and support this would have been a really rough time.

Turkey Image Source: http://bit.ly/1bBFIF4

PowerPoint–My Dashboard and Report Design Tool

At some point I think that I am becoming a Microsoft Office specialist as opposed to a BI Architect.  All of this work in Excel and now PowerPoint.  Okay, done with the ramblings.  As I have noted in a couple previous posts, I am working with a team on the Modern Apps Live! conference which is in Vegas next week.  Well, this is another “lesson learned” that I wanted to pass along as a result of doing that work.  (Hope to see you there.)

Using PowerPoint 2013

So I had to create two types of data visualizations for this conference.  Usually, I would use paper or white board to sketch it out and then proceed to make it a reality.  Somewhere along the way, I heard that Microsoft uses PowerPoint to lay out UIs.  Not sure if it is true or not, but it seemed easier and less expensive than Blend or Visio, so I thought I would give it a try.

So, I first needed to create a summary report for a poll within the app that was created.  I used the standard tools with in PowerPoint such as tables, charts, text boxes, and images to mock up my report.  What I liked was I was able to add notations to the mockup for future reference.

I had some frustration creating the charts as I wanted them to be representative.  But overall not a bad experience.  The next task I was taking on was working with the dashboards I was going to create in Excel 2013.  I still wanted to lay it out so I knew what I would be trying to design.  This was when I stumbled onto the Storyboarding menu.

I actually like using the shapes in this toolset better.  Turns out this is available when you install Visual Studio Ultimate, Visual Studio Premium (my version), or Visual Studio Test Professional.  More on that can be found on MSDN – Storyboard Using PowerPoint.  This can be integrated into TFS and directly associated to work items.  I am not a UX expert, but I like the ability to add tabs like I will have in Excel and there is even a SharePoint page background.

However, as you can see, even if you don’t have Storyboarding you can still effectively build up a PowerPoint slide to look like the report, dashboard, or even SharePoint page.  I was not sure if I would be able to embrace this, but in the end I really like the simplicity and using PowerPoint allows for comments, versioning in SharePoint, and other mechanisms to support dashboard design.

I also wanted to pass along another blog post I found from Jason Zander on the Windows Azure team on the same subject:  My Favorite Features: Creating Storyboards with PowerPoint.  Hopefully this gives you another simple way to mock up reports and dashboards when you can’t find that User Experience Pro.

Steps to Preload Data into Tables with SSDT

I am working as the data architect and developer on a modern app build with a the team from Modern Apps Live! in Vegas.  The goal of the project is to provide guidance to build modern applications and use this application as a reference.  While the conference is focused on the why of the build, we have learned some interesting things about how as well.  This is one of those how items.

In this post, I needed to preload some data into the database.  I wanted to include this process in the database project I had created.  However, I quickly found out that this was not a straightforward as I thought it would be.  Here are the steps I followed and any of the gotchas along the way.

1. Create Scripts for the Load Queries.

I started out with scripts that included a DELETE statement followed by an INSERT statement.  However, this created problems when data existed, particularly when the table is a list table used as a foreign key.

Next, I tried MERGE.  This worked great.  This gives me a way to handle new records that are required for the lookup or any changes made to existing data.  Here is the script I used:

merge dbo.MVCategory as target 
using ( 
        select 1, 'Fun' 
        union 
        select 2, 'Technology' 
        union 
        select 3, 'Entertainment' 
        union 
        select 4, 'News' 
        union 
        select 5, 'Sports' 
        union 
        select 6, 'Off-Topic' 
    ) as source (CategoryID, CategoryName) 
    on target.CategoryID = source.CategoryID 
when matched then 
    update set target.CategoryName = source.CategoryName 
when not matched then 
    insert (CategoryID, CategoryName) values (source.CategoryID, source.CategoryName) 
;

After going through this process on my own, I also found the same recommendation from the SSDT team at Microsoft as noted here: http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx

2. Add the Scripts to Your Project

This step is pretty straight forward.  You can either create the script files and add them to your project or you can create them within your project as script files.

3. Change the Build Action to None

This was one of the key pieces I missed.  After I added the scripts to the project and then ran a build, it was broke the build.  Each of these files which were merge scripts reported an error during the build.  It turns out this is called out in the article I reference above as well.  SSDT (SQL Server Data Tools) is designed to build database objects not manipulate data.  One other area of grief caused by this is that you can break the build in the solution if your project is part of a bigger solution such as mine.  As a result, you will get grief from the other developers, you can trust me on this one.

The image below shows where to set the Build Action property to NONE.  This will exclude these files from the build in this format.

4. Add a PostDeployment Script to Your Project

If you do not already have a PostDeployment Script, you need to do this at this point.  This is a specific type of script task that can be found in the Add menu.

5. Add SQLCMD Statements to the PostDeployment Script

The final part of the process is to add SQLCMD statement to the PostDeployment script to execute the files you have created.  As noted in the help in the template, you can execute the scripts by calling a single SQLCMD statement for each script.

:r .\PreLoadMVCategory.sql

The :r {filename} syntax will expand the script for execution during a publish call or DACPAC creation.

I hope you find this useful as well.  This is a common task required in creating solutions.

Join Me at Modern Apps Live! Las Vegas

If you are familiar at all with Visual Studio Live! then you should check out this new conference.  At the Vegas conference, I am participating in this unique three-day event which is a series of sessions that build on each other as the conference progresses called Modern Apps Live! The goal is to do a end-to-end application build during which the presenters build the application as well as focus on Application Lifecycle Management, best practices, tools, and a variety of technologies.

I will be presenting on the database design and business intelligence components of the solution and would love to see you there.  To make this even sweeter, if you register with the following code, MVSPK3, you will qualify for one of the following offers:

• The 5 day all-access Best Value Conference Package for just $1,595 – a savings of $500.00 off the standard price of $2,095! (*Includes pre and post conference events.)

OR:

• The 3 day Modern Apps Live! Conference for just $1,295 – a savings of $400.00 off the standard price of $1,695

This is for new registrants only.  Make sure to use the code by February 27, 2013, to take advantage of the full discount.

Also keep in mind that you will be able to take advantage of the Visual Studio Live! sessions as well as this is a cohosted conference.

I hope to see many of you there!