Final step,
I have managed to get the cube to build and deploy from within Transformer.
What I have now done is copied the method from an existing SSIS package with the thought that if it works for something else it'll work for my process.
BUT, I have attached the LOG files for the last two runs, one works one doesn't, the first is manual the second automated. The second is failing because of a password as I read it. But I can't see where to put it. I have set the SQL job to run from the system account, the same as the working packages.
Within the SSIS execute process task, Executable is C:\Program Files (x86)\ibm\cognos\c10\bin\cogtr.exe and arguments is -n2 -s -g "f:\BI\Models\FootfallV3Cube.pyj"
Again the same?
Could someone point me in the right direction please.
Transformer(XX.XX.XX.XX) Tue Nov 17 16:26:01 2015
LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252
Transformer Tue Nov 17 16:26:02 2015
Transformer(XX.XX.XX.XX) Tue Nov 17 16:26:02 2015
LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252
Tue 17 Nov 2015 16:28:14 4 00000000 Start cube update.
Tue 17 Nov 2015 16:28:14 4 00000000 Initializing categories.
Tue 17 Nov 2015 16:28:14 4 00000000 Timing, INITIALIZING CATEGORIES,00:00:00
Tue 17 Nov 2015 16:28:14 4 00000000 Start processing data source 'Footfallv3~1'.
Tue 17 Nov 2015 16:28:14 4 00000000 Reading source data.
Tue 17 Nov 2015 16:28:15 4 00000000 Timing, OPEN DATA SOURCE,00:00:01
Tue 17 Nov 2015 16:28:45 4 00000000 End processing 645293 records from data source 'Footfallv3~1'.
Tue 17 Nov 2015 16:28:45 4 00000000 Timing, READ DATA SOURCE,00:00:31
Tue 17 Nov 2015 16:28:45 4 00000000 Marking categories used.
Tue 17 Nov 2015 16:28:45 4 00000000 Timing, MARKING CATEGORIES USED,00:00:00
Tue 17 Nov 2015 16:28:45 4 00000000 Updating category status.
Tue 17 Nov 2015 16:28:45 4 00000000 Processing the work file.
Tue 17 Nov 2015 16:28:46 4 000002D9 Processing cube 'Footfallv32' at location e:\bi\cube build\footfallv3cube_1.mdc
Tue 17 Nov 2015 16:28:46 4 00000000 Timing, UPDATE CATEGORY AND PROCESS WORK FILE,00:00:01
Tue 17 Nov 2015 16:28:46 4 000002D9 Start metadata update of cube 'Footfallv32'.
Tue 17 Nov 2015 16:28:46 4 00000000 Marking categories needed.
Tue 17 Nov 2015 16:28:46 4 00000000 Updating the PowerCube metadata.
Tue 17 Nov 2015 16:28:46 4 00000000 Updating the PowerCube with currency data.
Tue 17 Nov 2015 16:28:46 4 000002D9 End metadata update of cube 'Footfallv32'. 1055 categories were added to the cube.
Tue 17 Nov 2015 16:28:46 4 00000000 Timing, METADATA,00:00:00
Tue 17 Nov 2015 16:28:46 4 000002D9 Start update of cube 'Footfallv32'.
Tue 17 Nov 2015 16:28:46 4 000002D9 --- Performing Pass 0 with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:46 4 000002D9 Selected dimension 2 for next pass of partitioning.
Tue 17 Nov 2015 16:28:46 4 00000000 Sorting the work file.
Tue 17 Nov 2015 16:28:46 4 00000000 Counting category hits.
Tue 17 Nov 2015 16:28:46 4 000002D9 End sorting 645293 records.
Tue 17 Nov 2015 16:28:46 4 000002D9 Start Count and Consolidation with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:46 4 000002D9 End Count and Consolidation with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:46 4 000002D9 Start Write leaving 1054 categories remaining.
Tue 17 Nov 2015 16:28:46 4 00000000 Updating the PowerCube data.
Tue 17 Nov 2015 16:28:46 4 00000000 Updating the PowerCube data.
Tue 17 Nov 2015 16:28:47 4 00000000 Performing DataBase Commit at record number 500000.
Tue 17 Nov 2015 16:28:47 4 00000000 Performing DataBase Commit at record number 645294.
Tue 17 Nov 2015 16:28:47 4 000002D9 End Write leaving 1054 categories remaining..
Tue 17 Nov 2015 16:28:47 4 00000000 Timing, CUBE UPDATE,00:00:01
Tue 17 Nov 2015 16:28:47 4 000002D9 --- Performing Pass 1 with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:47 4 000002D9 Selected dimension 1 for next pass of partitioning.
Tue 17 Nov 2015 16:28:47 4 00000000 Counting category hits.
Tue 17 Nov 2015 16:28:47 4 000002D9 End sorting 645293 records.
Tue 17 Nov 2015 16:28:47 4 000002D9 Start Count and Consolidation with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:47 4 000002D9 End Count and Consolidation with 2718 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:47 4 000002D9 Start Write leaving 1054 categories remaining.
Tue 17 Nov 2015 16:28:47 4 00000000 Updating the PowerCube data.
Tue 17 Nov 2015 16:28:47 4 00000000 Updating the PowerCube data.
Tue 17 Nov 2015 16:28:47 4 00000000 Performing DataBase Commit at record number 2719.
Tue 17 Nov 2015 16:28:47 4 000002D9 End Write leaving 1054 categories remaining..
Tue 17 Nov 2015 16:28:47 4 00000000 Timing, CUBE UPDATE,00:00:00
Tue 17 Nov 2015 16:28:47 4 00000000 Committing PowerCube(s).
Tue 17 Nov 2015 16:28:47 4 00000000 Timing, CUBE COMMIT,00:00:00
Tue 17 Nov 2015 16:28:48 4 00000000 End cube update.
Tue 17 Nov 2015 16:28:48 4 00000000 Timing, TOTAL TIME (CREATE CUBE),00:00:34
Tue 17 Nov 2015 16:28:50 4 00000000 Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Tue 17 Nov 2015 16:28:50 4 00000000 Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Tue 17 Nov 2015 16:28:50 4 00000000 Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151117162850'.
Tue 17 Nov 2015 16:28:50 4 00000000 Creating stub 'f:\bi\cube_live\footfall\footfallv3cube_1.mdc'.
Tue 17 Nov 2015 16:28:50 4 00000000 Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151117162850.ver'.
Tue 17 Nov 2015 16:28:50 4 00000000 (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151117162850'.
Tue 17 Nov 2015 16:28:50 4 00000000
Tue 17 Nov 2015 16:28:52 4 00000000 (TR3819) PowerCube(s) have been deployed successfully. [->OK]
Transformer Tue Nov 17 16:29:25 2015
Transformer(XX.XX.XX.XX) Wed Nov 18 02:01:24 2015
LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252
Transformer Wed Nov 18 02:01:24 2015
Transformer(XX.XX.XX.XX) Wed Nov 18 02:01:24 2015
LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252
Wed 18 Nov 2015 02:01:25 0 00000000 (TR3703) The user ID or password is either missing or invalid. Please re-enter the credentials.
Wed 18 Nov 2015 02:01:25 2 00000000 (TR0116) Transformer can't open the file Footfallv3~1. [->OK]
Wed 18 Nov 2015 02:01:25 4 00000000 Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Wed 18 Nov 2015 02:01:25 4 00000000 Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Wed 18 Nov 2015 02:01:25 4 00000000 Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151118020125'.
Wed 18 Nov 2015 02:01:25 4 00000000 Deleting 'f:\bi\cube_live\footfall\footfallv3cube_1__20151117162850.ver'.
Wed 18 Nov 2015 02:01:25 4 00000000 Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151118020125.ver'.
Wed 18 Nov 2015 02:01:25 4 00000000 (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151118020125'.
Wed 18 Nov 2015 02:01:25 4 00000000
Transformer Wed Nov 18 02:01:25 2015
just a further note, when I say manually I am logged into Transformer, if I try and run the job from BIDS I get the same failed log entry. So I am guessing the issue is within the SSIS part of the process rather than when I have created a job to run it.
Next attempt, ran the cogtr.exe from cmd with the arguments as per above and still not running, so what am I doing wrong??
Try using the -l option to pass credentials to the job. Also, I have found it sometimes helps to add the login to the sign-ons window in the default view.
Also remember to consider the credentials that will be used to run the scheduled task or SSIS job. It is likely not - and should not be - your logon being used. You should test with those credentials once you know you have the proper command line built.
Quote from: mrmo on 18 Nov 2015 05:54:59 AM
Next attempt, ran the cogtr.exe from cmd with the arguments as per above and still not running, so what am I doing wrong??
What happens when you run it from within Transformer? Does it prompt you to provide sign-on info? If so, that's why it is working from
within Transformer and not working from outside Transformer. There is a sign-on window in Transformer where you can embed the sign-on.
Once you do that then try running it from cmd. If it works then it should work from SSIS too because all SSIS should do is to fire up the script at a certain time after your ETL load finishes. You can also create a script with the commands , save it as a .bat file and test it from cmd. Once it starts running then
call that script from SSIS.
Cognostechie. that makes sense, but where is this window where i can embed the credentials?
bdbits, from reading the manual, the login credentials APPEAR to be plane text??? a service account is being used to run the jobs.
cognostechie and I were talking the same thing, it is a window in the default Transformer view. (I think you invoke that under the Edit menu, no access to verify at the moment.)
And yes, if you use them on the command line, they will be plain text. This might be unacceptable, I know, so try adding the credentials to the model first to see if that fixes your problem. If not try adding them to the command line, and if it works then we will know there is a permissions issue.
As bdbits said, the default view of Transformer should show that window and if it does not, then from the 'View' menu, select 'Signons'.
Thanks, will have a look in the morning and see if that fixes the issue.
Something happened, first run I got this as the result.
LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252
Thu 19 Nov 2015 09:28:40 4 00000000 Start cube update.
Thu 19 Nov 2015 09:28:40 4 00000000 Initializing categories.
Thu 19 Nov 2015 09:28:40 4 00000000 Timing, INITIALIZING CATEGORIES,00:00:00
Thu 19 Nov 2015 09:28:40 4 00000000 Start processing data source 'Footfallv3~1'.
Thu 19 Nov 2015 09:28:40 4 00000000 Reading source data.
Thu 19 Nov 2015 09:28:42 2 00000000 QE-DEF-0030 Expression parsing error.
Thu 19 Nov 2015 09:28:42 2 00000000 QE-DEF-0359 The query contains a reference to at least one object '[Output].[Output].[Hour]' that does not exist.
Thu 19 Nov 2015 09:28:42 4 00000000 End processing 0 records from data source 'Footfallv3~1'.
Thu 19 Nov 2015 09:28:42 4 00000000 Timing, READ DATA SOURCE,00:00:02
Thu 19 Nov 2015 09:28:42 4 00000000 End cube update.
Thu 19 Nov 2015 09:28:42 4 00000000 Timing, TOTAL TIME (CREATE CUBE),00:00:02
Thu 19 Nov 2015 09:28:42 2 00000000 (TR0168) Transformer could not open the data source [Footfallv3~1]. [->OK]
Thu 19 Nov 2015 09:28:42 4 00000000 Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Thu 19 Nov 2015 09:28:42 4 00000000 Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Thu 19 Nov 2015 09:28:42 4 00000000 Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119092842'.
Thu 19 Nov 2015 09:28:42 4 00000000 Deleting 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119083052.ver'.
Thu 19 Nov 2015 09:28:42 4 00000000 Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119092842.ver'.
Thu 19 Nov 2015 09:28:42 4 00000000 (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151119092842'.
Thu 19 Nov 2015 09:28:42 4 00000000
Transformer Thu Nov 19 09:28:43 2015
Transformer(XX.XX.XX.XX) Thu Nov 19 09:32:55 2015
and second run I got
Transformer Thu Nov 19 09:34:35 2015
Transformer(XX.XX.XX.XX) Thu Nov 19 09:34:35 2015
LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252
Thu 19 Nov 2015 09:34:35 4 00000000 Start cube update.
Thu 19 Nov 2015 09:34:35 3 000002D9 (TR0787) The locking of cube 'e:\bi\cube build\footfallv3cube_1.mdc' failed. It is probably in use by another application. Select Yes to retry, No to create the cube using a temporary name or Cancel. [->Cancel]
Thu 19 Nov 2015 09:34:36 4 00000000 End cube update.
Thu 19 Nov 2015 09:34:36 4 00000000 Timing, TOTAL TIME (CREATE CUBE),00:00:01
Thu 19 Nov 2015 09:34:36 4 00000000 (TR0102) The process or action was cancelled at your request. [->OK]
Thu 19 Nov 2015 09:34:36 4 00000000 Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Thu 19 Nov 2015 09:34:36 4 00000000 Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Thu 19 Nov 2015 09:34:36 4 00000000 Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119093436'.
Thu 19 Nov 2015 09:34:36 4 00000000 Deleting 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119093257.ver'.
Thu 19 Nov 2015 09:34:36 4 00000000 Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119093436.ver'.
Thu 19 Nov 2015 09:34:36 4 00000000 (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151119093436'.
Thu 19 Nov 2015 09:34:36 4 00000000
Transformer Thu Nov 19 09:34:36 2015
Can anyone offer some guidance as too what may be going wrong now?
Well I have got it working now, I am not quite sure what was wrong as the item it was telling me didn't exist certainly looked like it existed in the Framework Manager package?? But when I deleted it, I got the cube to build and deploy from the command line, and then got it to build and deploy from the SSIS package.
That leaves one last step, to see if the JOB runs ok tomorrow morning.
At least I feel like I have achieved something today!