Advanced Systems Designs (ASD) developed BI Express to move data seamlessly between the System i and SQL Server 2005 to support the deployment of a Microsoft Business Intelligence solution. The product is an integrated System i and SQL tool that helps build, organize, and manage an enterprise-class BI architecture that is applicable to organizations of any size.

BI Express automates the process of putting together the BI infrastructure by capturing and moving data from the System i to SQL Server 2005 without user-development on either platform to accomplish the task. All the necessary coding on both servers, along with any file construction, is handled automatically. ASD designed the product to run in a lights-out environment and to support exception monitoring and reporting back to the server.

Fabick CAT's Experience

Caterpillar dealer Fabick CAT of Fenton, Missouri, a long-time ASD customer, appreciates the value of BI and is in the process of implementing a new system by moving data from the company's System i to an SQL Server 2005 repository for user-friendly access.

Much of the work has fallen to Donnie Jobe, Fabick’s System i administration and development manager. Jobe reports that the most time-consuming step in the implementation of a BI solution is building extractions to move and load data from the System i to the SQL Server BI repository.

"We had already started down the BI path and were excited about the possibilities," Jobe says. "Although we have found that SQL can do a lot for you, there are still things you have to do yourself. It took one person more than an hour to write a package to move a specific piece of data from the System i to the SQL Server."

Jobe says the BI implementation process has been "evolutionary," with Fabick and other CAT dealerships discussing ways to make the process even easier. "We'd been talking about the development of a data model -- one that could be replicated so that we could repeat it. I told ASD, 'I don't want to have to do this manually. We've got to do something to make this easier.' ASD delivered with BI Express," he notes.

"Prior to BI Express, everything was manual using SQL Server Integration Services (SSIS). If you wanted to use the data from File A, you wrote a package to move that data from the System i to the SQL Server. With BI Express, all I have to do is hit a command on the System i. There are one or two things that you run, and BI Express takes care of everything else for you."

Jobe says he provides more value to the Fabick organization when he is spending his time putting the data in the repository rather than writing applications to move data from one system to another. "What used to take me an hour now takes five minutes," he reports. "ASD came up with a tool that is easy, solid, and quick. It's a way to automate BI to take the manual work away so that we can focus on getting the data to the repository."

A Solid Foundation of Data

One of the things Jobe likes most about ASD's BI solutions -- and BI Express in particular -- is that he can trust the quality of the data. "I don’t want the end user at Fabick making decisions based on bad data," Jobe says. "These processes let us ensure that data is correct before we let the user have access to it. We push the data, and every day I feel confident with the data on the other end."

Jobe explains that BI is all about replicating system data to another platform. "You have to make sure that you have a solid foundation of that data. I need to make sure that the data in the System i is the same as the data in my BI system (SQL Server)."

As BI Express extracts data from the System i, Jobe says, it's counting the records as they move to staging. If that number doesn't match when the records appear in the BI system, he receives a notification that lets him correct the problem before giving access to the end users.

Identifying Applications

Jobe has found BI Express perfectly suited for Caterpillar dealerships regardless of their level of BI. As Fabick has been implementing its new BI solution, Jobe has spoken with another dealership that has been using BI for a number of years with SQL Server 2000 and is now converting to SQL Server 2005.

"BI Express is a great solution for this dealership," Jobe says. "Rather than do individual packages for each conversion, this solution makes a list of files, tells them the steps to take to transfer, and it's done for them. They'll be moving 120 to 130 files, and BI Express will save two weeks to a month in the process."

There are many different applications, says Brent Hoover, director of Consulting Services for ASD. "BI Express lets you pull your data off easily and move it to another environment. You don't have to have a separate repository, for example. You can have data in SQL Server and use SQL tools to report on it directly. BI Express would work in that environment."

Although BI Express is especially valuable when implementing new BI solutions, it continues to support BI every time additional data files need to be moved.

How It Works

BI Express:

• Provides a single point of control for data logistics through the System i
• Accelerates deployment of the Microsoft Business Intelligence solution
• Simplifies integration from the System i to SQL Server 2005
• Automates SSIS development for data movement
• Provides data logistics for industry-standard BI architectures
• Lets developers focus on the data model and report development
• Lets System i developers deploy with limited SSIS skills
• Provides a source repository for System i data native on SQL Server 2005
• Lets the System i deploy and manage SQL jobs
• Eliminates the need for expensive data-movement tools