1 / 22

InfoCubes and Aggregates

InfoCubes and Aggregates. UNWBW1 – Business Information Warehouse NetWeaver Support Consultant Training. Content. Introduction Reporting Business content Data loading InfoCube Design Aggregates BW-BPS Business Planning & Simulation Monitoring & Technical Risks.

laurence-lemaitre
Download Presentation

InfoCubes and Aggregates

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. InfoCubesand Aggregates UNWBW1 – Business Information Warehouse NetWeaver Support Consultant Training

  2. Content • Introduction • Reporting • Business content • Data loading • InfoCube Design • Aggregates • BW-BPS Business Planning & Simulation • Monitoring & Technical Risks

  3. Star Schema Product Dimension Customer Dimension Sales Dimension Product Dimension Quantities Revenues CostsRev./Group Time Customer Dimension Time Dimension Competition Dimension Star Schema(Logical)

  4. Dimensions • Dimension tables are groupings of related characteristics. • A dimension table contains a generated primary key and characteristics. • The keys of the dimension tables are foreign keys in the fact table. Customer dimension C Customer # Region … 13970522 West ... Product dimension Time dimension P Product # Product group … 2101004 Displays ... T Period Fiscal year … 10 1999 ...

  5. Example: Sales Infocube Dimensions Customer Product Sales Salesperson Rep group Sales territory Sales region Sales district Sales planning group Distribution key Customer number Customer name Cust Category Cust Subcategory Division Industry Revenue Class Transportation zone Currency VAT # Legal Status Regional market Cust Statistics group Incoterms Billing schedule Price group Delivering plan ABC Classification Account assignment group Address State Country Region Material number Material text Material type Category Subcategory Market key MRP Type Material group 1 Planner Forecast model Valuation class Standard cost Weight Volume Storage conditions Creation Date Competition Nielsen indicator SEC Code Primary competitor Secondary Competitor Time Date Week Month Fiscal Year

  6. Fact Table • A record of the fact table is uniquely defined by the keys of the dimension tables • A relatively small number of columns (key figures) and a large number of rows is typical for fact tables • A fact table is maintained during transaction data load Fact table P C T Quantity Revenue Discount Sales overhead 250 500,000 $ 50,000 $ 280,000 $ 50 100,000 $ 7,500 $ 60,000 $ … … … ...

  7. Example: Sales Facts Facts - Sales Quantity sold List price Discounts Invoice price Fixed mfg. cost Variable cost Moving average price Standard cost Contribution margin Expected ship date Actual ship date

  8. Example: Sales Star Schema Sales Competition Nielsen indicator SEC Code Primary competitor Secondary Competitor Customer Customer number Customer name Cust. Category Cust. Subcategory Division Industry Revenue Class Transportation zone Currency VAT # Legal Status Regional market Cust. Statistics group IncoTerms Billing schedule Price group Delivering plan ABC Classification Account assignment group Address State Country Region Facts Customer Material Competition Sales Time Material Sales Material number Material text Material type Category Subcategory Market key MRP Type Material group 1 Planner Forecast model Valuation class Standard cost Weight Volume Storage conditions Creation Date Salesperson Rep group Sales territory Sales region Sales district Sales planning group Distribution key Qty sold List price Discounts Invoice price Fixed mfg cost Variable cost Moving average price Standard cost Contribution margin Expected ship date Actual ship date Time Date Week Month Fiscal Year

  9. Extending the Star Schema In a basic Star Schema we are limited: • Only characteristics of the dimension tables can be used to access facts. • No structured drill downs can be created. • Support for many languages is difficult. In BW, the Extended Star Schema adds access to: • Master data tables and their associated fields (attributes). • Text tables with extensive multilingual descriptions. • External hierarchy tables for structured access to the data.

  10. SAP BW: Extended Star Schema Customer Text Table Material Text Table Material Attributes Table CUSTOMER_ID MATERIAL_ID MATERIAL_ID Customer Name Material Group Material Name MaterialSID-Table Customer SID-Table InfoCube MATERIAL_ID CUSTOMER_ID CustomerDimension Table MaterialDimension Table SID_MATERIAL SID_CUSTOMER DIM_ID_CUSTOMER DIM_ID_MATERIAL external Material Hierarchy SID_CUSTOMER SID_MATERIAL Customer Attributes Table Fact Table CUSTOMER_ID DIM_ID_PACKAGEDIM_ID_TIMEDIM_ID_UNITDIM_ID_MATERIALDIM_ID_CUSTOMER CityRegion AmountSID-Table AmountSales AMOUNT_ID Datapackage Dimension Table UnitDimension Table SID_AMOUNT DIM_ID_PACKAGE DIM_ID_UNIT SID_REQUEST SID_AMOUNTSID_CURRENCY Time Dimension Table CurrencySID-Table DIM_ID_TIME CURRENCY_ID SID_MONTHSID_YEAR SID_CURRENCY RequestSID-Table REQUEST_ID Calendar YearSID-Table Calendar MonthSID-Table SID_REQUEST YEAR_ID MONTH_ID SID_YEAR SID_MONTH

  11. Material Material Material Attribute Table Dimension Material Number Dimension Material Number Material Type Material_Dimension_ID Material Text Table Material Number Material Number Material Number Language Code Language Code Material Dimension Table Material Name Material Hierarchy Table Vertriebsorganisation Region 1 Region 2 Region 3 Material Group Bezirk 1 Bezirk 2 Bezirk 3 Bezirk 4 Bezirk 5 Gebiet 1 Gebiet 2 Gebiet 3 Gebiet 3a Gebiet 4 Gebiet 5 Gebiet 6 Gebiet 7 Gebiet 8 Dimensions • up to 16 dimensions • 3 dimensions exist with each InfoCube (whether they are used and thus visible or not) • Time dimension • Unit dimension • Packet dimension • The remaining 13 dimensions are for individual schema design • Each dimension table may be up to 248 characteristics.

  12. Summary • The center of a multidimensional schema in BW are the fact tables. • The fact tables are surrounded by dimensions. • Dimension Table • In BW the attributes of the dimension tables are called characteristics (e.g. material). • Master Data Tables: • Attribute Tables • Dependent attributes of a characteristic can be stored in an Attribute Table for the characteristic. • Text Tables • Textual descriptions of a characteristic are stored in a separate text table. • External Hierarchy Tables • Hierarchies of characteristics or attributes may be stored in separate hierarchy tables.

  13. Compressing the InfoCube • Records added to InfoCube fact tables haveseveral “keys” which uniquely identify the record. • Request ID is just one of several fields in a record that helps identify the data. • But, Request ID can be removed, and each record can still be uniquely identified. • Compression finds records which are identical except for Request ID, then aggregates these to one single record. • If a compression is not performed, the “Group by” condition of any query’s SQL statement will remove duplicates. This results in decreased query performance.

  14. Compressing the InfoCube Request IDs Lost !!! E-Fact table F-Fact table COMPRESSION

  15. Content • Introduction • Reporting • Business content • Data loading • InfoCube Design • Aggregates • BW-BPS Business Planning & Simulation • Monitoring & Technical Risks

  16. Aggregates ... ... are like InfoCubes, ... are always based on InfoCubes ... summarize ("aggregate") data of the originating InfoCube, ... contain redundant information, but ... accelerate the access to that information, ... are performance-enhancing features.

  17. Aggregates - Example Data for queries like ‘sales for all countries’, ‘sales in Germany’, or ‘overall sales’ can be read out of the aggregate (country *). Fact Table: Sales Data Aggregate Tables: Sales Data Country * Country Customer Sales Buggy Soft Inc. Ocean Networks Funny Duds Inc. Ocean Networks Thor Industries Funny Duds Inc. Buggy Soft Inc. 10 15 5 10 10 20 25 USA Germany USA Austria Austria Germany USA Country Sales USA Germany Austria 40 35 20

  18. Aggregates - Example using filters Data for queries like ‘sales for all customers in Germany'can be read out of the aggregate (country =Germany; customer=*) Fact Table: Sales Data Aggregate Tables: Sales Data Country Germany Customer * Country Sales Customer Buggy Soft Inc. Ocean Networks Funny Duds Inc. Ocean Networks Thor Industries Funny Duds Inc. Buggy Soft Inc. 10 15 5 10 10 20 25 USA Germany USA Austria Austria Germany USA Sales Customer Country Germany Germany Ocean Networks Funny Duds Inc. 15 20

  19. Aggregates - Example using master data Attribute Table: Customer Industry Customer Buggy Soft Inc. Funny Duds Inc. Ocean Networks Thor Industries Technology Consumer Products Technology Chemical Aggregate Tables: Sales Data Fact Table: Sales Data Industry * Country Customer Sales Buggy Soft Inc. Ocean Networks Funny Duds Inc. Ocean Networks Thor Industries Funny Duds Inc. Buggy Soft Inc. 10 15 5 10 10 20 25 USA Germany USA Austria Austria Germany USA Sales Industry Technology Consumer Products Chemical 60 25 10

  20. Country Sales America Europe 40 55 Aggregates - Example using hierarchies All Hierarchy for Country Europe America USA Germany Austria Fact Table: Sales Data Aggregate Tables: Sales Data Country Hierarchy, Level 2 Country Customer Sales Buggy Soft Inc. Ocean Networks Funny Duds Inc. Ocean Networks Thor Industries Funny Duds Inc. Buggy Soft Inc. 10 15 5 10 10 20 25 USA Germany USA Austria Austria Germany USA

  21. Aggregates - Maintenance Show aggregate hierarchy Transport Activate & Fill Switch on/off BDS unsaved changes

  22. Aggregate Maintenance • After new data is loaded existing aggregates have to be adjusted in order to make the new data available for reporting: • Aggregate Rollup: • The newly uploaded transactional data is added to the aggregates • Changerun (Master Data Activation): • The newly uploaded master data is applied to the aggregates and activated. • During the change run, all aggregates containing navigational attributes and/or hierarchies are realigned

More Related