Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

>>

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

>>

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing options in AS2005

I'm trying to get my hand around these new fangled processing options in SSAS2005 and have read TK Anand's excellent paper on the subject: http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp.

Unfortunately I can't see the wood for the trees so here's a question. Is it possible to selectively remove data from a partition? i.e. Without doing a ProcessFull or ProcessClear

Here's the scenario for which i need this:

We have a cube into which we are loading returns (the nature of these returns is unimportant) submitted from external organizations. Each return can go through a series of statuses:

Submitted Received Accepted Approved Superceded

They want to see data for all 'Approved' returns. That's easy. However, a return becomes 'Superceded' we want to remove it from the cube. Is that possible? I don't want to have to do a full process every night because there is a huge amount of data and our processing window is small.

Thanks

Jamie

You have several ways to handle this problem.

One you can partition the data in your cube down futher. Creating several partitions. If you know range of data might change, create a separate parittion (or few partitions) for it .

Processing less data and processing in parralel should go way faster.

Second.

If you dont have many changes happening into your data. You just need to correct value for some transactions. You can enable Writeback for you measure group. Using Writeback you can "poke" 0 into any cell in multidimensional cube space. For instance you can zero out value of sales for User A on Day D.
Internally Analysis Serivces creates ROLAP partition where it writes corrective values.
You client application needs to be able to handle udates correctly. There are quite a few issues with writing into non-leaf cells.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward,

We were definately thinking of going down the partitions route so its good to get some confirmation of that approach.

Writing back from the client app is a no-no though - for reasons I won't go into.

-Jamie