Strategic data warehouse

It is not uncommon to the BI system’s life-cycle to have its beginning in a continuously improved and automated spreadsheet. Had it not been for the system’s manageability, the migration to a data warehousing solution would not have occurred. Though natural, the bottom-up driven approach of an analytical system development is far from ideal. Uncontrolled data redundancy and many independently set up reporting areas may turn out to become a limitation for multi-organizational enterprises.

The lack of a unified view of data severely reduces the possibility of enterprise level data reconciliation and comparison.

The worst case scenario which takes into account the aforementioned issues is that the cost of retrieving, evaluating and formatting data will be higher than the value of the information itself. Since a consistent reporting platform is not in place gathering data is an expensive process and does not guarantee that reliable results will be obtained which one can base any managerial decisions on.

Furthermore, the improvement in flexibility and enhancement of the analytical system is very limited as well. Consequences of considering a corporate BI strategy are:

• Uncontrolled data flows and data redundancy,
• Co-existence of many independent Data Marts,
• Increased costs,
• Unharmonized data,
• Limited flexibility for future enhancement of BI solution,
• Low data reliability.

The corporate road map to a BI solution should start with defining an appropriate corporate strategy that determines the rules and forms the guidelines of the analytical system’s architecture. Creation of this strategy should be a top-down initiative whose aims are the harmonization and unification of the local data view.

Choice of the BI platform along with the selected ERP system has the greatest influence on future IT decisions in the enterprise and it is often driven by the very ERP system already in place. As both BI and ERP system have consistent architecture and are easily integrated with each other many companies, as a rule of thumb, deploy solutions offered by the same vendor. It is not a rule, however, and it sometimes happens, though rarely, that the Business Intelligence system is the only shared solution within the corporation. Irrespective of the selected technology and the solution vendor, after making a decision to introduce a corporate standard for the BI system, a series of factors to determine a future system architecture should be considered. Since every company is different, there is no single, ready-to-use recipe. The construction of an optimal solution is a complex process, which should, among other things, include:

• Objectives and the scope of the data warehouse,
• Source systems,
• Reporting requirements,
• System management, maintenance and development,
• Reporting tools.

Who the warehouse is for

The main objective of a corporate data warehouse system is to create a data repository that will provide a consistent view of information scattered through different organizations, locations and systems.

Each and every company can have slightly different needs as far as usage of its BI system is concerned. The defined scope will impact the decisions made concerning the comprehensive system architecture and the relationships between the systems. Will the data warehouse support only the analytical processes at a corporate level, or will it be used by local branches at an operational level as well? If we plan a broad use of the tools at many organizational levels, it is worth considering and insuring early enough on that there are the possibilities to develop typical local reporting requirements. These may be met at a tool access level or through the development of a local data warehouse. If one of the objectives is to feed the data to other company reporting or transactional systems, the considerations should include the automation of such a process.

Source systems

The complexity of a corporate data integration process depends on the number and the heterogeneity of transactional systems which are the source of data for the data warehouse. In the case that local departments use only one transactional system, implementing a shared reporting tool will reduce maintenance costs and provide a unified point of access to the shared data structures. On the other hand, when local-level activities differ a lot between entities, a better approach is to develop local data warehouses fed with local transactional systems data. The corporate data warehouse receives data from local sub-systems that have been pre-processed in accordance with corporate rules and standards and have a desired level of detail and quality.

In another data flow scenario – the data can first reach a corporate repository and then, once being integrated is transferred to other systems. An example of such architecture may be the use of aggregated data in a feedback-driven, provisioning of a transactional system. A corporate data warehouse can also be used as a single point of access for distributing data to local analytical systems. This realizes the “One Version of the Truth” concept, yet allows for the often demanded independence of local BI solutions.

Requirements for today and tomorrow

The satisfaction of user expectations is the most important factor for a positive evaluation of a corporate BI system. Non-functional system requirements cannot be disregarded in the design process. Availability, refresh intervals, security and authorization are just as important as the number, purpose and definition of reports delivered through the system.

Other elements become even more important in specific situations e.g. in present-day systems spread over several time-zones around the globe. In this case, short time windows of business inactivity require more careful planning of the data upload processes, while simultaneously, ensuring continuous and uninterrupted data access.. Enclosing those constraints in the system architecture blueprint will help to meet SLA-levels in the future.

Planning for the future is easier said than done however, designing a flexible system for increased future requirements, possible changes and enhancements are equally as important as taking into account today’s needs.

One of the numerous examples of changes that many BI systems are facing some time after deployment is to meet operational level reporting expectations which does not necessarily go hand in hand with a corporate level reporting approach. Filling these kinds of “gaps” should be anticipated in each system architecture. How the BI system will be built and what it will offer once it is deployed is a culmination of many factors. We have to bear in mind an old rule of IT system design – as long as it does not meet the user’s needs it is useless. That is why it is crucial to involve stakeholders from different teams and organizational levels in the project. BI teams must not be left alone in the analysis of business processes and design changes to the report definitions.

In the case of a corporate project which engages many people, and even teams, from different organizational levels the responsibilities for the requirements concerning BI should be clearly delegated.

Management and maintenance

A corporate data warehouse is a living and continuously evolving solution as are the user’s awareness and market and business processes changes. Introducing new functionalities or changes into the current approach to the analysis should be possible without needing to introduce changes to the entire pre-existing system architecture or resulting in any downtime. This also applies to solution roll-ins in new company branches. Though effort in creating such an open architecture seems to be an unnecessary burden during system design and development, it will surely pay off in the future.

Another aspect related to the system maintenance is an appropriately balanced change management procedure. A modification entry path that is too complicated and arduous for potential users will lead to giving up on any attempt to improve the system. As a result, this will lead to overly large discrepancies between the requirements and the status quo, and thus to a low BI solution usage. Consent for hasty and uncontrolled changes to the system will have a similar effect.

The roles and responsibilities of the corporate and the local BI team who are developing the system should be separated. One of the most widely used practises puts the responsibility for the data model and shared reports in the hands of the central BI team and leaves the development of local reports to the local teams.

Data presentation layer

Different users have different needs as far as the creation, presentation, flexibility and interactivity of reports are concerned. The architecture of modern data warehouse systems, including SAP tools, allows for a separation of the data layer from the creation of reports and the presentation layer (OLAP tools). Therefore, the reporting tools may be made available independently to various groups of users both at the corporate level and locally. In this way, some expectations may be met at the application level, without the need to develop separate systems.

An example of such a solution would be the possibility to create in-house analyses by the employees of the foreign company branch. Instead of creating a system with a separate data repository for a branch, a local BI tool will be made available, which will allow for the creation and presentation of its own reports based on data from the corporate warehouse. Such an approach helps maintain the corporate reporting standards and realize the “one version of the truth” principle.

SAP tools for Business Intelligence

SAP BI and SAP BusinessObjects platforms support different models and configurations of corporate analytical systems. The LSA (Layer Scalable Architecture) concept proposed by SAP is a set of best practices in the modelling of a scalable, transparent and complete BI architecture. The idea of LSA consists of modelling the data and system functions according to dedicated layers that are the representation of services for particular system functions, i.e. data acquisition, consolidation, propagation, integration, reporting, etc.

This approach also tends from the most detailed and non-integrated data approach to the aggregated data which is consistent with the business definition approach. Additionally it allows for a logical vertical division of information according to organizational structures, regions, and time zones through all semantic layers. LSA may be a reference for creating a flexible and efficiently maintainable corporate Business Intelligence solution based on SAP BI data warehouse.

The possibility to choose from various tools for the creation, analysis and presentation of reports, (including a smooth integration with BusinessObjects tools), allows for the flexible management of user expectations and selection of the optimal architecture. The SAP BI platform offers a wide range of functions that support the needs of the corporate analytical system. However usage of these tools and the creation of new reporting needs must serve a primary goal which is the implementation of the corporate strategy.