Data Warehouse Modeling | Business Information Warehouse for SAP (Prima Techs SAP Book Series)

Team-Fly

As with any data warehouse project, data modeling is key to the success of a high-performance data warehouse implementation. Data modeling defines how information objects behave in a data warehouse: logically and physically. Prior to SAP BW 2.0, most data modeling was focused around a multidimensional paradigm to support slice-and-dice types of analysis. With the implementation of Operational Data Store (ODS) in SAP BW 2.0, you now need to think of SAP BW modeling from an enterprise data modeling perspective where you model ODS and multidimensional data objects to support operational, slice-and-dice, and historical data analysis needs, all under one information framework: SAP BW.

The first question that comes to mind is why you need to think differently when it comes to modeling a multidimensional or an enterprise data warehouse. The reason is that multidimensional database structures are designed to analyze numeric data, such as measurements, against a given set of variables or characteristics. On the other hand, the enterprise data warehouse is a logical collection of all information objects that include multidimensional, ODS, historical, and unstructured data objects. Therefore, the scope of enterprise data warehouse modeling is much broader than multidimensional modeling alone. The following section describes multidimensional data modeling from an SAP BW perspective.

Figure 12-1 shows several aspects of data warehouse modeling. Traditionally, data warehouse modeling is limited to the following models:

click to expand
Figure 12-1: Enterprise Data Warehouse Modeling.

Conceptual
Logical Data
Physical Data
Deployment

The Conceptual model outlines the flow of information across all business processes. From a data warehousing perspective, it describes how business decisions are made at all levels in an organization to drive business processes at an abstract level.

The Logical data model defines grouping and relationships of logically similar Information Objects in terms of entity relation models. Then from such entity relationships, you derive several Logical data models to support enterprise business intelligence needs. For example, for customer sales analysis, the logical entity relationship model describes how customer orders are related to sales organizations and material at a very detailed level, whereas the multidimensional data model may have only a summary of order information, such as customers and the products ordered.

The Physical data model describes data structure definitions and design architecture to meet business intelligence requirements. The Physical data model heavily depends on the data warehouse construction technologies best suited for specific data analysis needs. For example, you might decide to build analytical applications against pure multidimensional or relational technology and relational database engines for ODS or the enterprise data warehouse. The physical design must take into account the necessity to exploit database specific facilities for high scalability and performance for analytical applications.

The Deployment model is often considered part of the Physical data model. However, in today's volatile business environment, the Deployment model needs special attention. The Deployment model assures that the Physical data model conforms to the business Conceptual model, making your enterprise data warehouse flexible to accommodate new business analytical needs.

The Deployment model is derived primarily from the Physical and Conceptual models. For example, say a company has a central enterprise data warehouse where it receives data from all transaction systems and builds all reporting objects. However, as the company grows and launches business operations across several geographic regions and across global boundaries, it is not possible to build a very rigid central data warehouse for the entire corporation to support 24-hour operations.

Here the Deployment model describes how an enterprise data warehouse can be deployed as one central instance, partitioned or distributed without redesigning the Physical data model. For example, say you have one central data warehouse. It has an ODS, the data warehouse, and the OLAP data model. You decide to separate ODS from the rest of the data warehouse environment and deploy it on a stand-alone server (hardware, software, and services). If the Physical model and associated services for ODS are not defined in an architected fashion, you cannot separate ODS from the rest of the data warehouse. For this reason, when designing the Physical data model and associated services, keep in mind that the business is going to change even before a data warehouse is ready for deployment. Make sure that the physical design accommodates the business architecture, today and tomorrow.

The data modeling team consists of business and data analysts and enterprise data modelers to gather business requirements for analytical applications. For SAP BW projects, an SAP BW business content specialist should be a member of the conceptual and logical modeling phases. Because SAP BW does most of the database level implementation work, you do not need a database level programmer to code schemas, store procedures, triggers, or define indexes. For physical data modeling, however, you should keep one DBA on the team, especially for database sizing issues. Involve BASIS operations team members in defining the SAP BW path-to-production life cycle and in defining the deployment model when SAP BW goes in production. Make SAP BW support part of the SAP R/3 support structure. This chapter is designed for SAP BW developers who define InfoCubes and build analytical applications.

Data warehouse modeling, especially ODS modeling, is discussed in Chapter 17, "The Operational Data Store in SAP BW 2.0," which describes the ODS role in a data warehouse and how to implement ODS in SAP BW 2.0A and 2.0B.

Data Modeling Considerations Specific to SAP BW

Data warehouse modeling for SAP BW is somewhat easier than traditional data warehousing for companies that deploy SAP enterprise mission critical applications and have a major portion of business applications that are SAP-centric. The data modeling task for data warehouse modelers becomes somewhat easier for the following two reasons.

When you implement SAP R/3 applications, the business workflow, enterprise reference model, data standards, business rules, and business models have already been well understood, and such business operational models are available.
SAP BW business content comes with rich industry-specific data models. This saves a lot of initial investigative data modeling work for a data warehouse.

Though the above are good news for data modelers, you will run into cultural issues associated with the SAP R/3 OLTP and traditional data warehouse modelers, as discussed in Chapter 2.

The first three modeling areas-Conceptual, Logical, and Physical-mentioned previously apply to all data warehouse projects. However, the deployment model is important when modeling the data warehouse landscape for SAP BW. When modeling SAP BW, you need to be very careful in analyzing end-user requirements in modeling queries to define the physical and deployment models to exploit SAP R/3 BASIS multitiered architecture for optimum performance. Because SAP BW star schema is implemented in a relational DBMS, the key point to keep in mind is to model and implement long, thin fact tables and relatively small, short, and wide dimension tables.

Note

Due to SAP BW's tight integration with the SAP R/3 OLTP instance, be very careful to model information navigation schemes directly from SAP BW to SAP R/3 OLTP. SAP BW 2.0B provides this navigation capability, but this does not mean that you have to implement such direct to OLTP navigation in every query. Use this capability only when an analytical activity requires an analyst to investigate complete transaction-level details that still exist in the SAP R/3 OLTP. This is another cultural issue. I have seen SAP R/3-centric SAP BW developers who are more interested in jumping to the SAP R/3 OLTP instance rather than focusing on modeling InfoCubes and ODS objects in SAP BW. My advice is to limit real-time reporting against SAP.

The next topic describes multidimensional data modeling that is needed to build custom InfoCubes in SAP BW 1.2B and SAP BW 2.0A.

Team-Fly