Arcane Solutions: A Data Warehouse

Tomorrow I’m doing a presentation for Microsoft, in conjunction with the vendor that we outsourced the project with, CTS ( Our solution for the data warehouse was pretty cool, and I thought I’d briefly mention the technologies involved.

A SQL Server Integration Services solution reads data from our Oracle transaction system, and places the data into a SQL Server 2005 warehouse. This SSIS job runs every fifteen minutes, in what we call a “right time” scenario.

Next, the users first go to a SharePoint portal site. We have a master site for the company, the sub sites for each plant, then within those for each department. Our SharePoint site holds some report viewer SharePoint web parts, which hold SQL Server Reporting Services reports that act as our KPI (Key Performance Indicator) reports.

Also on the page is a special SharePoint web part we custom wrote in C# using the .Net 1.1 framework. This part reads a list of valid reports from a SQL Server 2005 table, then launches an ASP.Net 2.0 site.

The ASP.Net page, written in C# on the 2.0 framework, primarily houses a Microsoft report viewer control. It also has a drop down combo which holds a list of saved queries. These allow users to quickly pick combinations of stored report filters to apply to the reports.

We’ve also given users the ability to create ad-hoc reports by linking in the Report Builder control that’s part of the SQL Server 2005 suite. This lets the users create their own reports, without the necessity of providing ODBC connection info as we would with say Access.

I certainly don’t think Microsoft is perfect, as I’ve written before they certainly have room in some areas for improvement. But when it comes to their business applications, they are outstanding. I don’t know of any other company that puts out a suite of tools that integrate so nicely together, and provide the users such a complete, seamless solution.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s