Sql Developer Troubleshoot

Today morning I experienced strange error while opening the Sql Developer

Error: could not install some modules: oracle.icons - org.netbeans.InvalidException

I forgot to take the Screenshot.

Solution: I deleted sql developer folder from /AppData/Roaming/SQL Developer/folder then it is working fine

OBIEE BEST PRACTICES

REPOSITORY:

1. create aliases for  all tables and prefix their names with a text that reflects the type of table

Ex: Dim_Product, Fact_Sales

2. Create physical diagram using aliases, not the Imported ones
3. Avoid Circular Joins

Circular join have big impact on the data integrity. All ways avoid circular joins by creating aliases

4. Connection Pool Configuration
1. Use native database drivers instead of ODBC
2. Set the max no of connection high enough. Recommendation is 10-20% concurrent users multiplied by no of queries per dashboard
3. Use separate connection pool for initialization blocks

5. Enable query limits to avoid the slow down the BI Server and the database with bad report that extracts millions of records.
6. In BMM layer create Logical star schemas only. No snow flake
7. Use separate logical dimension table for each dimension. Don’t merge tables. This is same for the fact table also
8. Assigning business column as dimension primary key
9. Rename the logical column names to business names
10. Remove un used columns from the BMM layer
11. Every fact logical should have aggregation rule set
12. The primary key of each level must be Unique
13. The primary key of the lowest level of the hierarchy must always be the primary key of the logical table
14. Always create a dimension hierarchy for all dimension tables, even if you have only one level in the dimension
15. Always specify the number of elements per level. BI Server is uses this for identifying the aggregate tables. It doesn’t be accurate.
16. Always specify the content level in all logical table sources, both in facts and dimensions. It will allow BI Server to select the most optimized LTS in queries.
17. Set up an implicit fact column for each presentation folder. It prevents users from getting wrong results if they create a report without fact column.
18. Maintain a common time dimension for enterprise level.

Implicit Fact Column in OBIEE

In case we have multiple Fact tables, then it is a best practice to set an Implicit Fact Column. BI Server to make the best choice between two possible query paths.You need to select a fact attribute (measure) as an implicit fact column.

Whenever User request a report with only Dimensions and there is no Fact columns. Then BI server identified there is multiple query paths. The BI Sever will fetch the reports from the nearest fact table, these result may be different form expected result.

So, In  order to avoid this kind of error, we can create a dummy fact column called Implicit Fact Column in physical layer.You can find this functionality in the general property of a subject area in the repository. Join all dimensions to this fact column in BMM Layer.In presentation layer, double click the presentation catalog or go to properties of presentation catalog.In the general tab, find the Implicit Fact Column section. So, whenever a report on Dimensions is requested it joins through dummy fact table and fetches data.

Steps to set Implicit Column

1.  Select properties of presentation catalog in presentation layer.

2.  In implicit fact column section click on set and select any measure column from fact table.

3.  Click OK and save

Setting the Dummy Columns as Implicit Face Column

1.  Create a Physical Column in Fact table in Physical Layer.

2.  Name it as Implicit_Column.

3.  Drag this column in Fact table from BMM layer.

4.  Double click on logical table source of fact table.

5.  In content tab, assign any numeric value to Implicit_Column.

Once this is done we can set this column as Implicit Fact Column in Presentation catlog as mentioned above.