1. What are the differences between Personal, Shared and Secured connections?
- A Personal connection is created by one user and cannot be used by other users. The connection details are stored in PDAC.LSI file.
- A shared connection can be used by other users through a shared server. The connection details are stored in SDAC.LSI file in the Business Objects installation folder. However one cannot set rights and securities on objects in a shared connection. Neither can a Universe to exported to repository using a shared connection.
- A secured connection overcomes these limitations. Through it rights can be set on objects and documents. Universes can be exported to the central repository only through a secured connection. The connection parameters in this case are saved in the CMS.
2. What are custom hierarchies? How can they be created?
Custom Hierarchies are defined in a universe in order to facilitate custom drill down between objects from same or different classes according to user requirement. They can be created from Tools -> Hierarchies in the BO Designer.
3. What is a context in universe? How are they created?
In an universe, a context defines a particular join path between tables or a specific group of joins for a particular query. Any objects created on a table column which belong to specific contexts is naturally compatible with all other objects from same contexts. When objects from two or more contexts are used, separate SQL is generated and results are then merged in a micro cube. This makes sure that no incorrect result is generated due to loop or any other join path issue.
Contexts may be created using detect contexts feature or manually. They are generally created based on logical calculation and business requirements, hence the detect context method is not very effective. To manually create a context Go to Insert à Context, give the context name and select the joins that should be present in the context. For a universe contexts should be created in a way that all joins(except shortcut joins) fall in at least one context
4. What is a chasm trap? How can it be solved?
In a dimensional schema based universe, we may have one dimension table joined with two fact tables such that both of them are one-to-many joins(F >- D -<F ). In such a scenario, if we drag a measure each from both the fact tables along with dimensions from dimension table, the value of the measures in the fact tables are inflated. This condition is known as chasm trap.
A chasm trap can be solved using 2 methods:
- In the universe SQL parameters, the option, generate multiple queries for each measure needs to be selected. This will generate separate SQL statement for each measure and give the correct results. However, this method would not work, if a dimension (for example date) occurs multiple times in the result set due to chasm trap.
- A better approach is to put the two joins in two different contexts. This will generate two synchronized queries, thus solving the problem.
5. What is a fan trap? How can it be solved?
In a universe structure, we may have 3 tables joined in such a way that, the 1st table has a one to many join with the 2nd table, which in turn has a one to many join with the 3rd table(A -< B -< C). In such a scenario, if a measure is present in the 2nd table and it is dragged along with any dimension from the 3rd table, the value of the measure will be inflated. Such a condition is known as a fan trap.
A fan trap is solved by creating an alias of the 2nd table and defining contexts such that, the normal table is joined only with the first table, while the alias is joined with both the 1st and the 3rd table. We would take 2nd table’s measure only from the normal table and other dimensions of the 2nd table from the alias table
6. fan traps in a data warehouse scenario? If so, how?
If a data warehouse is based on the Kimball model, it is a dimensional schema. In a universe built on that DW, for a fan trap to occur in such a schema, we require direct join between two fact tables, which is against the principles of dimensional modeling.
On the other hand in a data warehouse based on Inmon model, it is a normalized schema. Though in such a case, universes are generally designed on Data Marts, which are dimensional schemas (where fan traps should not occur). However, if a universe is built on the DW (for the purpose of operational reporting), then a fan trap can occur in that universe
7. What is aggregate awareness? What is its advantage?
Aggregate awareness function is used in scenarios where we have same fact tables in different grains. Using this function we can define only one object for the measures in the fact tables as
@aggregate_aware(highest_level,lower level)
We also need to define dimensions for associated granularities and define their incompatibilities with the corresponding facts through the aggregate navigation. This is accesses through Tools -> Aggregate Navigation
The advantage is that in a Webi or Deski report when one drags the measure object with the dimension object of a particular granularity, the measure column from the Fact table of the corresponding granularity is selected in the BO default Query. If we did not use aggregate awareness, we would need to define separate objects for each of the fact tables which would be difficult to understand from a user’s point of view.
- What are the 2 different approaches of implementing aggregate awareness? Which one is better in terms of performance?
The 2 approaches are as follows:
- Aggregate tables are built in the database, which contains the dimension fields(not foreign keys) along with the aggregated measures. In the universe they are present as standalone tables, i.e they are not joined with any dimensions. Aggregate aware function is used to define both the dimensions and measures of such tables.
- No aggregate tables are built in the database level. They contain the normal fact table at different granularities. In the universe, aggregate aware is used only to define the measures and aggregate incompatibility is set accordingly.
The first approach is better in terms of performance, since for the higher levels of aggregation, all the information is obtained for a single table. However, a large scale implementation of this approach in a dimensional schema is difficult. In most BI projects, the second approach is preferred
9. What is a derived table? What is its utility?
A derived table is a table created in the universe using an SQL Query from database level. The columns selected in the query become the columns of the derived table. A derived table can be used for complex calculations, which are difficult to achieve in report level. Such calculations are done in query level itself.
Another use of derived table can be to access tables from a different schema through a dblink.
Advantages of derived table:
- It can be created using an SQL statement with complex expressions, joins and prompts which are not possible to create in BO Universe using normal approach.
- It acts just like a normal table in the universe and can be used to join with other database tables and derived tables present in the universe.
- Multiple levels of nested derived tables can be created, in which, one derived table is used in another derived table query.
- Since a derived table is based in a universe and is not dependent on any DDL, there’s no need for any interaction from the DBA or ETL team for its creation.
- Changing the structure of the derived table is as easy as changing the SQL statement it is made up of.
Disadvantages of derived table:
- Derived tables do not store data and hence every time a report using this table is run, the whole SQL query is executed. This may cause poor performance of the report.
- If the database tables used in the derived tables are huge and the query is complex, it may cause memory issues on the server.
- Since the derived table is a logical table, indexes cannot be used for faster data retrieval.
Points to remember:
- Use Derived Tables as a last resort since it slows down the execution of reports.
- Use only when an urgent (but temporary) fix is required and ETL implementation of this fix will take time.
- Use only when there’s a requirement for a prompt to be embedded in the table structure.
- Use only when the BO universe methods are incapable of creating an implementation with complex joins and calculations.
10. How is a derived table different from a view? Which one is a preferred solution?
A derived table is present only in the universe level, while a view is created in data base level. Generally views are preferred since, in its case the onus of calculation remains on the database and it does not load the BO server. However, in cases where developers do not have access to database, derived table is the only solution.
11. How can we access one derived table from another?
We can access one derived table from another using the function @derived_table. The syntax is: @derived_table(Derived Table Name)
@derived_table(Derived Table Name)
12. What is Index Awareness? How is it implemented?
Index awareness is a property of the universe, by means of which values in the filter conditions of the queries/data providers built from the universe, are substituted by their corresponding indexes or surrogate keys. Generally the values in the filter condition come from a dimension table (like country etc) and we require a join with the fact table to get this value.
However, if index awareness is implemented, this join is eliminated and the query filter takes the equivalent index value from the fact table itself.
To implement index awareness, one needs to identify the dimension fields which are to be used in query filter. In the Edit Properties of the object, we get a Keys tab. In this tab, the source primary key of the table from which the object is derived needs to be defined as primary key, and the database columns for all foreign key relationships with the other tables also need to be defined here. Once this is done for all required dimensions, the universe will become index aware
13. How can we use index awareness in universe prompt?
An extended prompt syntax is available since BO 3.1. It is as follows
@Prompt( ’message’, ‘type’, [lov],mono/multi,free/constrained/primary_key,persistent/not_persistent, {‘default value’:’default key’} )
If the indexes for the dimension object is defined in the universe and we define the prompt condition on the object with the clause ‘primary key’ in place of free or constrained, then the filter condition will convert the prompt values entered to their corresponding indexes and eliminate the join with the dimension table
14. What is a condition object? How is it different from query filter?
A condition object is that which has filtered condition – only one object is affected here, where as in Query filter entire query result is affected by query filter.
15. Implementing Shortcut join
Suppose in a Universe structure we have tables as shown in the diagram below. Tables A,B and C are in context ABC and C,E and F are in context CDE Now if there is a requirement which requires a join between Table E and Table B, we can define a new context BCDE. But what is the easiest way to implement this?
Define a shortcut join between tables B and E. To do this, join the tables normally. Then open the join editor and check the box shortcut join.
The join will show as a dotted line between the two tables. This kind of a join does not create loop and cannot be placed in any context. The shortcut join between Table B and Table E will only work when objects from both the tables are selected in the query panel of report.
16. Implementing Index Awareness:
We have objects from 3 tables A,B and C in the query panel of a report. Among them C is a lookup table which holds values with respect to keys. Table B holds the foreign key to the table C. A filter condition is applied to Table C in Query Level. The resulting Query is:
SELECT A.a, B.b FROM A,B,C WHERE A.bfk=B.pk AND B.cfk=C.pk AND C.val=’XXX’
SELECT A.a, B.bFROMA,B,CWHERE A.bfk=B.pkAND B.cfk=C.pkAND C.val=’XXX’
Now, we define Primary key and foreign key relations for Tables B and C. Suppose the surrogate key corresponding to val ‘XXX’ is 12. How will the query change after implementing this index awareness?
The resulting query will be:
SELECT A.a, B.b FROM A,B WHERE A.bfk=B.pk AND B.cfk=12
SELECT A.a, B.bFROMA,BWHERE A.bfk=B.pkAND B.cfk=12
The table C will be eliminated from the Query and the foreign key to C in table be will be equated to 12, the key corresponding to ‘XXX’. The join with C will be eliminated.
17 Implementing Access Restriction:
A User named User1 wants a privilege of running a BO Report for 40 mins and retrieving a report with row limit 40,000. However, in the SQL parameters of the universe, the row limit is set to 10,000 and the execution time limit is set to 10 mins. How can you give the user the required rights?
Go to Tools -> Manage Security Click on Manage Access Restrictions. Create a new restriction. In the Controls tab of the restriction, set the row limit to 40,000 and execution time limit to 40 mins.
In the Main Window, apply this restriction to User1
18. Implementing Formula in Context
In a report we have a table like this:
Dim A Dim B Measure 1AA 12 100BB 34 50CC 21 40DD 43 90EE 45 200FF 54 75
There is a report filter applied on this block which restricts both DIM A and DIM B in the table, i.e only select values of DIM A, DIM B and the corresponding measures from the query are displayed in the table. Another column needs to be added which will calculate the average for each row based on the sum of Measure 1 in the table (not all values in report). What would be the formula?
For this we will require the sum of Measure 1 in the table, which can be achieved only by In Block keyword.
The formula will be:
Sum( Measure 1 ) / Sum( Measure 1 In Block )
- Implementing Filtered Row in Dashboard
In the embedded sheet of an Xcelsius dashboard, we have data like:
Field A Field B Field C Field DXxx Tyu 100 98Xxx Yyy 45 76Xxx Dev 56 87Yyy Wes 78 13Yyy Rid 200 106
In the dash board, we need a selector on Field A for a chart which will plot the values of Field C and Field D against all Field B values against one Field A. How can we achieve that?
This can be achieved using filtered rows. In the Selector Properties, select Insertion Type as filtered rows and map the Labels by selecting all values of Field A(including the duplicates).
The labels will display unique values and when a particular value is selected, all rows corresponding to the value of Field A will be selected as output. The chart component needs to be mapped to the output this selector.
20. Cardinality in JOIN
| Cardinality is a property of a join that describes how many rows in one table match rows in another table. Cardinality is expressed as the minimum and maximum number of rows in a column at one end of a join, that have matching rows in the column at the other end of the join. The minimum and the maximum number of row matches can be equal to 0, 1, or N. |
21. What is the Drill filter and it’s usage?
to use the drill filter by default to generate ur report
after to select on toolbar click > tools menu > options
>select Drill filter in drill toolbar by using in drill
through section > automatically u select one column given the
right click > drill filter option will available.
for Ex to take objects year,Q,M,City,customer,revenue
i want only the year value to have 2008 only.
u select Drill filter option automatically u will get ur
filterised data.
22. How to move reports from Dev to QA in BO
| Using the Import Wizard we can migrate the Reports and associated repository objects from one env to another environment. |