OBI Tips & Tricks: recover a forgotten oc4jadmin password

Have you ever made the mistake of forgetting your OC4J administrative password when installing OBIEE? It's happened to many of us and here is a great solution originally from David Kwan that can help you reset your password in a few minutes. I can't claim this as my own but have used this several times to help clients recover from any non-documented installs of OBIEE on their servers.

So here's the secret from Oracle Technical Support to reset it.

Please use following steps to reset your password:

1. Navigate to the <oc4j_root>/j2ee/home/config directory
2. Backup the file: system-jazn-data.xml for restore or later reference
3. Edit the file: system-jazn-data.xml
Locate the following entries:

<user>
<name>oc4jadmin</name>
<display-name>OC4J Administrator</display-name>
<description>OC4J Administrator</description>
<credentials>{903} MMGWnYKcnMKBBOR1LroHHrQpy3zmVbGEohN4aUSjxK4=</credentials>
</user>

and add the XML attribute deactivated="true", as shown below:

<user deactivated="true">
<name>oc4jadmin</name>
<display-name>OC4J Administrator</display-name>
<description>OC4J Administrator</description>
<credentials>{903} MMGWnYKcnMKBBOR1LroHHrQpy3zmVbGEohN4aUSjxK4=</credentials>
</user>

When you next start the oc4j instance using "oc4j -start" the container will re-prompt you for the password and add the newly encrypted password to the <credentials> tag in the oc4jadmin entry.

OBIEE for Clinical Trial Management System (CTMS)

I just wanted to share my experience and thoughts about implementing an OBIEE Application on top of a Clinical Trial Management Application.

Oracle Siebel CRM Application has a model for Clinical Trial Management System, also known as CTMS targeted for biotechnology and pharmaceutical research and analysis. There is a potential demand for Analytics solution to address the needs of a Business Intelligence solution for the huge data cycles as a part of the clinical trials.

The legacy source of Business Intelligence reports for this data in most of the IT departments for this is through a legacy excel or an home grown application. Prepackaged OBIEE Applications does not have a module for Clinical Analytics and so there is a need to develop a complete custom OBI application in order to accomplish the requirements.

The challenge is to understand the business requirements and convert them to a data model.CTMS can be for Human Health or Animal Health divisions.

These implementations would need functional expert who understands the Siebel CRM CTMS application functionality end to end, CRM Data Model as well in which case a CRM application architect can explain any customizations done in data model design of the Siebel CRM Application. OBI architect needs to understand CRM data model and also the actual business process flow of a CTMS application.It is observed that usually there is a customization to an extent of 25% on the CRM application.

My experience as an OBIEE Architect for this kind of implementation was challenging and interesting. Requirements gathering sessions must be interactive with group of SME's, Team of members from business, project sponsors to mitigate any risk of
slipping the time lines. It is recommended to plan for regular client reviews and approvals of every build to avoid any gaps in the expectations by the client .

At a high level the reporting requirements may include tracking budget and finance, clinical trials, activities, investigators,Initiations, enrollments, expiration's, terminations. Cross dimensional hierarchies from Program to Protocol to Site to Subject is commonly desired.

Major dimension tables specific to CTMS includes Program, Protocol,Site,Subject, application, Investigator. Other common dimensions include Accounts, Contacts, Activities, Time, Geography,product, etc.

Here is a screen shot of a sample rpd for CTMS

Creating and connecting "Work Repository" for ODI

This is in continuation to my earlier BLOG. We have created a Master Repository for ODI and ODI also needs a Work Repository which is a data structure that contains the data models, projects.

The following are the steps to create a Work Repository

1. Connect to the Master Repository that we created earlier.








2. Open the Repositories View by clicking on Windows -> Show View -> Repositories










3. Right Click on Work Repositories and Click Insert Work Repository













4. In the connection window, complete the following parameters:

Name: Type the name for your work repository connection.Eg:ODIWorkRepository
Technology: Choose the technology of the server to host your work repository. I
have selected Oracle since my database is Oracle
User: User id of the owner of the tables you are going to create and host the work
repository.
Password: The above user's password.


















5. In the JDBC TAB provode the following details :

JDBC Driver: The driver required for the connection to the DBMS to host the work repository.

URL JDBC: The complete path of the data server to host the work
repository.









6. Click on Test. You must not click O.K without testing the connection

7. Now Click on OK to validate the parameters for connecting with the server to host our work repository. A window appears, asking you to give a unique name and user id code number to the repository.

8. In the window Work Repository, complete the following parameters:
ID: Give a unique number to your repository, from 1 to 998 included
Name: Give a unique name to your work repository eg: ODIWORKREPOSITORY
Type: Choose "Designer" in the list.

9. Validate by clicking on OK. The creation of the work repository begins and just
follow the different steps on the console

10. When the work repository has been created, the Work Repository window closes.

Connecting to Work Repository

The Work repository may be accessed through the modules Designer and Operator

1. In the Start Menu, select Programs > Oracle Data Integrator > Designer, or launch
the Designer script (bin/designer.bat or bin/designer.sh)
2. Click on the button New (first button to the right of the field Login name).
3. Complete the following values for Oracle Data Integrator Connection:

Login name: A generic alias Eg: ODIRepository)
User: SUPERVISOR (in capitals)
Password: SUNOPSIS (in capitals)

In the Database Connection(Master Repository)section, provide the following information:

User: User for the Master Repository
Password: This user's password.
List of drivers: The driver required to connect to the DBMS hosting the master
repository we have created now.
URL: The complete path for the data server hosting the master repository.

In the Work Repository section provide the following information:


Work repository name: The name of the work repository that we created. We can select the repository by clicking on the icon to the right side

4. Click on Test to check that the connection is working.
5. Click OK, OK. The module Designer opens

BICG Exhibiting at Oracle OpenWorld October 11-15, 2009

BI Consulting Group's booth can be found at Moscone South, booth number 2201. Stop by the BI Consulting Group booth to speak with management and learn about their professional services. During this year's event, BI Consulting Group will be hosting a happy hour event on the evening of Monday, October 12th. Details yet to be announced - looks to be a great time!

Connection Pools – Best Practices

Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

· Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage

· Create a separate connection pool for execution of session variables

· Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables

· If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

Creating and connecting ODI Modular Repositories

This is in continuation to my earlier BLOG "Installing ODI". In here I am summarizing details about ODI Modular Repositories and illustrating the steps needed for creating the ODI Repositories. These repositories must be created in order to use ODI engine.

The modular Repository for ODI is made up of a Master Repository and of one or several WorkRepositories. These repositories can be installed on any database engine that supports ANSIISO 89 syntax.

I have created these repositories on Oracle 11g Database

Creating Master Repository

1. Create a database user for Master Repository

For Example:
SQL>create user snpm identified by snpm
default tablespace system temporary tablespace temp;
SQL> grant connect, resource to snpm;

2. Click on repcreate.bat file under bin directory of ODI install root eg: C:\ODI\oracledi\bin and provide the following values and Test connect to make sure that the connection is successful, Select Technology as Oracle





3. It invokes Master Repository creation wizard window


















4. Click on the ellipsis button for Driver and select Oracle JDBG Driver from the dropdown list of names under Name













5. Update other parameters in the window like URL. The URL syntax can be got by clicking the ellipsis button for URL. After you get the sysntax update the values appropriately, provide values for user, password which are the values that you gave while creating the user for Master Repository. Change the ID default value, Select Technology as Oracle.(I have done this since my database is Oracle). Click Test Connection button for successfull connection and Click O.K

6. This brings up an Oracle Data Integrator Information window indicating successfull creation of the Master Repository. Click OK

Connect to the Master Repository

1. Launch Topology Manager from the Start Menu



2. Click on New button and provide values for Login Name which may be any name, User: SUPERVISOR (use capitals), Password: SUNOPSIS (use capitals)

3. In the Database Connection (Master Repository) section provide values for the Master Repository username, Password, select your driver name from the Drivers List (I have selected Oracle JDBC Driver). Click on the hand icon to the right side of Driver Name to update this automatically, similarly Click on the hand icon to the right side of Url to update this automatically but you have to provide the values for these parameters.

4. Click Test to validate successfull connection

In the next Posting I will illustrate the steps for creating and connecting "Working Repository" for ODI

OBIEE 10.1.3.4 Sample Application

In case you were not aware, the recently shipped 10.1.3.4 release of OBIEE includes a sample set of data, metadata, reports & dashboards that represent best practice for OBIEE metadata, report & dashboard development. There are some really good example reports and dashboards as well as solutions to common metadata modeling problems. It's well worth installing.

The sample files are also available stand alone on OTN for those not wishing to download the entire 10.1.3.4 distribution, and yes, they do work with previous 10g releases. Apparently the real trick is actually finding the file on OTN, so to save you the trouble here is a direct link: *LINKS UPDATED*

http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_analysis_setup_files.zip

The documentation of best practices is contained in the following file:

http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_app_content_guide.zip

ODI Installation on Windows Vista

Off late ODI is expected to be predominantly used for Data transformation needs more specifically to OBI needs. I just wanted to summarize the Oracle recommended system requirements and Prerequistes for installation of ODI and illustrate the steps for installation of ODI

System Requirements

Recommended System Requirements for Data Integrator Client:

512 MB RAM
300 MB Disk space
Operating system with graphical capabilities (such as Microsoft Windows, Linux, HP-UX, or Solaris)
Java Virtual Machine 1.5
Java Development Kit (SDK) 1.5
TCP/IP network

Recommended System Requirements for Data Integrator Agent:

512 MB RAM
200 MB free disk space on your hard drive
Others as for above for Data Integrator Client component

Recommended System Requirements for MetaData Navigator:

512 MB RAM
100 MB free disk space on your hard drive
J2EE 1.4 compliant application server, such as OC4J 10.1.3 and above
Others as for above for Data Integrator Client component

Recommended System disk space for Oracle Data Integrator Repository:

Master --> 30 MB

Working --> 40 MB. It varies depending on the size and volume of models and projects

Recommended System Requirements for Metadata Navigator:

Oracle Data Integrator also requires JDBC / JMS drivers

Oracle Data Integrator uses the JDBC (Java DataBase
Connectivity) standard for databases, JNDI (Java Naming and Directory Interface) and JMS (Java
Message Service) for MOM (Message Oriented Middleware).
Oracle Data Integrator does not include any third-party driver licenses.
Oracle Data Integrator requires the drivers for your different technologies to be installed in the /drivers sub-directory if they are not already referenced in the Java classpath.

Follow these steps to add a new driver for Oracle Data Integrator

1. Copy the driver packages (.zip or .jar files) to the /drivers sub-directory under the Oracle Data Integrator installation folder. Oracle Data Integrator will automatically detect the driver the next time it starts and will add these packages to the classpath. This must be done on each machine executing an Oracle Data Integrator component.
2. If the driver is used to connect a repository and you wish to use Metadata Navigator to connect to this repository, then copy the driver's packages in the /WEB-INF/lib subdirectory of the Metadata Navigator installation folder, then restart the application server.

Download Software

The latest software version ODI 10.1.3.5.0 can be downloaded from http://edelivery.oracle.com

Select Oracle Business Intelligence (10.1.3) Media Pack for Microsoft Windows (32-bit) under Oracle Business Intelligence Product Pack and download Oracle Data Integrator, Oracle Data Profiling, and Oracle Data Quality for Data Integrator 10g (10.1.3.5.0) for Windows Platforms (CD) with Part Number V14803-01

Extract the zipped file to a Folder eg: C:\ODI10.1.3.5.0_SW

Installation Steps

Click on Setup windows batch file located in eg: C:\ODISW\setup\Windows
It invokes the ODI setup through Oracle Universal Installer Window

Select the option button for Oracle Data Integrator 10.1.3.5.0 and click Next
I have selected this as I just wanted to install this component. The default option is setting up all the components of ODI i.e Oracle Data Integrator, Oracle Data Profiling, Oracle Data Quality

Oracle Data Profiling is a component that is used for monitoring and analysing the data quality.

Oracle Data Quality is a component used for Data cleansing

Leave the default option for installation type as Complete which needs 281 MB space.click Next. In case you select all components then the setup needs 1.18 GB of disk space

In this screen you have specify the Oracle Home path. Leave the default values and Click Next. If you need to change you may do that


Select Install
Click Exit after successful installation

Please follow another posting for ODI Modular Repository. It illustrates steps for creating and connecting ODI Modular Repositories

Oracle Essbase and the Meaning of "Time"

Of all the potential dimensions in an Oracle Essbase cube, in my opinion the most misunderstood is "Time". The reason is that "time" frequently means different things to different people, even within the same department.

When gathering requirements for a new Essbase cube, I first get a clear understanding of how people view their calendar(s). This defines their world. Typical questions include:

• When does your month begin and end? What specific dates?
• Can you provide a company calendar so I can see how days roll up to weeks, weeks to months, months to quarters and quarters to years?
• Does everyone follow the same exact calendar in the company? If not, how do they differ?
  

Businesses can operate on a traditional calendar, a fiscal calendar, their own unique calendar, an industry specific calendar or some variation of these. Sometimes they operate using all of these at the same time. Here are characteristics of common calendar types:

1. Traditional calendar: January 1 - December 31; months end on last calendar day of the month.
2. 4-4-5 calendar: this is a calendar where each year has 4 quarters and each quarter is made up of 13 weeks. Each 13 week quarter is then grouped into two 4-weeks "months" and one 5-week "month". Frequently used for retail and manufacturing companies since it provides a consistent schedule for when the end of a month or quarter will occur.
3. Unique company calendar: some major retailers have their fiscal years on non-traditional calendars. For example, Target Corporation has a fiscal year from February 1 thru January 31. It consists of 12 months, but is staggered to end in January to assist with their peak sales reporting period during December.
4. Segment calendar: NPD Group is leading global provider of consumer and retail market research information for many industries. They break the year down into their own unique time buckets. This is one example but other industry groups do the same type of thing.
   

Sounds crazy but "time" really does mean different things to different people. Luckily, Oracle Essbase can handle "time" without breaking a sweat. In my next blog I will demonstrate the use of shared member rollups which allows us to define dates within different time buckets and satisfy the need for multiple calendars in the same cube. I will also attempt to answer the eternal question "Does Anybody Really Know What Time It Is?"

Public Sector Workshop - Manhattan

I was fortunate to have had the opportunity to speak at Oracle's Public Sector Workshop at the beautiful Madison Avenue office in Manhattan last week.

This event was well attended by many of the city's agencies. In fact there was a standing room only feel to the room.

Oracle did a fantastic job of getting the users to connect to this hands-on lab. Full color step-by-step manuals were provided to each of the attendees.

I was asked to provide some insight as to how the Public Sector should proceed in their pursuit of OBIEE and the Analytic Applications. You don't get a second chance when you send in your recommendations to the city council and such. You have to do it right the first time.

BICG has completed over 280 successful implementations of OBIEE and the apps. Many of these have been in the public sector. Our group concensus came up with the following 7 tips for success in the Public Sector.

1. Do not boil the Ocean. - Deploy Analytic Applications in small projects. Do 3 or 4 areas at a time.
2. Sponsorship - Ensure the need for the BI/DW solution is sponsored be an executive.
3. Role Definition - Define clear project roles and responsibilities. Project Manager, Business Sponsor, Technical Sponsors etc. This is a must.
4. User Adoption - Involve the business users throughout the project / including the build phase / use iterative business reviews / brand the project and the interface design.
5. Experienced SI - Use a system integrator such as BICG that fully understands how to deploy Oracle's BI Solutions and has a proven track record.
6. Validation - Data validation is key. Data must be accurate and trusted. Get sign-off that the data is valid and trusted during the testing phase.
7. No Short Cuts ! - Ensure that you deploy a good solution for the users. Get business buy-in and feedback throughout the project.
   

Oracle has several more of these workshops coming up over the next few weeks. If you would like to hear the logic that goes behind these recommendations, contact your Oracle rep for a seat at the workshop - or drop me a line! I would be happy to discuss this with you. See you at the workshop !

-Steve

If you MUST install on Vista...

Over the past few weeks and months I've had the distinct pleasure of installing on my trusty Samsung laptop with Vista Business Edition an interesting variety of appps -- including Toad, Oracle Database 10g and 11g, Apex, OBIEE, DAC, Informatica, Application Server 10g (including the SOA Suite), JDeveloper 11g, various VPN clients, Active Perl... You name it.

Overall I think I can safely argue that the productivity I lost while chasing the ghosts of so many obscure install errors on Vista far exceeds the cost of an XP "downgrade" license -- and perhaps even rivals the cost of a Win2k3 Server license -- but that's another conversation. VMWare -- on Vista or even a Mac -- is a reasonable alternative but it certainly has its performance and productivity drawbacks, particularly on a laptop. Some hardcore Torvaldsians would say, "You should have bailed on Vista entirely and installed Linux!" Yes yes, perhaps I should have. But I didn't, and for many of us, uninstalling the OS on a machine supplied by our employer simply isn't an option.

Thanks to the collective intelligence of the extended Internet community of fellow Oracle techies, I have eventually been able to figure out pretty much all of my problems. In the process I've found that many of the solutions to Vista annoyances boil down to a few common lessons. I thought it might be helpful to share these lessons with you -- and ask that you give back to the Net's wisdom by contributing your own gems of Vista wisdom as well.

By far the biggest stumblings happened during installations. To be fair, once everything was up and running, things worked pretty well. In the six-plus months I've had my machine, I can count on one hand the number of times I needed to reboot during non-install working conditions. Take VMWare out of the picture and reboots are even more rare.

The lessons I have learned fall into three categories: General advice; what to do before OR during an installation; and what to do ONLY during an installation (and discontinue afterwards).

In general...

• Always run command line window as Administrator (easiest just to get into the habit)
• Observe error logs very carefully
• Look for clues in the Event Viewer
• Google well! Choose your search terms carefully - not too restrictive but not too expansive - I find that specific error messages are a good bet
• Don't forget to search Metalink and Metalink3 as well
• Breathe! Funny how quickly things clear up if you just relax a bit
• Read the manual!

Before or during the install...

• Check PATH and CLASSPATH settings - especially important for Java installs
• Set Compatibility Mode to XP (or, for certain apps like Informatica, Win2k3) and/or run executables as Administrator - for installables as well as services (especially tricky when the install unpacks and calls executables throughout the process)
• Install & configure Loopback Adapter for DHCP computers
• Disable IPv6
• Check Metalink and Metalink3 for Vista-related Notes or Patches -- e.g., 444112.1 - "Oracle Application Server 10g (10.1.3.x) - Installation Requirements for Microsoft Vista"
• Read the manual!

Only during install (best to revert to original state afterwards, if you can)...

• Turn off User Access Control
• Turn off virus protection
• Disconnect from the Internet (probably could also be solved by configuring the Loopback adapter, but this trick got me past one particularly intractable roadblockwithout going through the Loopback procedure so I feel compelled to mention it)
• Read the manual! Though I guess you can't really revert to your original state once you've been enlightened -- or, at least, MOST of us can't

What other advice would you give? What would you amend?

Hide PAGE OPTIONS button on the OBIEE Dashboard

There are occasions when a customer does not want to even show the PAGE OPTIONS button on the dashboard.

Here is one way to do that.

Edit "dashboardtemplates.xml" as follows :

<WebMessage name="kuiPersonalizeLink">

<HTML>

<span class="minibuttonOn" style="display:none">

<a href="javascript:void(null)" onclick="return NQWPopupMenu(event,'idPersonalizationMenu',null,'top')">

 

<sawm:messageRef name="kmsgDashboardPageOptionsMenu"/>

 

<img src="fmap:Views/sortdesc.gif" border="none"/>

 

</a>

</span>

<br/>

</HTML>

</WebMessage>

Importing SQL Server Analysis Services Cubes (MSAS) To OBIEE

OBIEE communicates with MSAS using XMLA via http. One of the first steps in trying to import the data into the Administration Tool requests the URL for the data source. If the database has never been enabled for XMLA access (i.e. there is no URL) you will need to configure that before connecting to MSAS with OBIEE. To enable access, the best resource is to follow the instructions provided by Microsoft which are located at http://technet.microsoft.com/en-us/library/cc917711.aspx

Testing XMLA communication to MSAS (outside of OBIEE)

To test the configuration go through the following steps:

1. Right click on My Computer -->Manage --> Services and Applications --> Internet Information Services (IIS) Manager --> Web Sites --> Default Website.
2. Select the olap directory under Default Website (or whatever you called your virtual directory during the configuration)
3. Select the msmdpump.dll --> Right Click --> Browse

4. If XMLA communication to SQL Server Analysis Services is working you will get back XML that looks like this:

Do not worry about the error message in the XML. As long as you get XML back then SQL Server is properly configuring for access via http. If this is not working, go back and check your Configuration steps for enabling XMLA Access on MSAS.

COMMON ISSUES /RESOLUTIONS

Issue: Although you have completed the configuration and confirmed all steps you still cannot get the XML to return.

Resolution: Consider what type of environment you are on and if you are using the correct dll. IIS will install with a 32 bit dll by default, if MSAS is on a 64 bit operating system you may need to reconfigure IIS to use the 64 bit dll.

Issue: When putting in the URL while trying to import the database in OBIEE you get the following error:

[nQSError: 64203] “XML/A error returned from the server: Fault code: ‘XMLAnalysisError.Oxc1010000”. Fault string: “The following system error occurred: No connection could be made because the target machine actively refused it…”

Resolution 1: Check to make sure the Database is running
Resolution 2: Check to make sure all of the appropriate Ports are not blocked

Issue: When selecting ‘OK’ after putting in the URL while trying to import the database to OBIEE you getting one of the following errors:

[sQSError: 46100] Sax Parser Failed with Error Invalid document structure.

[nQSError 46100] Sax Parser failed with error Unterminated entity declaration ‘Content Type’

Resolution 1: IIS is not properly configured. Double-check the IIS configuration steps for enabling XMLA access to MSAS, and validate the configuration by getting the XML displayed when browsing the .dll (above)

Issue: When trying to Import the database you are able to put in the URL (http://machinename/olap/msmdpump.dll) with the username and password and select ‘OK’. The MSAS Server name is then displayed however you cannot see any of the Cubes underneath the Server Name. If you select the ‘Import’ Option the Administration tool crashes with no errors.

Resolution 1: Ensure the user you are putting on the first Import screen is a valid user on the domain and has access to the cubes.

Resolution 2: Depending on your security settings, ensure the IUSR_MACHINENAME System Account has the ability to view and read the cubes. To see the Security Settings and Account Name right click on My Computer -->Manage --> Services and Applications --> Internet Information Services (IIS) Manager --> Web Sites --> Default Website --> Properties. Go to the Directory Security Tab --> Authentication and Access Control --> Edit.

Note: In the security configuration you may check or uncheck the 'Enable anonymous access' box, however do not ever change the default user. IUSR_MACHINENAME is an automatically generated system account with an unknown password so you can never change it back without having to completely reinstall or reconfigure IIS.

BICG & Oracle Host BI Workshop in Denver, CO

BI Consulting Group co-hosted a hands-on workshop with Oracle in Denver, CO on July 30^th. This free hands-on workshop was open to all customers and prospects of Oracle Business Intelligence (OBIEE+).

During the workshop, Oracle presented an overview of OBIEE+ including both the BI Platform and Applications including a demo of the BI Server's meta data layer. BI Consulting Group presented and led the workshop portion of the day where students were able to get hands-on experience with Answers. Labs included building ad-hoc reports with filters, sorting, spotlighting, custom formatting and more. Students within the workshop were also stepped through how to take created Tables, Pivots, and Charts and build custom prompted Dashboards against both relational as well as multi-dimensional sources like Essbase. BICG also presented an overview of best practices for deploying OBIEE+.

For info on workshops and ones that may be coming to a city near you, please visit: http://www.biconsultinggroup.com/aboutus.asp?CategoryID=373

OBI Reports from SAP BW Cube

OBIEE has the capability to build repository by importing a MOLAP Cubes. Let's see the steps to build OBI Repository and develop Reports from a SAPBW cube.

1. Open an instance of the OBI Adinistration tool
2. Click File--> New and give a name like SAPBW to the repository
3. Click File--> Import from Multi-Dimensional
4. In the Import from Multi-Dimension dialog box provide the parameters for connection to the XMLA Data structure



















5. This is the imported SAP Cube structure



















6. Create a new Logical Model with necessary logical joins as per requirements














7. Create the Presentation Layer as per requirements
















8. Make the necessary updates to the configuration files and starting the OBI services

9. Logon to OBI Presentation services and create a sample report to validate the data

Unlocking the hidden meaning...

How often have you come across, or been directed to a specific dashboard request, but you really have no clue what you’re supposed to get out of it? Perhaps it’s a bad design...or maybe it just needs a little explanation.

Ideally, the developer of that report should have included some kind of description. Or, if you’re the developer, you should also include something to give the end user a little direction if it’s needed.

The Problem is that no one wants to crowd valuable screen space with text that may or may not be valuable to everyone. If there was only a way to include a hover-over description… well, there is.

Here is a very simple way to utilize HTML in a Narrative View to provide you with a discrete, hover over description.

1. In your request, add a Narrative View.

2. Edit view as follows: Check the box “Contains HTML Markup”, enter 1 for “Rows to Display”, and add the following text to the Narrative box:(*Note: enter your description in place of the words, Enter Description Here)

3. In the Compound Layout, move the Narrative view to the top, click on the Format View icon, and set the Horizontal Alignment to “Left”.

The result is a [?] symbol at the top left of your request, that the user simply needs to hover-over to see the description you’ve entered.

BI Consulting Group in attendance for Oracle OpenWorld 2009

Location: Moscone South, booth number 2201.

BICG will be showcasing their professional support services for EPM & BI as well as their state of the art third party tools for OBIEE. BICG is participating in two official OOW presentation sessions: 1) with Los Alamos National Labs presenting on their Oracle BI Applications Deployment, and 2) with Capital One, LCRA, PNC, and Motorola presenting on How to Maximize Oracle BI User Adoption.

Additionally, BI Consulting Group will be featuring their Inaugural Insight Awards and Insight Award winners will be participating in the OOW festivities. Winning companies, events, and exact times to be posted at a later date.

Oracle OpenWorld dates are October 11-15, 2009 located in San Francisco, CA.

Simplifying Migration Process – Changing Environment Specific Variables in RPD

When it comes to migrating repository file between environments (Dev - TEST-Prod), one of the common questions from OBIEE environment administrators is if there is a way to change the connection information without having to change them manually in all the places.

Creating repository variables for DSN and DSN Username solves the issue to an extent. However, imagine having to deal with changing multiple DSNs and their respective usernames. Moreover, there is the setting password to the DSN username. One way to automate the setting of the variables is to store the values in a file and set them via admin tool command line mode.

First, define repository variables that can be used in the connection pool.

Reference the DSN information in the connection pool.

Now create a control file SetVariables.txt with the environment specific values.

_______________

'To Open rpd - Open <rpdname> <Administrator User> <administrator password>

Open YourRpd.rpd Administrator SADMIN

'Setting OLAP DSN variable

SetProperty "Variable" "OLAP_DSN" Initializer " 'QA_DSN' "

' Setting OLAP DSN Username variable

SetProperty "Variable" "OLAP_DSN_USER" Initializer " 'QA_User' "

'Setting OLAP DSN User QA database password

SetProperty "Connection Pool" "AppDW"."Connection Pool" "Password" "QAPassword"

Save

Close

Exit

______________

Create one per environment.

In the command line run the following command:

AdminTool.exe /command SetVariables_QA.txt

And the repository is now prepped for the other environment (QA in this case).

Essbase Alias Tables - The Universal Language Translator

Essbase alias tables provide a nice way for users in different countries, speaking different languages, to share and understand the same member names in their native tongue. This can be very valuable in gaining the user acceptance you need for your new cube since people will feel more comfortable with something they understand.

An Essbase cube may contain up to ten different alias tables so there's a lot of flexibility available. There are two ways to create alias tables:

1. create an empty alias table and then populate the members manually
2. import an alias table from a source file which contains the desired member names

Details on both methods are available in the Online Help section of the Essbase Administrators Guide.

Here is an example of the manual process:

• Open the database outline

• Select the "Properties" tab

• Right-click on "Alias tables"

• Select "Create alias table . . ."

• Give alias table a name (ie "French" in this example)

• This will add the table "French" to the list of available alias tables

To populate the new alias table with members, follow these steps:

• Select the "Outline" tab

• Select a member. Right-click and select "Edit member properties . . ." and go to the "Information" tab

• Enter the new member alias name under "French" and press "OK". I chose to use "Cafeine Cola Libre" as the French name for "Caffeine Free Cola".

• When you're done entering names, save and close the outline

Note that Essbase Administration Services will show only the active alias table in the outline. You can change which is the active database alias table by going to the outline "Properties" tab (select a table, right-click and select "Set as active").

I have created cubes with several foreign languages in different alias tables. Just remember that as new members get added, you must also have a way to include the correct aliases for all members.

When users enter Excel, point them to the new alias table ("French" in this case) under the Essbase User Options so their retrievals will show the new member names.


If you're really ambitious, you can insert special member names into alias tables which you can use during Excel macro report creation. I will leave that topic for another day.

7 Basic Steps to Succeed with Oracle Warehouse Builder

This article shows the 7 basic steps to succeed when start working with OWB. Those steps were taken based on my 6 years experience with the technology and mentoring a few developers and data architects about how to work with OWB 3.X, 9.X, 10g R1, R2 (Paris) and recently 11G.

OWB has improved a lot on the past years. Oracle made significant enhancements on OWB since its Release 2 Paris version and recently on 11g; the strategy was to integrate the OWB core with the Oracle Enterprise Edition database. Regardless of the version of the tool those are some basic steps to succeed when start working with OWB:

1. Take baby-steps, even after you get more knowledgeable about this tool. Create a simple source to target map first, deploy, run and check the results. Doesn’t matter how complex your mapping logic is, it is much easier to work iteratively in instead of try to accomplish everything at once.
2. Make the mappings simple and try to get some DFD documentation (data flow diagrams) or source-to-target diagrams before start developing your ETL. Don’t forget to keep the documentation updated! Sometimes creating SQL queries helps you to imagine about how to design the ETL in a visual way based on the results. If you are not hands-on SQL, I strongly recommend learning the ANSI coding basics before working with this tool.
3. Keep it clean, delete any unused objects and rely on the recycle bin. If you are not sure about removing the object simply rename the mappings as “delete_me_please” and remove it later. OWB still has the snapshot feature which allows you to work iteratively without making copies of your mappings, even if this feature is slow. Do not forget to purge the recycle bin frequently.
4. Do not change any default properties of the mappings until you finish the ETL framework. You can start playing with some properties such commit frequency or execution modes such as set based or row based after making sure everything runs.
5. Keep the Metadata synchronized with the physical tables and manage the data warehouse tables from the OWB tool. It is a good practice to keep all the objects information such indexes and primary keys within the Metadata and try to avoid changing objects directly on the database.
6. Try to include all of your mappings and transformations with a Process Flow. It integrates the mappings, defines the load sequential order and helps to identify unused mappings under your module.
7. I have seen a few developers and managers complaining about the performance and maintainability of the tool. Majority of the time the performance issue may be related to the overall database performance. If your project is not maintainable you may have some extremely complex mappings or undocumented process. Isolate the issue or even try to recreate your mapping in a separate environment first until you find the real issue.

Once you get more comfortable with the tool there are dozens of interesting and advanced techniques that will allow you to be more effective and successful on your projects!