How to run MDX query as scheduled job?

Hi,

Is there any way to run MDX

query as scheduled job on SQL Server 2005?

We need create local cubes by

weekly or monthly. How can we put “Create global cube” MDX query into scheduled

job?

Any info would be greatly

appreciated.

Thanks.

Yes. Create a new SQL Server Agent job and add a step with the step type of SQL Server Analysis Services Query. For this type of step, you need to specify the SSAS server to connect to, the database to run the query within, and the text for the query itself.

Should do exactly what you want...

HTH,

Dave Fackler

|||

Hi Dave,

Thanks for the quick replay.

Could you recommend any article or books that I can take look?

Thanks.

|||

I'm assuming you are asking about references for SSIS? If that is the case, check out Microsoft SQL Server 2005 Integration Services (SQL Server Series) by Kirk Haselden, Professional SQL Server 2005 Integration Services (Programmer to Programmer) by Brian Knight et al, and The Rational Guide to Extending SSIS 2005 with Script (Rational Guides) (Rational Guides) by Donald Farmer. All great books related to SSIS that should get you started...

HTH,

Dave Fackler

|||Thank you very much Dave for answered my question. It's helpful.

How to return sets by filtering those tuples with null values in a MDX statement?

Hi, dear friends,

How can we return the result by filtering tuples with null values? (e.g. I want to return the result for tuples specified by dimention DimA, measures B,C. therefore I want to filter the result with nulls values of B,C for DimA).

Hope it is clear for your help.

And I am looking forward to hearing from you shortly for your help.

Thanks a lot.

With kind regards,

Yours sincerely,

It depends what you mean by "filter" - do you want the null values included or excluded?

And what does B,C mean? Do you mean if either is null or both?

The following is one solution if you want to return members from DimA where both measures are empty (null).

Filter(DimA.members, IsEmtpy(Measures.B) AND IsEmpty(Measures.C)

but I would expect somthing like this could be faster as it could use block computation instead of cell-by-cell evaluation.

INTERSECT(NonEmpty(DimA.Members, Measures.B),NonEmpty(DimA.members, Measures.C))

|||

Hi, Darren,

Thanks a lot for your help.

With kind regards,

Yours sincerely,

How to return sets by filtering those tuples with null values in a MDX statement?

Hi, dear friends,

How can we return the result by filtering tuples with null values? (e.g. I want to return the result for tuples specified by dimention DimA, measures B,C. therefore I want to filter the result with nulls values of B,C for DimA).

Hope it is clear for your help.

And I am looking forward to hearing from you shortly for your help.

Thanks a lot.

With kind regards,

Yours sincerely,

It depends what you mean by "filter" - do you want the null values included or excluded?

And what does B,C mean? Do you mean if either is null or both?

The following is one solution if you want to return members from DimA where both measures are empty (null).

Filter(DimA.members, IsEmtpy(Measures.B) AND IsEmpty(Measures.C)

but I would expect somthing like this could be faster as it could use block computation instead of cell-by-cell evaluation.

INTERSECT(NonEmpty(DimA.Members, Measures.B),NonEmpty(DimA.members, Measures.C))

|||

Hi, Darren,

Thanks a lot for your help.

With kind regards,

Yours sincerely,

How to retrieve Member Properties in vb.net 2003 from MS AS 2000

Hi All,

We are facing one problem while retrieving member properties from MSAS 2000 using MDX query in VB.Net. we are able to retrieve the member properties like [Account].[All Account].[Net Income].[Total Expense] but i need the value should come like in this format [Account].[All Account].&[5000].&[5100] which is the member key value.

We are using ADOMD to connect MSOLAP and passing MDX query to retrieve the member properties. The below code which i used to retrieve in VB.Net.

Current Environment is VB.Net 2003 Framework, ADOMD and ADODB.

For intDimCount = 0 To cst.Axes(0).DimensionCount - 1

For intMem = 0 To cst.Axes(0).Positions.Count - 1

arrMem.Add(cst.Axes(0).Positions(intMem).Members(intDimCount).Caption)

arrMem.Add(cst.Axes(0).Positions(intMem).Members(intDimCount).UniqueName)

arrMem.Add(cst.Axes(0).Positions(intMem).Members(intDimCount).Name)

Next

Next

Could you please anyone to advise the same to retrieve member poperties( Name & Unique Name) like this [Account].[All Account].&[5000].&[5100].

Thanks,

Vishwesh

You can control the way the unique name is returned by adding a parameter to the connection string. This KB article explains the details http://support.microsoft.com/default.aspx/kb/304337

In AS2000 I think there was also a registry key setting, but the connection string option is probably better as then your application will be in control of how the unique names are formatted.

|||

Hi Darren,

I am Very thakful to u r reply.It is really helped to solve our Problem.