Create an InfoCube

Create an InfoCube

Creating an InfoCubeIn BW,Customer ID,Material Number,Sales Representative ID,Unit of Measure,and Transaction Date are called characteristics. Customer Name and Customer Address are attributes of Customer ID,although they are characteristics as well. Per Unit Sales Price,Quantity Sold,and Sales Revenue are referred to as key figures. Characteristics and key figures are collectively termed InfoObjects.

A key figure can be an attribute of a characteristic. For instance,Per Unit Sales Price can be an attribute of Material Number. In our examples,Per Unit Sales Price is a fact table key figure. In the real world,such decisions are made during the data warehouse design phase. InfoCube Design provides some guidelines for making such decisions.

InfoObjects are analogous to bricks. We use these objects to build InfoCubes. An InfoCube comprises the fact table and its associated dimension tables in a star schema.

In this chapter,we will demonstrate how to create an InfoCube that implements the star schema from figure. We start from creating an InfoArea. An InfoArea is analogous to a construction site,on which we build InfoCubes.

Creating an InfoArea

In BW,InfoAreas are the branches and nodes of a tree structure. InfoCubes are listed under the branches and nodes. The relationship of InfoAreas to InfoCubes in BW resembles the relationship of directories to files in an operating system. Let's create an InfoArea first,before constructing the InfoCube.

Work Instructions
Step 1. After logging on to the BW system,run transaction RSA1,or double-click Administrator Workbench.



Step 2. In the new window,click Data targets under Modelling in the left panel. In the right panel,right-click InfoObjects and select Create InfoArea….


Note
In BW,InfoCubes and ODS Objects are collectively called data targets.
Step 3. Enter a name and a description for the InfoArea,and then mark to continue.


Result
The InfoArea has been created.

Creating InfoObject Catalogs
Before we can create an InfoCube,we must have InfoObjects. Before we can create InfoObjects,however,we must have InfoObject Catalogs. Because characteristics and key figures are different types of objects,we organize them within their own separate folders,which are called InfoObject Catalogs. Like InfoCubes,InfoObject Catalogs are listed under InfoAreas.

Having created an InfoArea,let's now create InfoObject Catalogs to hold characteristics and key figures.

Work Instructions
Step 1. Click InfoObjects under Modelling in the left panel. In the right panel,right-click InfoArea–demo,and select Create InfoObject catalog….
Step 2. Enter a name and a description for the InfoObject Catalog,select the option Char.,and then click to create the InfoObject Catalog.
Step 3. In the new window,click to check the Info Object Catalog. If it is valid,click to activate the InfoObject Catalog. Once the activation process is finished,the status message InfoObject catalog IOC_DEMO_CH activated appears at the bottom of the screen.


Result
Click to return to the previous screen. The newly created InfoObject Catalog will be displayed,as shown in Screen

Following the same procedure,we create an InfoObject Catalog to hold key figures. This time,make sure that the option Key figure is selected Screen.

Creating InfoObjects-Characteristics

Now we are ready to create characteristics.

Work Instructions
Step 1. Right-click InfoObject Catalog–demo: characteristics,and then select Create InfoObject….

Step 2. Enter a name and a description,and then click to continue.

Step 3. Select CHAR as the DataType,enter 15 for the field Length,and then click the tab Attributes.

Step 4. Enter an attribute name IO_MATNM,and then click to create the attribute.

Note: Notice that IO_MATNM is underlined. In BW,the underline works like a hyperlink. After IO_MATNM is created,when you click IO_MATNM,the hyperlink will lead you to IO_MATNM's detail definition window.
Step 5. Select the option Create attribute as characteristic,and then click to continue.


Step 6. Select CHAR as the DataType,and then enter 30 for the field Length. Notice that the option Exclusively attribute is selected by default. Click to continue.

Note: If Exclusively attribute is selected,the attribute IO_MATNM can be used only as adisplay attribute,not as a navigational attribute. "InfoCube Design Alternative I Time Dependent Navigational Attributes," discusses an example of the navigation attributes.

Selecting Exclusively attribute allows you to select Lowercase letters. If the option Lowercase letters is selected,the attribute can accept lowercase letters in data to be loaded.

If the option Lowercase letters is selected,no master data tables,text tables,or another level of attributes underneath are allowed. "BW Star Schema," describes master data tables and text tables,and explains how they relate to a characteristic.
Step 7. Click to check the characteristic. If it is valid,click to activate the characteristic.

Step 8. A window is displayed asking whether you want to activate dependent InfoObjects. In our example,the dependent InfoObject is IO_MATNM.

Click to activate IO_MAT and IO_MATNM.


Result
You have now created the characteristic IO_MAT and its attribute IO_MATNM.

Note: Saving an InfoObject means saving its properties,or meta-data. You have not yet created its physical database objects,such as tables.

Activating an InfoObject will create the relevant database objects. After activating IO_MAT,the names of the newly created master data table and text table are displayed under the Master data/texts tab. The name of the master data table is /BIC/PIO_MAT,and the name of the text table is /BIC/TIO_MAT.

Notice the prefix /BIC/ in the database object names. BW prefixes /BI0/ to the names of database objects of Business Content objects,and it prefixes /BIC/ to the names of database objects of customer-created BW objects.

Repeat the preceding steps to create the other characteristics listed.
CHARACTERISTICS


The column "Assigned to" specifies the characteristic to which an attribute is assigned. For example,IO_MATNM is an attribute of IO_MAT.

The Material Description in Table will be treated as IO_MAT's text,as shown in Table,"Creating InfoPackages to Load Characteristic Data." We do not need to create a characteristic for it.

IO_SREG and IO_SOFF are created as independent characteristics,instead of IO_SREP's attributes. Section 3.6,"Entering the Master Data,Text,and Hierarchy Manually," explains how to link IO_SOFF and IO_SREG to IO_SREP via a sales organization hierarchy. "InfoCube Design Alternative I—Time-Dependent Navigational Attributes," discusses a new InfoCube design in which IO_SOFF and IO_SREG are IO_SREP's attributes.

BW provides characteristics for units of measure and time. We do not need to create them.From Administrator Workbench,we can verify that the characteristics in Table have been created by clicking InfoArea–demo,and then clicking InfoObject Catalog–demo: characteristics.


Creating Info Objects-Key Figures

Next,we start to create the keys.

Work Instructions
Step 1. Right-click InfoObject Catalog–demo: key figures,and then select Create InfoObject.
Step 2. Enter a name and a description,and then click to continue.
Step 3. Select Amount in the block Type/data type,select USD as the Fixed currency in the block Currency/unit of measure,and then click to check the key figure. If it is valid,click to activate the key figure.

Result
You have created the key figure IO_PRC. A status message All InfoObject(s) activated will appear at the bottom of Screen.

Repeat the preceding steps to create other key figures listed.
KEY FIGURES


From Administrator Workbench,we can verify that the key figures in Table have been created (Screen) by clicking InfoArea–demo,and then clicking InfoObject Catalog–demo: key figures.

Having created the necessary InfoObjects,we now continue to create the InfoCube.

Creating an InfoCube

The following steps demonstrate how to create an InfoCube,the fact table and associated dimension tables,for the sales data shown in Table

Work Instructions
Step 1. Select Data targets under Modelling in the left panel. In the right panel,right-click InfoArea–demo and then select Create InfoCube….

Step 2. Enter a name and a description,select the option Basic Cube in block InfoCube type,and then click to create the InfoCube.

Note: An InfoCube can be a basic cube,a multi-cube,an SAP remote cube,or a general remote cube.A basic cube has a fact table and associated dimension tables,and it contains data. We are building a basic cube.

A multi-cube is a union of multiple basic cubes and/or remote cubes to allow cross-subject analysis. It does not contain data. See,Aggregates and Multi-Cubes,for an example.

A remote cube does not contain data;instead,the data reside in the source system. A remote cube is analogous to a channel,allowing users to access the data using BEx. As a consequence,querying the data leads to poor performance.

If the source system is an SAP system,we need to select the option SAP RemoteCube. Otherwise,we need to select the option Gen. Remote Cube. This book will not discuss remote cubes.

Step 3. Select IO_CUST,IO_MAT,and IO_SREP from the Template table,and move them to the Structure table by clicking

Next,click the Dimensions… button to create dimensions and assign these characteristics to the dimensions.

Step 4. Click,and then enter a description for the dimension.

Note: BW automatically assigns technical names to each dimension with the format <InfoCube name><Number starting from 1>.

Fixed dimension <InfoCube name><P|T|U> is reserved for Data Packet,Time,and Unit. Section,"Data Load Requests," discusses the Data Packet dimension.

A dimension uses a key column in the fact table. In most databases,a table can have a maximum of 16 key columns. Therefore,BW mandates that an InfoCube can have a maximum of 16 dimensions: three are reserved for Data Packet,Time,and Unit; the remaining 13 are left for us to use.

Repeat the same procedure to create two other dimensions. Next,click the Assign tab to assign the characteristics to the dimensions.

Step 5. Select a characteristic in the Characteristics and assigned dimension block,select a dimension to which the characteristic will be assigned in the Dimensions block,and then click to assign the characteristic to the dimension.

Step 6. After assigning all three characteristics to their dimensions,click to continue.

Step 7. Select the Time characteristics tab,select 0CALDAY from the Template table,and move it to the Structure table by clicking

Step 8. Select the Key figures tab,select IO_PRC,IO_QUAN,and IO_REV from the Template table and move them to the Structure table by clicking.
Next,click to check the InfoCube. If it is valid,click to activate the InfoCube.

Result:
You have created the InfoCube IC_DEMOBC. A status message InfoCube IC_DEMOBC activated will appear at the bottom of Screen.

Summary

In this chapter,we created an InfoCube. To display its data model,you can right-click InfoCube–demo: Basic Cube,then select Display data model….

The data model appears in the right panel of Screen .

Note:
IO_SREG and IO_SOFF are not listed under IO_SREP as attributes; rather,they have been created as independent characteristics. "Entering the Master Data,Text,and Hierarchy Manually," describes how to link IO_SOFF and IO_SREG to IO_SREP via a sales organization hierarchy. "InfoCube Design Alternative I— Time-Dependent Navigational Attributes,"discusses a new InfoCube design in which IO_SOFF and IO_SREG are IO_SREP's attributes.

Info Cube

 

Remodeling Infocube

Remodeling Infocube

InfoCube Performance

Use

Here you can find the options that allow you to affect not only the load performance, but also the query performance.

Features

Database Indexes

With an increasing number of data records in the InfoCube, not only the load, but also the query performance can be reduced. This is attributed to the increasing demands on the system for maintaining indexes. The indexes that are created in the fact table for each dimension allow you to easily find and select the data. When initially loading data into the InfoCube, you should not create the indexes at the same time as constructing the InfoCube, rather only afterwards.
The indexes displayed are the secondary indexes of the F and E fact tables for the InfoCube. The primary indexes and those defined by the user are not displayed. The aggregates area deals with the associated indexes of the fact table for all aggregates of an InfoCube.

Checking Indexes

Using the Check Indexes button, you can check whether indexes already exist and whether these existing indexes are of the correct type (bitmap indexes).
Yellow status display: There are indexes of the wrong type
Red status display: No indexes exist, or one or more indexes are faulty
You can also list missing indexes using transaction DB02, pushbutton Missing Indexes. If a lot of indexes are missing, it can be useful to run the ABAP reports SAP_UPDATE_DBDIFF and
SAP_INFOCUBE_INDEXES_REPAIR.

Deleting Indexes

For delta uploads with a large quantity of data (more than a million records), you should not align the database indexes of the InfoCube with every roll up, rather delete the indexes first, and then completely reconstruct them after rolling up.

Repairing Indexes

Using this function you can create missing indexes or regenerate deleted indexes. Faulty indexes are corrected.
Build Index
You can delete indexes before each data load and then rebuild them again afterwards. You can also set this automatic index build for delta uploads.

Aggregate Table Indexes

Just as for the indexes of the fact table, you can maintain the indexes of the aggregation tables. Maintaining the indexes of the aggregation tables affects all the aggregation tables of the InfoCube. If the InfoCube contains more than 50 million records, you should, as a rule, refrain from deleting and recreating indexes.

Database Statistics

The database statistics are used by the system to optimize the query performance. You should keep the database statistics up-to-date for this reason.
It is recommended that you always update the statistics if you have loaded more than a million new records into the InfoCube since the last update.
You can automatically recalculate the database statistics after each load or after each delta upload.
Using the pushbutton Check Statistics, you can check the InfoCube to see if statistics exist. If no statistics exist
yet, the status display changes to red. Using Recalculate Statistics, you can add the missing InfoCube statistics in the background.
You can determine the percentage of InfoCube data that is used to create the statistics. The percentage is set to 10% by default. The larger the InfoCube, the smaller you should choose the percentage, since the demand on the system for creating the statistics increases with the change in size.
For up to 10 million entries in the InfoCube, you should set the percentage of InfoCube data, that is used for creating the statistics, to 100%.
Automatic deletion and building of indexes, as well as automatic calculation of database statistics
and cause conflicts with automatic compression and rollup (see Automatic Subsequent Processing ). These processes lock one another. See Functional Constraints of Processes.
If you use process chains to guarantee serial processing of the process, these settings are not
supported for the object. Then you have to include the automatic processes as process types in the process chain.

Loading plan and actual data into the same InfoCube

Here is a document  about loading plan and actual financial data into the same InfoCube. The process seems to be easy, but update mode and aggregation issues make me consider different way of loading such a data.

The actual data need to be loaded with delta update mode, plan data (e.g., 0VTYPE 20) can only use full update and the previous load need to be deleted to avoid duplicated data. I consider 3 loading options:

1. Automatic removal of the previous load after data loading using InfoPackage.

To use this option we need to modify the InfoPakage that is used to load data. In the DataTarget tab we choose Automatic Loading of Similar/Identical Requests from the InfoCube and then Delete Existing Requests, Full or Initial Requests, Same or More Comprehensive.

Now we can schedule the job or add the load to a process chain. After the update, Same or More Comprehensive requests will be deleted.

2. Delete previous load in the process chain.

The second option is to build a process chain (TCode: RSPC) to load data and add deletion of the previous load of plan data using Delete Overlapping Requests from InfoCube option.

3. Remove previous data via ABAP program using DELETE_FACTS.

This option requires including in a process chain an ABAP program that performs selective deletion of the data (TCode to generate the program: DELETE_FACTS).

I must also take into consideration data aggregation. If I have both actual and plan data in one InfoCube I can not aggregate the Cube. One of the option is to create two InfoCubes, the first for actual data (delta update) and the second for plan data (full update), and aggregate the first one. The second option is to remove plan data from our InfoCube before aggregating the InfoCube. I think I will choose the option with two InfoProviders because it appears to be more flexible to maintain.

What is your experience with loading plan and actual data? Which loading option do you prefer and how do you solve the aggregation issue? Feel free to comment the post and share your opinion with us.

Note: The overall issue is related not only with plan values but also with down payments, overall plan values, variances and accrual calculations. For these values only a full update is possible.