All about Dimensions....

Dimension design: A different perspective

by Manohar Delampady 

Version 3

Objective: The objective of this post is to simplify the understanding on dimension designs of an infocube and to decide upon the dimensions based on the repetition of the data held in the dimension tables.

Pre-requisites: An infocube is already created and active, and filled will data, which will be used for analysis of dimension tables.

Dimension to Fact Ratio Computation: This ratio is a percentage figure of the number of records that exists in the dimension table to the number of records in fact table or what percentage of fact table size is a dimension table. Mathematically putting it down, the equation would be as below:

          Ratio = No of rows in Dimension table X 100 / No of rows in Fact Table

Dimension Table Design Concept: We have been reading and hearing over and over again that the characteristics should be added into a dimension if there exists a 1:1 or 1:M relation and they should be in separate dimension if there exists a M:M relation. What is this 1:1 or 1: M? This is the relation which the characteristics share among each other.

For instance if one Plant can have only one Storage Location and one storage location can belong to only one plant at any given point of time, then the relation shared among them is 1:1.

If 1 Functional Location can have many equipment but one equipment can belong to only one functional location then the relation shared between the functional location and Equipment is 1:M.

If 1 sales order can have many materials and one material can exist in different sales orders then there absolutely is no dependence among these two and the relation between these two is many to many or M: M.

Challenges in understanding the relationship: Often we SAP BI consultants depend on the Functional consultants to help us out with the relationship shared between these characteristics / fields. Due to time constraint we generally cannot dedicate time to educate the functional consultants on the purpose of this exercise, and it takes a lot of time to understand this relationship thoroughly.

Scenario: An infocube ZPFANALYSIS had few dimensions which were way larger than the preferred 20% ratio. This had to be redesigned such that the performance was under 20% ratio.

This ratio could be either manually derived by checking the number of entries in the desired dimension table (/BIC/D<infocube name><dimension number>) to the fact table (/BIC/F<Infocube Name> or /BIC/E<Infocube name>) or a program SAP_INFOCUBE_DESIGNS can be executed in SE38 which reports this ratio for all the dimensions, for all the infocubes in the system.

SAP_INFOCUBE_DESIGNS:

We can find from the report that the total number of rows in the fact table is 643850. Dimension 2 (/BIC/DZPFANLSYS2) has around 640430 rows, which is 99% (99.49%)of the fact table rows and Dimension 4(/BIC/DZPFANLSYS4) has around 196250 rows, which is 30%  (30.48%)of the fact table rows.

Infocube ZPFANLSYS:

Approach:

Step 1: Analysis of the dimension table /BIC/DZPFANALSYS2 to plan on reducing the number of records.

/BIC/DZPFANLSYS2

Fact table:

Dimension table holds 1 record more than the fact table.

View the data in the table /BIC/DZPFANLSYS2 (Table related to Dimension 2) in SE12 and sort all the fields. This sorting will help us spot the rows which have repeated values for many columns, which will eventually lead to understanding the relationship between the characteristics (columns in dimension table).

Identifying the relationships:

Once the sorting is done we need to look out for the number of values that repeat across the columns. All the records which repeat could have been displayed in a single row with one dimension id assigned if all the columns had same data. The repetition is a result of one or more columns which contribute a unique value to each row. Such columns if removed from the table then the number of rows in the table will come down.

In the below screenshot I’ve highlighted the rows in green that were repeating themselves with new dimension IDs, as only 2 columns SID_ZABNUM and SID_0NPLDA have new values for every row. These two columns having new values for every row have resulted in rest of the columns repeating themselves and in turn increasing the data size in the dimension table. Hence it can be easily said that these two columns do not belong in this dimension tables, so the related characteristics (ZABNUM and 0NPLDA) need to be removed out of this dimension.
Few rows could be found which repeat themselves for most of the columns, but have a new value once in a while for some columns, as highlighted in yellow in the below screenshot. This indicates that these columns share a 1:M relation with the rest of the columns with repeated rows and these could be left in the same dimension.

Conclusion: The columns marked in green belong to this dimension tables and the columns marked in red needs to be in other dimension tables.

Step 2: Create a copy infocube C_ZPFAN and create new dimensions to accommodate ZABNUM and 0NPLDA.

ZABNUM was added to dimension C_ZPFAN8 and 0NPLDA was added to C_ZPFAN7. These were marked as line item dimensions as they have only one characteristic under them.

Analysed the issue with dimension 4 in the similar way and changed other dimensions to help the situation.

Post changes, loaded the data into the copy infocube C_ZPFAN and found the number of records in the dimension table /BIC/DC_ZPFAN2 to be 40286.

Ratio: 40286 / 657400 * 100 = 6.12 %

SAP_INFOCUBE_DESIGNS:

Dimension2 of the copy infocube: /BIC/DC_ZPFAN2

Even now there a few repeated rows and columns, but the ratio is within 20%. We can create up to 13 dimensions, but it is always better to keep a dimension or two free for future enhancements.

Hope this was helpful.

Recovering deleted web templates in development from quality – reverse transport

By Manohar 

Objective:

It is human to err, but some errors unfortunately involve high price tags. This post is to explore the options of recovering a deleted web template in SAP BW development system. I have not tried this for other objects so I’m unaware of the possibilities that lie ahead. It is always a best practice to avoid any types of deletion of objects or data without proper confirmations.

Requirement: Recovering a web template in development, which got deleted by mistake.

Pre-requisite: This web template’s latest version has been transported to quality and other target systems previously.

Procedure: Let us first create a scenario by deleting an existing web template:

Stock Overview report as shown below has been deleted from the development system BAD.

After choosing YES the template ceases to exist in the development system which is reconfirmed by the below checks:

Running the URL:

Landscape and direction of transport movement:

Step 1: Login to Quality - BAQ

Step 2: Identify the transport request containing the latest version of the required web template.

In transaction SE03 choose “Search for objects in Requests/Tasks” under “Objects in Requests” folder.

Enter the object type as TMPL in case of web templates and the name of the web template that was deleted and execute.

The result would display all the requests that contained the web template; it is wiser to choose the latest one as that would be the working latest version of the web template. Make a note of the transport request.

Step 3: Create a Transport Request in Quality of request type “Transport of copies” and target as DEVELOPMENT.

In transaction SE10 create a transport request of type “Transport of copies” checked.

Under create request choose the radio button “Transport of copies”.

Make sure to choose the TARGET as DEVELOPMENT system, use possible entries help here. If you choose only workbench request then the possible entries help would not display the Development system in the list.

Step 4: Insert the web template into the request from the transport request identified in step 2.

Choose to “Include Objects” into the selected request.

Select the radio button “Object List from Request” and specify the request number which holds the latest version of the required web template.

The message bar should indicate that the objects are inserted from one request to another; this could also be confirmed by checking the objects in the new request.

Step 5: Release the request from Quality so that it could be imported in Development.

Step 6: Login to development system and import the released request containing the web template from Quality:

Go to transaction STMS_IMPORT in development.

You should be able to see the request released from Quality here, provided you did not miss to mention the target as Development during creation of the request in Step 3.

Choose the required options and the import should start.

Step 7: Moment of Truth.

Post successful import of the transport run the development relevant URL and you should be able to find the web template opening up.

Also you should be able to find the deleted web template upon searching:

Even though we know the cure, prevention is always better. Please be extremely cautious before deleting objects from any system.

All about Attributes….

By:Sridevi Aduri
Attributes are InfoObjects that exist already, and that are assigned logically to the new characteristic
Navigational Attributes
A Navigational Attibute is any attribute of a Characteristic which is treated in very similar way as we treat as Characteristic while Query Designer. Means one can perform drilldowns, filters etc on the same while Query designing.
Imp Note:

• While Creating the Info Object -- Attributes Tab Page -- Nav Attri butes to be switched on .
• While Designign the Cube we need to Check mark for the Nav. Attributes to make use of them.

Features / Advantages

• Nav. Attr. acts like a Char while Reporting. All navigation functions in the OLAP processor are also possible
• Filters, Drilldowns, Variables are possible while Reporting.
• It always hold the Present Truth Data .
• Historic Data is not possible through Nav. Attributes.
• As the data is fetching from Master Data Tables and Not from Info Cube.

Disadvantages:

• Leads to less Query Performance.
• In the enhanced star schema of an InfoCube, navigation attributes lie one join further out than characteristics. This means that a query with a navigation attribute has to run an additional join
• If a navigation attribute is used in an aggregate, the aggregate has to be adjusted using a change run as soon as new values are loaded for the navigation attribute.
http://help.sap.com/saphelp_nw04s/helpdata/EN/80/1a63e7e07211d2acb80000e829fbfe/frameset.htm

Transitive Attributes

A Navigational attribute of a Navigational Attributes is called Transitive Attribute. Tricky right Let me explain

• If a Nav Attr Has the further more Nav. Attributes ( as its Attributes ) in it those are called Transitive Attributes .
• For Example Consider there exists a characteristic ‘Material’ .It has ‘Plant’ as its navigational attribute. ‘Plant’ further has a navigational attribute ‘Material group’. Thus ‘Material group’ is the transitive attribute. A drilldown is needed on both ‘Plant’ and ‘Material group’.
• And again we need to have both Material & Plant in your Info Cube to Drill down. (To fetch the data through Nav. Attrs. we need Master Data tables hence, we need to check mark/select both of them in the Cube )http://help.sap.com/saphelp_nw04s/helpdata/EN/6f/c7553bb1c0b562e10000000a11402f/frameset.htm

If Cube contains both ‘Material’ and ‘Plant’ Dimension table having both ‘Material’ and ‘Plant’ will have Dim ID, Sid of Material, and Sid of Plant. Since both the Sids exists reference of each navigational attribute is made correctly.

• If Cube contains only 'Material’

Dimension table having only ‘Material’ will have Dim ID, Sid of Material. Since Sid for first level navigational attribute (Plant) does not exists, reference to navigational attribute is not made correctly.
Exclusive Attributes / Attributes Only/ Display Attribute

If you set the Attribute Only Indicator(General Tab page for Chars / Addl Properties tabpage for Key Figs) for Characteristic while creating, it can only be used as Display Attribute for another Characteristic and not as a Nav. Attr.
Features:

• And it cannot be included in Info Cubes
• It can be used in DSO, Infoset and Char as InfoProviders. In this Info Provider , the char is not visible during read access (at run time)
• This means, it is not available in the query. If the Info Provider is being used as source of Transformation or DTP the characteristic is not visible.
• It is just for Display at Query and cannot be used for Drill downs while reporting.

Exclusive attributes:
If you choose exclusively attribute, then the created key figure can only be used as an attribute for another characteristic, but cannot be used as a dedicated key figure in the InfoCube.

• While Creating A Key Figure -- The Tabpage:Additional Properties -- Check Box for Attributies Only

Free xml sitemap generator
 

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?