Automate Powerplay Cube update? 2

[Jontility]

Hello all:
Can anyone suggest the best way to automate Cube update or cube rebuild...either through a macro or batch process...and also...what will be the cons of incremental update?..which is better...any ideas?


Thanks...
Jon






John Organ
MIS

 

[flex13]

Here's a macro to build a secured cube.

Sub Main

Dim objTrans as Object
Dim objModel as Object
Dim objCube as Object
Dim strModelName as String
Dim strPath as String
Dim strModel as String
Dim strCubeName as String

'*** File names and path(s) ***
strPath = "E:\"
strModelName = "secured_model.pyi"
strModel = strPath & strModelName

'*** Open model inside Transformer ***
Set objTrans = CreateObject ("CognosTransformer.application")
Set objModel = objTrans.OpenModel(strmodel,"Administrator","password","Root User Class")
Set objCube = objModel.Cubes.Item(1)

'*** Builds Cube ***
objCube.CreateMDCFile

End Sub

As for some cons against incremental updates:
- Not allowed ANY model changes after you build.
- Still need to perform periodic full cube builds.
- Maintenance nightmare (IMO)

 

[Jontility]

Flex13:
sorry it took me long time....ok...I hope I'm asking the right question, I already have a cube created...my question is, I will like this cube to be updated through automation either through a macro or batch process....I'm new to Cognos product....John



John Organ
MIS

 

[john76]

1 - Automated refresh of cubes
You can put this command line in a batch file and schedule through Windows scheduler for simple automation:

"<path to transformer directory>\trnsfrmr.exe" -n2 -s -i -nologo "<path to .pyi model file>\<your model filename>.pyi"

For example (taken from my batch file)

E:\Program Files\Cognos\cer3\bin\trnsfrmr.exe -n2 -s -i -nologo "F:\BI\Cognos\Transformer\Models\Daily Transaction Powercube.pyi"

2 - Incremental updates of cubes
Incremental updating is not generally a good idea. The reason is actually pretty obvious when you know exactly what incremental updating is. It basically tells Powerplay "Everything that is in the cube stays, and everytime I refresh the cube I want to add new data to what is already there".

This works well in very rare circumstances, and horribly in all other cases. If you are an accountant (for example) you may have a cube build on your GL data, for which you CANNOT change or delete any entries you already made (rather you have to "reverse" the transaction by putting in a new transaction that is the negative amount of the first), also the query that preps the data MUST be only the new entries, otherwise you will duplicate the data in your cube.

Given that you can live with these restrictions (anyone? anyone?) then incremental updates make build times much faster on your cube. Otherwise, it does nothing to address your original question on automating the refreshing of said cube, which I suspect is what you thought it might pertain to.


Hope this helps,
John

 

[Jontility]

John....Thanks....I will try the command tommorow at work....the automating the refreshing of cube is what I wanted..Thanks alot...and thanks Flex13



John Organ
MIS

 

[johnherman]

Incremental updates are most useful for orders, sales, and purchases.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[john76]

johnherman,

I agree that they can be useful if you have a large, slow-to-build and time-critical cube, but how do you deal with the situation of changes to previously entered data?

John

 

[johnherman]

john76, you are correct in that you really can't use incremental for those situations. However, it is generally good for things like sales, where you shouldn't do corrections, but rather have returns, which are a separate transaction. Cubes for things like GL, bank balances, Inventory, and similar "running totals" should not, generally, be cubes.

Most of my experience using incremental cubes had to do with sales, purchases, and insurance claims (similar to invoices) and we would update the cube monthly after accounting had "closed" the month.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[johnherman]

correction: similar "running totals" should not, generally, be <incremental> cubes.


Sometimes the grass is greener on the other side because there is more manure there - original.

 

[john76]

johnherman,

OK, I will agree with you then on tables where no corrections/modifications to previously entered rows are permitted, only "new" transactions.

I will still raise the caution flag on the point that the queries which gather the data to be inserted into the cube needs to ensure that only new rows are inserted as Cognos will have no qualms about putting the same record in twice.

John

 

[johnherman]

A point well worth raising, john75

As with any process, a strong degree of due diligence must be applied either programatically or procedurally to prevent posting the same data more than once, or missing posting it altogether.

Sometimes the grass is greener on the other side because there is more manure there - original.