info cube design

8/10/2019 Info Cube design

http://slidepdf.com/reader/full/info-cube-design 1/16

INFOCUBE-INDEX

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 calledInfoCubes & 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 StarSchema/Snow Flake Schema, that contains• 1 Fact Table• n Dimension Tables• n Surrogate ID (SID) tables• n Fact Tables• n Master Data TablesFact Table with KeyFiguresn Dimension Tables with characteristicsn Surrogate ID (SID) tables link Master data tables & Hierarchy Tablesn 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 reportsin 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

http://scn.sap.com/servlet/JiveServlet/showImage/102-47612-1-297160/Infocube+types.jpg

http://4.bp.blogspot.com/-utl8xEmPSK4/T-6zzWeQPlI/AAAAAAAAAHg/yFL7jIaNe44/s1600/Infocube+Types.jpg



3.1. BASIC CUBE: 2 TYPES: These are physically available in the same BW system inwhich 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 frequentlyused and used only by certain applications such as SEM & APO. Data are writtendirectly into such cubes bypassing UpdateRules

3.2. REMOTE CUBES: 3 TYPES:Remote cubes reside on a remote system. RemoteCubes 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 & communicationis 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. SAPor non SAP & is available via user defined function module. 4. INFOCUBE TABLES- F,E,P,T,U,NTransaction Code: LISTSCHEMALISTSCHEMA>enter name of the InfoSource OSD_C03 & Execute. Upon execution theprimary (Fact) table is displayed as an unexpanded node. Expand the node and see thescreen.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

http://scn.sap.com/servlet/JiveServlet/showImage/102-47612-1-297209/Infocube+tables.jpg

http://4.bp.blogspot.com/-5Mfn1cEj1jw/T-6z-Fe7ZuI/AAAAAAAAAHo/UPBM1q6nYuc/s1600/FEPTUN.jpg



available which would enable a quick reference for the intended values of fields in thetable.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 paralleldata reads of multiple partitions speeding up the query execution process.• By partitioning an InfoCube, the reporting performance is enhanced because it iseasier 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 LEVEL

Physical Partitioning also called table/low level partitioning is restricted to TimeCharacteristics and is done at Data Base Level, only if the underlying database allowsit.Ex: Oracle, Informix, IBM, DB2/390Here is a common way of partitioning is to create ranges. InfoCube can be partitionedon 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 PARTITIONING

Logical partitioning is done at MultiCubes(several InfoCubes joined into a MultiCube) orMultiProvider 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 queryperformance 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 intosmaller InfoCube from large InfoCube target, even in absence of MultiProvider.

5.3.3. EXAMPLES ON PARTITIONING USING 0CALMONTH & 0FISCYEAR



THERE 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 theInfoCube.

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 numberof 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 thedatabase 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)= 74single values.

The system groups three months at a time together in a partition4 Quarters Partitions = 1 YearSo, 6 years * 4 partitions/year + 2 marginal partitions = 26 partitions are created on thedatabase.



The performance gain is only gained for the partitioned InfoCube if the time dimensionof the InfoCube is consistent.

This means that all values of the 0CAL* characteristics of a data record in the timedimension must fit each other with a partitioning via 0CALMONTH.

Note: You can only change the value range when the InfoCube does not contain anydata.

PARTITIONING INFOCUBES USING THE CHARACTERISTIC 0FISCPER Mandatorything 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 emptypartitions do not have a corresponding entry in the related package dimension.

Solution1: the request SAP_PARTITIONS_INFO_GET_DB4 helps you to analyze theseproblems. The empty partitions of the f fact table are reported . In addition, the systemissues an information manage. If there is no corresponding entry for a partition in theInfoPackage 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 theapplication. The logs in the area of compression do not display any error manages. Theerror 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 affectedrequests or partitions is not displayed in the reporting because they do not have acorresponding entry in the package dimension.



Solution2: use the report SA P_DROP_FPARTITIONS</Z1) to remove the orphaned orempty partitions from the affected f fact tables, as described in note 1306747, to ensurethat 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 cubewhich is already partitioned that has loaded data. Actual & Plan data versions comehere. As we know, the InfoCube has actual data which is already loaded as per plandata after partition. If we do repartition, the data in the cube will be not available/littledata 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 andC) 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 fornumber of partitions per SQL 2005(i.e. 1000)

5.4. COMPRESSION OR COLLAPSE:Compression reduces the number of records bycombining records with the same key that has been loaded in separate requests.

Compression is critical, as the compressed data can no longer deleted from theInfoCube using its request ID's. You must be certain that the data loaded into theInfoCube 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 therequest ID's are lost / deleted / set to null.

After compression, comparably the space used by E-Fact Table is lesser than F-FactTable.F-Fact Table is compressed uses BITMAP IndexesE-Fact Table is uncompressed uses B-TREE Indexes

5.5. INDEX/INDICES

PRIMARY INDEX :The primary Index is created automatically when the table iscreated 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 ordifferent cube/ODS from PSA. The main purpose is that after deleting the requests byCompression/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 themfrom PSA.

Reconstruction of a cube is a more common r equir ement and is requir ed when:

1) A change to the structure of a cube: deletion of characteristics/key figures, newcharacteristics/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 datahas 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, whichdisplays incorrect values in BW, because the logic of before and After images are nolonger 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) Entertransaction - 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 = applicationnumber) 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 thecorresponding 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 datafrom 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 andSCN and formulated a simple thesis to make the audience, easy in understanding theconcepts

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 yourcurrent 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 tablesas 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 inBW 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, After image

„ „ 4712 12 reconstruction



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 asLine Item, You cannot *** additional Characteristics.

• Only one characteristic is allowed per Line Item Dimension & for (F4) help, the MasterData is displayed, which takes more time.

http://scn.sap.com/servlet/JiveServlet/showImage/102-47612-1-297298/lid.jpg

http://scn.sap.com/servlet/JiveServlet/showImage/102-47612-1-297297/ESS+Extended+Star+Schema.jpg

http://scn.sap.com/servlet/JiveServlet/showImage/102-47612-1-297298/lid.jpg

http://scn.sap.com/servlet/JiveServlet/showImage/102-47612-1-297297/ESS+Extended+Star+Schema.jpg

http://1.bp.blogspot.com/-ApwHj7BU2GI/T-60SZFOXHI/AAAAAAAAAHw/klo_VrlJ45U/s1600/FTDTSID.png

http://2.bp.blogspot.com/-65CdMlhQHpI/T-60ZvO7lSI/AAAAAAAAAH4/Z9iJP2KqMxg/s1600/ESS.jpg



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 isexpected 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 asHigh 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 goingReconstruction?

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 InfoCubedesign?

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?