Overview:

            Kinetico is a manufacturer and distributor of residential, commercial, and industrial water conditioning equipment. Since its inception in 1970, Kinetico has grown and expanded its locations.  They now have operations in France, UK, Denmark, and Canada. Because of growth and expansion, Kinetico had many different sales reports being produced by various departments. There was a native report writing tool included within their ERP package, customized RPG programs, and excel spreadsheets that were all utilized for reporting as well. Until recently, there was no standardized sales reporting tool being used, and this lack of standards caused inconvenience and operational inefficiencies. Kinetico needed a standard template for reporting in order to bring in all their data; including, ERP package information, data from the warranty registration system online, and financials.  Their solution was to search for one main source in which all reporting could be done.

Situation:

            Because of the rate of expansion and growth, Kinetico's sales reports were dramatically increasing. The increase in sales reports led to these reports being produced in many different areas. For instance, there was a report writing tool included in the ERP package, customized RPG programs that were written for sales reporting, along with reporting data in the form of Excel spreadsheets. Kinetico found that having many different points in which sales reports were being produced was problematic and forced them to look for another solution.

            There was no single or centralized place to create and reference sales reports. According to Ned Sherry, Director of Information Technology, "What we had at Kinetico was a lot of different sales reports being produced from different areas...We found that we had a lot of different points where people were producing sales reports, so we wanted to have a standard sales reporting tool."

The Information Technology team knew they needed a system that was easy to navigate and use, yet adaptable enough to fit their growing business so additional sales reports could continually be added over time. "We wanted something that was very user friendly, flexible so that we could add additional reports as time went on," states Sherry, "and also flexible from that standpoint that we could have a standard template for report, but allow us to use data selection to bring forth the information we were looking for without having to sort through all the data." A parameter driven system was needed to bring all information and data from the different locations into one spot to be used as the main source for all reporting.

      After a visit from Kinetico's sister company, Sprague Energy, the idea and concept for a parameter driven, centralized sales reporting tool began to be outlined. "[Sprague Energy]... showed us a data warehouse that they developed for sales reports and we liked the idea that the concept was indefinitely what we were looking for. We kind of took that idea and ran with it and we started laying out the different types of reports. We sat down with several different people and said ‘okay what is it we're looking for from a sales report.'  We actually came up with maybe about ten sales reports. We said ‘let's also bring in our financial reporting and let the data warehouse become not only a sales reporting tool but reporting for our financial reports.' So we laid out the different financial reports we were looking for..." Ned Sherry said.

Solution:

            Plans for the sales reporting project began in early 2007. Within the Information Technology Department at Kinetico, a scope of work document was created.   Sherry states, "We didn't have the resources in-house nor did we have the experience. After some research, we settled on we wanted to use Microsoft SQL so we said ‘okay, we're looking for people who have that type of skill set and that type of experience.' We brought in an architect from the outside to help us with the design, but again we didn't have the resources in-house, so we really needed a company that was experienced with Microsoft, the .NET, and one that had done data warehousing, so that we could bring those resources in to help us build it." Thus, Kinetico began to search for the company to best help them.

Ned Sherry explains, "As we did our research, we came across Solutient as a possible company, worked with Solutient on what our scope of work was and through the whole process we ended up selecting Solutient as the company of choice for building it." Together, Solutient and Kinetico decided upon a timeline for the sales reporting project to range from the beginning of April to the first of December. This timeframe included the design, the architect, loading SQL onto the servers, building databases, setting up the staging environment, and creating the SSIS packages. 

Working fulltime at Kinetico for the entire length of the project, Solutient's Oleg Lisheba was the main builder of the data warehouse. Lisheba along with Solutient Vice President, Gary Rabung worked with Kinetico to build everything that was originally designed. Sherry described what Rabung and Lisheba did for Kinetico, "Gary did the installation of SQL. He built some cubes for us and then we brought in Oleg... He [Oleg] was just building everything from the tables, setting up the databases, building the SSIS packages, setting up a staging area or bringing data down from an AS400 and we were also bringing data in from a web based application. We have a couple different sets of data coming in from different locations. He built those packages and the scheduling of them, helped us through the process of cleaning up some of the information as we brought it down into the database, and also building the reports."

There were three main requirements for this project. The first of the requirements was to make sales reporting flexible. In order to make them as adaptable as possible, the reports had to be parameter driven so an employee could start at a high level of information and continue to drill down. Sherry better clarifies this saying, "A great example would be, you want to look at our sales group, we call our sales group 300. Through the parameters you could select sales group 300 and you could drill down through the region, down to the district manager level, all the way down to the customer level and from there into the product.  It's one report and you continue to drill down." 

Lisheba was very helpful in helping Kinetico to hit this requirement. "We put some specs together on the reports. We had some examples, but actually taking that information, asking us the right questions and building them, Oleg really quickly understood the systems and the data that we were pulling the information from," Sherry stated. "He really understood the information format, the relationship the customer tables and the product tables and our sales history. As he grew in his knowledge, he was able to quickly and easily help us in the design and the build of additional reports."

The second requirement, in addition to a user friendly interface, was to have the ability to do pivot tables through Excel. According to Sherry, ". . . we had some power users that wanted to be able to do pivot tables through Excel. We had a couple of different cubes that we knew we wanted, so Gary actually helped us with the design and the build of those cubes so that we could use Excel's pivot table process to extract information and do some ‘what if' analysis." Rabung took care of the installation of SQL Server, configuring it, and building the additional cubes needed.

The final key requirement dealt with the security of the sales reports. In order to maintain security in the new data warehouse, it needed to be incorporated with Active Directory. By integrating the data warehouse into Active Directory, the security and profiles did not need to be recreated in various areas. Sherry better explained it, "...The web-based interface that they have, the system runs on our internal servers, so they are just basically browsing to a URL, a server within our network, and based on their level of security...the groups then apply to the data warehouse reports. The end users logon to the network in the morning, they browse to the data warehouse, and they're presented with folders that contain the reports that they need. They no longer have to search for ‘who is the person that used to run this report' or ‘this report is hard coded with these parameters, I'd like to see something a little different, now how do I go about getting those changed.'"

Overall, the data warehousing, sales reporting project went smoothly. The data warehouse was slowly being phased in as reports were being built, allowing the rest of Kinetico employees to become acquainted with it. In fact, the project was so successful and well received by Kinetico employees that many more requests for reports began coming in. While the range of the original report number increased, the project was able to be completed on time and even more impressive under budget. Sherry asserts, "...once the user started seeing the power...we actually had more and more report requests coming in. The scope of the number of reports we wanted greatly increased, but on a timeframe basis we brought the project in on time and under budget."

Benefits:

            The data warehousing project has made a tremendous difference to Kinetico and its employees. Describing the change of reporting from before the project to after, Ned Sherry states, "It's like night and day." There are several specific benefits resulting from this project.

Self-Sufficiency:

            Kinetico employees now have greater access to information through the new data warehouse. Employees have less dependence on others which provides greater access to information. Sherry states, "They're more self-sufficient. The information is at their fingertips twenty-four hours a day, seven days a week." Employees can now access any desired information at any given time.

            Contributing even more to the employees' self-sufficiency is the ability to have a specific desired report at their disposal at the same time every day. "One of other nice features about this system was...if there was a certain report they wanted and they had some specific parameters they wanted to have passed through that report and they also wanted that report delivered to them on a regular basis, whether it was weekly or monthly, we can set up what's called a subscription and so that report will be emailed to them and they can choose whether it is an Excel format, CSV, PDF, XML and on that date at that time, that report appears in their inbox," Sherry explains. The individuals no longer have to rely on others to receive reports. They are now guaranteed to have the report on time everyday.

            Less Man Hours:

            Before the new data warehouse was implemented, employees from various divisions would have to go through a procedure in order to receive the desired report. "In the past someone was always having to run these reports, print off multiple hardcopies, and then distribute them," Sherry continues. "For the people that weren't located at our facility, our off-site employees, they would have to have the hard-copy report sent via mail at times." Those employees located off-site had a waiting period to receive a report, which included the hours spent finding the requested report by another employee.

            Now less man hours are required to deliver information.  Kinetico employees can focus more time and energy on other work related activities. Ned Sherry said, "You're putting the information in the hands of the people who need it, when they need it. They're not having to call someone and ask them to run a report and if it isn't quite what they're looking for they have to call the person back and say, ‘hey can you change this?'"

More Reports:

            The data warehouse was so well received that more reports were continually being added to it. Kinetico decided to phase in the data warehouse as reports were being built, thus as reports were rolling out, employees could look at them. Sherry explains, "We actually have more reports now than we originally thought we were going to have at the end of the project. That tells me that the user community really took to liking this. They like the tool."

Because the data warehouse is much easier to use, the employees can sit back and find ways to better the reports. "They like the mechanism in which it gives them the information, the ease in which they can get it and so they started coming up with ideas on ‘hey what about this report or this style report' so that told me that they really liked the project and the end result," Ned Sherry stated.

Easy Navigation:

By having a parameter driven system, Kinetico employees are able to find information faster and much easier. Employees are able to quickly and efficiently navigate down through different area levels and find what it is they are looking for.

Sherry expanded on this benefit by saying, "...we looked at seven or eight parameters that were common across all or lines of businesses and we used those throughout all of our reports. Master planning family, model, products, customer group codes, our sales groups; those are the type of parameters that someone can select on...the head of our residential line of business can look at his information and not have to sort through the information out there for maybe our OBM or National Accounts. If they're looking for information on a particular product or maybe a matter planning family...they can drill down to the different levels from [Master Planning Family] down to a series down to a model, to a specific part number and see exactly what it is they're looking for, whether it's dollars, units, what month it was sold in."

Conclusion:

By working on this project together, both Kinetico and Solutient have established a strong relationship. The companies know each other's needs and how to address them. Ned Sherry states, "We've already brought Oleg back for a couple additional projects, one unrelated to the data warehouse . . . we saw the skills he brought to the table. He knows us. Solutient knows Kinetico. They know the type of data we're dealing with, so that makes it a lot easier. You don't have the learning curve of bringing in a new person and having to teach them a lot of the stuff that is important for them to develop the application we want, but is just background information so they can get right to the job and get the task done."

With the knowledge the two companies have with each other more work can be accomplished in a shorter period of time. Enthusiastically Sherry says, "The business process and the technical environment, the technical infrastructure, those things are what Oleg and Solutient have learned in working with us on this project, so they have the base of knowledge now. When we bring them back for other projects we won't have to go through all that. We'll get right to the project and deliver the end results."

The data warehousing, sales reporting project was very successful and extremely beneficial for Kinetico. When asked if he wished anything was done differently with the project, Ned Sherry responded, "I look at the project now and a lot of times with large projects like this you can go back and say, ‘well if I had to do it all over again.' You can usually come up with a list of a lot of different things that you could do differently. I really don't have a big list on that, other than I would have done it sooner. Looking at the approach we took initially laying out what it was we were trying to achieve, the process we went through in selecting the vendors, I think we selected a vendor that delivered exactly what they said they would deliver."