OBI cheat sheet

The Oracle BI Server is the central component: it parses logical SQL from clients, builds and executes physical queries against sources, and returns result sets.

The BI Presentation Services (Presentation Server) renders dashboards and analyses, stores catalog objects, and communicates with the BI Server via ODBC (nqsserver/nqscluster).

The repository (RPD) is a binary file containing three layers: Physical, Business Model and Mapping (BMM), and Presentation.

Oracle BI Administration Tool is the Windows client used to create and edit the RPD; it can open offline or online against a running BI Server.

The Oracle BI Scheduler service (nqscheduler) runs scheduled jobs (agents/ibots) and stores job metadata in the Scheduler schema.

Oracle BI Cluster Controller (nqsclustercontroller) coordinates multiple BI Server instances for scale-out and failover.

The JavaHost process handles charting, BI Publisher, and some map/geo rendering; Presentation Services calls it for graph generation.

OPMN/Node Manager or WebLogic (12c+) manages process lifecycle; 11g uses OPMN, 12c uses WebLogic Server and Node Manager.

Clients connect via ODBC/JDBC to the BI Server or via HTTP to Presentation Services; logical SQL is the BI Server's query language.

Physical layer: tables, columns, joins, keys, and connection pools; foreign keys defined here drive join inference in the BMM.

BMM layer: logical tables, logical columns, logical joins, and measures; business logic (aggregation rules, calculations) lives here.

Presentation layer: subject areas exposing columns to users; it maps to BMM columns and controls what clients see.

Aggregation rules: SUM, COUNT, AVG, MIN, MAX, and 'Aggregate by dimension'-set on logical columns and used by the server for rollups.

Logical table sources: multiple sources per logical table with priority and fragmentation rules; the server picks the cheapest source for a query.

Dimensions and hierarchies: define levels and keys in BMM to enable level-based measure calculation and drill-down.

Aliases and complex joins: physical aliases handle self-joins and role-playing dimensions; complex joins support non-equi or multi-column conditions.

Variables: session, repository, and presentation variables; 'Init blocks' populate session variables at login from SQL.

Time dimensions: use a logical time dimension with levels (Year, Quarter, Month) to support 'to-date' and period-ago calculations.

Each subject area in the Presentation layer appears as a subject area in Answers/Analyses; columns are grouped into folders.

Presentation columns can be renamed independently of BMM names, and their display format (e.g., currency, percent) is set here.

Presentation tables can be nested to create folder hierarchies; the 'Presentation Table' object controls grouping in the UI.

Use 'Implicit Fact Column' to set a default fact when a query includes only dimensions.

Set 'Default' aggregation and 'Sort order' on presentation columns to control client behavior.

The Presentation layer can expose 'Hierarchies' for drill-down; these map to BMM dimensional levels.

Repository merge: use the Administration Tool's Merge utility to combine RPDs; conflicts are resolved per object.

Online RPD changes: in 11g, some changes can be made online with 'Check Global Consistency'; full changes require offline editing and restart.

Security in the Presentation layer: set permissions on subject areas and columns via the 'Manage Privileges' and 'Application Roles' pages.

An Analysis (formerly 'Answer') is a query built from a subject area; it contains columns, filters, views, and formatting.

Views include Table, Pivot Table, Chart, Graph, Narrative, Title, Column Selector, View Selector, and Logical SQL.

Filters: 'is equal to', 'is greater than', 'is in', 'is null', and 'SQL expression' filters; filters are applied at the BI Server or Presentation layer.

Prompts: dashboard prompts and analysis prompts; presentation variables are set by prompts and referenced as @{varname}.

Dashboard pages can contain sections, columns, and objects; use 'Dashboard Properties' to set page layout and permissions.

Agents (ibots) schedule analyses and deliver results via email, FTP, or the catalog; they run under the Scheduler service.

Conditional formatting: use 'Conditional Formatting' on a column to apply color/icon based on a condition.

Drill-down: enable 'Drill Down' on a hierarchy; the client shows levels and allows navigation.

KPI and scorecard objects are available in some editions; they rely on the BI Server and Presentation Services.

Authentication: options include LDAP, Oracle Database, WebLogic (12c), and custom authenticators; configured in 'Security' in the Administration Tool.

Authorization: users and groups are defined in the RPD; group membership drives object permissions.

Application roles: defined in the RPD and in the WebLogic/EM console; they control access to Presentation Services features.

Object permissions: set on subject areas, tables, columns, and rows via the 'Permissions' dialog; 'Read' and 'Write' are the main privileges.

Row-level security: use 'Data Filters' (formerly 'Row-Level Security') with a logical SQL predicate; e.g., 'WHERE Customer.Region = VALUEOF(NQ_SESSION.REGION)'.

Session variables can be set from an init block using 'SELECT ... FROM ... WHERE user = :USER'; they are referenced as VALUEOF(NQ_SESSION.varname).

Privileges: 'Manage Privileges' controls who can create dashboards, agents, etc.; it is separate from object permissions.

SSL: configure between clients and Presentation Services, and between Presentation Services and the BI Server, using certificates.

Audit: enable 'Audit' in the RPD to log queries and user actions to a database table.

Query caching: the BI Server caches result sets in memory and on disk; cache is keyed by logical SQL and user/session context.

Cache settings: 'Cache' and 'Cache persistence time' on the physical table; 'Enable cache' in the server configuration.

Cache seeding: use 'Cache Seed' in the Administration Tool or 'Event Polling' to pre-populate cache.

Aggregate navigation: the server uses aggregate tables (logical table sources) to answer queries at higher levels; define aggregates and set priorities.

Indexes and statistics: ensure physical tables have appropriate indexes and up-to-date statistics for the source database.

Query plan: use 'Manage Sessions' > 'View Log' and the 'Query' tab to see the physical SQL and timing.

BI Server parameters: 'MAX_ROWS', 'MAX_QUERY_TIME', 'CACHE_MAX_ROWS', and 'CACHE_MAX_ENTRIES' control limits.

Connection pooling: set min/max connections per pool; too many connections can overload the source.

Partitioning: use 'Fragmentation' in logical table sources to split large tables by date or region.

BI Publisher integration: register BI Publisher as a server in the RPD and use 'BI Publisher' reports in dashboards.

Essbase integration: define an Essbase connection and import cubes as physical tables; use 'Essbase' functions for MDX.

ODBC/JDBC: clients can connect directly to the BI Server using the Oracle BI ODBC driver; logical SQL is the interface.

Web Services: the BI Server exposes SOAP/REST web services for metadata and query; used by external applications.

Migration: use 'Export' and 'Import' in the Administration Tool or the 'rpd' command-line utilities (e.g., biserverxmlgen).

Backup: back up the RPD, catalog, and Scheduler schema; the catalog is stored in the file system or database.

Monitoring: use 'Manage Sessions' in Presentation Services and 'Fusion Middleware Control' (11g/12c) for metrics.

Patch and upgrade: apply patches to the Oracle Home; run 'Upgrade Assistant' for repository upgrades.

Log files: nqserver.log, nqquery.log, and Presentation Services logs are in the 'log' directory; set log levels in 'Manage Log Configuration'.