Hi,

I've found a strange thing when you drillthrough on a write-enabled cube.
As far as I can see, Anaysis Services is performing two queries to get the
drillthrough results: one on the tables I specify in the drillthrough
definition
(= the underlying base tables) and one on the WriteBack table.
It is the second one that's failing. As you might now, when you create the
write-back table, Analysis Services, does not use the original column names,
but adds '_Lx' at the end (for dimension columns) or 'SUM_' at the beginning
(for measures). When performing the second query however, Analysis
Services tries to get the writeback data with the original column names.
This means that when we do a drillthrough, we see the orginal values and not
the aggregations with the write-back values.

Can somebody explain me why Analysis Services is trying to use the original
column names instead of the ones used in the write-back table? Is there
a way to solve this problem, because every time we perform a drill through
we get an error message in the Event Log?



We use SQL2000 with sp2 installed (I also tried SP3a on a test machine,
but it turned out to be the same).

Below you'll find also both procedure calls Analysis Services makes to the
SQL Server:

First Query (to the underlying base tables): SCM_VP_VersionDetails is the
fact table
declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P1 int,@P2 nvarchar(128),@P3 varchar(50),@P4
int', N'SELECT "dbo"."SCM_vw_VP_Customer"."Name" As "CustomerName",
"dbo"."SCM_vw_VP_Material"."ProductGroup",
"dbo"."SCM_vw_VP_Material"."Model", "dbo"."SCM_vw_VP_Material"."Material",
"dbo"."SCM_vw_VP_Material"."Generation", "dbo"."SCM_VP_Simulation"."Name",
"dbo"."SCM_VP_SimulationVersion"."VersionNumbe r",
"dbo"."SCM_vw_VP_Calendar"."Year", "dbo"."SCM_vw_VP_Calendar"."Quarter",
"dbo"."SCM_vw_VP_Calendar"."MonthOrder" As "Month",
"dbo"."SCM_vw_VP_Calendar"."Week",
"dbo"."SCM_VP_VersionDetails"."ESPincVATLC",
"dbo"."SCM_VP_VersionDetails"."VATPerc",
"dbo"."SCM_VP_VersionDetails"."RatetoEuro",
"dbo"."SCM_VP_VersionDetails"."ITGPerc",
"dbo"."SCM_VP_VersionDetails"."ChannelPerc",
"dbo"."SCM_VP_VersionDetails"."EMtoSM",
"dbo"."SCM_VP_VersionDetails"."ConfigurationCostIn terSony",
"dbo"."SCM_VP_VersionDetails"."InboundLogisticCost ",
"dbo"."SCM_VP_VersionDetails"."EMWarrantyProvision ",
"dbo"."SCM_VP_VersionDetails"."SIFcst",
"dbo"."SCM_VP_VersionDetails"."INFcst",
"dbo"."SCM_VP_VersionDetails"."MAFcst",
"dbo"."SCM_VP_VersionDetails"."SOFcst",
"dbo"."SCM_VP_VersionDetails"."ConfigurationCostNo nSony",
"dbo"."SCM_VP_VersionDetails"."CallCenterCost" ,
"dbo"."SCM_VP_VersionDetails"."SMstockCompensation ",
"dbo"."SCM_VP_VersionDetails"."Cogs" FROM "dbo"."SCM_VP_VersionDetails",
"dbo"."SCM_VP_SimulationVersion", "dbo"."SCM_VP_Simulation",
"dbo"."SCM_vw_VP_Material", "dbo"."SCM_vw_VP_Calendar",
"dbo"."SCM_vw_VP_Customer" WHERE
(("dbo"."SCM_vw_VP_Customer"."FK_Customer_ID"="dbo "."SCM_VP_VersionDetails".
"FK_Customer_ID") AND
("dbo"."SCM_VP_Simulation"."ID"="dbo"."SCM_VP_Simu lationVersion"."FK_Simulat
ion_ID") AND
("dbo"."SCM_VP_VersionDetails"."FK_SimulationVersi on_ID"="dbo"."SCM_VP_Simul
ationVersion"."ID") AND
("dbo"."SCM_VP_VersionDetails"."Week"="dbo"."SCM_v w_VP_Calendar"."YearWeek")
AND
("dbo"."SCM_vw_VP_Material"."FK_Material_ID"="dbo" ."SCM_VP_VersionDetails"."
FK_Material_ID")) AND ("dbo"."SCM_VP_SimulationVersion"."VersionNumber"= @P1)
AND ("dbo"."SCM_VP_Simulation"."Name"=@P2) AND
("dbo"."SCM_vw_VP_Material"."ProductGroup"=@P3) AND
("dbo"."SCM_vw_VP_Calendar"."Year"=@P4)', 1, N'Desktop Prod', 'DESP', 2003
select @P1

Second Query (to the write-back table): SCM_VP_WriteBack is the Write-back
table
declare @P1 int
set @P1=NULL
exec sp_prepexec @P1 output, N'@P1 int,@P2 nchar(128),@P3 varchar(50),@P4
int', N'SELECT "dbo"."SCM_vw_VP_Customer"."Name" As "CustomerName",
"dbo"."SCM_vw_VP_Material"."ProductGroup",
"dbo"."SCM_vw_VP_Material"."Model", "dbo"."SCM_vw_VP_Material"."Material",
"dbo"."SCM_vw_VP_Material"."Generation", "dbo"."SCM_VP_Simulation"."Name",
"dbo"."SCM_VP_SimulationVersion"."VersionNumbe r",
"dbo"."SCM_vw_VP_Calendar"."Year", "dbo"."SCM_vw_VP_Calendar"."Quarter",
"dbo"."SCM_vw_VP_Calendar"."MonthOrder" As "Month",
"dbo"."SCM_vw_VP_Calendar"."Week", "SCM_VP_WriteBack"."ESPincVATLC",
"SCM_VP_WriteBack"."VATPerc", "SCM_VP_WriteBack"."RatetoEuro",
"SCM_VP_WriteBack"."ITGPerc", "SCM_VP_WriteBack"."ChannelPerc",
"SCM_VP_WriteBack"."EMtoSM",
"SCM_VP_WriteBack"."ConfigurationCostInterSony ",
"SCM_VP_WriteBack"."InboundLogisticCost",
"SCM_VP_WriteBack"."EMWarrantyProvision", "SCM_VP_WriteBack"."SIFcst",
"SCM_VP_WriteBack"."INFcst", "SCM_VP_WriteBack"."MAFcst",
"SCM_VP_WriteBack"."SOFcst", "SCM_VP_WriteBack"."ConfigurationCostNonSony",
"SCM_VP_WriteBack"."CallCenterCost",
"SCM_VP_WriteBack"."SMstockCompensation", "SCM_VP_WriteBack"."Cogs" FROM
"SCM_VP_WriteBack", "dbo"."SCM_vw_VP_Customer", "dbo"."SCM_VP_Simulation",
"dbo"."SCM_VP_SimulationVersion", "dbo"."SCM_vw_VP_Calendar",
"dbo"."SCM_vw_VP_Material" WHERE
(("dbo"."SCM_vw_VP_Customer"."FK_Customer_ID"="SCM _VP_WriteBack"."FK_Custome
r_ID") AND
("dbo"."SCM_VP_Simulation"."ID"="dbo"."SCM_VP_Simu lationVersion"."FK_Simulat
ion_ID") AND
("SCM_VP_WriteBack"."FK_SimulationVersion_ID"="dbo "."SCM_VP_SimulationVersio
n"."ID") AND
("SCM_VP_WriteBack"."Week"="dbo"."SCM_vw_VP_Calend ar"."YearWeek") AND
("dbo"."SCM_vw_VP_Material"."FK_Material_ID"="SCM_ VP_WriteBack"."FK_Material
_ID")) AND (VersionNumber_L5=@P1) AND (Name_L4=@P2) AND
(ProductGroup_L7=@P3) AND (Year_L13=@P4)', 1, N'Desktop Prod', 'DESP', 2003
select @P1


Error in the event log:
Source: MSSQLServerOlapService
Event ID: 124
Description: Relational data provider reported error: [Statement(s) could
not be prepared.;42000;Invalid column name 'Week'.;42S22;Invalid column name
'FK_SimulationVersion_ID'.;42S22;Invalid column name
'FK_Material_ID'.;42S22;Invalid column name 'FK_Customer_ID'.;42S22;Invalid
column name 'STC'.;42S22;Invalid column name 'SOFcst'.;42S22;Invalid column
name 'MAFcst'.;42S22;Invalid column name 'INFcst'.;42S22;Invalid column name
'SIFcst'.;42S22;Invalid column name 'EMtoSM'.;42S22;Invalid column name
'ChannelPerc'.;42S22;Invalid column name 'ITGPerc'.;42S22;Invalid column
name 'RatetoEuro'.;42S22;Invalid column name 'VATPerc'.;42S22;Invalid column
name 'ESPincVATLC'.;42S22].



Regards,

Luc Wouters
Sony, ITE

Hi Luc,

I know that this is not the answer you want to hear but I thought that I
would share it with you anyway. We created a complete sample of how to use
writeback on the foodmart database. This could clear some things up for you
I hope.

Sample: Analysis Services Writeback Sample WGID:223
ID: 299797.KB.EN-US
http://support.microsoft.com/default...b;en-us;299797

samplefiles are referenced in the article.

HTH,
Bas