SQL BI

Moving to Serve You Better

2010-02-26T00:13:00.002-06:00

I am in the process of moving my blog to www.sqlbipro.com.

SQL Server Version Numbers

2009-06-17T15:33:00.001-05:00

A colleague of mine was setting up a development environment to mirror the deployment environment of our client. In the course of his activities, he needed to know the version number for SQL Server 2005 with SP3. Here is a handy link for decoding version number information in the future: http://support.microsoft.com/default.aspx/kb/321185

Pensacola SQL Saturday 14

2009-06-10T11:51:00.001-05:00

Thanks to my attentive and brilliant audience at my Introduction to MDX for SQL Programmers presentation at SQL Saturday 14, Pensacola. Should you desire assistance with any of the examples, feel free to contact me with SQL Saturday in the subject line.

BI Solutions for a Tough Economy

2009-06-10T11:47:00.002-05:00

My colleague Derek Comingore and myself will be presenting our three-part series of BI Solutions for a Tough Economy, sponsored via SQL Server and Windows IT Pro magazines in July.

We will be covering three main solutions:

Predictive Analytics with Data Mining
Metric visibility with Excel Services
Dashboard creation with PerformancePoint Services

You can register here at Windows IT Pro.

If Surgical Teams worked like Some Software Teams

2009-03-28T15:51:00.001-05:00

Oracle Date Numbers to SQL Server Datetime

2009-01-29T17:00:00.001-06:00

File this under ‘blogging so I never have to recall this from memory’. One of my current clients is extracting data from an Oracle database to be loaded into a SQL Server Analysis Services (SSAS) cube. Of course, the date columns contain floats, representing the number of days since January 1, 1900 in the integer portion and the fraction is the portion of the date (time of day) for this datetime.

Fortunately, we are only concerned with the date grain of data, so I am able to use dateadd(day, <<column with oracle>>,’1/1/1900’) to calculate a SQL Server date time (this is a 2005 project).

Thank You Sara Ford

2009-01-28T08:00:00.000-06:00

I owe a quick thanks to Sara Ford, author of the book Microsoft Visual Studio Tips in addition to creating the Visual Studio ‘Tip of the Day’ feed. That said, while reading her book, I picked up on a particularly helpful tidbit that is helping me maintain this blog, Windows Live Writer. Live Writer is a great solution for creating and publishing blog posts, it even allows me to author a post ‘on the fly’, scheduling it for later or simply storing it for further refinement. Even better, it plays exceptionally well with a non-MSFT blogging environment like Blogspot.

You can follow Sara’s other work on her blog at http://blogs.msdn.com/saraford/

HP Mini 1035nr

2009-01-27T11:45:00.001-06:00

As Circuit City continues to circle the drain, on their way to a corporate version of a Darwin Award, I had the opportunity to pickup a netbook. In particular, the HP Mini 1035nr. At a very attractive price point, the device has a 95% keyboard, critical to touch-typing things like this blog post. Which, I am doing while seated at lunch at the Nashville, TN location of the Flying Saucer.

The device has stellar battery life, i have gone over 3 hours without a charge, using wireless sporadically and wailing away on Word and Excel.

I was able to install the beta of Windows 7, per instructions here. I am amazed as to the cold boot time from flicking a switch to the Windows desktop. So far, these ‘stats’ are 1-gig of RAM per the licensing agreement allowing HP to equip these devices with Windows XP.

I have ordered a 2-gig RAM stick for $20. Stay tuned for updates on other mods, including swapping my 60-gig, 4200 rpm drive for SSD storage.

SQL Server Magazine Blog

2009-01-24T08:38:00.000-06:00

Last week, I was invited to share my thoughts on Microsoft BI on the SQL Server Magazine, Business Intelligence Blog. I have no immediate plans to halt posting here at sqlbi.blogspot.com, but the majority of my writing last week was posted to SQL Mag. You can read my initial SQL Server Magazine posts here.

Resolutions, Resolutions

2009-01-11T07:37:00.003-06:00

After a rather interesting first full week back in the office, following my two week vacation to close out the year, I am announcing to the vast audience here my only professional resolutions for 2009.

Update this blog with relevant content more frequently.
To further hone and refine my public speaking skills by presenting at least quarterly.

Upcoming Event - Pensacola SQL Users Group

2008-11-30T08:58:00.002-06:00

I am looking forward to my presentation this week on "MDX for SQL Programmers". I am at this very moment finishing some new demos for my upcoming session at the Pensacola SQL Users Group on December 4.

Microsoft BI Conference, Day One

2008-10-07T07:39:00.002-05:00

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

2008-09-12T17:10:00.002-05:00

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

2008-09-09T14:01:00.002-05:00

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

2008-08-22T05:15:00.003-05:00

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

2008-08-21T06:20:00.003-05:00

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

2008-08-19T19:15:00.002-05:00

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

2008-08-02T08:01:00.004-05:00

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

2008-08-01T07:08:00.002-05:00

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

2008-07-26T06:21:00.005-05:00

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:

What the developer is to do
How the developer will know when it the work is done
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

Steel City SQL, part 2

2008-01-30T11:35:00.000-06:00

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

2008-01-29T06:53:00.000-06:00

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

2008-01-23T02:36:00.001-06:00

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

2007-11-12T14:09:00.000-06:00

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!

2007-11-09T22:13:00.001-06:00

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

2007-11-08T07:43:00.000-06:00

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!

2007-11-08T07:25:00.000-06:00

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

2007-10-30T10:13:00.000-05:00

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

2007-10-09T16:23:00.000-05:00

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

2007-10-08T11:36:00.000-05:00

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

2007-09-21T08:32:00.001-05:00

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.

Expressions and the Data Flow Pipeline

2007-09-13T17:21:00.000-05:00

File this under the "more than one way to skin a cat" topic. I am creating a demo for one of my upcoming presentations, and I noticed a technique for cleaning data from extracts that escaped my earlier reading/training/lab time.

In particular, I am creating some SSIS packages to load Comma Separated Variable (csv) files. Only these files, from an extract provider over which I have little or no control, contain embedded quotes in them. For example a data record would read:

"column1 value", "column2 value", "column3 value"

This did not strike me as odd until the flat file connection manager for this extract had NO desire to see the quotes as part of the field delimiter. Unfortunately, my first pass at loading this data created a table with the following values.

Column 1	Column 2	Column 3
"Value"	"Value"	"Value"

Obviously, NOT what I wanted, even in a staging table, with which to begin my Transform and Load processing.

One way or another, I would have to strip the double quotes out of the data, and for some reason I latched onto the idea of using an expression within an SSIS Derived Column transformation.

Soon enough, I had the Derived Column transform, and was feeding it a steady diet of DT_STR columns from the Flat File Data Source. It seemed as though surely the REPLACE function would suit my needs, but how to express the target expression is "? To little surprise REPLACE(column1, ""","") is NOT what the SSIS Execution Engine is looking for. Thankfully, in the books online Microsoft has furnished a reference for common escape sequences. In my case, " was just what the doctor ordered. REPLACE(column1,"\"","") effectively stripped all of the quote characters from the data, leaving my staging table looking a little more appealing.

Somewhere on the Web, I found a custom SSIS component to strip quotes from an extract. That still may be worthwhile, as my candidate inbound set only had 50,000 or so records.

If anyone out there reading this has ideas as to how to download files (using http), and without a Web Service, drop me a reply via the blog. Perhaps that will be the subject of my next post.

The Dummy Script Task, or Conditional Execution 100

2007-09-06T15:50:00.000-05:00

So I am working on building a data warehouse ETL system with SSIS. Like so many times before, there may be certain steps in the process that I wish to execute, based on some condition. Maybe it is just one of Larry Wall's virtues, laziness, but I want a process to drive flawlessly whenever executed, 3pm or 3am, manually or automatically. An example of this is truncation of destination tables in a data mart / data warehouse project in either a Continuous Integration environment or in an execution of a priming load. To that end, I have employed the following trick I call the Dummy Script Task. SSIS provides a nice mechanism to conditionally execute one task, based upon evaluation of an expression, constraint or both.

Truncating the destination table is easy enough, using an Execute SQL Task. However, creating the conditional execution is a less straightforward. In order to employ conditional execution, the package must have two tasks in between which sits the conditional execution flow. The Truncate Destination Table (Execute SQL Task) is just sitting in a Sequence Container (labeled "Reload_YN" for clarity), with no flow, conditional or otherwise onto which to create the condition.

Enter the dummy script task.

The dummy script task is just that. A Script Task from the toolbar, with NO CODE BEHIND IT! The task merely serves as the initial end-point for a flow that will conditionally execute. It could not be more simple, just drag a Script Task from the toolbar and optionally rename it.

So as you will note from the figure at left, there is a blue data flow connecting the Script Task to the Truncate Destination Tables task. The flow is further noted as conditional by the fx label attached to it. Right mouse clicking a flow, and choosing the Edit selection presents you with a dialog similar to the one below.

This dialog allows us to set the Truncate task to execute ONLY if one or more conditions are true. I have created a package variable, called ReloadYN, of type boolean, that determines if tasks related only to a reload are to be executed. Perhaps the subject of another post, the package variable can be set many ways, via a dtexec command line option, script, or in this case via an indirect package configuration. By having this little gem in my standard Dimension and Fact table package templates, I know the entire database will be truncated (or not) on a consistent basis. One more thing to not have to worry about.

Putting it all together

Based on the value of ReloadYN at run-time, either the Truncate Destination Tables task executes (wiping the slate clean for a new load/test) or not. You can verify this by executing the package, or sequence container, from within Business Intelligence Developer Studio. The 'skipped' task will turn neither red, yellow or green. Execution continues at the first step following the Sequence Container.

Coming Soon to A Screen Near You

2007-08-30T23:35:00.000-05:00

Ahh, the smell of bourbon and pigskin. Football has returned to the South. It is going to be a busy Fall season between gridiron action and the following teaching/speaking events on my calendar.

September 25 - Microsoft Business Intelligence Boot Camp
I will be conducting the program on SQL Server Integration Services for this Microsoft Partner event.

October 6 - Alabama .Net Code Camp V
A couple of abstracts have been submitted, stay tuned for details should I get an invitation. Even if I am NOT speaking, I will be there. Well worth it if you can get to Birmingham for a code-filled Saturday.

November 10 - SQL Saturday
I will be doing a once more refined edition of my Data Mining with SQL Server 2005 presentation, with an eye to new features in SQL Server 2008.

SQL Saturday!

2007-08-27T09:50:00.000-05:00

I have been invited to present on SQL Server Data Mining at the SQL Saturday event, in Orlando, Florida on November 10, 2007. The event is sponsored by the Orlando Chapter of PASS and further details can be found at www.sqlsaturday.com.

SSIS Lookup Transforms and Varchar Natural Keys

2007-08-24T15:15:00.000-05:00

A recent project required the use of inferred dimension members due to early arriving facts. My initial thought was to do something like the figure below. I would use the client natural key for an in-bound fact row, and first attempt to find it in the dimension using a Lookup transform and a cached reference set. If the dimension key was found, it is pipelined to the Union All and eventually inserted into the fact table. If the dimensional key was not located, the row was dispatched to the Add Inferred Client Member task.

Essentially, this task would execute a T-SQL stored procedure to find the dimension key in the table and insert an inferred member if it is not found. The primary benefit to this is being able to avoid the case sensitivity of the SSIS engine and instead have the matching logic be executed in SQL Server. The row is then dispatched to the 2nd Pass Lookup.

The 2nd Pass Lookup is a Lookup transform identical to the initial attempt to find a matching dimension member, except with reference set caching disabled. This allows newly added rows, inserted by the Add Inferred Client Member task, to be part of the dimension lookup. If for some reason the candidate row is not found at this stage (perhaps an error getting the inferred member inserted was not caught), the row has an ErrorReason column added via a Derived Column transform, and is sent into an error flow. All inbound rows that find a dimension surrogate key in the 2nd Pass Lookup are moved into the destination (ie Production) fact table.

Caveat SSIS Developer

In this case the natural key for this dimension was a varchar datatype, and my initial impression was the use of a Lookup transformation would make the lookup of dimensional keys during fact table load straightforward. Unfortunately, there is a difference in how SSIS and SQL server handle string comparisons. SSIS executes a case-sensitive lookup. A fact table candidate natural key of 'A1234' would not match a dimension key of 'a1234'. Some lookup 'failures' in the initial lookup were actually case sensitivity differences. The first-cut of this process actually was two lookups, with the Execute SQL task between them only to insert an inferred dimension member. As duplicate natural keys for inferred members were inserted, the case sensitivity issue became evident.

Speaking In "LA"

2007-08-01T06:01:00.000-05:00

It was my pleasure to be the featured speaker at the Lower Alabama .Net User Group (LANUG), in Mobile, AL on July 31, 2007. The presentation was on the data mining features in SQL Server 2005 and can be downloaded as a PDF. I would like to add that Doug Greene and Matthew Hughes are doing a great job with a user group that just recently celebrated the beginning of their second year.

As the post-presentation discussion swerved into dimensional data warehousing, there was some discussion on doing a dimensional data modeling session at the next Alabama .Net Code Camp. If you are interested, please post a reply here or send me an email at bralston@comframe.com.

Alabama Code Camp IV

2007-04-16T09:01:00.000-05:00

It was my distinct pleasure to be chosen as a speaker at the fourth Alabama .Net Code Camp this past Saturday. A few of the attendees of my presentation on Agile Database Techniques for .Net requested that I post a link to the Database Publication Wizard, a cool tool for moving data and structures from SQL Server databases via text files and SQL.

The PowerPoint from the presentation (and supporting files for examples) will follow shortly.

Popping the Integration Bubble

2006-09-15T19:15:00.000-05:00

Picture this, a team is nearing the end of a five-week development effort on a small Data Mart project. A team of just three developers come to a screeching, abrubt halt because, in spite of dazzling specifications, and flawless design documentation, the code just doesn't play well together. At this stage the schedule goes out the window, because the team is engaged in the very unscientific process of wack-a-mole engineering to 'stamp out' the integration errors. The project and team in this example have just become witnesses to (as well as victims of) the Integration Bubble.

The root cause of our example Integration Bubble is the lack of putting the team's artifacts into an environment in which they would interract (dare I say integration server) for five weeks. It would stand to reason the most straightforward method for avoiding the IB is to integrate ALL of the executable artifacts early and often. There are a variety of tools to do this, Data Dude seems to be a promising IDE for the SQL Server database developer. I have also enjoyed success with putting Continuous Integration Processes, together by using Cruise Control .Net, Subversion and NAnt.

The key point here is the longer code for any project is unintegrated, the more unpredictable the integration process is going to be. To twist the Ben Franklin quote, "Don't intregrate tomorrow the defects you could solve today."

SSIS Configurations - Keep your friends close, your enemies closer

2006-09-15T10:25:00.000-05:00

OK, the thinly-veiled "Godfather" paraphrase is only remotely associated with this topic, but stay with me there is a tie-in.

In a recent effort to use SSIS for the ETL processing of a data mart, I found a need to manage different configurations between production, test and individual developer sandboxes. This is the nail for which package configurations is the hammer. It soon became apparent that while we had indeed implemented "a" way of say, managing the different connection strings, for example. Time would tell that we had missed something significant.

Our initial strategy had been to use Parent Package configurations to set the Value property of the Connection String to the correct server, catalog, etc. It turns out this works, but is not without it's disadvantages. When you try to edit the package configured in this way, it is necessary to put the connection string in the Connection String Value property, because you are not in the execution environment, none has been set from the Parent Package. Additionally, Expressions have to be used with the Connection Manager to accomplish the setting of the target value property with the parent package variable. This is further complicated by having to 'remove' this Connection String value before committing the package to version control, lest the SSIS Server attempt to connect to the developer machine and not the server indicated by the package configuration.

The better solution, it turns out, is to assign the Parent Package variable directly to the Connection String. This way, the true power of runtime package configuration is realized. Using this method, a developer can set one Connection String value in the child package, commit the package to revision control and be confident the Production, Test, etc configuration value will be used when the package executes. Furthermore, if all the developers on a team have identical properties for local execution, the values can remain set in the child package for the developer configuration, and overridden with the correct Production, Test, etc configuration at execution in those environments.

The final shot of setting the Connection String for the Data Mart connection manager is below. If you have any suggestions or questions please post a follow-up. Oh, the tie in to the post headline: Basically set the Package Configuration Properties as close as you can to the intended property.

Data, Data Everywhere

2006-01-20T08:37:00.000-06:00

I have spent the better part of the last 20 years working on software projects that leveraged, in one way or another a database. Software that analyzed banking customer profitability, retail sales, phone switch records, help desk tickets for a telephone utility, disease management, logistics to name a few. That doesn't even consider the torrent of data being generated by activity on the Web or RFID. What is a business to do with all of this data? How are decision makers going to stay afloat in this flood of data? How are managers going to find meaning (information) from the bits and bytes?

Presently, there are data structures that can address part of this problem. Data Warehousing/Mart structures permit users to conduct hypothesis verification. For example, division manager postulates "Widgets and Hoohas are draining our profitability". She can then proove or disprove the theory via inquiry of the Financial Data Mart, for instance.

This simply will not be suffiicient in the very near future. As more and more databases, come online, the manager will require a smarter way of using the data. Data Mining, using statistical and other modeling techniques to find patterns and relationships not readily apparent to even the knowlege worker, is how organizations may distance themselves from competition and draw themselves nearer to their partners and supply chain.