Dear all,

We have a big data warehouse of which the ETL is done using T-SQL. I
very much like to do the processing of dimensions and cubes also from
T-SQL. I know I can script the processing in XMLA (I will generated
these scripts using T-SQL based on a metamodel), but I don't know how I
can 'call' these scripts from T-SQL

Does anybody know how to do that?

If there is no way to do it from T-SQL, using xp_cmdshell is also fine.

Thanks a lot!
Paul

There are some examples of calling XMLA from vbscript on Chris
Harringtons site at: http://www.activeinterface.com/thinolap.html, which
should get you started.

If you are using SQL 2005 as you relational engine you could also write
a stored proc in .Net to call AMO to process the cubes.

As a final option you could also look into the proactive caching
features. You could set the cubes up to reprocess themselves when they
detect a change in the source data. This feature is highly configurable
and there are a couple of webcasts on MSDN and Technet on how you can
set it up.

Hope this helps.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144420669.860305.262140 (AT) i40g2000cwc (DOT) googlegroups.com>,
info (AT) vanoordt (DOT) nl says...

Although the simple solution that I hoped for doesn't seem to be
available, I'm certainly grateful for your suggestions.

I also thought about using vbscript with AMO. However I didn´t figure
that out either. It's a pity, or at least a novelty, that database
experts have to go into object programming. (If anybody can point me to
a sample script using AMO to process a cube, I'd be helped a lot.)

In article <1144661708.853465.111710 (AT) t31g2000cwb (DOT) googlegroups.com>,
info (AT) vanoordt (DOT) nl says...
I have taken one of the scripts off Chris's site and pulled out the bare
minimum needed to kick off a process of a database and put the details
in a blog article here:
http://geekswithblogs.net/darrengosb.../xmlaProcess.a
spx

You should really also have a look at Proactive Caching. You can set the
cube up to watch one or more fact tables, wait for a specified amount of
"silence" after it sees changes and then automatically process itself.
Setting all this up is a configuration exercise - no coding involved.

An equivalent AMO routine would look something like the following. But
vbscript will not work AMO is a .Net library, you would need to create
a VB.Net (or C#) console application, add a reference to the
Microsoft.AnalysisServices.dll library and compile the whole thing as an
executable.

ByVal sDatabaseName As String)
Dim svr As New Microsoft.AnalysisServices.Server
svr.Connect(sServerName)
Dim olapDb As Database = svr.Databases.GetByName(sDatabaseName)
olapDb.Process()
svr.Disconnect()
svr.Dispose()
End Sub

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

Below is a C# console app that will fully process an OLAP database. After compiling it, you can call it from the command line or from a SQL script using xp_cmdshell.

Jeff Roughgarden, Ph.D., MCSD, MCDBA

using System;
using Microsoft.AnalysisServices;

namespace ProcessCube
{
class Program
{
static void Main(string[] args)
{
int intArgCount = args.GetUpperBound(0);
if (intArgCount !=1)
{
Console.WriteLine("Error in usage; Correct syntax is: ProcessCube.exe [ServerName] [DatabaseName]");
return;
}
string strServerName = args[0];
string strDBName = args[1];
string strConnection = "Data Source=" + strServerName + "; Catalog=" + strDBName;
DateTime datStart = System.DateTime.Now;

Server oAnalysisServer = new Server();
try
{
oAnalysisServer.Connect(strConnection);
Database oAnalysisServerDB = oAnalysisServer.Databases.GetByName(strDBName);
Console.WriteLine("");
Console.WriteLine("Beginning to process " + strDBName + " database. This can take several minutes.");
oAnalysisServerDB.Process(ProcessType.ProcessFull) ;
TimeSpan tsElapsed = DateTime.Now - datStart;
Console.WriteLine("");
Console.WriteLine("Processing was successful; elapsed time (hh:mm:ss) was " + tsElapsed.ToString().Substring(0,8));
}
catch (Exception e)
{
Console.WriteLine("");
Console.WriteLine("An error occured during cube processing: " + e.Message);
}
finally
{
oAnalysisServer.Disconnect();
oAnalysisServer.Dispose();
}
}
}
}

From http://www.developmentnow.com/g/112_...p-cmdshell.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

Incidentally, all I did was implement Darrren's VB code and embellish it a little. Thanks Darren for getting me going.

From http://www.developmentnow.com/groups...hreadid=733581

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/