Of 180 loads, about 120 of them are just daily incrementations that run overnight. They contain the activity for the previous day. We only post transactions to credit cards at night, so that load only has to happen once every evening and then it’s done for the day. The data isn’t going to change during the day in the data warehouse. Then we kick off Reporting Services scheduled reports and they run against that data, using snapshot reports, so that when people look at those reports during the day in Reporting Services, the query has already been run against the data warehouse. Users think performance is extremely fast because the only thing Reporting Services has left to do is just to render the data. Reporting Services can render that data to 500 people quickly because it’s doing some web pages without having to touch the data warehouse itself—because we know in these particular reports that the data hasn’t changed, which saves the data warehouse from having repeating hits from all those reports.
Analysis Services also improves BI performance. Our Analysis Services server is separate, which provides some relief for the data warehouse. All the hits are done by the Reporting Services and Analysis Services servers. There are also some loads to the data warehouse that occur every 15 minutes or every half hour for load frequencies, and the data warehouse has to service them.
We use and leverage Microsoft’s stack pretty heavily. Every database engine has things it’s good at and things it’s not good at. A good DBA, a good schema, and a good process should leverage a database engine’s strengths. That’s what we do with SQL Server. It performs very well—queries take less than a minute on average, and we never have a query that takes over the SQL Server box. The data warehouse takes abuse constantly and it performs very well.
Bearly: Did you implement your BI platform
inhouse? Or did you contract with someone or work with Microsoft directly to set it up?
Van Zanten: We did it ourselves.
Bearly: What was the biggest technical hurdle that you had to overcome?
Van Zanten: When we were implementing it, our ETL process was done in DTS (we used DTS for order of precedence and dependencies), but almost all of the heavy lifting was done by SQL Server and stored procedures. In that space, the biggest technical hurdle was data quality and getting things married up. DTS wasn’t nearly as robust as SSIS is at sorting and weeding things out, so we had to do it by hand. You had to run SQL queries when cleaning queries, and look for inconsistencies.
Bearly: Did you encounter any problems other than with DTS?
Van Zanten: The quality of the data and the timeliness of our data sources is our biggest challenge. The other big challenge is having people understand the data warehouse—what’s in there, what’s not in there—as well as understand our process, such as knowing how to use Reporting Services and understanding how to pivot a cube. Cubes seem to be a leap for people. We could have the best data warehouse ever, but if nobody ever used it for their work, then it would be a failure.
Bearly: How long was it from when you started building your data warehouse to when it was functional for your users?
Van Zanten: Four months. The first high-value test reports were published in six months.
Bearly: Were you able to stay within the budget you were given?
Van Zanten: We were definitely within budget. Our first major purchase was the HP Itanium 8620. And we had a very small staff; it was actually just me the first year. I was the whole development staff on this project. Even now my staff includes only eight people who are dedicated to the BI data warehouse infrastructure, so it’s still fairly economical to run if you set it up correctly. The really giant purchase we made was the HP StorageWorks XP24000, because it was a seven-figure purchase. But we just bought that last year, so by that time we were a billion-dollar company, and all this corporate data was giving us a 700 percent return on investment; we could afford it. We were able to deliver, which is huge, and our data repository was delivered on time and correctly. It’s really important to think about how you’re going to show your return on investment.
Bearly: Are you planning to migrate to SQL Server 2008 when it’s released?
Van Zanten: We have SQL Server 2008 on a development box and for our test data warehouse. Our largest fact table is 2TB; but 700GB would likely go away if we could take advantage of SQL Server 2008’s compression.When SQL Server 2008 is released, we plan to upgrade our production data warehouse.