Home » Posts tagged "Technical Architecture"

Tag Archives: Technical Architecture

Building An Analytical Platform

Posted on by Posted in Articles Leave a comment

Reprint of an article published by SAP about a recent piece of work

I have recently been asked to build an analytical platform for a project – but what is an analytical platform? The client (a retailer) described it as a database where they could store data, a front end where they could do statistical work ranging from simple means, standard deviations, etc. through to more complex predictive analytics which could be used to (for example) analyse past performance of a customer to assess how likely it is that the customer will exhibit a future behaviour, or using models to classify customers into groups and ultimately to bring these two together into an area known as decision models. The customer had also come up with an innovative way to resource the statistical skills needed by offering work placements to Masters students studying statistics at the local university and getting them to work with the customer insight team to describe and develop the advanced models; all they needed was a platform to work with.

From a systems architecture and development perspective we could describe the requirements in three relatively simple statements

  1. Build a database with a (very) simple data model that could be easily loaded, was capable of supporting high performance queries and did not consume a massive amount of disk space. It would also ideally be capable of being placed in the cloud.
  2. Create a web-based interface that would allow users to securely log on, write statistical programs that could use the database as a source of data and output reports and graphics and well as populating other tables (e.g. target lists) as a result of statistical models.
  3. Provide a way in which to automate the running of the statistical data models, once developed, such that they can be run without engaging the statistical development resources.

Of course time was of the essence and costs had to be as low as possible too – but we’ve come to expect that with every project!

Step 1: The database

Our chosen solution for the database was a Sybase IQ database, a technology our client was already familiar with. Sybase IQ is a ‘column store’ database. This means that instead of storing all the data in its rows as many other databases do the data is organised on disk by the columns. For example if a column contains a field for country it will have the text of each country (for example) ‘United Kingdom’ stored many times. In a column store database the text is only stored once and given a unique ID. This is repeated for each column and therefore the ‘row’ of data consists of a list of IDs linked to the data held for each column.

This approach is very efficient for reporting and analytical databases. Firstly by replacing text strings with an identifier significantly less space is used. In our example ‘United Kingdom’ would occupy 14 bytes, whist the ID might only occupy 1 byte – consequently reducing the storage for that one value in that one column by a ratio of 14:1 – and this is compression effect is repeated for all the data. Furthermore because there is less data on the disk the time taken to read the data from disk and process it for queries is significantly reduced which consequently massively speeds up the queries too. Finally each column is already indexed which again helps the overall query speed.

Another incidental but equally useful consequence of using a column store database such as Sybase IQ is that there is no advantage in creating a star schema as a data model – instead holding all the data in one large ‘wide’ table is at least as efficient. This is because by storing each column with a key it means that the underlying storage of data is a star schema. Creating a star schema in a column store database rather than a large single table would mean incurring unnecessary additional join and processing overhead.

As a result of choosing Sybase IQ as a column store database we are able to have a data model that consists of a number of simple single table data sets (one table for each different type of data to be analysed) that is both quick and easy to load and to query.

It should be noted that this type of database solution is less efficient for OLTP (Online Transaction Processing) type applications because of the cost of doing small inserts and updates. However this is not relevant for this particular use case.

The solution can only be deployed on a Linux platform. We use Linux for three reasons; firstly RStudio Server Edition is not yet available for Windows, secondly the availability of pre-compiled packages for all elements of the solution on Linux which reduces the install effort and finally hosted Linux environments are normally cheaper than Windows environments due to the cost of the operating system licence.  We chose CentOS because it is a RedHat derivative that is free.

One additional advantage of this solution for some organisations is the ability to deploy it in the cloud. Since the solution only requires files to be remotely delivered and all querying is done via a web interface it is possible to use any co-location or cloud based hosting provider. Co-Location or cloud deployment offers a low start-up cost, reduced systems management overhead and easy access for both data delivery and data access. The system simply requires SSH access for management, FTP, SFTP or SCP for file delivery and the RStudio Web Service port open. RStudio Server uses the server login accounts for security but can also be tied to existing LDAP infrastructure.

Step 2: Statistical Tools and Web Interface

There are a number of statistical tools in the market, however most are very expensive, prohibitively so in this case and in addition the associated skills are hard to come by and expensive. However since 1993 an open source programming language called R  for statistical computing and graphics has been under development. It is now widely used among statisticians for developing statistical software and data analysis and used by many universities and is predicted to be the most widely used statistical package by 2015. The R Project provides a command line and graphical interface as well as a large open source library of useful routines and is available as packaged software for most platforms including Linux.

In addition there is a second open source project called RStudio which provides a single integrated development environment for R and can be deployed on a local machine or as a web based service using the servers security model. In this case we have implemented the server edition in order to make the entire environment web based.

Therefore in two simple steps (download and install R, followed by download and install RStudio) we can install a full web-based statistical environment. Note that some configuring and pre-requisite packages may be required depending on your environment however these are well documented on the source websites and in general automatically download if using tools such as ‘yum’

The next step was to get access to the data held in our Sybase IQ. This proved to also be very easy. There is a Sybase white paper that describes the process that can be simply stated as:

  • Install the R JDBC package
  • Setup a JDBC connection
  • Establish your connection
  • Query the table

We now have an R object that contains data sourced from Sybase IQ that we can work with! And what is amazing is that I have built the platform from scratch in less than half a day’s work.

At this point data has to be loaded and the statisticians can get to work – obviously this is more time consuming that the build and over the days and weeks the analysts created their models and produced the results.

For this exercise we used our in-house ETL tool to create a repeatable data extraction and load process, however it would have been possible to use any of a wide range of tools that are available for this process.

Step 3:  Automatically running the statistical models

Eventually a number of models for analysing the data had been created and we were ready to move into a production environment. We automated the load of the data into the agreed single table structure and wanted to also run the data models.

Sybase IQ has the ability to create User Defined Functions (UDF). These C++ programs talk to a process known as Rserve that in turn executes the R program and returns the results back to Sybase IQ. This allows R functions to be embedded directly into Sybase IQ SQL commands. Whilst requiring a little more programming experience to set up it does mean that all processing can be done within Sybase IQ.

Conversely it is possible to run R from the command line and call the program that in turn uses the RJDBC connection to read and write data to the database.

Having a choice of methods is very helpful as it means that it can be integrated with the ETL environment in the most appropriate way. If the ETL tool requires SQL only then the UDF route is the most attractive, however if the ETL tool supports host callouts (as ours does) then running R programmes from a command line callout is quicker than developing the UDF.

Building An Analytical Platform - Architecture

Building An Analytical Platform – Architecture

Conclusions

Business Intelligence requirements are changing and business users are moving more and more from historical reporting into predictive analytics in an attempt to get both a better and deeper understanding of their data.

Traditionally building an analytical platform has required an expensive infrastructure and a considerable amount of time to setup and deploy.

By combining the high performance, low footprint of Sybase IQ with the open source R & RStudio statistical packages it is possible to quickly and easily deploy an analytical platform in the cloud for which there are readily available skills.

This infrastructure can be used both for rapid prototyping on analytical models and in running completed models on new data sets to deliver greater insight into the data

Building a Data Warehouse of Call Data Records (CDR): Solution Considerations

Posted on by Posted in Articles Leave a comment

Source: Database Trends and Applications (http://www.dbta.com/)

Reprint of a magazine article from May 2012

With many organisations having established data warehouse solutions there comes a point where the solution needs to be extended in an unusual direction. This challenge was given to us by one of our clients a European Mobile Virtual Network Operator (MVNO) who 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:

  1. 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.
  2. To enable data retention law complianceEU 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.
  3. Enhanced CDR analysis in the existing data warehouseThe 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 two 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.

Download Building a Data Warehouse of Call Data Records (CDR): Solution Considerations now

Using the right data model in a data mart

Posted on by Posted in Presentations Leave a comment
An internal training presentation from March 2012 that looks at how the choice of technology affects the choice of data modelling strategy
It start with a review of the current state of the art:
  • The concept of a Data Mart as the data access interface layer  for Business Intelligence has been around for over 25 years
  • Kimball style Dimensional Modelling and Star Schemas have become the de facto data modelling technique for data marts
  • These have been and continue to be hugely successful with relational databases and reporting tools – but are they the right tool for todays technologies ?

And moves on to look at what the options are now

Download Using the right data model in a data mart now

Struggling with Data Management ?

Posted on by Posted in Articles Leave a comment

Re-print of a magazine article from August 2010

Those responsible for data management in your organisation are probably struggling. If you can find an individual or group responsible for everything then they have a lot on their plate.  Even if they have managed to write policies and procedures they probably don’t have the time or the space to implement and enforce them. So whilst many organisations recognise information as a key asset they are also often unwilling or unable to put the management of data into practice.

Data Management International (DAMA) defines ten Data Management topics in its Book of Knowledge which are: Data Governance; Data Architecture, Analysis & Design; Database Management; Data Security Management; Data Quality Management; Reference & Master Data Management; Data Warehousing & Business Intelligence; Document, Record & Content Management; Metadata Management and Contact Data Management. Ten areas is a lot of ground to cover however, so companies will often understandably start by introducing a number of dedicated initiatives to address specific parts of the problem. Despite this being a positive step, it will often fail as it doesn’t take a holistic view in improving the overall management of data.

One such holistic initiative that can work is the creation of a ‘Literal Staging Area’ or LSA platform. This platform is a literal copy of the business systems created by 1:1 mappings and it is refreshed on a daily basis either by whole dumps or by changing data capture processes. A LSA differs from the concept of an Operational Data Store (or ODS) only in the fact that no compromise is made in the 1:1 nature of the copy that is such an important factor in its maintainability. However, it can also create a further problem in that companies struggle to appreciate how, by adding yet another platform, will help with data management?

The LSA concept started from the Data Warehousing and Business Intelligence perspective with the move from Extract, Transform and Load (ETL) to an Extract, Load and Transform (ELT) strategy where data is extracted from the source system, loaded into the target system and then manipulated for reporting. A well-architected system will isolate each individual source system into its own schema and, by default, create a series of LSAs. Creating this environment immediately reduces the data extraction on operational systems as all downstream systems can query the LSA instead. A further benefit to this approach is the ability to then bring data in from multiple heterogeneous sources that can be used with simple 1:1 mappings. This can also have a further notable effect on the cost of ETL tools where connectors are charged as an additional item. The complex transformations that will come with the population of the data warehouse itself now have a single high-performance homogeneous source from which to get their data.

Once an LSA has been created we can use it as a staging area for the data warehouse. The most obvious secondary use here would be to allow some operational reporting to be done on this system rather than purely at source. If data that is a day old is sufficient for some reporting, and if the data warehouse has finished its downstream processing, then utilising this spare capacity is an obvious choice.

Another use of this data set is for analysis and (re-)design of systems. Often business analysts will require a number of tools along with access to a number of systems. They will also be restricted from using the production systems as a result of performance. Access to a full and complete data set on a homogeneous platform will dramatically reduce analysis time whilst vastly improving the accuracy of results.

Perhaps the least obvious, but largest, return on investment can come from Data Quality Management. This subject is often broken down into two phases, analysis/assessment and cleansing. Whilst cleansing should take place back at source the analysis can be done using the LSA. In fact, it is possible to go much further than a basic assessment or analysis and move the business to adopting continuous monitoring of data quality from which a company can carry out a (very large) number of checks each day in order to track the results over time and identify trends rather than one-off fixes. The scale and benefit from this should not be under-estimated. One current project has added between fifteen and twenty checks to each table in each source system and with an average of around two hundred tables per major source and five major sources this amounts to 15,000 data quality checks daily and consequential trends. All this can easily be managed by a well-designed exception handling process that prioritised trends and reported them to the data quality management team.

All of this seems like it requires a large and complex system but this is not the case. Sizing the system in terms of disk space is an easy calculation as it is the sum of the data space used by the source systems, whilst the mappings (by definition and as described above) are all 1:1.  We can also define some other requirements for the type of platform to be successful. Firstly it must be optimised for a very large number of high-performance queries that will allow this workload to be carried out. The solution must also be simple to configure and administer, as the objective is not to add any additional overhead to the systems administration. Finally it must be cost-effective, affordable and scaleable.

Curt Monash, an analyst in the business intelligence arena, claims that since October 2009 the benchmark figure for systems that can meet this requirement is now around US$20,000 per Terabyte of user data – and in real terms this price point is dropping rather than rising.  Monash goes on to suggest that the beauty of systems, such as the Netezza TwinFin – which led the way into this space – is that the number of CPUs in the Massively Parallel Processing (MPP) architecture scales in direct proportion the user disk space.

Taking as an example the system described above where there is massive data quality monitoring requirements across five major sources. The user data from all the source systems amounted to around 3Tb, whilst the data warehouse required around 2Tb and staging areas accounted for another 1Tb of user data space. So, a high performance, simple-to-manage platform for data warehousing with literal staging areas and data quality monitoring & operational reporting can be purchased for around US$120,000.

This type of solution is dramatically changing the challenges for those responsible for data management. Instead of searching for capacity and time to deal with the problems it instead becomes a case of prioritising the activities to make best use of the information available and finding enough business resources to respond to the challenges and issues uncovered by this process.

Download Struggling with Data Management? now

Ready for Change?

Posted on by Posted in Presentations Leave a comment

A presentation given to the 3rd Citia BTC Conference in Kiev.

This presentation looks at the problems of creating a long-term sustainable data warehousing environment in the context of the change that will affect them:

Data warehouses are constantly in a state of flux

  • The cost of build is small by comparison with the operational cost needed to support and change the system. This requires full-time long-term resourcing
There are seven types of change affecting the data warehouse :
  • User Driven Change (Enhancements & Requirements)
  • External Systems Change
  • Internal Systems Change
  • Risk Management
  • Data Stewardship
  • Alert & Trend Monitoring
  • Issue Management

Download Ready For Change? now

Conspectus: Data Warehousing Appliances: Fad or Future

Posted on by Posted in Articles Leave a comment

The original article for Conspectus magazine.

Conspectus is the UK’s leading independent report which authoritatively addresses the key IT application areas. It is acknowledged as one of the most trusted journals in the IT industry and is used by many organisations to help them in their selection of IT software and technology.

An updated version of the article was also published on BI on Rails in November 2009

Download Data Warehousing Appliances: Fad or Future now

From Volume to Value

Posted on by Posted in Presentations Leave a comment

A presentation given to the Oracle Business Intelligence forum for Telcos that discussed how businesses should move from collecting volume of data to using the value of the data. It sets the context about what issues businesses are facing and then suggests five actions that can be used to help address the issues. These are:

  • Exploitation/Quick Service Teams
  • Data Profiling & Data Cleansing
  • Process Integration
  • Business Information Portals
  • RSS – Really Simple Syndication

Download From Volume to Value now

Overview Architecture for Enterprise Data Warehouses

Posted on by Posted in White Papers Leave a comment

This is the first of a series of papers published by Data Management & Warehousing to look at the implementation of Enterprise Data Warehouse solutions in large organisations using a design pattern approach. A design pattern provides a generic approach, rather than a specific solution. It describes the steps that architecture, design and build teams will have to go through in order to implement a data warehouse successfully within their business.

This particular document looks at what an organisation will need in order to build and operate an enterprise data warehouse in terms of the following:

  • The framework architecture
    What components are needed to build a data warehouse, and how do they fit together?
  • The toolsets
    What types of products and skills will be used to develop a system?
  • The documentation
    How do you capture requirements, perform analysis and track changes in scope of a typical data warehouse project?

This document is, however, an overview and therefore subsequent white papers deal with specific issues in detail.

Download Overview Architecture for Enterprise Data Warehouses now.

Information Delivery: The Change In Data Management at Network Rail

Posted on by Posted in Presentations Leave a comment

In September 2004 we presented at the Oracle OpenWorld conference on the successful initial stages on rolling out an enterprise wide information and reporting portal built on the Oracle tool set.

Rail privatisation and RailTrack was failing and Network Rail was created to take over the management of the different aspects of the railways. This created a need to rapidly deploy information and improve the its accessibility. This paper outlines some of our initial successes

Download Information Delivery: The Change In Data Management At Network Rail now

Template Solutions for Data Warehouses and Data Marts

Posted on by Posted in Presentations Leave a comment

A presentation given in 1998 to the Data Warehousing & Business Intelligence Conference on the value of using template solutions for data warehouses and data marts.

It describes the types of reporting system and discusses the templates then available for them

Download Template Solutions for Data Warehouses and Data Marts Now