If you're looking for OBIEE Interview Questions for Experienced or Freshers, you are at right place. There are a lot of opportunities for many reputed companies in the world. According to research, has a market share of about 4.5%. So, You still have an opportunity to move ahead in your career in OBIEE Development. Mindmajix offers Advanced OBIEE Interview Questions 2020 that helps you in cracking your interview & acquire dream career as OBIEE Developer.
This tutorial gives you an overview and talks about the fundamentals of OBIEE.
OBIEE Tutorial For Beginners
What Is OBIEE ?
Today’s enterprise business scenarios are composed of multiple heterogeneous processes and systems that consists of transactional, ERP, decision support, analytical and custom homegrown desktop applications based on spreadsheets and Microsoft Access etc. Data is distributed across the enterprise and integration among various systems to produce a single coherent view of the business performance of the enterprise is difficult.
Oracle business intelligence is a system that provides the single enterprise view of the business performance producing actionable insights across and deep down from top to bottom of the enterprise. This is most popularly known as Oracle business intelligence enterprise edition OBIEE.
Oracle business intelligence provides the following features as listed below:
Unified Enterprise Vie: Enables a single consistent and logical view of enterprise across multiple heterogeneous sources such as ERP, data warehouse and operational systems.
Unified Semantic View: Models complex business information and modes form multiple sources to a common enterprise logical model.
End User Self Service: Business users can develop ad-hoc analytic reports based on their own needs and only be concerned about the business view of information:
Real-Time Information Access: Technologies like federated access, micro ETL, direct integration with transactional systems as well as replication provides this
Performance Support By Oracle Appliance: Oracle Exalytics is OBIEE self-contained in an appliance. It is a hardware machine with preinstalled and configured OBIEE software
Open Scalable Architecture: Multi-tiered architecture enables it to scale vertically and horizontally in any tier.
Multiple Data Source Support: OBIEE supports multiple data sources ranging from ERP(EBS and Peoplesoft), Siebel CRM, data warehouses, OLAP cubes like Essbase and SSAS, spreadsheets and text files. Oracle data integrator tool is the common hub for data integration that provides the various adapters.
Built-In And Customizable Analytic Capabilities: OBIEE Applications are a suite of products on top of the platform consisting of built-in functionality that is extensible to Enterprise needs.
Foundation For Integrating Multiple Analytic Applications
Foundation for integrating multiple applications like Finance, procurement, HR, Transportation etc.
Publishing And Integration With Microsoft Excel
Integrated OLAP Sources Like Essbase: Data can be pulled and integrated with Essbase in a horizontal (drill through) or vertical (drill down capability)"
Enterprise Mobile Capabilities: OBIEE has built-in and customizable mobile platform capabilities in a distributed disconnected model.
Data Source Abstraction To Business: OBIEE provides a logical business data modal based on Common Information model that hides the data source from the business.
Highly Customizable And Modular: OBIEE is customizable in all tiers 1, e reports, repository. Middleware, ETL and data layer. Tiered architecture and functional subject areas provide modularity.
Interactive And Customizable Dashboards And Report
KPI, Alerts, Delivery And Automation
Common Data Integration: Oracle data integrator provides the common data integration platform across transactional, OLAP, spreadsheet and other external data sources
A typical OBIEE implementation consists of the multiple tiers as shown in the architecture diagram below. Information from data source layer is transformed and integrated along the way and presented in a manner that is consumable effectively the end business user.
OBIEE Tiered Architecture
1. Client Tier: This consists of the browser and the URL for accessing the OVIEE system from a laptop or a desktop or across the internet or mobile devices.
2. Web Tier: This is web catalog layer that stores the reports and dashboards developed by the BI business users.
3. Application Tier: Metadata layer called repository that develops SQL queries based on user requests
4. Data Warehouse: This layer is the data warehouse that stores the historical data in a schema suitable for BI analysis
5. ETL: Extract transform and load is the tool that transforms the transactional data into a format that is suitable for BI analysis.
6. Data Source: The data source are the various kinds of transactional and spreadsheet data sources.
The diagram above shows OBIEE application layer architecture. The Oracle application layer or popularly known as the Fusion Middleware layer is built upon a common domain-based architecture that can support as a set of common core functionality (like security, scalability etc) across various Oracle products. This is implemented with the Weblogic domain as shown in the diagram (Oracle BI domain).
The Oracle BI domain consists of two components
Weblogic Domain: The Weblogic domain consists of the logical division of applications in the applications we server layer with a common architecture to administer them, called Admin servers and managed servers to perform the web applications work performed by the applications.
Admin Server: This consists of a set of admin server to manage the processes running in the domain.
Managed Server: These are product specific processes that run within Weblogic. The core architecture is designed as such that multiple managed server can be started across multiple machines for load balancing as BI usage increases. The managed server consists of different application-specific services like BI publisher (responsible for generating pixel-perfect reports), BI Office (integration with MS office components like excel and ppt), Action service (responsible for navigation based on the action defined for a data cell in the reports).
Oracle BI System Components: The Oracle BI systems components are the server side components of the weblogic application layer domain that performs a specific set of work. The system components are managed in an integrated fashion by a central process Oracle process manager (OPMN)
Oracle BI Server: Performs generation of SQL queries based on the Oracle BI repository metadata and the user requests. The SQL queries are then directed to the data source or the data warehouse to produce the desired results.
Oracle Presentation Server: This is responsible for rendering the report catalog with the data retrieved from the SQL queries to the dashboard.
Oracle BI Scheduler: Responsible for executing Oracle BI requests and in a scheduled automated manner and delivery of BI content such as generated reports to business users email. Responsible for monitoring the different business alerts set in the BI system.
Oracle BI Cluster Controller: Responsible for cluster control in high availability and failover environment in conjunction with Oracle cluster ware. Process failures detected in one node will results in automatic failover to the other nodes.
Oracle BI Java Host: This is responsible for enabling BI Presentation server to support various Java tasks for BI Scheduler, Publisher and graphs.
Job Responsibilities Of a OBIEE Developer
Design the technical specification to address the functional requirements• Implement the RPD design and reports addressing the project requirements.• Built Repository at three layers Physical Layer (connecting Data sources & import schema), Business Model & data mapping (create Logical tables & data source mapping) & presentation layer level (create presentation catalogs), create presentation folders.• Customize Presentation services in Answer Views & charts, Create & save Filters, Create Charts & Tables in OBIEE presentation services.• Create dashboard prompts and assign to multiple reports created on different subject areas.• Design and Configured Dashboards, Analytics requests.• Implement the change based on business requirements.
Job Responsibilities of a OBIEE Administrator
Install OBIEE 11g setup in the Unix Environment.Create/Modify Metadata repository suiting to data requirements by using OBIEE Administration tool.Migration from UAT Environment to Production Environment.Server and memory upgrade activities & Deploy patches in a server.Maintain the Platform Availability.Resolve Data Quality, Security issue and Report performance issue.Software installation in a server.Provide access to the end user and maintaining the entire applicationAssign Data Security, RPD level & UI level security to the Users.
1. Business Intelligence
Provides data and tools required by users to answer questions that are important for running a part of the business for which they are responsible:Determine if the business is on trackIdentify where things are going wrongTake and monitor corrective actionsSpot trends
Show me the most effective promotionsShow me customers most likely to switchShow me products that are not profitableCompare sales this quarter with sales a year agoShow me sales for each district by month
2. Oracle BI Products
Oracle Business Intelligence is sold in two varieties:
Oracle BI Enterprise Edition
Oracle BI Applications
Oracle BI Enterprise Edition:
Is a comprehensive suite of enterprise BI products that delivers a full range of analysis and reporting capabilities
It has the following components:
Oracle BI Server – Common enterprise business model and abstraction layer
Oracle BI Answers – Ad-hoc query and reporting
Oracle BI Interactive Dashboards – Highly interactive dashboards for accessing business intelligence and applications content
Oracle BI Delivers – Proactive business activity monitoring and alerting
Oracle BI Disconnected Analytics – Full analytical functionality for the mobile professionals
Oracle BI Publisher – Enterprise reporting and distribution of “pixel-perfect” reports
Oracle BI Briefing Books – Snapshots of dashboard pages to view and share in offline mode
Oracle BI Applications:
Are complete, prebuilt BI solutions that deliver intuitive, role-based intelligence for everyone in an organization
Oracle BI Applications are built on the Oracle Business Intelligence Enterprise Edition
Provides all that the standalone application does, plus:
1. Applications for common industry analytical processing such as Service Analytics, Sales Analytics, Marketing Analytics, and so on
2. Prebuilt role-based dashboards to support the needs of line managers to chief executive officers
3. A prebuilt database (Oracle Business Analytics Warehouse) designed for analytical processing with prebuilt routines to extract, load, and transform data from the transactional databases.
3. OBIEE Architecture
Is made up of five main components:
Oracle BI Presentation Server
Oracle BI Server
Oracle BI Scheduler
Web Clients: Provide access to bi information
Oracle BI Presentation Server
- Provides the processing to visualize the information for client consumption1. Is implemented as an extension to a web server2. Uses the presentation catalog to store aspects of the application
- Receives data from the Oracle BI Server and provides it to the client that requested it
Oracle BI Presentation Catalog
- Stores the application dashboards request definitions and information about scheduling reports
- Contains information regarding permissions and accessibility of the dashboards by groups and users
- Is created when the Oracle BI Presentation Server starts
- Is administered using Oracle BI Catalog Manager
Oracle BI Delivers
- Client application that:
1.Is used to create iBots
2. Deliver alerts to subscribed users
3. Is integrated with Dashboards and Answers
- Job identifies what information to filter when it should run, and who to send alerts to
Oracle BI Presentation Services Administration
- Is used to access administrative functions of Oracle BI presentation services and view information about the currently installed system
Oracle BI Server :
Provides efficient processing to intelligently access the physical data sources and structures the information
Uses metadata to direct processing
Generates dynamic SQL to query data in the data sources
Connects natively or via ODBC to the RDBMS
Structures results to satisfy requests
Provides the data to the Oracle BI Presentation Server
Several important components are used by the Oracle BI Server:
- Repository file (.rpd)
- Log files
1. Repository file (.rpd)
- Contains metadata that represents the analytical model
- It is created using the Oracle BI Administration Tool
- It is divided into three layers:
- Physical layer – represents the data sources
- Business and Mapping layer – models the data sources into facts and dimensions
- Presentation layer – specifies the users view of the model; rendered in Oracle BI Answers
Contains results of queries
Is used to eliminate redundant queries to the database:
Speeds up request processing
3. NQSConfig.INI :
- Is a configuration file used by the Oracle BI Server at startupSpecifies values that control processing, such as:
1. Defining the repository (.rpd) to load
2. Enabling or disabling caching of results
- Is a configuration file used by the Oracle BI Server
- Specifies values that control SQL generation:
1. the features supported by each database
5. Log Files
- NQServer.log records Oracle BI Server messages
- NQQuery.log records information about query requests
Oracle BI Scheduler:
- Manages and executes jobs requesting data analytics
- Schedules reports being delivered to users at specified times
- In windows, the scheduler runs as a service
- Contain the business data users want to analyze
- Are accessed by the Oracle BI Server
Can be in any format, such as
1. Relational databases
2. Online Analytical Processing (OLAP) databases
3. Flat files
Oracle Business Analytics Warehouse
- It is a predefined data source to support the analytical requirements of Oracle Business Intelligence Applications:1. Relevant data structures support Oracle BI Applications
- It is in a star schema format
- It is included with Oracle BI Applications (not available with standalone OBIEE purchases)
Data Warehouse Administration Console
- Data Warehouse Application Console (DAC) Client:
Used to schedule, monitor, configure, and customize OBAW extraction, transformation, and load
Accesses metadata about ETL mappings and dependencies in the DAC repository
- DAC Server:
1. Organizes ETL requests for processing
- Third-party Informatica Server populates the OBAW from the transactional Database (PeopleSoft database):
1. Uses extract, transform, and load (ETL) routines
Sample Request Processing
- User views a Dashboard or submits an Answers request
- The Oracle BI Presentation Server makes a request to the Oracle BI Server to retrieve the requested data
- The Oracle BI Server, using the repository file, optimizes functions to request the data from the data sources
- The Oracle BI Server receives the data from the data sources and processes as necessary
- The Oracle BI Server passes the data to the Oracle BI Presentation Server
- The Oracle BI Presentation Server formats the data and sends it to the client
REAL-TIME Implementation :
- Oracle BI components are often implemented across several computers on the network
Clustering Oracle BI Servers
Cluster Server Feature:
Subscribe to our youtube channel to get new updates..!
1. Allows up to 16 Oracle BI Servers in a network domain to act as a single server
2. Servers in cluster share requests from multiple Oracle BI clients, including Oracle BI Answers and Oracle BI Delivers
Cluster Controller is a primary component of the Cluster Server feature:
1. Monitors status of resources in a cluster and performs session assignment as resources change
2. Supports detection of server failures and failover for ODBC clients of failed servers
4. OBIEE Repository Basics
Stores the metadata used by the OBIEE Server in a file format with.RPD as an extension
Is accessed and exposed using the Administration Tool
Is created by the server architect who:
1.Imports – metadata from databases and other data sources
2. Simplifies and reorganizes the metadata into business models
Structures the business model for presentation to users for requesting information
Exposes the Oracle BI repository in three separate panes, called layers
Business Model and Mapping layer
Building Physical Layer of a Repository
After completing this chapter you will be able to:
Identify the objects in the Physical layer of the repository
Define a System Data Source Name (DSN) for a data source
Build the Physical layer Why you need to know:
Enables you to perform the first step of creating a repository, building the Physical layer
Provides the access to the data sources against which users will generate reports
Is the metadata that describes the source of the analytical data
Defines what the data is, how the data relates, and how to access the data
Is used by the OBIEE Server to generate SQL to access the business data to provide answers to business questions
Specifies the ODBC or native data source name
Defines how the OBIEE Server connects to the data source
Allows multiple users to share a pool of database connections
May create multiple connection pools to improve performance for a group of users
Table Types: Tables within the physical schema could be a:
Physical table – table existing in the database
Alias table – an alias of the physical table imported to the physical layer
Select Statement – forms the Opaque view in the physical layer of the repository
Foreign Key Joins
Represent the Primary Key – Foreign Key relationship between the tables in the Physical layer
Used to formulate the join when building the SQL
Complex joins are used to express relationships that do not involve a Primary Key – Foreign Key relationship
Import Data Source Schemas (Continued)
Select the tables and columns required to support the business model
Limit to tables and columns required to support the users’ analytical requirements
Building Physical Layer – Best Practices: Import only those tables that are likely to be used in the business models
Avoid importing joins from the database as it can lead to circular joins while defining custom join conditions in the physical layer
Import the database views, synonyms, and system tables only when required
Importing unnecessary database objects adds unnecessary complexity and increases the size of the repository
After importing the tables from the database, define/re-define the primary keys for every table.
Use complex joins to join the physical tables on non-primary keys or to specify an expression.
Expected questions on the physical layer
Q1) What is the physical layer?
Ans: Represents the physical structure of the data sources to which the Oracle BI Server submits queries. This layer is displayed in the right pane of the Administration Tool.
Q2) What is isolation level property in the connection pool?
Ans: Isolation level on each connection to the back-end database. The isolation level setting controls the default transaction locking behavior for all statements issued by a connection. Only one of the options can be set at a time. It remains set for that connection until it is explicitly changed.
Q3) How many maximum numbers of connections can be defined in a connection pool?
Ans:The maximum number of connections allowed for this connection pool. The default is 10. This value should be determined by the database make and model and the configuration of the hardware box on which the database runs as well as the number of concurrent users who require access.
Q4) What are parameters supported option in connection Pool?
Ans: If the database features table supports parameters and the connection pool check box property for parameter support is unchecked, the special code executes that allows the Oracle BI Server to push filters (or calculations) with parameters to the database. The Oracle BI Server does this by simulating parameter support within the gateway/adapter layer by sending extra SQLPrepare calls to the database
Q5) What is shared login option in connection Pool?
Ans: Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same username and password.
If this option is selected, then all connections to the database that use the connection pool will use the username and password specified in the connection pool, even if the user has specified a database username and password in the DSN (or in user configuration).
If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile
Q6) How do I create an alias to a table in the physical layer?
Ans: Right, Click the original table. Choose “New Object” and choose “Alias”. The mistake most people make is trying to bring in the same table again and rename it or choose “Duplicate” by right clicking the original table.
Q7) What is the use of Alias table?
Ans: An existing table can be used more than once by using the alias table.
Q8) How to deploy an opaque view?
Ans: We can deploy an opaque view in the physical database using the Deploy View(s) utility.
Building Business Model and Mapping Layer
BMM Layer: Objective
After completing this module you will be able to:
Identify the objects in the Business Model and Mapping (BMM) layer of a repository
Build the business model
Create simple measures
Why you need to know
Enables you to perform the second step of creating a repository,
building the Business Model and Mapping layer
Business Model and Mapping(BMM) Layer
Is the metadata that organizes physical sources into a dimensional business model
Is the basis of the users’ view of the data
Is used by the Oracle BI Server to determine the data necessary to satisfy the user’s request
Is created using the Administration Tool
Is the second layer built in the repository
BMM Layer Objects
BMM Layer objects include
Data source mappings
Logical primary keys
Logical table schemas
Define subject areas
Are the highest level objects in the BMM layer
Define data source mappings
Allow multiple physical data sources in one logical table
Have one or more logical columns
Data Source Mapping
Define the logical-to-physical table mappings
Reduce the complexity of the data model, allowing multiple physical data sources in one logical table
Represent the business view of the data
A single logical column might map to many physical columns
Logical Table Schemas
Define logical table joins Required for a valid business model.
Are calculations defining measurable quantities
Are created in the logical fact tables
Aggregation rules such Sum, Avg, Count can be set on the measure columns
Create a Logical Business Model
Right-click inside the Business Model layer and select New Business
Modify Logical Columns
Open the Logical column properties box to modify its properties
Consider renaming columns to make it meaningful to users
Rename Wizard can be used to rename the logical columns
Create Logical Joins
Open the Business Model Diagram to define the relationship between the logical tables
Right-click the logical table and select Business Model Diagram
Only complex joins should be used in the BMM layer as it gives the Oracle BI Server flexibility to select the most economical join path between the logical tables
Expected Questions on Bmm layer
Q1) Define what is BMM layer and steps involved for creation?
Ans: The Business Model and Mapping layer of the Administration Tool defines the business, or logical, model of the data and specifies the mappings between the business model and the Physical layer schemas.
Create a Business Model Create Logical Tables Create Logical Columns Create Logical Joins
Rename Business Model Objects
Delete Unnecessary Business Model Objects Build Dimension Hierarchies.
Q2) Did you create any new logical column in the BMM layer, how?
Ans: Yes. We can create a new logical column in BMM layer.
Example: Right click on fact table - a new logical column-give name for a new logical column like Total cost. Now in fact table source, we have one option column mapping, in that we can do all calculation for that new column.
Q3) Can you use physical join in BMM layer?
Ans: yes, we can use physical join in BMM layer. when there is SCD type 2 we need complex join in BMM layer.
Q4) Can you use an outer join in BMM layer?
Ans: yes, we can. When we are doing complex join in BMM layer, there we have options left, right, full outer joins
Q5) What is a logical table Source?
Ans: Logical table sources define the mappings from a single logical table to one or more physical tables. A logical table contains one or more logical table source. The mapping between physical columns and logical columns are done in this element
The definition of the logical table source is used:
to specify transformations/calculations (in the column mapping tab),
to enable aggregate navigation and fragmentation (in the content tab) The Content tab of the Logical Table Source is used to define :
any aggregate table content definitions (specify the grain of the physical tables),
fragmented table definitions for the source,
and WHERE clauses (if you want to limit the number of rows returned).
Q6) How to set aggregation rule for a measure column?
Ans: To specify a default aggregation rule for a measure column
In the Business Model and Mapping layer, double-click a logical
In the Logical Column dialog box, click the Aggregation
In the Aggregation tab, select one of the aggregate functions from the Default Aggregation Rule drop-down
The function you select is always applied when an end user or an application requests the column in a query.
Q7) Can a business model have a logical table source from different physical data sources?
Ans: Yes. If different types of physical database sources are used then the Obiee server has to perform merge functionality using the data sets retrieved from disparate DBs.
Building Presentation Layer of a Repository
Objective: After completing this chapter you will be able to:
Identify the objects of the Presentation layer of a repository
Modify the properties of the Presentation layer objects
Build the Presentation layer of a repository Why you need to know:
Enables you to perform the third step of creating a repository: building the Presentation layer of the repository
Provides the actual presentation of a business model to users
Is the metadata that describes the users’ view of the business model
Simplifies the model and makes it easy for users to understand and query
Exposes only the data meaningful to the users
Organizes the data in a way that aligns with the way users think about the data
Renames data as necessary for the set of users
Is created using the Oracle BI Administration Tool
Is the third layer built in the repository
Organize and simplify the business model for a set of users
Refer to a single business model; cannot span business models
Multiple presentation catalogs can refer to the same business model
Presentation Tables and Columns
Define the interface the user uses to query the data from the data sources
Appear as folders and columns in Oracle BI Answers
Refer to a subset of the logical tables and logical columns in the business model layer
Use terminologies that are meaningful to the users
Create a new Presentation Catalog
Drag and drop a business model from the Business Model layer to the Presentation layer
Select Edit > Duplicate to copy an existing catalog
Corresponding presentation layer objects are automatically created for business model layer objects
Customize the Presentation Catalog
Organize and modify the presentation objects so that they make sense to the users
Reorder tables and columns
Rename tables and columns
Remove columns like primary keys that serve no business need
Use a nested folder structure to organize the presentation tables
Open the presentation catalog properties box and use the Up and Down buttons or drag and drop to reorder the tables
In the presentation table properties box, use the columns tab and use the Up and Down buttons or drag and drop to reorder columns
In the presentation table properties box, use the general tab to rename tables
In the presentation table properties box, use the columns tab and use the Up and Down buttons or drag and drop to reorder columns
Use the General tab to rename columns in the Presentation Column properties box
An alias is created automatically when the name of a presentation column is changed
Remove Unwanted Columns
Right-click and select delete to remove unwanted columns
Remove keys that are used only for processing, unless they have an intrinsic meaning, such as date
Presentation Layer – Best Practices
- Use meaningful names in the presentation layer of the repository
- Names cannot contain single Admin tool prevents it
- Use of double quotes is permitted but should be avoided
- Keep presentation object names unique
- Naming presentation columns same as the presentation tables can lead to inaccurate results
- Group fields into folders and subfolders
- Separate numeric and non-numeric quantities into separate folder set
- Remove any unwanted columns for ease of use and understandability
- Key columns that have no business meaning
Expected Questions on Presentation Layer
Q1) What is the Presentation Layer?
Ans: The Presentation layer provides a way to present customized views of a business model to users. Presentation Catalogs in the Presentation layer (called Subject Area in Oracle Answers) are seen as business models by Oracle BI Presentation Services users.
Q2) How to create a presentation catalog?
Ans: There are several ways to create a Presentation Catalog in the Presentation layer. The recommended method is to drag and drop a business model from the Business Model and Mapping layer to the Presentation layer and then modify the Presentation layer based on what you want users to see.
Q3) Define steps to create presentation table?
Ans:To create a presentation table
1. Right-click a catalog folder in the Presentation layer, and then select New Presentation Table from the shortcutThe Presentation Table dialog box appears.2. In the General tab, specify a name for the3. Click the Permissions button to open the Permissions dialog box, where you can assign user or group permissions to the4. (Optional) Type a description of the table.
Q4) How to reorder a presentation column?
Ans: To reorder a presentation column
Right-click a presentation table in the Presentation layer, and then select
Click the Columns
Select the column you want to
Use drag-and-drop to reposition the column, or click the Up and Down
Q5) What is the use of the alias tab option of Presentation layer dialog box?
Ans: An Alias tab appears on the Presentation Catalog, Presentation Table, and Presentation Column dialog boxes. You can use this tab to specify or delete an alias for the Presentation layer objects.
Q6) Can a Presentation Catalog have tables from different Business Model?
Ans: No. It is not possible.
Q7) List a few reasons to have Presentation Layer?
Enables to group the dimensions and facts based on the organizational
Example: Departmental/Cross Departmental Subject Areas.
To implement Object Level To remove columns that serves the ONLY purpose of ‘key’ in the Logical Model.
5. Testing and Validating a Repository Objective:
After completing this chapter you will be able to:
Describe techniques for testing a repository
Execute steps to validate a repository Why you need to know:
Testing and validating the repository file before making it available to users helps ensure implementation success
Validating the Repository
Verifies that the business models yield correct results
Validation techniques include:
Checking repository for consistency
Turning on logging
Checking business model via Oracle BI ODBC Client
Using Oracle BI Answers
Checking results by inspecting SQL
Check Repository for Consistency
A feature in the Oracle BI Administration Tool that checks the metadata for certain kinds of errors, which include:
Finding logical columns that are not mapped to physical sources
Checking for undefined logical join conditions
Determining if physical tables referenced in a business model are not joined to tables referenced in the business model
Does not guarantee that the business model is constructed
Turn on Logging
Test proper repository configuration by logging query activity
Use logging for testing, debugging, and technical support
Query activity gets logged in a log file
Logging should be enabled for individual users
Set logging level for each user whose queries you want to be logged
Query logging is disabled by default
Can quickly produce very large log files
|Level 1 Logs||Level 2 Logs|
|Username, session ID and request ID for each query||All Items as in Level 1|
|SQL for the request using business model names||Repository name, business model name and presentation catalog name|
|Query Status ( Success, failure, termination or timeout)||SQL for the request using physical data source syntax|
|Elapsed times for query compilation, execution, query cache, and back-end database processing||Queries issued against the cache|
|Number of rows returned from the physical database|
|Number of rows returned to the client
Any errors are displayed in a dialog box
Correct the errors and check for consistency again
Repeat the process until there are no more errors
Testing and Validating Repository: Summary
A repository should be tested and validated for correctness before deploying the application.
Check repository consistency to ensure that there are no errors in the mappings and joins between the
Turn on logging levels for the users to check the queries generated by the Oracle BI
Data should be validated by executing queries in Oracle BI ODBC client and Oracle BI Answers and checking the query log
Expected questions on Testing and validating Repository
Q1) Define steps for Testing and validating repository?
Ans:To test and validate a repository, you perform the following steps:
a)Run a Consistency Check
b)Enable Query Logging
d)Start Oracle BI Services
e)Use Oracle BI Answers to Execute Queries
f)Use Query Log to Verify Queries.
Q2) What is the need to save, even after the changes are checked in when a repository is loaded in Online Mode?
Ans: style="color: #ffffff; font-family: georgia, serif; text-align: center; background-color: #e94350; padding: 10px 20px;" title="Check Out Admin Interview Questions" href="../../obiee-administration-interview-questions" target="_blank" rel="noopener">Check Out Admin Interview Questions
During the Check-In, the changes made in the repository are saved into Memory, but NOT yet saved into the physical RPD file and hence save should be performed.
Q3) What are the operational modes supported by ObI Administration Tool? OnLine. – A repository is loaded into memory by BI Server?
Ans: OffLine. – A repository is NOT loaded into memory by BI server.
Q4) When a repository is opened in the ON-Line mode the BI Administration Tool lists a dialog box that lists all DSN What is the significance of “Load all objects” checkbox?
Ans: If this check box is checked then BI loads each and every object that is loaded into the memory, otherwise only higher-level objects are
populated, other objects are populated on demand (when the higher level objects are expanded). It is advisable to keep this checkbox unchecked when a huge repository is opened using online mode.
Adding Multiple Sources to Dimension
Model multiple sources for the logical table
- Add Physical table source to a logical table1. Add physical tables to an existing logical table source2. When data is not duplicated across tables
- Add Logical table source to a logical table
1. Add a second logical table source that maps to the physical table that is the most economical sourceWhen data is duplicated across tables
2. Adding Physical Table Source
To add a physical table source to a logical table
Import physical sources and create joins
Determine the physical columns
Drag columns onto the existing source
Automatically adds the physical table sources, joins, and column mappings
Rename the columns
Add columns to presentation catalog
Determine the Physical Columns
Locate the tables and columns in the physical layer with the additional information
Drag Columns to Existing Source
Drag each physical column onto the existing logical table source of the Customers dimension
Creates the mapping, joins, and new columns in the Customers dimension automatically
Drag Columns to Existing Source (Continued)
Maps the new logical columns to the physical tables automatically
?Job Responsibilities Of a OBIEE Developer