Tuesday, April 12, 2011

Difference between Operational Data Store(ODS) and InfoCube

Infocubes have a multidimensional structure with dimension tables(max 16, 13 custom) and one fact table. they are meant for summarised records.

ODS store data at a more granular level. they have flat structures like a table in R/3. They have a unique feature "overwrite" which is absent in case of cubes.

You can use ODS to load to cube further.

Anyway, one major difference is the manner of data storage. In ODS, data is stored in flat tables. By flat we mean to say ordinary transparent table whereas in a CUBE, it composed of multiple tables arranged in a STAR SCHEMA joined by SIDs. The purpose is to do MULTI-DIMENSIONAL Reporting

Another difference is : In ODS, you can update an existing record given the KEY. In CUBES, theres no such thing. It will accept duplicate records and during reporting, SUM the keyfigures up. Theres no EDIT previous record contents just ADD. With ODS, the procedure is UPDATE IF EXISTING (base from the Table Key) otherwise ADD RECORD.

ODS

Stores line item level detail, more granular Can't create aggregates on ODS ODS are based on flat tables Only two dimensional reporting possible on ODS. Overwrite feature available while loading records

Infocube

- Stores summarized data, less granular.
- Aggregates can be created on top of Infocubes for better performance of Queries.
- Multi-dimensional reporting possible on Infocube.
- Theres no overwrite feature while loading records.

Infocubes are MDM objects that fact table and dimension table are available whereas ODS is not a MDM object there are no fact tables and dimension tables. It consists of flat transparent tables.

In infocubes there are characteristics and keyfigures but in ods key fields and data fields. we can keep non key characteristics in data fields.

Some times we need detailed reports we can get through ODS. ODS are used to store data in a granular form i.e level of detail is more. The data in the infocube is in aggregated form.

From reporting point of view ods is used for operational reporting where as infocubes for multidimensional reporting.

ODS are used to merge data from one or more infosources but infocubes does not have that facility.

The default update type for an ODS object is overwrite for infocube it is addition. ODS are used to implement delta in BW. Data is loaded into the ODS object as new records or updating existing records in change log or overwrite existing records in active data table using 0record mode.

You cannot load data using Idoc transfer method in ODS but u can do in infocube.

You cannot create aggregate on ODS. You cannot create infosets on infocube.

ODS objects can be used.

When you want to use the facility of overwrite. If you want to overwrite nonkey characteristics and key figures. If you want detailed reports you can use ODS.
If you want to merge data from two or more infosources you can use ODS. It allows you to drill down from infocube to ODS through RRI interface.

ODS objects can be used in the following scenarios. ODS is not a mandatory but depending on the requirements we have to use it.

When you want to use the facility of overwrite. If you want to overwrite nonkey characteristics and key figures in the data fields column.

If you want detailed reports, you can use ODS.

If you want to merge data from two or more infosources you can use ODS.

It allows you to drill down from infocube to ODS through RRI interface if u want detailed data from ODS.

If you want to create an external file.

The most important difference between ODS and BW is the existence of key fields in the ODS. In the ODS you can have up to 16 info objects as key fields. Any other info objects will either be added or overwritten! So if you have flat files and want to be able to upload them multiple times you should not load them directly into the info cube, otherwise you need to delete the old request before uploading a new one. There is the disadvantage that if you delete rows in the flat file the rows are not deleted in the ODS.

I also use ODS-Objects to upload control data for update or transfer routines. You can simply do a select on the ODS-Table /BIC/A00 to get the data.

ODS is used as an intermediate storage area of operational data for the data ware house . ODS contains high granular data . ODS are based on flat tables, resulting in simple modeling of ODS . We can cleanse transform merge sort data to build staging tables that can later be used to populate INOFCUBE .

An infocube is a multidimentionsl dat acontainer used as a basis for analysis and reporting processing. The infocube is a fact table and their associated dimension tables in a star schema. It looks like a fact table appears in the middle of the graphic, along with several surrounding dimension tables. The central fact is usually very large, measured in gigabytes. it is the table from which you retrieve the interesting data. the size of the dimension tables amounts to only 1 to 5 percent of hte size of the fact table. Common dimensions are unit & time etc.

There are different type of infocubes in BW, such as basic infocubes, remote infocubes etc.

An ODS is a flat data container used for reporting and data cleansing/quality assurance purpose. They are not based on star schema and are used primaily for detail reporting rather than for dimensional analyais.

An infocube has a fact table, which contains his facts (key figures) and a relation to dimension tables. This means that an infocube exists of more than one table. These tables all relate to each other. This is also called the star scheme, because the dimension tables all relate to the fact table, which is the central point. A dimension is for example the customer dimension, which contains all data that is important for the customer.

An ODS is a flat structure. It is just one table that contains all data. Most of the time you use an ODS for line item data. Then you aggregate this data to an infocube.

ODS holds transactional level data..Its just as a flat table. Its not based on multidimensional model. ODS have three tables 1. Active table 2. change log 3. New table

Cube holds aggregated data which is not as detailed as ODS. Cube is based on multidimensional model. Cube have 2 tables 1. E table 2. F table.