Insert Data Source Automation

[mjcotter]

I am writing a VB program to automate the adding in
of IQD datasources.

Has anyone done this???

I am getting stuck on this line

Set objDataSource = objModel.DataSources.Add(trIqdDataSource)

It says the argument value is wrong. Anybody know what it
should be??? I got the trIqdDataSource from the
Cognos support site.

Thanks!

 

[jbecher]

We've had a lot of success using MDL files over OLE/ActiveX automation with Transformer. I was sceptical at first but MDL ends up being *much* simpler and more reliable.

You don't need another exe or script to execute MDL (because Transformer interpretes the commands for you).

Any existing model can be referenced and manipulated through an mdl script. You write a simple script in a text editor and open it in Transformer.

Your IQD add function could be achieved with following script:

OpenPY "C:\models\mymodel.pyi"
DataSourceAdd "New IQD" Source "C:\datasource\myiqd.iqd"
SavePY "C:\models\mymodel.pyi"
...

or you could build cubes immediately with

CreateFiles

HTH,

Justin

 

[mjcotter]

Ok, so I created a file called "add.mdl" with the 3 lines
of code that you provided:

OpenPY "C:\models\mymodel.pyi"
DataSourceAdd "New IQD" Source "C:\datasource\myiqd.iqd"
SavePY "C:\models\mymodel.pyi"

To run it, I open Transformer and open "add.mdl". When I
then go open "mymodel.pyi", it shows the "new" data
source, but there is no plus next to it so I can expand it
and see what data elements were brought into it. If I
right-click and view the properties, it points to the
correct IQD.

It looks like doing a "modify columns"; resaving the
PYI; exiting; and then reopening it and the plus sign
shows up.

Hmm. Is there a way to automate a modify columns in the
script.

Thanks!

I have a call in to Cognos. If they ever call me back
and give me something good, then I will post it.

 

[mjcotter]

Where did you find the commands like
OpenPY and DataSourceAdd to add to
the script?

Thanks.

 

[jbecher]

Oh yeah, columns might be helpful...

Add this line after the DataSourceAdd command:

CreateColumns "New IQD"

The MDL reference material should be installed with Transformer (it's under Cognos BI --> Documentation --> PowerPlay --> Complementary Material)

Good luck!

 

[mjcotter]

Thanks, Justin!

I'll give CreateColumns a try. Thank you very much!!!
I'm going to have to track down the PowerPlay CD since
I don't have the Complimentary Materials folder on my
PC. I do have the Documentation folder but only see
Macro help files, no MDL scripting.

Thanks, again!

Matt

 

[mjcotter]

In case anyone was wondering, the MDL scripts work like a charm and are the best solution IMHO, but if you wanted to do it in VB or CognosScript, then this also works ...

' ***** Automatically ADD in Datasources to a Transformer Model. *****

'$Include "TranConst.inc"

Sub Main()
Dim objTransApp As Object
Dim objModel As Object
Dim objDataSource As Object
Dim strModelPath As String
Dim strDataSourcePath As String

strModelPath = "X:\Models\matt.pyi"
strDataSourcePath = "X:\IMRs\Product.iqd"

Set objTransApp = CreateObject("CognosTransformer.Application&quot
Set objModel = objTransApp.OpenModel(strModelPath)
Set objDataSource = objModel.DataSources.Add(trIqdDataSource)

With objDataSource
.GenerateCategories = True
.GeneratePowerCube = trGenerationDefault
.IsolationLevel = 0
.LocalPath = strDataSourcePath
.Name = "Go Sales"
.SetsCurrentPeriod = True
.SourceType = trQuery
.Update
End With
With objModel
'.Name = "GO Sales"
'.DoAutoDesign
'.TestBuild 20, True
'.Update
.Save
'.SaveAs strLocation & "\Discovering Transformer\GO_SalesX.mdl"
.Close
End With
Set objDataSource = Nothing
Set objModel = Nothing
Set objTransApp = Nothing
End Sub