BUSINESS OBJECTS - Raj Kumar discovery in BO « All about ...
BUSINESS OBJECTS
Frequently Asked Questions
Prepared by Raj
Date: July 12th, 2011
Version: BO XI 3.x
Note: This material is prepared by referring to many websites, blog sites of many BO gurus and material from various sources. I would like to thank all for their contribution. I just gathered and kept here for the sole purpose of helping BO newbie’s and whoever attending interviews to have a quick glance of the subject.
Chapter 1: Data warehousing & BO Concepts
1) What is Data warehouse?
Ans. A Data warehouse is a Subject-oriented, Integrated, Time-variant and Non-volatile collection of data in support of management‘s decision making process.
Subject-Oriented: A data warehouse can be used to analyze a particular subject area. For example, "sales" can be a particular subject.
Integrated: A data warehouse integrates data from multiple data sources (transactional systems - OLTP). For example, source A and source B may have different ways of identifying a product, but in a data warehouse, there will be only a single way of identifying a product.
Time-Variant: Historical data is kept in a data warehouse. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older data from a data warehouse.
Non-volatile: Once data is in the data warehouse, it will not change. Only inserts are done to data warehouse and No updates.
So, historical data in a data warehouse should never be altered.
2) What is Data mart?
Ans. Data mart is a subset of a Data warehouse.
3) What is Rapid Mart?
Ans. It uses pre-packaged data marts for SAP, Oracle, PeopleSoft and Siebel applications to accelerate the delivery of analytical data.
4) What is the difference between OLTP and OLAP?
Ans. OLTP stands for On Line Transaction Processing which deals with day-to-day transactions, stores the current data in the database which is normalized as updates are very frequent and deals with the small amount of data.
OLAP stands for On Line Analytical Processing stores the historical data based on OLTP source and the database is De-normalized as frequent updates will not happen and deals with bulk amount of data to support trend analysis and future predictions.
5) What are the types of Dimensions?
Ans. The types of dimensions are:
• Confirmed Dimension
• Junk Dimension
• De-generate Dimension
• Role-playing Dimension
6) What is Confirmed Dimension?
Ans. Dimension which is shared by all fact tables or shared across different data marts is called as Confirmed dimension.
Example: The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.
7) What is Junk Dimension?
Ans. It is a dimension table consisting of attributes that does not belong to the fact table or any of the existing dimension tables. These attributes are usually text or flags with yes/no or true/false indicators.
8) What is Degenerate Dimension?
Ans. It is a fact table primary key and represents the unique identifier of the parent. It has no attributes and doesn’t join to an actual dimension table. Example: Invoice/Tran number.
9) What are Degenerated Objects?
Ans. Objects created using SQL queries or stored procedures called Degenerated Objects.
10) What is Role playing Dimension?
Ans. Dimensions which are used in multiple applications within the same database. For example a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire". This is often referred to as a "role-playing dimension".
11) What is Casual Dimension?
Ans. Dimension which will not change the fundamental grain of the fact table is called as casual dimension.
Example: Gender - Male, Female.
12) What are slowly changing dimensions?
Ans : Slowly Changing Dimensions are basically those dimensions whose key value will remain static but description might change over the period of time. For example, the product id in companies, product line might remain the same, but the description might change from time to time.
13) What are the types of slowly changing dimension (SCD)?
Ans. There are 3 types of SCD.
Type 1: History of dimension is not stored in the dimension table. No trace of the old record exists
Type 2: A new record is added into the customer dimension table for maintaining history whenever the attributes of a dimension is changed.
Type 3: The original record is modified to reflect the change of dimension attribute. Partial history is maintained.
14) What is MOLAP? (Multidimensional)
Ans. In MOLAP data is stored in multidimensional cube. The data can be retrieved fast and slicing and dicing operation is optimal and can perform complex calculations but limited data can be handled.
15) What is ROLAP? (Relational)
Ans. In ROLAP data is stored in relational database. Can handle large amount of data but is limited by SQL functionalities and performance can be slow.
16) What is HOLAP?
Ans. It is the combination of MOLAP and ROLAP. For summary type information, it uses the cube technology for fast performance and when detail information is needed, it can drill through from the cube into underlying relational data.
17) What is Dimension Modeling?
Ans. Dimensional modeling is a logical design technique to present the data in a standard framework to allow for high-performance access. It is inherently dimensional and uses the relational model with some restrictions.
Every dimensional model is composed of one table with a multipart key called the fact table and a set of smaller tables called dimension tables. Each dimension table has a single-part primary key that corresponds exactly to one of the components of the multipart key in the fact table.
18) What is Fact table?
Ans. It is a table which contains two types of columns. One that contain numeric facts (measurements) and other column have foreign keys to dimension tables.
A fact table contains either detail-level facts or facts that have been aggregated (Summary tables).
19) What is Dimension table?
Ans. It is a table which contains further information about an attribute in a fact table.
A foreign key of a fact table references the primary key in a dimension table in a many-to-one relationship.
20) What are the different measure (fact) types?
Ans.
Additive: Measures that can be added across all dimensions.
Example - Sales
Semi-Additive: Measures that can be added across some dimensions and not across others.
Example: Inventory level, where you cannot tell what a level means simply by looking at it.
Non-Additive: Measures that cannot be added across any dimension.
Example – Average
21) What is ODS (Operational Data Store)?
Ans. An operational data store (ODS) is an integrated database, source includes legacy systems and it contains current or near term data, means data is not static. An ODS may contain 30 to 60 days of information, while a data warehouse typically contains years of data and data is static.
22) What is a Star schema?
Ans. A typical star schema has a completely de-normalized dimension and fact tables whose Entity-Relationship (ER) diagram looks like a star.
Dimensions have Primary key and Fact table have foreign keys referencing dimension table primary keys.
A star schema can have any number of dimension tables. The crow's feet at the end of the links connecting the tables indicate a many-to-one relationship between the fact table and each dimension table.
[pic]
23) What is a Snowflake schema?
Ans. In a snowflake schema one or more dimension tables are partially or completely normalized.
A snowflake schema can have any number of dimensions and each dimension can have any number of levels. The following figure shows a snowflake Schema.
[pic]
24) What is the difference between Star and Snow flake schema?
Ans. STAR SCHEMA: De-Normalized Data Structure, Category wise Single Dimension Table, More data dependency and redundancy, No need to use complicated join, Query Results Faster, No Parent Table, and Simple DB Structure.
SNOWFLAKE SCHEMA: Normalized Data Structure, Dimension table split into many pieces, less data dependency and No redundancy, Complicated Join, Some delay in Query Processing, It May contain Parent Table, Complicated DB Structure.
25) What is the use of AFD? Where it can be stored?
Ans. AFD stands for Automatic File Distributor. It is used to create dashboards. It can be stored in repository, corporate or personal.
26) What is Data Integrator (BODI)?
Ans. Data Integrator is a data movement and integration tool and has the capability of Extracting, Transforming and Loading data from multiple sources into a target database or data warehouse.
27) What is Data Federator?
Ans. It provides federated query capabilities that can accelerate deployment performance. It creates a virtual view of all data sources and allows a single BO universe or crystal reports to map to multiple sources and optimally federates (Integrates) queries against individual sources directly.
28) Are data mart and data warehouse normalized or de-normalized? Can both exist in same tier?
Ans. Data in Data mart and Data warehouse is de normalized and optimized for OLAP systems. As data mart is the subset of data warehouse both can exist in same tier and it supports a particular business unit.
29) Can you explain BO architecture?
Ans. BO is a 5 tier architecture consisting of:
• Client tier
• Application tier
• Intelligence tier
• Processing tier
• Data tier
[pic]
Client tier: There are two types of clients.
a) Windows based clients – These are also called as Thick clients and needs to be installed on your machine to work on it.
Example: Central Configuration Manager, Designer, Web Intelligence Rich Client, Desktop Intelligence, Report Conversion Tool, etc.
b) Web based clients – These are also called as Thin clients and are accessed by any web browser.
Example: Infoview, Web Intelligence, Central Management Console (CMC).
Application tier: BO servers will be hosted either in JAVA application or .NET application.
Intelligence tier: It has four servers.
a) Central management Server - It is a primary server which provide services for all other servers in the BI platform including management of
➢ Security
➢ Objects
➢ Servers
➢ Auditing
b) Event Servers - It is a responsible for monitoring file based events, and notifying the CMS of any events occurred.
c) Input/Output File Repository Server - It is a responsible for the creation of file system objects, such as exported reports, and imported files in non-native formats.
d) Cache Server - It is used to store previous run activities of report view requests and avoids accessing the database each time a report is requested, thus accelerates viewing performance and reduces network traffic.
Processing tier: It is the only tier that directly interacts with the reporting database and accesses the data tier and generates reports for clients. The main servers include:
➢ Job servers
➢ Processing servers
➢ Report Application server
➢ Multi-Dimensional Analysis server
➢ Dashboard and Analytics servers
Data tier: It is made up of the data sources that contain the information used in the reports and documents managed by BO enterprise system, and supports wide range of corporate databases.
The Connection server is responsible for handling connection and interaction with the various data sources. It supports relational databases (Oracle, MySQL, Microsoft SQL Server, DB2, Sybase) as well as OLAP (SAP BW, Microsoft Analysis Services, Hyperion Essbase).
30) What are the different authentication modes you know?
Ans. Version 3.0 supports below authentication:
• Enterprise
• Windows NT
• Windows AD
• LDAP (Light weight Data Access Protocol) which will be mapped in CMC.
31) What is Semantic layer?
Ans. It is a business transaction layer between the database and reports. The semantic layer in BO is called as Universe.
➢ As many business rules will be applied on database, these rules generate the SQL and if two users ask for the same information, these users will get same result.
➢ It gives the users independence on the technology as just by dragging objects the query will be modified.
➢ It is an extra layer and is not free. It has to be created, maintained and managed. It must be kept in sync with any database changes that occur.
➢ It could connect to only 1 database at a time.
32) What is meant by ZABO?
Ans. ZABO stands for Zero Administration Business Objects which uses the hardware resources of the client machine for application level processing and communicates through the protocol defined for the browser to use the server resources for processing External requests (like running a database Query, creating a data provider to access data.)
Chapter 2: Designer
1) What are the difference between BO XI R2 and 3.0?
Ans. BO XI 3.0 supports the following features which are not supported in XI R2. These are:
Smart measure: A measure can be called as a smart measure in the universe when its data aggregated in a way not supported by WEBI. A smart measure has projection function set to “Database delegated” on the properties tab of object properties.
[pic] [pic]
Optional prompts: In WEBI you can ignore optional prompts by not specifying any value and prompts does not apply a filter on the data.
ForceMerge function: With this WEBI function you can calculate measures for merged dimensions and is similar to Multicube function in DESKI.
Data tracking: WEBI has the feature to track data changes by highlighting the changed data and displays the previous value of a dimension or measure along with its current value.
It highlights the changed data according to parameters you set. In the option Auto-update, the current data becomes the reference data after each data refresh and the other option is “use the current data as referenced data” the report always show the difference between the most recent data and this fixed reference data.
Multilingual support: From the same report, you can decide in which available language you want to view the report’s metadata and prompts. Using Translation manager you can add translations to the universe on which the report is created or translate the report itself. Once the document is translated, it is published and available to users in their preferred viewing language.
Note: Data is dependent on database source cannot be translated.
Document linking: Using WEBI you can create links to other WEBI documents, to web sites, or to any resource accessible from a WEBI report using a simple user interface. From WEBI you can even pass data to linked resource.
Stored Procedures: In Designer, you can create a universe based on one or more existing stored procedures.
2) How can you optimize a Universe?
Ans. Query time can be shortened by optimizing a universe. There are several ways to optimize a universe.
• Optimizing the Array fetch parameter in the Universe parameters
• Allocating weight to each table
• Using shortcut joins
• Creating and using aggregate tables in database
Optimizing the Array fetch parameter
The array fetch parameter allows you to set maximum number of rows that are permitted in a fetch procedure and determines the packet size on the network. For example, if the array fetch is set as 20 and you plan to retrieve 40 rows, then two fetches will be executed to retrieve the data.
Some data sources do NOT allow to modify the fetch size, in such cases all rows will be return in single FETCH. If you want to reteive binary long-objects (BLOB) set FETCH size as 1.
If you have a network that allows you to send a large array fetch, then you can set a new larger value. This value will speed up the FETCH procedure and reduce query processing time. Values can be set from 1 to 999.
Allocating table weights
Table weight is a measure of how many rows are there in a table. By default BO sorts the table from lighter to heavier tables and this determines the table order in the FROM clause of SQL statement.
If you are using Oracle database, you can optimize the SQL by reversing the order that BO sorts the table. To do this we need to modify the oracle .PRM file by changing parameter REVERSE_TABLE_WEIGHT from ‘Y’ to ‘N’ (Default – Y ). By doing this BO forces to sort the tables from those with most rows to those with least rows.
Path of oracle.PRM file => \dataAccess\RDBMS\connectionServer\oracle\oracle.prm
Restart Designer to apply the changes to the file.
In Designer we can manually change the number of rows for any table.
To view, select View menu => Number of rows in tables. To modify
• Open a universe in designer, Right click the relevant table.
• Select number of rows in table from contextual menu. A dialog box appears.
• Select the Modify manually tables row count radio button. A text box appears.
• Type a number in the text box. This is the number of rows that you want to use for the table.
• Click ok and save the universe.
Using short cut joins
A short cut is an alternate path between two tables. Short cut join used to reduce number of tables that are used in a query.
Using Aggregate Awareness
The ability of a universe to make use of Aggregate tables to optimize SQL query is called Aggregate Awareness. These are the tables which contain pre-calculated data.
You can use @Aggregare_Aware function in the select statement for an object which directs SQL query to run against Aggregate tables rather than on base tables.
Using Aggregate tables speeds up the execution of query and thus improves the performance.
Setting up aggregate awareness
• Build the Objects
1. Identify all the possible definitions (table/column combinations) of the objects.
2. Arrange the objects by level of aggregation.
3. Build the objects using the @Aggregate_Awareness function.
• Specify the incompatible objects ( Tools- Aggregate Navigation )
1. Build an objects/aggregate tables matrix.
2. For the first aggregate table, decide whether each object is either:
- at the same level of aggregation or higher (compatible)
- at a lower level of aggregation (incompatible)
3. Check only the boxes of objects that are incompatible for that table.
4. Repeat the steps for the remaining aggregate tables.
• Define any necessary contexts
1. Define one context per level of aggregation.
• Test the results
1. Run several queries.
2. Compare the results.
3) Can you please clarify the difference between compatible and incompatible objects? When we will use these two?
Ans. When you are defining Aggregate tables in Universe design, you need to create hierarchies for aggregated objects, for that you need to make the relative objects incompatible (the objects which are of lower level of aggregation -- not of hierarchy). If you have Year, Quarter, Month, Day as a hierarchy, while aggregation, you need to define as follows -
Year - Quarter, Month, Day Compatibles
Quarter - Month, Day Compatibles, Year Non-compatible
Month - Day Compatible, Year, Quarter Non-compatible
4) How can you set access restrictions on a universe?
Ans. Access restrictions that apply to a user group are defined in a restriction.
Universe security is managed at two levels:
➢ CMS
o From the Central Management Console, you can set what universes Users can access and depending on the rights defined for a user group, you can restrict viewing, editing, deleting and other actions in a universe.
➢ Universe
o You can define restrictions for users allowed to use a universe. It can be
• Connection
• Query controls
• SQL generation option
• Object access
• Row access
• Alternate table access
5) How do you set Row access restriction?
Ans. Tools => Manage Security => Manage Access Restrictions
Click New => Click Rows tab => Click Add
Click Browse button next to Table box
Click table name and click ok
Click Browse button next to Where clause box
Give condition => OK => OK .
[pic]
To view the restrictions applied to all users and groups, select
Tools => Preview security restrictions, click user account name in the list => Preview.
Parameters and options that appear in red are those that have been modified and apply specifically to the restriction.
6) What are Linked universes?
Ans. Linked universes share the common components such as Parameters, classes, objects or joins. When you link two universes, one universe has the role of core universe the other has a role of derived universe. When changes are made in core universe, they are automatically propagated to derived universe.
Core universe is a universe to which other universes are linked.
Derived universe is a universe that contains a link to a core universe.
If the linked core universe is a Kernel universe, then components can be added to derived universe. If the linked core universe is a master universe, then the derived universe contains all the core universe components. Class and objects are not added to the derived universe but they can be hidden in the derived universe depending on the user needs of the target audience.
[pic]
[pic]
Advantages:
➢ Maintenance will be easy because when you modify a component in the core universe, designer will automatically reflect the changes in all the derived universes.
➢ No need to re-create common components each time you create a new universe as often used components can be kept in a core universe.
Requirements:
➢ Core universe and derived universe use the same data account or database. Using the same connection for both universes makes managing the universes easier, but this can be changed at any time.
➢ Core and derived universes must be in same repository.
➢ Core universe was exported and re-imported at least once. Derived universe does not need to have been exported before creating a link.
➢ Exported derived universes are located in the same universe domain as the core universe
➢ You have the authorization to link the given universe.
Restrictions:
➢ You can use only one level of linking. You cannot create derived universe from a universe which is itself derived.
➢ All classes and objects are unique in both the universes else conflict will occur.
➢ Only the table schema, classes and objects of the core universe are available in the derived universe. Context must be re-detected in the derived universe.
➢ The two universe structures must allow joins to be created between a table in one universe to a table in the other universe else Cartesian product will occur.
➢ List of values associated with a core universe are not saved when you export a derived universe with the core universe structure.
To save LOV’s associated with core universe:
• Create new objects using the same definition
• Assign the new objects the same LOV’s as the core objects
• Hide these new objects.
The hidden objects serve the function of holding the LOV’s so that they can be exported and imported with the derived universe.
[pic]
You can log into designer as a different user without quitting your work session.
Tools => Login As. Give user name and password.
(If there are any open universes, designer closes them automatically)
Tools => Change password ( to change password).
7) What is cardinality and what happens if Cardinalities are not resolved?
Ans. Cardinality means a relationship between two tables based on a join. Means how many rows of one table will match with rows in other tables when these tables are joined.
[pic]
If Cardinalities are not specified between the tables, then
We get more results at report level than the actual result, like a Cartesian product.
We can’t detect the Loops & Traps which are mainly detected by the CARDINALITIES option.
8) What is Context?
Ans. Context is a list of joins that defines a specific path for query and is used for solving loops and traps.
Main disadvantage is that it exposes the end users to the database structure and they are forced to decide which Context to use to run their query.
9) How to test the Context?
✓ Create a query which includes objects which are only in one context : BO should be able to get the correct result by determining the context.
✓ Create a query which includes objects from both the contexts: BO should generate two queries and then unions it.
✓ Create a query which includes object which are common two both the context: BO should prompt for contexts to be used.
10) What is Alias?
Ans. Alias used for solving loops and traps by using same table with different name in the query.
11) What is loop and how do you resolve them?
Ans. Loop is a closed circular path among the joined tables.
If the loop is formed between 1 fact table and 2 (or more) dimension tables, then create an Alias for existing dimension table.
If the loop formed contains more than 1 fact table, then go for Context.
If loops are not resolved and report is run following error might come.
Error: Incompatible combination of objects
12) What is Chasm trap?
Ans. It is a type of join path between three tables when there are two “many to one” joins converging on a single table, and there is no context in place that separates the converging join paths. Because of Chasm trap we get incorrect results i.e., Cartesian product will occur.
Resolving a Chasm trap:
• When there are dimension objects in one or both fact tables, use context.
• When there are measure objects defined for both fact tables, use the universe parameter option SQL “Multiple SQL statements for each measure”.
13) What is Fan trap?
Ans. It is a type of join path between three tables when a “one to many” join links a table which in turn kinked by another “one to many” join. Because of Fan trap we get incorrect results i.e., Cartesian product will occur.
Resolving a Fan trap:
• Create an alias for the table that is producing the multiplied aggregation.
• Create a join between the original table and Alias table
• Create the context
OR
• When there are only measure objects defined for both the tables , use the universe parameter option SQL “Multiple SQL statements for each measure”.
14) What are the Derived Tables and its use?
Ans. It is defined by an SQL query at the universe level that can be used as a logical table in Designer.
Sometimes it not possible to create dimension/measure directly in universe in that case we use derived tables. E.g. First time users, union queries etc.
Derived tables have the following advantages:
• Reduced amount of data returned to the document for analysis.
You can include complex calculations and functions in a derived table. These operations are performed before the result set is returned to a document, which saves time and reduces the need for complex analysis of large amounts of data at the report level.
15) What is the difference between Conditions and Filters?
Ans. The major difference between query conditions and document filters is that a condition is applied to the query and limits the data retrieved from the database, whereas the filter is applied to the data in the document to hide information and display only the information that you want to appear.
16) What is a Join? What are different types of Join?
Ans. As we retrieve data from more than 1 table, we apply join between the tables.
Different types of Joins are:
Inner join Left outer join Right outer join Full outer join Theta join Equi join Self join
17) What are different types of Objects?
Ans. Dimension object - Dimension is a main analysis object in a query which maps to one or more key columns in a database. Dimension objects can be organized hierarchically within a class to make default hierarchies for drill-down operations. For example, time can be a dimension with year-month-day hierarchy.
Detail Object - Detail object provides descriptive detail data about a dimension (maps to one or more columns or functions). For example, a month name in time dimension can be a detail of month index.
Measure - Object provides metrics (aggregated numbers) by which dimensions are compared.
18) What are concatenated Objects in a Universe?
Ans. A concatenated object is an object you create by combining two existing objects.
For example, let’s say that you wish to create an object called Full Name, which is a concatenation of the objects Last Name and First Name in the Customer class.
Example: Full Name = [First Name] + [Last Name]
If Month value is 1 and Year value is 2011 then MonthYear = 12011
(Both variables are number datatype)
=FormatNumber([Month];”0”) + FormatNumber([Year];”0”)
19) What is Class?
Ans. Class is a logical grouping of objects within a universe and can be divided hierarchically into subclasses and represents as folders on a tree hierarchy in the universe pane.
20) How do you group measure objects?
Ans. Create a new variable and use If..Then..Else in the formula.
Example: =If ( < 5000) Then "Low" Else If ( > 5000 and < 10000) Then "Medium" Else "High"
21) Is it possible to join more than one universe in Business Objects? If it is so how is that possible?
Ans. Yes it is possible. We can link the two universes by using add link option which is available in parameters (option seen in tool bar file menu).
22) What is category?
Ans. Category is logical way of structuring or organizing the content (BO document).
23) What are the different data providers can be used to create report?
Ans. Universe, Personal data files such as Text files(.asc, .prn,.txt,.csv),Excel spreadsheet (.xls),Database files(.dbf), xml files(.xml), Free-hand SQL, Stored Procedures, VB, etc.
24) What is the delimiter can be used for text files?
Ans. Tabulation, Space, or Character.
25) How do you combine data from different data providers?
Ans. Dimension objects from one data provider are linked to dimension objects that contain the same values in a different data provider.
26) What are the limitations for Combined queries?
• Queries must contain the same number of objects
• Objects must be of the same type( i.e. character, date, number)
• there can be upto eight queries in a combined query
• when using minus, the second query is subtracted from the first
27) How can you check the integrity of universe?
Ans. By making use of Check integrity button.
28) Is it possible to create reports from different universes in one document?
Ans. Yes it is possible to use different universe to generate a single report....multiple data providers. OR you can link other universes to existing universe and then develop the report.
29) What are Universe parameters?
Ans. Definition, Summary, Strategies, Controls, SQL, Links and Parameters .
30) Why do we need metrics and sets?
Ans. Metrics are used for Analysis and Sets are used for grouping.
31) What issues you faced while creating universe?
Ans. Loops, traps, connections to the data base.
32) What are Pre-defined conditions? How do you compare with report conditions?
Ans. These are the conditions defined by Designer in the Universe. Often used Query conditions are qualify for pre-defined condition.
33) What is object and what do you mean by object qualification?
Ans. Object is an instance of class and object qualification represents what kind of object it is like: Dimension or Detail or Measure.
Object is a component named with business terminology and maps to data in the database or uses other objects within the same universe.
34) What is LOV? Where it is store?
Ans. It displays the list of data values associated with an object. A list of value can contain data from a Database file or an External file.
35) Explain in Detail about Measure Objects? What is the use of it? How to create it?
Ans. Measure Object conveys numeric information which is used to quantify a dimension object. A measure object returns numeric information. You can create a measure object by using aggregate functions such as: Sum Count Average Minimum and Maximum.
36) In universe parameter if you don’t give the option multiple contexts, what is the impact in report level?
Ans. By having multiple contexts you can utilize the Multipass SQL feature.
Multipass: Breaking one large SQL into multiple SQL. If you are using the star schema with two or more fact tables, and you enable this feature, BO will automatically generate two or more SQLs (i.e. one SQL for each fact table object used in the report). Then the results will be synchronized in the report.
37) What is isolated join in check integrity
Ans. A join which is not associated to any one of the contexts.
38) Can I link two Universes having with different connection?
Ans. Yes as long as they point to same database.
39) I have 2 universes, U1 and U2. From U1, I created one report that is R1. Now i want to give the connection R1 to U2 and at the same time delete the connection from U1 to R1? How is it possible?
Ans. We can change the connection for the report R1. For webI reports in query panel on left side we have Query properties there we can change the connection of the universe to U2. Then it will map to the U2.
40) How to set Cascading LOV’s?
Ans. We are going to create Prompt based on:
Country > Resort > Service Line > Service (Hierarchy diagram shown below)
Go to the Edit Properties window of Resort
Select Automatic refresh before use and click Edit
Bring the Country object to conditions panel and build prompt:
“What Country are you interested in?” – Save and Close – Apply
Go to the Edit properties window of Service Line
Select Automatic refresh before use and click Edit
Bring the Resort object to conditions panel and build prompt:
“What Resort are you interested in?” – Save and Close – Apply
Go to the Edit properties window of Service
Select Automatic refresh before use and click Edit
Bring the Service Line object to conditions panel and build prompt:
“What Service Lines are you interested in?” – Save and Close – OK
In WEBI, Select Country, Service Line, Revenue in result objects and Service Line into query conditions panel and build prompt “What service lines are we tracking?”
Click Run query
Now you will be prompted for Service Line Hierarchy
[pic] [pic]
Click values and this is where the fun begins. You will be then prompted for a Resort
[pic] [pic]
[pic] [pic]
[pic] [pic]
[pic] [pic]
[pic]
41) What is the test methodology for testing BO Universes?
Ans. Universe is tested keeping in view of the reporting requirements. A Universe should support creation of all the reports that needs be created off it plus any other additional requirements.
• Using check integrity option we can test the universe.
• Check whether any loops are there in universe.
• Check any chasm trap and fan trap are there in universe.
• Check parse of all the objects created in universe.
• Check the joins. Weather any condition is not properly specified.
42) What is hierarchy?
Ans. Hierarchy is an ordered tree structure of relevant dimension objects. Hierarchies are used for drilling information across various levels.
43) What is the difference between the default hierarchy and custom hierarchy?
Ans. Default hierarchies are created as per the default classes and dimension structure in the Universe while Custom hierarchies are those explicitly defined by in Universe by using dimension objects.
44) What is difference between local filter and global filter?
Ans. Applying filter to a specific table (block) is called as local filter while applying filter to a report is called as global filter and it is applicable for all the blocks in a report.
45) What is Drill down, Drill up, Drill Through, and Drill across?
Ans. Drill Down: When you drill down, you display the next level of detail in a hierarchy.
Drill Up: When you drill up, you display the next highest level of detail in a hierarchy.
Drill Across: When you drill down and up, you move through the levels of the same Hierarchy. However, if you cannot find the answer to a question by analyzing data in its current hierarchy, you can move to another hierarchy to analyze other data.
Drill Through: If the lowest level of detail you need is not currently available in the report, you can drill through to the database directly from drill mode and get the data you need. You do not have to edit the query in the Query Panel.
46) What is the syntax of prompt?
Ans. @Prompt('message','type',[lov],mono/multi,free/constrained/primary_key,per
sistent/not_persistent, [{'default value':'default key'[,'default value':'default
key',...]})
‘message’ - Test of the prompt message enclosed in single quotes.
‘type’ - Data type returned by the function.
‘A’ for alphanumeric, ‘N’ for number & ‘D’ for date.
Lov - You can specify two types of list of values.
Hard coded list: {‘AUSTRALIA’,’FRANCE’,’USA’} for example.
Pointer to a list of values from an existing object: ‘CLIENT\COUNTRY’.
Mono - user can select only 1 value from LOV.
Multi - user can select multiple values from LOV
Free - user can enter a value or select from LOV
Constrained - user must select a value from the LOV
Primary key - Use the primary key parameter with "free" or "constrained". The user enters a value or selects from the list of values. If the primary key parameter is present, the entered or displayed value is not used to generate the query. The associated key value from the Index Awareness column is used.
Persistent - when refreshing a document, the last values used in the prompt is displayed by default.
Not persistent - when refreshing a document, no last values will be displayed in prompt.
'default value':'defaultkey' - The default values parameter is used to define default values presented to the user.
You can define multiple default values. The syntax for each default value is: ‘value’:’key’.
The colon (:) is the separator between the value and the key.
When refreshing a document these values are displayed by default but if
the persistent option is set, then the last values used in the prompt are used instead of the default values.
If you specify the primary key parameter, then you must provide the key value(s).
47) What is Index Awareness in Universe?
Index awareness is the ability to take advantage of the indexes on key column to retrieve the data fast.
The objects that we create in the universe are based on database columns that are meaningful to an end user. When you set up index awareness in designer, you tell designer which database columns are primary and foreign keys which will increase query performance.
To setup Index Awareness, open the properties of objects, open “Keys” tab. Click Insert and Add respective primary key and foreign key.
48) How to implement Index awareness?
Ans. This is a feature of the universe to speed up performance of queries in several ways:
➢ It can remove joins from the SQL
➢ It can remove tables from the SQL
➢ It can search indexed, rather than non-indexed, columns
[pic]
Here ClientName is a transformation (concatenation of FirstName and LastName) and searching a transformation is never going to be fast, as it cannot be indexed in the database.
Now with the help of Index awareness, instead of searching a ClientName, the SQL will search the ClientID which is a indexed column.
[pic]
The Key values are then stored in the LOV for the object. When a user selects a value from the LOV, the SQL will switch the value to the indexed column. Of course, if the user does not select from the LOV, but types in the value instead, Index Awareness is not used. So you will need to train your users to select from the LOV.
[pic]
Index awareness works best when used on a transactional database, or a snowflake schema. Star schemas are usually already optimized for queries, so you may not get as much improvement with a star schema.
49) You are a universe designer and report developer in BO, what type of information you gather from client?
Ans. Universe level:
• What new objects to be made available in Universe
• What new pre-conditions to be added
• What User restrictions apply
Report Level:
• Attributes required in report
• Report input criteria (Prompts)
• Report filters to be added
• Alerter - Any information to be highlighted on specific criteria
• Any calculated attributes required and exact calculation in functional terms
• Is User to be informed in case of duplicate records and what should be the action
• Report layout
50) What is BIAR file?
Ans. It stands for Business Intelligence Archive Resource which is similar to a zip file which is used to collect objects together to facilitate easier backup, restore or transporting objects from one system to another.
You can create, export, import and promote a BIAR file with Life Cycle Manager (LCM).
51) How to make Back-up of a universe?
Ans. Using Business Objects Designer import the universe. Then making no changes, close the universe. This will create a fresh, unaltered copy of the universe on your local computer.
Locate the universe file (*.unv) and the folder (has the same name as the universe file) in the following folder path on the computer on which you are running Designer:
C:\Documents and Settings\Application Data\Business Objects\Business Objects 12.0\Universes@DevelopmentWorking Area
Select both the universe file and the folder (hold CTRL and use mouse). Then right-click either the selected file and chose “WinZip -> Add to Zip File…” Note: Any compression tool will do.
Enter a name that is meaningful to you; it may indicate the version of the universe, the date/time, or editor. Click “OK” and notice the new Zip file you created. This is your backup.
52) How to Restore a back-up of a universe
Ans. Many logical work flows for handling universes can result in changing CUIDs and lost report bindings. Nevertheless, if properly back-up a universe can be restored to a prior state by following this work flow.
Open Designer, but do not open any universe
Locate the desired corresponding Universe folder and zip file.
They should be located in:
C:\Documents and Settings\Application Data\Business Objects\Business Objects 12.0\Universes@DevelopmentWorking Area
Back-up the existing universe file and folder to a new Zip file
Delete the existing universe file and folder
Unzip the backed-up universe file and folder to this same location. Note: They must be in the same local folder as the universe file and folder they are replacing.
Open the universe file that you just unzipped and export it to the folder in which you are working:
You will receive a prompt similar to the following. Click “Yes”.
Prompt reads, “A newer version of this universe exists in the repository. If you continue with the export you may overwrite existing changes. Do you want to continue?
[pic]
WARNING: if you receive any messages asking you to Move, Copy, or Overwrite a universe then you may not be restoring to the exact location that the universe previously resided. Prompts asking to overwrite the universe will be received if the universe you are attempting to restore does not have the same CUID as the backup universe. In this case, double-check your directories and analyze everything in Query Builder.
1. At the end of the export close the universe and import the universe you just exported.
2. Verify that this is the correct universe.
3. You have successfully restored the universe
Chapter 3: Web Intelligence & Desktop Intelligence
[pic]
1) What is the difference between Variable & Formula?
Ans. Formulas are unnamed and will create confusion when there are many formulas in a report or document. There are three methods to create a formula.
• Enter a formula directly into a cell
• Enter a formula into the Formula bar
• Use the formula editor
Variable is a named formula and stored in a document. A complex formula can be simplified by using variables to create the formula in pieces.
The major difference is the Variable Editor has a definition tab, where the name and variable type are specified.
2) What is the difference between User objects and Formulas / Variables?
Ans. Formulas and Variables are used to manipulate the data after data has been retrieved from the database while User objects operate at the database level through the SQL query.
Formulas and Variables are associated with single document and are available to any user refreshing or modifying the document while User objects are associated with specific universe and are not available to other users. They exist only on your computer.
(To create, Tools ==> Universe. Select the Unv under which the user object will be created for)
3) How you will do Relative Positioning of Tables, Charts, and Cells?
Ans. Moving one block relative to the other block either up or down or left or right is called relative block positioning. Process is:
Click on the table, chart, or cell until you see a border around the object.
Right click – Align – Relative position or Right click and select “Position”
[pic] [pic]
Then your choices are flexible: horizontal, right and left, and vertical, top and bottom.
Each option allows the space to be defined in pixels (px).
4) What is User Object?
Ans. It is not available to other end users and if an end user tries to refresh or edit a query that contains another user's user object, BO removes the objects from the query and report.
5) What is SetPrompts Failed error message?
Ans. When the Universe and reports migrated from DEV region to QA region and at the time of testing reports by running them via Infoview, we get this error message SetPromptsFailed(). If we refresh All list of values at the time of Universe migration, we can get rid of this error.
6) What is GetLov Failed – Cannot load universe?
Ans. We get this error while refreshing report mostly in Infoview.
If you do not have data access to universe then you will get this error. In case if you have access, open the report in DESKI. Tools menu ==> Universe, select the particular universe causing the issue and click List of values. Select the particular object or class and click Refresh. If you select the class then it will refresh each and every object of that class.
7) What is Micro cube? How it works?
Ans. Micro cube works like a cache. For example if you drag objects in Result objects pane and run query, complete report data will go and store in Micro cube and then display in the report.
In report if you apply filter, you will get data that satisfies your filter. The remaining data will not be deleted from the report, micro cube will store that data.
Whenever you remove that filter from the report, again you will get complete data from the micro cube.
8) How do you do BO report testing in DESKI?
Ans. When you open the Report in Desktop Intelligence before refreshing the report, you need to set the row limit according to your convenience in Data Manager by clicking the Options tab. You can select 10, 20 or whatever number of rows you want in your report. Now run the report, It will retrieve all the rows from database and stored it in the data cube but in Report only the number of rows selected will be displayed. It will make the report testing easy.
9) How do you do BO report testing in WEBI?
Ans. When you open the Report in Web Intelligence before refreshing the report, you need to set the row limit according to your convenience in EDIT QUERY mode by clicking the Properties tab. You can test for Rows retrieved, Retrieve duplicate rows, Prompt order, etc.
10) How can you do load testing for WEBI reports? Concurrent users refreshing report at one time?
Ans. Load testing can be done using Load runner tool. The results which you get are:
• Maximum running virtual users
• Hits per second
• Average response time for login, logout, click on particular link, etc
• Errors per second
• Connections per second
11) How you will do report investigation?
• First look at the report and check for report filters
• Check for query filters
• Look at SQL
12) How you can create Date prompt with Default today?
Ans. CALENDAR_TABLE.CAL_DATE = (
CASE WHEN @Prompt(‘Enter date or Today’,'A’,,mono,free,not_persistent,{‘Today’}) =’Today’ THEN TRUNC(SYSDATE) ELSE @Prompt(‘Enter date or Today’,'A’,,mono,free,not_persistent,{‘Today’}) END)
For DB2 use CURRENT_DATE and for SQL Server use GetDate()
13) How to overcome overflow of data in a report?
Ans. When you have huge number of rows in report then u will get partial results in that report. for that u have to increase the (Limit Size of the Result set) check box button in Universe Parameters under Controls. The number of rows that are returned in a query are limited to the number that you specify.
14) What do you mean by Purging?
Ans. Purging removes the data but keeps the structure. Before you share the report with other users we need to purge the document because other users have different security access based upon their profile. Purging also reduces the size of document and saves disk space. The purged data can be retrieved by user by using refresh.
15) How do u get requirement from the client? In what format?
Ans. Design Specification (.doc)- shows the architecture of the Project.
Requirement Specification (.doc) - Security Requirements, Performance Requirements, Non Functional Requirements, Functional Requirements
Project Plan (.mpp) - Project Duration
Report Specification Template (.xls) - Look and feel of the Reports
Data Element Matrix (.xls) - Tables and Columns involved in the Project
16) How can you add multiple values in a filter?
Ans. Right-click on the report, a block, or an individual element
Filter > Add Filter option to provide access to the ‘Report Filter Editor,’ ‘Block Filter Editor,’ and ‘Filter’ dialog boxes
Use these dialog boxes/editors to create and/or edit your filters
You can now use both AND and OR operators with these filters
The selection of operands is limited here as compared to query filters. Example Matches pattern
17) How can I see and work with all of the filters applied on a report at once?
Ans. You can only do this in InfoView's 'Interactive View' mode by making use of the 'Document Structure and Filters' tab. Double-click on one of the report filter boxes in the 'Document Structure and Filters' tab, the 'Block Filter Editor' dialog box opens.
18) What character do I use for a "wildcard" and how should I use it?
Ans. Wildcards are used with the "Matches Pattern" operator which has the keyword “LIKE”
% is used for multiple value and _ is used for single value.
19) How can I break on multiple columns at once (e.g., Name and Penn ID together)?
Ans. Create a variable that concatenates these columns, and then put break on that column.
20) How do you retrieve information from Repository?
Ans. In earlier versions, this information is stored in repository database and can be obtained easily with query SELECT * FROM OBJ_M_CONNECTION
Using Query Builder tool (URL http:// /AdminTools/querybuilder/query.jsp )
SELECT * FROM CI_APPOBJECTS WHERE SI_KIND = “MetaData.DataConnection”
[pic]
Using DESKI and VBA to retrieve information from repository:
Launch DESKI with the new report wizard and then create a simple query using just one object and click RUN button.
From Tools menu, select Macro => Visual Basic Editor ( Alt F11 key).
On the left side, right click on doc name and select Insert => Module.
Copy and paste the code and name it.
[pic]
Insert menu => Table => Access new data in different way => Others(Visual basic for Applications) => Choose the previously created subroutine and click Run. The query result is inserted into report.
We can query the System Database using an abstraction layer, made up of 3 virtual tables:
• CI_INFOOBJECTS (user desktop info + …)
• CI_SYSTEMOBJECTS (administration info)
• CI_APPOBJECTS (App info + universe metrics)
21) How can I see how many rows of data were returned from my query?
Ans. In InfoView Interactive View mode, you can see the number of rows returned, last execution time on the Data Summary tab.
[pic]
22) How can I see the order of the report breaks or sorts, and how can I change them?
Ans. The most efficient way to do this is with InfoView Interactive View mode.
Right-click in a column that you think has a break.
Notice that the Sort and Insert break... buttons change and look 'indented' when a column that has sorts or breaks is clicked.
[pic] [pic]
Select Break from the drop-down menu that appears when you right-click.
Select Properties and click on it and the Break Properties box will appear.
Existing breaks are listed in order here.
Up and down arrows allow you to change the order of multiple breaks.
Existing Display Properties are shown and you can change them.
Existing Page Layout items are shown and you can change them.
You can also remove a break. [pic]
In custom sort, you can add and remove temporary values in the sort as well as change the order of the sort.
23) If the time duration given in seconds, then you can display in hh:mm:ss format in report?
Ans. In WEBI
[pic]
V_hh =If (Truncate(([Duration] / 3600) - (Truncate([Duration]/86400 ; 0) * 24) ; 0) ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.