Direct Update DSO

You can use a DataStore object for direct update as a data target for an analysis process. Has only Active Data table. They can be filled from APDs. Data can be aggregated as well be over-written into DSO and can also be used for direct reporting, though not recommended.

• SID’s can’t be generated in such DSO.
• Data records with same key are not aggregated in such DSO.
• Every record have its own technical key. Since a change log is not generated, we cannot perform a delta update to the Info Providers.
• Supports parallel load.

Scenario of it is as follows.

Standard DSO

Standard DSO

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

• Activation queue(New Data)
• Used to save DataStore object data records that need to be updated, but that have not yet been activated. After activation, this data is deleted if all requests in the activation queue 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.
• 
  

Data can be loaded from several source systems at the same time because a queuing mechanism enables a parallel INSERT. The key allows records to be labeled consistently in the activation queue. Reports can be built on Std. DSOs.

Delta works with Image concept here. When the data is being loaded from DSO to Infocube, it is always read from the Change Log table which has the history of delta update entries. 0RECORDMODE helps in maintaining the delta concept when data is being loaded to another Infoprovider.

Settings in Std.DSO

Unique Data Records

Overwriting data records not permitted.The DataStore object can be used for loading unique data records.

With this indicator you determine whether ONLY unique data records are posted in the DataStore object. The performance of activation can be increased considerably in this way.

If the loaded request does contain data that is already in the DataStore object, activation of the request will lead to an error.

Note:

This property of a DataStore object is only supported when SID values are generated during activation (see indicator "generate SID values").

SID Generation upon Activation

When checked(Occurs by default), the SIDs Generation Upon Activation box causes the system to generate an integer number known as a Surrogate ID (SID) for each master data value. These SIDs are stored in separate tables called SID tables.

For each characteristic InfoObject, SAP Netweaver BW checks the existence of an SID value for each value of an InfoObject in the SID table. The system then generates a new value of SID if an existing value is not found. The SID is used internally by SAP Netweaver BW when a query is based on a DSO.

Secondary Index on DSO

A secondary index, simply, is a way to efficiently access records in a database by means of some piece of information other than the usual (primary) key. In other words, secondary index will be necessary if a table is accessed in a way that does not take advantage of the sorting of the primary index for the access.

If u click SE14, and give the table name(active table of DSO), it gives you the performance of the select statement on the Table.

Change Log
For each record being loaded from source to DSO, there will be multiple records in the change log.
If the record being loade is a new record, there will be only entry in CL and the record mode will be 'N'.
record mode = 'N'.
If it is an existing record, there are 2 entries. One is Before image where record mode = 'X', and After Image where record mode is ''.

--------------------------------------
Datasource -----> DSO_firstlevel -----> DSO_secondlevel.
In this flow, delta flows fine.
when doing full loads, FULL loads from datasource to DSO_firstlevel but when first level is loaded to second level, no records are loaded ( may be bcos full load did not have any data changes in them ??? Not sure )

-------------------------------------

Change Log Deletion
Change log has to be deleted frequently, so as to improve the performance of the loading  of the DSO.
3 ways of deleting.
Manual Deletion
Go to Manage screen of the DSO --> Environment --> Delete Change Log Data.

Deletion in process chain
Go to 'Other BW Process' --> Deletion of Requests from the Change Log . Check the last check box, else data will not be deleted.

using SE38 Program

Extra Notes
DSO Job Log and Activation Parameters
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f0d82054-f705-2e10-75b8-fc0f428bc16e?overridelayout=true

Write Optimized DSO

Write Optimized DSO

This can be used a base level DSO where the data can be stored same format as it is extracted from source.  Most of the cases the data is saved as replica of PSA, wherein, even if the PSA is deleted the data is always present in this layer.

• Records with the same key are not aggregated, but inserted as new record, as every record has new technical key.
• There are No SIDs generated for this DSO, which decreases the load time.
• It allows parallel load, which saves time for data loading.

Reporting can be done on this DSO but it is not recommended.

Data that is loaded into write-optimized DataStore objects is available immediately for further processing. There is no activation needed for the DSO, saving time. No SIDs are generated and the DSO consists of only ‘Active Table’.

Delta cannot be created based on entries (image based delta ) as they do not have change log, but a request based delta is possible.

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.

Semantic Key

They act as key fields in the DSO, similar to the std. DSO key fields. They help is getting unique records in the DSO, as they do not allow duplicates. The regular technical key of the Wdso would be Request GUID field(0REQUEST), the Data Package field (0DATAPAKID) and the Data Record Number field (0RECORD).

Semantic Key identifies error in incoming records or Duplicate records, thus protects Data Quality such that all subsequent Records with same key are written into error stack along with incorrect Data Records.

My Notes
Semantic keys
they can handle duplicate records..
But if the same record is coming in a next load, it filters out those values tooo...

I think it works when deleting and reloading the data

Datastore Objects

A DataStore object serves as a storage location for consolidated and cleansed transaction data or master data on a document (atomic/detailed) level. The data in DataStore objects is stored in transparent, flat database tables.

The data from a DataStore object can be updated with a delta update into InfoCubes (standard) and/or other DataStore objects or master data tables (attributes or texts) in the same system or across different systems.

There are 3 types of DSOs.

• Standard DSO
• Write Optimized DSO
• Direct Update DSO

Star Schema

Star Schema                 

Star schema basically has a fact table and Dimension table surrounding it. All the metrics are stored in the fact table and the entities and their attributed are stored in the dimension tables. The dimension tables are connected to fact tables but are not connected to each other.

The main reason for opting this model is easy retrieval of data in queries. Let’s say, there is a requirement which says  ‘Show me the Sales Amount by Sales Department by Material Group by Month’. When using Star Schema,  Sales Amount is a present in the Fact table and the Sales Department , Material, and Month in Dimension table. How we model our dimensions is important when using Star Schema.

Case Study:                                                                                                                                                                              

‘Track Performance of Materials with respect to Customers and Sales Person.

• Having complete understanding of the underlying business process
• Identify the Entities and the relations between them. 
• The entities like, Customer, Material and Sales Person are called ‘Strong Entities’ when designing dimensions, we need to know the relation between them. From general understanding we can say that, one customer can buy any number of materials and similarly, a material can be purchased by any number of customers. They share a n:m relation. Same with the Sales Person and Customer, Sales Person and Materials.
• Know the how the performance is measured/Facts.
• For knowing the performance, we need to know how many materials are sold which gives the facts. Facts are normally additive. Sales transactions would give the details. Hence, Sales Transactions become the Intersection Entity which changes the n:m relation between the dimensions to give a 1:n relation.
• Determine if any additional details are required.
• This gives additional entities and attributes. For Materal, we have material number, Material Name, type etc  as attributes and we might need to also consider, material group which is again an entity and can have attributes of its own.
• If additional details are required, we need to know the relation between entities and relation between entities and their attributes.
• Creating a valid data model.
• The organization of the entities and their attributes that are arranged in a parent-child relationship(1:n) into groups need to done

These groups are called dimensions and their properties are called attributes. The strong entities define the dimensions and the attributes of the dimension represent a specific business view on the facts which are derived from the intersection entities.

The attributes of the dimension are then organized in a hierarchical way and the most atomic attribute forms the leaves of the hierarchy tree defines the granularity of the dimension. This is MDM (Multidimensional Model) where the facts are based in the center with the dimensions surrounding them, is a simple but effective concept.

Fact Table:  A central intersection entity defines a Fact Table. An intersection entity such as document number is normally described by facts (sales amount, quantity), which form the non-key columns of the fact table. In fact, M:N relationships between strong entities meet each other in the fact table, thus defining the cut between dimensions.

Dimensions (Tables): Attributes with 1:N conditional relationships should be stored in the same dimension such as material group and material.The foreign - primary key relations define the dimensions

Time Dimension: One exception is the time dimension. As there is no correspondence in the ERM, time attributes (day, week,year) have to be introduced in the MDM process to cover the analysis needs.

Dimension tables should have a 'relatively' small number of rows (in comparison to the fact table; factor at least 1:10 to 1:20).

Disadvantages

• In star schema, the master data is present in the dimension table, which increase the size and also,it is accessible only for that infocube. Resuability is reduced.
• Handling Languages ( For each language , there is a new record)
• Handling Time Dependency – increases the volume of the table
• Star schema used alpha numeric values for keys which increase the time when compared to using numeric keys.
• There can only be 16 dimensions  for conducting analysis(*)