I believe that this is a new function within BW 3.0.. The DataSource now have an option “DataSource transfers double data records” on the “Processing” tab. Then there is a flag to “Ignore double data records”.
Not sure of the workaround for lesser versions of BW.
Two Topics: Automating your InfoCube deletes, and using an ODS to track deltas.
TOPIC 1
——-
If you do not COMPRESS the current fiscal period’s data in your target cube (which eliminates the Request ID,) you can automate the process of deleting the prior request when you reload your data every day. You accomplish this through configuration on the InfoPackage with which you load the data:
A) Go in to InfoPackage maintenance
B) Click the Data Targets tab.
C) For your selected data target, click the icon which looks like the Greek sigma, under the column labeled “Automatic loading of similar/identical requests in the InfoCube.” You can also type DELE in the command field. A popup window entitled “Deleting Request from InfoCube after Update” will appear.
D) On the popup window, you have the option of selecting “Always delete existing requests if delete conditions are found” and “Only for the same selection conditions.” If you select these options, then BW will automatically delete (or reverse if you have aggregates) any prior request for the same selection criteria which has not been compressed.
You can configure your InfoPackage in this manner, and then compress the request when you go to a new fiscal period and no that you will not load any more data for the prior fiscal period.
TOPIC 2
——-
It sounds like you may be reloading every day in order to catch changes to the data. You may know this already, but just in case: You can use an ODS object to capture deltas, and then send only the changes to your cube. This eliminates the need to delete the prior request from your cube, and simplifies the use of cube compression and aggregates.
You then would load the current period’s full set of data into your ODS every day, and the built-in functionality of the ODS object would detect the differences and send only these on to your target cube. You can read more about this scenario in the white paper at service.sap.com/bw entitled “Data Staging Scenarios.”
Finally, using an ODS object as the data staging area eliminates (I think) your issues with the PSA. Instead of having your application read the PSA, have it read from an ODS object instead. Every ODS object has a unique key, so you won’t get duplicate records as you can with a PSA. You can also report on ODS data in BEx queries, if you have any need to, which is yet another advantage this method has over the use of PSA.
We use Cube 0COOM_CO1, which has a time characteristic of fiscal period. We manually load the current period into this cube daily. But to ensure that we do not have duplicate data in the cube we manually delete the previous day’s request before loading the period again. There has to be an easier way to do this, any suggestions on how to automate this process?
Also, the same with the PSA. We have an application reading the PSA, and to avoid duplicate records in the PSA, we are deleting the PSA load before loading it again the next day. Unfortunately with the PSA, the only way to delete a specific request that I can find is to go into the request and mark it as a status of NOT OK, then delete all requests with errors.
You can automate the data deletion process from the InfoPackage. If you look under the “Data Targets” tab of the InfoPackage, there is a Checkbox to “delete the entire content of the data target”. Setting this checkbox will ensure that the data is deleted before the new load.
About the PSA, there is feature by which data can be deleted from the PSA.
Here are the steps for this -
1. Go to the PSA menu under the Admin. Workbench.
2. Navigate to the DataSource for which you want to the set the deletion criteria.
3. Highlight the DataSource and right-click. You will see “Delete PSA Data” in the context menu. Select that.
4. On the next screen, you can now maintain the deletion parameters. The deletion of PSA data can be done based on Date or Days. You can also select successful & error requests for deletion.
5. Create a background job after setting the deletion parameters and schedule it.
Log on to your SAP
Transaction code RSA1—LEAD YOU TO MODELLING
1. Creation of Info Objects
* In left panel select info object
* Create info area
* Create info object catalog ( characteristics & Key figures ) by right clicking the created info area
* Create new characteristics and key figures under respective catalogs according to the project requirement
* Create required info objects and Activate.
2. Creation of Data Source
* In the left panel select data sources
* Create application component(AC)
* Right click AC and create datasource
* Specify data source name, source system, and data type ( master data attributes, text, hierarchies)
* In general tab give short, medium, and long description.
* In extraction tab specify file path, header rows to be ignored, data format(csv) and data separator( , )
* In proposal tab load example data and verify it.
* In field tab you can you can give the technical name of info objects in the template and you not have to map during the transformation the server will automatically map accordingly. If you are not mapping in this field tab you have to manually map during the transformation in Info providers.
* Activate data source and read preview data under preview tab.
* Create info package by right clicking data source and in schedule tab click star to load data to PSA.( make sure to close the flat file during loading )
3. Creation of data targets
* In left panel select info provider
* Select created info area and right click to select Insert Characteristics as info provider
* Select required info object ( Ex : Employee ID)
* Under that info object select attributes
* Right click on attributes and select create transformation.
* In source of transformation , select object type( data source) and specify its name and source system Note: Source system will be a temporary folder or package into which data is getting stored
* Activate created transformation
* Create Data transfer process (DTP) by right clicking the master data attributes
* In extraction tab specify extraction mode ( full)
* In update tab specify error handling ( request green)
* Activate DTP and in execute tab click execute button to load data in data targets.
4. Monitor
Right Click data targets and select manage and in contents tab select contents to view the loaded data. Alternatively monitor icon can be used.
Uploading of Transaction data
Log on to your SAP
Transaction code RSA1—LEAD YOU TO MODELLING
5. Creation of Info Objects
* In left panel select info object
* Create info area
* Create info object catalog ( characteristics & Key figures ) by right clicking the created info area
* Create new characteristics and key figures under respective catalogs according to the project requirement
* Create required info objects and Activate.
6. Creation of Data Source
* In the left panel select data sources
* Create application component(AC)
* Right click AC and create datasource
* Specify data source name, source system, and data type ( Transaction data )
* In general tab give short, medium, and long description.
* In extraction tab specify file path, header rows to be ignored, data format(csv) and data separator( , )
* In proposal tab load example data and verify it.
* In field tab you can you can give the technical name of info objects in the template and you not have to map during the transformation the server will automatically map accordingly. If you are not mapping in this field tab you have to manually map during the transformation in Info providers.
* Activate data source and read preview data under preview tab.
* Create info package by right clicking data source and in schedule tab click star to load data to PSA.( make sure to close the flat file during loading )
7. Creation of data targets
* In left panel select info provider
* Select created info area and right click to create ODS( Data store object ) or Cube.
* Specify name fro the ODS or cube and click create
* From the template window select the required characteristics and key figures and drag and drop it into the DATA FIELD and KEY FIELDS
* Click Activate.
* Right click on ODS or Cube and select create transformation.
* In source of transformation , select object type( data source) and specify its name and source system Note: Source system will be a temporary folder or package into which data is getting stored
* Activate created transformation
* Create Data transfer process (DTP) by right clicking the master data attributes
* In extraction tab specify extraction mode ( full)
* In update tab specify error handling ( request green)
* Activate DTP and in execute tab click execute button to load data in data targets.
8. Monitor
Right Click data targets and select manage and in contents tab select contents to view the loaded data. There are two tables in ODS new table and active table to load data from new table to active table you have to activate after selecting the loaded data . Alternatively monitor icon can be used.
Create info package by right clicking data source and in schedule tab click star to load data to PSA. ( make sure to close the flat file during loading )
* Activate DTP and in execute tab click execute button to load data in data targets.
BW is mainly for Reporting purpose on Organizations Historical Data.
It is a Data Warehousing solution developed by SAP in 1997. Using this we can analyze …..Subject Specific…..Integrated….Non volatile….Time variant Data in support of DECISION MAKING.
BW is offering End-End solution…like From Modeling to Repoting.
We can Extract data from 8 different source systems.
Business content is a big advantage.
Web Application Designer and integration to Portals are some other features of BW.
In ABAP ( R/3) Tables contain current data.
In BW Cubes contain historical Data.
R/3 is designed for Transactional purpose ( OLTP ).
BW is designed for Analysis purpose ( OLAP ).
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.
Validation allows solid data entry regarding special rules. According to previous rules, the system can evaluate an entry and a message can appear on the user’s terminal if a check statement is not met. A validation step contains prerequisite statement and check statement. Both of them are defined using Boolean Logic or calling an ABAP/4 form.
Data Reconcialtion:
Reconcilation is the process of comparing the data after it is transferred to the BW system with the source system. The procedure to do reconcilation is either you can check the data from the SE16 if the data is coming from a particular table only or if the datasource is any std datasource then the data is coming from the many tables in that scenario what I used to do ask the R/3 consultant to report on that particular selections and used to get the data in the excel sheet and then used to reconcile with the data in BW . If you are familiar with the reports of R/3 then you are good to go meaning you need not be dependant on the R/3 consultant ( its better to know which reports to run to check the data ).
How to do Reconciliation?
There are two ways for Reconciliation:
1) Create Basic Cube and load the data from the source system. In the same way create another cube of type Virtual cube. After creating those two cubes, create one multiprovider by using the Basic Cube and Virtual Cube, in the Identification of the Multiprovider select two cube. Then go to reporting create the query and write on formule to compare the values of these two cubes.
2) See the contents of the basic cube which is there is BW. In that screen one Button is there as “SAVE AS”. Click that button and select as “Spread sheet”. Save as .xls. In the Source system side also go to T-Code RSA3, select your data source which you assigned to the basic cube. Click on execute and see the contents.
Now again here also select the “SAVE AS” button and select the spread sheet and save under .xls file. Ok now your two flat file are ready. Now move one file into other by using “move copy”. Now two flat files are in one excel sheet only in different sheets. Now write a formula to compare the values of sheet 1 and sheet 2 in either in sheet1 or sheet2.
Why there is frequent load failures during extractions? and how they are going to analyse them?
If these failures are related to Data,, there might be data inconsistency in source system..though you are handling properly in transfer rules. You can monitor these issues in T-code -> RSMO and PSA (failed records).and update .
If you are talking about whole extraction process, there might be issues of work process scheduling and IDoc transfer to target system from source system. These issues can be re-initiated by canceling that specific data load and ( usually by changing Request color from Yellow – > Red in RSMO).. and restart the extraction.
How To…Create a generic extractor from SAP R/3
What is reconciliation in bw? What the procedure to do reconciliation?
Reconcilation is the process of comparing the data after it is transferred to the BW system with the source system. The procedure to do reconcilation is either you can check the data from the SE16 if the data is coming from a particular table only or if the datasource is any std datasource then the data is coming from the many tables in that scenario what I used to do ask the R/3 consultant to report on that particular selections and used to get the data in the excel sheet and then used to reconcile with the data in BW . If you are familiar with the reports of R/3 then you are good to go meaning you need not be dependant on the R/3 consultant ( its better to know which reports to run to check the data ).
