With many organizations having established data warehouse solutions there comes a point where the solution needs to be extended in an unusual direction. This challenge presented when a European mobile virtual network operator (MVNO) wanted to add both compliance and archiving functionality to their existing data warehouse.
If you don’t know, a MVNO is a mobile phone operator that directly services its own customers but does not own the network assets, instead leasing these from a mobile (non-virtual) network operator. The company’s profits are derived from the margin between the price a subscriber pays to the MVNO and the wholesale cost of purchasing capacity from the underlying host operator.
MVNOs differentiate themselves by very competitive pricing on certain parts of the call package, for example ethnic MVNOs target ethnic communities by providing inexpensive calls to their home country. To enable such offerings and to track costs, especially where subscribers start using the service in unexpected and possibly costly ways, is a key business function and this is where the data warehouse is used.
The primary role of the data warehouse is to profile subscribers with their associated attributes and their call usage pattern. Our client has this built in a database that provided them with all of the subscriber information and three months of call detail records (CDRs) and data detail records (IPDRs). Beyond the three-month limit there was aggregate data about calls but no direct access to the original call records.
This caused the business a number of challenges:
- To provide a cost effective query-able archive of CDR/IPs.
The original solution archived the incoming CDR files to disk and subsequently to CD. This meant that if specific set of records were required they had to be recovered, re-loaded and then queried. - To enable data retention law compliance
EU law (amongst others) requires Telcos to retain call and data detail records for a period between 6 and 24 months. Whilst the MVNO was in compliance with the regulations, much of the data was stored on CD and so suffered from the same problem as archive data. - Enhanced CDR analysis in the existing data warehouse
The business was looking to be able to analyse more of the CDR data on an ad hoc basis without the need to store more data in the main data warehouse. They also wanted to be able to change the stored aggregates quickly and easily when new data patterns were found. Previously this had again required the recovery and re-loading of archived data.
The objective of the project was to design a solution that allowed CDRs to be stored for immediate access for at least two years and that could easily be extended to support up to seven years of data. As a consequence it is also envisioned that the solution would improve the performance of aggregate load and rebuild when required because the CDRs are available online.
Solution Considerations
Given the business requirements a number of technical aspects were then considered. These can be outlined as follows:
1) Should Hadoop or NoSQL solutions be used?
Hadoop and other NoSQL solutions have grabbed a significant number of column inches over the past couple of years and in the right circumstances they offer a very cost effective and high performance solution. The discussion about the use of a Hadoop or NoSQL solution centred on the following aspects:
a) A strength of Hadoop and NoSQL solutions is in allowing easy mapping of unstructured data. The data files in use had a strong pre-defined structure – and importantly any change to this structure would signify a material change across a number of business systems. There is no unstructured data that we wish to analyse.
b) There are no existing skills within the IT organisation. This in itself should not be a blocker to technological innovation within a company. However, this particular project is about maintaining business as usual and can’t be used as a vehicle for internal training.
c) NoSQL – well exactly that – not only does the IT department have no skills but our user community has a range of SQL tools – if the NoSQL route was selected then any data extraction would fall back onto the IT department rather than self service by business users.
d) The business has a range of Business Intelligence tools such as Business Objects and these require an SQL database. It was noted that at this point in time there are no significant BI tools that work with NoSQL solutions
e) Internal perception. Some companies choose to be at the vanguard or bleeding edge of technology, others are the last to change or upgrade. This company favours the middle ground of choosing established technologies that offer a route to the future.
2) If it is a database then what type of database?
Once the Hadoop debate had been put to rest the discussion turned to what type of database to use. There were three options that were considered.
a) Row Store Databases
A row store database is the type of database that most people will be familiar with. Each record is stored as a row in a table and queried using SQL. The organisation has a number of different row store databases platforms for operational systems however the existing Data Warehouse is based on Sybase ASE so this would be the natural choice. The primary benefit of this would be to continue to use existing technology, however as a large database it would not necessarily be performant and would require significantly more disk space than the current raw files as both data and index space would be required.
b) MPP based data warehouse appliances
There are a number of massively parallel processing (MPP) architectures. These also store the data in rows however they apply many CPUs with data on disks close to the CPU to speed up the problem. Whilst these can be very attractive solutions they posed two specific problems to the project.
Firstly it would not be cost effective to retain the existing data warehouse on the current platform and create an archive store on an MPP solution because MPP solutions generally match CPU and disk together for performance. Therefore to get high performance there would be a significant under-utilisation of the available disk resource.
Secondly (and as a consequence of the first issue) if the project were to move the existing data warehouse to an MPP platform it would require porting/rewriting of the load processes (also known as ETL) that would add cost and time. This was exacerbated by the use of platform specific user defined functions (UDF) in the current data warehouse that are not available on the MPP platforms.
c) Column-Store Databases
The third option was the choice of a column store database. To the user these look identical to a row based database but behind the scenes the data is actually stored in its columns with a record of data being made up of pointers to the data held in the columns.
Column store databases consequently store only one copy of each value in a column. If you have a column in a table that contains the calling telephone number then each telephone number will be repeated many times. Storing it only once and using a pointer to the string considerably reduces space and because there is less space used there is less I/O required to retrieve the data from the disk that also speeds up the query time. Both a smaller disk foot print and faster query time make column store databases very attractive.
Column store databases are considered more difficult than traditional row based databases to update because the update has to check the data in the record map and in the column store itself. This is not an issue for data warehousing and archiving solutions where the data is usually loaded once and not updated.
Despite a lack of general awareness of column store databases they have been around since the mid 1970s and Wikipedia currently lists 21 commercial options and 8 open source solutions. The most established column store database in the market is Sybase IQ, which fitted nicely with the existing data warehouse on Sybase ASE.
Based on the choices the client chose to go with a column-based database. The next stage of the project was to build a prototype Sybase IQ store to understand what sort of performance and compression we obtained.
Solution Design
The solution design was to create a simple schema with two types of tables, one for the CDR data and one for the IPDR data. It was also decided that the tables would be split into multiple datasets based on the start date of the call or data session. As a result we ended with a number of tables of the form CDR_2012, CDR_2011, etc. and IPDR_2012, IPDR_2011.
Views were created over these tables, called CDR over the CDR_XXXX tables and IPDR for the IPDR_XXXX tables. A further view was created called DATA_RECORDS across all CDR_XXXX and IPDR_XXXX tables but only selecting the fields that were common to both (start time, cost, record type, etc.). This was done for future ease of management, as it would be possible to remove an entire year of data simply by rebuilding the view to exclude the year and then dropping the table.
One year of call records consisted of around 500GB of raw data and 1 Billion rows of CDRs. When loaded into the database and it took up less than 100GB (achieving a compression ratio of 5.7:1). Similar results were obtained for the IPDRs. Therefore a 2TB system (4TB when mirrored) could effectively hold 7 years of history even allowing for predicted growth.
The testing split the performance into two aspects – the load time and query performance.
The CDRs are delivered from the host operator on an hourly schedule as fixed format flat ASCII file that typically contained around 35,000 records, where each record was 583 characters long containing 80 fields. Loading an average file (typically 35K records in a 2MB file) took around 1 second and since there is around 200 files per day this was not a burden on the system, even allowing data to be loaded throughout the day rather than a single nightly batch to allow more immediate access to the CDRs.
Initially 2 years of data was loaded although this will be allowed to grow over the coming years. User queries directly on the Sybase IQ database ranged dramatically depending on the type and complexity but were typically 10 to 15 times faster than the equivalent Sybase ASE queries. In addition the queries were working on a 24-month data set rather than the original 3-month data set.
The existing data warehouse was left in situ and using Sybase ASE with Sybase IQ permitted the users to access both data sets from a single unified interface. The aggregate load, held in the existing Sybase ASE Data Warehouse, was also modified to build from the Sybase IQ store rather than the raw files and this reduced load complexity and allowed the IT team to be more responsive in delivering new aggregates.
In summary a CDR/IPDR archive was created on a second server with directly attached storage, roughly equivalent in size and performance to the original data warehouse platform, that was accessible via SQL, allowed users access from within the current environment, compressed the data by more the five times and was typically ten times faster to query.
About the author:
David Walker has been involved with business Intelligence and Data Warehousing for over 20 years, first as a user, then with a software house and finally with a hardware vendor before setting up his own consultancy firm Data Management & Warehousing (http://www.datamgmt.com) in 1995.