OBIEE Interview Questions Part - 8

1. Explain the Architecture of OBIEE 11g and function of each components?

OBIEE Comprises Presentation Services, Oracle BI Server:
The user constructs sql and passes it to the Analytic Engine and then the Oracle BI (Analytic Engine) describes the physical sql to the Data sources and recover the data back to the Engine and presents to the presentation Services .

2. How we can extract sql from OBIEE for reports?

There are many ways to extract the sql:
a. Change the request and click Advanced in that you get xml code and also the actual sql.
b. In the catalog Manager click Tools-Create Report. In the Create Report Window –> Click Request SQL and save the sql to the physical path in your PC.
c. Enable Log level to 2 in the OBIEE 11g Administration Tool from Manage-> Security and enable the log level to 2 by clicking properties for the user, then go to the NQQuery.log in BI_HOME/OracleBI/Server/Logs.You will find the SQL for that User.
d. By clicking Administration->Manage sessions-> view sql.

3. How can you sort in Reports in OBIEE 11g?

Click on modify and then click on sort (order by icon) on the relevant column in the criteria pane.

4. How we can do different types of narrative Reports in OBIEE?

By clicking modify request and Narrative View and by giving @1 for the first column result and @2 for the 2nd column and so on and we can also give a heading for No Results by clicking the Narrative view.

5. How will you create Interactive Dashboards?

By clicking on Administration and Manage dashboards and by adding column selector we can create interactive Dashboard.

6. What is write-back in OBIEE?

We can give a column as updatable and then view the reports, this option is called write back option.

7. How will you execute Direct SQL in OBIEE?

By clicking Direct Database Request below the subject area in us can execute Direct SQL in OBIEE.

8. How OBIEE Developer can create report from two subject areas?

From the Criteria Pane of the Report Created from First Subject Area
come to the bottom of the page and click combine request. By this we can create report from two subject areas.

9. How we can Port changes for dashboards, reports, rpd from development to production?

For the RPD we can use the Merge option in Admin Tool and for dashboards and reports we can use Content Accelerator Framework.

10. What are the different types of variables in OBIEE 11g? Explain

There are two types of variables in OBIEE 11g.
a. Repository variable:
This variable is used for the whole repository.
b. Session variable:
session variable are of two types: system variable and non-system variable.
System variable uses NQ_SESSION. Examples of non-system variables are user defined filters.

11. How will you enable or disable caching in the system level and table level?

In the NQSConfig.ini file use ENABLE under CACHE Section for System Level
For tables, if we want to enable the cache at table level, open the repository in offline mode this should be different from the current repository and click enable or disable the cache.

12. How will you go about adding additional column to the repository in the presentation layer?

Check if the table is already existing if so add in physical layer, then click on BMM and then Presentation layer, then reload server metadata, then it will be visible to all users.

13. How will affect the changes for a report, if for certain users only the column heading in the report should be changed?

Using session variables for that user.

14. What is a table alias in OBIEE 11g? Where and how will you create it?

Table alias is used for creating self joins. Table alias can be created by right clicking the table in the physical layer then click alias.

15. Have you created Hierarchy in OBIEE 11g? If so where and how?

Yes, we can create hierarchy in BMM Layer of OBIEE in dimensions for the dimension tables. This can be done by right clicking the dimension table and click create dimension and then we can manually define the hierarchy and its levels.

16. What is Level Based Metrics? How we can create it?

Level-base metrics means, having a measure held at a certain level of the dimension. Examples Monthly Total Sales or Quarterly Sales.
To create a level based measure, create a new logical column based on the original measure .Drag and drop the new logical column to the appropriate level in the Dimension hierarchy.

17. What are the different layers of OBIEE 11g Repository?

• Physical Layer
• Business Model
• Mapping Layer
• Presentation Layer.

18. What is Authentication? How many types of authentication.

Authentication is the procedure by which a system confirms, through the use of a user
a. Operating system authentication
b. External table authentication
c. Database authentication
d. LDAP authentication

19. What is a bridge table?

If we want to connect two tables where there is no relationship we can use a third bridge table for connecting them which will have same columns in both tables.

20. How Time based triggered report can be generated?

Using Scheduler – ibots we can generate time based triggered report.

21. What is ibot?

Ibot is an arrangement agent, used to arrange reports to be sent across many devices such as email, pager, mobile, other devices etc.

22. What are different types of joins that are possible in OBIEE RPD?

1. Complex Join –it uses multiple conditions, such as A.ROW_WID = B.ROW_WID AND / OR A.A_WID = B.B_WID
2. Natural Join

23. How we can have two different columns from two different tables or subject areas?

Using confirmed dimensions, we can fetch many metrics across various facts, but the join should be of same level of data detail.

24. How can we use saved filters and where we will save the filters?

Shared Folders-XYZ Folder- It have two folders
a. Prompts
b. Reports

25. Have you worked in dashboard? When we run a dashboard? How will we stop the dashboard report run automatically?

By clicking on the cancel button, we can stop a exact report placed on the dashboard.

26. Define surrogate key.

A surrogate key is an artificially generated key, usually a number. A surrogate key, in the level aggregate table, simplifies this join and removes unnecessary columns from the fact table, resulting in a smaller-sized fact table.

27. Define repository in terms of Siebel Analytics.

1. Repository stores the Meta data information. Siebel repository is a file system, extension of the repository file. rpd.
META DATA REPOSITORY
2.With Siebel Analytics Server, all the rules needed for security, data modelling, aggregate navigation, caching, and connectivity is stored in metadata repositories.
3. Every metadata repository can store many business models. Siebel Analytics Server can access many repositories.

28. Explain the end to end life cycle of Siebel Analytics?

Siebel Analytics life cycle
1. Collect Business Requirements
2. Recognize source systems
3. Plan ETL to load to a DW if source data doesn’t exist.
4. Build a repository
5. Build dashboard or use answers for reporting.
6. Define security
7. Based on performance, decide an aggregations and/or caching mechanism.
8. Testing and QA.

29. How does Siebel Architecture works? Explain the three layers.

There are five parts of Siebel Architecture.
1. Clients
2. data sources
3. Siebel analytics Web Server data sources
4. Siebel analytics scheduler
5. Siebel analytics server
o Metadata represents the analytical Model which is created using the siebel Analytics Administration tool.
o Repository divided into three layer
1. Physical – Signifies the data Sources
2. Business – copies the Data sources into Facts and Dimension
3. Presentation – Specifies the user’s view of the model; rendered in Siebel answer.

30. If we have 3 facts and 4 dimension and we need to join would you recommend joining fact with fact? If no than what is the option?

In the BMM layer, we can create one logical table (fact) and add the 3 fact table as logical table source.

31. What is connection pool and how many connection pools did we have in our last project?

Connection pool is needed for each and every physical database.
o It contains material about the connection to the database, not the database itself.
o We can use either shared user accounts or pass-through accounts -Use: USER and PASSWORD for pass through .
o We can have many connection pools for each group to avoid waiting

32. Define JDK and why do we need it?

Java Development Kit (JDK), is a software package that contains the minimal set of tools needed to write, compile, debug, and run Java applets.

33. Oracle doesn’t recommend Opaque Views because of performance considerations, so why/when do we use them?

An opaque view is a physical layer table that contain select statement. An opaque view should be used only if there is no other option.

34. Can we migrate the presentation layer to a different server?

No we can’t migrate the presentation layer to a different server rather we have to migrate the whole web & rpd files

35. How we can identify the dimension tables and how we can decide them during the Business/Data modeling?

Dimension tables contain descriptions about data analysts which is used as query in the database. Every table contains a primary key that contains one or more columns; each row in a table is uniquely identified by its primary-key value or values

36. Why do we have multiple LTS in BMM layer? What is the purpose?

To improve the performance and query response time we have multiple LTS in BMM.

37. What is the full form of rpd?

There is no full form for rpd as such, it is just a repository file which is known as Rapidfile Database.

38. How we can disable cache for only 2 particular tables?

We can disable cache in the physical layer, by right clicking on the table there we can have the option which define cacheable

39. How we can split a table in the rpd given the condition. (The condition given was Broker and customer in the same table) Split Broker and customer.

We have to make a dubbed table in the physical layer.

40. What type of protocol did we use in SAS?

TCP/IP type protocol.

41. Can we have multiple data sources in Siebel Analytics?

Yes, we can have multiple data sources in Siebel Analytics.

42. How we can deal with case statement and expressions in siebel analytics?

By using expression builder we can create case statement when…then..end.

43. What do you know about Initialization Blocks?

o Init blocks are used for instantiating a session when a user logs in.
o To make dynamic variable we have to create IB to write sql statement.

44. Explain query repository tool?

o It is utility of OBIEE /Seibel Admin tool
o allows us to examine the repository metadata tool
o It Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer
o for example: search for objects based on name, type.

45. How can you bypass siebel analytics server security?

It can by-passed by setting authententication type in NQSCONFIG file in the security section as:authentication_type=bypass_nqs.instanceconfig.xml and nqsconfig.ini are the 2 places

46. Where we can add new groups and set permissions? Explain what is R?

We can add groups by going to manage>security>add new groups> we can give permissions to a group for query limitation and filter conditions.

47. What are the things we can do in the BMM layer?

We can do Aggregation navigation, level base matrices, time series wizard, create new logical column, complex join.

48. Where are passwords for userid?

Passwords for userid are in siebel analytics server repository.

49. Define pipeline.

Pipelines are the stages in a particular contract, valuation, economics etc.

50. What are the Key Configuration Files in OBIEE?

The key configuration files in OBIEE 11g are NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml

51. Give a brief introduction of OBIEE?

Oracle BI Enterprise Edition (sometimes simply referred to as Oracle Business Intelligence) provides a full range of business intelligence capabilities that allow you to:
• Collect up-to-date data from your organization
• Present the data in easy-to-understand formats (such as tables and graphs)
• Deliver data in a timely fashion to the employees in your organization

52. What Is the Oracle BI Presentation Catalog?

The Oracle BI Presentation Catalog stores the objects, such as analyses, dashboards, and KPIs, that you and other users create using Oracle BI EE. Users have their own personal folder (My Folders), where they can store the objects that they create. The objects in a personal folder can be accessed only by the user who created and saved the content into that folder. Users can add sub-folders to their personal folders to organize their content in the way that is the most logical to them.
You can also store objects in shared folders where other users or groups can access the objects. A combination of business logic security, catalog object security, and data level security determines who has the ability to view data and objects, edit objects, and delete objects from the catalog. Your administrator creates and maintains the catalog’s shared folder structure.

53. What is OBIEE 11G?

Oracle Business Intelligence Enterprise Edition (OBIEE) 11g is the most significant release of OBIEE since 2006.

54. Explain the main features of OBIEE 11G?

Oracle Business Intelligence is now more integrate with the Oracle Fusion Middleware software family.
Oracle WebLogic Server replaces Oracle Application Server and Oracle Containers for Java (OC4J).
Oracle Business Intelligence 11g Installer replaces the installer that was used in previous versions of Oracle Business Intelligence. It can perform software installation and configuration steps in the same process or separately in their own processes
This release add the following administration command-line utilities:
Oracle WebLogic Server scripting tool (WLST) for managing the Oracle WebLogic Server domain.
OPMN and the opmnctl commands for the Oracle Process Manager and Notification Server.
A database repository must be created before installation of the components with the Repository Creation Utility (RCU) tool
Log centralization via the web log viewer
In addition, Oracle Business Intelligence is now deployed in a clustered configuration by default. Because of this, the default ODBC DSN for the Oracle BI Server points to the Cluster Controller by default, rather than to the Oracle BI Server

55. Explain Oracle Business Intelligence Applications?

An Oracle BI application consists of pre-built, industry-specific Oracle BI interactive dashboards and Oracle BI reports that are built using industry best practices and address key functional areas within an organization. Oracle BI applications are integrated with operational applications to provide business metrics in reports, in context with an organization’s business function and industry.
Oracle BI applications include Extract Transform Load (ETL) routines to extract, transform, and load data into the Oracle Business Analytics Warehouse.
• Security in Oracle BI:
OBIEE allows a high degree of control over access to elements in Oracle BI applications. The security mechanism comprises Business logic object security, Presentation Catalog object security and Data level security.
• Drilling Down in Oracle BI:
In OBIEE, you can drill down from a dashboard or report to an actual item in the database. For example, if you work in sales, you can drill down to the city level within a sales report, and observe that there is a large sale pending in Paris. If you are using an Oracle Siebel operational application, you can then drill down on the pending sale and go directly to that opportunity in the Oracle BI application.

56. Define repository in terms of OBIEE?

Repository stores the Meta data information. The extension of the repository file is “.rpd”.With OBIEE Server, all the rules needed for security, data modelling, aggregate navigation, caching, and connectivity is stored in metadata repositories. Each metadata repository can store multiple business models. OBIEE Server can access multiple repositories
Repository is divided into three layer:
• Physical – Represents the data Sources
• Business – model the Data sources into Facts and Dimension and apply business logic
• Presentation – Specifies the user’s view of the data rendered in OBIEE answers client

57. What is the end to end life cycle of OBIEE?

OBIEE life cycle:
• Gather Business Requirements
• Identify source systems
• Design ETL to load data to the Data Warehouse
• Build a repository
• Build dashboards and reports
• Define security (LDAP or External table)
• Based on performance, decide on aggregations and/or caching mechanism.
• Testing and QA.

58. What is the purpose of Alias Tables?

An Alias table is a physical table with the type of Alias. It is a reference to a physical table, and inherits all its column definitions and some properties from the physical table. A logical table source shows how the logical objects are mapped to the physical layer and can be mapped to physical tables, stored procedures and select statements. An alias table can be a reference to any of these logical table source types.
Alias Tables can be an important part of designing a physical layer. The following is a list of the main reasons to create an alias table:
• To reuse an existing table more than once in your physical layer (without having to import it several times)
• To set up multiple alias tables, each with different keys, names, or joins
• To help you design sophisticated star or snowflake structures in the business model layer. Alias tables are critical in the process of converting ER Schemes to Dimensional Sachems.

59. What is Authentication? How many types of authentication do we have in OBIEE?

Authentication is the process by which a system verifies a user with the help of a user ID and password. It checks if user has the necessary permissions and authorizations to log in and access data. There are 4 main types of Authentication in OBIEE:
• Operating system authentication
• External table authentication
• Database authentication
• LDAP authentication

60. What is aggregate navigation? How do you configure the Aggregate tables in OBIEE?

Aggregate tables store pre-computed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems.
Ex: If you observe that most of the queries are fired on aggregated values like yearly or monthly data rather that day level data then to speed up the report we can create an aggregate table containing month level and year level data and map it to the respective logical table and set the aggregation levels in the content tab of LTS.

61. Difference between a session and repo variable?

Session variables are similar to dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.
A repository variable has a single value at any point in time. Repository variables can be used instead of literals or constants in Expression Builder in the Administration Tool. The Oracle BI Server substitutes the value of the repository variable for the variable itself in the metadata.
Unlike a repository variable, there are as many instances of a session variable as there are active sessions on the Oracle BI Server. Each instance of a session variable could be initialized to a different value.

62. What kind of sources can be connected to OBIEE?

OBIEE can be plugged on Relational Databases, Cubes or Flat files, with the following constraints:
• The sources must be modelled as star schemas or snowflakes schemas in order to have an efficient and safe behaviour.
• In case of multiple sources directly in OBIEE used together, be aware that OBIEE will conduct the needed joins itself, and OBIEE is not a database.

OBIEE Interview Questions Part - 7

101: What are Left outer join, inner join. When do you use inner join and when Outer Join.

Outer Join

Definition: An outer join selects all of the records from one database table and only those records in the second table that have matching values in the joined field. In a left outer join, the selected records will include all of the records in the first database table. In a right outer join, the selected records will include all records of the second database table. One or more fields can serve as the join fields.

Inner Join

Definition: An inner join is a join that selects only those records from both database tables that have matching values. Records with values in the joined field that do not appear in both of the database tables will be excluded from the query. One or more fields can serve as the join fields.

·         The inner join is also known as an equi-join.

---------------------------------------------------------------------------------------------------------------------

102: Modes of RPD when & how can you access.

        --online: when the rpd is on server the we have to open it in online mode for editing.

                      Caching can be done only in online mode

        --offline:when the repository is not on server

---------------------------------------------------------------------------------------------------------------------

103: How many BI servers can one presentation server can point & vice versa.

---------------------------------------------------------------------------------------------------------------------        

104: On which port schedular is configured

                                --->9705

---------------------------------------------------------------------------------------------------------------------

105: How database authentication is performed.

        In NQSconfuig file # is placed in front of set Authentication_type='Database',removed that # and give the database name also.

---------------------------------------------------------------------------------------------------------------------

  106. Which databases have you worked

          Oracle,sqlserver

---------------------------------------------------------------------------------------------------------------------

107  Can you write PL/SQL

     Yes

---------------------------------------------------------------------------------------------------------------------

108  Do you know sub queries? What are the correlated sub queries?

         Sub Query

         A sub query is a query that lives inside a query. By query of course we mean a SELECT statement.In sub query  the  inner query is first executed, and the result is then fed into the outer query.

         Correlated Sub Query:

            A query is called correlated subquery when both the inner query and the outer query are interdependent. For every  row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

---------------------------------------------------------------------------------------------------------------------

109  What is Indexing?

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

Syntax to create Index:

CREATE INDEX index_name
ON table_name (column_name1,column_name2...);

---------------------------------------------------------------------------------------------------------------------

110  Tell me how exactly does indexing works.

Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.

---------------------------------------------------------------------------------------------------------------------

111  What is the difference between Union and Union All?

union is used to select distinct values from two tables

where as union all is used to select all values including duplicates from the tables

---------------------------------------------------------------------------------------------------------------------

112  What do you mean by surrogate key?

When creating a dimension table in a data warehouse we generally create the tables witha system generated key to unqiuely identify a row in the dimension. This key is also known as a surrogate key. The surrogate key is used as the primary key in the dimension table. The surrogate key will also be placed in the fact table and a foreign key will be defined between the two tables.

Surrogate keys are system generated keys. They are integers.

Surrogate keys are extremely useful when having type 2 data ( i.e. storing historical information)
For ex: Consider one has a table in which a person and his location are stored. Now when his location is changed and we want to keep a historical record of the same it is stored with a surrogate key that will help us to uniquely identify the record.

This is also a reason that OLTP keys are not used in the warehouse and a seperate dimension or surrogate key is maintained.

---------------------------------------------------------------------------------------------------------------------

113  What is the difference between Primary Key and surrogate key?

Primary Key: A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL.

Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value.

Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.
---------------------------------------------------------------------------------------------------------------------

114  Assume a scenario where I want to purge the cache as soon as the ETL completes. How could this be achieved in OBIEE

  Using event polling table

---------------------------------------------------------------------------------------------------------------------

115  Tell me about the scope of dashboard prompts.

-->There are two scpoes of dashboard Page And Dashboard.

Page prompt:The scope of the prompt is for that page only.
Report Prompt:The scope of the report prompt is for that report only.

Suppose, you have a Prompt on Year and you want to filter only the reports (that are prompted on Year) on that page on the selected value, go for page prompt. Reports on the other pages will not be affeted by this selection.

On the other hand, if you make this as a dashboard prompt, all the reports (that are prompted on Year) on all the pages (in the same dashboard) will be filtered on the selected value.

---------------------------------------------------------------------------------------------------------------------

 115 Suppose I want to deliver the reports (using iBots) to few people on their E-Mail IDs which are not in the repository. (These users are not OBIEE users). How this could be done using iBots?

---------------------------------------------------------------------------------------------------------------------

116  Tell me the syntax of Ago () Function.

                1)Ago Function--->Calculates aggregated value as of some time period

                                                    shifted from current time.

                ago(<measure>,<time level>,<number to shift>)

---------------------------------------------------------------------------------------------------------------------

117  What kind of views have you used in your project?

---------------------------------------------------------------------------------------------------------------------

118   How do you handle Snowflake schema in BMM Layer?

we will create a sinle logical table and in that we will create multiple lts and add the columns of the descriptive dimensions in that logical table.

---------------------------------------------------------------------------------------------------------------------

119:Creating Dimension Levels and Keys:

• A dimension contains two or more levels.
• The recommended sequence for creating levels is to create a grand total level and then create child levels, working down to the lowest level.
• Grand total level. A special level representing the grand total for a dimension. Each dimension can have just one Grand Total level. A grand total level does not contain dimensional attributes and does not have a level key.
• Level. All levels, except the Grand Total level, need to have at least one column.
• Hierarchy. In each business model, in the logical levels, you need to establish the hierarchy (parent-child levels). One model might be set up so that weeks roll up into a year.
• Level keys. Each level (except the topmost level defined as a Grand Total level) needs to have one or more attributes that compose a level key. The level key defines the unique elements in each level. The dimension table logical key has to be associated with the lowest level of a dimension and has to be the level key for that level.
Associating a Logical Column and Its Table with a Dimension Level
After you create all levels within a dimension, you need to drag and drop one or more columns from the dimension table to each level except the Grand Total level. The first time you drag a column to a dimension it associates the logical table to the dimension. It also associates the logical column with that level of the dimension. To change the level to be associated with that logical column, you can drag a column from one level to another.
After you associate a logical column with a dimension level, the tables in which these columns exist appear in the Tables tab of the Dimensions dialog box.
---------------------------------------------------------------------------------------------------------------------

119  What is Row Wise Initialization block?

The row-wise initialization allows us to create and set session variables dynamically. It is similar to external table authentication except here we have fixed number of columns (name, value) and each row for a particular user represents different name (variable name) and associated value (column value).
It allow to create session variable dynamically and set their values when session starts .
Name and value of session variable reside in external table that access through connection pool

---------------------------------------------------------------------------------------------------------------------

120  Can we join fact table with fact table?

   NO,If we will join fact with fact then it will consider one fact as dimension

---------------------------------------------------------------------------------------------------------------------

121  In pivot table, sorting of column can be done?

YES

---------------------------------------------------------------------------------------------------------------------

122  In pivot table, drill down and navigation is possible?

     yes

---------------------------------------------------------------------------------------------------------------------

123  Repository variable can have multiple values or not?

     NO

---------------------------------------------------------------------------------------------------------------------

124  How to pass parameters of column values from summary report to detailed report?

In the summary report in column formula we will make the value interaction to navigate(navigate it to detail report) and in the detail report make the column as is prompted.

---------------------------------------------------------------------------------------------------------------------

125  If name of Subject area is changed and it has 80 reports. Then how to access that reports? 

 --We can access those reports,but for the edining we have to go to advance tab we have tro give name of the new subject area.

---------------------------------------------------------------------------------------------------------------------

126  I want to know the difference between applying content of filter and referencing  the filter when using saved filters?

---------------------------------------------------------------------------------------------------------------------

 128. I have two dahboards ,say A and B.every time when I login I see dashboard A.Now I want to make dashboard B as default dashboard so that whenever I login I see dashboard B.

-->In settings—My Account-->In preferences set default dashboard as dashboard B.

---------------------------------------------------------------------------------------------------------------------

129. There are 400 values to be displayed in dashboard propmt,but if I select dashboard prompt type to be drop-down then at the max I can see 256 values,so I want to know what is the solution for this.

·         OracleBIdatawebconfiginstanceconfig.xml

·         insert the following piece of code <prompts><maxdropdownvalues>1000</maxdropdownvalues></prompts>. The value ’1000′ can be any value as per your need.

·         Save the file and restart your presentation server.

---------------------------------------------------------------------------------------------------------------------

130. I want to hide request on a dashboard page,how can I do this?

We can hide the request on a dashboard page,by adding javascript....

There are two ways of adding JS into the OBIEE dashboard:

If the script is for one page only - Enter the dashboard editor and edit your page. Add a new Text object from the bar on the left. It doesn't matter where you add it in the page. Click on its Properties button and write your script in the text box. Don't forget to start it with <script> and end it with </script>. Check the "Contains HTML Markup" box. There is no need to hide this object because it doesn't contain anything visual. Rename the text object and give it a descriptive name.

If the script is common to more than one page or may be in the future - Create a new request which contains only one column. It can be any column from any table, but I recommend you to use a column which doesn't have many rows (you can use a small dimension if you have one). Click on the Results tab and enter the Narrative view. Write your script in the Narrative text box. Like before, don't forget to start it with <script> and end it with </script>. Check the "Contains HTML Markup" box and enter zero in the "Rows to display" text box. You can save your request now. Every dashboard page that contains this request will run the JS you just written.

How to develop correctly with JS in OBIEE
Don't enter bunch of code in the request. It'll only get you messy once the length of the code will grow. Your code inside the dashboard should only call functions. For example, it can look like this:
<script>
if (myFunction)
{
myFunction();
}
if (mySecondFunction)
{
mySecondFunction();
}
</script>

You will write and maintain these functions in external JS file. Take the common.js file (search it under the OBIEE dir in the server) which is called in every page in OBIEE dashboards (don't forget to backup first!!) and add your code in the bottom of the file. Now you have a convenient place where you can edit your JS code. There is one last thing you need to know before proceeding - the browser caches the JS files so you'll need to clear the browser's cache every time you change your code. In Internet Explorer, you can do it in Tools -> Internet Options -> Delete Files -> Delete all offline content. You'll do it a lot while developing, so having a button that does that will be great. You can use Microsoft's IE developer toolbar or another developer toolbar.

---------------------------------------------------------------------------------------------------------------------       

131. I want to show table headings along with column headings.

                --->Go in table properties-Table view Properties--->Display table and column heading and select As separate rows.

---------------------------------------------------------------------------------------------------------------------

132.  I want to know the importance of Limited Values while using it for column filter prompt.

to  do the constarint on prompt.suppose w ehave taken two prompts state and city,if we will select particular state,the city under that state sould be displayed ...for that we use limited value option for the city prompt.

---------------------------------------------------------------------------------------------------------------------

133. I have two prompts defined on the same column.One prompt is having page scope and other propmt is having scope for dashboard,so i want to know which prompt will be applied to that column?

                --->Dashboard prompt which have dashboard scope will get applied to that column

---------------------------------------------------------------------------------------------------------------------

134. How to enable sorting of columns in request on the dashboard?

 In presentation services -->in table view properties-->we have this option

---------------------------------------------------------------------------------------------------------------------

135. How to add multiple navigation target to the column value?

                --->Add navigation target to the column interaction heading

---------------------------------------------------------------------------------------------------------------------136. What is the concept of Act As option in Dashboard Editor.

It acts as proxy

---------------------------------------------------------------------------------------------------------------------137. Dashboard prompt is present but still not filtering the report even though is prompted is applied.What can be the reason behind this?

---------------------------------------------------------------------------------------------------------------------

138. Can there be join between two business models?

          NO

---------------------------------------------------------------------------------------------------------------------

139. Can presentation catalog refer to more than one business model?

        NO

---------------------------------------------------------------------------------------------------------------------

140. What is Parallel dimensional hierarchy

---------------------------------------------------------------------------------------------------------------------

141. What is  Event Polling Table

Event Polling Table is serves as a message bus. You put the names of the tables for which the data has changed. The OBIEE server polls this table for update events and purges cache entries that reference those tables. It also removes those entries from the polling table – so avoid purging them at the next polling interval.

Oracle BI Event Tables

Oracle BI Event tables Utility allows us to Identify a table as an Oracle BI Event Polling table. An Event pulling table is a way to notify the Oracle BI Server that one or more physical tables have been updated.

To mark the table object as an Event Polling Table
1 Click on the Tools > Utilities menu item.
2 Select the option Oracle BI Event Tables from the list of options.
3 Click Execute.
4 Select the table to register as an Event Table and click the >> button.
5 Specify the polling frequency in minutes, and click OK.
The default value is 60 minutes.

---------------------------------------------------------------------------------------------------------------------142. How  would you come to know about the updations in  tables present in physical layer of repository, after ETL has been executed?

we have to write the triggers so that we can get the name of the objects gets stored in event polling table.

---------------------------------------------------------------------------------------------------------------------143.  With LDAP authentication implemented, how admin password can be reset? (In repository or in LDAP server?)

           it can be reset IN LDAP server

---------------------------------------------------------------------------------------------------------------------

144. Is it possible to have presentation server pointing to more than one BI servers?

        yes

---------------------------------------------------------------------------------------------------------------------

145.  What are the specific tasks we can perform on repository only in online mode?

You can make changes in the RPD file and push in changes which will be immediately visible to the users who are already connected. This feature we may use in production environment

--- cache

--- set the log level

--- any minor work

---------------------------------------------------------------------------------------------------------------------

146.  What are the spesific tasks we can perform on repository only in offline mode?

-- development of repository

-- creating users

---------------------------------------------------------------------------------------------------------------------

147.  what are Database Hints?

Database hints are instructions placed within a SQL statement that tell the database query optimizer the most efficient way to execute the statement. Hints override the optimizer's execution plan, so you can use hints to improve performance by forcing the optimizer to use a more efficient plan.
Hints are database specific. The Oracle BI Server supports hints only for Oracle 8i, 9i, and 10g servers.

---------------------------------------------------------------------------------------------------------------------

148. What all objects are get created after running time series wizard.

1)Ago Function--->Calculates aggregated value as of some time period

                                                    shifted from current time.

                                ago(<measure>,<time level>,<number to shift>)

2)Todate--->Aggregates a measure attribute from the beginning of a specified time period ti the currently displayed time.

                                ToDate(<measure>,<time level>)

---------------------------------------------------------------------------------------------------------------------

149. Different ways to purge the cache.

Purging cache is the process of deleting the entries from the query cache. In Siebel Analytics you can purge the cache entries in the following ways:

a) Manually, using the Administration tool cache manager facility (in online mode)

b) Automatically, by setting the Cache persistence time field in the physical table dialog box for a particular table.
c) Automatically, by setting the BI server event polling table.
d) Automatically, as the cache storage space fills up.

---------------------------------------------------------------------------------------------------------------------

150. Use of aliases tab (Presentation table)

OBIEE uses aliases in presentation layer in order to avoid report failure whenever a source column or source table name changes in the repository.

2. Create an alias

Automatically

- For a Table
A table alias is created automatically when the table’s name is changed only and exclusively by taking the following actions: right click -> rename; if a table’s name changes by taking a different action, the alias is not created automatically.

For a column
A column alias is created automatically when the column’s name is changed (double click -> deselect “Use Logical Column name” -> specify a new name):

Manually

An alias can be created manually through the “Aliases” tab in the table/column properties (double click on the item or right click -> Properties).

---------------------------------------------------------------------------------------------------------------------

151. what are the ways to avoid circular joins

aliases are required to workaround the circular join condition....OBIEE dosent allow the circular join ...so we use the alias tables to break the join by placing a new alias table in place ---------------------------------------------------------------------------------------------------------------------

152. If you have 3 facts and 4 dimension and you need to join would you recommend joining fact with fact? If no than what is the option?

In the BMM layer, create one logical table (fact) and add the 3 fact table as logical table source If you have dimension table like customer, item, time and fact table like sale and if you want to find out how often a customer comes to store and buys a particular item, what will you do?
o write a query as SELECT customer_name, item_name, sale_date, sum(qty) FROM customer_dim a, item_dim b,
time_dim c, sale_fact d WHERE d.cust_key = a.cust_key AND d.item_key = b.item_key AND d.time_key = c.time_key
GROUP BY customer_name, item_name, sale_date
---------------------------------------------------------------------------------------------------------------------

153. What is connection pool and how many connection pools did you have in your last project?

connection pool is needed for every physical database.
o It contains information about the connection to the database, not the database itself.
o Can use either shared user accounts or can use pass-through accounts -Use: USER and PASSWORD for pass
through .
o We can have multiple connection pools to incraese query performance

---------------------------------------------------------------------------------------------------------------------

154. Did you create any new logical column in BMM layer, how?

Yes. We can create new logical column in BMM layer.
o Example: Right click on fact table -new lgical column-give name for new logical column like Total cost.
o Now in fact table source,we have one option column mapping, in that we can do all calculation for that new column.

---------------------------------------------------------------------------------------------------------------------

155. Can you use physical join in BMM layer?

---------------------------------------------------------------------------------------------------------------------

156. Can you use outer join in BMM layer?

---------------------------------------------------------------------------------------------------------------------

157. What are other ways of improving summary query reports other than Aggregate Navigation and Cache Management?

---------------------------------------------------------------------------------------------------------------------

158. What is Object and Data Level Security?

Object level security

o There are two types of object level security: Repository level and Web level
o Repository level : In presention layar we can set Repository level security by giving permission or deny permission to
users/groups to see particular table or column.
o web level:thisprovides security for objects stored in the siebel anlytics web catlog,such as dashboards,dashboards pages,folder,and reportsyou can only view the objects for which you are authorized. For example,a mid level manager
may not be granted access to a dashboard containing summary information for an entire department.

Data level security

o This controls the type an amount of data that you can see in a report.When multiple users run the same report the
results that are returned to each depend on their access rights and roles in the organization.For example a sales vice
president sees results for alll regions, while a sales representative for a particular region sees only data for that

---------------------------------------------------------------------------------------------------------------------

159. How do you implement security using External Tables and LDAP?

External Tables

Instead of storing user IDs and passwords in a  Server repository, you can maintain lists of users and
their passwords in an external database table and use this table for   purposes. The external database
table contains user IDs and passwords, and could contain other information, including group membership and display
names used for Siebel Analytics Web users. The table could also contain the names of specific database catalogs or
schemas to use for each user when querying data

LDAP
o Instead of storing user IDs and passwords in a  Server repository, you can have the 
Server pass the user ID and password entered by the user to an LDAP(Lightweight Directory Access Protocol ) server
for authentication. The server uses clear text passwords in LDAP authentication. Make sure your LDAP servers are set
up to allow this.

---------------------------------------------------------------------------------------------------------------------

160. If you have 2 fact and you want to do report on one with quarter level and the other with month level how do you do that with just one time dimension?

Using levelbase matrics.

o Find the sql query of the report in Admin->manage Session-> run the sql query on toad ->read the explain plan output
->modify the SQL based on the explain plan output
---------------------------------------------------------------------------------------------------------------------

161. Did you work on a stand alone Siebel system or was it integrated to other platforms?

Stand alone

---------------------------------------------------------------------------------------------------------------------

162. If you want to limit the users by the certain region to access only certain data, what would you do?

using session variable

---------------------------------------------------------------------------------------------------------------------

163. Define pipeline. Did you use it in your projects?

---------------------------------------------------------------------------------------------------------------------

164. Where are passwords for userid? Ldap,external table authentication stored respectively?

ldap-- ldap server,external table --external table columns

---------------------------------------------------------------------------------------------------------------------

165. what is Ragged hierarchy? and how do u manage it

Ragged Hierarchy is one of the different kinds of hierarchy.
o A hierarchy in which each level has a consistent meaning, but the branches have inconsistent depths because at least
one member attribute in a branch level is unpopulated. A ragged hierarchy can represent a geographic hierarchy in which the meaning of each level such as city or country is used consistently, but the depth of the hierarchy varies.
o For example, a geographic hierarchy that has Continent, Country, Province/State, and City levels defined. One branch has North America as the Continent, United States as the Country, California as the Province or State, and San Francisco as the City. However, the hierarchy becomes ragged when one member does not have an entry at all of the levels.

For example, another branch has Europe as the Continent, Greece as the Country, and Athens as the City, but has no entry for the Province or State level because this level is not applicable to Greece for the business model in this example. In this example, the Greece and United States branches descend to different depths, creating a ragged
hierarchy.

---------------------------------------------------------------------------------------------------------------------

166. What is the difference between Single Logical Table Source and Multiple Logical Table Sources?

If a logical table in BMM layer has only one Table as the source table then it is Single LTS.
o If the logical table in BMM layer has more than one table as the sources to it then it is called Multiple LTS.
o Ex: Usually Fact table has Multiple LTS’, for which sources will be coming from different Physical tables.
---------------------------------------------------------------------------------------------------------------------

167. Can you let me know how many aggregate tables you have in your project? On what basis have you created them?

Aggregate tables store precomputed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems.
o If you are writing SQL queries or using a tool that only understands what physical tables exist (and not their meaning), taking advantage of aggregate tables and putting them to good use becomes more difficult as the number of aggregate tables increases. The aggregate navigation capability of the Siebel Analytics Server, however, allows queries to use the information stored in aggregate tables automatically, without query authors or query tools having to specify aggregate tables in their queries. The Siebel Analytics Server allows you to concentrate on asking the right business question; the
server decides which tables provide the fastest answers.
---------------------------------------------------------------------------------------------------------------------

168. How do you know which report is hitting which table, either the fact table or the aggregate table?

After running the report, go to Administration tab and go to click on Manage Sessions. There you can find the queries
that are run and in the View Log option in the Session Management you can find which report is hitting which table.

---------------------------------------------------------------------------------------------------------------------

169. Suppose I have report which is running for about 3 minutes typically. What is the first step you take to improve the performance of the query?

       in ui--manage sessions--view log

---------------------------------------------------------------------------------------------------------------------

170. Have you heard of Implicit Facts? If, so what are they?

An implicit fact column is a column that will be added to a query when it contains columns from two or more dimension tables and no measures. You will not see the column in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives.
o For example, there might be many star schemas in the database that have the Campaign dimension and the Customer dimension, such as the following stars:
Campaign History star. Stores customers targeted in campaign.Campaign Response star. Stores customer responses to a campaign. Order star. Stores customers who placed orders as a result of a campaign. In this example, because Campaign and Customer information might appear in many segmentation catalogs, users selecting to count customers from the targeted campaigns catalog would be expecting to count customers that have been targeted in specific campaigns. To make sure that the join relationship between Customers and Campaigns is through the campaign history fact table, a campaign history implicit fact needs to be specified in Campaign History segmentation catalog. The following guidelines should be followed in creating
segmentation catalogs: Each segmentation catalog should be created so that all columns come from only one physical star.

Because the Marketing module user interface has special features that allow users to specify their aggregations, level-
based measures typically should not be exposed to segmentation users in a segmentation catalog.
---------------------------------------------------------------------------------------------------------------------172. Can you have multiple data sources in OBIEE?

yes

---------------------------------------------------------------------------------------------------------------------

173. How do you deal with case statement and expressions in OBIEE?

o use expression builder to create case when…then.. end statement

---------------------------------------------------------------------------------------------------------------------

174. Do you know about Initialization Blocks? Can you give me an example where you used them?

Init blocks are used for instantiating a session when a user logs in.
 To create dynamic variable you have to create IB to write sql statement.

---------------------------------------------------------------------------------------------------------------------

175. Oracle doesn’t recommend Opaque Views because of performance considerations, so why/when do we use them?

an opaque view is a physical layer table that consists of select statement. an opaque view should be used only if there is no other solution

---------------------------------------------------------------------------------------------------------------------

176. what is the full form of rpd?

Ans There is no full form for rpd as such, it is just a repository file (Rapidfile Database)

---------------------------------------------------------------------------------------------------------------------

177. how can i disable cache for only 2 particular tables?

-->Click on table and disable cacheable option in physical layer.

---------------------------------------------------------------------------------------------------------------------

178. I want To display Request, in which region and customer are two columns

                and filter is applied on Region Column.

     I want Result like whatever the region is selected in request it should display that region with some greeting just bellow that request with specific format.like if region is east then color is red and background is pink and image is diffrent if region is west then color is blue background is yellow and image is diffrent.

---------------------------------------------------------------------------------------------------------------------

179. In chart i want to display the result of only one row of measure How i can do this?

---------------------------------------------------------------------------------------------------------------------

180. How to change color in pie chart view?

        In advanced propoerties we can change color

---------------------------------------------------------------------------------------------------------------------

181. Is it possible to set combination of both name of x and y axis label in the data label in chart view.

---------------------------------------------------------------------------------------------------------------------

182. Is it possible to sort the column in runtime while selecting values of dashboard prompt. Eg, if we select 1 then ascending and if we select 2 then descending.

---------------------------------------------------------------------------------------------------------------------

183. Show department wise maximum salary and only display department related column

---------------------------------------------------------------------------------------------------------------------

184. Is it possible to replace value of dashbard prompt.eg 'a' should be repalced with 'b'.

---------------------------------------------------------------------------------------------------------------------

185. There are two columns employee and manager and we want to display manger 'x' related information. Tell the sql query for this as well as how to set filters.

---------------------------------------------------------------------------------------------------------------------

186. What are the different dashboard prompt controls?

---------------------------------------------------------------------------------------------------------------------

187. How you override the session variable values from dashboard?

Request variable.

---------------------------------------------------------------------------------------------------------------------

188. If I have 2 years data and I have created level based measures of dollars on year, if I create a report by taking only yearly dollar (level based measure) column how many rows it will return?

                -->Two rows.

---------------------------------------------------------------------------------------------------------------------

189. Difference between ToDate (time series function) and RSUM () function.?

---------------------------------------------------------------------------------------------------------------------

190. What is use of filter function? (Filter in a expression builder).?

---------------------------------------------------------------------------------------------------------------------

200. what are slowly changing dimensions?

                --The Type 1 methodology overwrites old data with new data, and therefore does              not track historical data at all.

                --The Type 2 method tracks historical data by creating multiple records for a        given natural key in the dimensional tables with separate surrogate keys and/or   different version numbers.

                --The Type 3 method tracks changes using separate columns. Whereas Type 2     had unlimited history preservation, Type 3 has limited history preservation.

                --The Type 4 method is usually referred to as using "history tables", where one   table keeps the current data, and an additional table is used to keep a record of   some or all changes.

                --The Type 6 method combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6).

---------------------------------------------------------------------------------------------------------------------

201. if you navigate to a particular request then how do you navigate back?

using guided navigation link

---------------------------------------------------------------------------------------------------------------------

202. How do you manage the scheduling the cache using job manager?

seeding of cache

Seeding is the process of prepopulating the cache with queries that are known to generate cache hits.

•Helps to improve query performance

–Use queries that heavily consume database processing and are likely to be reused.

•Is performed by running prebuilt queries during off hours or immediately after purging

–Manually in Answers

–Automatically using Oracle BI Delivers to schedule queries to run at a specified time

Seeding the cache is the process of prepopulating the cache with queries that are known to generate cache hits. One of the main advantages of seeding the cache is the improvement of query performance. A good strategy, therefore, is to seed the cache during off hours by running queries and caching their results. A good seeding strategy requires knowing when cache hits occur so that you can seed the cache with the appropriate queries. The best queries to seed the cache with are queries that heavily consume database processing resources or that are likely to be reused. For example, seed the cache with queries that have many joins or a great deal of sorting, or with queries that is used frequently throughout the business day. Be careful not to seed the cache with simple queries that return many rows and require very little database processing.
---------------------------------------------------------------------------------------------------------------------

203. What are the different views that you have used in your project?

---------------------------------------------------------------------------------------------------------------------

204 In a multi select dashboard prompt is it possible to redistribute values from the presentation variable? if yes then how?

---------------------------------------------------------------------------------------------------------------------

205.What is the difference between materialized view and view?

Materialized View:

A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query.

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.

A materialized view is a stored summary containing precomputes results (originating from an SQL select statement).

As the data is precomputed, materialized views allow for (seemingly) faster dataware query answers

View:

An opaque view is in the physical layer a physical table that consists of a Select statement .

In the repository, opaque views appear as tables in the physical layer but the view does not actually exist. ---------------------------------------------------------------------------------------------------------------------

206. Is it possible to drill from one dimension heirarchy to other? if Yes then dimension must have any relation or not?

                --Yes it is possible.For this there is no need to have any relation between   dimensions.

---------------------------------------------------------------------------------------------------------------------

207.  How many logs files You used? What is name of the log files? What is use of NQQUERY. Log and NQSERVER.Log?

                There are NQQuery,NQServer,MQSchedular,NQCluster,NQSAdminTool

                log files.

                --NQQuery.log file shows Physical SQL Query, logical execution plan where          Usage tracking does not have this information.

                --NQServer File contains when server is start or stop,which subject area is loaded,

                   how many % of cache is been used.

---------------------------------------------------------------------------------------------------------------------

208. we have 2 measures in request can we show 1 at X axis & other at Y axis?  

  yes..

---------------------------------------------------------------------------------------------------------------------

209. If we have 20 pages in one dashboard then all pages are shown in one line can we make 2 line which contain 10 pages each?

---yes.

---Go in portal.css file and

             a)copy tabdimcell and paste it.rename it to  tabdimcell2

             b)copy tabhicell and paste it.rename it to  tabhicell2

---then on each dashboard page add html script.

---------------------------------------------------------------------------------------------------------------------

210. What do you mean by chronological key

chronological key is used in time dimension function to uniquely identify data at particular level to compare previous data

The chronological keys are used in the hierarchies where the dimension attributes referred in the same keep on increasing in a particular fashion. The best example of the same is the date dimenion. The chronological key is referred to as the attribute which keeps on increasing. This helps in using the time series functions and the YTD and MTD measures

---------------------------------------------------------------------------------------------------------------------