Microsoft BI Conference, Day One

Gemini, Madison, Kilimanjaro and More

I know when a company puts on a show like the Microsoft BI Conference, there are going to be announcements, and pre-announcements of new products and features. The bullets:

Project Gemini - A self-serve BI application, rooted in Excel and an in-memory datastore the primary goal of which is to allow user creation of SSAS databases. Databases that are then managable and not 'spread marts' or 'spreadsheet purgatory'. Donald Farmer actually demonstrated filtering and sorting 20 millon rows on a thousand dollar quad-core pc.

Project Madison - With the DATAllegro purchase, Microsoft is quickly integrating the newly aquired MPP capability into the Madison release targeted for the first half of 2010. Jesse Franklin demonstrated clean-cache queries against a 150 terabyte data store accross 24 instances of SQL Server. The response times were screaming.

Project Kilimanjoro - This will be a focused release of SQL Server, that I understand to be a re-architecture (read euthanasia) of the failed Report Builder functionality in SQL 2005. Instead, a set of task focused add-ins to Excel that will pave the way for component based report writing. No demo, but very exciting rhetoric.

Countdown to Microsoft BI Conference

The project scheduling fates have smiled upon me and I will be attending the 2nd Annual Microsoft Business Intelligence Conference, October 6-8 in Seattle, WA. As I will not be presenting, this is the first conference in years, that I will be attending as a pure attendee. If anyone out there is attending or if you have any tips for visitors to the Seattle area, please let me know.

Caffeine and WiFi, Wooo hooo

Thanks to Starbucks and AT&T, you can trade a little personal information for free wifi nearly anywhere on the planet. Get a Starbucks Card, use it once a month, and get 2-hours of free wifi Internet access from AT&T.

Knowing my profession, personal coffee habits, and the amount of travel I do, getting into a Starbucks to use the card is not going to be a problem. Second, with a location within walking distance of every point on the globe, we are one step closer to universal Internet access.

When Processes Break

I was on-site with one of my current clients, an instantly recognized leader in insurance. The cube my team was using for the day was a typical 'hotel cube' given to traveling consultants. As we cleared the desk to do some work, I noticed a snappy little publication covered in 3-D graphics. The purpose of the document seemed to communicate the virtues of finding defects early, as opposed to late in the software develolpment process.

The priceless comment from one of my senior team members was something like "...everybody knows that, but nobody does anything about it..."

File Under SSAS Demo Creation

In the course of creating a demo for a potential client, my team made the following observation. While the time dimension is always crucial to a BI solution, for a laptop portable demo stay away from models that incorporate two (or more) time dimensions. For example, in Hospitality refrain from using a model with Check-In and Check-Out dates, instead consider using Check-In Date (or Check-Out Date) and a measure indicating the interval, for example Nights. This helps avoid a sparse cube with few aggregatable date pair combinations.

Obviously, in a production environment the client may have valid reasons to employ both a Check-In and Check-Out date dimension role. However, production hardware deployment won't have the constraints of an all-up demo environment executing SQL Server, SharePoint and PerformancePoint an a single (laptop) machine.

Techmixer University

It was my distinct pleasure to be a speaker at Techmixer University this afternoon. I was able to successfully demonstrate using SharePoint and Excel Services to rapidly create dashboards for performance monitoring. Stay tuned for an update as to where to get the presentation and demonstration files.

To those of you that attended, thanks again for your time, attention and questions.

Travel Size Supermarket

My work has required varying levels of travel, from out on Sunday - back on Friday "Road Warrior" trips with never the same night in the same city, to the simple overnight for an early am meeting or software demonstration. I am fortunate that less than half of my work requires travel by air. However, for those of you racking up the frequent flyer miles, or who just prefer minimalist packing, Minimus has a wide selection of creature comforts for you next Planes, Trains and Automobiles adventure.

From a micro roll of paper towels to sink-sized Woolite for on-the-road laundry (in the event of an unplanned extra night for instance).

Belated Congratulations

Join me in congratulating (however belated) a fellow Steel City SQL board member, Robert Cain for earning Most Valued Professional status from Microsoft. Robert works tirelessly on a number of user groups, including Steel City SQL, he blogs seemingly a continuous stream of relevant posts at www.arcancecode.com. I urge you to add it to your feedlist.

ETL Specification, Part 1

Specifications are vital to the success of a data warehouse project, just as good specifications are vital to ANY software project. Specifications are important not for their page count or weight, but for the thought required to commit their contents to paper. I have seen too many "White Board Cowboys" create a so-called specification as a series of scribbles on a dry erase board. I am a big fan of White Boards, but they are a collaboration tool. A good spec should communicate the following:

1. What the developer is to do
2. How the developer will know when it the work is done
3. Anticipate and eliminate points of potential confusion

Item #3 is the whole reason for the specification. It is in thinking the design through in which truly calendar-busting problems are resolved. A recent client suggested that because development tools (and frameworks) had evolved to such an efficiency point, the task of software design was obsolete. Nothing, in my opinion, could be further from the truth.

"...I have always found that plans are useless but planning is indispensable." - Dwight D. Eisenhower

More on this subject shortly...

Steel City SQL, part 2

The official version of my volunteer work with the Steel City SQL User Group. As Director of Program Development, I churn through my address book to find speakers to deliver presentations and software/book publishers willing to grant us review copies of their wares. If you have either, drop me a line at ComFrame.

Steel City SQL Users - Speakers Needed

Having spoken at user groups around the Southeastern United States, I recognize that a vibrant and active technical community carries with it many benefits. That said, I am volunteering my time this year to assist my local group, Steel City SQL Users to schedule speakers and develop other programs of value for our audience.

If you know of anyone who either may be interested in addressing this group directly or may in turn know someone interested in delivering a presentation, please don't hesitate to reach me via my blog or via the contact us link at Steel City SQL's site.

Alabama .Net Code Camp 6

Save the date. Code Camp v 6.0 is scheduled for February 23, 2008, in Huntsville, Alabama. If you are interested in speaking, sponsoring or attending, the details can be found at the Alabama .Net Code Camp site.

SQL Saturday Presentation Available

My presentation on Data Mining with SQL 2005 will soon be available for download here.

Thanks again to everybody for a wonderful experience. This was beyond a doubt the best produced, best-attended, Code Camp event I have participated in as a speaker. The facilities at Seminole Community College were just first-class, as pictured here in the main atrium.

On My Way to SQL Saturday!

Flying into Orlando today for my presentation on data mining with SQL 2005, I have a few traveling observations.

1. Southwest Airlines new, business-friendly boarding process rocks!
2. The Orlando International Airport is my favorite, among major-city terminals.
3. The Hertz Neverlost GPS system blows. A batting average of .333 is great if you are a major league ballplayer. However, most consumers of these services (devices) can achieve that level of proficiency without a paper map. I guess I will await, patiently the general availability of the promising Dash. In the meantime, truth in advertising would suggest Hertz rename the product to EVERLOST.

Hardly BI related, but it is my blog.

PerformancePoint Server Evaluation Editions

Microsoft announced yesterday the availability of PerformancePoint Server 2007 evaluation editions. You can get the 32-bit version here.

This is surely one of the more exciting products released in the last few years that will enable organizations to drive strategy down to the individual worker, via a "BI for the Masses" execution.

High Tech Barn Raising!

While I have never participated in a physical barn-raising myself, the good folks at Tech Birmingham have managed to stage an awe-inspiring event this weekend. The plan is starting tomorrow, November 9 and finishing Sunday night, November 11, to complete a beta release of an Internet startup.

If you are interested in the concept, or participating, you can get more information at Startup Birmingham.

Unfortunately, I will not be participating this year, as I have previously committed to speaking at SQL Saturday. See you Saturday if you are in the Orlando area.

Wanted: Talented BI Professionals

Wondering why you have not seen a fresh blog post lately? It is because the BI practice at ComFrame is growing at a record pace. If you are an exceptionally talented, experienced BI professional and are interested in joining a premier consulting organization and overall great place to work, please email your resume to work@comframe.com. Please place sqlbi.blogspot.com in the subject header so your resume will get immediate attention. Third party submissions will not be considered. If you want to learn more about ComFrame, check out www.comframe.com.

Presentation Available

My presentation from Alabama .Net Code Camp V is now available here. I am already looking forward to the next Code Camp event.

Alabama .Net Code Camp V

Well the Fifth Alabama .Net Code Camp is in the books. I would like to express my humble gratitude to attendees who drove from the ends of the state to attend the event. If you had half as much fun as I did, then it was worth the time and effort. As promised, my presentation "Everything I Ever Wanted to Know About Beginning Business Intelligence with SQL 2005" will be available via ComFrame's website soon. As soon as I know the files are available, I will post an update here so the news can easily get out to RSS readers.

If you are interested in the methodology discussed to 'bootstrap' db connections and store configurations in the database, please read my post on SSIS configurations.

I have added some content to the SQLBI blog, notably a list of web resources I referenced in Saturday's discussion, and a few links to Amazon.com for the book titles I mentioned as well. Finally, I would ask that if you enjoyed the discussion on Saturday, or anything that I am writing here, please make use of the comment links available on my posts to send feedback.

Finally, I am working on a post to go up by the middle of this week, that will walk through the steps (and screen shots) I used to create the examples and demos in Saturday's discussion. Stay tuned here for updates.

File Download via HTTP

SSIS has some interesting components for 'out of the box' support to obtain data via FTP and even Web Services. However, in creating a demonstration project for a class I am teaching, I found the need to download files from a web page via http. I found a cool little script on sqljunkies that did just the trick.

To further describe the problem, I am interested in downloading the weekly performance statistics for Division 1 College Football, graciously made available by the NCAA here. There are three files, one each for the schedule, offensive and defensive statistics. I really want the package to fire on a weekly basis, and get the new stats updating my data mart.

As a brute-force, first-pass effort, I created package variables to represent the remote file uri, the local filename and local path. In the case of the Offensive production statistics, these variables were named RemoteUriOffense, LocalFileNameOffense, and Extract_Home. Unfortunately, one of the downsides of the script task is having to use some variable at the package (or container) level and explicitly declare that variable as being available for read access by the script. An example of that is below:



Substituting the variable names declared as readable by the script, in the script code as illustrated below.

While the script task from sqljunkies does get the job done, there are a few disadvantages. First and foremost, creation of the variables for each of the files. I could have done some manipulation of a single set of variables, iterating through for all three files, and using a single script task, firing the task once for each file instance. Additionally, given the variable infrastructure needed for the script task, and the probability this is not the last time I will ever get data via http, this may be a good candidate to create a custom component.

Among the advantages of the custom component, all the logic for file download would be centralized. What happens, in my three-script components when the a change occurs to the download logic (for instance)? Yep, I am replicating the change for each extract. True enough this problem is solved by using a single script task and iterating over a list of files to download.

However, this may just be the perfect task on which to base a first custom component. Stay tuned.