"We are happy we made the decision of migrating to Sybase IQ. Our industry requires analysis on large data volumes within a constrained time. The data grows in leaps and bounds and you can't continue asking for more space. Sybase IQ has helped our business keep costs under control along with automating the delivery of the information."
General Manager, IT
Spice Telecom, a large telecommunications company providing services across the Indian states of Punjab and Karnataka, uses a host of Sybase technologies including Sybase IQ, ASE, PowerDesigner, and Replication Server to populate a data warehouse with raw mobile and landline call data. The query speed and data compression of Sybase IQ has been the key to implementing the data warehouse successfully. Business Advantage Key Benefits Sybase Technology Industry
Spice Telecom, a large telecommunications company providing services across the Indian states of Punjab and Karnataka, uses a host of Sybase technologies including Sybase IQ, ASE, PowerDesigner, and Replication Server to populate a data warehouse with raw mobile and landline call data. The query speed and data compression of Sybase IQ has been the key to implementing the data warehouse successfully.
Spice Communications Ltd. (Spice) is Punjab's second leading telecommunications company, and with over three million current subscribers, Spice is one of the region's fastest-growing cellular phone service providers. Spice offers a wide range of voice and non-voice cellular services to subscribers on a postpaid or prepaid basis. Using Sybase IQ, Spice maintains a 12 terabyte data warehouse populated with three months of customer call data. Spice runs advanced multi-dimensional analytics on the information to identify sources of revenue loss and uncover gaps in its service offerings. The data warehouse has lowered costs, improved data quality, increased their competitive edge, and fostered exceptional growth.
Information Abounds, the Problem is Getting it All Processed
In the past decade India has undergone a phenomenal technological transformation, increasing mobile capabilities and rapidly expanding user bases. In fact, a new Spice cellular subscriber is added each second of every working day.
Through the course of doing business, phone companies collect enormous quantities of raw information on customer use patterns. The bulk of the data originates from Call Detail Records (CDRs) that are created for customer billing and financial transfers between telecom companies. Every call by each of Spice's more than three million subscribers adds to the data. Most of the information comes from Spice's own customers, but they also receive data from the other telecommunications companies as those calls pass through the Spice infrastructure.
Profit margins are thin in the highly competitive telecommunications industry and even small degrees of efficiency improvements add up and fund investments in services and basic infrastructure. In turn, service and quality become key differentiators between competing telecommunications companies. Sanjay Srivastava, General Manager -IT, explains part of the need for analytics, "The telecommunications industry is one of the most challenging and data-intensive marketplaces in existence. Competition is relentless, and we have to constantly make on-the-spot decisions based on the information we have available. We need to be ready to counter our competition's offers and be able to provide more options without endangering profitability and keep our costs to a minimum. With more data we can model different scenarios."
The First Incarnation of a Data Warehouse
Spice Telecom's initial foray into data warehousing was built on traditional OLTP database. The data warehouse needed to address the requirements of the marketing, finance, customer service, sales, and engineering departments. However, it did not take long for the sheer volume of daily input data to begin swamping the original system. Query response times increased and it became painful to extract even simple operational counts. Concurrent users from multiple departments slowed the system even further while memory usage reached unacceptable levels; it was not unusual for the system to be unavailable to concurrent users.
The inability to analyze data limited the company's decision-making process. Not only were ad-hoc, "what-if" queries out of the question, the standard queries that monitoring the pulse of the company were becoming unwieldy. Varundeep Kaur, Manager - IT, describes the challenge, "It rapidly became obvious that providing timely access to accurate data, irrespective of its source, was turning into a business-critical issue for us. Call traffic CDRs were large and increasing daily and multi-dimensional analysis had become a distant dream. Around that time, to even further raise the stakes, the Telecommunications Regulatory Authority of India imposed a stringent set of information reporting requirements that were designed to elevate telecommunication operational standards and enhance the country's infrastructure. It was clear that we needed to make key changes in order to continue our success."
In addition to the performance issues, Spice Telecom needed to address the system's significant storage constraints. The data warehouse needed a lot of information, but also needed to take up less room. Maintenance functions like backups, transfers, and redundancy were a drag on the entire architecture and increased the number of potential failure points. Temporary fixes included creating smaller, summary samples of data that –while not as good as the real data– were capable of being processed in a timely manner. However, the process of creating the smaller samples was itself a drain on both processing and storage resources. The system, with its associated workarounds, was essentially collapsing under its own weight.
Defining the Criteria for Success
Determined to create a successful data warehouse, Spice assembled a list of features and considerations for their next generation data warehouse. Based on what the IT team learned, they identified these criteria for the new data warehouse :
- Extreme Performance – Performance was given the highest priority. Having a huge data store without an ability to perform reasonable analysis means the essential function of the data warehouse is lost. Minimally, the solution must have sufficient performance to run basic daily reporting. Ideally, it would do much more.
- Small Load Window – The ETL process (extract, transform, and load) from the staging repository into the data warehouse needed to be fast. With the previous implementation, the window of time to run the ETL kept increasing. In some cases it was taking so long there was concern it would not complete before it was time to load the next batch of data.
- Support Existing Tools – Spice wanted to avoid proprietary, locked solutions and try to preserve as much of their existing architecture as possible, while delivering on the performance demands of the business.
- Use Existing Hardware – Spice already had a significant investment in powerful HP servers, the data warehouse solution would need to maximize the existing HP hardware.
- Storage Space –On a large terabyte scale, disk storage itself becomes a significant cost factor. Spice needed to find a solution to reduce the storage requirements and attendant costs.
The team also wanted the solution to help them manage database growth and lower the overall maintenance costs and administrative efforts.
The Foundation of a Highly Functional Data Warehouse
After evaluating potential solutions, the team chose an architecture powered by Sybase IQ –developed specifically for advanced analytics. Chosen because of its reputation for performance and data compression, Spice anticipated a significant boost in analytics with Sybase IQ.
Sybase IQ represented a break from the traditional RDBMS, which –given the problems they were facing– was exactly what Spice needed. "With Sybase IQ, we are seeing at least a 10X performance increase over how we were doing it before. For routine queries, the rate is usually even higher," says Sanjay Srivastava. Varundeep says "I would recommend Sybase IQ for almost every data warehouse. Sybase IQ is a column-based analytics server, which is extremely good for query analysis and loading. The learning curve is not steep and Sybase IQ let us easily experiment with data. It is so simple to run that our operations work has reduced significantly, and database maintenance is almost negligible."
The project took about four months with another month spent testing before it was ready for production. The team used the Business Objects platform for portions of the ETL and reporting processes, and Sybase PowerDesigner for data modeling. Varundeep Kaur explains, "We use PowerDesigner for the data modeling, creating logical models and then we expose those models to the Sybase IQ database. The models also act as documentation so we can keep track of the primary keys and foreign keys."
Spice uses the latest version of Sybase ASE to augment the data warehouse. Sybase ASE, a traditional RDBMS, is used as a data repository for both reporting and, in conjunction with Sybase Replication Server, loads external source data in real-time into the Sybase IQ analytics server. Spice Telecom also uses SQL Anywhere to maintain connectivity to Sybase IQ from local data sites.
Because this external data is maintained in third party applications running on an Oracle database, Spice uses Sybase Replication Server Option for Oracle along with Replication Server to immediately capture data changes in the Oracle system and move them into Sybase IQ. Replication Server's flexibility also lends itself to a number of other uses, including extending the life of the OLTP database. In reflecting on Sybase's contribution to the Spice data warehouse, Sanjay Srivastava notes, "I am happy to say the Sybase environment actually did wonders for us."
Empowering – Sybase IQ, Faster, Deeper, and Broader Queries with Room for Growth
Spice stores 12 terabytes of data that –if it were not compressed by Sybase IQ– comprises about 25 terabytes of information. Sybase IQ gives Spice the ability to analyze trends across the entire data warehouse. Before Sybase IQ, Spice was spending time and processing cycles to distill the data down to summary tables just to make it manageable. Now Sybase IQ gives Spice the ability to analyze trends across the entire data warehouse..
A useful data warehouse primarily depends on having a large enough data sample to identify trends, but the queries that sift the data must also run to completion within a reasonable timeframe. The benefits of a data warehouse can range from the daily and weekly reports used to steer the organization, up to advanced trend analysis that seeks patterns in the information, patterns subtle enough to escape all but the most rigorous analytics.
Competitive CDR traffic also flows through the Spice system. With Sybase IQ, Spice has enough analytics power to collect and analyze more of this additional data. Varundeep Kaur is very pleased with their data warehouse, and she is not alone, "Our C-levels are very happy with the performance of Sybase IQ. The business is now able to look at and analyze dimensions that were previously out of reach. Analysis that used to be a pain and took hours or even days to complete has been reduced to minutes and hours. The space savings is huge. Load windows have reduced from hours to minutes. Querying is a cakewalk and a pleasure. Users are now able to drill down to very fine granular levels without any issues. Our data quality has also improved as users are now able to easily reconcile discrepancies."
Happy Users Enjoy Unforeseen Dividends
With the Sybase IQ-based data warehouse, the problem of concurrent users dragging down the system or being knocked off the system has evaporated. The data warehouse is readily available to the people who most require the information. In fact, the rejuvenated system capability has spawned a new batch of "what-if" questions–something that was previously impossible. Srivastava says, "With the performance now available, the number of ad-hoc queries has shot up and it gives a different flavor to the business when users can analyze whatever they dream up."
People enjoy tools that work. Varundeep Kaur likes visiting the users these days, "The performance makes people cheerful. Whenever you walk down to the users and ask, ‘How is everything going, how is Sybase?' They do not complain about the speed. You find them very excited with the analysis, they are happy users, and that is very good."
Data Breeds Success and Success Breeds Data
Spice Telecom is riding a wave of explosive growth in a competitive market. Rich business intelligence is one of the driving factors behind their growth. Spice adds a new customer every second of every business day. However that very growth adds to the overall volume of data to be analyzed, creating a massive store of useful information. Spice turned to Sybase to help it avoid being crushed under the weight of this success. The results have been a 10X or better improvement in speed coupled with 40-50% data compression. This means the deep analytics required to keep the business on course will continue to create new customers and fuel the positive feedback loop now and into the foreseeable future.