Arcane Fun Fridays–Music to Code By

I love music, it’s great to listen to when I read or program. I do find music with a lot of lyrics distracting when I’m trying to concentrate, thus action/adventure soundtracks are one of my favorite genera’s. Some surprisingly good music can be found from video game soundtracks. I thought I’d share a few of my favorites with you.

Call of Duty – Modern Warfare 2 – This is an awesome score. I love the game, and love the music even more. It’s fast paced and will really keep your energy flowing.

Call of Duty – Black Ops – Another great game, and another great soundtrack.

Army of Two – After finding the Call of Duty soundtracks, I began to explore the soundtracks from other video games. This is the first one that I thought was in the same class as the Call of Duty games. Good stuff.

The Presenters Packing List

In my previous post I mentioned a packing list. I use this list prior to heading out to speaking engagements. I thought others might find it useful so I thought I’d put it here, along with a short explanation when needed.

Laptop – Pretty obvious

Backup Laptop – I bring my netbook along, as a backup computer. Just in case the main one dies.

External Hard Drive – I also bring along an external drive that has all my presentations, necessary ISOs, etc. This way in a real crisis I can borrow another presenters laptop to do my presentation. This actually happened at a SQL Saturday, only it was my laptop that got borrowed when a fellow presenters laptop failed to boot. Fortunately he had everything on a thumb drive.

Extension cord – I have been in a few venues where the power plug was almost out of reach. I keep a 12 foot extension cord in my bag that has three, three prong outlets on it. Even if you don’t need it for your presentation, it’s quite handy in the hotel room, and especially so if you’re flying. Often waiting areas have few outlets, most of which are usually occupied. I’ve never been turned down though when I whip out my handy extension cord and ask if we could both plug into it.

CAT-5 Network Cable – I keep a 12 foot network cable in my backpack, I’ve been to several places that had no wireless but did have wired. Heck, even going to business meetings it’s come in hand on more than one occasion. Also great in the hotel room as you can run the cord a distance and relax in bed.

Spare Mouse – Normally I like a trackball, but it’s easy enough to forget, so I keep a small travel mouse in my bag all the time.

Presenters Mouse – Please, please, please my fellow presenters! If you are going to present and have more than 3 slides in your presentation invest in a decent presenters mouse. It’s very distracting when you stand by the screen, then have to keep running back to your laptop to advance to the next slide.

Zune – True, I don’t use it during the presentation, but I keep it handy when I travel.

Kindle – Again, not used during presentation, but a great way to make the trip go by quickly, or to look up an answer to something you’ve been asked.

Cell phone – OK, I admit it, I once went to a week long convention and left my cell phone sitting in my desk charger at home.

USB Cords – Check to make sure you have all the cords you need for all the toys you are bringing. You will need at least one for each device, so you can interface with and also recharge.

Powered USB Hub – If you have more than a few devices, you might also wish to bring along a powered USB hub. Even if you don’t plug it into your laptop you can still use it to charge all your stuff.

Spare Batteries – I use rechargeable batteries in everything, a great investment and good for the environment. Before I leave I swap out batteries in everything I’m bringing for fresh ones, and make sure to bring enough spares to resupply everything at least once. If you are going to be gone a long time, you might also bring along your battery charger.

USB Thumbdrives – I keep a few thumbdrives in my bag, usually ones I picked up at some conference for free. I’m often asked by attendees if they can get a copy of my demos/slides, but then they don’t have a drive. Sometimes I even have the foresight to put the material on them in advance.

Business Cards – I bring along a big stack of business cards, they are easy to hand out (put a pile by the door and near or on the podium). Let’s face it, while business may not be your main motivation for being there, you wouldn’t want to turn any potential new customers down either.

Pen/paper – Keep a small notepad and paper handy, it’s great for writing down contact info, or “to do’s” that arise during an event.

Mouse pad – Sometimes your mouse just won’t work where you need it to, maybe the surface is too reflective, or not reflective enough. A spare mouse pad fixes that quickly.

Laptop cable locks – If I’m going to a multi-day conference, I might only need my big laptop for the day of my presentation. For the other days my netbook will do fine. To make sure my big laptop is safe in my room I use a cable lock to secure it to my desk or some other fixed object. Also handy if you want to leave it in a speakers lounge for a while, sadly there have been incidents where laptops have gone missing.

Penlight – I keep an inexpensive penlight in my bag, sometimes I have to get down low to run my power cord / video cable, or perhaps dig deep in my bag for something. A little extra light never hurts.

Swiss Army Knife – I have a collection of Swiss Army Knives, but by far my favorite is the CyberTool. It’s made for working on computers, I’ve found it extremely useful. Warning, you can’t take this on the airplane with you thanks to TSA rules, but you can put it in your suitcase so you have it while you are at your destination.

Swag – If you plan to give away anything, don’t forget to bring it along.

Cash – Keep a small amount of money in your bag. Maybe $20 (or the equivalent in your local currency). Keep it in small bills, plus a few dollars in coins. One late Sunday night I was caught in an airport with no open restaurants. Naturally there’s been no meal service on the plane and I was hungry. Fortunately I had quite a few ones and some change on me, so I was able to grab some food from the vending machines. Not fine cuisine, but kept me alive and my stomach from rumbling. Also handy if you have a car and need to stay in a parking deck or feed a curbside parking meter.

Power Supplies – Don’t forget them.

The “Extras”

Sometimes when I travel, if I’m going by my own vehicle and I’ll be there for several days, or if I’m helping to host an event, I’ll grab my “extras” bag. It’s a spare laptop bag, one of a bunch I’ve gotten from attending various conferences. In it I toss in things I might need. Among some of the contents are:

Extra CAT5 network cables; Extension VGA cables; A power bar with multiple outlets; small network hub; a variety of USB cables; Extra earbuds for my Zune;

Start Packing

Well there’s my list. Use it and adapt it as you see fit. Feel free to leave a comment with your own ideas of things in your laptop travel bag.

Speaking on the SS Titanic

If you listened to the episode of Deep Fried Bytes that I was interviewed on you’ll recall I was interviewed along side fellow SQL Server MVP Denny Cherry (blog | twitter). He recently wrote a blog post called “When the demo’s don’t work the men and women are separated from the boys and girls.”

I know from the perspective of the audience it looks like presenters are calm, rehearsed and have everything under control. Sadly, we are usually freaked out, winging it, and trying to herd a group of cats.

Well OK, it’s not quite that bad, but sometimes it does feel like it, especially when disaster hits in the form of our demos going bad. Denny and several others have been sharing stories about their demo disasters so I thought I’d give you a glimpse into the scary mind that is Arcane Code and share with you too.

Demoing Beta software is a lot like working with kids and animals

The old show business wisdom of “never work with kids or animals” could easily extend to beta software. I was demoing the early beta of PowerPivot. I’d rehearsed and work though my presentation several times all without a hitch. So naturally when I was doing it before the audience, nothing seemed to work right. The data imports crashed, none of the charts would display correctly, and it crashed Excel twice. What to do?

Well, I’m a big believer in Scott Berkun’s advice in “Confessions of a Public Speaker”. Let the audience in on it, don’t try to BS. Heck, a lot of the time there are people in the audience far smarter than I am. I’ve even had PHD’s attend my sessions. Even if an audience member is new at coding they’re still smart enough to smell someone trying to feed them a line.

So I simply admitted what was happening, explained what they should be seeing, and kept going. I skipped ahead to some things that were working, fortunately this group took an intermission about halfway through the meeting for a pizza break. While they chowed down I was able to reboot, and get the demos working again. I even had enough time for a few pieces of pepperoni myself!

When they returned, I brought them up to speed on what I’d done, showing them the now working pivot charts. I finished off the section I was on, then returned to my original flow.

A pretty shade of Blue

On two occasions I’ve been bitten by the “Blue Screen Of Death” The first was trivial, I was presenting at CodeStock last year. (By the way CodeStock is open for registration and speaker call, go check it out.) Just as I advanced to the very last slide in my deck, the “any questions here’s my e-mail/blog again” slide, the audience started laughing. I turned to see the “BSOD” screen on the big projector. Fortunately the session was over and I was able to field questions without needing the computer.

The other time though had a bit more of an impact. It was fairly early in my presentation and I had installed Windows 7 Beta 1 on my PC so I could learn it. A few slides in my laptop screen suddenly went black. I was puzzled then saw a BSOD quickly followed by a reboot. What to do?

Well, I just made a quip about using beta software, then proceeded to talk about what had been on the slide I’d been showing and the next one as well while my machine rebooted and I started launching PowerPoint and my demos. It’s a bit hard to talk and launch, but you can get the hang of it, and I think short pauses are OK, especially if the audience sees what’s up. Remember, they can feel your pain, everyone has experienced that lovely shade of blue at some time or another.

More power Scotty, I need more power!

My worst disaster wasn’t directly demo related, nor did the audience ever see it, but it was still a nightmare. I had traveled 3 hours to attend a SQL Saturday. I got to the hotel about 11 pm, to my horror I found I’d left my laptop power supply at home. To make it worse, I’d been using the laptop on the way over (don’t worry my lovely wife was driving) so there wasn’t a lot of battery power left.

Fortunately my wife had her laptop. I wound up staying up until 4 am downloading the various ISOs I needed to install the software I needed to do demos on her computer. Bleary eyed I showed up the next day and was able to do my presentations, using her laptop plus sucking up the remaining power on my laptop. It was a close call, on my laptop I had about 15 minutes of power left when my session was over.

Pain is funny

If you look at YouTube, you’ll find no shortage of people hurting themselves. Falling off things, getting whacked in inappropriate body parts, and we all laugh. So as you laugh at my pain, I hope you’ll take away some of the lessons I learned, should you speak, or even do a presentation at your office.

1. Never BS. Follow Burken’s advice, if something goes wrong simply acknowledge it and move on.

2. Know your stuff. When your computer crashes, and it will, be prepared to keep talking. Know your slides well enough so you can talk even without them. Be prepared to describe in vivid detail what the audience would see if Visual Studio / SQL Server / your tool of choice was running.

3. Create a list, and check it twice. After the forgotten power supply incident, I created a packing list. I have on it all the things I’ll need to do demos with. I check it, and double check it, before I zipper up my laptop bag and suitcase.

4. Have a backup box. I got home from a trip a few years ago, to find the internal screen on my laptop had quit working. I was lucky that it was when I got home, but it still taught me a lesson. Now when I head out, I have two laptops, my main demo box and my netbook.

As netbooks are relatively inexpensive, it wasn’t a big investment, plus with their long battery life they are great for pulling out and taking notes on all day. True, there are a few things I cannot do on my 2 gig netbook that I can do on my 8 gig laptop, but I can at least still run PowerPoint, show the code, etc, even if I can’t run the main demos.

I also keep an external portable hard drive handy. On it I have all the ISOs I might need to load up a computer with the software I need to do a demo. I also have all my demos backed up on it, along with a nifty piece of freeware called Virtual Clone Drive. It installs easily, and lets you mount an ISO as if it were another CD / DVD drive.

As you can see, public speaking is not all fun and games. But it’s intensely gratifying, and I encourage you to give it a try if you ever get the chance.

Visual Studio Database Project Course

I’ve recently completed another course for Pluralsight, this one is on Visual Studio Database Projects. In it, I cover how to reverse engineer an existing database, then the many options you can configure for deployment. I then cover data comparisons, schema comparisons, and pre/post deployment scripts.

One of the most powerful features of Visual Studio is the data generation tools which give you the ability to generate vast quantities of realistic, predictable test data. Finally, I cover how to unit test your database code.

While I use Visual Studio 2010 and SQL Server 2008 R2 in the course, everything I teach is applicable all the way back to Visual Studio / SQL Server 2005, so even if you are still on an older version of your development tools the lessons you learn here can still be applied.

Deep Fried Arcane

At TechEd last year I was interviewed by the Deep Fried Bytes guys, along with another great SQL guy Denny Cherry. The topic of our interview was What Should Developers Know About SQL Server. (Click the link for the show.)

In the interview we cover SQL Server Full Text Search, SQL Server Service Broker, and SQL Server Integration Services. And if you listen, you’ll hear about my favorite deep fried food!

Data Warehousing / Business Intelligence at Pluralsight

It’s been another busy month, and I’m pleased to announce my two newest modules were published today on Pluralsight. If you’re not familiar with Pluralsight, they are a training company that has a vast library of training videos.

The description of my new course can be found here:

http://www.pluralsight-training.net/microsoft/olt/Course/Toc.aspx?n=intro-dwbi-course

There are two modules, the first is an introductory session for DW/BI. It’s primarily slides, by the end you’ll have a grasp on the arcane terms around Business Intelligence such as facts, dimensions, surrogate keys, OLAP, and more.

The second module is an overview of the Microsoft tools for doing DW/BI. It starts with the Adventure Works Lite database. From there a data warehouse is built, on top of which an analysis services cube is created. Finally a report will be generated off the cube that meets a specific business need.

Along the way attendees will get to see Visual Studio 2010 Database Projects, along with the SQL Server toolkit: SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services. Attendees will also get a look at SQL Server Management Studio.

Enjoy!

PowerPivot with Pluralsight

For the last few months I’ve been cooped up here in the Arcane dungeon, working on a new project. And now that’s it’s released I can finally share all the details.

While I’m still happily employed during the days as a BI Architect for Comframe, in my spare time I’ve been acting as a technical advisor to a training company called Pluralsight.

I’ve developed a series of four training modules on PowerPivot for Excel 2010. The first module is an overview, that gives you the basic steps on creating a pivot table using PowerPivot.

Module 2 goes in-depth on working with data behind the scenes. How to add calculations, formatting data, filtering data and more.

In the third module I cover the creation and formatting of Pivot Tables, formatting them for users, using DAX (Data Analysis eXpressions), and how to use Sparklines with PowerPivot data.

In the last module we explore the charting capabilities of PowerPivot, covering various types of charts and ways to format charts. We then use the knowledge we’ve gained over the last four modules to create a realistic dashboard.

If you are already a subscriber to Pluralsight, take a look, the courses are now live. If you aren’t, take a look, Pluralsight has the best training I’ve seen on the net and it’s growing daily.

Birmingham User Group Holiday Party is on! Dec 6 2010

I’m pleased to announce the third annual Birmingham User Group Holiday Party. Each year user groups across Birmingham gather for a combined Holiday Party.

As in past years, this event will be at Richards BBQ and Grill on Acton Road. We’ll begin about 5 with a social hour, and order food around 6 pm, to begin eating between 6:30 and 7. Don’t worry though, if you are running late you’re still welcome. We’ll conclude around 8pm. As in past years, we’ll simply order off the menu which has a wide variety of selections, enough to satisfy everyone’s taste buds. With the mix of groups, each person will be responsible for their own bill.

We highly encourage everyone to bring their spouses and kids. You may bring a gift for your own kids to open if you wish. We promise to keep the geek talk to a minimum.

This event is open to all of the Birmingham tech community, there is no requirement to be part of an existing user group to attend.

For more details, e-mail me, arcanecode at gmail.com.

Visual Studio Database Developer – Data Dude at SQL Saturday 21

On Saturday August 21, 2010 I will be presenting “Visual Studio Database Developer Edition – Data Dude” at the Nashville SQL Saturday #51. This looks to be a great event, and I’m excited to be a part of it.

My slide deck is downloadable from this link: DataDude.pdf

In my presentation I also used some data from a previous post to generate data. That post has detailed information on how to generate sample data, and can be found here: https://arcanecode.com/2009/04/02/sql-server-sample-data-the-sql-name-game/

Thanks to everyone who attended, hopefully after my presentation you’ll have the same passion for Data Dude that I do.

https://arcanecode.com/wp-content/uploads/2010/08/datadude.pdf

Congratulations to Tracy Hamlin, Exceptional DBA

I just wanted to give a hearty congratulations to Tracy Hamlin, who won the Exceptional DBA Award for 2010. Tracy is a friend of mine from the Steel City SQL user group, and clearly deserves this award. You can see her award at the link above, or read her interview at SQL Server Central. (They ask you to register, but it’s free to join.)

SSIS For Developers at DevLink 2010

I have the honor of presenting at DevLink 2010 today. DevLink is a great conference in Nashville, TN, this year attendance topped 800 people. In my session, SSIS For Developers, we’ll look at how SSIS, commonly used in Data Warehousing, can also be used by most developers to solve issues that frequently come up in the course of their job. Data conversion and exporting data are two good examples, and we’ll also look at how to call your new SSIS job from your .Net application.

There are two code demos used during the presentation, both available at my Code Gallery site. The first is the basic SSIS For Devs demo with the three packages. The second is the more complex example showing how to call SSIS from your .Net application.

SSRS Quick Tip – An item with the same key has already been added

I was in the process of creating a new report in SQL Server Reporting Services today. I was loading my dataset from a stored procedure, and when I hit the “Refresh Fields” button I recieved the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

When I clicked the details button I got this further information:

“An item with the same key has already been added.” Here’s a screen shot of my error.

Well this had me scratching my head, as I had made sure to run the stored procedure, and it executed with no errors. After doing some considerable research I finally found a question in the Technet forums that was tangentially related to the error. This gave me the clue to figure out what I had done.

In my stored procedure, I had inadvertantly included the same column name from two different tables. My query looked something like:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.field99
FROM TableA a JOIN TableB b on a.Field1 = b.Field1

SQL handled it just fine, since I had prefixed each with an alias (table) name. But SSRS uses only the column name as the key, not table + column, so it was choking.

The fix was easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together, which is what I did.

As it took me a while to figure this out, tought I’d pass it along to anyone else who might be looking.

SQL Server Integration Services for Developers

Today I presented SSIS For Developers, we looked at how SSIS, commonly used in Data Warehousing, can also be used by most developers to solve issues that frequently come up in the course of their job. Data conversion and exporting data are two good examples, and we also looked at how to call your new SSIS job from your .Net application.

Linked Subreports in SQL Server 2008 Reporting Services

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

The previous lesson showed how to include a subreport into another report. This could be used to link independent reports together into a single report. It can also be useful to have a related subreport. A subreport whose data is driven by that of the main report. This can be accomplished by the use of paramenters.

For this lab we’ll create a subreport that returns category totals for the region passed in from the main report. Note that this is a greatly simplified example to illustrate the technique. Even though in this sample everything comes from the same database, each report could just as easily come from completely different data sources. Subreports would be a great technique for combining data from different systems.

Step 1. Create the subreport.

Use Contoso as the shared datasource. For the query, enter:

SELECT [Region]
     , [ProductCategoryName]
     , SUM([TotalAmount]) AS ProductTotal
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] = @pRegion
 GROUP BY [Region], [ProductCategoryName]
 ORDER BY [Region], [ProductCategoryName]
Use a tabular report, move everything into the details area, Generic for the table style, and for the report name use “Subreport – Region Category Totals”.

Step 2. Cleanup the subreport.

Click the edge Region texbox in the header (so it’s selected instead of being edited), and press delete. Repeat with the [Region] textbox in the detail row. We won’t need it since Region will be displayed on the parent report.

Change the other headers to Category and Total. Make them wider, make what had been the Region column smaller but leave it, it will give a nice indent padding when included on the parent report. In the textbox properties for ProductTotal, make sure to set the Number to Currency, and in the Alignment area change the Horizontal to right align.

Remove the “Subreport – Region Category Totals” text box

Click on the main table grid, then move it to top of body. Collapse the body to fit the table.

Step 3. Add the parameter.

In the Report Data window, right click on Parameters and pick Add Parameter. Name the property Region. For the prompt, enter “Region – Hidden”. Since the prompt will never be visible, it really doesn’t matter, but making a habit of entering the name and the word Hidden will give a clear indicator that this parameter is a hidden one.

Leave the data type set to text, and check on “Allow blank value”. If you don’t, the report will error out when used as a subreport. Next, set the visibility to Hidden. This means it won’t appear if you run the report, but you can still pass in parameters, from another report or via a URL. Click OK to close the properties window.

Finally, we need to bind the parameter to the parameter the dataset needs. Right click on the dataset and go to properties. On the parameters area @pRegion should already be present (remember, it was part of the WHERE clause in the SQL query). Pick @Region in the drop down for Parameter Value.

Step 4. Create the main report.

Add a new report, using Contoso as the shared datasource. For the query, use:

SELECT [RegionCountryName]
  FROM [Report].[V_Regions]
Use a tabular report, move the RegionCountryName to the details area, and pick Corporate for the style. Finally, for the name use “Regional Report”.

Step 5. Layout the report.

Since there’s only one column, expand it to take up the width of the body.

Right click on the row selector (the gray box with the lines on the left of the table) and pick Insert Row->Inside Group Below.

Into that area, drag a Subreport control from the toolbox. Note in this case there is only one column, but if there were multiple cells you could highlight them, right click and pick Merge Cells.

Step 6. Setup the subreport.

Right click on the subreport control.

Under “Use this report as a subreport” select the “Subreport – Region Category Totals”. Under the parameters area, click Add. Select Region under Name, and for the Value select RegionCountryName.

Step 7. Preview the report

Preview the report to see your results:

Notes

Just a few notes. In this report, we left the table headers in the subreport (Category and Total). Often these are removed, to make the subreport blend in more with the parent.

Here only one parameter was passed, however you can pass multiple parameters if you need to.