ETL Questions Part-IV

151. What are data-marts?

· Data Marts are used to exchange data between different BW systems or to update data within the same BW system (Myself Data Mart). Here, the InfoProviders that are used to provide data are called Data Marts.

152. Which one is more normalized; ODS or InfoCube?

· InfoCube is more normalized than ODS.

153. What is replication of DataSource?

· Replication of DataSource enables the extract structure from the source system to be replicated in the BW.

154. What are the quality checks for inefficient InfoCube designs?

· Huge Dimension tables make an InfoCube inefficient.

· The query takes a long time.

155. Why is star schema not implemented for ODS as well?

· Because ODS is meant to store a detailed document for quick use and help make short-term decisions.

156. Why do we need separate update rules for characteristics on each key figure?

· If the requirement specifies a different need for each characteristic then we have separate update rules for each of the characteristics.

157. What is the use of Hierarchies?

· Efficient reporting is one of the targets of using hierarchies. Easy drilldown paths can be built using hierarchies.

158. What is "Referential Integrity"?

· A feature provided by relational database management systems (RDBMS) that prevents users or applications from entering inconsistent data. For example, suppose Table B has a foreign key that points to a field in Table A.

o Referential integrity would prevent from adding a record to Table B that cannot be linked to Table A.

o Referential integrity rules might also specify that whenever you delete a record from Table A, any records in Table B that are linked to the deleted record will also be deleted. This is called cascading delete.

o Referential integrity rules could specify that whenever you modify the value of a linked field in Table A, all records in Table B that are linked to it will also be modified accordingly. This is called cascading update.

159. What is a Transactional InfoCube and when is it preferred?

· Transactional InfoCubes differ from Basic InfoCubes in their ability to support parallel write accesses. Basic InfoCubes are technically optimized for read accesses to the detriment of write accesses. Transactional InfoCubes are designed to meet the demands of SEM, where multiple users write simultaneously into an InfoCube.

160. When is data in Change Log table of ODS deleted?

· When requests loaded into ODS object are neither required for delta update nor for initialization, they can be deleted. If delta initialization for update exists in connected data targets, the requests have to be updated first before the data can be deleted.

161. How is the data of different modules stored in R/3?

· Data is stored in multiple tables in R/3 based on ERM (Entity Relationship model) to prevent the redundant storage of data.

162. In what cases do we transfer data from one InfoCube to another?

· Modifications can't be made to an InfoCube if there is data present in the InfoCube. If we want to modify an InfoCube and no backup for data exist then we can design another InfoCube with the parameters specified and load data from the old InfoCube.

163. How often do we have a Multi-layered structure in ODS stage and in what cases.

· Multi-layered structure in ODS stage is used to consolidate data from different DataSources.

164. How is data extracted from systems other than R/3 and Flat files?

· Data is extracted from systems other than R/3 and flat files using staging BAPIs.

165. When do tRFC and IDoc errors occur?

· tRFC and iDoc errors- when you load data , these are connection specific and if the source system is not set properly or is interrupted , you get these errors.

· Intermediate Document (IDoc) is a container for exchanging data between R/3, R/2 and non-SAP systems. IDocs are sent in the communication layer by transactional Remote Function Call (tRFC) or by other file interfaces (for example, EDI). tRFC guarantees that the data is transferred once only. Was not able to find out when the errors occur.

166. On what factors does the loading time depend on?

· Loading time depends on the work load both on the BW side and source system side. It might also depend upon the network connectivity.

167. How long does it take to load a million records into an InfoCube from an R/3 system?

· Depending on work load on BW side and source system side loading time varies. Typically it takes half an hour to load a million records.

168. Will the loading time be same for the same amount of data for non-SAP systems like Flat files?

· It might not be the same. It depends on the extraction programs used on the source system side.

169. What is mySAP.com?

· SAP solution to integrate all relevant business processes on the Internet. mySAP.com integrates business processes in SAP and non-SAP systems seamlessly and provides a complete business environment for electronic commerce.

170. How was Data modeling done in your project? Explain

· Initially we study the business process of client, like what kind of data is flowing in the system, the volume, changes taking place in it, the analysis done on the data by users, what are they expecting in the future, how can we use the BW functionality. Later we have meetings with business analyst and propose the data model, based on the client. Later we give a proof of concept demo wherein we demo how we are going to build a BW data warehouse for their system. Once we get an approval we start requirement gatherings and building your model and testing follows in QA.

171. As you said you have worked on InfoCubes and ODS, Which one is better suited for reporting? Explain and what are the drawbacks and benefits of each one?

· Depending on the type of report the data is stored in InfoCube or ODS. BW is used to store high volumes of data and faster reporting. InfoCube is used to store normalized data. Master Data and transaction data are stored in InfoCube as per the Extended Star Schema using SIDs. The reporting is fast.

· ODS stores data in more detail utilizing its structure of transparent tables. Reporting on this will be slow. ODS is better used for RRI.

172. How do you measure the size of InfoCube?

· In number of records

173. What is the difference between InfoCube and ODS?

· InfoCube is structured as per Extended Star Schema with the fact table surrounded by different dimension tables which connect to SIDs. And the data can be aggregated in the InfoCubes. ODS is a flat structure and does not use the star schema concept and has detailed data in transparent tables.

174. What is the difference between display attributes and navigational attributes?

· Display attribute is one which is used only for display purpose in the report. Where as navigational attribute is used for drilling down in the report. We don't need to maintain NAV attribute in the InfoCube as a characteristic (that is the advantage) to drill down.

175. Data is uploaded twice into InfoCube. How to correct it?

· You can delete it by the Request ID.

176. Can you add a new field at the ODS level?

· Yes.

177. Can many DataSources have one InfoSource?

· Yes. For example, for loading text and hierarchies we use different DataSources but the same InfoSource.

178. Apart from R/3, which legacy db you used for extraction?

· Access, Informatica

179. There were problems with delta loads. The DataSources and transfer rules were re-activated and transported from both DEV systems to the Production systems. When the jobs are scheduled, there is an error that the delta update to the InfoCube has been invalidated because a previous delta load has been deleted from the InfoCube. All data from the ODS objects and the InfoCube then ran the initialization of delta job to restart the process has already been deeted. The load to the ODS objects completes successfully but the subsequent load to the InfoCube from the two ODS objects fails with this error.

· Prior to deleting all the data did you run the last deltas from the delta collective runs into the InfoCube? It sounds as if you may have left the delta data in the queue and re-initialized the delta process. Thus when you go to load the delta data it doesn't want to load b/c it is looking for it’s predecessor delta that you deleted.

180. I had initialized an LIS structure for billing and when I ran the delta request it is giving me an error stating that this is a duplicate document and my user wants the report urgently.

· What I did was,

o Deleted setup data (LBWG in R3)

o Deleted delta queue (RSA7 in R3)

o Generated new queue (for 2LIS_13_VDITM it is OLI9BW transaction)

181. I have a requirement in one of the existing InfoCube to change the Attribute as Navigational. I am sure that I have to check the Navigational option in the Attributes tab of respective characteristic and I have to check the option in the InfoCube also. My concern is, this InfoCube already has millions of records in production, I wonder is there any way to realign it with out reloading the InfoCube. How about realigning master data after making those changes... Is it mandatory to reload the InfoCube or any work around is available.

· All you need to do after selecting and activating the object and InfoCube is run a Hierarchy attribute change run. That should make the Nav attr. visible for reporting. In my first project I created an FI InfoCube with the company info. customized the FI AR InfoCube and created a MultiProvider for the users to drill down on customer info and ledger accounts. I also customized the sales overview for transactional data

182. When Extracting Sales Data using V3 Collective Run using LBWE job control, no data is being extracted (Nothing is being shown in RSA3). When filling up Setup Table for OLD Documents, we are able to see the Extracted Data records in RSA3. What may be wrong? And what should be the actual procedure to see data consistently in RSA3 so that BW can pull records from R/3.

· In RSA7 on the source system do you see your DataSources under queue maintenance and are they green? Also, did you run OLI7BW to setup your data in the statistical queue for initialization? And finally did you run your init from BW to initialize the delta process and get the initial load into BW? If you have done all these things you should be collecting deltas in the delta queue under RSA7.

183. Where the PSA data is stored?

· In PSA table.

184. What is data size?

· The volume of data one data target holds(in no. of records)

185. Different types of InfoCubes.

· BasicCube,

· Virtual Cube

o RemoteCube

o SAP RemoteCube

o Virtual InfoCube with services

186. What is an InfoSet?

· InfoSet is an intersection of multiple InfoProviders. They can be made of ODS and InfoObjects only.

187. If there are two DataSources how many transfer structures are there?

· Two in R/3 and Two in BW

188. What are indexes?

· Indexes are database indexes, which help in retrieving data fast.

189. Is it necessary to initialize each time the delta update is used?

· No

190. After the data extraction what is the image position?

· After image

191. What are Authorizations?

· Profile generators.

192. Can a Characteristic and InfoObject be InfoProvider?

· Yes

193. What is data Integrity and how can we achieve this?

· Data Integrity is about eliminating duplicate entries in the database and achieve normalization.

194. What is index maintenance and what is its purpose?

· Indexing is a process by which the address of data is stored. It helps easier access to the data.

195. When and why use InfoCube compression?

· When the data in the InfoCube is not going to change ever and if it is occupying a lot of space then we compress the InfoCube. The data that is compressed cannot be altered or deleted except through selective deletion, hence we have to make an informed decision on compression. This compression can be done through process chain and also manually.

196. How can Business Content be enhanced? And why do we need to enhance the Business Content?

· We can enhance the Business Content by adding fields to the Extract Structures delivered by SAP BC. We may need to enhance the BC because we need to provide fields which are not already in the BC as per the customers needs. Eg: you have a customer InfoCube (in BC) but your company uses attribute for say Apt number. Then instead of constructing the whole InfoCube you can add the above field to the existing BC InfoCube.

197. What is Tuning and why do we do it?

· Tuning is done to increase efficiency. It is done to lower time for:

o Loading data into Data Target

o Accessing a query

o For drilling down in a query, etc

198. What is a MultiProvider and how do we use MultiProvider?

· MultiProvider can combine various InfoProviders for reporting purposes. We can extract data from an ODS, InfoCube, InfoSet, InfoObject, etc in any combination.

199. What are scheduled and monitored data loads?

· Scheduling of data load means to schedule the loading of data for some particular date and time. It can be done from the scheduler tab on Create InfoPackage. Data Loads are monitored using transaction RSMON.

PERFORMANCE TUNING

200. What are the data load tuning one can do?

· Load balance on different servers

· Indexes on source tables

· Use fixed length files if data is loaded from flat files and put the file on the application server

· Use content (SAP delivered) extractor as much as possible

· Use “PSA and Data target in parallel” option in the InfoPackage

· Start several InfoPackages parallel with different selection options

· Buffer the SID number ranges when lot of data is loaded at once

· Load Master Data before loading Transaction Data

· Watch out the ABAP code in transfer and Update rules – This might slow performance

201. What are the general tuning guidelines?

· Archive and delete old data

· Use line item dimensions for large dimensions

· Use MultiProviders to parallel query on the basic InfoCubes

· Use the BW statistics InfoCube to monitor performance

· Reporting authorizations slow the performance

· Web reporting is faster than BEx reporting

· Use the aggregate hierarchies to minimize the roll-up time

· Use parallel upload and activation of ODS objects

· Disable the BEx reporting flag if ODS is not used for reporting

202. Ways to improve the performance

· Define as many dimensions as possible.

· Create aggregates

· Check and define Line Item Dimension

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.