Rejected Records - overall and individual dimension settings

Are handled for the build overall (right click build - properties) and at each dimension that references a lookup (right click dimension - properties)

Cognos Datamanager multi user Catalog

What DM does NOT do:


On opening the Catalog it does not copy the entire Catalog into memory. This would suggest that each user that opened and modified a catalog would overwrite his entire version each time he saved, thus making shared catalogs an impossibility.

This was suggested by COGNOS's recommendation for each user to have his individual version of the catalog.


What DM does:

DM reads the entire object tree at start up. If you change the name of an object in one session but not in another when you try to access you get

1. ERRORDM-CAT-0910 Export failed; JobStream 'LoadOrgExtractsToGroupTable' does not exist.--------------------------------------------------------------------------------

2. ERRORDM-CAT-0406 JobStream 'LoadOrgExtractsToGroupTable' cannot be validated.

This is a fatal error that takes you out of DM and hence explains Cognos' recommendation of individual versions of the catalog.

DM copies the catalog object by object into memory as they are accessed. When you 'Save' you write the altered versions of objects back to the database. I dont know if DM reads unaltered objects back from the database each time or whether it caches them.

What DM should do to allow multi user access to the same Catalog

DM should place a date modified at the root of the catalog tree and this should cause the tree to refresh if a version has an older version. All objects should be referred to by an internal code to prevent a name change causing confusion.

DM should never cache objects unless they are altered. When they are altered they should be locked on the database and other users should be able to view the object with a warning that they cannot alter it since someone else is altering it.

In the word of the Meerkat - ees simples for Cognos to sort out this major bugbear and misunderstood facet of the product.

FileRead funtion returns xFFFE though file is readable

The file is encoded in 16 bit Unicode - the xFFFE is a bof marker of this encoding. Edit the file in notepad and save it in ANSI (8 bit format).

Strictly speaking COGNOS need to expand the capability of DM since this was an output from COGNOS BI I was trying to read!

DM-DBM-0400 UDA-SQL-0564 SQLSTATE=08S01, SQLERRORCODE=0

1. ERROR
DM-DBM-0400 UDA driver reported the following on connection Staging_db:
UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Connection failure (SQLSTATE=08S01, SQLERRORCODE=0)

Come out of the DM application and restart it.

Common DM Errors - leaving a variable declaration as CHARACTER 10

It does not complain if the result is more than 10 characters - it just truncates the results.

Catalog Restore does not work

If you try and restore a backed up catalog (File > Restore Catalog ) into a new catalog it does something and then places you into the catalog you were trying to restore.

What actually happens is:

Restores to the currently open catalog but gives it the catalog name of the restored catalog.

Actions required are an immediate renaming to the new catalog. If you go past this point you will have to remove the list of recently used catalogs since it uses the catalog name as the basis of this.

DM-DBM-0400 UDA-SQL-0115 Inappropriate SQL request.

DM-DBM-0400 UDA driver reported the following on connection Staging_db:

UDA-SQL-0115 Inappropriate SQL request.

UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Cannot drop the index 'dmcuser.tbl_Output_financials_Grouped.ix0tbl_Output_financials_Grouped', because it does not exist in the system catalog. (SQLSTATE=42S12, SQLERRORCODE=3703)

Solution - index should be created first - right click on the build and 'view DDL'. Under create index there will be SQL that you can run in your rdbms to create the required index .

DM-DLV-0070 Delivery 'xxx': the column 'period' (delivery element 'period') does not exist in the destination table

Solution - your delivery element must match your column name including Case. This is a bug since rdbms are not case sensitive.

Image 1 and 2. Delivery Properties will allow you to import the target table column names

Image 3. Right Click and do Target Column Check on the build to check you match the case.

DM-EXP-0001 Identifiers are not allowed in this context.

DM-EXP-0001 An expression parse error occurred at position 141;Identifiers are not allowed in this context.

You missed the $ off the variable or you copied the function example in and did not change the dummy arguments to real ones!

Easy way to Debug Expressions

Execute the Build/Jobstream to where dialog box comes up.


In the Additional options box, type the following string to invoke trace logging
-VEXP_TRACE_VALUES=BASIC,DATATYPE,VARIABLE,BUILTIN


Reduce the number of options if the output is too overwhelming.

The log does not appear in the console window. You have to go to View Logs and then you will see it with the same name as the original log but with _TRACE appended.

Data Manager Logmsg() not working, broken, failed

You need to explicitly switch on user messages to get it to work or use the -M in additional options.

Other areas are in the Job Stream or Build properties although these are always overridden with an execute in DM.

Does anyone know how to set User on as a default?

Cognos Datamanager aka ...

Cognos Decisionstream
Cognos Datamanager
Cognos Data manager
Cognos Decision Stream
Cognos DM
IBM Cognos Datamanager

Data Mangler
Data Mgr aka Data Mugger

etc etc cont p.94

Test part of a calculation

See below. If you highlight an expression you will test just that part of the code.

NB This can be confusing if you do it accidentally.

Cognos Datamanager Comments for Variable and Derivation Calculations

Use C++ // syntax as shown opposite

You can also add them past the semi colon which I have not shown here.

Data Delivery hangs for UPDATE refresh type

The key columns will differ in length or format to the original insertions. Take great care to make them match - see earlier post for some rules and pitfalls.

Import Package Avoid Prompts for Dependents

Individual selections are checked for inclusion of items on which the selection depends. Select All is not.

Therefore select all and deselect the things you don't want.

I know it sounds obvious but I have battled through a mass of pop-ups for want of better to do.

Length of Column output to Build Table

1. Length of column = length of column in query.

2. If column is checked against a lookup then if the lookup has a bigger length the output column inherits that. NB This may include static elements in the lookup.

3. You may need to remove and re-instate a lookup to get the output column to accept a changed length. This is probably a bug in DM

THE ONLY WAY TO CONTROL FORMAT AND LENGTH OUTPUT TO YOUR BUILD TABLES IS TO USE DERIVATIONS.

Creating a Primary Composite Key for DM Output Table

Tick all the keys you want in your primary key. Do not tick the index button against each column since this creates single indicies and will SLOW up the load.

Instead tick the Index box next to the Index Options.

If you open the index properties (right) then ensure you untick the suppress error when you are developing otherwise the index creation will fail without warning.

The index is not actually a primary key but a unique index since a primary key requires all non nullable columns.

Variables in Datamanager - Manual Unclear

NB Variables are case insensitive

PARISHILTON
parishilton
ParisHilton

are all equivalent.

1. Explicit Declaration of variables can be done at 6 levels.

========== Highest Level ================

(a) As an environment variable

(b) In the command line to invoke DM build or Job Stream

(c) In the Jobstream variables tab

(d) In the Build variables tab

(e) In the DataSource variables tab

(f) In the declaration of a function

========== Lowest Level ================

The lifetime of the variable is the lifetime of the item within which it is defined.

The variable is visible and modifiable to all lower levels unless it is masked when a variable of the same name is declared at a lower level.

NB do NOT use a $ when you declare a variable. It does not complain if you do!

2. In addition to the above levels Variables can be used

(i) In derivations in the Datasource

(ii) In derivations in the Datastream

(iii) In derivations in the Transformation Model

NB Make sure you use a $ to distinguish a variable from a derivation or an argument.

Variables can be assigned to derivations.

$x = y ;

In the event that multiple assignments take place the value returned to the jobstream will be the LAST calculated value.

Derivations can be assigned to variables

eg

$x

or

return $x

3. Implicit declaration is done

When a function is run at the function level during testing then any variables in the function are implicitly defined.

When the Build is run at the build level then any variables at the build are implicitly defined.

If you rely on an implicit definition and run at a higher level then you get this message.

DM-PRS-1001 Error parsing build specification, failed to replace special items;variable 'DI' could not be found

Implicit declaration will not work where you assign a variable to an expression that contains the same variable eg $x := $ x + 1 ;

4. Variable tracing

Does not record every assignment, but seems to be triggered on the creation of a variable. Certainly it shows variables as being incorrectly assigned to a ''.

DM-BLD-0022 The domain size of 256 was exceeded on Dimension 'EmployeeNumber'

This will be when you are aggregating or rejecting duplicates or other action that requires a hash table.

Double click on EmployeeNumber dimension and increase the Domain Size from the default 256.

IBM Datamanager training at Staines

If you stay at the Travelodge next to TK maxx make sure you get a room on the second floor as the floor board creak otherwise.

The instructor, Salim, was excellent. The course emphasises Kimball and his methods of Warehousing. Not enough emphasis is placed on Variables and Functions which turn out to be an integral part of DM and only get one chapter.

Overall very good and the bacon butty with tabasco from the IBM canteen is a great way to start the day.

DM-EXP-0003 An expression validation error occurred

DM-EXP-0003 An expression validation error occurred at position 220

Unknown variable '$aperiod'.

Recheck the variable and in the function or calculation and you will find trailing blanks to remove.

Using Cognos Data Manager to produce TM1 cubes

The key is to have the TM1 binaries in your Windows PATH variable.

Further info here

http://forums.olapforums.com/viewtopic.php?f=3&t=1269

How to Parameterize SQL from your Data Source

1. Set up a variable for a Fact Build by right clicking properties (see picture)

2. The key to embedding the parameter is to use the curly brackets in your SQL.

select "SecondedToFromRate",

"SecondedToFromLegacySystemID",

"SecondedToFromLegacyEmployeeID", "GCI"

FROM "PeoplePortal_Headcount"

WHERE D_I = '{$DI}'