SAP BO Interview Questions and Answers for Freshers

Question.1 What is the role of surrogate keys in the data warehouse and how will you generate them?

Answer: A surrogate key is a simple Primary key which maps one to one with a Natural compound Primary key. The reason for using them is to alleviate the need for the query writer to know the full compound key and also to speed query processing by removing the need for the RDBMS to process the full compound key when considering a join.

For example, a shipment could have a natural key of ORDER + ITEM + SHIPMENT_SEQ. By giving it a unique SHIPMENT.ID, subordinate tables can access it with a single attribute, rather than 3. However, it’s important to create a unique index on the natural key as well.

Question.2  What is data cleaning? how is it done?

Answer:      

•  We can simply say it as Purifying the data.
• The act of detecting and removing and/or correcting a database’s dirty data (i.e., data that is incorrect, out-of-date, redundant, incomplete, or formatted incorrectly).
• Data cleansing is nothing but standardizing and reformatting (encoding, decoding, data type conversion) the data before we store the data in the warehouse.

Question.3 What is junk dimension and Degenerate Dimension?

Answer: Junk dimension is a collection of random transactional codes, flags and text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides the convenient place to store the junk attributes. Whereas degenerate dimension is data that is dimensional in nature but stored in a fact table.

Question.4  What is junk dimension?

Answer:

Junk dimension: It is a collection of random transactional codes, flags and text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides the convenient place to store the junk attributes. Whereas degenerate dimension is data that is dimensional in nature but stored in a fact table.

The column which we are using rarely or not used, these columns are formed a dimension is called junk dimension. A junk dimension is a convenient grouping of flags and indicators. It’s helpful, but not absolutely required, if there’s a positive correlation between the values.

Question.5  What is Degenerate Dimension?

Answer:

Degenerative dimension: The column which we use in dimension are a degenerative dimension.

Example: emp table has empno. ename, sal, job,deptno but we are talking only the column empno,ename from the emp table and forming a dimension this is called degenerative dimension

Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub-dimension.Their benefits:

• Provide a recognizable, user-intuitive location for related codes, indicators and their descriptors in a dimensional framework
• Clean up a cluttered design that already has too many dimensions. There might be five or more indicators that could be collapsed into a single 4-byte integer surrogate key in the fact table
• Provide a smaller, quicker point of entry for queries compared to a performance from constraining directly on these attributes in the fact table. If your database supports bit­mapped indices, this potential benefit may be irrelevant although the others are still valid.

Question.6 What is LOOKUP table?

Answer: When a table is used to check for some data for its presence prior to loading of some other data or the same data to another table, the table is called a LOOKUP Table.

• When a value for the column in the target table is looked up from another table apart from the source tables, that table is called the lookup table.
• when we want to get related value from some other table based on particular value. Suppose in one table A we have two columns emp_id,name and in other table B we have emp_id address in target table we want to have emp_id,name,address we will take source as table A and look <sip table as B by matching EMp_id we will get the result as three columns…emp_id,name,address
• A lookup table is nothing but a ‘lookup’ it gives values to referenced table (it is a reference), it is used at the run time, it saves joins and space in terms of transformations. Example, a lookup table called states, provide actual state name (‘Texas’) in place of TX to the output.
• The Look-Up table provides the detailed information about the attributes. For example, the lookup table for the quarter attribute would include a list of all the quarters available in the data warehouse.
• If the data is not available in the source systems then we have to get the data by some reference tables which are present in the database. These tables are called lookup tables.

Question.7  The difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better?

Answer: Star schema and snowflake both serve the purpose of dimensional modeling when it come to data warehouses. star schema is a dimensional model with a fact table (large) and a set of dimension tables (small). The whole set-up is totally de-normalized. However in cases where the dimension table are split into many tables that are where the schema is slightly inclined towards normalization ( reduce redundancy and dependency) there comes the snow flake schema.

The nature/purpose of the data that is to be feed to the model is the key to your question as to which is better.

b) Star schema contains the dimension tables mapped around one or more fact tables. It is a de-normalized model. No need to use complicated joins. Queries results fast. A snowflake schema is the normalized form of Star schema.

It contains in-depth joins because the tables are split into many pieces. We can easily do modification directly in the tables. We have to use complicated joins since we have more tables. There will be some delay in processing the Query.

C) Star Schema means A centralized fact table and surrounded by different dimensions Snowflake means In the same star schema dimensions split into another dimensions Star Schema contains Highly De-normalized Data Snow flake contains Partially normalized Star cannot have parent table But snow flake contain parent tables.

Question.8  Why need to go there Star?

Answer :

•       less joiners contains
•       simply database
•       support drilling up options

Question.9  Why need to go Snowflake schema and Star schema?

Answer: Here sometimes we used to provide separate dimensions from existing dimensions that time we will go to snowflake.

Disadvantage Of snowflake:

• Query performance is very low because more joiners are there.
• Star schema and snowflake both serve the purpose of dimensional modeling when it come to data warehouse.
• Star schema is a dimensional model with a fact table (large) and a set of dimension tables (small).
• The whole set-up is totally de-normalized, however in cases where the dimension table are split into many tables that is where the schema is slightly inclined towards normalization (reduce redundancy and dependency) there comes the snow flake schema.
• The nature/purpose of the data that is to be feed to the model is the key.
• Both represent the dimensional model, in case of star schema the dimensions does not split. Where as in the case of snowflake you can see the further split in dimension.

For eg: if you are using more than one telephone at your desk and it is available to more than one and at the same time the telephone gives the facility of usage more than one member then, in this case, we need the further split in the table, because we need in-depth analysis.

Question.10  What is ods?

Answer:

• ODS stands for Online Data Storage.
• It is used to maintain, store the current and up to date information and the transactions regarding the source databases taken from the OLTP system.
• It is directly connected to the source database systems instead of to the staging area.
• It is further connected to a data warehouse and moreover can be treated as a part of the data warehouse database.
• It is the final integration point in the ETL process before loading the data into the Data Warehouse.
• It contains near real-time data. In typical data warehouse architecture, sometimes ODS is used for analytical reporting as well as the source for Data Warehouse.

Operational Data Services is the Hybrid structure that has some aspects of a data warehouse and other aspects of an Operational system.

• Contains integrated data.
• It can support DSS processing.
•  It can also support High transaction processing.
• Placed in between Warehouse and Web to support web users.

The form that data warehouse takes in the operational environment.

Operational data stores can be updated, do provide rapid constant time, and contain only limited amount of historical data

An Operational Data Store presents a consistent picture of the current data stored and managed by transaction processing system. As data is modified in the source system, a copy of the changed data is moved into the ODS. Existing data in the ODS is updated to reflect the current status of the source system

It is used to store current data through transactional web applications, sap, MQ series

Current data means particular data from one date into one date.

An Operational Data Store is a collection of data in support of an organization’s need for up to operational, integrated, collective information. ODS is purely operational construct to address the operational needs of a corporation. While loading data from Staging to ODS we do the process of data scrubbing, data validation.

Question.11  What is SCD1 , SCD2 , SCD3?

Answer:

–>SCD 1: Complete overwrite SCD 2: Preserve all history. Add row SCD 3: Preserve some history. Add an additional column for ol/new.

->SCD Type 1, the attribute value is overwritten with the new value, obliterating the historical attribute values. For example, when the product roll-up changes for a given product, the roll-up attribute is merely updated with the current value.

SCD Type 2, a new record with the new attributes is added to the dimension table. Historical fact table rows continue to reference the old dimension key with the old roll-up attribute; going forward, the fact table rows will reference the new surrogate key with the new roll-up thereby perfectly partitioning history.

SCD Type 3, attributes are added to the dimension table to support two simultaneous roll-ups – perhaps the current product roll-up as well as “current version minus one”, or current version and original.

->SCD: The value of dimensions is used change very rarely, That is called Slowly Changing dimensions.

Here mainly 3

SCDl: Replace the old values overwrite by new values.

SCD2: Just Creating Additional records.

SCD3: It maintains just previous and recent In the SCD2 are of 3 types

1.      Versioning.

2.      Flagvalue

3.      Effective Date range

Versioning: Here the updated dimensions inserted into the target along with version number

The new dimensions will be inserted into the target along with Primary key

Flagvalue: The updated dimensions insert into the target along with 0

and new dimensions inset into the target along with 1

->SCD1, SCD2 and SCD3 can be also

Type I, Type II, Type III Dimensions:

Type I-Changed attribute overwrites the existing one. eg: If the income of customer changes from 4000 to 5000 it will simply replace 000 by 5000.

Type II Dimension- For the changed attribute a new record is created, eg: If the income of customer is changed from 4000 to 5000, then a new record is created with income 5000 and the previous one will remain as it is. This will help us to record the history of data.

Type III Dimension-Here a new column will be added to capture the change, eg: If the income of customer increases from 4000 to 5000, then a new column will be added to the existing row titled “new income”. So in that record 2 cols will be there “income” and “new income”.

Question.12  What is the Difference between OLTP and OLAP?

Answer: 

• OLAP: Current and historical data Long database transactions Batch update/insert/delete De-normalization is promoted Low volume transactions Transaction recovery is not necessary. But ->OLAP(Online Analytical Programming) contains the history of OLTP data, which is, non-volatile, acts as a Decisions Support System and is used for creating forecasting reports.

->In OLap’s Data cannot be insert, update, and Delete. Follows Dimensional Modeling.

• OLTP: It is nothing but Online Transaction Processing , which contains normalized tables and online data, which have frequent insert/updates/delete.

->In Oltp’s, Data Can be insert, update and Delete. Follows ER Modeling

Question.13  What is a bo repository?

Answer: Repository means set of database tables, Business object store security information e.g user, group, access permission, user type etc. , universe information e.g. objects, classes, table name, column name, relationship etc.

BO repository is an set of db tables which holds the metadata information. This bo repository is divided in to 3 domains Security, universe, document domain. This metadata holds all the information related to users groups their privileges except the “supervisor” this is the super user of bo rep. The supervisor info is stored BOMain. key which is stored in default home dir of bo. Generally, Repository is the Metadata

BO 5.0 Repository create/maintain 50 tables, they are distributed as follows

25 tables for Security domain

Table for Universe domain 1 Table for Document Domain in 6.5.1 repository tables in sec. domain 1 table in doc domain 31 tables in uni domain it is a metadata

In BOXI R2, there are no separate domains like in BO 5x and 6x.

There is a single repository which stores the entire information about the users , objects, repotrs etc.

Bottomline: There are NO separate domains for Security, Document, and Universe like in classic BO.

In BO Xi R2 here also, we use the same domains.

At the time of repository creation 8 tables will be generated.

Question.14  What is BO 6.5 Architecture or explain about the tires?

Answer: BO xi r2 follows three tier architects.

1) Client (Dl/Web I Crystal reports)

2) Application tier (Java / .net)

3) Database tier

Example Country Name is like India/UK or ALL In parameter I selected India or UK it should show only particular country details in BO. If I select ALL in parameter. It should show both country data in different tables.

Use matches pattern option to solve your problem, in matches pattern ( select country name or % for all), now if u select particular country name then corresponding data is displayed else use % to show all data for country prompt.

Question.15  What are the documentations you can create ?

Answer: We create many docs while creating the reports/ while designing the universe. We create LDD (low level design document) based on the HDD while designing/developing phase, and also, UAT/Test plan/Regression test plan docs also should be prepare before sending the report/universe to the customer.

Question.16  What are the errors you have faced in creating reports in business objects and explain some?

Answer: While creating the Reports mainly we face syntax/Computation/multivalve/Objects are not compatible related errors.

Question.17  What we need for business objects and what we need to Study?

Answer: Business Objects he need to have the skills mentioned below

1.       Oralce (sql)

2.       Datawarehouse Concepts

3.       Business Objects tools :

4.       Designer

5.       Desktop Intelligence

6.       Web Intelligence

7.       CMC

8.       Migration

9.       Report conversition tools

10.   Infoview

All these Business Object tools are the products of the Business Objects which come with the product.

Question.18  What is thin client And thick client..?

Answer:  In BO, the Thin client is Web intelligence, where you just need the server path or the URL to access reports, whereas Thick Client is Desktop Intelligence, where you need to install BO Full Client on your PC to access/create reports.

Thin Client means: exam – the user can access the detail form database directly it is call as thin client

Thick Client means: exam the user can access the details from database thru third layer 10.

Question.19  How to check for a particular value from one column with the list of values present in another column in Webi reports?

Answer:  Easy one, save the webi report to PDF and search for the value in PDF. If it is duplicate it will show otherwise not.