Resetting BISytemUser password in OBIEE 11g

 BISystemUser by default is the user that is used as an inter-bi-component communication user, this could also be used when Impersonation is used. This is refferenced by an Authenticator ( usually Defaault Authenticator unless changed to different providors like Active Directory or other directories ).

 

The credentials for this user are managed via cwallet.sso which is the default credential store under oracle.bi.system - system.user. The BISystemUser does not need any Group membership , however it would need Weblogic Global Role called 'Admin' [ P.S - This is not an 'Application Role' by any means ]. By default BISystemUser is a member of an LDAP Group called 'Administrators' which is assigned to the Weblogic Global Admin Role.

 

OracleSystemUser is used by Oracle Web Services Manager (OWSM) which is integrated with WLS EM Console to provide the management and securing of web services through administration of policies.By default OracleSystemUser is a member of OracleSystemGroup in Weblogic LDAP. This is also refferenced via Default Authenticator this could be changed by following the FWM documentation.

More information could be found : http://docs.oracle.com/cd/E21764_01/bi.1111/e10543/privileges.htm

To reset BISystemUser:
 

1. Stop the system components in Enterprise Manager.

Click on Business Intelligence >Core application> Availability

 

 

2. Log into Weblogic Console and change the BISystemUser password.

Click on security realms > myreams > user and group

 

BISystemUser > Passwords

 

 

 

3. Change password in EM:

Weblogic Domain > right click on bifoundation_domain > Security > Credentials > oracle.bi.system > system.user > Edit > change the password

 

 

4. Start BI System components from Enterprise Manager.

Click on Business Intelligence >Core application> Availability

5. Wait for 10 mins

6. Try the new password in the OBIEE URL.

 

 If you configure Oracle BI to use an Active Directory , OID etc authentication providers, then you must select a user from MSAD to use for this purpose and give that user the required permissions. You can create a new user in MSAD for this purpose or use a pre-existing user. You give the chosen user the permission they need by making them a member of the pre-existing BISystem Application Role.

 

Once you have removed the default BISystemUser from the Default Authenticator because you wanted to configure external LDAP store. You need to create another user for BISystemUser and Whilst configuring this user keep in mind of the following considerations that could cause authentication failures:

 

1. The BISystemUser which is created in the external LDAP (Active Directory or any third party user directory),  the user configuration in MSAD is should not be configured as "Reset Password on First Login" since there is not reset login screen when OBIEE is trying to use this user for its interal communication purposes.

 

2. OBIEE cannot handle special non-alphanumeric characters in the password.  See BUG 11880111 - password restrictions for bisystemuser, for more information.

 

3. Make sure the external BISystemUser in an external LDAP password and the account should be set to NEVER expire else you cannot login to OBIEE.

 

4. Make sure you have assinged correct roles and your BISYSTEM and system.user password are always synchronised.

 

5. If you have changed the password of this account but not updated the credential store with the new credentials (or have not restarted the system afterwards) authentication will fail.
 

Post your Questions/Comments.

Accidentally deleted BISystem Role

There was a time once where someone deleted BISystem Role from EM accidentally and all the hell broke loose and NO one was able to access OBIEE thru any of the authentication providers ( Default, AD , SSO) and it took a while to figure out until the system_jazn_data.xml's from Prod and Test were compared side by side.

When you check the log files you would see errors like this:
nqsever.log
[2013-02-14T13:22:45.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: cb5a346296ed2a97:-7393df37:13cda108fa1:-8000-0000000000001ff6] [tid: 45bda940] [nQSError: 43126] Authentication failed: invalid user/password.
[2013-02-14T13:28:32.000+00:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: 004pRGflGvX3NA3_zlG7yW0002vb000000] [tid: 45ddc940] User OBIS spent 33.000000 milliseconds for http response when authenticateWithLanguage
[2013-02-14T13:28:32.000+00:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: 004pRGflGvX3NA3_zlG7yW0002vb000000] [tid: 45ddc940] User OBIS spent 0.000000 milliseconds for xerces parsing when authenticateWithLanguage
[2013-02-14T13:28:32.000+00:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: 004pRGflGvX3NA3_zlG7yW0002vb000000] [tid: 45ddc940] The response for user OBIS during authenticateWithLanguage is: <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"><env:Header/><env:Body><env:Fault><env:Code xmlns:env="http://www.w3.org/2003/05/soap-envelope"><env:Value>env:Receiver</env:Value></env:Code><env:Reason><env:Text xml:lang="en-US">oracle.bi.security.service.SecurityServiceException: SecurityService::checkSystemUserPermissionsSystem user has not been granted required permission oracle.bi.server.impersonateUser</env:Text></env:Reason></env:Fault></env:Body></env:Envelope>

sawlog0
BI Security Service: 'Error Message From BI Security Service: oracle.bi.security.service.SecurityServiceException: SecurityService::checkSystemUserPermissionsSystem user has not been granted required permission oracle.bi.server.impersonateUser'

[2013-02-14T13:00:04.000-06:00] [OBIPS] [ERROR:31] [] [saw.security.odbcuserpopulationimpl.searchidentities] [ecid: ] [tid: ] Error retrieving user/group data from Oracle BI Server's User Population API.
Unable to create a system user connection to BI Server while running user population queries
Odbc driver returned an error (SQLDriverConnectW).
State: HY000.  Code: 10058.
So I went ahead to creating this role in EM manually by comparing the Roles and Policies from Production.

Steps you need to follow in the case when BISystem Role accidently gets deleted:

1.Recreate the BISytem Role map this to BISystem User.

•  Login to EM
• Business Intelligence > coreapplicaiton > Right click > Application Roles
• Select the Application Stripe as obi
• Click Create
• Role Name: BISystem
• Display Name: BI System Role
• Members: BISystem User

2.Recreate BISystem Policies add BISystem Role as member to this.

• Business Intelligence > coreapplication>Right Click > Application Policies
• Select the Application Stripe as obi
• Click Create
• Add the BISystem Role as the Grantee
• In the Permissions section, add the following:

oracle.bi.scheduler.manageJobs

Grants permission to use Job Manager to manage scheduled Delivers jobs.

oracle.bi.server.queryUserPopulation

Internal use only.

oracle.bi.server.impersonateUsers

Used by internal components that need to act on behalf of end users.

oracle.bi.server.manageRepositories

Grants permission to open, view, and edit repository files using the Administration Tool or the Oracle BI Metadata Web Service.

EPM_Essbase_Administrator

Grants permissions for EPM Essbase Administrator.

3.When creating Application policies for BISystem Roles if you dont find from the search results, then leave the Resource Name empty and click continue and manually add the Permission Class , Resource Name and Permissions Actions.


Example when you trying to add the oracle.bi.server.impersonateUser but could not find in search results leave the Resource name empty and click Continue

And enter these Permission Class, Resource Name and Permission Actions manually

 There once you have added all the Policies and restart your services, OBIEE should be back up and you should be able to log back in again.

 Reference:

http://docs.oracle.com/cd/E28271_01/bi.1111/e10543/install.htm

OBIEE Reports and Dashboards Best Practices

 

 Dashboard - Performance

Practice Reasoning Avoid designing dashboards that return too much data Dashboards should show summary data with ability to drill into or link to detailed information with more reports. Avoid designing requests that use overly complex queries Derived metrics should be in the data source layer first, the metadata layer second, and report layer last. Use guided navigation to link to smaller dashboards Guided navigation can be set at the dashboard or at the report level. Ensure global filters have a default value Setting value to nothing or all will return all the data and impact performance. Setting a default values creates a smaller data set enhancing readability. All embedded reports on a dashboard issue their queries at dashboard load time Take this into consideration when choosing to embed large reports or link to them. Use multiple dashboard pages to separate large reports from each other when performance becomes an issue.
Dashboard - Readability Practice Reasoning Use fewer columns More columns in a request are not necessarily better. OBI is not a spreadsheet and should be viewed as such. Simplicity can tell the reader what is going on in one look. Use charts to simplify views of data, not to confuse them Charts can allow for easier understanding of data. Common mistake is to add charts to fill up white space or show different metrics than the associated table. Use conditional formatting to focus attention on data outside given parameters Do not just produce reports. Drive users towards actionable data based upon conditions. Eliminate white space and scrolling wherever possible Set report widths to 100%. If you have to scroll - your report is too large. Use alternating row formatting, 'Green Bar', on grids to enhance readability.
Dashboard - Security Practice Reasoning Use security groups to filter out irrelevant or unwanted dashboards Focus on providing data relevant to a user's role. Ask why a user should see a dashboard instead of why not. Organize dashboards by roles to help users locate the data they are interested in Executive dashboard. Management dashboard. Individual Dashboard. Use groups for access instead of individuals Assigning ownership of an object should be at the group level. Create parent groups with sub groups as members to control access. Individuals assigned to objects should be the exception.
Metadata - Business Model and Mapping (BMM) Layer Practice Reasoning Avoid fact-to-fact joins Poor performing. Use conforming dimensions to solve fact-to-fact requests. Prefer ETL sourced derived metrics to BMM Layer derived metrics Calculate once vs. on every query. Common mistake is to do all the work in the BMM Layer. Databases can perform functions more efficiently than Oracle Business Intelligence's application server. Rename columns Renaming columns at the BMM Layer saves time and is easier to manage. Avoids the creations of alias column names in the Presentation Layer. Alias column names can cause confusion in troubleshooting situations. Use complex joins whenever possible Provides Oracle Business Intelligence the ability to the choose the logical table sources that will produce the most efficient SQL. Metadata - Performance Practice Reasoning Disable logging in production Results in performance degradation. When logging is needed for troubleshooting, pre-production environments can be used. Metadata - Physical Layer Practice Reasoning Establish foreign key relationships before moving object to BMM layer BMM layer inherits foreign key relationships when object are dragged from Physical Layer to BMM Layer. Establish uniform naming conventions for tables e.g Dim_tablename, Fact_tablename, View_tablename. Allows for a cleaner, more organized repository. Allows developers and support individuals to easily determine object intent. Nullable flag on physical columns must be set correctly Controls how SQL is generated. Properly set flag enhances performance. Use table aliases at all times Associates one physical object to one business model object. Used to eliminate circular joins. Allows physical tables to be easily managed in shared environments. Metadata - Presentation Layer Practice Reasoning Organize presentation tables by entities that make sense to business users Time, product, sales, sales facts. Use subgroups to further organize the data: Time - Calendar Date, Time - Fiscal Date. Follow the "Rule of 7" Tables should be organized to show seven or less columns per level. Tables with too many columns become unreadable. Sort Columns Sort columns such that it makes sense to the end user. Metrics should be sorted by most commonly used metrics first. Attributes can be sorted by commons elements, hierarchy, and finally alphabetically. Answers Standard A top level shared folder will exist for each project. The project shared folder will be named using the project specific moniker. A 'Dashboard Reports' sub-folder will be created under the project's top level folder. Sub-folders under 'Dashboard Reports' will be created mimicking dashboard names and organization. Report names will mention the dashboard they reside upon. Prompts and filters will be named according to their object types. Metadata - Business Model and Mapping (BMM) Layer Standard A top level business model folder will exist for each project. The project business model folder will be named using the project specific moniker. Metadata - Physical Layer Standard All project database names will be prefixed with their project specific moniker. All physical tables will have at least one alias table to do work from. All alias table names will be prefixed with the table type: Dim - dimension table Fact - fact table Fact_Agg - aggregate tables Metadata - Presentation Layer Standard All project presentation catalog folder names will be prefixed with their project specific moniker. Metadata - Other Standard Common variable names will be prefixed with SYS. Common init block names will be prefixed with SYS. Project variable names will be prefixed with the project specific moniker. Project init block names will be prefixed with the project specific moniker.