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 |
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. |
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.
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 |
| COUNTRY | CITY | SALES REVENUE |
| INDIA | MUMBAI | $2000 |
| INDIA | CHENNAI | $6000 |
| JAPAN | TOKYO | $5000 |
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 |
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 |
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 |
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 |
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 5th 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 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 |
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 |
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 1st table and dimension key in 2nd 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.
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.
No comments:
Post a Comment