1. Always run Global Consistency Check before releasing a repository:
- Whenever we make changes to a repository, always be sure to run Global Consistency Check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI server from loading the repository. Use the Consistency chec k manager to identify and debug check messages.
Note: Whenever you do Consistency check , Right Click the Changed Business Model Object and go for Check consistency rather than using the Global Consistency Check. This will minimize the time needed for Consistency Check.
2. Minimize the use of Snow-flakes. Always go for Star Schema’s:
- Every logical fact table must join to atleast one logical dimension table.
- Note that when the source is a fully de-normalized 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.
3. Create dimension hierarchies for every Dimension in the Business Model:
- Even if a meaningful hierarchy definition cannot be thought of, just create one with the Grand Total Level and Detail Level. For Dimension Hierarchies the ‘Number of Elements at this level’ should increase from 1 at grand Total to the corresponding distinct values at each level. This can be approximate values; need not be the exact ones. Define keys at each level of the Hierarchy.
4. Don’t keep unwanted physical columns in the Logical Layer:
5. Give Meaning ful Names to the Logical columns. Avoid assigning a logical column the same name as a logical table or Business Model Object:
6. Logical Fact & Dimension table columns:
- Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users.
- Bring only required columns into BMM layer for reporting.
- Do not assign logical primary key for logical fact columns.
- Create “dummy” measures to group facts.
7. Use Multi User Development Environment:
- Use the Multi-User Development facility if there are multiple developers. Multiple developers to connect “online” to the same repository file and Making changes is not recommended.
- Multi User Development allows user to define a series of projects with in the repository file, where each project is a subset of the entire repository. If developers want to make changes, they can check out a project to a local machine make and test the changes, and then check the modifications back into the master repository file.
8. Level-based Metrics:
- When creating level-based measures, make sure that all appropriate fact sources map to the appropriate level in the hierarchy using aggregation content. You set up aggregation content in the levels tab of the Logical Column dialog for the measure.
- Note that this is different from the content tab of the Logical dialog for the measure. Note that this is different from the content tab of the logical table source dialog, which is used to specify the grain of the source tables to which it maps. You only need to set up aggregation content in the levels tab of the logical column dialog for level-based measures. For measures that are not level based, leave the logical level field bank.
9. Separate business model:
- Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality.
10. Arrange the logical columns alphabetically if customer does not require any. This will save time when you revisit:
11. Fix the warnings if any, don’t ignore it:
12. Performance Tuning:
- Minimize the use of conditional checks and ‘CASE WHEN’ usage in the formula of Logical columns. This will affect performance. Instead make proper use of the where clause content filter of the LTS if the condition applies to all the columns/ measures in the logical table.
- Make proper distinction between count and count distinct. If you are counting on a unique value column don’t use count distinct. This will affect performance.
- Avoid dimensions in Fact tables and avoid measures in dimension tables.
