Transcription

Modeling the Data Warehouse Layer with BIVersion 1.0July 19, 2006

Table of Contents:1 Dos and Don’ts for Modeling a Data Warehouse Layer . 32 Data Warehouse Layer (Enterprise Data Warehouse) in BI . 42.1 Motivation and Benefits . 42.2 Conceptual Layers of Data Warehousing with BI . 52.3 Modeling Examples. 63 Document-Type Data (Line Items) in DataStore Objects and InfoCubes . 94 BI Data Models for Line Item and Header Information . 104.1 BI Data Model Scenarios .104.2 Comparison of Different Scenarios .164.3 General Recommendations .165 Performance Aspects of the Data Warehouse Layer . 175.1 Performance When Activating Data and the BEx Reporting Indicator .175.2 Unique Records in DataStore Objects .175.3 Indexes .17Modeling the Data Warehouse Layer with SAP BW.docPage 214.06.2012

Data Warehouse LayerData warehousing has developed into an advanced and complex technology. For some time it was assumedthat it was sufficient to store data in a star schema optimized for reporting. However this does not adequatelymeet the needs for consistency and flexibility in the long run. Therefore data warehouses are now structuredusing a layer architecture. The different layers contain data at differing levels of granularity. We differentiatebetween the following layers: Persistent staging area Data warehouse Architected data marts Operational data storeFigure 1 Conceptual Layers of Data WarehousingThe data warehouse layer offers integrated, granular, historic, stable data that has not yet been modified fora concrete usage and can therefore be seen as neutral. It acts as the basis for building consistent reportingstructures and allows you to react to new requirements with flexibility.1 Dos and Don’ts for Modeling a Data Warehouse Layer It is generally not recommended that you create a stovepipe data model with:oA direct dataflow from an extractor with document-type data to a highly aggregated datamart InfoCubeoProprietary creation of commonly-used central InfoObjects. It is recommended that youreuse central InfoObjects like business partner, product or company code.If you have a heterogenous source system landscape that comprises data from different componentsand systems: It is generally recommended that you create an intermediate consolidation layer withinyour data model (for example, within the dataflow from data source to a data mart InfoCube). Theappropriate storage object for this layer is the DataStore object.See example: full-blown content model (see section 2.3 of this document) If you are extracting document-type data that is not preaggregated to BI: It is generally recommendedthat you build a data warehouse layer with DataStore objects where the data is stored in a slightlydenormalized form at the most appropriate level of granularity.See example: light-weighted content model (see section 2.3 of this document) DataStore objects for a data warehouse layer should be modeled with the same granularity as the datathat is delivered by the extractor:Modeling the Data Warehouse Layer with SAP BW.docPage 314.06.2012

oNo aggregation of business-relevant data to retain information on operational leveloSlight denormalization is recommended: for example, header and item information fromdocument-type data can be flattened into one extract structure (see section 4 of thisdocument)oExtractors for master and transactional mass data should be delta enabledHistorical completeness of data to an appropriate extent where required: for example, adding a timeelement to the data.One example from SAP standard BI Content is the DataStore Object 0FIAR O03 FI-AR: Line Item:oFinancials documents are updated in BI when document field entries of non-key fields arechanged: status and clearing data. The document status changes from open to cleared andthe clearing date is set simultaneously when the document status is changed.oThus without sending a separate change document, the changed information can beretained in the data warehouse.Thereby, for example, aging lists can be created withcalculated business processing KPIs as a way of retaining the history of data changes.2 Data Warehouse Layer (Enterprise Data Warehouse) in BI2.1 Motivation and BenefitsData warehousing provides data that is: Integrated as far as possible: master data is consolidated and master data is uniformly coded Consistent: central metadata models are shared to enable cross-application scenarios Historical: the history of the data is retained in dedicated data containers Complete: the data is not aggregated in dedicated data containers and is stored according to thegranularity of the OLTP dataOrganizations and businesses with multiple BI implementations and a heterogenous source systemlandscape face the challenge of avoiding islolated, inconsistent, stovepipe data warehouse solutions with: Redundant data flows Redundant extractions Redundant data stores Redundant data modelsIf this redundancy is not controlled, it is difficult to achieve integrated consistent reporting on the data andmetadata. Moreover, the whole administration of the data and metadata becomes more complex andexpensive.A company-wide Enterprise Data Warehouse (EDW) concept helps to address these challenges. Itcomprises aspects of: Data storage: a multi-layer concept for persistent data storage Data model: BI objects for each layer and their relationships System landscape: this is not discussed in this paperThe following sections concentrate on data modeling. They explain how you can implement a multi-layerconcept while focusing on the data warehouse layer as an element of this concept.Modeling the Data Warehouse Layer with SAP BW.docPage 414.06.2012

2.2 Conceptual Layers of Data Warehousing with BIThe main motivation for a layer concept is that each layer has its own optimized structure and services forthe administration of data within an enterprise data warehouse. Therefore each layer also requires its ownmetadata modeling limitations constraints (see Figure 1 Conceptual Layers of Data Warehousing).a) Architected Data Mart Layer Analysis and reporting layer Common master data definitions (consolidated InfoObjects) Aggregated data Data manipulation with business logic, for example, calculation of process time KPIs (for example,delivery time variance) Modeled using InfoCubes or DataStore objectsb) Data Warehouse Layer (DWH Layer) Corporate information repository of EDW Historical completeness - different levels of completeness are possible: from availability of latestversion with change date to change history of all versions No aggregation of reporting-relevant data; for example, document line-item granularity for documenttype data Normally no reporting targets – exception: operational reporting on line items Modeled using DataStore objects Common master data definitions (consolidated InfoObjects) to retain cross-system integration ofsystem-dependent master data Optional: separation intooPropagation tier: data source-dependent, primary foundation for applicationsoIntegration tier: integrates data from different processesThis separation into two tiers produces the full-blown content model (See example‘Global Spend Analysis’ in section 2.3.2 of this document).c) Operational Data Store Layer For operational list reporting Common master data definitions (consolidated infoObjects) Transaction-near data Optional: Near real-time access Modeled using DataStore objectsWhat are the benefits of a specific DWH layer? Customers expect a DWH layer embedded in their overallEDW strategy because it is used predominantly as: Information hub to distribute OLTP data from multiple source systems to BI targets andsubsequently to SAP or non-SAP applications Historical basis for archiving OLTP data from multiple source systems in BI (timeframe 5-7 years)ostorage of document version (actual version)oexceptional and case-dependent: storage of change history (for example, order changehistory)Modeling the Data Warehouse Layer with SAP BW.docPage 514.06.2012

Integration basis to integrate OLTP data from multiple source systems or components; in manycases more than one layer of DataStore objects is necessary.2.3 Modeling ExamplesThis section contains three modeling examples from BI Content of a DWH layer for transaction data.2.3.1 Bank AnalyzerThe data model comprises the data flow from the operational banking systems (for example, CML, AM) to ananalytical solution like the Bank Analyzer. The inbound and outbound data part of the DWH layer is modeledusing DataStore objects. This scenario emphasizes the integration and consolidation aspects of the DWHconcept.Data Flow of Bank Analyzer Solution: Overall PictureExtractionMapping, ConsolidationDistributionData Warehouse LayerExtractorAMExtractorUpdate - rdParty.DataStoreObjectsStandardCustomer SpecificBank AnalyzerStandardBusiness Content as ExampleImplementation SAP AG 2003SAP AG 2001 BW - The Open Business Intelligence Platform/ J. Haupt / 1Figure 2 Data Flow of Bank Analyzer Solution in BIIn this example, the most important aspects of the DWH layer are: Integration of data from different operational finance systems for distribution to other applications(integration basis function) Storage of consolidated data and consistent metadata in BI: retain cross-system integration ofsystem-dependent data (integration basis function) Data is distributed to subsequent non-BI data targets/analytical application, for example, FinancialDatabase (FDB) / Bank Analyzer (information hub function)Modeling the Data Warehouse Layer with SAP BW.docPage 614.06.2012

2.3.2 SRM Global Spend AnalysisThis scenario is used to analyze the expenditure of an affiliated group over all its companies and systems. Atypical system landscape can include more than one SAP back-end system and enterprise buyer systemsconnected to a BI.The data flow consists of invoice or purchase order data from the procurement systems (SRM or MM).Different DataStore objects contain data for each document type at line-item level. The detailed data isconsolidated in a subsequent DataStore object. An InfoProvider designed for analytical reporting containsdata from all enterprise buyer and purchasing systems that feed the prelimary DataStore objects. Thisscenario is an EDW example of a full-blown content model with DataStore objects.In this example, the most important aspects of the DWH layer are: Integration of data from different procurement systems and the appliance of business rules accrosstwo layers of DataStore objects (integration basis functionality) 1 layer: data is stored with document line-item granularity with no business rule manipulations. Thedelta method for the extractors is AIMD: after-image delta records with delete records; in the changelog table of the DataStore objects, you can trace the history of after-image records; during dataextraction, header and item data is combined into one OLTP structure. 2 layer: flat-list operational reporting accross the whole purchasing process data at document level.Calculations using business logic are applied to generate KPIs such as Delivery Time from PurchaseOrder to Confirmation.stndExample of Data Flow Modeling: Full-BlownDWH Layer ProcessIntegration:Consolidated masterdata, amounts and values- flat list reporting withcomponent-independentviewProduct, Vendor,Organisation,Location, KYFsKYFCalculations:PerfectPOs, PriceVariance, QuantityVarianceDataStore object : 0BBP DS1item levelPO ValuesLayerDocGuid, DocItemGuid, ContractGuid,ContractItemGuidProduct, alue,DeliveryTimeVariance Calculations: DeliveryTimeVariance, neg. Indicator for CreditMemo Val&Quan.,DataStore object: 0BBP CONitem levelConfirmationDataStore object : 0BBP INVitem levelLocation,vendor, product,net invoice value,invoicequantity, InvoiceDocGuid, DocItemGuid, ContractGuid,ContractItemGuidInvoiceGuid, InvoiceItemGuid,Data WarehouseDWH LayerPropagation:SeparateDataStore objectfor eachdocument type:purchase order,invoice,confirmationInfoCube:Global Spend0BBP C01Architected Data MartLayer: Aggregated view –no document levelDataStore object : 0BBP POitem levelLocation,Vendor,Purchase Order Product,NetOrderValue,OrderQuantity,POGuid, POItemGuid, Requested DeliveryDate, Different SW components possible - EBP or R/3 Purchasing SAP AG 2004Figure 3 Data Flow of Full-Blown Content ModelModeling the Data Warehouse Layer with SAP BW.docPage 714.06.2012

2.3.3 CRM Sales AnalysisThis scenario is an EDW example of a light-weighted content model with DataStore objects. That meansthat it is not necessary to integrate data from heterogenous source systems and complex processes are notrequired. Document header data and item data are extracted and stored with line-item granularity in