Hello all,

Being tired of insufficiencies of MDX and difficulties of using DSO I
decided to use DSO from MDX. I wanted to share this scheme although it's a
very trivial one because there might be people who might be in need of that.
What I did:

1- Create a dummy dimension in any database you like. Dimension table might
be a view like: create view dbo.DummyDimensionView as select
'DimensionMember1' as Dim
2- Create a dummy cube which uses that dimension. Fact table might be a view
like: create view dbo.DummyCubeView as select 'DimensionMember1' as Dim, 1
as DummyMeasure
3- Create a dll which will function as a wrapper for DSO. You can pack
various commands like CreateCube, DropCube, ProcessCube, RefreshDimension,
etc.
4- You can run these functions you wrote using DSO like this:

use library "c:\library\path\for.dll"
with member Measures.FunctionCall as
'DropCube("ServerName","DBName","CubeName")'
select {Measures.FunctionCall} on columns
from Dummy

You can get the return code/message of your function from the measure.
Furthermore, you can write simple stored procedures to wrap this
functionality so you can practically control any OLAP database from SQL
Server.

create procedure dbo.ProcessCube(@servername varchar(50), @olapdb
varchar(50), @cubename varchar(100)) as
begin
declare @outputmsg varchar(100);
declare @dropSQL nvarchar(3000);

set @dropSQL =
'select @outputmsg = cast("[Measures].[FunctionCall]" as varchar(100))
from openrowset(''msolap.2'', ''DataSource=' + @servername + '; user
id=;password=;Initial Catalog=' + @olapdb + ';'',
''with member Measures.FunctionCall as ''''ProcessCube("'
+ @servername + '","' + @olapdb + '","' + @cubename + '")''''
select {Measures.FunctionCall} on columns
from Dummy'') as q';

exec sp_executesql @dropSQL, N'@outputmsg varchar(100) output', @outputmsg
output

if not @outputmsg is null raiserror(@outputmsg,16,1);
end
go

I hope this helps somebody out there.

Chadash