ETL Questions Part-III

101. What are the places we use ABAP code in BW?

· Start routine

· Update routine

· InfoPackages (to populate selection parameters )

· Transfer Rules

· Variable Exits

· To create any generic DataSources

102. Sales flow

· Quotation à inquiry à Sales order à Delivery à Post goods issue à Invoice à Accounting document

103. What is delta queue (RSA7)? When will the data queue here and from where?

· Delta queue stores records that have been generated after last delta upload and yet to be sent to BW. The queued data will be sent to BW from here. Depending on the method selected, generated records will either come directly to this queue or through extraction queue.

104. What is Extraction Queue? What does it contain?

a. Newly generated records will be stored in the extraction queue and from there a scheduled job will push it to delta queue.

105. What are Serialized and Unserialized V3 updates?

· In serialized V3 Update data is transferred from the LIS communication structure, using extract structures (e.g. MC02M_0HDR for the header purchase documents), into a central delta management area.

· With Unserialized V3 Update mode, the extraction data continues to be written to the update tables using a V3 update module and then is read and processed by a collective update run (through LBWE).

106. 5 different types of Source Systems are:

· SAP Systems – SAP Basis Release 3.11 and above – SAPI

· DataBases – Use SAP DB Connect

· External Systems – BAPI

· File Systems

· SAP BW

107. 4 different types of DataSources:

· Transaction Data

· Attributes

· Texts

· Hierarchy

108. 2 types of InfoSources:

· Direct InfoSource

· Flexible InfoSource

109. 3 types of Transfer Rules:

· The fields copied from the Transfer Structure and are not modified

· Fixed Value can be assigned to an InfoObject

· An ABAP routine or a form field determines the value of the InfoObject

110. 6 types of connections between the Source Systems and the BW:

· RFC Connections

· ALE Settings

· Partner Agreements

· Ports

· IDoc Types

· IDoc Segments

111. What is Transfer Method and what are the types of Transfer Methods?

· The transfer method only determines how the data is transferred.

o IDoc transfer method: A data IDoc consists of a control record, a data record, and a status record. The control record contains administration information such as receiver, sender, and client. The status record describes the status of the IDoc, for example "modified". The data stores in the ALE inbox and outbox have to be emptied or reorganized.

o PSA (tRFC) transfer method: With this transfer method, a transactional Remote Function Call is used to transfer the data directly from the source system to the SAP BW. Here, there is the option of storing the data in the PSA (the tables have the same structure as the transfer structure.) This is the preferred transfer method, because it improves performance better than the IDoc method. When you use tRFCs to transfer data, the maximum number of fields that can be used is restricted to 255. The length of a data record is also restricted to 1962 bytes when you use tRFCs (IDoc --> 1000 bytes.)

112. 6 control Parameters for Transferring Data: (SBIW à General settings)

· Source System

· Maximum size of the DataPackage

· Maximum number of rows in a DataPackage

· Frequency

· Maximum number of parallel processes during the transfer of data

· Target system for batch job

113. 3 uses of SAPI technology:

· Transfer data and Metadata from SAP Systems

· Transfer data from XML files

· Transfer data between BW data targets or from one BW system to another (Data Marts)

114. 4 functions of LO Cockpit:

· Maintaining Extract Structures

· Maintaining DataSources

· Activating Updates

· Controlling Updates

115. 3 update Methods for InfoPackage:

· Full Update

· Initialize Delta

· Delta Update

116. 3 update Methods in Logistics Extraction:

· Direct Delta

· Queued Delta

· Unserialized V3 Update

117. 4 advantages of LO Extraction:

· Improved performance and reduced volumes of data

· Simple handling

· Standardized solution for all Logistics applications

· No use of LIS functions

118. DataSource: Customer version edit:

· Field Name

· Short Text

· Selection

· Hide Field

· Inversion

· Field only known in Customer Exit

119. 8 Delta Terms:

a. Service API: Layer in the source system that sends requests and starts extractors.

a. Delta Management: A group of programs for the delta queue that control the transaction data delta.

b. Update Mode (init, full, delta, and so on): A term that describes which data is requested.

c. Delta Queue: A holding area for new and modified (delta) records in the SAP system.

d. Delta Types: A term that describes how the data gets into the delta queue.

e. Serialization: The sequence in which the data records arrive in BW.

f. Record Mode: A description of the contents of a record.

g. Delta Method: A term that classifies a DataSource according to record mode, serialization, and delta type.

120. 5 update modes:

· Update mode “F” = Full Update: Available for all DataSources

· Update mode “C” = Initialization Delta: If the extractor supports deltas, it must be initialized prior to a delta run. Selection conditions are saved. Finally, a full upload is started for the selected range of data. Additional settings are saved to allow for future delta uploads.

· Update mode “D” = Delta: If the extractor supports deltas, only new or changed records are sent.

· Update mode “R” = Repeat: If a delta is showing a RED traffic light status then a dialog box prompts to decide whether or not the last group of delta records needs to be reloaded.

· Update Mode “A” = Master Data. Master data does not however use the delta queue functions

121. 3 ways to load a delta queue:

· At the time of the transaction – Direct Delta

· At a later date after the transaction (V3 job) – Queued Delta

· At the time the extractor job is called by BW – Unserialized V3 Update

122. 6 types of Record Mode: 0RECORDMODE is an InfoObject that specifies the method in which delta information is supplied.

· “After Image” = Record Mode “ ” - The way a record looks after the change

· “Before Image” = Record Mode “X” - The way a record looked before the change

· “Additive Image” = Record Mode “A” - Shows only the difference for all numeric values

· “New Image” = Record Mode “N” - For each change, a new, unique record is generated

· “Delete” = Record Mode “D” - Only provides the key information required to make a deletion

· “Reverse” = Record Mode = “R” - Sends information to numerically “cancel” a deleted record.

123. 2 points where Currency Translation can take place:

· When data is updated in the InfoProvider, currency can be determined for the Key Figure

· When analyzing data in the BEx, we can determine the currency conversion key and target currency for each structure part separately.

124. There is one product of color black and one is pink. The color properties should be displayed when the query is run but it is not displaying. What might be the problem?

· Check if the colors have been checked as navigational attribute of material.

· Check if master data for the same is maintained and extracted in BW.

125. What is the importance of Compounding of InfoObjects?

· This defines a superior InfoObject which must be combined to define another InfoObject and it makes the superior InfoObject uniquely identifiable. For example, in a Plant, there can be some similar products manufactured. (Plant A-- Soap, Paste, Lotion; plant B--Soap, paste, Lotion) In this case Plant A and Plant B should be made unique. So the characteristics can be compounded to make them unique.

126. What is delta upload? What is the use of delta upload?

· When transactional data is pulled from R3 system instead of pulling all the data daily (Instead of having full load), if we pull only the changed records, or newly added records, the load on the system will be very less.

127. What is SID? What is the impact in using SID?

· SIDs are Surrogate IDs which are system generated numbers and assigned to each characteristic value when they are uploaded. Search on Numeric character is faster than Alpha characters and hence SIDs.

128. What are the three tables of ODS Objects? Explain?

· ODS has three database tables. New Table, Active Table and Change Log Table. Initially new data are loaded and their traces are kept in Change log table. When another set of data comes, it actually compares with change log and transfers the data (delta data) into active table and also notes in Change log. Every time the tables are compared and data is written into the targets.

129. Other than BW, what are the other ETL tools used for SAP R/3 in industry?

· Informatica, ACTA, COGNOS, Business Objects are other ETL tools.

130. Does any other ERP software use BW for data warehousing?

· No.

131. What is the importance of hierarchies?

· One can display the elements of characteristics in hierarchy form and evaluate query data for the individual hierarchy levels in the BEx (in Web applications or in the BEx Analyzer).

132. What are hierarchies? Explain how you used in your project?

· Hierarchies are organizing data in a structured way. For example BOM (Bill of material) can be configured as hierarchies.

133. Where is 0RECORDMODE InfoObject used?

· It is an InfoObject which specifies the method in which the delta information is supplied. ODS uses 0RECORDMODE for delta load. 0RECORDMODE can have any of the 6 values as “ ”, “X”, “A”, “N”, “D” & “R”.

134. Are all the characteristics - key fields in an ODS?

· No. An ODS object contains key fields (for example, document number/item) and data fields that can also contain character fields (for example, order status, customer).

135. What is the use BAPI, ALE?

· BAPI & ALE are programs to extract data from DataSources. BW connects SAP systems (R/3 or BW) and flat files via ALE. BW connects with non SAP systems via BAPI.

136. Where to check the log for warning messages appearing in activation of transfer rules?

· If transfer rules are not defined for InfoObjects, then traffic lights will not be green.

137. Can we load transaction data into InfoCube without loading the master data first?

· Yes.

138. What is difference between saving and activating?

· In BIW, Saving à actually saves the defined structure and retrieves whenever required.

· Activating à It saves and generates required tables and structures.

139. What is time dependent master data?

· Time dependant master data are one which keeps changing according to time. For example: Assume a Scenario, Sales person A works in East Zone till (Jan 30th 2004), and then moves to North Zone from Jan31st 2004. Thus the master data with regard to Sales person A, should be changed to different zone based on a time

140. What does delta initialization do?

· It initializes the delta Update mechanism for that DataSource.

141. What is difference between delta and pseudo delta?

· Some data targets and modules have delta Update feature. Those can be used for delta Update of data. Say ODS, InfoCube, COPA are delta capable. Data can be expected stage wise. After first accumulation of data, BIW expects the data in delta for these data target. When a data target does not have this feature (delta update), it can be made delta capable using ODS as data target.

142. What is Third Normal Form and its comparison with Star Schema?

· Third normal form is normalized form of storing data in a relational database. It eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on only the key.

· Star schema is a demoralized form of storing data, which paves the path for storing data in a multi-dimensional model.

143. What is Life period of data in Change Log of an ODS?

· The data of Change Log can be scheduled to be deleted periodically. Usually the Data is removed after it has been updated into the data targets.

144. What are Inbound and Consistent ODSs?

· In an Inbound ODS object, the data is saved in the same form as it was when delivered from the source system. This ODS type can be used to report the original data as it comes from the source system.

· In a Consistent ODS object, data is stored in granular form and consolidated. This consolidated data on a document level creates the basis for further processing in BW.

145. What is Life period of data in PSA?

· Data in PSA is deleted when one feels that there is no use of it in future. There is a trade off between wastage of space and use as a back up.

146. How to load data from one InfoCube to another?

· A DataSource is created from the InfoCube which is supposed to feed. This can be done by right-clicking on the InfoCube and selecting export DataSource. Then a suitable InfoSource can be created for this DataSource. And the intended data target InfoCube can be fed.

147. What is activation of objects?

· Activation of objects enables them to be executed, in other words used elsewhere for different purposes. Unless an object is activated it cannot be used.

148. What is transactional ODS?

· A transactional ODS object differs from a standard ODS object in the way it prepares data. In a standard ODS object, data is stored in different versions (active, delta, modified); whereas a transactional ODS object contains the data in a single version. Therefore, data is stored in precisely the same form in which it was written to the transactional ODS object by the application.

149. Are SIDs static or dynamic?

· SIDs are static.

150. Is data in InfoCube editable?

· No.