We had to do a POC on some MS-SQL data. Instead of bringing an extra Oracle DB we used an existing MS-SQL instance.
1. Create a repository database:
We called ours “Repositories”
2. Enable case-sensitive collation
ALTER DATABASE Repositories COLLATE Latin1_General_CS_AS
3. Enable Row versioning
ALTER DATABASE Repositories SET READ_COMMITTED_SNAPSHOT ON
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:
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!
When you have multiple Subject Areas, list the common dimensions in the same order across all the Subject Areas
- Time dimension first:
Since the time/calendar dimension is often the main aggregator make it the first in your presentation layer list.
- No prefixes:
Presentation Table names within each Subject Area must not begin with “Dim – “ or “Fact –“ or “Fact Compound –“. So remove these prefixes if they are present after creating the Subject Area by dragging Logical Tables directly from the Business Model.
- Identify your facts:
The Presentation Table containing your facts should be listed right at the bottom, and the Presentation Table name should contain words like “Measures” or “Facts”
- Ensure logical relationship:
There should be absolutely no possibility whatsoever of a user selecting objects from a Subject Area that have no logical relationship. So, if there are any objects within the same Subject Area that cannot co-exist in the same report, then your Subject Area design is incorrect!
- Split over multiple subject area:
Within OBIEE11g report can be build using multiple presentation layers based on the same business layer:
- Dimension Column Order:
Try to have the column in the same order as your hierarchy: Year > Quarter > Month > Week > Date or Business Line > Brand > Product
- Special characters:
Special HTML characters {< > / } should be avoided in the object names. Not ever browser can render them correctly.
- Metadata dictionary
Have a well maintained metadata dictionary in place:
Remember in OBIEE11G you have to redeploy the metadata dictionary after each RPD deployment
Till Next Time
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:
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!
- Prefix Logical Tables
All Logical Tables should be prefixed. There are several naming convention's in use:
- 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
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:
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!
- 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
I personally think it’s never a good idea to allow online changes to the RPD by default. Especially not on production environments. In OBIEE11g you can set the online editing of the RPD in
Enterprise Manager>Business Intelligence > Coreapplication > Capacity Management > Performance:
Click on lock and Edit Configuration:
Press close:
(un)Check the box:
Click Apply:
Press activate Change:
Wait a bit:
Press Close:
Go to the restart page by pressing:
Press Restart:
Till Next Time
If you look in the configuration guide you will find that the CASE_SENSITIVE_CHARACTER_COMPARISON parameter in the NQConfig file controls the case sensitive search within OBIEE. Be aware that you might be fooled by your database settings. If you are on an Oracle database 10G+ you can use a connection script in the repository to allow case insensitive searches. Go to the connection pool, click on the connection script tab.
Press New:
Enter:
alter session set NLS_SORT=BINARY_CI
alter session set NLS_COMP=LINGUISTIC
Save the repository, reboot the BI-Server
Till Next Time
A colleague discovered this: If you are using brackets in the BMM and the column is part of a dimension and the last character is a closing bracket ')'.
Then when building a report the column appears 'strange' in answers, it includes the folder prefix:
It doesn't seem to influence the results, it appears to be just on of those things.....
Till next time
Found some good documentation from Oracle on the new Sample Sales repository in OBIEE:
http://www.oracle.com/technology/products/bi/files/oracle_bi_sample_app_content_guide_ext_part1.zip
http://www.oracle.com/technology/products/bi/files/oracle_bi_sample_app_content_guide_ext_part2.zip
Be aware that it is referencing the extended samples sales (not in the normal OBIEE download) which can be found here:
http://www.oracle.com/technology/products/bi/files/oracle_bi_sample_analysis_setup_files.zip
Till Next Time
DECLARE USER "NewUser" AS "NewUser" UPGRADE ID 1 FULL NAME {New User} PASSWORDThe password can only be entered encrypted, this string is for "welcome1".
'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F'
PERIODICITY 90 HAS ROLES ( "Administrators", "XMLP_ADMIN" ) DESCRIPTION
{password = welcome1} PRIVILEGES ( READ);
Next you have to "merge" the txt file into the reopsitory:
E:\OracleBI\server\Bin>nQUDMLExec.exe -U Administrator -P Administrator -I E:\temp\NewUser.txt -B E:\temp\paint.rpd -O e:\temp\newrep.rpd
