Optimizing MS OLAP Cube build time and response time

Hi,
Can someone highlight some of the common methods to improve and optimize the CUBE BUILD TIME and RESPONSE TIME using MS OLAP Services with SQL Server?
Regards,
Omkarwhat kindof dataset are you trying to load?

never use ROLAP, unless you have to.

do analysis services on a dedicated server.

www.sqlserverperformance.com

a lot of good tips are there..

openxml to build a report

Trying to build a report for XML data stored in a table... Employee.EmployeeUDF has six fields...I need to be able to report on all the data for the table....any way to do this?

So far, I have found a plethora of information that will easily allow me to return one row of the table...like below...but can't get more than that...

DECLARE @.idoc int
DECLARE @.doc varchar(1000)

--The line remarked line below will select the udf xml values from the Employee table
SELECT @.doc = EmployeeUDF FROM Employee

--The following is an example of the getting the udf_date_tamex info from the xml
--SELECT @.doc = ' <udf>
<udf_text_tam>595297022</udf_text_tam>
<udf_date_tamex>2009-12-20</udf_date_tamex>
<udf_text_sher>2547793</udf_text_sher>
<udf_date_sherex>2010-02-15</udf_date_sherex>
<udf_text_helth> 772469 </udf_text_helth>
<udf_date_expriration>2008-04-28</udf_date_expriration>
</udf>'

-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/udf',2)
--specify the fields you wish to return by specifying the tag and datatype
WITH (udf_date_tamex datetime)

EXEC sp_xml_removedocument @.idoc

Thanks....
Jason

If you are using SQL Server 2000, you will have to wrap the above into a cursor that loops over every row.

If you are using SQL Server 2005, you should use the nodes() method in the following way:

select n.value('f_text_tam[1]', 'bigint'), n.value('udf_date_tamex[1]','datetime'), ....
from Employee cross apply EmployeeUDF.nodes('/udf') as E(n)

Best regards
Michael

|||

Thanks Michael. The cursor solution is the one. Should have specified 2000. It also reminds me to request 2005 install tomorrow...

J.