Syntax Error in MDX Statement.....
-
I have a cube with drillthrough setup on it.
It has 6 different demensions, one being a Time dimension named PeriodEnd. The PeriodEnd dimension has two levels Year and Month.
When I perform MDX drillthrough queries on the [PeriodEnd] Year and/or Month levels I get the following error:
"Incorrect syntax near the keyword 'from'.;42000".
Querying on the All PeriodEnd works fine. I get no errors when quering on any other dimensions.
This query errors out:
DRILLTHROUGH SELECT FROM [MY_CUBE] WHERE ([Financial Class].[All Financial Class].[CHAMPUS], [PeriodEnd].[All PeriodEnd].[2005].[October], [Age Bucket].[All Age Bucket].[b. 0-30])
This query does not:
DRILLTHROUGH SELECT FROM [MY_CUBE] WHERE ([Financial Class].[All Financial Class].[CHAMPUS], [PeriodEnd].[All PeriodEnd], [Age Bucket].[All Age Bucket].[b. 0-30])
TIA!!!!!
-
Interesting....
This is the first query sent to SQL Server from the drillthrough....
SET FMTONLY ON
select DatePart(day, "dbo"."FHIS_AR_period_end"."period_end_date",
"dbo"."FHIS_AR_period_end"."financial_class"
from "dbo"."FHIS_AR_period_end"
SET FMTONLY OFF
Now I see the syntax error....any ideas how to fix? Should I call MS support?
-
it doesn't close the function call? interesting!
Just out of interest, what are the key and name settings for your month level in the t dimension?
Steve.
-
I've been able to recreate this error (on another dev machine with the pubs db) and have found that it only happens when the fact table is a view. hmmmmmmm.
Steps to recreate using SQL Server pubs database as datasource:
Create a view in pubs:
CREATE VIEW dbo.vw_pub_test
AS
SELECT dbo.stores.stor_name, dbo.titles.price, dbo.sales.ord_date, dbo.titles.title
FROM dbo.sales
INNER JOIN dbo.stores ON dbo.sales.stor_id = dbo.stores.stor_id
INNER JOIN dbo.titles ON dbo.sales.title_id = dbo.titles.title_id
GO
Create a cube in Analysis Server for a pubs datasource:
Fact Table = vw_pub_test
Dimensions: Stor Name, Title, Ord Date (Year, Month)
Measure: Price
Storage = MOLAP
Enable Drillthrough: I selected all fields in the view for the drill through
I then use a pivot table and a drillthrough macro in Excel that will issue the query:
(spreadsheet logic similar to this one http://www.mosha.com/msolap/samples/Drillthrough.xls)
Drillthrough maxrows 1000 Select {[Stor Name].[All Stor Name].[Bookbeat]} on 0, {[Ord Date].[All Ord Date].[1994]} on 1 From [pub_test]
SQL Profiler says this query was sent:
SET FMTONLY ON select "dbo"."vw_pub_test"."stor_name",DatePart(day, "dbo"."vw_pub_test"."ord_date" from "dbo"."vw_pub_test" SET FMTONLY OFF
If I use the base tables instead of the view it works fine, but I really need to use the view as the fact table.
-
Just recreated as per yuor specs, drillthrough from within AM works fine and using the spreadsheet/macro as supplied by MP I can't get it to fault??
SET FMTONLY ON select "dbo"."vw_pub_test"."stor_name",DatePart(year,"dbo"."vw_pub_test"."ord_date") from "dbo"."vw_pub_test" SET FMTONLY OFF
Are you running SP3 on AS?
Steve.
-
Just looked at your metadata query versus mine, why is your time dimension doing a datepart of 'day' for the year level keys?
What is the key and name formula for the year and month levels in your time dim?
Steve.
-
Steve.... thanks for taking the time to look at this.
I am running Analysis Services 2000 SP3 (msmdsrv.exe 8.0.760)
Dimension: Ord Date (October 1 begins a new fiscal year fiscal year - I didn't mention that before...ooops.)
Level Year:
Member Key Column:DatePart(year,Case when (DatePart(month, "dbo"."vw_pub_test"."ord_date") < 10 or (DatePart(month, "dbo"."vw_pub_test"."ord_date") = 10 and DatePart(day, "dbo"."vw_pub_test"."ord_date") < 1)) then "dbo"."vw_pub_test"."ord_date" Else DateAdd(year, 1, "dbo"."vw_pub_test"."ord_date") End )
Member Name Column: DatePart(year,Case when (DatePart(month, "dbo"."vw_pub_test"."ord_date") < 10 or (DatePart(month, "dbo"."vw_pub_test"."ord_date") = 10 and DatePart(day, "dbo"."vw_pub_test"."ord_date") < 1)) then "dbo"."vw_pub_test"."ord_date" Else DateAdd(year, 1, "dbo"."vw_pub_test"."ord_date") End )
Level Month:
Member Key Column: ( DatePart(year,"dbo"."vw_pub_test"."ord_date") * 100) + DatePart(month,"dbo"."vw_pub_test"."ord_date")
Member Name Column:convert(CHAR, DateName(month,"dbo"."vw_pub_test"."ord_date"))
Could part of the problem be specifing in the Time dimension wizard that the year starts on October 1???
I'm currently working around this by adding seperate year and month columns to my fact table, which gets around the formula issue.
-
It could have to do with the formula but I'm not sure why.
A really quick thing, unless prototyping I would *always* create a seperate Time dimensional table, mainly because I hate having a time dim with missing values as you get when building it from the fact. Also means you can process it seperately, and I typically like to use an integer as the key in the vain hope that all joins will be better performing
And lastly means that you should never have to have a formula in the name or key values for the dim levels as all this logic has been done in the base table build. Plus means you can add holidays, yo can have many *calendars* within the one table eg fiscal year for US, CAN, AUS plus standard gegorian calendar etc etc etc.If you're looking for some quick/easy ways to do this, either write up a quick excel spreadsheet and copy and paste the TSQL in the spreadsheet to create some inserts, or alternatively, there are sample TSQL scripts that come with the BI accelelrator that will build time dim/s. I can post these up if required.
Steve.
-
Thanks again Steve for all looking at this issue. Today I spent some time redesigning the cube and the datasource. I dumped the view into a table and did build some other dimension tables. My performace (especially on drillthrough) greatly increased and I was able to work around the issue above.
I'm just an OLAP novice, but I *think* I'm starting to learn how some of this warehousing/BI stuff works...
-
i can't remember all ofthe conditions, but i think it centers on having shared dims and unique lowest level members in dims, so if you have these, go to the Tools (?) option and optimize your schema. This should give you an improvment in cube build times. Basically it replaces the key members in the lowest level of the dims with the foreign key from the fact table, if you pre-build your shared dims then process the cube, it should have to go only to the fact table.
have fun!
Steve.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply