INFOCUBES - ULTIMATE SAP BIW CONCEPTS EXPLAINATION

INFOCUBES - ULTIMATE SAP BIW CONCEPTS EXPLAINATION - by "Shrikanth Muthyala"

INFOCUBES - ULTIMATE EXPLAINATION

INFOCUBE
1.  INFOCUBE-INTRODUCTION:
2.  INFOCUBE - STRUCTURE
3.  INFOCUBE TYPES
3.1  Basic Cube: 2 Types
3.1.1  Standard InfoCube
3.1.2  Transactional InfoCube
3.2  Remote Cubes: 3 Types
3.2.1  SAP Remote Cube
3.2.2  General Remote Cube
3.2.3  Remote Cube With Services
4.  INFOCUBE TABLES- F,E,P,T,U,N
5.  INFOCUBE-TOOLS
5.1  PARTITIONING
5.2  ADVANTAGES OF PARTITIONING:
5.3  CLASSIFICATION OR TYPES OF PARTITIONING
5.3.1  PHYSICAL PARTITIONING/TABLE/LOW LEVEL
5.3.2  LOGICAL PARTITIONING/HIGH LEVEL PARTITIONING
5.3.3  EXAMPLES ON PARTITIONING USING 0CALMONTH & 0FISCYEAR
5.3.3.1  ERRORS ON PARTITIONING
5.3.4  REPARTITIONING
5.3.4.1  REPARTITIONING TYPES
5.3.5  Repartitioning - Limitations- errors
5.3.6  EXAMPLES ON PARTITIONING USING 0CALMONTH & 0FISCYEAR
5.4  COMPRESSION OR COLLAPSE
5.5  INDEX/INDICES
5.6  RECONSTRUCTION
5.6.1  ERRORS ON RECONSTRUCTION
5.6.2  Key Points to remember while going for reconstruction
5.6.3  Why Errors Occur in Reconstruction?
5.7  STEPS FOR RECONSTRUCTION
5.8  ROLLUP
5.9  LINE ITEM DIMENSION/DEGENERATE DIMENSION
5.9.1  LINE ITEM DIMENSION ADVANTAGES
5.9.2  LINE ITEM DIMENSION DISADVANTAGES
5.10  HIGH CARDINALITY
6.  INFOCUBE DESIGN ALTERNATIVES
6.1  ALTERNATIVE I :  TIME DEPENDENT NAVIGATIONAL ATTRIBUTES
6.2  ALTERNATIVE II :  DIMENSION CHARACTERISTICS
6.3  ALTERNATIVE III : TIME DEPENDENT ENTIRE HIERARCHIES
6.4  OTHER ALTERNATIVES:
6.4.1  COMPOUND ATTRIBUTE
6.4.2  LINE ITEM DIMENSION
7.  FEW QUESTIONS ON INFOCUBES

1. INFOCUBE-INTRODUCTION:
The central objects upon which the reports and analyses in BW are based are called InfoCubes & we can seen as InfoProviders. an InfoCube is a multidimensional data structure and a set of relational tables that contain InfoObjects.

2. INFOCUBE- STRUCTUREStructure of InfoCube is considered as ESS-Extended Star Schema/Snow Flake Schema, that contains
• 1 Fact Table
• n Dimension Tables
• n Surrogate ID (SID) tables
• n Fact Tables
• n Master Data Tables
Fact Table with KeyFigures
n Dimension Tables with characteristics
n Surrogate ID (SID) tables link Master data tables & Hierarchy Tables
n Master Data Tables are time dependent and can be shared by multiple InfoCubes. Master data table contains Attributes that are used for presenting and navigating reports in SAP(BW) system.

3. INFOCUBE TYPES:

• Basic Cubes reside on same Data Base
• Remote Cubes Reside on remote system
• SAP remote cube resides on other R/3 System uses SAPI
• General remote Cube resides on non SAP System uses BAPI
• Remote Cube wit Services reside on non SAP system

3.1. BASIC CUBE: 2 TYPES: These are physically available in the same BW system in which they are specified or their meta data exist.
3.1.1. STANDARD INFOCUBE: FREQUENTLY USEDStandard InfoCube are common & are optimized for Read Access, have update rules, that enable transformation of Source Data & loads can be scheduled

3.1.2. TRANSACTIONAL INFOCUBE:The transactional InfoCubes are not frequently used and used only by certain applications such as SEM & APO. Data are written directly into such cubes bypassing UpdateRules

3.2. REMOTE CUBES: 3 TYPES:Remote cubes reside on a remote system. Remote Cubes gather metadata from other BW systems, that are considered as Virtual Cubes. These are the remote cube types:

3.2.1. SAP REMOTE CUBE:the cube resides on non SAP R/3 system & communication is via the service API(SAPI)

3.2.2. GENERAL REMOTE CUBE:Cube resides on non SAP R/3 Source System & communication is via BAPI.

3.2.3. REMOTE CUBE WITH SERVICES:Cube resides on any remote system i.e. SAP or non SAP & is available via user defined function module.

4. INFOCUBE TABLES- F,E,P,T,U,NTransaction Code: LISTSCHEMA
LISTSCHEMA>enter name of the InfoSource OSD_C03 & Execute. Upon execution the primary (Fact) table is displayed as an unexpanded node. Expand the node and see the screen.
These are the tables we can see under expanded node:

5. INFOCUBE-UTILITIES
5.1. PARTITIONINGPartitioning is the method of dividing a table into multiple, smaller, independent or related segments(either column wise or row wise) based on the fields available which would enable a quick reference for the intended values of fields in  the table.
For Partitioning a data set, at least among 2 partitioning criteria 0CALMONTH & 0FISCPER must be there.

5.2. ADVANTAGES OF PARTITIONING:• Partitioning allows you to perform parallel data reads of multiple partitions speeding up the query execution process.
• By partitioning an InfoCube, the reporting performance is enhanced because it is easier to search in smaller tables, so maintenance becomes much easier.
• Old data can be quickly removed by dropping a partition.
you can setup partitioning in InfoCube maintenance extras>partitioning.

5.3. CLASSIFICATION OR TYPES OF PARTITIONING
5.3.1. PHYSICAL PARTITIONING/TABLE/LOW LEVELPhysical Partitioning also called table/low level partitioning is restricted to Time Characteristics and is done at Data Base Level, only if the underlying database allows it.
Ex: Oracle, Informix, IBM, DB2/390
Here is a common way of partitioning is to create ranges. InfoCube can be partitioned on a time slice like Time Characteristics as below.
• FISCALYEAR( 0FISCYEAR)
• FISCAL YEAR VARIANT( 0FISCVARNT)
• FISCALYEAR/PERIOD(0FISCPERIOD)
• POSTING PERIOD(OFISCPER3)
By this physical partitioning old data can be quickly removed by dropping a partition.
note: No partitioning in B.I 7.0, except DB2 (as it supports)

5.3.2. LOGICAL PARTITIONING/HIGH LEVEL PARTITIONINGLogical partitioning is done at MultiCubes(several InfoCubes joined into a MultiCube) or MultiProvider level i.e. DataTarget level . in this case related data are separated & joined into a MultiCube.
Here Time Characteristics only is not a restriction, also you can make position on Plan & Actual data, Regions, Business Area etc.
Advantages:
• As per the concept, MultiCube uses parallel sub-queries, achieving query performance  ultimately.
• Logical partitioning do not consume any additional data base space.
• When a sub-query hits a constituent InfoProvider, a reduced set of data is loaded into smaller  InfoCube from large InfoCube target, even in absence of MultiProvider.

5.3.3. EXAMPLES ON PARTITIONING USING 0CALMONTH & 0FISCYEARTHERE ARE TWO PARTITIONING CRITERIA:
calendar month (0CALMONTH)
fiscal year/period (0FISCPER)
At an instance we can partition a dataset using only one type among the above two criteria:
In order to make partition, at least one of the two InfoObjects must be contained in the InfoCube.
If you want to partition an InfoCube using the fiscal year/period (0FISCPER) characteristic, you have to set the fiscal year variant characteristic to constant.
After  activating InfoCube, fact table is created on the database with one of the number of partitions corresponding to the value range.
You can set the valuerange yourself.
Partitioning InfoCubes using Characteristic 0CALMONTH:
Choose the partitioning criterion 0CALMONTH and give the value range as
From=01.1998
to=12.2003
So how many partitions are created after partitioning?
6 years * 12 months + 2 = 74 partitions are created
2 partitions for values that lay outside of the range, meaning < 01.1998 or >12.2003.
You can also determine how many partitions are created as a maximum on the database for the fact table of the InfoCube.
You choose 30 as the maximum number of partitions.
Resulting from the value range:
6 years *12 calendar months + 2 marginal partitions (up to 01.1998, from 12.2003)= 74 single values.
The system groups three months at a time together in a partition
4 Quarters Partitions = 1 Year
So, 6 years * 4 partitions/year + 2 marginal partitions = 26 partitions are created on the database.
The performance gain is only gained for the partitioned InfoCube if the time dimension of the InfoCube is consistent.
This means that all values of the 0CAL* characteristics of a data record in the time dimension must fit each other with a partitioning via 0CALMONTH.
Note: You can only change the value range when the InfoCube does not contain any data.

PARTITIONING INFOCUBES USING THE CHARACTERISTIC 0FISCPERMandatory thing here is, Set the value for the 0FISCVARNT characteristic to constant.

5.3.4. STEPS FOR PARTITIONING AN INFOCUBE USING 0CALDAY & 0FISCPER:Administrator Workbench
   >InfoSet maintenance
     >double click the InfoCube
        >Edit InfoCube
           >Characteristics screen
              >Time Characteristics tab
                 >Extras
                    >IC Specific Properties of InfoObject
                       >Structure-Specific Properties dialog box
                         >Specify constant for the characteristic 0FISCVARNT
                           >Continue
                              >In the dialog box enter the required details

5.3.5. Partition Errors:
F fact tables of partitioned InfoCube have partitions that are empty, or the empty partitions do not have a corresponding entry in the related package dimension.
Solution1: the request SAP_PARTITIONS_INFO_GET_DB4 helps you to analyze these problems. The empty partitions of the f fact table are reported . In addition, the system issues an information manage. If there is no corresponding entry for a partition in the InfoPackage dim table(orphaned).
When you compressed the affected InfoCube, a database error occurred in drop partition, after the actual compression. However, this error was not reported to the application. The logs in  the area of compression do not display any error manages. The error is not reported in the developer trace (TRANSACTION SM50), the system log ( TRANSACTION SM21) and the job overview (TRANSACTION SM37) either.
The application thinks that the data in the InfoCube is correct, the data of the affected requests or partitions is not displayed in the reporting because they do not have a corresponding entry in the package dimension.
Solution2: use the report SA P_DROP_FPARTITIONS</Z1) to remove the orphaned or empty partitions from the affected f fact tables, as described in note 1306747, to ensure that the database limit of 255 partitions per database table is not reached unnecessarily.

5.3.6. REPARTITIONING:Repartitioning is a method of partitioning, used for a cube which is already partitioned that has loaded data. Actual & Plan data versions come here. As we know, the InfoCube has actual data which is already loaded as per plan data after  partition. If we do repartition, the data in the cube will be not available/little data due to data archiving over a period of time.
You can access repartitioning in the Data Warehousing Work Bench using Administrator>Context Menu of your InfoCube.
5.3.6.1. REPARTITIONING - 3 TYPES: A) Complete repartitioning,
B) Adding partitions to an e fact table that is already partitioned and
C) Merging empty or almost empty partitions of an e fact table that is already partitioned

5.3.7. REPARTITIONING - LIMITATIONS- ERRORS:SQL 2005 partitioning limit issue: error in SM21 every minute as we reached the limit for number of partitions per SQL 2005(i.e. 1000)

5.4. COMPRESSION OR COLLAPSE:Compression reduces the number of records by combining records with the same key that has been loaded in separate requests.
Compression is critical, as the compressed data can no longer deleted from the InfoCube using its request ID's. You must be certain that the data loaded into the InfoCube  is correct.
The user defined partition is only affecting the compressed E-Fact Table.
By default  F-Fact Table contains data.
By default SAP allocates a Request ID for each posting made.
By using Request ID, we can delete/select the data.
As we know that  E-Fact Table is compressed & F-Fact Table is uncompressed.
When compressed, data from F-Fact Table transferred to E-Fact Table and all the request ID's are lost / deleted / set to null.
After compression, comparably the space used by E-Fact Table is lesser than F-Fact Table.
F-Fact Table is compressed  uses BITMAP Indexes
E-Fact Table is uncompressed uses B-TREE Indexes

5.5. INDEX/INDICES
PRIMARY INDEXThe primary Index is created automatically when the table is created in the database.
SECONDARY INDEX(Both Bitmap & B-Tree are secondary indices)
Bitmap indexes are created by default on each dimension column of a fact table
& B-Tree indices on ABAP tables.

5.6. RECONSTRUCTION:Reconstruction is the process by which you load data into the same cube/ODS or different cube/ODS from PSA. The main purpose is that after deleting the requests by Compression/Collapse by any one, so if we want the requests that are deleted (old/new) we don't need to go to source system or flat files for collecting requests, we get them from PSA.
Reconstruction of a cube is a more common requirement and is required when:1) A change to the structure of a cube:  deletion of characteristics/key figures, new characteristics/key figures that can be derived from existing chars/key figures
2) Change to update rules
3) Missing master data and request has been manually turned green - once master data has been maintained and loaded the request(s) should be reconstructed.

5.6.1. KEY POINTS TO REMEMBER WHILE GOING FOR RECONSTRUCTION:• Reconstruction must occur during posting free periods.
• Users must be locked.
• Terminate all scheduled jobs that affect application.
• Deactivate the start of RSBWV3nn update report.

5.6.2. WHY ERRORS OCCUR IN RECONSTRUCTION?Errors occur only due to document postings made during reconstruction run, which displays incorrect values in BW, because the logic of before and After images are no longer match.

5.6.3. STEPS FOR RECONSTRUCTIONTransaction Codes:
LBWE  : LO DATA EXTRACTION: CUSTOMIZING COCKPIT
LBWG  : DELETE CONTENTS OF SETUP TABLES
LBWQ  : DELTA QUEUED
SM13   : UPDATE REQUESTS/RECORDS
SMQ1  : CLEAR EXTRACTOR QUEUES
RSA7  : BW DELTA QUEUE MONITOR
SE38/SA38  : DELETE UPDATE LOG

STEPS:1. Mandatory - User locks :
2. Mandatory - (Reconstruction tables  for application 11 must be empty)
 Enter  transaction - LBWG & application = 11 for SD sales documents.
3. Depending on the selected update method, check below queues:
 SM13 – serialized or un-serialized V3 update
 LBWQ – Delta queued
 Start updating the data from the Customizing Cockpit (transaction LBWE) or
 start the corresponding application-specific update report RMBWV3nn (nn = application  number) directly  in transaction SE38/SA38 .
4. Enter RSA7 & clear delta queues of  PSA, if it contains data in queue
5. Load delta data from R/3 to BW
6. Start the reconstruction for the desired application.
 If you are carrying out a complete reconstruction, delete the contents of the  corresponding data targets in  your BW (cubes and ODS objects).
7. Use Init request (delta initialization with data transfer) or a full upload to load the data  from the reconstruction into BW.
8. Run the RMBWV3nn update report again.

5.6.4. ERRORS ON RECONSTRUCTION:Below you can see various errors on reconstruction. I had read SAP Help Website and SCN and formulated a simple thesis to make the audience, easy in understanding the concepts
ERROR 1: When I completed reconstruction, Repeated documents are coming. Why?
Solution: The reconstruction programs write data additively into the set-up tables.
If a document is entered twice from the reconstruction, it also appears twice in the set-up table. Therefore, the reconstruction tables may contain the same data from your current reconstruction and from previous reconstruction runs (for example, tests). If this data is loaded into BW, you will usually see multiple values in the queries (exception: Key figures in an ODS object whose update is at “overwrite”).

ERROR 2: Incorrect data in BW, for individual documents for a period of reconstruction run. Why?
Solution: Documents were posted during the reconstruction.
Documents created during the reconstruction run then exist in the reconstruction tables as well as in the update queues. This results in the creation of duplicate data in BW.
Example: Document 4711, quantity 15
Data in the PSA:
ROCANCEL DOCUMENT QUANTITY
‘ ‘ 4711 15 delta, new record
‘ ‘ 4711 15 reconstruction
Query result:
4711 30
Documents that are changed during the reconstruction run display incorrect values in BW because the logic of the before and after images no longer match.
Example: Document 4712, quantity 10, is changed to 12.
Data in the PSA:
ROCANCEL DOCUMENT QUANTITY
X 4712 10- delta, before image
‘ ‘ 4712 12 delta, image anus
‘ ‘ 4712 12 reconstruction
Query result:
4712 14

ERROR 3: After you perform the reconstruction and restart the update, you find duplicate documents in BW.
Solution: The reconstruction ignores the data in the update queues. A newly-created document is in the update queue awaiting transmission into the delta queue. However, the reconstruction also processes this document because its data is already in the document tables. Therefore, you can use the delta initialization or full upload to load the same document from the reconstruction and with the first delta after the reconstruction into BW.
After you perform the reconstruction and restart the update, you find duplicate documents in BW.
Solution: The same as point 2; there, the document is in the update queue, here, it is in the delta queue. The reconstruction also ignores data in the delta queues. An updated document is in the delta queue awaiting transmission into BW. However, the reconstruction processes this document because its data is already contained in the document tables. Therefore, you can use the delta initialization or full upload to load the same document from the reconstruction and with the first delta after the reconstruction into BW.

ERROR 4:Document data from time of the delta initialization request is missing from BW.
Solution: The RMBWV3nn update report was not deactivated. As a result, data from the update queue LBWQ or SM13 can be read while the data of the initialization request is being uploaded. However, since no delta queue (yet) exists in RSA7, there is no target for this data and it is lost.

5.7. ROLLUPRollup creates aggregates in an InfoCube whenever new data is loaded.

5.8. LINE ITEM DIMENSION/DEGENERATE DIMENSIONlf the size of a dimension of a cube is more than 20% of the normal fact table, then we define that dimension as a Line Item Dimension.
Ex: Sales Document Number in one dimension is Sales Cube.
Sales Cube has sales document number and usually the dimension size and the fact table size will be the same. But when you add the overhead of lookups for DIMID/SID's the performance will be very slow.
By flagging is as a Line Item Dimension, the system puts SID in the Fact Table instead of DMID for sales document Number.
This avoids one lookup into dimension table. Thus dimension table is not created in this case. The advantage is that you not only save space because the dimension table is not created but a join is made between the two tables Fact & SID table(diagram 3) instead of 3 Tables Fact, Dimension & SID tables (diagram 2)

Below image is for illustration purpose only( ESS Extended Star Schema)

Dimension Table, DIMID=Primary Key
Fact Table, DIMID-Foreign Key
Dimension Table Links Fact Table And A Group Of Similar Characteristics
Each Dimension Table Has One DIMID & 248 Characteristics In Each Row

5.8.1. LINE ITEM DIMENSION ADVANTAGES:
Saves space by not creating Dimension Table

5.8.2. LINE ITEM DIMENSION DISADVANTAGES:• Once a Dimension is flagged as Line Item, You cannot ass additional Characteristics.
• Only one characteristic is allowed per Line Item Dimension & for (F4) help, the Master Data is displayed, which takes more time.

5.9. HIGH CARDINALITY:If the Dimension exceeds 10% of the size of the fact table, then you make this as High Cardinality Dimension. High Cardinality Dimension is one that has several potential occurrences. when you flag a dimension as High Cardinality, the database is adjusted accordingly.
BTREE index is used rather than BITMAP index, Because in general, if the cardinality is expected to exceed one fifth that of a fact table, it is advisable to check this flag
NOTE: SAP converts from BITMAP index to BTREE index if we select dimension as High Cardinality.

6. INFOCUBE DESIGN ALTERNATIVES:

Refer: SAP R/3 BW Step-by-Step Guide by Biao Fu  & Henry Fu

InfoCube Design techniques of helps us to reveal automatic changes in the InfoCube. These alternatives may be office/region/sales representative.
6.1. ALTERNATIVE I   :  TIME DEPENDENT NAVIGATIONAL ATTRIBUTES
6.2. ALTERNATIVE II  :  DIMENSION CHARACTERISTICS METHOD
6.3. ALTERNATIVE III  : TIME DEPENDENT ENTIRE HIERARCHIES
6.4. OTHER ALTERNATIVE:
6.4.1. COMPOUND ATTRIBUTE
6.4.2. LINE ITEM DIMENSION

7. FEW QUESTIONS ON INFOCUBES
What are InfoCubes?
What is the structure of InfoCube?
What are InfoCube types?
Are the InfoCubes DataTargets? How?
What are virtual Cubes(Remote Cubes)?
How many Cubes you had designed?
What are the advantages of InfoCube?
Which cube do SAP implements?
What are InfoCube tables?
What are Sap Defined Dimensions?
How many tables are formed when you activate the InfoCube structure?
What are the tools or utilities of an InfoCube?
What is meant by table partitioning of an InfoCube?
What is meant by Compression of an InfoCube
Do you go for partitioning or Compression?
Advantages and Disadvantages of an InfoCube partitioning?
What happens to E-Fact Table and F Fact Table if you make partition on an InfoCube?
Why do u go for partitioning?
What is Repartitioning?
What are the types of Repartitioning?
What is Compression? Why you go for Compression?
What is Reconstruction? Why you go for Reconstruction?
What are the mandatory steps to do effective error free reconstruction, while going Reconstruction?
What are the errors occur during Reconstruction?
What is Rollup of an InfoCube?
How can you measure the InfoCube size?
What is Line Item Dimension?
What is Degenerated Dimension?
What is High Cardinality?
How can you analyze that the cube as a LineItem Dimension or HighCardinality?
What are the InfoCube design alternatives?
Can you explain the alternative time dependent navigational attributes in InfoCube design?
Can you explain the alternative dimension characteristics in InfoCube design?
Can you explain the alternative time dependent entire hierarchies in InfoCube design?
What are the other techniques of InfoCube design alternatives
What is Compound Attribute?
What is LineItem Dimension? Will it affect designing an InfoCube?
What are the maximum number of partitions you can create on an InfoCube?
What is LISTSCHEMA?
I want to see the tables of an InfoCube. How? Is there any Transaction Code?
When the InfoCube tables created ?
Are the tables created after activation or Saving the InfoCube structure ?
Did you implemented RemoteCube? Explain me the scenario?
Can you consider InfoCube as Star Schema or Extended Star Schema?
Is Repartitioning available in B.W 3.5 or B.I 7.0? Why?
On what basis you assign Characteristics to Dimensions?