“Klaatu barada nikto”

--The Day the Earth Stood Still

 

A few weeks ago, I posted a blog entry about the phenomenon I call the Cheshire Data Mart - a data mart that disappears from the perception of the end user whose only interaction with the data is through a presentation tool.

Today I want to talk about the opposite effect - the inclination of the user to distrust the data mart data in all instances when the data does not tie perfectly to the user's program of choice. I call this "technoxenophobia" - the fear of alien technology.

I'm not talking about the need to validate the data mart loads - that's a necessary and understood process (at least from our perspective). I'm talking about the resistance that business users can experience when asked to work with data or tools that are outside their normal comfort zone.

None of our clients, to my knowledge, has ever had an ERP or OLTP system that perfectly matches their business. They're always forced to do some kind of work-around or to store supplemental data in odd corners (cough***Excel***cough) in order to meet their reporting needs. These "data cubbies" are not usually supported by a tight business process or (which would be even more preferable) enforced by the API of the OLTP system. The more manually-maintained and/or distributed these data cubbies are, the more likely it is one or more maintenance steps could be missed.

When we design and build a data mart, we try to incorporate all of these special cases and additional data, so that the data mart actually does align with the business's reporting expectations and requirements.

Since the data mart data is normally distributed to a broader business community via a business intelligence architecture than functionally specific OLTP applications (point-of-sale systems or accounting applications, for instance), any errors or omissions in the data cubby maintenance are exposed in this larger environment, frequently in a much more rapid life cycle than the users responsible for their manual maintenance expect. Additionally, the extended business community may know nothing about the supplemental data manually maintained by users outside their own sphere.

Result? Any presentation of unexpected data is blamed on the data mart, since it's the only new guy in town.

The data mart is the only place where all these disparate parts are brought together. The results can sometimes be startling for the end user, exposing data usage from different parts of the organization that can be either redundant or conflicting.

In my very earliest days in data mart development, I always accepted these accusations at face value and tried to find the errors in my code. I've learned over the years, however, that the first place to check is any source file that is heavily dependent on human intervention. The gradual exposure to the end users of these points of fragility in their own business systems is, in my opinion, one of the cool things about data mart implementation. The business community is given the opportunity to tighten their own procedures by observing the results of those procedures as defined by their own data.

The true secret to a successful data mart invasion is not conquest but self-knowledge and evolution. Not all aliens are hostile, after all.

This week, I started work on a data mart for a client whose source data resides on an AS400. The data access for the source system is handled via the iSeries ODBC driver from IBM. Getting the DSNs set up correctly to work with the client's 64-bit SQL Server installation was a little irritating, so I thought I'd share some lessons learned.

As you may already know, even though the database engine for 64-bit SQL Server is a 64-bit application (with a 64-bit dtexec executable for SSIS packages), the BIDS designer is a 32-bit application. This presents a challenge in package development and deployment when data connections either do not have a 64-bit driver (as in the case of Jet for Excel or Access) or when the 64-bit and 32-bit drivers exist, but require different executable versions (as with ODBC).

In this case, I needed to connect to the AS400 with the iSeries ODBC driver from within SSIS. The client had created the DSN for me and believed he had created a 32-bit DSN (since the driver description within the ODBC Administrator was labeled as 32-bit). However, the DSN did not appear in the DSN selection drop-down in the ODBC connection manager in SSIS.

The problem was that, regardless of what the driver was labeled, the DSN had to be created with the 32-bit ODBC Administrator in order for it to exist as a 32-bit DSN and become accessible to SSIS. Furthermore, in order for the package to run both in debug mode from within the designer (32-bit) and as a scheduled job in SQL Server Agent using the Integration Services step type (64-bit), you have to create DSNs with the identical name in both the 32-bit ODBC Administrator and the 64-bit Administrator.

So, in the Program menu for the iSeries access, you'll see a 64-bit and 32-bit ODBC Administrator:

You need to create identical system DSNs using both Administration modules.  The DSN defined in the 32-bit Administrator will be exposed and utilized from with BIDS; the DSN defined in the 64-bit Administrator will be utilized by SQL Server Agent and 64-bit dtexec.

To add a little more excitement to your day, SSIS has no ODBC source adapter for the Data Flow task, and in SQL Server 2008, the Data Reader source adapter has been retired and replaced with the ADO.NET source adapter.  In order to use the iSeries DSN, you need to create your connection manager as an ADO.NET provider (.Net Providers\Odbc Data Provider) rather than as an ODBC connection manager type.

"You have to be very, very careful with this, because it's thousands and thousands of years old…from when Mommy was little."

--Hana, age 5

After my daughter uttered the fateful words above, my husband took entirely too much pleasure in calling me at work and telling me about it. After all, he's not that much younger than I am. Considering that Hana is now a junior at the University of Oregon, I suspect that another three or four millennia have been added to my age from her perspective.

Nothing makes you feel older than comments from your children.

Or so I thought.

This week, 71.43% of PCon is attending the annual PASS (Professional Association for SQL Server) conference in Seattle. Yesterday was the first day of the conference, and over dinner, Toni, Kathy, Eric, Serena and I discussed some of the sessions we'd attended. No life-changing sessions yet (something I always hope for at PASS), but we'd each seen sessions on features upcoming in the next SQL Server release. Yay! New stuff! We love it!

However, Eric also related a conversation he had with a young man in the elevator at his hotel. They discovered they were both in town to attend a tech conference. The other fellow asked if Eric was attending the iPhone conference on the second floor. Eric replied that he was here for PASS. The response:

"Oh, you're an old school geek."

It was very lowering.

According to this whippersnapper (I failed to ask Eric if the youngster was still wearing his Cub Scout uniform, although it wouldn't surprise me), PASS is the technological equivalent of AARP.

Well.

I refuse to be dismayed by this. I'm looking forward on my session today on SSIS design patterns, a Microsoft chalk talk on metadata-driven ETL and the session tomorrow on using agile development techniques in SSIS.

However, when I get back to Portland next week, I'm going to have to discuss my job title with Bill. In another month or so, "Senior Consultant" might be construed as a politically incorrect term in my case. I'll draw the line at "Consultant Emeritus" though. Even at my advanced technological age, I still look forward to learning cool new stuff – even if I have to look through my bifocals to do it.