Business Objects: Question and Answers

Today i would like to share some of the important question and answers that could help you in cracking any interview in the BO/BI domain and one must go through it.

"Continuous practice makes a person perfect & his will power makes the person conquer his goal of life".

So here are the following Q & A for cracking down any interview in the BO/BI domain.

What is SAP BO?
BO stands for business objective and now it is called as BI i.e. business intelligence. It is a front end application that allows users to view, sort and analyze business intelligence data from the database with the help of connection like OLAP or relational connection.
What is IDT and UDT? And what is the difference between them?
IDT – information design tool.
UDT – universe design tool.
Both of these extract the data from different data source with the help of connection like OLAP or relational connection and helps in creation of universe.

IDT	UDT
Known as information design tool	Known as universe design tool
Extension is .unx	Extension is .unv
Multiple source	Single source
It can be directly connected to dashboard designer or crystal reports	It is not possible in it as IDT
It is tab based	It is window based
We can open the .unv extension files in IDT by converting them to .unx	Conversion is not possible in UDT

Which tool is used for the conversion of the files in BI?
UMT (upgrade management tool) is used for the conversion of files from .unv to .unx which is present in new versions of business object i.e. BI 4.0 and so on.
What are the types of connections available in BI?
There are 3 basic types of connection which are personal, shared and secured connections.
Personal connection – it is a type of connection that is created on a local system and can only be used by a particular user and others can’t able to share it. The extension for this type of connection is stored with PDAC.LSI file.
Shared connection – the name of the connection itself defines about the connection. In this types of connection it can be shared with all the users with the help of a shared server. The extension for this type of connection is stored in SDAC.LSI file which is located at BO installation folder. In this connection we can’t able to export universe to repository and also we can’t able to set rights on a object in this connection.
Secured connection – it is a type of connection in which we can do all the things that we can’t able to do in other two types of connection. In this we can assign rights on objects and we can even export universe to central repository. Connection parameters are saved in CMS (central management server).
What is the difference between OLAP and relational connection?

RELATIONAL CONNECTION	OLAP (Online Analytical Processing)
If we like to access data from the tables and RDBMS then we go for relational connection	If data is stored in an application or cubes then we go for OLAP connection
It can be created in UDT/IDT only	It can be created in IDT and CMC
It creates SQL statements from a report in case of HANA.	It creates MDX statements in case of HANA.

What are the parts of UDT and IDT?
When we are designing universe with the help of design tools like UDT and IDT then I can say that in –
UDT – structure pane and universe pane.
Structure pane – here we drag the tables and create join between the tables.
Universe pane – here we create class, subclass and even objects. Here we label them as dimension or fact or detailed.
IDT – data foundation layer, business layer and relational and OLAP connection.
Data foundation layer – this layer allow us to import tables and joins from different relational database. We can also define derived tables, custom calculations, additional joins, prompts etc.
Business layer – here we define measures and dimensions. We can also apply here aggregations. For completing the universe creation one must have to save the universe and then publish the business layer to repository.
What is class, sub class and object?
Class – it is used to group of similar object in a report on in universe level.
Sub class – it contains the sub category of objects.
Objects – these are the most refined components of the universe which is classified into 3 types mainly dimension, fact and detailed.
Dimension – one time entry objects are dimension. Ex – customer name.
Measure/fact – numerical entries are fact or measure. Ex – revenue, salary, bonus.
Detail – gives details about the dimension objects. Ex – customer address.
Why do we perform integrity check or checking of universe integrity?
We perform integrity check to know about the errors or any thing missing while creating the universe design. While we check integrity we can find errors like loops, traps, join problems etc.
What is loop? How to resolve it?
According to my knowledge loop is an error that we can visualize in structure pane of universe. In other words I can define loop as number of joins is equal to number of tables and thus forming an endless path which gives incorrect result in reports.
Ex – suppose we have 4 tables like country, region, sales, showroom and we join them all. Now we can find loop which will be highlighted among the joins that we have made between the tables and we can count that number of tables will be equal to number of joins.
So now to resolve it we can use either of 2 methods which are alias or context.
Alias – it forms the Xerox copy of main table for resolving of loop and it terminates the loop from the structure.
Ex – we have 4 tables like country, region, sales, showroom and loop is formed then while applying the alias it will make the duplicate copy or Xerox copy of the main table i.e. here country table and now we have to remove the join from the main table and connect with the Xerox copy of main table and in this way loop is resolved.
Steps for applying alias are as follows –
First check for the cardinalities in which table must be in 1:1 ratio.
After this right click on the table and click on alias option in the tool menu and it will create a Xerox copy of main table.
Post to this remove the join from the original main table and following tables and reconnect the join to the new alias table.
Context – like alias it will not terminate the loop rather then it will create an alternative path for the joins between the tables and loop can be resolved.
Condition – in context number of path depend on number of fact tables and it is the condition when we have more than one fact table.
Steps – first check for the cardinality and then go to tool menu at the top and click on the automated context option and detect the loop and get it resolved.
What are the different states in a context?
In a context there are 3 different states defined for a join which are
Include join – in a part of a schema that is unclear or ambiguous the context resolve the loop by defining a path with the included join
Exclude join – in apart of schema that is unclear or ambiguous the exclude join will define a path that context will never take place.
Neutral join – these are the part of a schema that is not ambiguous or we can say clear and are always included in the query path of the context.
What is a trap and how do you resolve it? What is the difference between trap and loop?
To define trap I could say that it is a join path problem which occur in structure level of universe and we can’t visualize it like that of loop. It gives Cartesian product in the report level.
It is of 2 types basically i.e. fan and chasm trap.
Fan trap – like a fan have 3 wings or blade and a oval like structure to which all the 3 blades are connected similarly here in this type of trap we have 1 fact table with 2 dimension tables.
In other words I could say one to many to many join occur between the tables which cause formation of fan trap.
Ex – let’s take 3 tables like client, sale and sale model and we map them with help of join.
Now again assume that client(x) whose sales revenue is $1000. So when fan trap occur then client sales revenue will be $2000 that we could see in the report.
Chasm trap – it is just the reverse of fan trap and here we have 2 fact tables and 1 dimension tables.
In other words I could say many to one join merge on a table which on return too many join and thus many relationship occur. It finally leads to Cartesian product.
Ex – we have 3 tables like client, sale and rental. Client x have sale revenue as $2000 and rental as $3000. So when we have chasm trap then it will double up the value and we will have sale revenue as $4000 and rental as $6000.
Resolving chasm trap –
it can be resolved by 2 ways mainly.
First is with the help of SQL parameter by selecting the multiple query for each measure object.
This will generate the SQL statement for each measure objects and give correct result in report and finally helps in performance tuning.
Second way is that sometime we have dimension like client then we go for using context and more over it is the best way to resolve the chasm trap.
This will create a 2 different path for each measure objects and get it resolved.
The difference between these two are that loop is visible in the universe level where as the trap is not visible and when only we run the reports it could be analyzed when there is a Cartesian product formation.
What is hierarchy? What are the steps involved in setting up a hierarchy formation?
Hierarchy is a parent child relationship. In other words I could define it as where objects are arranged from higher to lower granularity or we can say collection of dimension object that support drill functionalities.
Ex – time hierarchy (year – quarter – month – week – day – hour – min – second)
We can create custom hierarchy in the universe level as per our requirement. For this
Menu tool bar - tools - hierarchy -enable custom radio button -provide the name -select the desired objects -drag and drop them -click add -arrange them -enter ok.
What is the difference between members and named state in a hierarchy?
Members are defined at different levels of hierarchy in a data source.
Ex – in a geography hierarchy contains member “INDIA” as country and “MAHARASHTRA” as state level.
Named set is defined as named expression and results a set of members. We can define it at universe level or at connection time like OLAP or relational connection.
What is custom LOV? What are the steps involved in creating for it?
It is a set of LOV associated with hierarchy in a universe level and prompts are defined for each level for return to level.
Steps –
Go to tool
-create a list of values
- created cascading LOV. -
Select custom hierarchy for which we like to create cascading LOV-
double click and select the objects of higher granularity-
add the LOV objects to LOV section and each object must have a prompt text.
Then select the check box “hierarchical view” to get the prompt view like expand or collapse in the WEBI which is used as reporting tool-finally shows that cascading LOV generated successfully.
What is derived table? What are its uses?
It is a virtual table that we found in the structure pane of universe and it combines other tables using calculation and functions.
Use – To create a single table that combines 2 or more tables.
What is the difference between derived table and view?

Derived table	View
It is a dynamic and a virtual table that we create within a universe level	It is created at database level
@functions is only possible in derived tables	It can’t be used in views
It don’t fetch data directly from the database	It fetch data directly from the database and good for performance.

Syntax for derived table - @derived _ table (derived table name)

What is aggregate awareness? What are the 2 different approaches of implementing it? Which will be better for performance?
It is used for aggregating pre aggregated data in the tables in the database. It is used for query performance by processing less number of rows.
When we add a aggregation aware object in a query then the query generator retrieve the data from the table from the highest aggregation level.

Syntax - @Aggregate_Aware(sum(aggr_table_1), …, sum(aggr_table_n))

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.
What is index awareness?
Index awareness in universe determines which values in a filter condition of the queries built from the universe are replaced by their corresponding indexes. Values in a filter comes from dimension table and we need fact table joining to get the value.
Ex – employee name is dimension while the salary is fact. So when we have to give salary then in filter dimension have to be added so that it will show particular salary for particular employee.
What are the modes available in WEBI? What is the extension of WEBI doc?
There are 3 modes available in WEBI which are design, reading and data application mode. We can only work i.e. doing editing only in design mode. The extension of WEBI doc is .wid.
What are the types of filters that are present in SAP BI/BO?
There are variety of filters that are present in SAP business objects. The following types of filters are described as below.
Global level filter – this is a type of filter that is been used in universe level. The main purpose of this filter is to restrict the data that comes from the universe level to report level. It helps in performance tuning.
Ex – there are many states in India and we would like the sales revenue of Assam state then, while applying global filter we would get only the Assam state sales revenue and no other state revenue on the report.
There are few filters that are applied at report level and known as report filters. These are used to hide the data from the user. These are as follows –
Custom filters – these are the filters that can be created before and after running the report.
To set this filter add an object that we want to filter
- drag it to query filter pane
-click the arrow next to default operator and finally
-select the operator such as in list, not in list, equal to etc.
-then click the arrow filter type
-select the filters types such as constant, value from the list, prompt etc.
Query filters – this filters are used to retrieve the values quickly that we want to have.
Ex – suppose a report have years ranging from 2000-2017 and we would like to view only the year 2016-2017 then we can apply this filter and we can only view the year 2016-2017 in the report
For setting up this filter
-select and add quick filter which is present on top right corner of result object pane.
-New dialog box will appear and we can select the list of LOV that we would like to add in quick filter.
Apart from these there is one more filter that is present in WEBI which is prompt.
Prompt – it is a special filter and allows user to enter a value every time data is refreshed in the document. It do helps in performance tuning.

Syntax - @prompt(‘message’,’type’’,[lov],mono/multi,free/constrained/primary_key,persistent/non_peristent,{‘default value’:’default key’})

There are 3 objects in a prompt syntax which are object, operator and message.
Ex – let’s take current year which is 2017, so we can say that current year is equal to (2017)

Current year - an object

Equal to - an operator

Prompt message is - (“2017”)

We can also use (AND, OR) operators to create multiple prompts in same query.
Steps involved when applying this prompts –

• Add a object that we like to filter with prompt to query filter pane.
• Select the filter operator from the list and click the last arrow mark to select the prompt.
• We can add then text message for the prompt value and run the query.
• While we run the query prompt dialog box will allow us to enter the value as per selected operator.
• When the values are selected just click ok
• Data for selected values in the document will be refreshed in the report.
• When we refresh the document in WEBI every time prompt will appear.

What is sub query? What are the steps for assigning sub query?
It helps in filtering of data as compared to other query filter .Using this we can limit the values returned with WHERE clause. These subqueries work on basis of SQL which is used to retrieve the query data. If the database doesn’t support SQL then we can’t able to see the option of subquery at query panel. It only be made on dimensions and not on measure objects
Steps for making sub query
Select an object in the result pane on which we like to make a sub query.
Then it will add subquery outline in the query filter pane. Now if we like to add a WHERE condition then drag and drop the objects to the area of subquery.
Then after this select the operator value used to filter object in WHERE condition. We can add multiple queries to query panel. We can also use her and, or operator relationship between sub queries.
By default we have 2 sub queries always linked with the AND relationship. For this drag a sub query to the area and drop an object.
What is input control? What are the steps for it?
These are used to filter and analyze the report data. We can define input control using text boxes and radio button. These are associated with the report elements like tables and section headers and use control to apply filter on report elements.
Steps –
Go to analysis
- filters
-input control
-define control.
Then after this - option choose and define control.
Choose control allows us to select different control types like single value or multiple values.
Then Define control allows us to select the name of control, description, values in control to select, operator and number of lines displayed in input control, etc.
In the next window, we have to assign Report Elements.
It will allow you to choose from report elements.
We can assign report elements to input controls.
Click ‘Finish’ and input controls will be added to the left pane under Input Control tab.
If we like to define input control for a table and charts then we follow steps which are as follows –
Select the table → Right-click and select linking → Add Element Linking.
We can select a single object or all objects to define as filtering objects -->Click ‘Next’.
We can enter the name and description of input control - Click ‘Next’.
Once we click next, we can select any other block to use this object as input control -Click ‘Finish’.
What is conditional formatting in WEBI?
Conditional formatting is known as alerters in WEBI which is used for highlighting purpose. If we have to highlight some values or data in the report then we can use this technique.
Ex – if we like to highlight the highest sales revenue and the lowest sales revenue earned during the year 2006-2017 then by applying alerters we can show the highest and the lowest sales revenue. It is basically used by the senior executives of a company.
We can use maximum up to 30 alerters in a WEBI document.
Steps –
First open the report in design mode and we must use rich client as it will not work in web mode.
Go to Analysis → Conditional → New Rule. It will open formatting rule editor.
Enter the rule name and description. Select the cell contents in filtered object or cell box.
We can select Operator and Operands value. We have to define text formatting in Format tab. Click on Format to set the formatting of the tab.
We can select the font, font style, size, effects (underline, etc.) and alignment. Once formatting is defined for condition, we have to click Ok.
Now to apply conditional formatting to the object, select the column in the report. Go to formatting rules drop down → Select the conditional formatting rules checkbox you have created.
Conditional formatting will be applied to the desired cells. We can add multiple conditions in a single formatting rule on multiple objects.
What is break and section? What is the difference between these?
Breaks are used to define all data for every unique value of an object in separate parts. Using Breaks you can apply -Subtotals and Sub aggregations and Display data in more effective manner.
 Steps –
Section breaks up the data into multiple cells that are called section headers and each section header contains a value for dimension and data corresponding to dimension value.
Break is used to divide the data into one block and each column carries dimension, attributes and measures. These values are repeated for each row value in the block.
Steps for applying break on a report –
First select the column in which you want to insert a Break. Go to Analysis → Display → Break → Add Break.
This will divide the table into many mini tables as there are unique values in the column.
If we like to manage breaks that we have applied then steps for it are as follows –
Go to Analysis → Display → Break → Manage Break. It will open a new window and show all the breaks in the table.
What are the types of charts that we can use in WEBI?
Bar chart, box plot chart, pie chart, column charts, line charts, map chart, waterfall charts etc.
What are sorts? How we can assign them?
It can be applied on tables on section. And in charts to get organize the data in specific order in a WEBI report.
By default it is set in order i.e. left to right order of objects of query panel.
Steps for custom sorting –
To insert a sort, open report in Design mode. Select the Column you want to sort.
Then Go to Analysis → Display → Sort → Ascending/Descending.
You can apply multiple sorts in a single table on different columns. You can use ‘Remove All Sorts’ option to delete all the sorts.
To manage the Order of Sorts, go to Advance tab. You can define order for all sorts using up and down arrows. You can define Custom Sorts here if no other sort is applied.
Ex – suppose we have sales report of ABC of XXX state and what all its raw material which is used in its company such as manganese, cobalt, zinc, aluminum etc.
So while we apply sort we have 2 options i.e. ascending or descending order and we can choose any one order and it will arrange them in required order of alphabets.
What is hyper link?
It is reference to a data that a user can directly follow by clicking on it. We can set hyper link on a report.
Steps for adding hyper link to a cell in WEBI –
Select the cell and select, right click and select linking à add hyper link to display the “create hyper link dialog box”.
Select the link to web page tab.
Type or paste the hyper link text into the box.
Click parse to extract the hyperlink parameters into the customize URL parameters area. (*it is not visible till we parse)
To supply a data from formula or variable as parameter value click the arrow next to each parameter value and select an option.
To add or remove a parameter modify the hyperlink syntax à click parse.
Click the arrow next to cell content to text displayed in the hyperlink cell and choose one of the options.
Type the tool tip text next to tool tip box by using build formula or select variable options.
Click the arrow next to target window to define how the target URL appears.
What is a complex report according to you or what type of report is complex?
Complex reports are the reports that contains all the elements or components of WEBI. It include application of break and sections, hyperlink, filters, context operators, formulas, variables, merging, drill functionalities etc.
What is Ad-Hoc report?
Ad – Hoc reports are the reports that are made one time.
Ex – if the prime minister of India likes to see the corruption of all the states for the year 20016-017 then that report could be told as Ad – Hoc report as it will be made once not every time in that particular year.
What is performance tuning?
Performance tuning is the process by which we can refresh the data quickly or we can say that we get faster view of reports that we run. It can be done in various levels such in universe level, report levels, database levels, and in server levels.
Universe level performance tuning could be done by following ways –
Modification of array fetch parameter – by doing this it sets the maximum number of rows that are permitted in a fetch procedure.
Ex – if array fetch size is 20, and total number of rows are 100 then 5 fetch will be executed to retrieve the data which will consume more time so if we increase the fetch size then it will reduce the query processing time.
Using of shortcut joins – shortcut joins allows user to skip intermediate tables and allows paths between tables. Use of these types of joins reduces number of tables and in query to improve performance tuning
Use of aggregate functions – it aggregates the data in database level rather than in report level which helps in performance tuning.
Report level performance tuning can be achieved by following ways –
List of values, opt for refresh at – will over refresh on open, by using conditional objects, minimize use of variables and formulas.
Server level tuning could be achieved by – maximum allowed size of cache, event based scheduling etc.
Database level tuning -  It is the first step of performance tuning in BO. Some times it do happens that even tuning in report and universe level we see slow process so for that we have to do tuning at database level. Things that we can do at database level to enhance it are as follows -
Execution plan of SQL - it tells about the types of joins and sorting mechanism used in the internal side.
Use of Index -  Indexes on key columns speed up the data retrieval. Indexes are used while retrieving the data from table instead of original column values. The data retrieval speeds up and performance can be increased using the indexes. For example, if we have the Customer name in the query then it is easy for the end user of the report. We can retrieve the Employee name using the Customer id which is indexed and retrieves the data faster where as if we use Customer name to get the data and it is not indexed takes more time.
Division of the tables -  As time goes on, the tables can became large and deduce the problems such as backup, restore and lengthy query times etc. So it is better to do partitioning of tables to avoid the performance issues in future. Partitioning is the process of splitting a table or index into separate parts based on specific criteria. The criterion may be number range, date range etc.
What is variables and formulas?
These are used for calculation purpose in the WEBI and if we use maximum then it makes the report complex.
What is query stripping?
Query stripping is a feature in WEBI that removes un used objects that are not present in report structure to improve performance tuning.
What is context operator?
Context operator is a feature in WEBI that is used for calculation purpose. It is of 3 types – in, for each, for all.
In operator - this operator is used to specify which dimension to include in a context.
Ex - we have a report showing year and sales revenue and we now like to add one more column showing maximum sales revenue by quarter in a report so in this condition we can use in operator in a report.

Syntax - Max ([Sales Revenue] In ([Year];[Quarter])) In ([Year])

For each operator - this is used to include a dimension in a context.
Ex – we can show maximum revenue for each quarter in a report which have a quarter dimension in query panel when we have 2 tables like year and sales revenue

Syntax - Max ([Sales Revenue] ForEach ([Quarter])) In ([Year])

For all – it is used to remove dimension from the context.
Ex – we have a report showing year quarter and sales revenue and we like to add a new column known as yearly total revenue.

Sum ([Sales Revenue] For All ([Quarter]))

What is merging?
It is a feature in WEBI which allow linking of data providers i.e. query. It is of 2 types – extended and force merge.
Extended merging – it is a condition where dimension don’t have same nature and feature and still merge. To get a better understanding of it lets take 2 data provider as DP1 and DP2.
DP1 – CITY AND SALES REVENUE. (first table)
DP2 – COUNTRY AND CITY. (second table)

CITY	SALES REVENUE
MUMBAI	$2000
CHENNAI	$6000
TOKYO	$5000

COUNTRY	CITY
INDIA	MUMBAI
INDIA	CHENNAI
JAPAN	TOKYO

 If the auto merge option is enabled then the city dimension will merge from the data providers and in report we will get result as

COUNTRY	CITY	SALES REVENUE
INDIA	MUMBAI	$2000
INDIA	CHENNAI	$6000
JAPAN	TOKYO	$5000

 Force merging – it is a condition where measure objects are been merged.
In this type of merging it will merge country with the sales revenue but we know the fact that country and sales revenue can’t merge or there is no similarity but still merging. Here we have to use a formula and i.e.

FORCE MERGE ([SALES REVENUE])

COUNTRY	SALES REVENUE
INDIA	$8000
JAPAN	$5000

What is schema and what are the types of schema that you have gone through till date?
Schema is a representation or collection of fact and dimension tables in a particular form. It is of types like – star, snow fake, extended star.

Star schema	Snow fake schema
De normalize data structure	Normalize data structure
Category wise single dimension tables	Dimension tables are spitted into pieces
More data dependency	It is less
No need of complicated joins	Complicated joins
Query result faster	Its slow
No parent tables	It may have
Simple database structure	Complicated DB

What are different versions of Business objects you have worked? What is the difference between BO 3.1 and BI 4.0?
I have worked on version BO 3.1, BI 4.0 and 4.1 and 4.2 too.

BO3.1	BI 4.0
Previously named as business objective	Now known as business intelligence
It contain UDT	UDT with IDT
WEBI	WEBI interactive analysis
Saved in .unv extension	Saved in .unv and .unx extension
Deski	No deski
Crystal report 2008	Crystal report 2011 with CRE[tool]
BO xcelius platform	Dashboard design
No hide option	Hide option available
Infoview	Launchpad
Single data source	Multiple data source
Migration through import wizard or LCM	Only through LCM
Less graphic option	More graphic option

What changes are made in BI 4.2 version?
User notification – it helps administrator to broadcast information to users via CMC. Admin can notify time line for notifications to be visible which can be seen in launch pad.
Commentary – comments are stored in audit database by default and we can customize it. For this CMC à Application à BI commentary à properties. Comments are managed by rights and authentication for objects and folder level.
Ex – enable right for document folder “ABC _ FOLDER” - GO USER - SECURITY - SELECT USER - ALL RIGHTS.
Recycle bin – enables users to restore files that were deleted accidentally. For this go to CMC - application - recycle BI application.
Rest API – for users management (add/ create/ modify) and also upload and downloads.
Upgrade manager – admin can control during upgrading.
Audit – auditing is improved when no event is logged.
What are the types of dimensions you know so far?
There are 3 types of dimension that I know so far which are
Confirmed dimension – dimension shared by all fact tables
Ex – time is a confirmed dimension because it's attributes like day, month, week have the same meaning  when joined with any fact fact table.
Slow changing dimension – dimension key value remain static but description may change.
Ex – change in sur name of wife after their marriage. We can take another example in real working scenario like suppose a employee whose name is (X) is a manager at India and later he is been transferred to Singapore. So in this case we observe that the place changes or we can say that the country changes with a period of time but the employee name and his designation and his Id remain same in that same company.
Casual dimension – dimension that do not change their fundamental grain of table.
Ex – male, female.
What are the 2 modes in universe design?
The modes in design of universe are –
Enterprise – here we work with repository. It contain online and offline options.
Work group mode – work without repository.
What is the differences between OLAP and OLTP connection?

OLAP	OLTP
Consolidation data; OLAP data comes from the various OLTP Databases	Operational data; OLTPs are the original source of the data.
Purpose of data is to help with planning, problem solving, and decision support	To control and run fundamental business tasks.
Multi-dimensional views of various kinds of business activities	Reveals a snapshot of ongoing business processes
Periodic long-running batch jobs refresh the data	Short and fast inserts and updates initiated by end users
Queries are Often complex queries involving aggregations	Relatively standardized and simple queries Returning relatively few records
Processing speed  Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes	Typically very fast
Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP	Can be relatively small if historical data is archived
Database design Typically de-normalized with fewer tables; use of star and/or snowflake schemas	Highly normalized with many tables
Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability

What is data tracking?
WEBI has a feature to track data change by highlighting the data change and display the previous values of dimensions or fact along with the current values. It high lights the changed data according to parameter we set.
What is dimension modelling?
Logical design technique to present data in standard frame work to allow high performance access is dimension modelling.
What is combined query?
It is a group of queries that work together to return a single result. We can do it in 3 ways – union, intersect and minus.
Union – it will take the data from the both queries, eliminate duplicate rows and builds a combined data set.
Intersect – it returns the data that is common in both queries.
Minus – it returns the data in the first query that does not appear in the second.
What is promotion management and what makes it differ from scheduling?
Promotion management is a tool that is present in BI that allows contents like folders, reports, universes, users, connections etc. to be migrated from one BO enterprise to another.
Previously in older versions of BO it was known as LCM (life cycle manager) and in new versions like BI4.0 it is known as promotion management.
What are the types of drills that you have used so far and what is drilling?
I have used two types of drill functions most of the time which are drill up and drill down. Apart from these we have drill through and drill across.
What is a server? Which is the important server as per you?
It is a computer program or device that provides functionality for other programs or devices called as clients.
As per me CMS (central management server) and ADJS (adaptive job processing server) is important as without it maximum of the works are dependent on this.
What is CMC, CMS, and CCM?
These are the components of BI admin.
CMC – central management console
CCM – central configuration management
CMS – central management server [port number – 6400]
How do you create users and group in CMC?
Steps for creation of user and group are as follows –
Creating a user group is a very common task in administration.
For this log on to CMC.
Then under organize section i.e. the first column under which there will be list of options and the 5^th option will be users and group.
Click on it and all the users and groups you can view present there.
Now on the LHS top corner there will be an option for creating user and group.
Click on it and give the credentials that it ask such as name and the password and click ok.
After this your user and groups both can be created.
Under a group you can keep as many as users you like to keep.
How do you assign rights to user and group? What is the difference between view and view on demand?
We can assign rights to user and group by following steps –
Open the CMC homepage and select access level in that.
A new window will get opened in which name of the access levels and the description will be given below.
Ex – view, view on demand, schedule etc.
Now go to top in the LHS and under manage there will be option to create new access level.
Once clicked it will re direct to new page of new access level in which you can give a name as owner view or something like that.
Once done with that now you can see 2 panes one in LHS and other the RHS. In the LHS you can see properties, user security, and included rights.
Click on included rights and on RHS pane now you could able to see view and schedule rights with the status marked as denied or granted or undefined.
You can modify them and save it as a new access level right.
View on demand – it gives user real time access to live data from database server.
Ex – if a manager of a large distribution center wanted to keep a track of inventory shipped on a continuous basis then live reporting is the way to give the information he required.
View – to reduce the amount of network traffic and number of hits on database server we can run reports in specified time or we can say it as schedule. When the report is run then the users can view the report instance as they need without hitting the database.
Ex – if our sales database is updated once a day then I can run the report on a particular schedule. Sales representative can then have access to view the sales data but they will not hit the database every time they open the report.
What is Tomcat Apache?
Tomcat is a HTTP server that runs on java technology.
What is RCA? How do you perform it?
RCA stands for root cause analysis. We perform it to know the root reason behind the error and how to resolve it.
Ex - Suppose one is working on ETL and the person have faced an error then while analyzing the person will check from where does the error starts. So as we know ETL means Extract Transform Load. Thus the person while doing may have to go till database level to find out the reason behind the error and then if the person finds that error is coming from database level then he have to do some sort of modification at that level and after that in report level the same error will not be seen in future. The person may not be sometime going to last level that is database level since the person could get the error in the designing level so from there he can rectify the error and thus finally in reports he will not get the error. So the process of analyzing a problem from report to database level is known as RCA.
What is foreign key and primary key in BO?
Primary key is a field or a group of fields that uniquely identify a record in a table. Primary key fields cannot be null and can’t contain duplicate values.
If we like to link 2 tables, then the primary key of one table will get added to primary key of another table and the primary key of first table is now known as foreign key of second table.
Ex –

Dept _id (P.KEY)	Dept _ name
001	FINANCE
002	HR
003	SALES

EMP _ ID (P.KEY)	NAME	PLACE
01	JACK	SINGAPORE
02	HARY	LONDON
03	MARK	LA
04	RAJ	MUMBAI

 Now if we link department table with employee table then primary key of department table i.e. dept _id to employee table. Here dept _id becomes the foreign key of employee table.
Now the employee table will be such as –

Emp _id	Name	Place	Dept _ id
01	Jack	Singapore	001
02	Hary	London	001
03	Mark	LA	002

What is thick and thin client? What are the difference between these two?
Thin client is web based application and most of the processing is done on server side.
Thick client is installed into in client side and is connected to server but most of its processing is done on client side.

Thin client	Thick client
Web based	Software based
Easy to deploy	More expensive to deploy
Data captured is verified by the server	It is been verified by the client
Reduced security threats	Increase security issue.
Ex – CMC, WEBI	Ex – DESKI, IMPORT WIZARD

What is the difference between info view and ZABO?
ZABO – zero admin business object which is a 3 tier full client business object. This is a special version of full client where we don’t need to go to user’s desktop and do an installation. Here the user visit the WEBI server and set option to use BO as a query editor and as well as that he tries to edit query and an installer launches and install a mini version of full client on user desktop.
Info view – it is a web based interface that end user access to view schedule and keep track of published reports.
What is the difference between prompt and @ prompt?
Prompt is a type of filter that we use in WEBI reporting side where as @prompt is used in universe level. If we create using prompts by @prompt when we drag an object that prompt will appear in reporting environment.
What are the types of error that you have come across while creation of report and how do you resolved them?
There were many errors that I have faced while designing a WEBI reports with my client such as #multi value error, syntax error etc.
Multi value error – Occurs when you place a formula that returns more than one value in a cell that outputs. Or if a single cell is trying to display multiple values. Or occurs when you don’t set SQL aggregate functions for a measure object in universe designer.
Ex: [Revenue] ForEach ([Country]) returns #Multivalue because country might have multiple countries like India; US.one cell cannot display revenue for 2 countries. If report is broken into section on country the formula is correct.
Solution – for this I have tried creating a variable to select distinct value of the object. If there are more than one distinct values then inserted the same as new table.
Some time it also happens that when we apply break on the report during that time I have tried replacing the cell with the aggregation of above field.
Syntax error – this error is there when the select column /cell is a calculated field and calculation is based on field which can’t pick up report lay out or Occurs when a formula references an object that no longer exists in the report.
Solution – for this I used to pull the basic fields into the report and then the fields which were based on calculation basis and hide the unwanted fields.
#Div/0 – this is a very common error that we find in WEBI and basically it is related to measure object as calculations are been done on measure fields. This error occur when a formula tried to divide a number by zero.
Solution - to resolve this error one can use the If () and is error () function.
Is error () will return a true or a false value depending on the item being evaluated. We have to wrap it with and if () statement and we can evaluate the formula as normal if it is ok or return a message to the user if there is any error.
Ex - =If(IsError(15/0);”Error in formula”;15/0)
       =IF (Denominator = 0) Then 0 Else (Numerator / Denominator)
#Computation - when we add formula or a variable that we created to table or cross tab the result is that we get #Computation error.
Solution – hide the fields that the formula or variable uses to the body of the table or cross tab.
Ex – lets take a column as salary and another as employee id. In one of the row of employee table we have null value and we like to find the total salary so in this case we get computation error.
#Data Sync – this error occur basically when we are working with multiple data providers and we try to use un related objects from different queries.
Ex – suppose we have car make and sales amount from query 1 and year from query 2 and try to combine all the 3 in one block then we will get a #Data Sync error. This happen since car make and year object are not related. We will also do have a # context error for sales amount because it is a measure.
#Incompatible – this happen when we try to pull a dimension from 2 data providers which are not been merged then it will show # incompatible error.
Solution – for this go to merge dimension and create merge on common dimension like dimension key in 1^st table and dimension key in 2^nd table and then merge it to resolve the error.
What is self-join and cross join?
Self-join is a set of query used to compare to itself. This is used to compare the values in the same column with the other values in same table.
Cross join defines a Cartesian product when the number of rows in first table multiplied by number of rows in second table.
Ex – WHERE clause is used in cross join then the query will work like an INNER JOIN.
What is collation?
It is a set of rules that determine how character data can be stored or compared.
What is AGGREGATE & SCLAR functions?
AGGREGATE functions are used to evaluate mathematical calculations and return in single value. Ex - Aggregate – max(), count – Calculated with respect to numeric.
SCLAR function return a single value based on input value. Ex - UCASE(), NOW() – Calculated with respect to strings.
What is condition object? How it differ from query filter?
A condition object is a filter condition created in the universe level. When this object is dragged in the Query Panel, the filter condition appears in the query SQL.
However a query filter exists in the report only. It is added during building a query. This also appears in the query SQL
What is the difference between input and output calculation context in WEBI?
Input context consists of any dimension objects that need to be included directly in the calculation itself.
Output context consists of one or more dimension objects that determine the aggregation level at which the calculation is displayed.
Suppose in a report, we have a filter on a block. In the table, we need to display an aggregate of a measure for all data in report. But if we use only the aggregate function, it will be limited by the block filter. How can we achieve that?
We have to use the No Filter() function. When we put the expression within No Filter(), it overrides any report filter as well as ranking applied in report level. The syntax is : No Filter( expression )
Suppose we want to display the total sum of a measure in row level of the report block. What will be the solution?
We have to use context Operator In along with keyword report. This will give the total sum of the measure in row level(and anywhere in the report). The expression will be like:

Sum( Measure In Report )

What are the different ways in which an Xcelcius dashboard can access data from a BO universe?
There are 3 ways in which Xcelcius can access data from the universe:
Query as a Web Service: Using Query as a Web Service tool, we can create a queries from the universe along with filter condition. The QAAWS query panel is similar to the Web Intelligence query panel. In Xcelcius dashboard, we can create a QAAWS connection that would point to a particular Query and import the data into the excel data sheet of the xlf
Business Intelligence Web Service: In this method, we can use the output of a report directly in the Xcelcius dashboard. Using Webi Rich Client, we export the report to repository, then select a block from the report, right click and select Publish as Web Service option. However BIWS does not have a connection of its own. We access this BIWS through a QAAWS connection only.
Live Office: Live Office is an additional component that needs to be installed. This creates a sort of plugin for all MS Office applications, though which they can access data from Web Intelligence reports. I MS Excel, we can launch the Live Office Insert Wizard by Insert -> Web Intelligence Content. We can use this wizard to add selective content from a webi report. In the Xcelcius dashboard, a Live Office connection is created and we access this Live Office excel sheet though this connection
What is the use of the scope of analysis pane in the Query tab of Webi report?
It has 2 uses:
The Scope of Analysis pane sets the limit of drill down in the report. Suppose we have a hierarchy defined in 3 levels, but if we set the scope of analysis is set to 2 levels, the report will not be able to drill down to the 3rd level. We can also remove objects showing in the scope of analysis pane and limit the drill down
If the analysis level is set to custom, the objects from existing hierarchies can be dragged in the scope of analysis panel to set the scope for drilldown in the report. This has an advantage that we can drill down to more than 3 levels, which is not possible in the normal level setting, since it is up to 3 only.
Here is a requirement that in a numeric column of a webi report we need to display the sum of the above rows. Which function should we use?
In this case we should use the RunningSum() function.
The syntax is: RunningSum( [measure] ; [Row|Col] ; [ (reset_dimensions) ] )
When should we use a query filter and when a report filter?
The choice of query or report filter has to take in consideration the performance of the report.
Suppose we have report where multiple tabs are having different objects requiring same or different filter conditions, it is advisable to use a Query Filter.
In case where there are multiple tabs having same objects, but with slightly differing filter conditions, it is advisable to fetch the whole data in the query and then apply report filters to the various tabs to get the desired data instead of using separate queries for each tab.
What do you understand by resource dependencies in universe?
In a Universe there can be many objects which are dependent on each other and moving, deleting a resource can impact other resources that
depends on that resource. To check the dependency between different resources, you can select show local dependency.
What is the use of Dashboard?
It is a data visualization tool that is used basically for higher level management for reviewing the data in graphical form as it is more simple to understand for them.
What steps do you follow to connect dashboard to universe?
While we use IDT, in that from the query browser we have to first click on the add query option and then universe as the data source. Then select the published Universe and to add objects, you can use Query panel.
When query is added, you can do mapping of objects to spreadsheet and later to Dashboard components.
Is it possible to set dashboard to refresh after a specific time period?
Yes, Refresh before components are loaded and Refresh every time duration.
Where do you define color setting for a component in data model?
You can set color for each element of a component in data model. Colors can be defined in Appearance tab under Properties pane. There are wide range of colors available and you can also create your own custom colors.
To define color for each element, select the element → Go to Color Selector for each element. You can select below sections in color dialog box −
Theme Color − To define color of current theme.
Standard Color − These are group of basic colors.
Recent Color − This shows recently used colors.
What is the difference between cluster and non-cluster index?
Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A non-clustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.
What is auto increment?
Auto increment keyword allows user to create a unique number to be generated when a new record is inserted in to the table. It is of 2 types –
AUTO INCREMENT keyword – in ORACLE.
IDENTITY keyword – SQL server.
Mostly these keywords are used when there is a primary key.
 What do you understand by ‘linking’ of universe in Business Object?
Linked universe are the universe which share common components such as objects, classes and the joins between the tables. When 2 universes are linked then one of the universe is called as core universe’ and the other as ‘derived universe’.
There are 3 approaches while linking the universe. These are – kernel, master and component.
Kernel – in this one universe contain the core components which can be added to the derived universe. In the derived universe it contains the components of core universe. Any changes made in the core universe will show effect in the derived universe.
Master – it holds all possible components. In this certain components are hidden depending on their relevance to target users. The components visible in the derived universe are always a subset of master universe and any changes made to the kernel universe are automatically reflected in all the derived universes.
Component – merging of 2 or more universes into 1 universe.
Process –

• Let’s we have 2 universe like loan universe and the credit universe. In this we can link the credit universe (core) into the loan universe (derived).
• For this go to loan universe à universe parameters à link tab à click add link and then select the core universe.
• Then after linking the core universe components will appear dim or highlighted in derived universe.
• Then save the derived universe and export both the core and the derived universes to the same repository.

 What are the types of security profiles in Information Design Tool?
There are 2 types of security profile that can be used in IDT, one is Data security and another is Business security.
Data security can be applied over objects in data foundation layer of IDT. In this type of security we can define things like connection, control, SQL, rows and tables. Security setting in the profile determines what objects, data and the connection the user view when connecting the universe.
Ex – to restrict data retrieved in a query to the current user, user the BOUSER variable in the where clause: WHERE EMPLOYEES. EMPLOYEES _ NAME = @Variable (‘BOUSER’)
Another security option is there in IDT which is Business security, which is defined on an object to grant or deny the business layer objects in the query panel. Ex- dimensions, measure, filters, prompt, attributes etc.
We can define the types of business securities like create queries, display data and filtered data returned in queries.

Business Objects: Web Intelligence

WEBI - (WEB INTELLIGENCE)

It is a designing tool and hence we generate BO reports through it. I have used this tool till date and part from it i have rarely gone for Lumira.
So before starting this i would like to inform you that the topics that i am going to say you in this which are listed as follows -

1. FILTERS
2. SUB QUERY
3. INPUT CONTROLS
4. CONDITIONAL FORMATTING
5. SORTS
6. BREAKS AND SECTIONS
7. CHARTS IN WEBI
8. HYPERLINK
9. MERGING
10. RANKING
11. TRACKING
12. SCOPE OF ANALYSIS
13. COMBINED QUERY

Above mentioned things are important as per me and one who design reports with this tool he or she will come across often these things time to time.

SO LETS BEGIN -

Filters -

these are the option available in business intelligence to filter a data or we can say refine a data at report level i.e. in web intelligence a.k.a WEBI.
Following are the type of filters that I have known and used so far in my career in my previous projects.

1. Predefined filters-

These are the filters which are created during the time of universe creation and directly be used in report level. The main benefits of these filters is that once it is been created at the universe level after that there is no need for creating custom filters furthermore at report level at WEBI. To add a predefined filter one can drag or double click the filter to query panel – then filter pane. After that when we run the query corresponding to the data with respect to filters will be added to the report.

2. Custom filters –

These are the filters that are created at the report level. There is 2 ways by which I could create them one is before running the query and another is after running the query.
The main purpose of using the filter is for hiding the data from the specific user.
To create a custom query filter add the object you want to use in the filter then drag it query filter pane and then click the arrow next to default operator and finally select the operator such as in list, not in list, equal to, not equal to, greater then, greater then equal to, less then etc.…
Then click the arrow on filter type and now we can select the following filter types which are listed such as constant, value from the list, prompt, object from the query.
Then select the value that you wish to include in the filter. And for removing the filter select the filter and press the delete button and further more if you like to remove all the filters then click remove all option that is present at top right corner of the screen.

3.Quick filters –

The main objective behind using this filter is that to retrieve the values quickly you want to have and in this way there is no need of using the filter editor.
For this select add quick filter which is present at top right corner of result object pane.
Then a new dialog box will get open and now we have to finally select the list of values i.e. LOV that we like to use in the quick filter.

Example-

there are several years in a report such as from 2000-2017 and if we like to get only the years 20016-20017 then we can use it by selecting these both years.
And to delete this filter select the filter in query filter pane and press delete button.

4.Prompts –

I can define as a special filter for a user in business intelligence which allows the user to enter a value every time data is refreshed in the document. This filter helps in performance tuning.
Following are the elements of prompt which are an object, an operator and message.
Ex – suppose we are the taking the current year i.e.2017 so in this we can say current year equal to (“2017”).

So in this                            current year ------------- an object

                                           Equal to – -------------an operator  

                                          Prompt message is------- (“2017”)

A prompt can be applied to dimension, measure, attributes, hierarchies etc.
More over to it we can also do use of and”, ör”operators here to create multiple prompts in the same query.
Note – “while suing IDT AND Bex query we can only use “AND”OPERATOR WITH THE PROMPTS.”

Steps involved in creating a prompt are as follows –

1. To create a prompt add the object that you like to filter with a prompt to query filter pane.
2. Select the filter operator from the list and click on the last arrow mark to select a prompt
3. Then we can add the text message for prompt value and run the query.
4. While we run the query prompt dialog box will allow us to enter the values as per the selected operator.
5. When the values are selected just click ok and data for selected values in the document will be reflected in the report.
6. When we refresh the document in WEBI during that time prompt will appear every time to the select the values.
7. There are conditions when we have prompt already applied or we can say that existing prompts in a report then in that scenario
8. Drag the object on which prompt has to be applied to query filter pane.
9. Then select from universe by selecting the existing prompt and press ok button. It will then display the list of all the prompts which are compactable with the objects in the query filter.

There are few types of prompt which are –

 Merged prompts –

when we are using multiple data provider in that time we have to use merge option to get the data into one report. So in this type of scenario we can use merge multiple prompts with same data type , same operator and same prompt text are been merged. So while we will refresh the data providers then one prompt will only appear for all the data providers.

Hierarchical prompts –

we can create this where the objects like hierarchy,   levels, and dimension associated with hierarchical LOV are been displayed.
It is displayed in a tree form and we can navigate up and down in the tree. As per filters in the prompts we can select items from different level of LOV.
In my last project I have even combined prompts with the query filters which have helped me to limit the data in the document and helped me in performance tuning.

Example

I had added query filter for department and the year and the prompt for a specific employee name.

State not equal to Orissa

Year 2017

Which category

So now when we run the query it will ask for enter the value of category so in webi it will filter the data as per filter in the query panel and display the result as per prompt value.
One if like to define order of prompt then we have to go to query properties at the top and from there we can select the order of prompt as per our need.

SUB QUERY –

it helps in filtering of the data as compared to other query filters. Using this we can limit the values returned with WHERE clause. These sub queries work on basis of SQL which is used to retrieve the query data. If the database doesn’t support SQL then we can’t able to see the option of sub query at query panel It only be made on dimensions and not on measure objects or attributes.

Steps for making sub query

1.Select an object in the result pane on which we like to make a sub query.
2. Then it will add sub query outline in the query filter pane. Now if we like to add a WHERE condition then drag and drop the objects to the area of sub query.
3. Then after this select the operator value used to filter object in WHERE condition. We can add multiple queries to query panel. We can also use her and, or operator relationship between sub queries.
4. By default we have 2 sub queries always linked with the AND relationship. For this drag a sub query to the area and drop an object.

Input controls –

Input controls are there to filter and analyze the report data. I have defined input controls using text boxes and radio buttons. These are associated with report elements like tables and section headers and use control to apply filter on report elements.
When we select a value for input control it will filter the values in the report element that is associated with the input control by selected values.
Input controls can also be used on hierarchical data.
For using this one must be in design mode of WEBI.

Go to analysis – filters – input control – define control.

It will then open define input control dialog and select the report object used to filter data and click “next”.

Example –

suppose if we have selected a Report element and then we can select Define Control, we will get an option to include objects from selected block only. If we select this checkbox, it will show us objects only from the selected report element.
Then after this we will get a option choose and define control. Choose control allows us to select different control types like single value or multiple values. Define control allows us to select the name of control, description, values in control to select, operator and number of lines displayed in input control, etc.
In the next window, you have to assign Report Elements. It will allow you to choose from report elements. You can assign report elements to input controls.
Click ‘Finish’ and input controls will be added to the left pane under Input Control tab.
If further if we like to edit the input control then
To edit an input control → Select Input control from the left pane → Click Edit icon.
It will open Edit Input Control dialog box. Make changes to Input Control and click Ok.

Steps to define tables and charts as input control

Select the table → Right-click and select linking → Add Element Linking.
You can select a single object or all objects to define as filtering objects. Click ‘Next’.
You can enter the name and description of input control. Click ‘Next’. Once you click next, you can select any other block to use this object as input control. Click ‘Finish’.

Conditional formatting – (alerters)

Conditional Formatting is used to highlight some specific values in the report. If you want you can show specific low or high values with some specific colors. Conditional formatting can be applied to the following elements −

• Rows in horizon tables

• Columns in vertical tables

• Cells in forms and cross-tables

• Sections

• Free holding cells

• Condition formatting can be used to make the following formatting changes − Text color, size and style, cell border, color, size and style, and cell backgrounds.

• You can add up to 30 conditional formatting formulas in a Webi document.

  

Steps –

1. First open the report in design mode and we must use rich client as it will not work in web mode.

2. Go to Analysis → Conditional → New Rule. It will open formatting rule editor.
3. Enter the rule name and description. Select the cell contents in filtered object or cell box.
4. We can select Operator and Operands value. You have to define text formatting in Format tab. Click on Format to set the formatting of the tab.
5. We can select the font, font style, size, effects (underline, etc.) and alignment. Once formatting is defined for condition, you have to click Ok.
6. Now to apply conditional formatting to the object, select the column in the report. Go to formatting rules drop down → Select the conditional formatting rules checkbox you have created.
7. Conditional formatting will be applied to the desired cells. We can add multiple conditions in a single formatting rule on multiple objects.

Sorts –

It can be applied on tables on section. And in charts to get organize the data in specific order in a WEBI report.
By default it is set in order i.e. left to right order of objects of query panel.

Example -

Ascending numeric order for numeric data
Alphabetical order for alphanumeric data
Ascending − in this, smallest value at the top to highest value at the bottom.
Descending − in this, highest value at the top to smallest value at the bottom
Apart from default way of sorting we can even customize it as per our wish. Custom order of sorting is available on dimension and attributes and not at hierarchy and measures.

Steps –

1. To insert a sort, open report in Design mode. Select the Column you want to sort.

2. Then Go to Analysis → Display → Sort → Ascending/Descending.
3. You can apply multiple sorts in a single table on different columns. You can use ‘Remove All Sorts’ option to delete all the sorts.
4. To manage the Order of Sorts, go to Advance tab. You can define order for all sorts using up and down arrows. You can define Custom Sorts here if no other sort is applied.

Example –

suppose we have sales report of XYZ of ABC state and what all its raw material which is used in its company such as manganese, cobalt, zinc, aluminum etc.
So while we apply sort we have 2 options i.e. ascending or descending order and we can choose any one order and it will arrange them in required order of alphabets.

Breaks  & Sections–

• Breaks are used to define all data for every unique value of an object in separate parts. Using Breaks you can apply – Subtotals and Sub aggregations and Display data in more effective manner.

Break vs Sections –

Section breaks up the data into multiple cells that are called section headers and each section header contains a value for dimension and data corresponding to dimension value.
Break is used to divide the data into one block and each column carries dimension, attributes and measures. These values are repeated for each row value in the block.

Steps to insert breaks –

1. First select the column in which you want to insert a Break. Go to Analysis → Display → Break → Add Break.

2. This will divide the table into many mini tables as there are unique values in the column.
3. If we like to manage breaks that we have applied then steps for it are as follows –
4. Go to Analysis → Display → Break → Manage Break. It will open a new window and show all the breaks in the table.
In WEBI we can even filter of data and by doing so we can limit the data displayed in a WEBI document. We must have to mention elements such as filtered objects, operators, filter value, to create report filter.

The basic difference between query and report filter is that query filters are defined at query panel and these are used because of limiting the data retrieved from different data source and get back to WEBI report. Whereas report filters are the filters which are used for hiding the data in a table, report, chart, section present within a WEBI report.

We have various operators which are been used in a WEBI report such as equal to, not equal to, greater then, lesser then, between, in list, not in list, is null, is not null etc.

Steps in applying a filter are as follows –

1. First Select the Report Element you want to apply a filter. Go to Filter → Add Filter.

2. It will open the Report Filter dialog box. You can add objects, operand and value to apply filter at the report level.
3. Using the functions − Add Filter, Remove or Remove All − you can add or delete filters accordingly.
4. To Edit a Filter → Go to Analysis Tab → Filter → Edit Filter.
5. You can make changes to filters in Report Filter Dialog box. Click Ok to apply.

Charts in WEBI –

There are multiple types of chart available in WEBI which help us in graphical representation in a report that we are making.
Bar chart - charts are used to compare similar groups of data and they display data in rectangular form horizontally.

Box plot chart - it is a graphical display of a five number summary based on distribution of a dataset: the maximum, the minimum, the first quartile, the third quartile, and the median. It can also show abnormal values called outliers.

Column charts - charts are constructed of vertically-oriented rectangular bars. The height of the rectangles are proportional to the values associated with different category items.


Line charts - XY chart that displays lines connecting plots. Value axis plot positions are expressed by analysis category items. The second value axis plot positions represent the associated values.

Map charts - chart displays values within nested rectangles that can be colored. The levels of nesting correspond to the levels of hierarchy breakdown. The size of rectangles and their color both express a set of values.

Pie chart - circular chart made up of sectors. The area of circle represents a whole, and the sectors of circle represent the parts of a whole.

Waterfall - is used to show the cumulative effect of values of a measure and each bar starts with the level of previous one.

Steps of adding chart to a WEBI report- it is of 3 ways. First way is -

Go to Report Element → Chart → Select a Chart and click on the report area where you want to insert a chart.

Chart is greyed when there is no data assigned to it. To assign data, you can drag the object from the list of available objects to chart axes.

Second way is –

Right-click → Insert → Select the Chart type as shown in the following screenshot. Click the report to add a blank chart and then assign data by dragging objects from the list of available objects.

Third way –

Another method is by converting a table into chart. You can do this by selecting a table in a Webi report. Right-click on a column → Turn Into → Select a Chart. In this way tables will be converted to column chart.

Merging –

According to me merging is a feature which is available in WEBI which allows linking of 2 or more data providers (query).
Basically we do merging from 2 or more queries one WEBI report so that we can use them in same block. The primary thing in merging is that data must be of same type.
It is basically of 2 types – extended merging and force merging.
EXTENDED MERGING –
It is a condition where dimension do not have same nature and features and still merge.
For example – we have 2 data providers which are –
DP – 1 CITY AND SALES REVENUE.
DP2 – 2 COUNTRY AND CITY

CITY	SALES REVENUE
LA	$1000
NY	$2000
JAPAN	$3000

COUNTRY	CITY
USA	LA
USA	NY
JAPAN	TOKYO

If auto merge option is enabled than city dimension will merge from both data providers and report will get result as,

COUNTRY	CITY	SALES REVENUE
USA	LA	$1000
USA	NY	$2000
JAPAN	TOKYO	$3000

Force merging – it is a condition where measure objects are been merged.
Example – for creating a report for country wise sales without cities in that then we know that there is no direct link between country and sales. For this we do force merging function for sales measure.

 FORCE MERGE ([SALES REVENUE])

COUNTRY	SALES REVENUE
USA	$3000
JAPAN	$3000

 Here in the above table when we are applying force merge function than in case of USA it is adding up the entire sales revenue of USA country i.e. total of NY & LA which is equal to $3000. Where as in japan there is only Tokyo city and its sales revenue is $3000.

FUNCTIONS 

@PROMPT	@prompt(‘message’,[‘type’],[lov],[mono],[multi],[free],[constrained])
@SELECT	@select(class name\object name)
@WHERE	@where(class name\object name)
@VARIABLE	@variable(‘my name’)
@SCRIPT	@script(‘var name’,’var type’.script name)

DIMENSION MODELLING –

Logical design technique to present data in standard frame work to allow for high performance access.
Every dimension model is composed of one table with multi part key called as fact table and set of smaller table called as dimension table.

DATA TRACKING –

WEBI have a feature to track data change by highlighting the change data and display the previous values of dimension or fact along with current values. It highlights the changed data according to parameter we set, in the option auto update current data becomes the reference data after each data refresh and other option is used the current data as reference data. The report always show the difference between the most current data and this fixed reference data.
We can track following types of data change such as –

• Inserted data • Deleted data • Changed data • Increased values • Decreased values

Example - If sales have decreased in a region, data tracking displays the decrease. You can then drill down into the data for the region to understand why revenue is falling.
It can be done in 2 ways, these are as follows –
Automatic - In automatic data tracking mode, you always compare the current data with the data before the last refresh. This is achieved by automatically setting the current data as the reference data just before each refresh. The reference data is always one refresh behind the current data.
Manual - In manual data tracking mode, you select the reference data. You continue to use this data as a reference point until you update the reference point.

Steps –

1. Click Track Changes on the status bar at the bottom of the window to display the "Data Tracking" dialog box.
2. Select the Data tab.
3. To compare the data with the last refresh, select Compare with last data refresh. When you select this option, the current data becomes the reference data after each data refresh.
4. To compare the data with a specific data refresh, select Compare with data refresh from and select the date of the data refresh from the list.
5. Select the reports that you want to display data tracking from the Reports with data tracking shown list.
6. Click Refresh data now to refresh the data when the dialog box closes.
7. Click OK

SCOPE OF ANALYSIS –

The scope of analysis for a query is extra data that you can retrieve from the database to give more
Details on the results returned by each of the objects in a query.
In the universe, the scope of analysis corresponds to the hierarchical levels below the object selected
for a query. For example, a scope of analysis of one level down for the object Year, would include the
object Quarter, which appears immediately under Year. The hierarchies in a universe allow
us to choose your scope of analysis, and correspondingly the level of drill available.

Steps involved in this are as follows –

1. Click the Show/Hide Scope of Analysis Pane button so that it appears pressed in.

2.The Scope of Analysis panel appears at the bottom of the Result Objects pane. The default scope of analysis is none. Each dimension in the Result Objects pane appears in the Scope of Analysis pane.
3.Click the down arrow in the Scope of Analysis drop-down list box.
4.Select a level for the scope of analysis. The level appears in the list box and the dimensions that are hierarchically below each dimension in the Result Objects pane appear in the Scope of Analysis pane.
5.If you want to add selected dimensions to the scope of analysis or create a custom scope of analysis, select dimensions in the Query Manager and drag them across to the Scope of Analysis panel.

COMBINED QUERY –

A combined query is a group of queries that work together to return a single result. You can combine queries in three relationships:• union• intersect• minus
A UNION query takes all the data from both queries, eliminates duplicate rows, and builds a combined data set.
An INTERSECT query returns the data that is common to both queries.
A MINUS query returns the data in the first query that does not appear in the second.

Steps –

1. Create an initial query in the query panel.
2. Click the Add a Combined Query icon on the toolbar to display the Combined Queries panel under the list of query objects. The Combined Queries panel shows the current query. You can change the query name by clicking the query in the panel, selecting Edit Name from the menu, then typing a new name in the Name box in the dialog box that appears.
3.  Click Add a query to add another query. The second query appears in the Combined Queries pane and is:

• Combined with the original query in a UNION relationship.
• Named Combined Query #n.

4. To switch to a query, click it in the Combined Queries pane.
5. To delete a query select it in the Combined Queries pane and press the Delete key, or drag and drop the query to the universe outline.

6. To change the combination type, double-click on the operator. The operator moves through the sequence UNION, MINUS, INTERSECT.

7. Build each query within the combined query as you build any normal query.
8. Click Run Query.

STEPS FOR RANKING WITH EXAMPLE.

RANKING – STEPS

1. Add the objects that you want to appear in your query to the Result Objects pane of the query Panel.

2. Click Add ranking on the toolbar at the top of the Query Filters pane. The ranking outline appears in the Query Filters pane.
3. Select the ranking direction and type (Top, Top%, Bottom, Bottom %).
4. Type the number of records (if you selected Top or Bottom) or percentage of records (if you selected Top% or Bottom %) you want the ranking to return in next box.You can specify a prompt instead of a constant by clicking on the arrow next to the number and selecting Prompt. When you select a prompt the user must enter the ranking number when the query is run.

5. Drag the ranking dimension to the box to the left of the Based on box.
6. Drag the measure on which you want to base the ranking to the Based on box.
7. Drag the dimension that provides the calculation context for the measure to the Ranked by box. This dimension is optional. To display the Ranked by box, click the arrow to the right of the Based on measure.

8. Drag any dimensions that you want to include in the WHERE restriction to the area at the bottom of the ranking.

9. Click Run Query.

EXAMPLE – (10 employee by salary in each dept.)

The query panel is open.

1. Drag the Department, Employee Name, and Salary objects to the Report Objects pane within the Query Panel

2. Click Add ranking at the top of the Query Filters pane. An outline database rank is added to the Query Filters pane.

3. Rank the top 10 employees in descending order by setting the ranking direction/type to Top, and the number of records to 10 in the box next to Top.

4. Rank employees by dragging the Employee Name object to the box to the right of the number of records.

5. Base the ranking of employees on salary by dragging the Salary object to the Based on box.
6. Click the arrow next to the Based on measure if the Ranked by box is not already visible to display the Ranked by box.

7. Rank employees based on salary by department by dragging and dropping the Department object to the Ranked by box and run the query to return to the ranking.

STEPS FOR ADDING HYPER LINK IN WEBI -

1. Select the cell and select, right-click and select Linking > Add Hyperlink to display the "Create Hyperlink" dialog box.

2. Select the Link to web page tab.
3. Type or paste the hyperlink text into the box.
4. Click Parse to extract the hyperlink parameters into the Customize URL parameters area (which is not visible until you click Parse).

Dynamic hyperlinks contain parameters whose values can change. Parameters appear as name=value parts at the end of the hyperlink after the question mark. For example, the URL – http://salesandproductreport/default.asp?reportname=products contains one parameter, report name, whose value is “products". After you click Parse, each parameter appears on a separate line with the parameter name on the left and the parameter value on the right. The static part of the hyperlink (the part without the parameters) appears in the Main section.

5. To supply data from formulas or variables as parameter values, click the arrow next to each parameter value and select an option.
6. To add or remove a parameter, modify the hyperlink syntax, then click Parse.
7. Click the arrow next to Cell content to change the text displayed in the hyperlink cell and choose one of the options.
8. Type the tooltip text in the Tooltip box or build a dynamic tooltip by using the Build formula or Select variable
9. Click the arrow next to Target window to define how the target URL appears.