Analysis Services

I was at a Microsoft sales presentation last week for the new version
of SQL Server, Yukon. They had an extensive presentation on SQL
Server and Reporting Services but didn't mention Analysis Services.
When I asked about the future of the product the MS Sales rep couldn't
give me any info. When I looked at the big product chart I got at the
presntation, Analysis Services wasn't on it. There was just box
refering to Reporting Services and XML. Does anyone know what or if
there is a future for Analysis Services?BruceGilpin (brucegilpin@.ameritech.net) writes:
> I was at a Microsoft sales presentation last week for the new version
> of SQL Server, Yukon. They had an extensive presentation on SQL
> Server and Reporting Services but didn't mention Analysis Services.
> When I asked about the future of the product the MS Sales rep couldn't
> give me any info. When I looked at the big product chart I got at the
> presntation, Analysis Services wasn't on it. There was just box
> refering to Reporting Services and XML. Does anyone know what or if
> there is a future for Analysis Services?

I'm not into Analysis Services myself, but on the Yukon events I have
been, there have been plenty of sessions about Analysis Services, so
I'm confident to say that it has a bright and lively future.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Alternative to T-SQL OpenXML

Hi,

Our application makes extensive use of XML to move data between ASP.NET and the backend SQL Server 2000 database. Lately we have found that the OpenXML statement is becoming a major performance bottleneck.

Could someone suggest a more efficient way of 1) passing data between the web and data tier or 2) alternatives to OpenXml/sp_prepare_document.

Regards
Arjun

Dear Arjun

First, how does your OpenXML code look like? There are some best practises that can have a large performance impact.

Secondly, are you using Service pack 4? That also has some sometimes substatial performance improvements.

Otherwise, I would recommend at looking at the SQLXML 3.0 XML Bulkload object, or a ADO.Net DataSet based approach.

Best regards

Michael

|||

Dear Michael,

Here's a code snippet. This is contained in a stored procedure which accepts the XML data as input. Any suggestions to tune this.

exec sp_xml_preparedocument @.outputDoc OUTPUT, @.strAddXML

Insert into @.Table(txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev )
Select txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev FROM OPENXML(@.outputDoc, 'DocXML/intRow',1)
with
(
txtCategory varchar(50),
intFinYearEnd int,
fltTotalRevenue float,
fltCost float,
fltCostPer float,
fltCommPer float,
fltAprRev float,
fltMayRev float,
fltJunRev float,
fltJulRev float,
fltAugRev float,
fltSepRev float,
fltOctRev float,
fltNovRev float,
fltDecRev float,
fltJanRev float,
fltFebRev float,
fltMarRev float
)

Regards
Arjun

|||

Hi Arjun

Sorry for the late reply but I was travelling.

The OpenXML statement that you show looks ok to me. Have you done some performance testing to see where the time is being spent? During parsing of the sp_xml_preparedocument or the actual OpenXML call?

How large is the XML that you are passing in? Note that SQL Server 2000 versions prior to SP4 used a parser that had some non-linear scaling problems during parsing of larger documents (which has been fixed with SP4). What service pack version are you using?

Best regards

Michael

Alternative to T-SQL OpenXML

Hi,

Our application makes extensive use of XML to move data between ASP.NET and the backend SQL Server 2000 database. Lately we have found that the OpenXML statement is becoming a major performance bottleneck.

Could someone suggest a more efficient way of 1) passing data between the web and data tier or 2) alternatives to OpenXml/sp_prepare_document.

Regards
Arjun

Dear Arjun

First, how does your OpenXML code look like? There are some best practises that can have a large performance impact.

Secondly, are you using Service pack 4? That also has some sometimes substatial performance improvements.

Otherwise, I would recommend at looking at the SQLXML 3.0 XML Bulkload object, or a ADO.Net DataSet based approach.

Best regards

Michael

|||

Dear Michael,

Here's a code snippet. This is contained in a stored procedure which accepts the XML data as input. Any suggestions to tune this.

exec sp_xml_preparedocument @.outputDoc OUTPUT, @.strAddXML

Insert into @.Table(txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev )
Select txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev FROM OPENXML(@.outputDoc, 'DocXML/intRow',1)
with
(
txtCategory varchar(50),
intFinYearEnd int,
fltTotalRevenue float,
fltCost float,
fltCostPer float,
fltCommPer float,
fltAprRev float,
fltMayRev float,
fltJunRev float,
fltJulRev float,
fltAugRev float,
fltSepRev float,
fltOctRev float,
fltNovRev float,
fltDecRev float,
fltJanRev float,
fltFebRev float,
fltMarRev float
)

Regards
Arjun

|||

Hi Arjun

Sorry for the late reply but I was travelling.

The OpenXML statement that you show looks ok to me. Have you done some performance testing to see where the time is being spent? During parsing of the sp_xml_preparedocument or the actual OpenXML call?

How large is the XML that you are passing in? Note that SQL Server 2000 versions prior to SP4 used a parser that had some non-linear scaling problems during parsing of larger documents (which has been fixed with SP4). What service pack version are you using?

Best regards

Michael