Tag Archives: Obiee
OBIEE11g Golden Rules: RPD-Business Model Layer
First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.
The series contains:
- RPD-Physical Layer
- RPD-Business Model Layer
- RPD-Presentation Layer
- Report Building
- Dashboard Building
- Catalog Management
The “rules” is this article are somewhat in random order
This is always a “work in progress” and please feel free to make any suggestions!
Business Model Layer
- Prefix Logical Tables
All Logical Tables should be prefixed. There are several naming convention's in use:
- “Dim – “, “Fact – “ or “Fact Compound –“
- “D## name”, “F## name” or “FC## name”
- No “physical” column names
No “physical” column names should ever be seen on the Business Model layer. All naming conventions should be “business oriented”. For example use “$ Revenue” rather than “DOLLARS” .
- No Primary or Surrogate Physical Keys
Physical Primary Keys or Surrogate Keys should not be present on the Business Model layer (unless, for example, you have a Primary Key such as Order Id which will be displayed on reports)
- Logical Keys
Dimension Logical Tables must always have a Logical Key assigned. The Logical Key should be something “business oriented” such as “Employee Login” rather than “EMPLOYEE_PK”
- No Facts in dimensions
Dimension Logical Tables must only contain dimension attributes, they should never contain any measure columns (which have an Aggregate Rule)
- No Logical Keys on facts
Fact Logical Tables should not have a Logical Key assigned.
[UPDATE: ] As far as I know this is because the OBIEE optimizer uses the logical key to determine the "driving" table. No logical keys on fact tables should ensure the "correct" optimizer path. Please correct me if I’m misinformed.
- Aggregation Rules on Facts
Every Logical Column within a Fact Logical Table must be a measure column, and therefore have an Aggregation Rule assigned.
- Only Complex Joins
When defining Logical Joins between Logical Tables, only use “Complex Joins” (and use the default settings – you only ever specify a “Driving Table” when dealing with cross-database joins)
- No Snowflakes
The Business Model should only consist of logical star-schemas, there should not be any snow-flaking
- Hierarchies on Dimensions
Every Dimension Logical Table should have a corresponding Dimension Hierarchy (with “Total” as a Grand Total level, and “Detail” at the lowest level)
- Number of Elements
Each level of a Dimension Hierarchy should have its “Number of Elements” appropriately set (there is a utility in Tools that can do this automatically).
- Content Levels
Every Logical Table Source within every dimension and fact Logical Table should have its “Content Levels” appropriately set. The only time the “Content Level” is not set for a particular dimension is when there is no logical relationship existing
- Multiple facts tables
Do not merge all your measures into a single Fact Logical Table. For example, you should split “Forecast Sales” and “Actual Sales” measures into two Logical Tables e.g. “Fact – Sales” and “Fact – Forecast”
- Description fields
All available description field should have meaningful descriptions with non technical users.
Till Next Time
OBIEE11g Golden Rules: RPD-Physical Layer
First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.
The series contains:
- RPD-Physical Layer
- RPD-Business Model Layer
- RPD-Presentation Layer
- Catalog Structure
- Report Building
- Dashboard Building
The “rules” is this article are somewhat in random order
This is always a “work in progress” and please feel free to make any suggestions!
Physical Layer
- Clear the cache check box
Using cache should be a last resource, reconsider your data model and ETL processes first!
- Always use “Foreign Key” joins, not “Complex Joins” on the Physical Layer
If your join looks like D_DATE = TRUNC(S_DATETIME) try add a extra column S_DATE in your DWH. Any matching processing done by the BI-server costs time and you often loose the advantage of an index in your DWH.
- Prefix your tables
When modelling a star-schema data-model, create aliases for all your physical tables (prefixed with either “Dim_”, “Fact_” or “Fact_Agg_” )
- Physical Display Folder
Use Physical Display Folder to organise your stars
- Call Interface
When possible, configure your connection pools to use a “native driver” to connect to your physical databases. For example, use OCI for connecting to an Oracle database rather than ODBC. 
- Parameterize your data source name
This way you only have to change it in one place when moving from development to production.
- 3NF in DWH
Try to avoid doing 3NF to Star Schema Modelling in the Physical Layer. Flatten the table if possible during the ETL or in a view on the database.
- Connecting User
The User you use to connect to your data should by default not by the “owner” of the table, but should have only select rights trough a role.
Till Next Time
OBIEE obips_config_base.xsd
Good Sunday afternoon reading, locate you obips_config_base.xsd and open it read only in a text editor (or download the PDF here).
It' contains loads of info on the instanceconfig.xml tags.
Have a special look at the <!-- Top Secret, Do NOT Expose On Penalty Of Dismissal (throw away) –> stuff
(Kudos to the genius who left them in the normal download 
)
Till Next Time
store non-OBIEE objects in the catalog
Using the regular Upload functionality you can easily upload a (for instance) PDF file into the Shared Folders of the webcatalog.
The awkward thing is, that if you put a link on your dashboard and use the Browse button to navigate to the particular folder, you don't see the PDF file since it is not an OBIEE object.
A workaround for this is to create a (dummy) analysis and store it in the same folder as the PDF. Then, as described above, put a link on your dashboard and navigate to the dummy analysis. As a final step, (since you now have the path to the folder where the PDF resides) replace the name of the OBIEE analysis with the PDF's name.
There you go ! Now you can have a link on your dashboard to non-OBIEE objects and use your security model on it !
Kind regards,
René
OBIEE Catalog for large organisations part 2
In OBIEE Catalog for large organisations part 1 I showed you how to set up a catalog for large organisation following the OBIEE documentation. But still you might run into trouble.
Take a large energy firm which supplies there customers with some reporting on there energy consumption. They serve about 90.000 homes. Each home is an account, each account is based on the Dutch postal code system. >>zip_code+house_number<< (fi: 5721XW002).
The code is roughly divided into 4 part; The first 2 numbers are the post district area, the next 2 are the city area, the 2 letters are the street(side), the 3 number indicated the house number (optionally there might be a suffix)
If we only ‘hash’ the catalog by the first 2 characters you still run into trouble since there are roughly 20.000 houses in the 57xx postal code area.
A solution might be to change the 2 into a 5. This will ‘hash’ the first 5 characters. (yes, theoretically you might still run into trouble, but luckily not every possible postal code is issued and not every street has a 999 houses 
)
Remember this setting has to be made BEFORE the first user log on to the catalog!
Till Next Time
