Trouble with an MDX statement ...

[SNFK]

Hi all. I'm new to the OLAP forum and ashamed of my question but here it is :
given the following statement :

CREATE CUBE Sales

(

DIMENSION Time TYPE TIME,

HIERARCHY [Fiscal],

LEVEL [Fiscal Year] TYPE YEAR,

LEVEL [Fiscal Qtr] TYPE QUARTER,

LEVEL [Fiscal Month] TYPE MONTH OPTIONS (SORTBYKEY, UNIQUE),

HIERARCHY [Calendar],

LEVEL [Calendar Year] TYPE YEAR,

LEVEL [Calendar Month] TYPE MONTH,

DIMENSION Products,

LEVEL [All Products] TYPE ALL,

LEVEL Category,

LEVEL [Sub Category],

LEVEL [Product Name],

DIMENSION Geography,

LEVEL [Whole World] TYPE ALL,

LEVEL Region,

LEVEL Country,

LEVEL City,

MEASURE [Sales]

FUNCTION SUM

FORMAT 'Currency',

MEASURE [Units Sold]

FUNCTION SUM

TYPE DBTYPE_UI4

) to SQL Server 7.0, it returns error :
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near 'CUBE'.

Could anyone tell me what's wrong with the statement definition because i don't figure it out at all ...
Thanks

 

[issahar]

Dear SNFK!
Welcome to our forum.
How do you run your MDX query?
Which object do you use and which method of this object?

My best regards, Issahar

 

[SNFK]

Thank you for your answer and questions. I run my MDX query in the query analyser of MS SQL Server 7.0 on a remote OLAP Server and there shouldn't be any problem normally. I got a single hypothesis about the reason of the mistake : My instance of SQL Server 7.0 client happens to be in french and the OLAP Server is in english but I don't think it's important at all ...
Anyway, thank you for your concern
SNFK

 

[issahar]

Dear SNFK!
1 How do you run your MDX Query on query analyser ?
I think you are using a not correct tool...
I think it is not a tool to run these type of queries
at all. I never heard that somebody did this before...
I am not sure but maybe you confusing sql server 7 and sql server 2000...I really don't know if the sql server 2000 query analyzer supports MDX queries....

2.I run such types of mdx query by using DSO command interface, e.g. next command will add Calculated member to the cube "mycube"

in VB:
Dim dsoCube as As DSO.MDStore
Set dsoCube = dsoDB.MDStores("MyCube&quot

Dim dsoCalculatedMember As DSO.Command
Set dsoCalculatedMember = dsoCube.Commands.AddNew("MyCalc&quot

dsoCalculatedMember.CommandType = cmdCreateMember

'set the MDX statement that defines the calculated member
dsoCalculatedMember.Statement = "CREATE MEMBER MyCube.Measures.[MyCalc] AS '#WRITE HERE YOUR CALCULATED MEMBER FORMULA#'"

dsoCube.Update


Please, notify me if it helped your to solve your problem
My best regards,
Issahar