Federated Business Intelligence at AstraZeneca

I’ve had a really busy year so far but I thought it was about time I should share some of the things I’ve been working on with you. My role as BI Architect at AstraZeneca means I get involved in some interesting projects and challenges and this year has certainly been no exception. We have a major programme running in the commercial space to rationalise toolsets and introduce a more centralised approach to BI.

Traditionally, our marketing companies across the globe have been autonomous and free to choose their own IS solutions and have been in control of their own IT budgets. That model has now changed somewhat and a centralised approach has been adopted. So, last year we set off to review the BI landscape out in the markets and we were astonished to discover a total of 21 BI tools from different vendors, in other words we had pretty much every BI tool out there! In order to realise significant savings from a licencing perspective a programme was given the go ahead to rationalise the landscape by adopting a standardised set of BI solutions; we called it “BI in a box”. The solutions would use the SAP BusinessObjects product set which has already been a standard within central IS for some years. Standard reporting packs for Finance and CRM reporting were rolled out based around WebIntelligence, Crystal Reports, Xcelsius and Dashboard manager. Power users are trained in Webintelligence for ad-hoc reporting and some more mature marketing companies are being given the capability to manage their own environments. We have established a BusinessObjects shared service in each of our three world class data centres and using a delegated administration model a marketing company can have their own development, test and production environments. In addition, Predictive Workbench is being rolled out to the larger marketing companies to enable analysts to mine their data and populate their own data marts for reporting purposes.

So, a lot is going on as you can imagine and the rollout will continue over the next couple of years to cover every corner of the globe. But what’s the bit about federation in the title of this blog all about? Well, we found that some of the larger marketing companies that I mentioned earlier already had quite a mature BI capability and whilst our standard solutions and tools gave them the core functionality to run certain aspects of their business there were some areas that were quite specific to an individual market. For example, marketing data pertinent to the local market is often purchased from external providers and legal requirements for individual countries may require additional data to be maintained and reported upon. A good example is Spain who already had a well established data warehouse based around Microsoft SQL Server and Analytics Services. They were keen to adopt the standard solutions and the SAP BusinessObjects toolset as the benefits were obvious but we quickly realised that we couldn’t replace everything that they currently had. We wanted to keep the central Enterprise Data Warehouse as generic as possibly so were reluctant to start loading local country specific data as this would bring a whole new element of data governance into the equation. We also wanted the marketing companies to retain a certain element of independence as we were already forcing a lot of change onto them. So, we needed some way to integrate the data on the fly. We considered a number of options like merging data at report level but that would mean having to repeat the data integration in every report and with added complexities like master data inconsistencies (a whole other story!) it just added to the problems. The obvious solution to these issues it seemed was SAP BusinessObjects Data Federator.

Data Federator enables you combine data from multiple sources on the fly. It handles a whole host of different database platforms using JDBC and other sources such as flat files, XML or data services. Data Federator works out the most appropriate execution path and order in which to select data from the source systems. In other words, it doesn’t just pull the result sets from each source and then try to join them – it works out which is the smaller of the two data sets, retrieves that first and then joins it to the second source to reduce the result set appropriately. The result is a “virtual” relational database against which standard ANSI SQL can be executed.

We started a proof of concept with the Spanish data and setup a sandbox environment in our European Data Centre. With the help of SAP consultancy services we built some projects and mappings that pulled together central finance data from the Oracle based Enterprise Data Warehouse with some local data from the Spanish SQL Server data warehouse. The Designer tool enabled us to write rules to provide consistency around the master data mismatches and through a series of ETL-like transformations we were able to produce a consistent set of integrated output data. We then built a Universe on top of the virtual database in the same way that you would build any other Universe against a relational database. WebIntelligence reports were then written to query the data and the response times were impressive considering what was going on behind the scenes. The PoC was considered a success and we are now planning to roll out a production system with Spain being the first marketing company to use the platform. There is demand from other marketing companies for similar requirements  and the US have expressed an interest in using Data Federator as a way of integrating internal CRM data with external web services. I’ll tell you more about that when we have proved the tools capabilities in that area.

One thing to bear in mind before you rush out to buy a Data Federator licence is that some of the functionality for linking data sources will be provided in Information Designer (the replacement for Universe Designer) In SAP BusinessObjects 4.0 which will be available early 2011.