cube is not refreshing from SSIS Execute process task

[orbair]

HI all

I try to run from SSIS a command that refresh, deploys and publish my cube. the process task runs successful but my report is not refreshed with new data.

-n2 -s -g -j  "C:\Users\sCognos\Documents\Transformer\Models\PartnerWOCountry.pyj"

Can you help me how should I do this? Even in log file I cannot see that something happened when I scheduled this task.

thank you

[bdbits]

Is the SSIS job running, and just the step that calls Transformer is failing? Or the SSIS job is not running at all?

What happens if you run the command from a command line while logged in as whatever credentials the scheduled SSIS job is using?

[orbair]

Hi bdbits

The SSIS job runs successfully just I cannot see my report is updated after. I am new to this, I don't know how to run the command from command line.

thank you for help

[bdbits]

I am making as assumption here that the command line is being executed from within an SSIS package. Depending on how it was built, it is possible for an SSIS job to appear to execute successfully, but the actual transformer job could still be failing. So the first thing is to find out whether the cube itself has actually been rebuilt.

You have a partial command line in your original post. You need the full command line which should be starting with cogtr, which is the transformer executable. Then you need to log in as the user under which the job is being executed, open a command prompt window (cmd.exe), and paste or type in the command line and run it, and observe what happens. You can check the logs that are generated when you build the cube, which will give you detailed information on the cube build, deploy, etc.

You could also create a report based on your cube, then create a Layout Calculation using the following expression: CubeDataUpdatedOn([Your_Cube].[Some Dimension])
Note that [Some Dimension] should be pointed at the top level of the dimension and not a level or member.

[orbair]

Hi bdbits,

Yes, the command lines running from SSIS. The commands are

Executable: C:\Program Files (x86)\ibm\cognos\c10\bin\cogtr.exe
Arguments: -n2 -s -g "D:\BI\Cube_user\Model\PartnerWOCountry.pyj".

The thing is when I run from SSIS the cube builds and deploy successful (I can see in the log file and my reports are refreshing). But I have scheduled this SSIS package using SQL Server Agent, and that way however I receive the email that everything runs successfully there is nothing in my log file, and no data were refreshed. So I assume the problem is with scheduling. When I schedule a job, do I need to do with the same user ID what I have in cognos?  So my schedule task can run my ssis package, but cannot create my cube.

Thank you for your help

[bdbits]

I am fairly sure you have a security context issue. If you log in and run the job yourself, it is running under your own logon. When it is running out of SQL Server Agent, it is likely running under another logon, probably the one SQL Server Agent service is using. It would be like logging in using that logon instead of yours, and running the command line. So you need to ensure that the SQL Server Agent service logon has access to everything needed for the build.