Explore topic-wise InterviewSolutions in .

Building reports on denormalized DATA is not a best practice it leads to PERFORMANCE ISSUES but we can build the reports. These are reports can’t be used for business analysis because the data will fluctuate NON regular intervals.

Building reports on denormalized data is not a best practice it leads to performance issues but we can build the reports. These are reports can’t be used for business analysis because the data will fluctuate non regular intervals.

Leval-base matrics MEANS, having a measure pinned at a certain level of the dimension. Monthly Total Sales or Quarterly Sales are the examples.

To create a level based measure, create a NEW logical column based on the ORIGINAL measure (like Sales in the example above). Drag and drop the new logical column to the appropriate level in the Dimension hierarchy (in the above example you will drag and drop it to Month in TIME Dimension.

Leval-base matrics means, having a measure pinned at a certain level of the dimension. Monthly Total Sales or Quarterly Sales are the examples.

To create a level based measure, create a new logical column based on the original measure (like Sales in the example above). Drag and drop the new logical column to the appropriate level in the Dimension hierarchy (in the above example you will drag and drop it to Month in Time Dimension.

ORACLE BI Java Host, Oracle BI Presentation SERVER, and Oracle BI Server.

Oracle BI Java Host, Oracle BI Presentation Server, and Oracle BI Server.

We cannot have outer joins in Physicla layer. We can outer joins in BMM layer. In BMM layer – COMPLEX join can be full INNER join or full outer join or whatever your CRITERIA was,but in PHYSICAL layer – physical join is always an inner join.

We cannot have outer joins in Physicla layer. We can outer joins in BMM layer. In BMM layer – complex join can be full inner join or full outer join or whatever your criteria was,but in physical layer – physical join is always an inner join.

Read the NQSConfig file “REPOSITORY Section”. You will easily FIND the answer. The answer for this is “It cant be changed”.

The repository LOCATION can be changed, this must be done when CLUSTERING the BI Server. The parameters in the NQSCONFIG.INI file are

REPOSITORY_PUBLISHING_DIRECTORY =

REQUIRE_PUBLISHING_DIRECTORY = YES;

Read the NQSConfig file “Repository Section”. You will easily find the answer. The answer for this is “It cant be changed”.

The repository location can be changed, this must be done when clustering the BI Server. The parameters in the NQSCONFIG.INI file are

REPOSITORY_PUBLISHING_DIRECTORY =

REQUIRE_PUBLISHING_DIRECTORY = YES;

The Provider Services tool that COMES with Essbase is used to provide the interface, with Oracle BI Server TALKING to Essbase through its XMLA interface. (Need to find the answer still)

XML for Analysis (abbreviated as XMLA) is an industry STANDARD for data access in analytical systems, such as OLAP and Data Mining. XMLA is based on other industry standards such as XML, SOAP and HTTP.

The Provider Services tool that comes with Essbase is used to provide the interface, with Oracle BI Server talking to Essbase through its XMLA interface. (Need to find the answer still)

XML for Analysis (abbreviated as XMLA) is an industry standard for data access in analytical systems, such as OLAP and Data Mining. XMLA is based on other industry standards such as XML, SOAP and HTTP.

[InstalledDirectory]OracleBIserverRepository.

[InstalledDirectory]OracleBIserverRepository.

• Because it’s very flexible and can integrate into any existing security architecture an ORGANIZATION has built and reduces the need for one more layer of administration..
• Did Oracle OBIEE do a good JOB of COMMUNICATING and convincing the user community regarding the security architecture.
• Probably not… I understand the complexity BEHIND this and the mere flexibility of the tool makes this even difficult.

Access to iBots, CERTAIN tabs in the dashboard, delivers, ALERTS, schedule reports ETC etc..

Access to iBots, certain tabs in the dashboard, delivers, alerts, schedule reports etc etc..

Yes, FIRST at the RPD level and SECOND at the presentation SERVICES level.

Yes, first at the RPD level and second at the presentation services level.

AUTHORIZATION is DONE as PART of SECURITY in Presentation Services.

Authorization is done as part of security in Presentation Services.

INSTEAD of storing usernames and PASSWORDS directly in the RPD, these are stored in the DATABASE for better management purposes. This also HELPS in RPD migration and deployment across multiple ENVIRONMENTS.

Instead of storing usernames and passwords directly in the RPD, these are stored in the database for better management purposes. This also helps in RPD migration and deployment across multiple environments.

It’s very flexible and can be integrated to one of your existing technologies like LDAP, Oracle EBS, AD, and Oracle Database. So, do the same username have to exist in OBIEE SECURITY layer as part of RPD development? Yes, by default, OBIEE stores LIST of usernames and passwords and checks incoming credentials against it.

It’s very flexible and can be integrated to one of your existing technologies like LDAP, Oracle EBS, AD, and Oracle Database. So, do the same username have to exist in OBIEE security layer as part of RPD development? Yes, by default, OBIEE stores list of usernames and passwords and checks incoming credentials against it.

Authentication is process of confirming WHETHER the user is a VALID user or not. Is he part of this COMPANY. Is he an employee of our external suppliers.

Authorization is process of giving ACCESS to different pieces of the OBIEE. One user “UserA” can access dashboards, can create iBots, can schedule REPORTS and can do administrative tasks. Another user “UserB” can see only part of what “UserA” has access to and “UserB” has access to some other tabs of the dashboard that “UserA” does not.

This layer of separating who can access what is done as part of Authorization.

Authentication is process of confirming whether the user is a valid user or not. Is he part of this company. Is he an employee of our external suppliers.

Authorization is process of giving access to different pieces of the OBIEE. One user “UserA” can access dashboards, can create iBots, can schedule reports and can do administrative tasks. Another user “UserB” can see only part of what “UserA” has access to and “UserB” has access to some other tabs of the dashboard that “UserA” does not.

This layer of separating who can access what is done as part of Authorization.

Data LEVEL security controls the type and amount of data that you can see in reports. Object level security provides security for OBJECTS STORED in the OBIEE web catalog, LIKE DASHBOARDS, dashboards pages, folder, and reports.

Data level security controls the type and amount of data that you can see in reports. Object level security provides security for objects stored in the OBIEE web catalog, like dashboards, dashboards pages, folder, and reports.

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 all REGIONS, while a sales representative for a particular region sees only data for that region.

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 all regions, while a sales representative for a particular region sees only data for that region.

There are TWO types of OBJECT level security:

1. Report Level
2. Web Level

Repository level: In presentation layer we can set Repository level security by giving permission or deny permission to users/groups to SEE particular table or column.

Web level: this provides security for objects stored in the OBIEE web catalog, such as dashboards, dashboards pages, folder, and reports you 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.

There are two types of object level security:

Repository level: In presentation layer we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.

Web level: this provides security for objects stored in the OBIEE web catalog, such as dashboards, dashboards pages, folder, and reports you 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.

Authentication is the PROCESS, by which a system verifies, through the use of a user ID and password, that a user has the necessary permissions and AUTHORIZATIONS to log in and ACCESS data. The Siebel Analytics SERVER authenticates each connection request it receives.

• Operating system authentication
• External table authentication
• Database authentication
• LDAP authentication

Authentication is the process, by which a system verifies, through the use of a user ID and password, that a user has the necessary permissions and authorizations to log in and access data. The Siebel Analytics Server authenticates each connection request it receives.

Monitoring and managing the cashe is cache management.There are three ways to do that.

Disable caching for the system.(NSQconfig INI file), Cache persistence time for specified physical tables and Setting event polling table.

Disable caching for the system.(INI NQ CONFIG file :

You can disable caching for the whole system by setting the ENABLE parameter to NO in the NQSConfig.INI file and restarting the Siebel Analytics Server. Disabling caching stops all NEW cache entries and stops any new queries from using the existing cache. Disabling caching allows you to enable it at a later time without losing any entries already stored in the cache.

Cache persistence time for specified physical tables:

You can specify a cacheable attribute for each physical table; that is, if queries involving the specified table can be added to the cache to answer future queries. To enable caching for a particular physical table, select the table in the Physical layer of the Administration Tool and select the option MAKE table cacheable in the General tab of the Physical Table properties dialog box. You can also use the Cache Persistence Time settings to specify how long the entries for this table should persist in the query cache. This is useful for OLTP DATA sources and other data sources that are updated frequently, potentially down to every few seconds.

Setting event polling table:

Siebel Analytics Server event polling tables store information about updates in the underlying databases. An application (such as an application that loads data into a data mart) could be configured to add rows to an event polling table each time a database table is updated. The Analytics server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables.

For event polling table ,It is a standalone table and doesn’t require to be joined with other tables in the physical layer.

Monitoring and managing the cashe is cache management.There are three ways to do that.

Disable caching for the system.(NSQconfig INI file), Cache persistence time for specified physical tables and Setting event polling table.

Disable caching for the system.(INI NQ config file :

You can disable caching for the whole system by setting the ENABLE parameter to NO in the NQSConfig.INI file and restarting the Siebel Analytics Server. Disabling caching stops all new cache entries and stops any new queries from using the existing cache. Disabling caching allows you to enable it at a later time without losing any entries already stored in the cache.

Cache persistence time for specified physical tables:

You can specify a cacheable attribute for each physical table; that is, if queries involving the specified table can be added to the cache to answer future queries. To enable caching for a particular physical table, select the table in the Physical layer of the Administration Tool and select the option Make table cacheable in the General tab of the Physical Table properties dialog box. You can also use the Cache Persistence Time settings to specify how long the entries for this table should persist in the query cache. This is useful for OLTP data sources and other data sources that are updated frequently, potentially down to every few seconds.

Setting event polling table:

Siebel Analytics Server event polling tables store information about updates in the underlying databases. An application (such as an application that loads data into a data mart) could be configured to add rows to an event polling table each time a database table is updated. The Analytics server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables.

For event polling table ,It is a standalone table and doesn’t require to be joined with other tables in the physical layer.

There are two classes of variables:

1. Repository variables
2. Session variables

Repository variables: 

A repository variable has a single value at any point in TIME. There are two types of repository variables:

Static: This value persists, and does not change until a Siebel Analytics Server administrator decides to change it.

Dynamic: The values are refreshed by data returned from queries. When defining a dynamic repository variable, you will create an initialization BLOCK or use a preexisting one that contains a SQL query. You will also set up a SCHEDULE that the Siebel Analytics Server will follow to execute the query and PERIODICALLY refresh the value of the variable.

Session Variables:

Session variables are created and ASSIGNED a value when each user logs on.

There are two types of session variables:

1. system
2. non-system.

There are two classes of variables:

Repository variables: 

A repository variable has a single value at any point in time. There are two types of repository variables:

Static: This value persists, and does not change until a Siebel Analytics Server administrator decides to change it.

Dynamic: The values are refreshed by data returned from queries. When defining a dynamic repository variable, you will create an initialization block or use a preexisting one that contains a SQL query. You will also set up a schedule that the Siebel Analytics Server will follow to execute the query and periodically refresh the value of the variable.

Session Variables:

Session variables are created and assigned a value when each user logs on.

There are two types of session variables:

System variables: System variables are session variables that the Siebel Analytics Server and Siebel Analytics Web USE for specific purposes. System variables have reserved NAMES, which cannot be used for other kinds of variables (such as static or dynamic repository variables, or for non-system session variables). When using these variables in the Web, preface their names with NQ_SESSION. For example, to FILTER a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.

Non-system variables: A common use for non-system session variables is setting user filters. For example, you COULD define a non-system variable called SalesRegion that would be initialized to the name of the user’s sales region. You could then set a security filter for all MEMBERS of a group that would allow them to see only data pertinent to their region.

When using these variables in the Web, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion set the filter to the Variable NQ_SESSION.SalesRegion.

System variables: System variables are session variables that the Siebel Analytics Server and Siebel Analytics Web use for specific purposes. System variables have reserved names, which cannot be used for other kinds of variables (such as static or dynamic repository variables, or for non-system session variables). When using these variables in the Web, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.

Non-system variables: A common use for non-system session variables is setting user filters. For example, you could define a non-system variable called SalesRegion that would be initialized to the name of the user’s sales region. You could then set a security filter for all members of a group that would allow them to see only data pertinent to their region.

When using these variables in the Web, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion set the filter to the Variable NQ_SESSION.SalesRegion.

You can use variables in a repository to streamline ADMINISTRATIVE TASKS and modify METADATA CONTENT dynamically to adjust to a changing data environment. The Administration Tool INCLUDES a Variable Manager for defining variables.

You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment. The Administration Tool includes a Variable Manager for defining variables.

You can enable logging level for individual users; you cannot configure a logging level for a group.Set the logging level based on the amount of logging you WANT to do. In normal OPERATIONS, logging is generally DISABLED (the logging level is set to 0). If you decide to enable logging, CHOOSE a logging level of 1 or 2. These two levels are designed for use by Siebel Analytics Server ADMINISTRATORS.

Set Logging Level:

1. In the Administration Tool, select Manage > Security.
2. The Security Manager dialog box appears.
3. Double-click the user’s user ID.
4. The User dialog box appears.
5. Set the logging level by clicking the Up or Down arrows next to the Logging Level field.

You can enable logging level for individual users; you cannot configure a logging level for a group.Set the logging level based on the amount of logging you want to do. In normal operations, logging is generally disabled (the logging level is set to 0). If you decide to enable logging, choose a logging level of 1 or 2. These two levels are designed for use by Siebel Analytics Server administrators.

Set Logging Level:

TCP/IP

TCP/IP

(The condition given was Broker and customer in the same table) SPLIT Broker and customer.

We NEED to MAKE an alias table in the physical LAYER.

(The condition given was Broker and customer in the same table) Split Broker and customer.

We need to make an alias table in the physical layer.

In the PHYSICAL LAYER, right CLICK on the table there we will have the OPTION which says cacheable.

In the physical layer, right click on the table there we will have the option which says cacheable.

There is no full FORM for RPD as such, it is just a REPOSITORY file (Rapidfile Database).

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

To IMPROVE the PERFORMANCE and QUERY RESPONSE TIME.

To improve the performance and query response time.

Dimension tables contain descriptions that data ANALYSTS use as they query the database. For example, the Store table contains store names and addresses; the Product table contains product packaging information; and the Period table contains month, quarter, and YEAR VALUES. Every table contains a primary key that consists of one or more COLUMNS; each row in a table is uniquely identified by its primary-key value or values.

Dimension tables contain descriptions that data analysts use as they query the database. For example, the Store table contains store names and addresses; the Product table contains product packaging information; and the Period table contains month, quarter, and year values. Every table contains a primary key that consists of one or more columns; each row in a table is uniquely identified by its primary-key value or values.

No we have to migrate the WHOLE web & RPD files

No we have to migrate the whole web & RPD files

• Java Development Kit (JDK), A software package that contains the minimal SET of tools needed to write, compile, debug, and RUN Java applets.
• 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.

• It is utility of Siebel/OBIEE Admin tool.
• Allows you to examine the repository metadata tool.

For example: SEARCH for objects BASED on name, type.

Examine relationship between metadata objects LIKE which column in the presentation layer maps to which table in physical layer.

For example: search for objects based on name, type.

Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer.

• OLD method in 10G version consisted in opening LOG files on the server.
• Now, in 11g, log INFORMATION is consultable in Enterprise Manager.

• In OBIee 11G, main configuration items are managed in ENTERPRISE Manager (WEB INTERFACE).
• However, some CONFIGURATIONS are still located in configuration files, like NQSConfig.ini, NQSCluster.ini, odbc.ini, and instanceconfig.xml.

• First of all, check the VALUE of your SESSION variable LOGLEVEL.
• 5 is fine.
• Then, go into Administration -> Manage sessions.
• Find your query (at the bottom of the LIST), and click on “VIEW log”.
• If you hit the cache, you will see “cache hit on query”. Otherwise, you will see the physical SQL below the logical SQL.

Theoretically, yes. In practice, the two SUBJECT AREA must come from the same business model in the repository, which is quite useless. Then, we can consider the two FOLLOWING alternatives :

• Create an analysis with “combine REQUEST”: one subrequest by subject area. NOTE that it’s tricky to implement for standard users.
• Set up a transverse subject area, that will gather tables from both business topics.

Theoretically, yes. In practice, the two subject area must come from the same business model in the repository, which is quite useless. Then, we can consider the two following alternatives :

OBIEE can be plugged on Relational Databases, Cubes or Flat FILES, with the FOLLOWING constraints :

• The sources must be modeled as star schemas or snowflakes schemas in ORDER to have an efficient and safe behavior.
• 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 can be plugged on Relational Databases, Cubes or Flat files, with the following constraints :

• You can use the Aggregate Persistence Wizard to create the SQL file that will be USED to create aggregate tables and map them into the metadata.
• Do not use aggregate persistence against tables with active Virtual Private Database (VPD) security filters. There is a possibility that the aggregate information might be persisted without the VPD filter, posing a security risk.
• The Aggregate Persistence Wizard is a nifty utility for CREATING, modelling and populating aggregate tables available in OBIEE 10g and 11G. This utility is especially useful to quickly build a single aggregation layer upon FEDERATED heterogeneous physical sources. OBIEE will create, MODEL and populate aggregate dimensions and facts based upon the intelligence you have built into the RPD. This avoids re-writing the same logic into your ETL.

From the BI side, you can basically use 2 TYPES of HINTS.

The Index hint instructs the optimizer to scan a specified index rather than a table.

The LEADING hint forces the optimizer to build the join order of a query with a specified table.

Importantly, the Explain Plan contains the following information regarding your query.

• Ordering of the tables referenced
• Access method for each table
• Join method for each join operation
• DATA operations, such as filter, sort or aggregation

And in addition:

• Optimization (Cost and Cardinality)
• Partitioning
• Parallel Execution

From the BI side, you can basically use 2 types of hints.

The Index hint instructs the optimizer to scan a specified index rather than a table.

The Leading hint forces the optimizer to build the join order of a query with a specified table.

Importantly, the Explain Plan contains the following information regarding your query.

And in addition:

So exactly what is a request variable? Well, PUT succinctly:

A request variable is a variable that is used to temporarily override the value of a session variable.

And, with a little more precision, a request variable is:

A variable that is defined within the Presentation Services;

A variable whose scope (page or dashboard) is that of the associated dashboard prompt that defines the request variable and its value;

A variable whose name should not match that of a repository variable or a security SYSTEM session variable; and

A variable that overrides the value of the corresponding session variable (should it exist) for the purpose of BI Server request parsing and physical SQL construction for all requests that are ISSUED from dashboard pages that fall within the scope of the variable’s DEFINITION.

Note, USING a request variable does not change the actual value of a session variable – neither temporarily nor permanently. It simply appears to change the value for certain purposes during the execution of a request.

So exactly what is a request variable? Well, put succinctly:

A request variable is a variable that is used to temporarily override the value of a session variable.

And, with a little more precision, a request variable is:

A variable that is defined within the Presentation Services;

A variable whose scope (page or dashboard) is that of the associated dashboard prompt that defines the request variable and its value;

A variable whose name should not match that of a repository variable or a security system session variable; and

A variable that overrides the value of the corresponding session variable (should it exist) for the purpose of BI Server request parsing and physical SQL construction for all requests that are issued from dashboard pages that fall within the scope of the variable’s definition.

Note, using a request variable does not change the actual value of a session variable – neither temporarily nor permanently. It simply appears to change the value for certain purposes during the execution of a request.

By CLICKING on the cancel button, you can stop a SPECIFIC report PLACED on the dashboard

By clicking on the cancel button, you can stop a specific report placed on the dashboard

SHARED FOLDERS/XYZ Folder/ It CONTAINS two folders:

1. Prompts – SAVED filters will be stored here.
2. Reports

Shared Folders/XYZ Folder/ It contains two folders:

Using conformed DIMENSIONS, you can FETCH multiple METRICS across various facts, but the join should be of the same GRAIN / same level of data detail.

Using conformed dimensions, you can fetch multiple metrics across various facts, but the join should be of the same grain / same level of data detail.

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.

(or)

Session variables can have different values for each user, repository variables have the same value for everyone.

There are two TYPES of session variables:

• System session variables: out of the box variables like USER
• Non-system session variables: user-defined variables, can have different values for each user and are typically used to customize user experience , for example you could have a SALES_REGION variable to show only data relevant to the user's region.

Repository variables also have 2 subtypes:

• Static repository variable: Fixed value that can only be changed with the admin tool, can be used to replace hardcoded values like database schemas and user names in connection pools.
• Dynamic repository variable: These hold values that apply to any user, for instance currency exchange rates.

Both System variables and dynamic repository variables get their values from initialization blocks that can contain SQL.

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.

(or)

Session variables can have different values for each user, repository variables have the same value for everyone.

There are two types of session variables:

Repository variables also have 2 subtypes:

Both System variables and dynamic repository variables get their values from initialization blocks that can contain SQL.

COMPLEX JOIN – to use multiple CONDITIONS, such A.ROW_WID = B.ROW_WID AND / OR A.A_WID = B.B_WID like that NATURAL Join / Equi Join

Complex Join – to use multiple conditions, such A.ROW_WID = B.ROW_WID AND / OR A.A_WID = B.B_WID like that Natural Join / Equi Join

Ibot is an SCHEDULING agent, USED to SCHEDULE reports to be sent across VARIOUS DEVICES such as email, pager, mobile, other devices etc.

Ibot is an scheduling agent, used to schedule reports to be sent across various devices such as email, pager, mobile, other devices etc.

USING SCHEDULER - ibots

Using Scheduler - ibots

NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml

NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml

If you WANT to CONNECT TWO tables where there is no RELATIONSHIP you can use a third BRIDGE table for connecting them which will have common columns in both tables, this is used in BMM Layer.

If you want to connect two tables where there is no relationship you can use a third bridge table for connecting them which will have common columns in both tables, this is used in BMM Layer.