Very Computer

I have create a cube based on an SQL 2000 database.  I am trying to
partition the cube.  I can create the partitions based on the Time
dimension, but when I try to process the cube I get the following error
message

Data source provider error: Incorrect syntax near the keyword
'from'.;42000

This is being done on a Win2k advanced server with SQL 2000 Advanced
Server and Analysis Services

Any help appreciated

Andy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Can you capture the SQL and examine it for clues? You can copy/paste it
from the screen log generated during processing.

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab

ISVs & IT organizations: Find out how DSS Lab can speed your development!

It sounds like the SQL being run by AS might be incorrect.  Take a
look at the SQL statement and see if anything is obviously wrong with
it.

Quote:> I have create a cube based on an SQL 2000 database.  I am trying to
> partition the cube.  I can create the partitions based on the Time
> dimension, but when I try to process the cube I get the following error
> message

> Data source provider error: Incorrect syntax near the keyword
> 'from'.;42000

> This is being done on a Win2k advanced server with SQL 2000 Advanced
> Server and Analysis Services

> Any help appreciated

> Andy

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

After some investigation, I think I have found the problem but not a
solution.  I have a number of cubes in different olap databases that are
partitioned on the time dimension.  All these work fine, because the
time dimension is set to start the year on January 1.

The cube I am having problems with, the time dimension starts the year
in April which is the company's financial year.  If I alter that
dimension to start the year on January 1, the partitioning works
correctly.

I have taken the problem query from Analysis Manager and run it against
the database in query analyser and the query works fine.

I was wondering if anyone knew if this is a known bug, or if there is a
work around

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Andrew,

Maybe I have a workaround for you. Our financial year starts also in
April.
I already wondered that the year level was not calculated correctly
when you use the wizard to create a shared time dimension with the
year starts in April, but that was easy to solve (modify the SQL for
the member and key column).

I also ran in the same problem Andrew describes with processing my
sales partitions. But I was lucky. I have an (extra field in my fact
table which states the fiscal month. This long value state eg. 199901
for April 1999, 200106 for September 2001, 200202 for May 2002 and so
on.

My Sales cube is divided into different partitions for every fiscal
year. Don't use the time dimension year level to filter your
partitions.
In the wizard of the 'Edit Partition' you can filter the years by
clicking the Advanced button and enter the following code:

"Sales"."Fiscal_Month" > 199912 and "Sales"."Fiscal_Month" < 200101
for Fiscal Year 2000
"Sales"."Fiscal_Month" > 200012 and "Sales"."Fiscal_Month" < 200201
for fiscal year 2001

...... And so on....

I hope this work around can be used for your problem too! Please let
me know.

By the way: George Spofford wrote an excellent book (MDX Solutions)!!!
This book helps you a lot understanding the MDX language!

You can always mail me for any tips/questions to make life easier.

Dennis van den Berg

Quote:> After some investigation, I think I have found the problem but not a
> solution.  I have a number of cubes in different olap databases that are
> partitioned on the time dimension.  All these work fine, because the
> time dimension is set to start the year on January 1.

> The cube I am having problems with, the time dimension starts the year
> in April which is the company's financial year.  If I alter that
> dimension to start the year on January 1, the partitioning works
> correctly.

> I have taken the problem query from Analysis Manager and run it against
> the database in query analyser and the query works fine.

> I was wondering if anyone knew if this is a known bug, or if there is a
> work around

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!