Stein Mart's Move to SQL Server 2008's BI Space

Abstract

SQL Server 2008, with its built-in SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSlS), and SQL Server Reporting Services (SSRS) business intelligence (BI) subsystems, is a recognized leader in the enterprise BI space. In this interview, SQL Server Magazine's Megan Keller and Michael Otey talk with IIan Wajsman, director of IT Data Systems at Stein Mart, and Fausto !barra, senior director of Microsoft Business Intelligence, about Stein Mart's experiences migrating from a legacy platform with limited BI reporting tools to SQL Server 2008. Stein Mart chose a data warehousing and BI solution built on SQL Server 2008 with hardware from HP. [...] of this migration to a new system, Stein Mart not only got new tools for business analysis and forecasting but also was able to reduce the time it takes to generate its weekly sales reports from 14 hours to only 3 hours.

Headnote

The reasons, hurdles, and benefits

SQL Server 2008, with its built-in SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSlS), and SQL Server Reporting Services (SSRS) business intelligence (BI) subsystems, is a recognized leader in the enterprise BI space. In this interview, SQL Server Magazine's Megan Keller and Michael Otey talk with IIan Wajsman, director of IT Data Systems at Stein Mart, and Fausto !barra, senior director of Microsoft Business Intelligence, about Stein Mart's experiences migrating from a legacy platform with limited BI reporting tools to SQL Server 2008. IIan shares some of the main reasons why Stein Mart migrated to the SQL Server platform, the hurdles faced along the way, and the benefits that Stein Mart attained from the move.

Ibarra: As you know, most companies today are facing a data explosion with terabytes or petabytes of data that they need to manage. Forrester research estimates that the amount of data in large databases is growing aï 65 percent per year. One company facing such a growth in data is Stein Mart. Stein Mart is a large department store chain that's based in Florida. And, like so many other retailers, it's always looking for ways to get better intelligence and better insights into its business. Specifically, it was looking for a new BI system that would offer faster data reporting and new ways to analyze data. It had previously been using a BI system based on technology by IBM. Stein Mart chose a data warehousing and BI solution built on SQL Server 2008 with hardware from HP. As a result of this migration to a new system, Stein Mart not only got new tools for business analysis and forecasting but also was able to reduce the time it takes to generate its weekly sales reports from 14 hours to only 3 hours. And it was able to get significant savings of the order of $600,000 per year in IT operation costs. So, let me introduce you to IIan, who's going to be walking you through the project and what Stein Mart found out.

Wajsman: At Stein Mart, we faced some technical limitations because of our infrastructure and we needed to increase our BI capabilities. The existing solution we had was running on IBM AS/400, with an IBM DB2 data warehouse. Basically, we were reaching the limits in terras of table size and being able to scale out. Fausto talked earlier about data growth. Our data warehouse has actually doubled in size over the last six months.

SQL Server Magazine: When did the actual migration take place?

Wajsouuu We went into full-time production in June 2010, and it was roughly a nine-month process. Most of the migration time was actually spent migrating our ETL code from RPG and COBOL to SSIS packages.

SQL Server Magazine: Which of the SQL Server BI solutions did you implement?

Wajanaii: We're using SQL Server 2008 for our data warehouse, and all of our ETL code is in SSIS packages. For the front end, we're using SharePoint and SSRS for a lot of our reports. We're using PerformanceR>iiU dashboards, and using Report Builder to build some of the reports. And we're still using MicroStrategy for some of the legacy reports. We're using SQL Server 2008 R2 for our analysis server.

SQL Server Magazine: What's the size of your data warehouse?

Wajanan: Uncompressed it was seven terabytes. When we moved it into SQL Server, it compressed down to two terabytes. Currently, it's a little over four terabytes and growing.

So, there are two main reasons we moved to SQL Server. One is its BI capabilities, and the other is the cost. In our old environment, we paid about $50,000 a month in maintenance costs for licenses for the OS and the hardware itself.

SQL Server Magazine: Wow, that's expensive.

Wajsman: Yeah, and that was just for the hardware and the licensing, not to mention the amount of labor to keep that system running. So, we had quite a lot of options m terms of being able to apply that money to get a full-blown system without going beyond our existing budget the first year.

SQL Server Magazine: What hardware is your data warehouse is running on?

Wajsman: For the first month, we piloted with the HP ProLiant DL385 to make sure everything worked. Now, production is running on the HP ProLiant DL785, while the development environment is still running on the ProLiant DL385.

SQL Server Magazine: Did you run into any problems during the nine-month migration process?

Wajsman: Not really. There were some rounding issues - DB2 rounded a little bit differently than SQL Server. It was really immaterial. We also had a lot of code that was probably 1 0 years old. Trying to figure out what the code did was the most challenging. But once we understood the code, being able to put it into a nice package or a stored procedure made the ETL process a lot easier. Everything is documented now and the ETL process isn't dependent on a very specialized skill set.

SQL Server Magazine: Sounds like a big improvement. What are you using your data warehouse data for? What kinds of decisions and information are you getting out of it? And how are end users accessing the data warehouse?

Wajsmaii: Multiple ways really. Every Monday afternoon the executives get together and look at the performance of the stores at every level, including inventory level, sales, and individual line performance. They then use that information to make decisions for the rest of the week. They get very unhappy if they don't have the performance data in front of them - and that's what we were seeing with the legacy system. The load time was increasing, so there were delays in getting the reports out. Now we're way ahead of schedule.

Some of the new capabilities that we rolled out are what we call store dashboards. Every morning the store managers can look at the dashboards and see the performance of their store versus other stores and see how certain lines are doing in their store versus other stores. This information really gives them the opportunity to make some actionable decisions about what's going on in their stores.

Going forward, we plan to build dashboards for al! the other business units. The goal from a BI perspective is to become an information-based culture. We want to give everyone the opportunity to see how their business is doing and give them the information they need to make decisions.

We're also using the data warehouse as an integration source between our core ERP1 point of sale, and other systems. Because of the high processing capabilities, we're able to integrate these systems fasler by using the data warehouse as the centralized information and transformation source - the "bridge" between these systems.

SQL Server Magazine: You said you're running on SQL Server 2008- is that R2?

Wajsman: For the data warehouse itself, we're not using SQL Server 2008 R2. We're using SQL Server 2008 R2 in our SSAS server, which gives us better performance for cubes and the ability to build multidimensional cubes that support the dashboard.

SQL Server Magazine: You mentioned you were running on the HP ProLiant DL785. What are some of the specs for the server? How much memory does it have, and what kind of CPUs does that system use?

Wajsman: I believe we have 120 gigabytes of RAM. There are six processors with eight cores each, so we have a total of 48 cores.

SQL Server Magazine: And how many users is the data warehouse serving?

Wajsman: Both store people and corporate people are using it. Rounding up the numbers, about 1,000.

SQL Server Magazine: When you were evaluating replacements for your DB2-based data warehouses, how did you decide on SQL Server?

WajsroaD: Well, the main thing was performance. My biggest concern back then was being able to gei the reports out to the executives on time. So, I looked at appliances because they had, on paper at least, the best performance. We looked at the Netezza, Oracle, and DATAllegro solutions. I liked the DATAllegro architecture, but then I found out that it was no longer available because it was bought out by Microsoft. So, I looked at the SQL Server Fast Track Data Warehouse, which essentially put that same architecture into the SQL Server data warehouse. I did more research and found that Fast Track's performance was equivalent to an appliance and it gave us the ability to grow. The limit is 48 terabytes. Hopefully, we won't reach this limit, but at least we have that ability.

SQL Server Magazine:, Do you have any plans to implement Microsoft's PowerPivot for Excel or PowerPivot for SharePoint in the future?

Wajsroan: Yes, absolutely. We want to take advantage of PowerPivot for SharePoint to build more cubes and make those cubes available to users. The cubes will have a limited number of dimensions so that they're simple enough to use. The cubes wilt give users the ability to answer their own questions. PowerPivot will allow access to large amounts of data.

Let me give you an example. Recently someone asked us, "When people come to the store and buy leather chairs, how many do they buy at a time? Do they buy one at a time, or two or three at a time?" We had to send the question to a report writer on the database side to figure it out. The answer can impact how we package the product when we go to sell the leather chairs online. Do we want to sell them in sets of twos or threes or fours?

We want to give users that kind of information through PowerPivot for Excel or SharePoint. We want them to be able to point to a cube and drag a dimension, such as a particular SKU, into it. Even though there might be millions of rows, they'd be able to obtain the information they need.

SQL Server Magazine: Did you run into any hurdles transferring between the different architectures? Using the AS/400 must be quite different than using SQL Server, so did you need different personnel? Was there training involved? And how big of an IT staff did you have working on this project?

Wajsntan: We spent a lot of time building the project plan and scheduling it We did have to bring in some COBOL and RPG folks and some subcontractors because the people that were here had their 9-to-5 job also. So, we engaged Pragmatic Works. Its staff came in and helped us architect a solution, come up with a project plan, and execute it. As for the total number of people, 1 think there were about five people in here. Each came in at a different time, depending on what stage of the project we were in.

SQL Server Magazine: What do you use now for normal operations in terms of manpower?

Wajsman: We have two people who are responsible for the data warehouse.

SQL Server Magazine: Do they develop BI solutions, such as reports, for end users as well?

Wajsman: No, those are the people responsible for the data warehouse itself. We have a separate BI group that's made up of a senior BI manager and three analysts. We also use contract developers or report writers on a project-by-project basis.

SQL Server Magazine: How big was the team that was managing your DB2 data warehouse?

Wajsman: That team had four people who were responsible for the data warehouse, and there were people in other areas responsible for the ETL code. Today, it's easy enough for the two people I mentioned to manage the data warehouse itself and the SSIS packages,

SQL Server Magazine: In addition to your savings of $50,000 a month, which is obviously very substantial, what are some of the other main advantages or benefits that you've received from moving to the new system?

Wajsman: Basically, we've gained the ability to integrate with SharePoint, build dashboards, and get the reports done on time. In addition, with the old system, we weren't able to re-aggregate our SKUs because they changed departments over time. To get the performance history for an SKU in the current department, you need to reaggregate the data to see what the performance would've been like had the SKU been in that department a year ago. That requires a re-aggregation of the entire fact table. We wouldn't have been able to do that number crunching in the old system. We're able to do that in the system today. This ability gives us much better analysis capabilities of what our history was and what we should do going forward.

InstantOoc ID 136375