Analysis Services task

Question

Analysis Services task

vick12

SSC Eights!

Points: 856
More actions
February 28, 2011 at 7:03 pm

#144887

Hi all,
I have a DTS 2000 package for processing cubes which uses Analysis Processing task. I need to convert this dts package to SSIS 2008.The analysis services cubes are on the 2000 server.
So I was wondering can I use the "Analysis Services Processing task " in SSIS 2008 to process the 2000 cubes. If not what could be an alternative?
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

If you can't process the SQL 2000 cube with the Analysis Services Processing Task, there are alternatives to do the same.

I took the following list from the book Microsoft® SQL Server® 2008 Integration Services Problem–Design–Solution:

* Using the control flow object Analysis Services Processing Task is the most straightforward
approach, and is discussed in the next section. This allows any SSAS objects (that involve
processing) and their processing method to be selected. A few drawbacks are listed in the next
section.
* Although the Analysis Services Execute DDL Task may sound as if it only allows objects to be
modified, created, or deleted, its functionality goes way beyond Data Definition Language
(DDL). This task runs an SSAS XMLA script. XMLA includes the capability not just to run DDL,
but also query and process. Therefore, this task is very useful for SSAS processing, because the
XMLA can be modified in an SSIS package before it is executed.
* Another approach involves using the Execute Process Task to call the ASCMD executable that
comes with SSAS. This executable can run MDX, Data Mining Expressions (DMX), and XMLA,
and also contains some command - line switches to make your processing more dynamic.
The data flow in SSIS includes two destinations: the Dimension Processing destination and the
Partition Processing destination . These allow data directly from the pipeline to be pushed into
either an SSAS dimension or SSAS partition, respectively. Unlike all the other SSIS approaches
discussed here, this approach is the only one where data is pushed directly to SSAS. The
other approaches essentially tell SSAS to start pulling the data from a data source. Like the
other SSIS approaches, this approach is described later in this chapter.
* Finally, the programming API for SSAS, called Analysis Management Objects (AMO), allows the
interface into the SSAS object model and can leverage the processing methods to process any of
the SSAS objects described. To use AMO for processing, you must leverage either the Script Task
in the control flow, or the Script Component in the data flow.

This is a direct quote, so I hope I didn't violate some copyright law.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vick12 SSC Eights! Points: 856 More actions · Answer 2

Thanks a lot for your reply. I am thinking of using "Analysis Service processing" task in SSIS 2008 to process analysis services 2000 cubes. But I am getting a connection error. So i just wanted to know if SSIS 2008 support Analysis Services 2000 cubes?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

vick12 (3/1/2011)
Thanks a lot for your reply. I am thinking of using "Analysis Service processing" task in SSIS 2008 to process analysis services 2000 cubes. But I am getting a connection error. So i just wanted to know if SSIS 2008 support Analysis Services 2000 cubes?

What is the error that you get?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vick12 SSC Eights! Points: 856 More actions · Answer 4

I am sucessfull when I try to connect to the Analysis services 2005 cubes in SSIS 2008, and it also lists the different objects available.

But when I connect to the Analysis services 2000 cubes through SSIS 2008, it says cannot connect and does not list any objects on that server. The error message is "A Connection Cannot be made . Ensure that the server is running."

My understanding is that SSIS 2008 does not support Analysis service 2000 cubes? Please correct me if I am wrong.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

According to various sources, you can't connect with SSIS 2008 to SSAS 2000.

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/0610eafb-85e3-4506-af64-b15e251c4185

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/23f31694-37d8-4a83-8272-30ecc240f733

The suggested alternatives are;

* create a DTS package to process the cube and run this DTS package from SSIS.

* script it out using DSO (http://technet.microsoft.com/en-us/library/ms403369.aspx#DSO_ProcessNewSales)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vick12 SSC Eights! Points: 856 More actions · Answer 6

Thank you for your reply. I have never used DSO, so not familiar with it. Is the script is a VB script? How should I create it in SSIS?

I was thinking of using Script task and use VB.net/C# script and import Microsoft.Analysisservices.DLL in to the script . Do you have any idea of how we can use a script task to first process all the dimensions and then the cubes. I want to use a script task to first process all the dimensions and another tofinally process the cube.

Thanks a lot again.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Sorry, but I have absolutely no experience with SSAS 2000 and certainly not with DSO.

You can follow the article as a starting point, but I'm afraid you'll have to rely on your google fu skills to solve this problem.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP