This tutorial gives you an overview and talks about the fundamentals of OBIEE.
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 home grown 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 business performance of the enterprise is difficult.
Oracle business intelligence is a system that provide 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 View
Enables a single consistent and logical view of enterprise across multiple heterogeneous source 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 an 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), Sieble 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 provides 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 set of common core functionality (like security, scalability etc) across various Oracle products. This is implemented with weblogic domain as shown is the diagram (Oracle BI domain).
The Oracle BI domain consists of two components
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.
This consists of a set of admin server to manage the processes running in the domain.
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 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
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 upgrades activities & Deploy patches in server.
Maintain the Platform Availability.
Resolve Data Quality, Security issue and Report performance issue.
Software installation in server.
Provide access to end user and maintaining entire application
Assign 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 track
• Identify where things are going wrong
• Take and monitor corrective actions
• Spot trends
- Show me the most effective promotions
- Show me customers most likely to switch
- Show me products that are not profitable
- Compare sales this quarter with sales a year ago
- Show 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:
v Are a complete, prebuilt BI solutions that deliver intuitive, role-based intelligence for everyone in an organization
v Oracle BI Applications are built on the Oracle Business Intelligence Enterprise Edition
v 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:
- Web Clients
- Oracle BI Presentation Server
- Oracle BI Server
- Oracle BI Scheduler
- DAta Sources
Provide access to bi information
Ø Web browser
Ø Enterprise application
Ø Corporate portals
Oracle BI Presentation Server
Ø Provides the processing to visualize the information for client consumption
1. Is implemented as an extension to a web server
2. 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:
1. Repository file (.rpd)
5. Log files
1. Repository file (.rpd)
1. Contains metadata that represents the analytical model
2. Is created using the Oracle BI Administration Tool
3. Is divided into three layers:
1. Physical layer – represents the data sources
2. Business and Mapping layer – models the data sources into facts and dimensions
3. 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 database:
- Speeds up request processing
3 . NQSConfig.INI :
Ø Is a configuration file used by the Oracle BI Server at startup
Ø Specifies 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. Defines 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 to be delivered to users at specified times
Ø In windows, the scheduler runs as a service
Data Sources :
Ø 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
Ø Is a predefined data source to support the analytical requirements of Oracle Business Intelligence Applications:
1. Relevant data structures support Oracle BI Applications
Ø Is in a star schema format
Ø 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
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 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:
- Imports – metadata from databases and other data sources
- 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
- Physical layer
- Business Model and Mapping layer
- Presentation layer
4.1 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
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
- Stored Procedure
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 physical layer
Q: What is physical layer?
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.
Q: What is isolation level property in connection pool?
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.
Q: How many maximum number of connections can be defined in a connection pool?
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.
Q: What is parameters supported option in connection Pool?
If the database features table supports parameters and the connection pool check box property for parameter support is unchecked, 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
Q: What is shared logon option in connection Pool?
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 user name and password.
- If this option is selected, then all connections to the database that use the connection pool will use the user name and password specified in the connection pool, even if the user has specified a database user name 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
Q: How do I create an alias to a table in physical layer?
Right Click the original table. Choose “New Object” and choose “Alias”. The mistake most people make is try to bring in the same table again and rename it or choose “Duplicate” by right clicking the original table.
Q: What is the use of Alias table?
An existing table can be used more than once by using alias table.
Q: How to deploy an opaque view?
We can deploy an opaque view in the physical database using the Deploy View(s) utility.
4.2 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
- Business models
- Logical tables
- Data source mappings
- Logical columns
- 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 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
Q. Define what is BMM layer and steps involved for creation?
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.
Q. Did you create any new logical column in BMM layer, how?
Yes. We can create new logical column in BMM layer.
Example: Right click on fact table -new lgical column-give name for 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.
Q. Can you use physical join in BMM layer?
yes we can use physical join in BMM layer.when there is SCD type 2 we need complex join in BMM layer.
Q. Can you use outer join in BMM layer?
yes we can.When we are doing complex join in BMM layer ,there we have options left,right,full outer joins
Q. What is a logical table Source ?
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).
Q. how to set aggregation rule for a measure column?
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.
Q. Can a business model have logical table source from different physical data sources?
Yes. If different types of physical database sources are used then the Obiee server has to perform mergefunctionality using the data sets retrieved from disparate DBs.
4.3 Building Presentation Layer of a Repository
Building Presentation Layer: Objectives
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 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 can not 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 sub folders
- Separate numeric and non numeric quantities into separate folder sets Ø Remove any unwanted columns for ease of use and understandability Ø Key columns that have no business meaning
Expected Questions on Presentation Layer
Q: What is presentation Layer?
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.
Q: How to create presentation catalog?
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.
Q: Define steps to create presentation table?
To create a presentation table
1. Right-click a catalog folder in the Presentation layer, and then select New Presentation Table from the shortcut
The Presentation Table dialog box appears.
2. In the General tab, specify a name for the
3. Click the Permissions button to open the Permissions dialog box, where you can assign user or group permissions to the
4. (Optional) Type a description of the table.
Q: How to reoreder a presentation column?
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
Q: What is the use of alias tab option of Presentation layer dialog box?
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.
Q: Can a Presentation Catalog have tables from different Business Model?
No. It is not possible.
Q: List 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
- 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 log file
- Logging should be enabled for individual users
- Set logging level for each user whose queries you want logged
- Query logging is disabled by default
- Can quickly produce very large log files
|Level 1 Logs
||Level 2 Logs
|User name, 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
- 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
Q: Define steps for Testing and validating repository?
To test and validate a repository, you perform the following steps:
a)Run a Consistency Check
b)Enable Query Logging c)Modify NQSConfig.ini d)Start Oracle BI Services
e)Use Oracle BI Answers to Execute Queries f)Use Query Log to Verify Queries.
Q: What is the need to save, even after the changes are checked in when a repository is loaded in Online Mode?
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.
Q: What are the operational modes supported by ObI Administration Tool? OnLine. – Repository is loaded into memory by BI Server.
OffLine. – Repository is NOT loaded into memory by BI sever.
Q. 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” check box?
If this check box is checked then BI loads each and every object that are 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 check box unchecked when a huge repository is opened using on-line mode.
6. Adding Multiple Sources to Dimension
Model multiple sources for the logical table
Ø Add Physical table source to a logical table
1. Add physical tables to an existing logical table source
2. 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 source
When 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 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