Data Warehouse

About Client

For such a fast-paced, entrepreneurial business some are surprised to learn the company’s history spans 150 years of innovation for consumers across the world. With a German and British heritage, client’s commitment to producing top quality, trusted medicines, medical devices, hygiene and other home products has shaped its history of strong financial performance coupled with social responsibility.

Their products include: Strepsils, Dettol, Bang, Harpic, Lysol, Durex, Clearasil, Scholl, Mortein, Air wick, Veet, etc. to name a few.

Business problem

Client’s employees and partner’s access Birst system ( to get the Business Intelligence (BI) reports. These reports accessed via a custom login page build by Birst.

Birst is the enterprise BI platform that delivers the self-service, and agility front-line business workers demand, and the scale, security, and control to meet rigorous corporate data standards.

Birst custom login page authenticates to RB Active directory using RADIUM.

The challenges

Our client wanted deeper insight into their growing business, including which products were selling to which customers, at what time etc. They also required information on what products weren’t selling so they could refine their target market. They needed expert advice on determining the priorities and dependencies for implementing these solutions.

Problem Statement
  • Latham Birst Automation – Daily and Weekly Reporting (Sales and Orders)
  • Eliminate single point of failure risk by delivering a stable reporting and consolidation tool that multiple users can manage
  • Data extraction from multiple sources which are independent of each in terms of technology and OS
  • Loading of previous years’ data along with current live data from 3 different sources
  • With more than 50 business rules to satisfy before actually moved to production for reporting
  • Validating other rules via dimension tables (third party verifications-outside birst)
  • Minimal impact on the existing architecture and current reporting services
  • Email notifications on the overall progress of the reporting scheduler packages
  • Data load and monitor frequency – Daily and Weekly
  • monitor the packages on AWS servers – Overall success and failure of the reporting tool
The Solution

Over an eight-week period, our Analytics & Information Management practice created a detailed roadmap outlining a set of activities to improve reporting and analytics using the Microsoft MSBI tools. The approach and process included interviewing key stakeholders and establishing business requirements and then grouping those requirements into initiatives and projects as bellow.

  • After detailed analysis, the solution divided into 3 steps
  • Develop the queries to extract data from 2 sources and validate with stake holder
  • Verify and load the data from 3rd source to common database
  • Apply business logic (applied around 50 business rules from 3 different sources)
  • Make common data for each source and store – reject bad rows with rules defined
  • Bifurcate data based on daily and weekly groups, also validate on other rules which are defined by third party through dimension tables
  • Developed Queries which will merge the data on daily and weekly basis with high performance
  • Integration of the automation part with existing system for detailed and more accurate data for the decision making on sales and orders part’s
Technology stack
Web Technology
  • SVN
  • MVC 4.0
  • Bootstrap
  • Angular Js
Deployment Server
  • RB data center
  • Active Directory
  • Microsoft .Net Framework 4.0
  • Microsoft SQL Server 2008 R2 and above
  • SQL Server Management Studio & IIS – Internet Information Services
Build Server
  • Microsoft .Net Framework 4.0
  • Microsoft Visual Studio 2013 or above
The Benefits
  • Financial reports were ready when the new fiscal year began with daily/weekly sales reporting options
  • Accounting and finance users rely on one source of truth and manage the tool without IT assistance
  • Business users received expert guidance on what Birst Automation projects to do, and in what order.
  • They also received key insight on where to invest time and energy to maximize business value and minimize technical risk.
  • By identifying quick wins and high value, low effort opportunities we enabled our client to quickly reap the benefits of this technology and focus on value realization.
  • Users now trust the data and access it independently
  • This successfully launched reporting and analytics, allowing the company to continue to thrive, and today the BI suite is successfully implemented as per the plan.