run MDX query in SQL Server Agent issue

Hello,

We have 'Create global cube' MDX query running fine in Management studio, but it can not run in job agent, error massage:
[136] Job testcube reported: Microsoft.AnalysisServices.Xmla.XmlaException: CREATE GLOBAL CUBE statement. FILENAME|C:\dw\cube\monthly\mycube.cub|DDL|<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Create AllowOverwrite="true"><ObjectDefinition><Database><ID>mycube</ID>
<Name>mycube</Name>
<Cubes><Cube><ID>mycube</ID><Name>mycube</Name>
<ScriptErrorHandlingMode>IgnoreAll</ScriptErrorHandlingMode>
<MeasureGroups><MeasureGroup>
<ID>Fact mycube</ID><Name>Fact mycube</Name>
<Source xsi:type="MeasureGroupBinding"><DataSourceID>mycube</DataSourceID><CubeID>mycube</CubeID><MeasureGroupID>Fact mycube</MeasureGroupID><Persistence>All</Persistence></Source><IgnoreUnrelatedDimensions>true</IgnoreUnrelatedDimensions><Measures><Measure><ID>Count Orders</ID><Name>Count Orders</Name><Visible>true</Visible><AggregateFunction>Sum</Aggregat

Anyone could help with? any suggestions?

Thanks in advance.

Is this the entire error message? And can you post your MDX? How is your job set up that calls this?

Thanks,

~Shari

|||Thanks for the response.

Following are the job step set up.
We created a new job and new step.
step name:testcube
Type: SQL Server Analysis Services Query
Run as: SQL Agent Service Account
server: my_server_name
database: mycube
commond:
CREATE GLOBAL CUBE mycube
Storage 'C:\DW\cube\monthly\mycube.cub'
FROM [mycube]
(
MEASURE [mycube].[Count Orders],
DIMENSION [mycube].[All Languages],
DIMENSION [mycube].[All Order Methods],
DIMENSION [mycube].[All Periods],
DIMENSION [mycube].[All Products],
DIMENSION [mycube].[All Reasons]
)

The message is entire error message which I can see, but seems already truncated.

Am I missing something?

Thanks.
|||

The error might be caused by lack of permissions on the Analysis Services server with the SQL Agent Service account.

Try to temporarily make SQL Agent Service an administrator for Analysis Services (start SQL Management Studio, right click on the AS server, Properties, Security tab). If that fixes the error, then you'll need to create (or edit) a role that allows querying the cube and has the SQL Agent Service as member.

|||Thank you very much Adrian for the suggestion.

We add administrator to Analysis Services, but the error is still the same.

What else I can try?

Thanks.
|||The error must be hidden in the log file for your job (process). It appears that the real problem is hidden deeper in the error.|||

Try running the create global cube ... MDX from within the Management Studio, cube you are referring to. Let me know if the cube gets built without error.

Thanks,

Shari

|||Thanks Shari for the reply.

The MDX "create global cube" query works fine in Management Studio, and it is created cub file.
The log file contents in SQL Server Agent are same as log file viewer.

What else I can check and try?

Thanks a lot.

|||Hi SQLexperts,

We created a domain\sqladmin user on the sql server, granted all privileges to this user and added this user to Analysis Service user group, but we still get the same error. We stuck on this problem a while, anyone could give us some suggestions? or another way to work around?

Any one have successed run MDX on SQL Agent, I would be grateful for that information.
Appreciate the help.
|||

Another way that I create the global cubes is through a SSIS package. Are you familiar with creating packages? I run the global build each night to create "fresh" files for offline use.

I am wondering now though if it is a permission issue, maybe one of the services is runniing locally but needs Network access and should be run as a Network Service.

~Shari

|||We still not solve this issue yet.
I have tried SSIS, no luck.
Which control flow you use? Analysis Service Execute DDL task or Processing task?

Could you kindly give me direction?

Appreciate any help.

|||

OLAP_user ... email me directly

hopefully you copied my email address already

|||Hello Sharil,

Unfortunately I do not know where can copy your email.

Thanks.

|||

When you tested your create cube statement from SSMS, were you logged in as the domain\sqladmin account? I find that logging in as the service account is sometimes the easiest way to track down some of these issues when a taks works for me, but fails when it is scheduled.

Aslo, have you checked that this user has access to the 'C:\DW\cube\monthly' folders?

|||

We use the following steps in the package to create nightly refreshes of the global cubes"

1. Execute SQL task - this has MDX that creates a MDX statement for each sales rep.

2. For Each loop - loops through each sales rep's MDX to create a single global file per rep

It is a pretty simple package. And if you are just creating one static global cube then you wouldn't need the ForEach loop. You would just need Execute SQL Task. And some notification as far as success or failure.

Does this help?

~Shari