There are two types of variables, namely the session variables and the repository variables. The session variables are pertaining to each session that is created for every login of a user. They may be System or Non-system variables.
The repository variables are the ones that are specific to a repository/database. The repository variables contain the parameters that are corresponding to different attributes of the repository and queries. They are again classified as static and dynamic variables. The static variables are the ones that are having permanent values throughout. The administrator can change it whenever needed. The dynamic variables are the ones that have values that are corresponding to the SQL queries and data fetches.
The dynamic variables can take up values depending on the scheduled updates that are started by the administrator. They can also take up values due to the SQL queries that have been recently executed from the user side. Initialization blocks run at a specific time or triggered according to a specific condition.
If we want to connect two tables where there is no relationship we can use a thrid bridge table for connecting them which will have same columns in both tables.
The user ID and password need not be stored in the repository of the Siebel Analytics server. The external tables and LDAP offer other possibilities. The userID and password for user authentication are stored in the external table. The information on different tables and the access information for each user are stored in this external table. The other way is the Lightweight Directory Access Protocol. This is similar to imposing an access limitation to all the different directories and folders thereby having the limitations to the data viewable for the different users.
There are 2 different types of session variables
1. System variables: The system variables are the ones that are used by the analytics server and web to manage and coordinate the sessions and related data. Such names cannot be used for non system variables or other repository variables. You can try naming such variables with a prefix to identify their nature and content. This will make it easier for classification within each session.
2. Non-System variables- The non system variables are always used to have user-defined aspects of data stored at a specific place. If you have a data called “numberOfPersons” in a specific data from the user space, you can then use this to classify the entire database according to the numberOfPersons etc.
When using the variables from the Analytics Webserver, you can just prefix the variable name with NQ_SESSION.(Varaiblename) to narrow down the scope to the present session.
Guided navigation is a feature of Oracle BI Interactive Dashboards that enables the content and layout of Oracle BI Interactive Dashboard to change dynamically based on changes in the information being analyzed. Specifically, sections in a dashboard page can be set up and only appear when there is interesting information of the data.
The implicit fact column is the one that is created due to the join, combinations from the different data or columns from the different tables. There may not be the exact data in form of the column in the result or even in the physical layer of database. These are implied from different data and can just be a temporary layer which help us in getting the required result.
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.
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.
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.
When handling complex requirement is not possible in data modeling from RPD side in obiee then user needs to create direct select statement and use it in to RPD is known as Opaque view.It is not recommended to use Opaque views as it degrades the performance of the report.
The creation of the logical column on the higher level of the dashboard will have an effect on the tables only on that view level and not on the other dashboards and other requests. The logical columns created on the repository level will, in turn, get their effect on all the other requests and reports from different view levels. So it is always preferable to have the logical column created at the repository 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
For the RPD we can use the Merge option in Admin Tool and for dashboards and reports we can use Content Accelerator Framework.
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.
The presentation layer is dependent on the database that is underlying each server. Therefore the presentation layer alone cannot be migrated as a stand-alone aspect of the database. What we can do instead is have an ODBC or similar database connection established across from the different servers to the particular main system and then carry over the presentation semantics from the other server with that database-oriented changes in the logic layer.
The Admin tool has the “Manage Sessions” tab which gives you access to the logs that are being generated for each session. After the report generation sessions, you can easily view the log to map each request to the corresponding tables and databases.
The opaque views are tables that are created with join or other query data that contain “SELECT” query output. The opaque views make the logical understanding simple for implementation but there are heavy performance constraints. They are only used when there is no other way to get to the final solution.
Change the request and click Advanced in that you get xml code and also the actual sql.
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.
Enable Loglevel 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.
By clcking Administration->Manage sessions-> view sql.
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 Datasources and recover the data back to the Engine and presents to the presentation Services .
To hide certain columns from a user:
1.Do not add the column in the report
2.Do not add the column in the presentation layer
To get real physical SQL sent by OBIEE to the database, check following details.
1.Verify the value of your session variable LOGLEVEL (5 is fine)
2.Go into Administration -> Manage sessions
3.Find your query (at the bottom of the list), and click on “View log.”
4.Below the logical SQL, you will see the physical SQL.
The Query repository tool gives the option to search and analyze the data from the database according to the name, type, and other attributes that are describing the database.
The relationship between the different view layer data and the corresponding physical layer columns.
The term JDK stands for Java development kit which is basically a package of software that consists of tools that are required for writing, compiling, debugging, and also for running Java applets.
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.
You can stop a report run on the dashboard automatically by selecting the cancel button.
There are mainly two folders known as the prompts and reports.
Use the option scheduler that will help you in generating trigger reports that are time-based.
The query repository tool is used for OBIEE or Siebel admin tool. It also allows the user to make an examination of the tool of repository Metadata. Also, the relation between certain objects of Metadata is examined by this. The objects generally consist of in which column of the presentation later should map with which table of the physical layer.
The difference between logical table source and the logical table is that a logical table consists of one or more logical table source. The mapping between logical column and physical columns are done in this element.
There are following security providers used by OBIEE:
1.Authentication provider to authenticate users.
2.Policy store provider is used to access privileges on all applications except for BI Presentation Services.
3.Credential store provider is used to store credentials used internally by the BI application.
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.
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 :
1. Operating system authentication.
2. External table authentication.
3. Database authentication.
4. LDAP authentication
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 Schems to Dimensional Sachems.
Repository stores the Meta data information.The extension of the repository file is “.rpd”.With OBIEE Server, all the rules needed for security,data modeling, 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
1. Physical – Represents the data Sources.
2. Business – model the Data sources into Facts and Dimension and apply business logic.
3. Presentation – Specifies the user’s view of the data rendered in OBIEE answers client
The metadata information is stored in the repository. The Siebel repository is known as a file system that has the repository file extensions of file. rpd. Metadata repository.
The rules which are connected with data modeling, connectivity, aggregate navigation, and security and caching are stored in the repositories of the metadata. This happens with the Siebel Analytical server.
Several repositories can be accessed with the help of the Siebel analytics server. Also, each repository of metadata is able to store several business models.
There may be a situation in which there is a need for connecting two tables that have no relation to them. In this case, another table is used that helps in connecting the other two tables. This third table is known as a bridge table that has the same columns as present in the other two tables.
An Oracle BI application consists of pre-built, industry-specific Oracl e 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.
To get real physical SQL sent by OBIee to the database, check following details
1. Verify the value of your session variable LOGLEVEL (5 is fine)
2. Go into Administration -> Manage sessions
3. Find your query (at the bottom of the list), and click on “View log.”
4. Below the logical SQL, you will see the physical SQL.
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 level 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.
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
Passwords for userid are in siebel analytics server repository.
We can do Aggrigation navigation ,level base matrics, time series wizard, create new logical column, comlex join.
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.
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.
The method of containing a measure that is held on a particular level of dimension is referred to as Level base metrics. Quarterly sales or monthly sales in one such example of metrics that are level-based.
In order for creating metrics that are level-based, you need to first make a new column that is logical and should be based on original measures. After this, you have to drag and then drop this new column accordingly.
<> It is utility of OBIEE /Seibel Admin tool.
<> allows us to examine the repository metadata tool.
<> It 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.
* Init blocks are used for instantiating a session when a user logs in.
* To make dynamic variable we have to create IB to write sql statement.
By using expression builder we can create case statement when…then.. end .
We can disable cache in the physical layer, by right clicking on the table there we can have the option which define cacheable.
There is no full form for rpd as such, it is just a repository file which is known as Rapidfile Database.