Transformer Object model issue 1

[Pyash]

Dear Cognos Guys,

First of all I really thankful to you guys for helping me out with my previous posts.

Now I have another situation. I have my cubes publised to PPES and right now I have to manually regenerate the cube whenever the cube need to be updaetd. I would like to create a job that will do this on a daily basis. So I figured out that I can use macros or vb script files or even a vb application that can perform the cube generation using the CognosTransformer object model.

I put together a small script, but it is not working. It is generating the following error.

trModel(TestBuild) : (TR0118) Transformer can't read the database [BrOLAP] defined in C:\temp\COGNOS\Exp DM\expense.iqd.

This is the sample script i have.

Dim strModelPath As String
Dim strLocation As String
Dim strModelSource As String

strLocation = "I:\VSS\Br\Cognos v7.3\"
strModelSource = "Expense Data Mart\Transformer\expense.mdl"
strModelPath = strLocation & strModelSource

Set objTransformer = New CognosTransformer.Application
Set objModel = objTransformer.OpenModel(strModelPath, "brOLAP", "pe##01")
Set objCube = objModel.Cubes.Item("Expense")

With objCube
.CompressMDC = False
.Consolidate = trConsolidateDefault
.CubeCreation = trCubeCreationON
.DesiredPartitionSize = 500000
.EstimatedRows = 10000000
.MaxNumPartLevels = 5
.Optimize = trOptimizeAutoPartition
.Update

If Not .IsMDCInUse Then
.CreateMDCFile 'Fails here
End If

End With

objModel.Close

Set objCube = Nothing
Set objModel = Nothing
Set objTransformer = Nothing



I have no clue why it is not working. I am using a DSN to connect to the DB. For some reason I can't use Oracle Driver to create the catalog. So my catalog is creaetd using the ODBC driver for Oracle.

Do you guys have any ideas wht's going on?

Thanks,
Pyash

 

[drlex]

Pyash,
If you manually open the model on the machine on which you run the script, can you generate the cube (or use data source viewer to see the first 100 rows) or does transformer give the same error?
The reason for asking is that the error you are receiving is one given when the database defined in the IQD does not match the data sources defined on the machine, so transformer cannot read the data.


soi la, soi carré

 

[Jim2005]

The UserID and Password used in the way like
Set objModel = objTransformer.OpenModel(strModelPath, "brOLAP", "pe##01")
is for the secure model, not signon for database.

There are two ways to specify database signon for running Transformer in batch mode:
1. Specify database signon for the Data Sources in Data Source properties dialog: Data Source properties dialog box->Source tab->Edit button for Database Signons->Enter UserID & Password->OK->OK. Then save the model in .pyi format rather than .mdl. You don't need to pass in signon for database when building cubes manually or automatically.
2. User Command-line options like:
Trnsfrmr -n -kfield=CarlosR/pw462 Field3.mdl


Hope this help you.
Jim

 

[Pyash]

drlex, thanks for the comments. Really appreciate it

Jim, You rock, man. I did whatever u suggested and it is working like a charm now. I have been pulling my hair for 2-3 days with this issue. Thanks a lot man.

Also do you know why it doesn't work with the .mdl files even if u specify the database signon?

Thanks,
Pyash

 

[Jim2005]

Hi Pyash,

Because a .mdl file is stored in plain text format using Transformer's MDL (Model Definition Language), so MDL files can be opened in any text editor. For security consideration, database signon is not stored in MDL file by default.

Jim

 

[Pyash]

Thanks, Jim