среда, 25 сентября 2013 г.

SAP BO tips for Interview

Several years ago i fall my interview, after this i took all important definitions and learn them by heart. It was very helpful!

What is the universe?
The business objects universe is the semantic layer that isolates business users from the technical complexities of the databases where their corporate information is stored. For the ease of end user, universes are made up of objects and classes that map to data in the database, using everyday terms that describe their business environment.
Size 1 Mb, max 500 objects.
Does universe store data?
You associate data to universes by mapping to a data source. Data is not stored in the .unv file.
What are the main advantages of a universe?
·       Only the universe designer needs to know how to write SQL and understand the structure of the target database.
·       The interface allows you to create a universe in  an easy-to-use graphical environment.
·       Data is secure. Users can see only the data exposed by the universe. Users can only read data, not edit it.
·       The results are reliable and the universe is relatively easy to maintain.
·       Users can use a simple interface to create reports.
·       All users work with consistent business terminology.
·       Users can analyze data locally.
Describe the connection types and their purposes.
·       Secured: Use this connection to enable your universe to be exported to the Business Objects Enterprise repository.
·       Shared: Use this connection to share your universe across the network with the other designers.
·       Personal: Use this connection if universe is solely for a single user and there is no need to neither share nor publish this universe to the Enterprise system.
What are the types of objects that can be created in a universe? Provide a brief description of each type.
·       Dimensions: These objects relate to the key information being retrieved from the database. The can be date, character, or number type, and may contain a calculation. They are used in queries and condition/filters and can also be used to drill on hierarchies and link between queries.
·       Detail: These objects relate to supportive information and are associated with dimensions. They can be date, character, or number type, and may contain a calculation. They are used in queries and condition/filters but cannot be used to dril on or link on between queries.
·       Measures: These objects are dynamic aggregates and are always a number type, containing functions like Sum, Count, Average, Max, Min. Depending on the other objects in the query, they dynamically aggregate themselves when displayed in the report block.
·       Predefined Condition/ Filter: These objects contain a name, a description and a restriction. They are designed to give the user a choice as to whether to apply a condition filter or not. They can be quite basic, or can contain a complex where clause.
What are the different join types allowed in the universe structure. Provide a short description of each type.
·       Equi: This join works on the principle of the data in the fields that are a joined maych, for example Customer_ID=Customer_ID, therefore returning data from both tables where the data matches.
·       Outer: This join works in the same way as an equi-join, however there may be data in one table but not the other, and the requirements is for the data to be returned regardless. For example, a join between Customer and Sales would be based on Customer_ID, however a customer may still be prospective and not have any records in the sales table, but all customers must be reported on regardless of sales.
·       Complex/Theta: This join works on the Between principle. For example, a sales date may be joined to a begin and also an end date in a table which is date based, for example sales date between begin date and end date.
·       Self-restricting: This join works as a restriction on the table. If there is a field that can be used as a flag on a table, then this join would allow that flag to be referenced every time the table is used in the SQL statement.
·       Self-referencing: A self-referencing join is a join from one column of a table to another column of the same table. This join should not be used as it creates a loop. Ideally,  if this join exists, then the table should be aliased and the appropriate join and cardinality should be assigned.
·       Shortcut: A shortcut join is a join that provides an alternate path between two tables. It improves the performance of a query by not taking into account intermediate tables, and shortening a normally longer join path.
What are the different types of SQL traps that can be found in a schema? How can each of these types be identified? How can each type be resolved in the universe schema?
·       Chasm trap:
Identify – A chasm trap is essentially a many-to-one-to-many relationship and may be seen as a parent table with two child tables, the child tables having the many end of the relationship. If a query is run with a chasm trap, depending on the objects used, either too many or not enough rows (but aggregation may be affected) are produced.
Resolve – Best resolve by using contexts or modify SQL parameters (select for each measure).
·       Fan trap:
Identify – A fan trap is identified as a one-to-many or a one-to-many-to-many relationship. In itself, this isn’t an issue is if there are two fields being used as aggregates and they are at different levels in the path. This can give the same result as a chasm trap.
Resolve – If they can be avoided by using the lowest level of granularity then this is the best practice, however that isn’t always possible. In which case, a combination of aliases and contexts resolves the fan trap and modify SQL parameters (select for each measure).
What are loops and how you would test a measure to make sure the aggregation levels are projected correctly?
Loops are joins between tables that (eventually) come back to the starting point, forming a circle, or loop. Usually the error Incompatible combination of objects alerts you to the fact you may have a loop.
Loops can be resolved by creating aliases and contexts manually. However, depending on the complexity of a universe, this can be a cumbersome task.
Describe how you would test a measure to make sure the aggregation levels are projected correctly.
To test a measure correctly, a minimum of three queries should be created.
1.      The measure on its own.
2.      The measure with two different dimensions, in different queries. Once the table has been projected, remove the dimensions and then add them again using the drag and drop method. This checks the aggregation in the report is correct.
3.      Applying a total to the tables checks (if they are all the same total) the aggregation level from the database.
Explain two drawbacks of using restrictions at the objects level.
1.      Conflicting restrictions in the objects may be confusing if those objects are placed in the query. For example: UK customers, US customers. Effectively you are saying they must be UK and US at the same time, this is a conflict so no data is returned.
2.      Creates a flat level at the end of the class making drilling through the hierarchy difficult.
Describe the use of the @selection function.
The @select function has no benefit to the end user. However, it does have benefit to the designer, as this is how they can reuse code between objects.
@prompt: You can use @prompt to create  an interactive object. Are usefull when you want  to force restriction in the inhered SQL but do not want to present value of the condition. It forces an end user to enter value for a restriction when that object is used in a query.
@where:  they can reuse code between objects.
What is a hierarchy?
A hierarchy is the order of dimensions in a “class” in one-to-many order.
Hierarchies can be default (or natural) and custom and time hierarchis.
(Automatic/Using DB functions/Table based)
Describe what a derived table is and how it is generally used in a universe schema.
A derived table is and how it is generally used:
·       When it is not possible to create a table at the database level, especially if there is a need to link two otherwise un-likeable tables together.
·       To create a table based on a restriction – instead of aliasing tables based on their flag and using self-restricting joints
Describe what index awareness is, and how it is used in a universe schema.
Index awareness is a way of utilizing the indexing in the database to enable queries to be more efficient in the SQL that they generate. It is set up via the object properties.
What are the pros and cons of delegated functionality in a universe-based reporting structure?
Delegated or “smart” measure objects were discussed in the core Universe Design class. The objective is to shift the responsibility for the aggregation to the data source, instead of the universe. This allows for complex and weighted calculations of dimensional subsets, but requires a refresh to the data source in order to display the results in Web Int report.
@Aggregate awareness.
@Aggregate awareness directs query to run against aggregate tables or summary tables, whenever possible.
Using summary table data speed up response time:
·       There are fewer rows to process.
·       Value calculation is not required.
·       Fewer, if any, joints are required.
Aggregate Navigation – tool you use in Univ Designer to set incompatibilities objects and tables in the structure of the universe.
 In BO, universe security can be managed at these levels:
·       Connection credentials and data store.
·       Central Management Server.
·       Universe (restrictions – objects, rows access, query, SQL generation controls and connection controls).
Restriction sets:
·       Connection – you can select an alternative connection for the universe.
·       Query controls – options to limit the size of the result set and query execution time.
·       SQL generation options – options to control the SQL generated for queries.
·       Objects access – you can apply column-level security.
·       Row access – you can define a WHERE clause that restricts access to row and limits the result set returned by a query.
·       Alternative table access – you can replace a table referenced in the universe by another table in database.
Forced restrictions – clause where
Optional restriction – predefine condition
Objects restrictions problem:  Multiple Objects, Hierarchy Problems, Name confusions, Restr conflicts.
The CMS is responsible for authenticating users and groups, and keeping track of the availability of the other BO Enterprise services. It also maintains the BOE system database, which includes information about users, groups, security levels, BOE content, and services. The CMS also maintains a separate audit database of information about user actions and managers the BO repository.
CUID - Component Unique ID
BIAR – Business Intelligence Archive Resource file