BI DataStore Objects

Definition

A DataStore object serves as a storage location for consolidated and cleansed transaction data or master data on a document (atomic) level.

This data can be evaluated using a BEx query.

A DataStore object contains key fields (for example, document number/item) and data fields that can also contain character fields (for example, order status, customer) as key figures. The data from a DataStore object can be updated with a delta update into InfoCubes and/or other DataStore objects or master data tables (attributes or texts) in the same system or across different systems.

Unlike multidimensional data storage using InfoCubes, the data in DataStore objects is stored in transparent, flat database tables. The system does not create fact tables or dimension tables.

Use

The cumulative update of key figures is supported for DataStore objects, just as it is with InfoCubes, but with DataStore objects it is also possible to overwrite data fields. This is particularly important with document-related structures. If documents are changed in the source system, these changes include both numeric fields, such as the order quantity, and non-numeric fields, such as the ship-to party, status and delivery date. To reproduce these changes in the DataStore objects in the BI system, you have to overwrite the relevant fields in the DataStore objects and set them to the current value. Furthermore, you can use an overwrite and the existing change log to render a source delta enabled. This means that the delta that is further updated to the InfoCubes, for example, is calculated from two successive after-images.

There are different types of DataStore object:

...

● Standard (see Standard DataStore Objectss)

● For direct update (see DataStore Objects for Direct Updates)

● Write-optimized (see Write-Optimized DataStore Objectss)

Differences between DataStore object types:

Type	Structure	Data Supply	SID Generation	Are BEx Queries Possible?
Standard	Consists of three tables: activation queue, table of active data, change log	From data transfer process	Yes	Yes
For direct update	Consists of the table of active data only	From APIs	No	Yes
Write-optimized	Consists of the table of active data only	From data transfer process	No	Yes

Integration

Integration with the Data Warehousing Workbench - Modeling

Metadata

DataStore objects are fully integrated with BI metadata. They are transported just like InfoCubes and are installed from BI Content (for more information, see Installing Business Content). DataStore objects are grouped with InfoCubes in the InfoProvider view of the Data Warehousing Workbench - Modeling, and are displayed in a tree. They also appear in the data flow display.

Update

Transformation rules define the rules that are used to write data to a DataStore object. They are very similar to the transformation rules for InfoCubes. The main difference is the behavior of data fields in the update. When you update requests into a DataStore object, you have an overwrite option as well as an addition option.

See also Transformation Type.

The delta process that is defined for the DataSource also influences the update. When loading files, the user must select a suitable delta process so that the correct transformation type is used.

Unit fields and currency fields operate just like normal key figures, meaning that they must be explicitly filled using a rule.

Scheduling and Monitoring

The processes for scheduling the data transfer process for updating data into InfoCubes and DataStore objects are identical.

It is also possible to schedule the activation of DataStore object data and the update from the DataStore object into the related InfoCubes or DataStore objects.

The individual steps, including processing the DataStore object, are logged in the monitor.

There is a separate detailed monitor for executed request operations (such as activation or rollback).

Loadable DataSources

In full-update mode, each transaction data DataSource contained in a DataStore object can be updated. In delta-update mode, only those DataSources that are flagged as delta-enabled DataStores can be updated.

BW Scenario for SAP DataStore Objects

The diagram below shows how DataStore objects are used in this example of updating order and delivery information, and the status tracking of orders, meaning which orders are open, which are partially-delivered, and so on.

There are three main steps to the entire data process:

...

Loading the data into the BI system and storing it in the PSA

The data requested by the BI system is stored initially in the PSA. A PSA is created for each DataSource and each source system. The PSA is the storage location for incoming data in the BI system. Requested data is saved, unchanged, to the source system.

1. Processing and storing the data in DataSource objects

In the second step, the DataSource objects are used on two different levels.

a. On level one, the data from multiple source systems is stored in DataSource objects. Transformation rules permit you to store the consolidated and cleaned up data in the technical format of the BI system. On level one, the data is stored on the document level (for example, orders and deliveries) and constitutes the consolidated database for further processing in the BI system. Data analysis is therefore not usually performed on the DataSource objects at this level.

b. On level two, transfer rules subsequently combine the data from several DataStore objects into a single DataStore object in accordance with business-related criteria. The data is very detailed, for example, information such as the delivery quantity, the delivery delay in days, and the order status, are calculated and stored per order item. Level 2 is used specifically for operative analysis issues, for example, which orders are still open from the last week. Unlike multidimensional analysis, where very large quantities of data are selected, here data is displayed and analyzed selectively.

2. Storing data in the InfoCube

In the final step, the data is aggregated from the DataStore object on level two into an InfoCube, meaning in this scenario, that the InfoCube does not contain the order number, but saves the data, for example, on the levels of customer, product, and month. Multidimensional analysis is also performed on this data using a BEx query. You can still display the detailed document data from the DataStore object whenever you need to. Use the report/report interface from a BEx query. In this way, you are able to analyze the aggregated data from the InfoCube, and target the specific level of detail you want to access in the data.

SAP Standard BI DataStore Object

Use

The standard DataStore object is filled with data during the extraction and load process in the BI system.

Structure

A standard DataStore object is represented on the database by three transparent tables:

Activation queue: Serves to save DataStore object data records that are to be updated, but that have not yet been activated. The data is deleted after the records have been activated.

Active data: A table containing the active data (A table).

Change log: Contains the change history for the delta update from the DataStore object into other data targets, such as DataStore objects or InfoCubes.

The tables of active data are built according to the DataStore object definition. This means that key fields and data fields are specified when the DataStore object is defined. The activation queue and the change log are almost identical in structure: the activation queue has an SID as its key, the package ID and the record number; the change log has the request ID as its key, the package ID, and the record number.

This graphic shows how the various tables of the DataStore object work together during the data load.

Data can be loaded performantly from several source systems simultaneously because a queuing mechanism enables a parallel INSERT. The key allows records to be labeled consistently in the activation queue.

The data arrives in the change log from the activation queue and is written to the table for active data upon activation. During activation, the requests are sorted according to their logical keys. This ensures that the data is updated to the table for active data in the correct request sequence.

See also: Example of Activating and Updating Data.

DataStore Objects for Direct Update

Definition

The DataStore object for direct update differs from the standard DataStore object in terms of how the data is processed. In a standard DataStore object, data is stored in different versions (active, delta, modified), whereas a DataStore object for direct update contains data in a single version. Therefore, data is stored in precisely the same form in which it was written to the DataStore object for direct update by the application. In the BI system, you can use a DataStore object for direct update as a data target for an analysis process. For more information, see Analysis Process Designer.

The DataStore object for direct update is also required by diverse applications, such as SAP Strategic Enterprise Management (SEM) for example, as well as other external applications.

Use

DataStore objects for direct update ensure that the data is available quickly. The data from this kind of DataStore object is accessed transactionally. The data is written to the DataStore object (possibly by several users at the same time) and reread as soon as possible.

It is not a replacement for the standard DataStore object. It is an additional function that can be used in special application contexts.

Structure

The DataStore object for direct update consists of a table for active data only. It retrieves its data from external systems via fill or delete APIs. See DataStore Data and External Applications.

The load process is not supported by the BI system. The advantage to the way it is structured is that it is easy to access data. Data is made available for analysis and reporting immediately after it is loaded.

Creating DataStore Objects for Direct Update

When you create a DataStore object, you can change the DataStore object type under Settings in the context menu. The default setting is Standard. You can only switch between DataStore object types standard and direct update if data does not yet exist in the DataStore object.

Integration

Since you cannot use staging and fill DataStore objects for direct update with BI data (DataSources do not provide the data), DataStore objects are not displayed in the scheduler or in the monitor. However, you can update the data in DataStore objects for direct update to additional InfoProviders.

If you switch a standard DataStore object that already has update rules to direct update, the update rules are set to inactive and can no longer be processed.

Since no change log is generated, you cannot perform a delta update to the InfoProviders at the end of this process.

The DataStore object for direct update is available as an InfoProvider in the BEx Query Designer and can be used for analysis purposes.

SAP Write-Optimized BI DataStore Objects

Definition

DataStore object that only consists of one table of active data. Data is loaded using the data transfer process.

Use

Data that is loaded into write-optimized DataStore objects is available immediately for further processing.

You use write-optimized DataStore objects in the following scenarios:

● You use a write-optimized DataStore object as a temporary storage area for large sets of data if you are executing complex transformations for this data before it is written to the DataStore object. Subsequently, the data can be updated to further (smaller) InfoProviders. You only have to create the complex transformations once for all data.

● You use write-optimized DataStore objects as the EDW layer for saving data. Business rules are only applied when the data is updated to additional InfoProviders.

The system does not generate SIDs for write-optimized DataStore objects and you do not need to activate them. This means that you can save and further process data quickly. Reporting is possible on the basis of these DataStore objects. However, we recommend that you use them as a consolidation layer, and update the data to additional InfoProviders, standard DataStore objects, or InfoCubes.

Structure

Since the write-optimized DataStore object only consists of the table of active data, you do not have to activate the data, as is necessary with the standard DataStore object. This means that you can process data more quickly.

The loaded data is not aggregated; the history of the data is retained. If two data records with the same logical key are extracted from the source, both records are saved in the DataStore object. The record mode responsible for aggregation remains, however, so that the aggregation of data can take place later in standard DataStore objects.

The system generates a unique technical key for the write-optimized DataStore object. The standard key fields are not necessary with this type of DataStore object. If standard key fields exist anyway, they are called semantic keys so that they can be distinguished from the technical keys. The technical key consists of the Request GUID field (0REQUEST), the Data Package field (0DATAPAKID) and the Data Record Number field (0RECORD). Only new data records are loaded to this key.

You can specify that you do not want to run a check to ensure that the data is unique. If you do not check the uniqueness of the data, the DataStore object table may contain several records with the same key. If you do not set this indicator, and you do check the uniqueness of the data, the system generates a unique index in the semantic key of the InfoObject. This index has the technical name "KEY". Since write-optimized DataStore objects do not have a change log, the system does not create delta (in the sense of a before image and an after image). When you update data into the connected InfoProviders, the system only updates the requests that have not yet been posted.

Use in BEx Queries

For performance reasons, SID values are not created for the characteristics that are loaded. The data is still available for BEx queries. However, in comparison to standard DataStore objects, you can expect slightly worse performance because the SID values have to be created during reporting.

If you want to use write-optimized DataStore objects in BEx queries, we recommend that they have a semantic key and that you run a check to ensure that the data is unique. In this case, the write-optimized DataStore object behaves like a standard DataStore object. If the DataStore object does not have these properties, unexpected results may be produced when the data is aggregated in the query.

SAP Creating BI DataStore Objects

Procedure

...

1. Select the InfoArea that you want to assign the DataStore object to, or create a new InfoArea. Choose Modeling ® Info Providers ® Create InfoArea.

2. In the context menu of the InfoArea, choose Create DataStore Object.

3. Specify a name and a description for the DataStore object, and choose Create.

If you want to create a copy of an existing DataStore object, specify the DataStore object that you want to use as a template.

You get to the DataStore object maintenance screen.

4. Copying InfoObjects:

On the left side of the screen, there are various templates to choose from. These allow you to get a better overview in relation to a particular task. For performance reasons, the default setting is an empty template. You use the pushbuttons to select different objects as templates.

On the right side of the screen you define the DataStore object. Using the drag and drop function, assign the InfoObjects in the key fields and in the data fields. You can select several InfoObjects at once. The system assigns navigation attributes automatically. These navigation attributes can be switched on to analyze data in BEx.

Or:

You are also able to insert InfoObjects without selecting a template in the left half of the screen. This is useful if you know exactly which InfoObjects you want to include in the DataStore object. To do this, choose Insert InfoObjects in the context menu for the node for key fields or data fields. In the dialog box that appears, you can enter and transfer up to 10 ten InfoObjects directly and or you can select them using input help. You can use drag and drop to reassign them.

There has to be at least one key field and one data field.

Additional restrictions:

- You can create a maximum of 16 key fields (if you have more key fields, you can combine fields using a routine for a key field (concatenate)).

- You can create a maximum of 749 fields

- You can use 1962 bytes (minus 44 bytes for the change log)

- You cannot include key figures as key fields

5. In the context menu of the Data Fields folder you can Insert New Hierarchy Nodes. In this way you can sort the data fields in a hierarchy. You then get a better overview of large quantities of data fields in query definition.

6. Using Settings you can make various settings and define the properties of the DataStore object. See also DataStore Object Settingss.

7. Under Indexes, call the context menu to create secondary indexes. This improves the load and query performance of the DataStore object. Primary indexes are created automatically by the system.

If the values in the index fields uniquely identify each record in the table, select Unique Index from the dialog box. Note that errors can occur during activation if the values are not unique.

The description of the indexes is specified by the system. To create a folder for the indexes, choose Continue from the dialog box. Now you can transfer the required key fields into the index folder using drag and drop.

You can create a maximum of 16 secondary indexes. These are also transported automatically.

For more information see Indexes.

8. Use Check to make sure that the DataStore object is consistent.

9. Save the DataStore object, and activate it. When you activate the DataStore object, the system generates an export DataSource. You use this to update the DataStore object data to further InfoProviders.

DataStore Object Settings

Use

When creating and changing a DataStore object, you can make the following settings:

DataStore Object Type

Select the DataStore object type. You can choose between standard, direct update and write-optimized, where standard is the default value and direct update is only intended for special cases. You can switch the type as long as there is still no data in the DataStore object.

More information: Standard DataStore Objects, DataStore Objects for Direct Update, and Write-Optimized DataStore Objects

Check Table for InfoObjects

Here, the system displays the name of the InfoObject for which the DataStore object can also be the check table. You can specify this when creating an InfoObject. More information: Tab Page: Master Data/Texts in the InfoObject maintenance

Type-Specific Settings

The following settings are only available for certain DataStore object types:

For Write-Optimized DataStore Objects

Do Not Check Uniqueness of Data

This indicator is only relevant for write-optimized DataStore objects. With these objects, the technical key of the active tables always consists of the fields Request, Data Package, and Data Record. The InfoObjects that appear in the maintenance dialog in the Semantic Key folder form the semantic key of the write-optimized DataStore object.

If you set this indicator, no unique index is generated with the technical name "KEY" for the InfoObjects in the semantic key, and there can be multiple records with the same key in the active table of the DataStore object.

For Standard DataStore Objects:

Generating SID Values

With the Generation of SID Values indicator, you specify whether SIDs are created for the new characteristic values in the DataStore object when the data is activated. If you do not set the indicator, no SIDs are created and activation is completed faster.

Loading Unique Data Records

If you are only loading unique data records (data records with nonrecurring key combinations) into the DataStore object, the loading performance improves if you set the indicator Unique Data Records in the DataStore object maintenance.

The records are then updated more quickly because the system no longer needs to check whether the record already exists. You have to be sure that no duplicate records are loaded because this terminates the process.

If you set this indicator, the system automatically sets the indicator for creating SID values.

This DataStore object property is only supported if SID values are created during activation (see the Creating SID Values indicator). Otherwise, we recommend using a write-optimized DataStore object.

Automatic Further Processing

If you still use a 3.x InfoPackage to load data, you can activate several automatisms to further process the data in the DataStore object. If you use the data transfer process and process chains that SAP recommends you use, you cannot however use these automatisms.

We recommend that you always use process chains.

More information: Including DataStore Objects in Process Chains

Settings for automatic further processing:

● Automatically Setting Quality Status to OK

Using this indicator, you can specify that the system automatically sets the quality status of the data to OK after the data has been loaded into the DataStore object. Activate this function. You should only deselect this indicator if you want to check the data after it has been loaded.

● Activating the DataStore Object Data Automatically

Using this indicator, you can specify that data that has the quality status OK is transferred from the activation queue into the table of active data, and that the change log is updated. Activation is carried out by a new job that is started after data has been loaded into a DataStore object. If the activation process terminates, there can be no automatic update.

● Updating Data from DataStore Objects Automatically

Using this indicator, you can specify that the DataStore object data is automatically updated. Once the data has been activated, it is updated to the connected InfoProviders. The first update is automatically an initial update. If the activation process terminates, there can be no automatic update. The update is carried out by a new job that is started once activation is complete.

Only switch on automatic activation and automatic update if you are sure that these processes do not overlap.

For further settings, see Performance Optimization for DataStore Objects.

SAP BW Multidimensional Clustering Data Store Objects

Use

Multidimensional clustering (MDC) allows you to save the sorted data records in the active table of a DataStore object. Data records with the same key field values are saved in the same extents (related database storage unit). This prevents data records with the same key values from being spread over a large memory area and thereby reduces the number of extents to be read upon accessing tables. Multidimensional clustering greatly improves active table queries.

Prerequisites

Currently, the function is only supported by the database platform IBM DB2 Universal Database for UNIX and Windows.

Features

Multidimensional clustering organizes the data records of the active table of a DataStore object according to one or more fields of your choice. The selected fields are also indicated as MDC dimensions. Only data records with the same values in the MDC dimensions are saved in an extent. In the context of MDC, an extent is called a block.

The system creates block indexes from within the database for the selected fields. Block indexes link to extents instead of data record numbers and are therefore much smaller than row-based secondary indexes. They save memory space and can be searched through more quickly. This particularly improves performance of table queries that are restricted to these fields.

You can select the key fields of an active table of a DataStore object as an MDC dimension.

Multidimensional clustering was introduced in Release SAP NetWeaver 2004s and can be set up separately for each DataStore object.

For procedures, see Definition of Clustering.

Data Store Objects Definition of Clustering

Prerequisites

You can only change clustering if the DataStore object does not contain any data. You can change the clustering of DataStore objects that are already filled using the Reclustering function.

For more information, see Reclustering.

Features

In the DataStore maintenance, select Extras ® DB Performance ® Clustering.

You can select MDC dimensions for the DataStore object on the Multidimensional Clustering screen.

Select one or more InfoObjects as MDC dimensions and assign them consecutive sequence numbers, beginning with 1. The sequence number shows whether a field has been selected as an MDC dimension and determines the order of the MDC dimensions in the combined block index.

In addition to block indexes for the different MDC dimensions within the database, the system creates the combined block index. The combined block index contains the fields of all the MDC dimensions. The order of the MDC dimensions can slightly affect the performance of table queries that are restricted to all MDC dimensions and those that are used to access the combined block index.

When selecting, proceed as follows:

· Select InfoObjects that you use to restrict your queries. For example, you can use a time characteristic as an MDC dimension to restrict your queries.

· Select InfoObjects with a low cardinality. For example, the time characteristic 0CALMONTH instead of 0CALDAY.

You cannot select more than three InfoObjects.

· Assign sequence numbers using the following criteria:

¡ Sort the InfoObjects according to how often they occur in queries (assign the lowest sequence number to the InfoObject that occurs most often in queries).

¡ Sort the InfoObjects according to selectivity (assign the lowest sequence number to the InfoObject with the most different data records).

Note: At least one block is created for each value combination in the MDC dimension. This memory area is reserved independently of the number of data records that have the same value combination in the MDC dimension. If there is not a sufficient number of data records with the same value combinations to completely fill a block, the free memory remains unused. This is so that data records with a different value combination in the MDC dimension cannot be written to the block.

If for each combination that exists in the DataStore object, only a few data records exist in the selected MDC dimension, most blocks have unused free memory. This means that the active tables use an unnecessarily large amount of memory space. Performance of table queries also deteriorates, as many pages with not much information must be read.

Example

The size of a block depends on the PAGESIZE and the EXTENTSIZE of the tablespace. The standard PAGESIZE of the DataStore tablespace with the assigned data class DODS is 16K. Up to Release SAP BW 3.5, the default EXTENTSIZE value was 16. As of Release SAP NetWeaver 2004s the new default EXTENTSIZE value is 2.

With an EXTENTSIZE of 2 and a PAGESIZE of 16K the memory area is calculated as 2 x 16K = 32K, this is reserved for each block.

The width of a data record depends on the width and number of key fields and data fields in the DataStore object.

If, for example, a DataStore object has 10 key fields, each with 10 bytes, and 30 data fields with an average of 9 bytes each, a data record needs 10 x 10 bytes + 30 x 9 bytes = 370 bytes. In a 32K block, 32768 bytes/370 bytes could write 88 data records. At least 80 data records should exist for each value combination in the MDC dimensions. This allows optimal use of the memory space in the active table.

DataStore Data and External Applications

Use

Various functions allow you to read data from DataStore objects from external applications or to fill DataStore objects from external applications.

Features

The BAPI, BAPI_ODSO_READ_DATA_UC, for reading data, enables you to make DataStore data available to external systems. This BAPI can be used for all DataStore object types.

BAPI BAPI_ODSO_READ_DATA was used fort his purpose in the previous release. It is now obsolete.

The following APIs are available for direct update of DataStore objects (see DataStore Object for Direct Update):

RSDRI_ODSO_INSERT: Inserts new data (with keys not yet in the system).

RSDRI_ODSO_INSERT_RFC: see above, can be called up remotely

RSDRI_ODSO_MODIFY: inserts data having new keys; for data with keys already in the system, the data is changed.

RSDRI_ODSO_MODIFY_RFC: see above, can be called up remotely

RSDRI_ODSO_UPDATE: changes data with keys in the system

RSDRI_ODSO_UPDATE_RFC: see above, can be called up remotely

RSDRI_ODSO_DELETE_RFC: deletes data

Also take a look at the extensive documentation for this function module in the BW system.

These APIs allow you to fill DataStore objects with data from external applications, for example, from Web applications.

You can also download from the DataStore object. You can display the DataStore table data from the DataStore object administration, and also download the data using the data browser so that you can use or display it in other applications. For more information see DataStore Object Content.

Data Store Objects Interview Questions and Answers

What are the benefits of loading requests in parallel?

Several requests can be updated more quickly in the DataStore object.

Can the processes for loading and activating requests be started independently of one another?

Yes. You can create a process chain that starts the activation process once the loading process is complete. More information: Including DataStore Objects in Process Chains

Is there a maximum number of records that can be activated simultaneously?

No.

Can I change the loading method that is used to load the data into the DataStore object from a full update to a delta update?

No. Once a full update has been used to load data into the DataStore object, you are no longer able to change the loading method for this particular combination of DataSource and source system. One exception to this is updating a DataStore object to another (not yet filled) DataStore object if InfoProviders already exist that have been supplied with deltas from the DataStore object. You can run a full upload, which is handled like an initial, into the empty DataStore object and then load deltas on top of that.

Why is it that, after multiple data loads, the change log is larger than the table of active data?

The change log grows in proportion to the table of active data, because before and after-images of each new request are stored there. More information: Example for Activating and Updating Data and the description of the delta process.

Can I delete date from the change log once the data has been activated?

If a delta initialization is available for updates to connected InfoProviders, requests have to be updated before the corresponding data can be deleted from the change log. In the DataStore object administration, you can then call the Delete Change Log Data function. You can schedule this process to run periodically.

However, you cannot immediately delete the data that you just activated, because the most recent deletion selection that you can specify is Older Than 1 Day.

Are locks set when I delete data from the DataStore object to prevent data being written simultaneously?

More information: Functional Constraints of Processes

When is it useful to delete data from the DataStore object?

There are three options available for deleting data from the DataStore object: by request, selectively, and from the change log. To determine the best option, read the detailed description of deleting data from DataStore objects.

When do I use the DataStore object for direct update?

You use this type of DataStore object to load data quickly without using the extraction and load processes in the BI system. More information: DataStore Objects for Direct Update

InfoObjects As InfoProvider SAP BW

Definition

You can indicate an InfoObject of type characteristic as an InfoProvider if it has attributes and/or texts. The data is then loaded into the master data tables using the transformation rules.

You can only designate a characteristic as an InfoProvider if it contains texts or attributes. It is not possible to use transformation rules to load hierarchies.

Use

You can define transformation rules for the characteristic and use them to load attributes and texts.

You can also define queries for the characteristic (more exactly: for the master data of a characteristic) and then report using the master data.

In InfoObject maintenance, you can also select two-level navigation attributes (the navigation attributes for the navigation attributes of the characteristic) for this characteristic in the Attribute tab page. These are then available like normal characteristics in the query definition.

Integration

If you want to turn a characteristic into an InfoProvider, you have to assign an InfoArea to the characteristic. The characteristic is subsequently displayed in the InfoProvider tree in the Data Warehousing Workbench.