Friday, January 25, 2013

OBIEE 11g New features (compared to 10g)

Apart from major architectural differences, OBIEE 11g has several new features that were not available in 10g.
Some of the key features include:
  • Supports creating hierarchy objects in Subject Area
  • Export to xml or export to tab delimited/ and power point
  • 11g lets variable have multiple values
  • 11g RPD cannot be without a password
  • 11g supports ragged hierarchies
  • Column selector in 11g can be defined on multiple columns, compared to just one column in 10g.
Key Terminology Changes

In 11g, the following terminology changes have been made:
  • iBots have been renamed to agents (also known as intelligent agents)
  • Requests (or reports) have been renamed to analyses, and the term reports now refers to BI Publisher reports
  • Presentation columns have been renamed to attribute columns, and a new column type, hierarchical column, is introduced
  • Charts have been renamed to graphs
  • Security terminology and architecture has changed.
The user interface has undergone major improvements in several areas, including a new Home page and redesigned editors and panes.

  • You can use a "selection step" to limit data for columns by specifying the criteria for a set of members
  • Drill in data (expanding a member, without impacting rest of the report)
  • Sorting in 11g is now enabled by default. Sorts can be applied to members, measures, and rows in views such as tables, pivot tables, graphs, and maps
  • In a pivot table, you can easily drag and drop columns to various edges, to rearrange the display to suit your needs
  • Graphs and gauges come with new feature of section sliders.
  • Map view is a new view type that presents data in spatial form
  • We can also create master-detail relationships among certain view types
  • We can create calculated items to perform mathematical operations on members. In 10g, we could create a calculated item for an attribute column. In 11g, we can create a calculated item for the members of an attribute column or of a hierarchical column
  • 11g allows us to work with multiple subject areas in same report. In addition to the primary subject area that you select when you create a new analysis, filter, or dashboard prompt, you can include additional subject areas that are related to the primary subject area with which to work
 Interaction with Other Oracle Products

  • Oracle BI Publisher is a fully integrated member of the Oracle BI EE 11g by default. All of the BI Publisher functionality appears seamlessly within the Oracle BI EE application, and all of the reports and related objects are created within Oracle BI EE and saved to the Oracle BI Presentation Catalog
  • Oracle Hyperion Smart View is available for download from the Oracle BI EE Home page
  • Interface with MS Office is enhanced, including an additional functionality to download to CSV. In 10g, there was an option to download data, but it was only 'Tab delimited', not Comma seperated.
SQL Functions for Time Series Calculations and Level-Based Measures

The following new and revised SQL functions enable you to create time series calculations and level-based measures:
  • PERIODROLLING is a new function that computes the sum of a measure over the period starting x units of time and ending y units of time from the current time.
  • AGGREGATE AT is a new function that aggregates columns based on the level or levels specified.
  • AGO is a time series aggregation function that calculates the aggregated value from the current time back to a specified time period. In this release, the time_level argument is optional, and there are additional syntax changes. This function was called PERIODAGO in the previous release.
  • TODATE is a time series aggregation function that aggregates a measure attribute from the beginning of a specified time period to the currently displayed time. The syntax for this function has changed in this release. This function was called PERIODTODATE in the previous release.
Enhanced database function usage capability

OBIEE 11g allows calling EVALUATE, EVALUATE_AGGR, and EVALUATE_PREDICATE function for advanced calculations.

Enhancements to the Oracle BI Presentation Catalog

The Oracle BI Presentation Catalog is a powerful, underlying storage directory-based system that contains the objects (dashboards, scorecards, prompts, and KPIs for example) that you create. If you use BI Publisher, then the catalog also contains BI Publisher data and objects (data models and schedules, for example). Using the catalog, you can:
  • Perform basic tasks such as creating, deleting, and copying objects and folders.
  • Perform object-specific tasks (for example, modifying settings using the associated editor, or creating and assigning an agent).
  • Find objects by performing a search.
In addition to these tasks, administrators can:
  • Access the root folder that contains system-wide object and user data.
  • Specify permissions that determine the folders and objects that users can view.
  • Create folders.
  • Work with properties such as changing the ownership of a folder or viewing the creation date of an object.
  • Archive the entire catalog, folders, or objects to unzip them in another directory location.
Actions

You can create actions that navigate to related content or that invoke operations, functions, or processes in external systems. You can include actions in analyses, dashboard pages, agents, scorecard objectives, scorecard initiatives, and KPIs

OBIEE 11g BI Server not starting due to expired password

I installed sampleapp v207, and after few days, BIServer and Presentation Services do not start up. WLS Admin Server throws an error that password in expired. This really referrs to OID password, which needs to be changed.



This is a known bug for sampleapp v207. Issue is caused due to OID password expiry set to a default period of 120 days. In order to reset the default password and also set password expiry to never expire, do the follow.

Open a terminal inside SampleApp v207 image and execute the following commands. Then, restart opmn and all services should come up.

# Following commands resets password expiry parameter to 0 (never expire).
# It also sets passwords of orcladmin, oiduser1 & oiduser2 to default Admin123).

export ORACLE_HOME=/home/oracle/oid/Oracle_IDM1
export ORACLE_INSTANCE=/home/oracle/oid/bioid2
export LDAP_PRMS="dn: cn=default,cn=pwdPolicies,cn=Common,cn=Products,cn=OracleContext,dc=us,dc=oracle,dc=com\nchangetype: modify\nreplace: pwdmaxage\npwdmaxage: 0"
export LDAP_PWD="cn=Users,dc=us,dc=oracle,dc=com\nchangetype: modify\nreplace:userpassword\nuserpassword:Admin123"
cd /home/oracle/oid/Oracle_IDM1/
echo "Admin123" | ./ldap/bin/oidpasswd connect=OIDDB unlock_su_acct=true
echo -e $LDAP_PRMS | ./bin/ldapmodify -D cn=orcladmin -w Admin123 -h localhost -p 3060
echo -e "dn: cn=orcladmin,"$LDAP_PWD | ./bin/ldapmodify -D cn=orcladmin -w Admin123 -h localhost -p 3060
echo -e "dn: cn=oiduser1,"$LDAP_PWD | ./bin/ldapmodify -D cn=orcladmin -w Admin123 -h localhost -p 3060
echo -e "dn: cn=oiduser2,"$LDAP_PWD | ./bin/ldapmodify -D cn=orcladmin -w Admin123 -h localhost -p 3060
cd –

This solution does work..!!

OBIEE 11g services do not start after making RPD changes

We made some changes to BI RPD and published it to server using enterprise manager. However after activating the changes and trying to start services, EM kept throwing error and services did not start. Following were some of the error we got after services failed to start:

!!! Oracle FOI server destroyed. !!!
!!! Oracle MapTileServer destroyed. !!!
!!! Oracle Web Map Server destroyed. !!!
Operation Failed: start; OracleInstance: instance1; Component: coreapplication_obis1; msg: 0 of 1 processes started.

Processing Event: SEVERE: Element Type: PROCESS, Element Id: instance1:coreapplication_obis1, Operation Result: FAILED_TO_START, Detail Message: Operation Failed: start; OracleInstance: instance1; Component: coreapplication_obis1; msg: 0 of 1 processes started.
Odbc driver returned an error (SQLDriverConnectW)
Unable to create a system user connection to BI Server during start up. Trying again.

As evident from error list, the issue was somewhere in connection pool, and sure enough, we had incorrect connection type (also reffered to Call Type). I changed it from ODBC 3.5 to OCI 10g/11g and saved the RPD. This made the services to start properly with no issues. 

This can be confusing, as settings on client machine for connecting to database can be different than settings on the server. However when publishing the RPD, settings must be those applicable for server.


All well, except one problem, the report still gave TNS error (Unable to connect to database). For this, we had to change the user ID used in RPD, that had access to tables, and also check 'Require Fully Qualified Table Names' within the connection pool settings. This made the error disappear

Thursday, January 24, 2013

Starting OBIEE 11g Services

OBIEE 11g comes with fancy enterprise manager, and weblogic console, however if you have to restart the server for any reason, the services may not start back up on their own (unlike 10g). The checklist below will be helpful if 11g fails to start

1. Start the node manager:

/MiddlewareHome/wlserver_10.3/server/bin/nohup ./startNodeManager.sh &

2. Set WLS (weblogic server) environment:

cd /MiddlewareHome/user_projects/domains/bifoundation_domain/bin

Setting the environment:

./setOBIDomainEnv.sh
./setDomainEnv.sh

3. Start the WLS:

nohup ./startWebLogic.sh -Dweblogic.managment.username=weblogic -Dweblogic.management.password=webdev123 &

Weblogic logs are located at:

/MiddlewareHome/user_projects/domains/bifoundation_domain/servers/AdminServer/logs
The server log file
/MiddlewareHome/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/AdminServer.log

change auto boot file:
/MiddlewareHome/user_projects/domains/bifoundation_domain/servers/AdminServer/security/boot.properties (this is the file as in nohup.out log)
/MiddlewareHome/user_projects/domains/bifoundation_domain/servers/bi_server1/security/boot.properties

4. Login to WLS console:

http://localhost:7001/em

and navigate to :

Farm>WebLogic Domains>bi_cluster.

Click control > Start

4b. Use following command to start BI application from command line :
cd /MiddlewareHome/instances/instance1/bin

./opmnctl status

./opmnctl startall

error log
/MiddlewareHome/instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1/console~coreapplication_obips1~1.log

This can be also started from EM

By now, services should be up and running. If not, check log files for error messages and you will need to do troubleshooting.


LOG FILES:

-The server log file. All server side log events will be written to this file:
/MiddlewareHome/user_projects/domains/bifoundation_domain/servers/AdminServer/logs/AdminServer.log

-Presentation server log:
get /MiddlewareHome/instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1/sawlog0.log


CONFIG FILES:
-Instance config:
/MiddlewareHome/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1

-NQSConfig.ini file:
/MiddlewareHome/instances/instance1/config/OracleBIServerComponent/coreapplication_obis1/

Saturday, January 19, 2013

Top 10 Costly Mistakes for Business Intelligence / OBIEE Implementation Projects

Top 10 Costly Mistakes for Business Intelligence / OBIEE Implementation Projects

Okay, you have done your research, you have meticulously compared market offerings, and lined up a team of resources for implementation for Oracle BI 11g product. Senior management is supporting you, and they are expecting a great implementation of the product from your team. With a company new to the tool, how to make sure of a successful BI implementation? Having over a decade of experience in the industry, I assure you, success doesn't come easy. Selecting a technology stack is less that 10% of the battle fought to achieve your overall BI goals and expectations. Below are listed some of the most common and highly avoidable mistakes by various organizations from tiny to gigantic in sizes:

1. Not setting the expectations right with users and senior management - Having millions of dollars of funding approved from management does not mean that ton of reports will be available for analysis. It is best to release small batch of reports first, and then build upon it based on the users feedback. IT department is often under pressure to arrive being a hero group, unfortunately, there is no easy button in BI.

2. Going for BI Apps and assuming all out of the box reports will be ready and used by business - The Out-of-The box BI apps are extensive, and look good. But I am yet to find a company, that has used them without major customizations. Most Out of the box report will require some level of customization in order to be able to be used. BI apps make sense to purchase to get ETL code, which will help in building warehouse tables and having a baseline repository to begin with. Even that would require some customizations, matching the same done to source systems. Verdict - consider BI Apps to get ETL code and a baseline warehouse built. But plan to perform customizations.

3. Not spending enough time on designing repository to enable intuitive ad-hoc reporting - One of the key selling features of product like OBIEE 11g is the ad-hoc reporting capability, which allows end users to make simple changes to reports themselves, thus reducing their dependence on IT, and cutting down development cycle. For this to happen, the data model should support it and repository should be in line with ad-hoc reporting. Without proper foresight, this may not happen, and is a costly mistake to be avoided from the beginning. Some companies plan for this, however during the development, with several changes coming back and forth, and multiple fix and break cycles, its easy to loose track. Change in BI projects will be there, it cannot be eliminated, it has to be managed keeping ad-hoc in mind. One way to reduce change cycles is to involve users from beginning, and this brings us to our next costly mistake as below.

4. Lack of user involvement during development project - User know what they want, in their heads, unfortunately, they cant communicate in BI language. They will communicate in the language of MS Excel or whatever current reporting tool they have. Waiting for six months or closed to delivery date, to show first cut to the business would be a big mistake, as they are new to BI. Their light bulbs will go on only after looking at the reports and dashboard. Best would be to do a proof of concept (POC), or to involve them from the beginning, by having working sessions, or letting them see work in progress in development. I kid you not, this a common mistake.

5. Not testing subject areas for Ad-hoc reporting - Developers and rest of the team are under pressure often to deliver the laid out requirements, which usually is a list of reports. Seldom, the subject area itself is part of that list. This is a big mistake, and letting SA go to prod without testing will add to user frustration during ad-hoc reporting (this is supposed to be a key value offering of BI tool set).

6. Not having BI standards for requirement gathering, development, testing and migrations - Including inconsistent naming conventions across subject areas. Too much focus is instead spent on technology stack setup, users requirement at hand, and deliverable on which success will be evaluated. Although standards evolve over time, its best to set a baseline from the beginning. Your users will be happier and less frustrated 5 years down the road. It is a good idea to have a resource who is knowledgeable and has access to BI best practices for development and implementation.

7. Hiring a great data modeller experienced with relational source systems, and letting him lead dimensional model design - will you visit a brain surgeon for a root canal treatment? No I suppose, then why would you go to a relational data modeler for dimensional modeling project? Yet, it is a common mistake made. It can lead to wrong designs, which brings to our next mistake.

8. Using relational models instead of dimensional model - Technically - possible, practically - useless. Warehouse models should be dimensional - star and snowflake - period. Reasons? Relational models are good with write and read systems, while dimensional models are best for read performance. Relational models offer poor performance, are difficult to maintain, complex to troubleshoot in case of issue, very hard to enhance per customer request, and most of all, it does not support ad-hoc reporting.
9. Not having application level backups or other faster ways for disaster recovery. Most companies have teams that will backup all servers and have a plan for disaster recovery. However, you would be surprised, how much time a read disaster will take for recovery. If you loose one file to a bad sector in hard disk, will business accept waiting 48 hours (or more), before server is back up? Application level back ups would be a minimum, if fail over servers or high availability environment is not setup. It will allow at least the application admins to do basic restore of services in event of a disaster.

10. Having developers or power users write access to non ad-hoc reports in Test, or Production environment -
This is a trap, to make changes in any environment other than Dev (ad-hoc reporting is a valid exception although, where reports are built directly in production by end users). Unless you want to be wondering down the road about which environment has the latest working code, no one should be making changes to test or prod servers. Not even administrators, leave alone power users. I'm not kidding, it is a common mistake. Delivery deadline, and customer push, makes toughest of the security policemen bend over to this rule. Set the expectations right, with proper reasoning, and avoid future headache.


I feel compelled to point out one more item, which may or may not be a mistake depending on who you are talking to, so let me continue:

11. Having multiple installations of software withing the same organization - There are some merits of this, like HR in most cases would want a silo installation and have a silo development team due to sensitive nature of data they own. They have a separate hardware and software stack. But finance and sales having silos doesn't make sense. This is particularly true with large shops, who don't care about chump change it takes to build a silo, and fall for flexibility, independence and speed of development with this model. For most of us, it is a mistake to avoid.

Hope you found this article useful.