Installing Business Objects on Windows Server 2003

Hit an error during BO XI R2 install at the CMS Log On step:

Unable to log on to the CMS.

The install was unable to log on to the CMS. Please ensure that your User Name and Password are correct, and try again.

I attempted to use the local Administrator login but the solution I found out there is to use a blank password!

Infoview comes up with

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
A name was started with an invalid character. Error processing resource 'http://servername/businessobjects/enterprise115/In...
<%@ Register TagPrefix="ce" Namespace="CrystalDecisions.Enterprise.WebControls" Assembly="CrystalDecisions.Enterprise.Web...

The problem appears to be that IIS did not have a default dotnet version selection before the BOXI setup. There seemed to be nothing I could do to repair the situation - I had to reinstall BOXI after setting the dotnet version as follows:

• Open IIS Manager
• Right-click Default Web Site and select Properties
• Select the ASP.NET tab
• Select an ASP.NET version in the dropdown and click OK

Cognos Oracle Content Store Creation

Cognos "Quick" Start Installation and Configuration Guide transcript

Error: Cognos Configuration error creating new data store

Database connection failed
Content Manager failed to start because it could not load driver "oracle.jdbc.driver.OracleDriver"

This went away when I copied classes12.jar from ORACLE_HOME\jdbc\lib to c8_location\webapps\p2pd\WEB-INF\lib

Error: Cognos Configuration error creating new data store #2

Content Manager is unable to initialize the content store by using the initialization file "C:\Program Files\cognos\c8\configuration\schemas\content\oracle\dbInitTest_oracle.sql"

It appears the Schema user has insufficient privileges. I have given the following:

Roles

• Connect

System Privileges

• Alter any procedure, sequence, table, trigger
• Create any procedure, sequence, table, trigger, view
• Delete any table
• Drop any procedure, sequence, table, trigger, view
• Insert any table
• Update any table

When I execute the script in SqlPlus the error is ORA-01950: no privileges on tablespace 'X'. Apparently this means no quota has been allocated for this user.

I attempted to grant unlimited quota on all tablespaces but got an error ORA-30041: Cannot grant quota on the tablespace. I forgot that the temp table space doesn't have a quota, so I was a bit more specific in my selection.

Error: Cognos Configuration error creating new data store #3

Content Manager cannot start because the database character set for the content store is not supported. Before you restart Content Manager, you may need to recreate the content store database or clean it using dbClean_*.sql.

Following the instructions regarding character set, I found NLS_NCHAR_CHARACTERSET = AL16UTF16. Unfortunately this doesn't appear to be sufficient. SELECT NLS_CHARACTERSET FROM NLS_DATABASE_PARAMETERS returned WE8MSWIN1252. Some guidance on changing character set is that it's safest to export-create new db-import.

My steps for export are:

• Bring up Database Control
• Select Maintenance tab
• Select Export to Export Files
• Select Database and enter OS admin user and password
• (Create the export folder before doing the next step)
• Create Directory Object, enter name and folder path and select OK
• Select the name of the newly created dir object from Directory Object dropdown and select Next
• Select the dir object again and select Next
• Enter a job name and description and select Next
• Select Submit

The job kicked off but produced some errors saying it couldn't find the export directory object. Makes no sense to me, I thought I would just try and sneak on by.

I created a new instance with Database Configuration Assistant using character set AL32UTF8 and tried to import the export files into it but I hit a lot of errors.

I decided to use a separate instance just for Cognos. Phew, I could finally connect ok to the Content Database.

Cognos 8 Cryptographic Services

Copied c8_location\bin\jre\version\lib\ext\bcprov-jdk13-125.jar to java_home\lib\ext. I have no idea if this has to be done but I did it anyway.

Starting the Service

Selected Root node in Cognos Configuration and Action:Test. All came back good except mail server which I don't care about for the moment. Selected Action:Start which was also successful.

Getting the web server to respond

Created virtual directory for cognos8 at c8_location\. Worked through the IE settings and browsed to http://localhost/cognos8. 404 Page not found. Trawled around on web and found that I could Allow All Unknown CGI Extensions in IIS Web Extensions. This time I got 403 Not authorised to view this page.

Figured out that I didn't read the instructions correctly - there are 2 nested virtual directories.

• cognos8 points to c8_location\webcontent
• cgi-bin points to c8_location\cgi-bin. Created by right-clicking the previously created cognos8 virtual directory

At last it works! The Framework Manager install was straightforward after this.

OWB - table upsert mapping

Error:

VLD-2750: Missing update matching criteria in YOUR_DIM Match by Constraint is set to all constraints or a specific constraint for YOUR_DIM, then all mapped attributes of this constraint will be included in the WHERE clause in the UPDATE statement. If there is no constraint on the target, or none of the constraint attributes are mapped, set Match by Constraint to no constraints for YOUR_DIM, and define the match condition on the attributes of the target

Solution excerpted from Oracle forums:

When you use a surrogate key (eg. generated with a sequence, presumably for
a slowly changing dimension) you also have to define a unique natural key on
your target table, and use the natural key for matching/updating.

If you change your target table columns or constraints after the operator
was put on the mapping canvas, there is a possibility that OWB doesn't change
the "Loading Properties" correspondingly. To fix this;

1) first finish up your constraints work, then
2) set operator property "Match by constraint" to "No constraints", then
3) check column attribute properties "Loading Properties" for all
columns;
a) columns being part of the unique natural key should have "Load column when
Updating Row" set to "No", other columns should normally have this property set
to "Yes"

b) the "Match Column ...." properties should be set to "Yes" if part of the
natural key, "No" otherwise

c) "Load column when Inserting Row" should normally be "Yes".

4) Finally, you can change the operator property "Match by constraint" from
"No constraints" to your natural key

SSIS Oracle RDB to 10G source

Lessons for me from this exercise:

• I had to throw away the custom ODBC script tasks I had built for RDB and create a new OLE DB based extraction package
• Data that previously loaded into SQL Server Int columns now caused a datatype mismatch error. I had to change the target columns to Numeric
• This had the flow-on effect of causing many data flow data sources to break with an invalid index error. These data sources had to be recreated
• Some text data that had previously loaded in a trimmed state now had trailing spaces

So that was the dev environment. To get the packages to work via IIS required:

• Had to allow inprocess in SQL Management Studio for the provider OraOLEDB.Oracle http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766133&SiteID=1
• Working my way through the steps in http://support.microsoft.com/kb/255084. The reboot actually made a difference

SSIS Oracle OLE DB connection

Set AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component

http://blogs.conchango.com/jamiethomson/archive/2005/10/25/SSIS_3A00_-Connect-to-Oracle.aspx

So how do you do set this attribute for an Oracle ODBC source?

Everybody positioned in Leaders Quadrant for Business Intelligence

Is the recent BI M&A activity rendering this Gartner report meaningless?

Microsoft's rating upgrade is unfortunately overshadowed by their being too cheap to license a copy of the report. You have to go back to the old guard for this (thank you Cognos):

http://mediaproducts.gartner.com/reprints/cognos/vol4/article3/article3.html

Mea culpa, MS did actually dip into its wallet but didn't refer to the report location in their press release.

OWB - External Table to Dimension

I have a dev OWB 10g R2 environment into which I want to load up some reference dimensions from production. I don't have a connection to production so I thought I could load the dimensions as files since they are small. However they have a large number of attributes that I wanted to avoid rekeying if I possibly can.

Consequently I was looking for a way to load the dimensions from files with no transformation and minimum effort.

The following approaches don't work:

• Create the file location, create the external table using the import option and create the dimension using the import option. What appears to be a dimension import is actually a reimport of the external table.
• Create the file location, create the external table using the import option and create a mapping to generate the dimension operator. Unbound dimension operators aren't allowed in the mapping.

It looks like I wasn't going to be able to avoid rekeying the huge numbers of attributes until I discovered the Create Dimension expert, a free unsupported wizard that can be added to the OWB IDE.