Best of OBIEE 11.1.1.7–What’s New

 

A couple of images which summarize what is New with OBIEE 11.1.1.7:

Best of OBIEE 11.1.1.7–Smart View

 

This post is one of a series of brief posts on some of the “best of” features as judged by my humble self on the latest OBIEE release.

To start with, something that I have been hanging out for a long time: being able to directly create reports in Excel using the Oracle BI subject areas you have painstakingly built. In the past, the Oracle BI excel plugin only allowed you to import existing reports, but not access the Oracle BI semantic layer (Oracle BI Server) directly. Smart View solves this!

Good Youtube video on this:

http://www.youtube.com/watch?v=y2WSmnb—88

 

Smart View on Oracle BI

You can now create views directly from Excel onto the Oracle BI semantic layer using the Smart View plugin. The plugin can be downloaded from Home->Desktop Tools->Smart View link on the left hand side.

After you have installed Smart View, create a Private Connection to OBIEE from within Excel. Make sure to append /jbips to your usual OBIEE URL.

Eg. http://10.245.2.180:9704/analytics/jbips

Aside from the the ability to import OBIEE analysis that have already been built into Excel, you can also create new ones in Excel proper through Oracle BI EE->View Designer.

Displayed as a Pivot Table in Excel, with all of Excel’s pivoting capabilities:

This can be then published back to the OBIEE presentation catalog with the Publish View link.

And saved as regular OBIEE analysis.

And viewed as such:

Great little plugin it seems!

How to Install Endeca Information Discovery 3.0 on Windows 7 x64

 

Oracle recommends the following installation order:
1. Oracle Endeca Server. For instructions on installing Oracle Endeca Server, including system
requirements, see the Oracle Endeca Server Installation Guide.
2. Oracle Endeca Information Discovery Integrator. For instructions on installing Oracle Endeca Information
Discovery Integrator, including system requirements, see the Integrator Installation Guide.
3. Studio (see About the Studio Installation Process on page 13). After installing, to verify the installation, log
in to Studio. Use a created Endeca data domain to provide the data source.
4. Provisioning Service (See Installing the Provisioning Service on page 54).
5. Integrator Server. For instructions about installing Integrator Server, see the Integrator Installation Guide.
6. Integrator Acquisition System. For instructions about installing IAS, see the Integrator Acquisition System
Installation Guide.

Endeca Server Installation

As per installation guide

http://docs.oracle.com/cd/E37502_01/server.751/es_install/toc.htm

 

• Download all installation files, Jrockit, WLS 10.3.6, and ADF components
• Install Oracle Java Jrockit JDK
• Install Weblogic Server 10.3.6 to new FMW folder (was not prompted for Windows service install)
• Install Oracle ADF Runtime to recently installed WLS 10.3.6
• Install Endeca Server to recently installed WLS 10.3.6 (do not use secure mode SSL)
• Create Weblogic domain for Endeca Server (endeca_domain) (used Development Mode as opposed to instructions’ Production Mode)
• Skipped SSL keys generation
• Verify installation by starting Admin Server (on default port 7001)

EndecaServer7.5.1_1\endeca-cmd>endeca-cmd create-dd testme

EndecaServer7.5.1_1\endeca-cmd>endeca-cmd get-dd-health testme

Data domain: testme
Leader Node Health:
        Hostname: DANOBRIE-NZ
        Port:7001
        Protocol:HTTP
        Is available
Follower Nodes Health:
Detail:[]

• Changed WLS listen port to port 8001 (WLS Console->Environment-Servers->Admin Server)

Total Time excluding downloads = 30 minutes

 

 

 

 

 

 

 

 

 

Download Endeca 3.0 Files from edelivery

Endeca Server

From ServerInstallGuide.pdf

Requires:

Weblogic Server 10.3.6 (wls1036_generic.jar from OTN)

Java version 6 of Oracle JRockit JDK

On my platform: Win 7 x64, Steps 11 & 12 did not materialize – ie. no need to create a Windows Service.

Warehouse Table Naming Conventions

 

These conventions are based upon Oracle’s BI Applications 7.9.6.3 standards.

The Analytic Data Warehouse (ADW) is an arbitrary name for the performance layer of your data-warehouse. The diagram below shows a typical data warehouse reference architecture with the Performance Layer circled in red.

Table Names

The ADW schema will predominantly hold tables built by ETL and queried by OBIEE

• Fact tables contain columns which can be summed, averaged, etc.
• Dimensional tables contain descriptive columns
• ADW tables to be loosely named in the format business name/grain/type
• Business name is a short business description of the table which is not tied to Northgate or e-Business Suite names
• Grain is optional and describes table granularity (useful for aggregate tables)
  • For the time dimension, the level names DAY, WEEK, MONTH, YEAR should be used
• Type is one of:
  • _A = aggregated fact table
  • _D = dimension table
  • _DH = dimension hierarchy table
  • _F = fact table
  • Staging tables (used during ETL but not by OBIEE) will have S appended to the
    • _DS = dimensional staging table
    • _FS = fact staging table
  • Materialized views should have MV appended to them
    • _DMV = dimensional materialized view
    • _FMV = fact materialized view
• By default use singular names (facts maybe plural?)
• Some examples:
  • TENANCY_D = Tenancy dimension
  • DEBT_DETAIL_F = Debt fact (lowest level detail)
  • DEBT_REGION_A = Debt fact aggregated to a regional level
  • DAY_D = Calendar dimension at day level (lowest level)
  • WEEK_D = Calendar dimension at week level
  • MONTH_D = Calendar dimension at month level
  • PROPERTY_UTILISATION_FMV = Property utilisation fact materialized view

Columns

• Column names should be business relevant, non-technical, and not tied to Northgate or e-Business Suite names
• Column suffixes:
  • WID - Identifier generated by ETL to link dimension and fact tables (except ROW_ID). For example TENANCY_WID would reference TENANCY_D.ROW_ID.
  • _ID - Source system identifier
  • _FLG - Flags Y/N
  • _IND - Indicator e.g. D,W,M
  • _DT - Date
  • _TS – Timestamp
  • _AMT - Amount
  • _CNT - Count
  • _PCT - Percentage
  • _CODE - A coded alphanumeric value that defines a possible value for a data element
  • _NAME - A short name that describes the code
  • _DESC - A long description for the code
• Other conventions:
  • _NO - For descriptive number columns, i.e. TELEPHONE_NO
  • _TXT - For text fields which contain concatenated values etc.
• System columns on each table:
  • ROW_WID - surrogate key to uniquely identify record (generated during ETL), first column in table
  • DATASOURCE_NUM_ID - Unique identifier of source system from which data was extracted. TBD numbers to use, 2nd column in table
  • INTEGRATION_ID - Unique identifier of fact or dimension in source system, 3rd column in table
  • W_INSERT_DT - Date inserted to Warehouse, at the end of table column definitions
  • W_UPDATE_DT - Date updated to Warehouse, at the end of table column definitions
  • ETL_RUN_ID - Identifier of ETL process that loaded, integrated, checked the row
• Optional on each table:
  • CREATED_ON_DT - Date created in source system
  • CHANGED_ON_DT - Date changed in source system
  • DELETED_FLG - For logical deletes of warehouse records
• Type 2 Slowly Changing Dimension columns:
  • EFFECTIVE_FROM_DT – Date that the dimensional record is effective from
  • EFFECTIVE_TO_DT – Date that the dimensional record is effective to
  • CURRENT_FLG – Is record current? Y/N
• All ADW surrogate keys should be integers and generated from a sequence
• Foreign keys should always use warehouse surrogate keys
• The W_ prefix denotes a translated to warehouse code/name/description combination. (the concept of domain values)

Notes on Time Dimensions

• Reporting, financial, and other calendars are all associated (de-normalized) onto each day of the year
• Dimensional aggregates are needed by OBIEE for aggregate navigation. So WEEK_D, MONTH_D, and YEAR_D should also be created.
• ETL updates dimension with reporting flags as necessary
• The surrogate key ROW_WID is an integer representation of the period, of the format YYYYMMDD or YYYYMM or YYYY

Example Physical Table Column Conventions

Domain	Datatype	Notes
AMT	Number (12,2)	Amount
CNT	Integer	Count
CODE	Varchar2 (20)	Code e.g. TUS, TUD
DD	Number (2)	Day of month e.g. 1-31
DDD	Number (3)	Day of year e.g. 1-366
DESC	Varchar2 (200)	Code description
DS	Number (2)	Datasource e.g. 1=EBS
DT	Date	Date and time
FLG	Char (1)	Y/N flag
ID	Varchar2 (12)	Identifier
IND	Varchar2 (3)	Indicator e.g. D,W,M,Q,A
MM	Number (2)	Month of year e.g. 1-12
NAME	Varchar2 (250)	Name
NO	Number (12)	Number
PCT	Number (3,3)	Percentage
QQ	Number (2)	Quarter of year e.g. 1-4
TS	Timestamp	Timestamp
TXT	Varchar2 (200)	Text string e.g. +64 4 1234567
WW	Number (2)	Week of year e.g. 1-53
WID	Number (12)	Warehouse Identifier i.e. rowid
YYYY	Number (4)	Year e.g. 2012

 

Refresher on Slowly Changing Dimensions Types

(from Wikipedia)

OBIEE Naming Conventions, Standards, and Tips

 

Further to my previous blog on modelling the RPD, below is a more detailed explanation.

Before you start, make sure you are familiar with OBIEE modelling techniques. A good place to start is the Introduction chapter in the Admin Tool help. Modelling is done with Admin Tool, and the file modelled is name the Repository or RPD (its extension). The RPD can be opened in online mode when there few developers. Backup the RPD regularly.

Best Practise

Oracle's BI Applications RPD file is available as a best practise reference. The conventions in this document follow many of the BI Applications modelling standards. My initial post also lists the basic RPD modelling steps. 

BI Applications RPD

General Conventions and Tips

• Do not tie any names to source system implementation
• The goal of the logical layer is to simplify a complex physical implementation: thus re-use existing logical tables, do not recreate a logical table for the same business concept
• In online mode, save backups of the online repository before and after every completed unit of work. If needed, use Copy As on the File menu to make an offline copy containing the changes.
• Use the Physical Diagrams in the Administration Tool to verify sources and joins.
• Use business readable names and descriptions in most places (apart from Physical Layer and Variables)
• Enable query logging to test the physical SQL generation

Physical Layer Tips

This is where access to actual resources are defined, be they databases, files, cubes or other. The main task here is to define databases, connections, physical tables, aliases, keys, and joins of an actual implementation.

• Use the Import Metadata feature to import tables to the physical layer
• Always work with aliases not physical objects. This allows for object reuse for certain dimensions (ie. the physical table DAY_D could be reused for multiple date dimensions). This allows you to:
  • Eliminate all physical joins that cross dimensions (inter-dimensional circular joins) by using aliases
  • Eliminate all circular joins (intra-dimensional circular joins) in a logical table source in the Physical Model by creating physical table aliases. For example, say you have a Customer table that can be used to look up ship-to addresses, and using a different join, to look up bill-to addresses. Avoid the circular joins by aliasing the table in the Physical layer so that there is one instance for each purpose, with separate joins.
• Opaque views (a Physical layer table that consists of a SELECT statement) should be used only if there is no other solution to your modeling problem

• Name aliases Dim_ or Fact_ for example the alias Dim_TENANT_D would point to the physical table TENANT_D
• Create joins on the aliases - not the underlying

• Keys are created on dimensional aliases (these are done automatically when you create joins)

• Foreign keys are created on fact aliases (these are done automatically when you create joins)

Business Model and Mapping Layer Conventions and Tips

This layer is often termed the logical layer and is where the physical layer is simplified into a single business representation.

• The business model should ideally resemble a simple star schema in which each fact table is joined directly to its dimensions.

• Never use technical terms on this layer, all terms should be business orientated.

Work under single business model, eg. HNZC

• Name logical tables Dim - [business name] or Fact - [business name]. For example Dim - Tenant, or Fact - Debt.

• Name logical dimensions the same as the logical table names.

• Use the Admin Tool->Tools-Utilities-Rename Wizard to rename en masse logical columns with the following rules:
  • Change each occurence of '_' into a space
  • All text lowercase
  • First letter of each word capital

• Remove columns that are not needed
• Use the description field for logical tables columns, as this is made available to users through tooltips in the Analysis Editor

Tooltip

• Every logical fact table must join to at least one logical dimension table. Note that when the source is a fully denormalized table or flat file, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables.
• Every logical dimension table should have a dimensional hierarchy associated with it. This rule holds true even if the hierarchy has only one level

Set the Content tab for each logical table source to reflect the grain of each fact and dimension

• Typically, logical fact tables should not contain any keys. The only exception is when you need to send Logical SQL queries against the Oracle BI Server from a client that requires keys. In this case, you need to expose those keys in both the logical fact tables, and in the Presentation layer.
• Normally, all columns in logical fact tables are aggregated measures, except for keys required by external clients, or dummy columns used as a divider. Other non-aggregated columns should instead exist in a logical dimension table.
• Aggregate sources should be created as separate logical table sources. For fact aggregates, use the Content tab of the Logical Table Source dialog to assign the correct logical level to each dimension
• Each dimension level in a hierarchy must have a unique level key. Also, each logical dimension table must have a unique primary key. Normally, this key is also used as the level key for the lowest hierarchy level
• Renaming columns in the Business Model and Mapping layer automatically creates aliases (synonyms) for Presentation layer columns that have the property Use Logical Column Name selected
• To prevent problems with aggregate navigation, ensure that each logical level of a dimension hierarchy contains the correct value in the field named Number of elements at this level.
• Model outer joins only when necessary. Outer joins are always defined in the Business Model and Mapping layer. Physical layer joins do not specify inner or outer.

Presentation Layer Convention and Tips

The presentation layer is the exposed layer to ODBC query clients including the OBIEE presentation services component. This is the layer that users will see in the Analysis Editor when they build queries.

• Group subject areas around business relevant functions, such as Debt Management. These relate to logical stars, for analysis.

• Describe each subject area

• Name presentation folders the same as logical tables but remove the Dim - or Fact - prefix.
• In a subject area, sort presentation folders first by dimension then fact, then alphabetically

• So that it is displayed in the Select Subject Area drop down.

• Because there is no automatic way to synchronize all changes between the Business Model and Mapping layer and the Presentation layer, it is best to wait until the Business Model and Mapping layer is relatively stable before adding customizations in the Presentation layer.
• There are many ways to create subject areas, such as dragging and dropping the entire business model, dragging and dropping incremental pieces of the model, or automatically creating subject areas based on logical stars or snowflakes.
• It is a best practice to rename objects in the Business Model and Mapping layer rather than the Presentation layer, for better maintainability. Giving user-friendly names to logical objects rather than presentation objects ensures that the names can be reused in multiple subject areas. Also, it ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model.
• Be aware that members in a presentation hierarchy are not visible in the Presentation layer. Instead, you can see hierarchy members in Answers.
• You can use the Administration Tool to update Presentation layer metadata to give the appearance of nested folders in Answers.
• When setting up data access security for a large number of objects, consider setting object permissions by role rather than setting permissions for individual columns
• You can show fewer columns than exist in the Business Model and Mapping layer. For example, you can exclude the key columns because they have no business meaning.
• You can set permissions to grant or deny users access to individual subject areas, tables, and columns.
• You can export logical keys to ODBC-based query and reporting tools.
• You can create multiple subject areas for a single business model.
• You can create a list of aliases (synonyms) for presentation objects that can be used in Logical SQL queries. This feature lets you change presentation column names without breaking existing reports.

Initialization Blocks and Variables

• Name all repository variables (static, dynamic, session) in upper case with _ instead of spaces. Ie. for example CURRENT_DATE.

• Name initialize blocks in plain text. Example: Authorization.

How to Create a OBIEE Virtual Machine on a Shoestring

 

It is sometimes useful to have an all in one OBIEE 11g virtual machine for purposes such as training. The following steps show how a bare bones OBIEE 11g and Oracle 11g database was setup on a Windows 2003 SP2 32-bit platform with 3600 MB RAM.

Inventory:

• Vmware Player 5.0
• Windows 2003 SP2 32-Bit
• Oracle 11gR2
• RCU 11.1.1.6.0
• OBIEE 11.1.1.6.0

 

I setup the initial virtual machine with: 3600 MB Ram, 2 CPUs, and 50 GB disk (c: 10 GB, d: 40 GB for OBIEE 11g software, Database software, and database itself).

I setup the network mode to Host Only.

After installing Windows 2003 x32 SP2, copy the software installs to a folder (eg. d:\stage) using a network share.

Then, install the database, there is a minimalist guide to installing Oracle here

ie. Install the db software first, then create a customized, minimalist, database.

Some parameters of note that I used:

Default to standard SID = ORCL

Disabled Enterprise Manager

 

Install only OLAP and Spatial packages

In Standard Database Components, remove APEX.

• 600 MB SGA, Automatic allocation
• ALF32UTF8 character set

Note – do not use 4096 bytes block size, the RCU install will fail. Stick to 8192 bytes.

Create the default listener and local naming entry using Net Configuration Assistant.Install the Microsoft Loopback adaptor on IP address 10.10.10.10

After installing the RCU, do a simple OBIEE 11g Install:

For testing purposes, I install also Firefox and Flash Player.

The results:

I have 4.45 Gb free on my D: drive after all the installations (installer files still present too).

Remove the installers, and this pumps up to circa 22 Gb.

With oracle db, OBIEE running, and Sample App Lite, we have the following memory profile, still 1.5 Gb free:

And we can see that we have about 1.5 Gb of Oracle software memory footprint. (Weblogic, database, BI system components)

How to Copy Sample App Usage Tracking Dashboards & RPD

 

The OBIEE Sample Application has a good set of standard Usage Tracking dashboards. It is fairly easy to graft the dashboards, analyses, and RPD objects into your target environment. This will get you up and running quickly with Usage Tracking. You first need to obtain a copy of the Sample App RPD and web catalog.

There are other system based dashboards that can be copied from the Sample App.

Steps:

• Obtain Sample App v207 RPD & Web Catalog
• Archive Sample App Usage Tracking catalog folder(s) to local file
• Un-archive to target catalog
• Open target RPD and Sample App RPD in two windows
• Copy Physical, Logical, and Presentation objects in order (select, ctrl-c, ctrl-v), at a time
• Modify Physical layer connections and schema names as required
• Copy variables as necessary
• Deploy RPD and catalog

Usage Tracking RPD: