CONSOLIDATED DATA WAREHOUSE

CUSTOMER

British American Tobacco Trading Company, a member of British American Tobacco Group, the world leader in the tobacco industry.

CHALLENGE

The customer’s primary business objective was to build a distribution network, enable seamless communication among all participants of the network, conduct thorough market research, and organize market analysis and planning activities. In order to perform all these activities effectively, they needed a consolidated data warehouse solution for data storage, analysis, and reporting.

The biggest challenge for developing such system was the fact that the data came from four different systems that were not connected to one another:

Pervasive-based ERP system containing all data about customer’s business operations
Automatic m-commerce management system based on MS SQL Server 2008. It stored information about all activities of customer’s sales representatives.
Order processing and approving system based on Lotus Notes Database. The orders are prepared by sales representatives and approved by their respective managers.
Access database containing information about outlets working with the customer.

Another challenge was absence of reference data or unique classification codes, and thus homogenous data.

Besides, corporate security policy, internal systems business processes regulations, and complicated systems integration mechanisms led to necessity to develop a special mechanism for system monitoring and control ensuring that the users get accurate data.

SOLUTION

The project’s objective was developing a consolidated data warehouse for storage and analysis of data uploaded from the four systems described above. The solution also needed to generate ad-hoc reports.

As the system had to provide online data processing and analysis, we chose Microsoft SQL Server 2008 Analysis Services (SSAS) OLAP technologies as the basis for the solution. All input data was moved to OLAP cubes, and the multidimensional data warehouse was the only place where it was stored and processed.
This allowed the managers to analyze measures coming from different systems (a total of more than 150 measures) in different analytical slices (more than 40 dimensions and around 500 attributions).

One of the important features the customer wanted in the system was analysis of information about different outlets with regard to its change history. In order to implement it, we developed a special data upload suite that analyzes data changes and uploads data figures as well as their relevance period into the table.

In order to monitor the system’s performance, we created a special OLAP cube analyzing data relevance based on the time it was uploaded and processed by a certain system.

The solution is fully web-based, and it provides scalable around-the-clock and around-the-globe access to information while also having a high security level.

RESULT

Our team developed a single data warehouse solution for the customer, which collects data from four independent systems, processes it and allows generating ad-hoc reports.
Due to automation of data collection and verification, the quality of output data advanced, and expenses plummeted.
Managers receive instant access to various information about the outlets they work with, which allows them to make grounded decisions faster, for the benefit of their business and their clients.