dts import agent: dso

Showing posts with label dso. Show all posts

Tuesday, February 14, 2012

DSO Questions

I am using DSO to list all the partitions (> 200) in my cube and then do a
threaded process of these partitions.
Thres questions:
1) I am using a grid to show all the partitions, whether they have been
processed or not and what their Estimated rows are. During population of
this grid (basically just iterating Cube.MDStores[x]), 9 times out of 10
I
get this random divide by zero error. I'm definately not causing this.
This is my loop:
for i:=1 to FCube.MDStores.Count do
begin
FPartition:=FCube.MDStores.Item(i);
FEstimatedRows:=FPartition.EstimatedRows;
sgPartitionList.Cells[0,i]:=FPartition.Name;
if FPartition.State = olapStateCurrent then
sgPartitionList.Cells[1,i]:='Processed'
else
sgPartitionList.Cells[1,i]:='No';
sgPartitionList.Cells[2,i]:=IntToStr(FEstimatedRows);
// Brain fart mentioned below goes here.
end;
Interestingly enough (2AM brain fart), I added this:
if i mod 12 = 0 then
begin
Application.ProcessMessages;
Sleep(500);
Application.ProcessMessages;
end;
to the loop (every 12 iterations, sleep 500 ms after processing all system
messages in the queue) and it works. Any idea why?
2) This same behavior presents itself during partition creation. At 225
partitions (without the message processing and pause), the OLE system kicks
back divide by zero errors or Access Violations. I've added the pause and
ProcessMessages to the creation loop and it's fine, but it worries me. Is
DSO really that flaky or am I missing something?
3) During partition process: I have a multi-threaded approach. I call
CoInitialize (to create a new OLE session because apparently DSO is not
threaded correctly) create a connection to the server, the database, the
cube and the partitions and then call FPartitionObject.Process;
This actually works great, but my question is, sporadically, the processing
just stops. I check the data directory and there won't have been any
updates to the files in several hours (as opposed to minutes when the
processing is working) and the CPU usage will be almost nil. I kill the
application, I also have to restart the OLAP service. There is no pattern
here, sometimes it'll process the whole cube (> 200 partitions), sometimes
it'll die at 50, sometimes 100, sometimes 4. Any Ideas? I've included my
Delphi source for the meat of the thread below.
procedure TPartitionProcessThread.Execute;
var
FServerObject, FDatabaseObject, FCubeObject, FPartitionObject: OleVariant;
begin
FStartTime:=Now;
CoInitialize(nil);
try
try
FSuccess:=False;
while not TryEnterCriticalSection(FConnectSection)
do
Sleep(500);
try
FServerObject:=CreateOLEObject('DSO.Server');
FServerObject.Connect(FServerName);
FDatabaseObject:=FServerObject.MDStores.Item(FDatabaseName);
FCubeObject:=FDatabaseObject.MDStores.Item(FCubeName);
FPartitionObject:=FCubeObject.MDStores.Item(FPartitionName);
finally
LeaveCriticalSection(FConnectSection);
end;
FPartitionObject.Process;
FSuccess:=True;
except
on E: Exception do
begin
FErrorMessage:=E.Message;
FSuccess:=False;
end;
end;
finally
CoUninitialize;
FEndTime:=Now;
end;
end;
Thanks a lot!
-MalcolmOne possible issue is that DSO is written in VB and thus it will have
serious threading issues in this environment. This is why the PPU uses
separate processes (rather than threads) to process objects in parallel.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Malcolm Toon" <mtoon@. no-spam.retailsolutions.com> wrote in message
news:uVqYwLvLFHA.1396@.TK2MSFTNGP10.phx.gbl...
> I am using DSO to list all the partitions (> 200) in my cube and then do a
> threaded process of these partitions.
> Thres questions:
> 1) I am using a grid to show all the partitions, whether they have been
> processed or not and what their Estimated rows are. During population of
> this grid (basically just iterating Cube.MDStores[x]), 9 times out of
10 I
> get this random divide by zero error. I'm definately not causing this.
> This is my loop:
> for i:=1 to FCube.MDStores.Count do
> begin
> FPartition:=FCube.MDStores.Item(i);
> FEstimatedRows:=FPartition.EstimatedRows;
> sgPartitionList.Cells[0,i]:=FPartition.Name;
> if FPartition.State = olapStateCurrent then
> sgPartitionList.Cells[1,i]:='Processed'
> else
> sgPartitionList.Cells[1,i]:='No';
> sgPartitionList.Cells[2,i]:=IntToStr(FEstimatedRows);
> // Brain fart mentioned below goes here.
> end;
> Interestingly enough (2AM brain fart), I added this:
> if i mod 12 = 0 then
> begin
> Application.ProcessMessages;
> Sleep(500);
> Application.ProcessMessages;
> end;
> to the loop (every 12 iterations, sleep 500 ms after processing all system
> messages in the queue) and it works. Any idea why?
>
> 2) This same behavior presents itself during partition creation. At 225
> partitions (without the message processing and pause), the OLE system
kicks
> back divide by zero errors or Access Violations. I've added the pause and
> ProcessMessages to the creation loop and it's fine, but it worries me. Is
> DSO really that flaky or am I missing something?
> 3) During partition process: I have a multi-threaded approach. I call
> CoInitialize (to create a new OLE session because apparently DSO is not
> threaded correctly) create a connection to the server, the database, the
> cube and the partitions and then call FPartitionObject.Process;
> This actually works great, but my question is, sporadically, the
processing
> just stops. I check the data directory and there won't have been any
> updates to the files in several hours (as opposed to minutes when the
> processing is working) and the CPU usage will be almost nil. I kill the
> application, I also have to restart the OLAP service. There is no pattern
> here, sometimes it'll process the whole cube (> 200 partitions), sometimes
> it'll die at 50, sometimes 100, sometimes 4. Any Ideas? I've included my
> Delphi source for the meat of the thread below.
> procedure TPartitionProcessThread.Execute;
> var
> FServerObject, FDatabaseObject, FCubeObject, FPartitionObject:
OleVariant;
> begin
> FStartTime:=Now;
> CoInitialize(nil);
> try
> try
> FSuccess:=False;
> while not TryEnterCriticalSection(FConnectSection)
do
> Sleep(500);
> try
> FServerObject:=CreateOLEObject('DSO.Server');
> FServerObject.Connect(FServerName);
> FDatabaseObject:=FServerObject.MDStores.Item(FDatabaseName);
> FCubeObject:=FDatabaseObject.MDStores.Item(FCubeName);
> FPartitionObject:=FCubeObject.MDStores.Item(FPartitionName);
> finally
> LeaveCriticalSection(FConnectSection);
> end;
> FPartitionObject.Process;
> FSuccess:=True;
> except
> on E: Exception do
> begin
> FErrorMessage:=E.Message;
> FSuccess:=False;
> end;
> end;
> finally
> CoUninitialize;
> FEndTime:=Now;
> end;
> end;
>
> Thanks a lot!
> -Malcolm
>|||What about AS2005? Will it have the same problems? We're still in R&D,
Proof of Concept phase, so we're open to starting with AS2005 when we move
to actual development and production, but will it have the same problem?
Should I start coding our solutions to really be different EXEs? Is there
any alternative to DSO? Is there another API of some sort? Some way to
mimic what DSO does.
Thanks again for the help!
-Malcolm
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> wrote in message
news:%23Go1Kh%23LFHA.3788@.tk2msftngp13.phx.gbl...
> One possible issue is that DSO is written in VB and thus it will have
> serious threading issues in this environment. This is why the PPU uses
> separate processes (rather than threads) to process objects in parallel.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Malcolm Toon" <mtoon@. no-spam.retailsolutions.com> wrote in message
> news:uVqYwLvLFHA.1396@.TK2MSFTNGP10.phx.gbl...
a[vbcol=seagreen]
of[vbcol=seagreen]
I[vbcol=seagreen]
system[vbcol=seagreen]
> kicks
and[vbcol=seagreen]
Is[vbcol=seagreen]
> processing
pattern[vbcol=seagreen]
sometimes[vbcol=seagreen]
my[vbcol=seagreen]
> OleVariant;
>|||SQL 2005's management APIs (AMO) are all written in managed code. They are
totally thread-safe.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Malcolm Toon" <mtoon@. no-spam.retailsolutions.com> wrote in message
news:uo2aJ6%23LFHA.244@.tk2msftngp13.phx.gbl...
> What about AS2005? Will it have the same problems? We're still in R&D,
> Proof of Concept phase, so we're open to starting with AS2005 when we move
> to actual development and production, but will it have the same problem?
> Should I start coding our solutions to really be different EXEs? Is there
> any alternative to DSO? Is there another API of some sort? Some way to
> mimic what DSO does.
> Thanks again for the help!
> -Malcolm
> "Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> wrote in message
> news:%23Go1Kh%23LFHA.3788@.tk2msftngp13.phx.gbl...
> rights.
do[vbcol=seagreen]
> a
been[vbcol=seagreen]
> of
10[vbcol=seagreen]
> I
this.[vbcol=seagreen]
> system
225[vbcol=seagreen]
> and
> Is
not[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
> pattern
> sometimes
included[vbcol=seagreen]
> my
>

DSO properties?

RemoteRepositoryConnectionString
A string property that defines a connection to a remote Analysis Services
repository.
RepositoryConnectionString
A string property that defines the connection to the metadata repository.
what are they really?
are they the cube database or the data source database which is used by Cube?
> RemoteRepositoryConnectionString
> A string property that defines a connection to a remote Analysis Services
> repository.
> RepositoryConnectionString
> A string property that defines the connection to the metadata repository.
> what are they really?
> are they the cube database or the data source database which is used by Cube?
The repository database is the one that holds the cube structures and
metadata. This database is used by Analysis Manager and the DSO library.
It really only comes into play when building and processing cubes and
dimensions.
As someone pointed out in a post recently, you can query cubes without
having this database at all.
There are two different connection strings because when you are on the
server you can access this database using "localhost" or a reference to
"C:\...", depending on whether you are using Access or SQL to hold your
repository. When you access AS remotely using Analysis Manager you need
to connect to the server by its network name, or to the access database
via a network share (which is setup when you install and Analysis
Manager knows about)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||Do you have any examples for these two? I could not find any in 2005 BOL.
Thanks,
Guangming
"Darren Gosbell" wrote:

>
> The repository database is the one that holds the cube structures and
> metadata. This database is used by Analysis Manager and the DSO library.
> It really only comes into play when building and processing cubes and
> dimensions.
> As someone pointed out in a post recently, you can query cubes without
> having this database at all.
> There are two different connection strings because when you are on the
> server you can access this database using "localhost" or a reference to
> "C:\...", depending on whether you are using Access or SQL to hold your
> repository. When you access AS remotely using Analysis Manager you need
> to connect to the server by its network name, or to the access database
> via a network share (which is setup when you install and Analysis
> Manager knows about)
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
|||They come from the AS2K server. Bring up Analysis Manager, right-click on
the server and select "Edit Repository Connection String" or something like
that :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:EC5E30E2-D181-4BA1-AFDD-61C93E0CB04C@.microsoft.com...[vbcol=seagreen]
> Do you have any examples for these two? I could not find any in 2005 BOL.
> Thanks,
> Guangming
> "Darren Gosbell" wrote:

DSO properties?

RemoteRepositoryConnectionString
A string property that defines a connection to a remote Analysis Services
repository.
RepositoryConnectionString
A string property that defines the connection to the metadata repository.
what are they really?
are they the cube database or the data source database which is used by Cube
?> RemoteRepositoryConnectionString[vbcol=s
eagreen]
> A string property that defines a connection to a remote Analysis Services
> repository.
> RepositoryConnectionString
> A string property that defines the connection to the metadata repository.
> what are they really?
> are they the cube database or the data source database which is used by Cube?[/vbc
ol]
The repository database is the one that holds the cube structures and
metadata. This database is used by Analysis Manager and the DSO library.
It really only comes into play when building and processing cubes and
dimensions.
As someone pointed out in a post recently, you can query cubes without
having this database at all.
There are two different connection strings because when you are on the
server you can access this database using "localhost" or a reference to
"C:\...", depending on whether you are using Access or SQL to hold your
repository. When you access AS remotely using Analysis Manager you need
to connect to the server by its network name, or to the access database
via a network share (which is setup when you install and Analysis
Manager knows about)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||Do you have any examples for these two? I could not find any in 2005 BOL.
Thanks,
Guangming
"Darren Gosbell" wrote:

>
> The repository database is the one that holds the cube structures and
> metadata. This database is used by Analysis Manager and the DSO library.
> It really only comes into play when building and processing cubes and
> dimensions.
> As someone pointed out in a post recently, you can query cubes without
> having this database at all.
> There are two different connection strings because when you are on the
> server you can access this database using "localhost" or a reference to
> "C:\...", depending on whether you are using Access or SQL to hold your
> repository. When you access AS remotely using Analysis Manager you need
> to connect to the server by its network name, or to the access database
> via a network share (which is setup when you install and Analysis
> Manager knows about)
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>|||They come from the AS2K server. Bring up Analysis Manager, right-click on
the server and select "Edit Repository Connection String" or something like
that :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:EC5E30E2-D181-4BA1-AFDD-61C93E0CB04C@.microsoft.com...[vbcol=seagreen]
> Do you have any examples for these two? I could not find any in 2005 BOL.
> Thanks,
> Guangming
> "Darren Gosbell" wrote:
>

DSO partitioning problem

Hi,
I have some DSO code which is partitioning a cube very nicely by deleting
and recreating old monthly partitions by cloning an original month partition.
When I actually come to test the data present in the cube I only ever get the
the original partitions data.
If I use the partition wizard to check out the filter settings for the new
partitions they all look OK - so I get an original partition for August and
then new ones for September, October, November.
In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
fields of the new partitions but get the feeling I am missing something.
Any ideas out there?
Yup. Find a copy of the SQL Server 2000 Resource Kit. On it was a utility
called the Metadata Scripter. When you installed it into Analysis Manager,
you can right-click on an object and generate a VB6 program which will
re-create that object. It generated DSO code. So the sequence of events to
figure out how to program with DSO is:
1) start from a known system.
2) do whatever you do in your custom application, then use the metadata
scriptor to generate "ProgA.vb".
3) go back to your known system
4) do whatever you do using Analysis Manager, then use the metadata scripter
to generate "ProgB.vb"
5) do a windiff comparing ProgA.vb and ProgB.vb -- and you will see that
there is some property or setting which your application is missing, but
Analysis Manager catches.
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:6D35B127-A2CB-442A-83BF-A63E4DBBE564@.microsoft.com...
> Hi,
> I have some DSO code which is partitioning a cube very nicely by deleting
> and recreating old monthly partitions by cloning an original month
> partition.
> When I actually come to test the data present in the cube I only ever get
> the
> the original partitions data.
> If I use the partition wizard to check out the filter settings for the new
> partitions they all look OK - so I get an original partition for August
> and
> then new ones for September, October, November.
> In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
> fields of the new partitions but get the feeling I am missing something.
> Any ideas out there?

DSO partitioning problem

Hi,
I have some DSO code which is partitioning a cube very nicely by deleting
and recreating old monthly partitions by cloning an original month partition
.
When I actually come to test the data present in the cube I only ever get th
e
the original partitions data.
If I use the partition wizard to check out the filter settings for the new
partitions they all look OK - so I get an original partition for August and
then new ones for September, October, November.
In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
fields of the new partitions but get the feeling I am missing something.
Any ideas out there?Yup. Find a copy of the SQL Server 2000 Resource Kit. On it was a utility
called the Metadata Scripter. When you installed it into Analysis Manager,
you can right-click on an object and generate a VB6 program which will
re-create that object. It generated DSO code. So the sequence of events to
figure out how to program with DSO is:
1) start from a known system.
2) do whatever you do in your custom application, then use the metadata
scriptor to generate "ProgA.vb".
3) go back to your known system
4) do whatever you do using Analysis Manager, then use the metadata scripter
to generate "ProgB.vb"
5) do a windiff comparing ProgA.vb and ProgB.vb -- and you will see that
there is some property or setting which your application is missing, but
Analysis Manager catches.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:6D35B127-A2CB-442A-83BF-A63E4DBBE564@.microsoft.com...
> Hi,
> I have some DSO code which is partitioning a cube very nicely by deleting
> and recreating old monthly partitions by cloning an original month
> partition.
> When I actually come to test the data present in the cube I only ever get
> the
> the original partitions data.
> If I use the partition wizard to check out the filter settings for the new
> partitions they all look OK - so I get an original partition for August
> and
> then new ones for September, October, November.
> In code I am setting the DimensionSlice, SliceValue and MemberKeyColumn
> fields of the new partitions but get the feeling I am missing something.
> Any ideas out there?

DSO on AS2005

Right now I am just working with DSO 8.5 and AS2005 in VB .NET (framework 2.0). I am trying to create an MDX statement called [Year to Date Amount] but when I do that, something puts brackets around Date to make it [Year to [Date] Amount] which will cause an MDX script error when processing it.

Someone mentioned this was a known issue and is going to be in SP2 but I was wondering if anyone knows where/if there is a hotfix for this?

Yes, I will be switching to AMO asap but I wanted to make sure what I have works with AS2005 right now (or will work with a simple patch).

Thank you.

This very much could be a known problem.

But please go ahead and file it using feedback site: http://connect.microsoft.com/SQLServer/feedback/.

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

DSO in SQL2005

Anybody can share the way to administer AS2005 using DSO?

AMO (Analysis Management Objects) is the object model for administering AS2005 and it's highly recommended to be used instead of DSO 8.5 shipped in AS2005 (DSO 8.5 is DSO 8.0 with SP4 fixes and additions to support AS2005 connectivity).
DSO 8.5 is for administering databases who are:
1. migrated from AS2000 to AS2005
and
2. are not edited/maintained with the AS2005 tools or AMO

Because of the changes between AS2000 and AS2005, a total DSO compatibility with AS2005 was not obtained and these are the restrictions:
- DSO 8.5 sees only the databases from the repository (DSO8’s repository)
- DSO 8.5 does not see the databases on the AS2005 server, he only reads the repository and deploys on the AS2005 server when processing an object; in other words, new databases on AS2005 (created with AS2005 tools) are not exposed through DSO 8.5
- DSO 8.5 overwrites (if in conflict) the databases from AS2005; for example if you created a database on AS2005 with DSO 8.5, then you edit it with AS2005 tools, when using DS0 8.5 the database will be overwritten with its version from the DSO repository (the AS2005 tools do not update the DSO repository)
- Because of changes between the AS2000 and AS2005 models, DSO 8.5 has an un-optimal design, certainly not having the AMO’s speed for comparable tasks

Please note that sometimes DSO 8.5 is referred to as DSO9 (since it works with AS2005 aka AS 9.0). I am calling it DSO 8.5 because that's the actual file version.

To setup DSO 8.5 to work with AS2005, you need to:
- have the DSO repository (from AS2000); usually this is the msmdrep.mdb file from "%ProgramFiles%\Microsoft Analysis Services\Bin"
- setup AS2005 to specify connection string to the DSO repository and also the locks folder; to do this, stop the AS2005 server, edit the msmdsrv.ini file (from the AS2005 installation folder), look for the <DSO> xml section:
<DSO>
<RemoteRepositoryConnectionString/>
<RepositoryConnectionString/>
<RemoteLocksDirectory/>
<LocksDirectory/>
</DSO>
- specify values for those properties (to see what they mean, please check books online "ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/75e062bd-7f24-43b0-8b0b-d48437d3a116.htm")
- start the AS2005 server
- connect with DSO 8.5 (for example using Analysis Manager from AS2000); please remember you will see the databases from the repository and not the actual databases from the AS2005 server

Adrian Dumitrascu.

|||What about this errror that I get after connecting to the repository with DSO:

HAIdcom.clsDrillThrough.GetDTColumnNames(): Path not found

|||Hmm - so it seems that using DSO from an application against AS2005 is not a good idea? It sound slike from your description that the use of DSO will overwrite any modifications made directly using SQL Mgmt Studio? Thats not good.
I have been trying to use DSO also in this scenario - where is the RepositoryConnectString supposed to point to - and what provider shpuld be used? The MS documentation gives no hints to this, when I try pointing to the access mdb you suggested I get:
Errors in the OLE DB provider. The data source or location is not valid. Provide a valid server to which to connect.
If I point to the Analysis server database I want to connect to using the new Analysis 9.0 provider I get:
Query (1, 10) Parser: The syntax for ',' is incorrect.
I would have thought there would be more people trying to do this and getting similar issues?
Any help just to get back to previous functionality would be greatly appreciated, thanks.
|||Hi All.

The DSO has been replaced by the AMO.
But the problem that arises with this is as follows.
DSO was COM. So, VBA code could use the DSO to access data/metadata of AS2000.
Now, I intend to access the same in VBA/Excel.
Any idea how I could do this, since AMO is not a COM class, and can be used thru .NET.

Essentially, my intention is to document a cube of AS2005 and throw the metadata into Excel sheets.
Thanx in advance.|||Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me
|||

fantasimus wrote:
Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me

Fantasimus, please read the earlier posts.

The AMO is what you should be using to access SSAS (AS2005).

And as I have said earlier, the AMO is a .NET library unlike the DSO which was COM.

However, using Visual Studio 2005 Tools for Office version, one can do VBA like work, since office project templates are available with VSTO.

And then, the programming can be in c# or vb.net

|||

Thanks for all comments.

It seems our work is proved to be correct. As these reasons Adrian refered, we never used DSO any more, neight DSO 8.5. But we made the wrapper for AMO using .NET, and CCW it to make COM visiable. That's saving time to update millions of codes. For balance to our worktime, at some points, we wrapper our business layer functions directly.

Hope our solution and choice will make sense to others who are willing to lift SQL2005, thanks!

|||

Hello,

I am testing the Beta Version of MS Project Server 2007, using Analysis Services 2005 to try to build an OLAP cube. I am consistently getting the following error (see below) and this forum is the only place that I can find any information on it. I'm not a developer, so I'm not sure how (or if) this issue relates to the DSO problem that you all discussed above. Do you have any insight?

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi Brian,

Did you get a solution for the error "Query (1,10) Parser: The syntax for
',' is incorrect. "?

I'm also getting the same error and clueless of what should be done.

Thanks

Umanga.

Posted By: bciampa on 11-07-2006 8:48 PM UTC

Subject: Re: DSO in SQL2005 Message:

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi guys! Have you got any solution for that error message(Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Query (1, 10) Parser: The syntax for ',' is incorrect.)?

Pls. help if you have one

Thanks

|||

Hi,

I don't have a straight answer for this problem, but let's try to debug it.

You are using DSO version 8.5 to connect to Analysis Services 2005 (after you configured AS2005 to setup the DSO-related server properties, the connection string to the repository and the lock directory).

You are able to connect, to read all the objects (although please remember that you read all the objects from the repository, not from the real AS2005 server).

And you get the error when you try to process an object ? If yes, let's try to see what commands is DSO sending to the AS2005 server before the error.

- start SQL profiler for Analysis Services

- re-run the minimal DSO code that would generate the error

- in profiler you should see the commands that DSO sent to server just before the error

- I assume you will have 2 commands: an Alter statement (for the Database probably, or maybe for a Partition if you process a Partition) followed by a Process statement, the Alter succeeds, but the Process fails; but let's check these assumptions first

Adrian Dumitrascu

|||

Any one has idea how to create roles and permissions for a SSAS cube using VB and then using AMO

I basically want to create the VB script and then want to run it through cmd prompt

please, let me know all the stepsto do all this

thanks

|||

This seems to be unrelated with the DSO in AS2005, there are these other threads about creating roles and permissions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798355&SiteID=1

DSO in SQL2005

Anybody can share the way to administer AS2005 using DSO?

Please note that sometimes DSO 8.5 is referred to as DSO9 (since it works with AS2005 aka AS 9.0). I am calling it DSO 8.5 because that's the actual file version.

Adrian Dumitrascu.

|||What about this errror that I get after connecting to the repository with DSO:

HAIdcom.clsDrillThrough.GetDTColumnNames(): Path not found

Essentially, my intention is to document a cube of AS2005 and throw the metadata into Excel sheets.
Thanx in advance.|||Hi,

thank you for the details.

I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?

I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new

reference in the project for DSO 8.5. With the old DSO it is not

possible to acces the AS 2005 :-(

Hop somebody can help me|||

fantasimus wrote:
Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me

Fantasimus, please read the earlier posts.

The AMO is what you should be using to access SSAS (AS2005).

And as I have said earlier, the AMO is a .NET library unlike the DSO which was COM.

However, using Visual Studio 2005 Tools for Office version, one can do VBA like work, since office project templates are available with VSTO.

And then, the programming can be in c# or vb.net

|||

Thanks for all comments.

Hope our solution and choice will make sense to others who are willing to lift SQL2005, thanks!

|||

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi Brian,

Did you get a solution for the error "Query (1,10) Parser: The syntax for
',' is incorrect. "?

I'm also getting the same error and clueless of what should be done.

Thanks

Umanga.

Posted By: bciampa on 11-07-2006 8:48 PM UTC

Subject: Re: DSO in SQL2005

Message:

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Pls. help if you have one

Thanks

|||

Hi,

I don't have a straight answer for this problem, but let's try to debug it.

You are able to connect, to read all the objects (although please remember that you read all the objects from the repository, not from the real AS2005 server).

And you get the error when you try to process an object ? If yes, let's try to see what commands is DSO sending to the AS2005 server before the error.

- start SQL profiler for Analysis Services

- re-run the minimal DSO code that would generate the error

- in profiler you should see the commands that DSO sent to server just before the error

Adrian Dumitrascu

|||

Any one has idea how to create roles and permissions for a SSAS cube using VB and then using AMO

I basically want to create the VB script and then want to run it through cmd prompt

please, let me know all the stepsto do all this

thanks

|||

This seems to be unrelated with the DSO in AS2005, there are these other threads about creating roles and permissions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798355&SiteID=1

DSO in SQL2005

Anybody can share the way to administer AS2005 using DSO?

Please note that sometimes DSO 8.5 is referred to as DSO9 (since it works with AS2005 aka AS 9.0). I am calling it DSO 8.5 because that's the actual file version.

Adrian Dumitrascu.

|||What about this errror that I get after connecting to the repository with DSO:

HAIdcom.clsDrillThrough.GetDTColumnNames(): Path not found

fantasimus wrote:
Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me

Fantasimus, please read the earlier posts.

The AMO is what you should be using to access SSAS (AS2005).

And as I have said earlier, the AMO is a .NET library unlike the DSO which was COM.

However, using Visual Studio 2005 Tools for Office version, one can do VBA like work, since office project templates are available with VSTO.

And then, the programming can be in c# or vb.net

|||

Thanks for all comments.

Hope our solution and choice will make sense to others who are willing to lift SQL2005, thanks!

|||

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi Brian,

Did you get a solution for the error "Query (1,10) Parser: The syntax for
',' is incorrect. "?

I'm also getting the same error and clueless of what should be done.

Thanks

Umanga.

Posted By: bciampa on 11-07-2006 8:48 PM UTC

Subject: Re: DSO in SQL2005Message:

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Pls. help if you have one

Thanks

|||

Hi,

I don't have a straight answer for this problem, but let's try to debug it.

You are able to connect, to read all the objects (although please remember that you read all the objects from the repository, not from the real AS2005 server).

And you get the error when you try to process an object ? If yes, let's try to see what commands is DSO sending to the AS2005 server before the error.

- start SQL profiler for Analysis Services

- re-run the minimal DSO code that would generate the error

- in profiler you should see the commands that DSO sent to server just before the error

Adrian Dumitrascu

|||

Any one has idea how to create roles and permissions for a SSAS cube using VB and then using AMO

I basically want to create the VB script and then want to run it through cmd prompt

please, let me know all the stepsto do all this

thanks

|||

This seems to be unrelated with the DSO in AS2005, there are these other threads about creating roles and permissions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798355&SiteID=1

DSO in SQL2005

Anybody can share the way to administer AS2005 using DSO?

Please note that sometimes DSO 8.5 is referred to as DSO9 (since it works with AS2005 aka AS 9.0). I am calling it DSO 8.5 because that's the actual file version.

Adrian Dumitrascu.

|||What about this errror that I get after connecting to the repository with DSO:

HAIdcom.clsDrillThrough.GetDTColumnNames(): Path not found

Essentially, my intention is to document a cube of AS2005 and throw the metadata into Excel sheets.
Thanx in advance.|||Hi,

thank you for the details.

I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?

I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new

reference in the project for DSO 8.5. With the old DSO it is not

possible to acces the AS 2005 :-(

Hop somebody can help me|||

fantasimus wrote:
Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me

Fantasimus, please read the earlier posts.

The AMO is what you should be using to access SSAS (AS2005).

And as I have said earlier, the AMO is a .NET library unlike the DSO which was COM.

However, using Visual Studio 2005 Tools for Office version, one can do VBA like work, since office project templates are available with VSTO.

And then, the programming can be in c# or vb.net

|||

Thanks for all comments.

Hope our solution and choice will make sense to others who are willing to lift SQL2005, thanks!

|||

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi Brian,

Did you get a solution for the error "Query (1,10) Parser: The syntax for
',' is incorrect. "?

I'm also getting the same error and clueless of what should be done.

Thanks

Umanga.

Posted By: bciampa on 11-07-2006 8:48 PM UTC

Subject: Re: DSO in SQL2005

Message:

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Pls. help if you have one

Thanks

|||

Hi,

I don't have a straight answer for this problem, but let's try to debug it.

You are able to connect, to read all the objects (although please remember that you read all the objects from the repository, not from the real AS2005 server).

And you get the error when you try to process an object ? If yes, let's try to see what commands is DSO sending to the AS2005 server before the error.

- start SQL profiler for Analysis Services

- re-run the minimal DSO code that would generate the error

- in profiler you should see the commands that DSO sent to server just before the error

Adrian Dumitrascu

|||

Any one has idea how to create roles and permissions for a SSAS cube using VB and then using AMO

I basically want to create the VB script and then want to run it through cmd prompt

please, let me know all the stepsto do all this

thanks

|||

This seems to be unrelated with the DSO in AS2005, there are these other threads about creating roles and permissions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798355&SiteID=1

DSO in SQL2005

Anybody can share the way to administer AS2005 using DSO?

Please note that sometimes DSO 8.5 is referred to as DSO9 (since it works with AS2005 aka AS 9.0). I am calling it DSO 8.5 because that's the actual file version.

Adrian Dumitrascu.

|||What about this errror that I get after connecting to the repository with DSO:

HAIdcom.clsDrillThrough.GetDTColumnNames(): Path not found

fantasimus wrote:
Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me

Fantasimus, please read the earlier posts.

The AMO is what you should be using to access SSAS (AS2005).

And as I have said earlier, the AMO is a .NET library unlike the DSO which was COM.

However, using Visual Studio 2005 Tools for Office version, one can do VBA like work, since office project templates are available with VSTO.

And then, the programming can be in c# or vb.net

|||

Thanks for all comments.

Hope our solution and choice will make sense to others who are willing to lift SQL2005, thanks!

|||

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi Brian,

Did you get a solution for the error "Query (1,10) Parser: The syntax for
',' is incorrect. "?

I'm also getting the same error and clueless of what should be done.

Thanks

Umanga.

Posted By: bciampa on 11-07-2006 8:48 PM UTC

Subject: Re: DSO in SQL2005 Message:

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Pls. help if you have one

Thanks

|||

Hi,

I don't have a straight answer for this problem, but let's try to debug it.

You are able to connect, to read all the objects (although please remember that you read all the objects from the repository, not from the real AS2005 server).

And you get the error when you try to process an object ? If yes, let's try to see what commands is DSO sending to the AS2005 server before the error.

- start SQL profiler for Analysis Services

- re-run the minimal DSO code that would generate the error

- in profiler you should see the commands that DSO sent to server just before the error

Adrian Dumitrascu

|||

Any one has idea how to create roles and permissions for a SSAS cube using VB and then using AMO

I basically want to create the VB script and then want to run it through cmd prompt

please, let me know all the stepsto do all this

thanks

|||

This seems to be unrelated with the DSO in AS2005, there are these other threads about creating roles and permissions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798355&SiteID=1

DSO in SQL2005

Anybody can share the way to administer AS2005 using DSO?

Please note that sometimes DSO 8.5 is referred to as DSO9 (since it works with AS2005 aka AS 9.0). I am calling it DSO 8.5 because that's the actual file version.

Adrian Dumitrascu.

|||What about this errror that I get after connecting to the repository with DSO:

HAIdcom.clsDrillThrough.GetDTColumnNames(): Path not found

Essentially, my intention is to document a cube of AS2005 and throw the metadata into Excel sheets.
Thanx in advance.|||Hi,

thank you for the details.

I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?

I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new

reference in the project for DSO 8.5. With the old DSO it is not

possible to acces the AS 2005 :-(

Hop somebody can help me|||

fantasimus wrote:
Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me

Fantasimus, please read the earlier posts.

The AMO is what you should be using to access SSAS (AS2005).

And as I have said earlier, the AMO is a .NET library unlike the DSO which was COM.

However, using Visual Studio 2005 Tools for Office version, one can do VBA like work, since office project templates are available with VSTO.

And then, the programming can be in c# or vb.net

|||

Thanks for all comments.

Hope our solution and choice will make sense to others who are willing to lift SQL2005, thanks!

|||

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi Brian,

Did you get a solution for the error "Query (1,10) Parser: The syntax for
',' is incorrect. "?

I'm also getting the same error and clueless of what should be done.

Thanks

Umanga.

Posted By: bciampa on 11-07-2006 8:48 PM UTC

Subject: Re: DSO in SQL2005

Message:

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Pls. help if you have one

Thanks

|||

Hi,

I don't have a straight answer for this problem, but let's try to debug it.

You are able to connect, to read all the objects (although please remember that you read all the objects from the repository, not from the real AS2005 server).

And you get the error when you try to process an object ? If yes, let's try to see what commands is DSO sending to the AS2005 server before the error.

- start SQL profiler for Analysis Services

- re-run the minimal DSO code that would generate the error

- in profiler you should see the commands that DSO sent to server just before the error

Adrian Dumitrascu

|||

Any one has idea how to create roles and permissions for a SSAS cube using VB and then using AMO

I basically want to create the VB script and then want to run it through cmd prompt

please, let me know all the stepsto do all this

thanks

|||

This seems to be unrelated with the DSO in AS2005, there are these other threads about creating roles and permissions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798355&SiteID=1

DSO in SQL2005

Anybody can share the way to administer AS2005 using DSO?

Please note that sometimes DSO 8.5 is referred to as DSO9 (since it works with AS2005 aka AS 9.0). I am calling it DSO 8.5 because that's the actual file version.

Adrian Dumitrascu.

|||What about this errror that I get after connecting to the repository with DSO:

HAIdcom.clsDrillThrough.GetDTColumnNames(): Path not found

fantasimus wrote:
Hi,
thank you for the details.
I use the DSO Object for AS2000. Where can i find the DLL for DSO 8.5 ?
I use the Interop.DSO.dll for DSO and AS 2000 and have to set e new reference in the project for DSO 8.5. With the old DSO it is not possible to acces the AS 2005 :-(
Hop somebody can help me

Fantasimus, please read the earlier posts.

The AMO is what you should be using to access SSAS (AS2005).

And as I have said earlier, the AMO is a .NET library unlike the DSO which was COM.

However, using Visual Studio 2005 Tools for Office version, one can do VBA like work, since office project templates are available with VSTO.

And then, the programming can be in c# or vb.net

|||

Thanks for all comments.

Hope our solution and choice will make sense to others who are willing to lift SQL2005, thanks!

|||

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Hi Brian,

Did you get a solution for the error "Query (1,10) Parser: The syntax for
',' is incorrect. "?

I'm also getting the same error and clueless of what should be done.

Thanks

Umanga.

Posted By: bciampa on 11-07-2006 8:48 PM UTC

Subject: Re: DSO in SQL2005 Message:

Hello,

=====Process Completed=====
[11/6/2006 2:11 PM] Failed to build the OLAP cubes. Error: Analysis Services
session failed with the following error: Query (1,10) Parser: The syntax for
',' is incorrect.

Thanks,

Brian

|||

Pls. help if you have one

Thanks

|||

Hi,

I don't have a straight answer for this problem, but let's try to debug it.

You are able to connect, to read all the objects (although please remember that you read all the objects from the repository, not from the real AS2005 server).

And you get the error when you try to process an object ? If yes, let's try to see what commands is DSO sending to the AS2005 server before the error.

- start SQL profiler for Analysis Services

- re-run the minimal DSO code that would generate the error

- in profiler you should see the commands that DSO sent to server just before the error

Adrian Dumitrascu

|||

Any one has idea how to create roles and permissions for a SSAS cube using VB and then using AMO

I basically want to create the VB script and then want to run it through cmd prompt

please, let me know all the stepsto do all this

thanks

|||

This seems to be unrelated with the DSO in AS2005, there are these other threads about creating roles and permissions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1795829&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1798355&SiteID=1

DSO Cell Calculation Problem

Hi
I am trying to set up a cell calculation using DSO and can not figure
out why it fails. The code is in VB6 (also tried in c#) but to no
avail.
Any help - thanks a lot
--
Dim dsoServer As New DSO.Server
Dim dsodb As Variant
dsoServer.Name = "ServerName"
dsoServer.Connect "ServerName"
Set dsodb = dsoServer.MDStores.Item("DBName") ' Name of AS Database
Dim dsoCube As DSO.Cube
Set dsoCube = dsodb.MDStores.Item("Test") ' Name of Cube
Dim dsoCommand As Variant
' Remove if found
If dsoCube.Commands.Find("CommandName") Then
dsoCube.Commands.Remove ("CommandName")
End If
Set dsoCommand = dsoCube.Commands.AddNew("CommandName")
dsoCommand.CommandType = DSO.CommandTypes.cmdCreateCellCalculation
dsoCommand.Description = "CommandDescription"
dsoCommand.Statement = "CREATE CELL CALCULATION [Test].[x] FOR
'{[Account].&[5102]}' AS '1001'"
dsoCube.Update
dsoServer.CloseServer
--
It creates a cell calulation object and saves it in the cube, but when
I look at it in analysis manager I get 2 errors...
1. Unable to pase the CREATE CELL FORMULA command. Please use DSO to
edit this command. Automation Error.
2. Unable to pase the CREATE CELL CALCULATION command. Please use DSO
to edit this command. Automation Error.
thanksSOLVED
The Statement needed "( )" around the sets in the FOR part (which would
normally indicate a tuple of the sets - which does not make sense to
me)... so it looks like this...
dsoCommand.Statement = "CREATE CELL CALCULATION CURRENTCUBE.[x] FOR
'({[Account].&[5102]})' AS '1001'"
I also used CURRENTCUBE instead of naming the cube.
No I can put the other 400 calcs in the cube and see how it performs
Hope this saves someone some time.

DSO

I was using DSO to make atree based on this example. But my Question is where could i put code for complexity_penalty or minimum_leaf_case in my code below?

And what's complexity_penalty or minimum_leaf_case use for? are they important in our coding?

Public Sub CreateRelMiningModel()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoDS As DSO.DataSource
Dim dsoDMM As DSO.MiningModel
Dim dsoColumn As DSO.Column
Dim dsoRole As DSO.Role

Dim strLQuote As String, strRQuote As String
Dim strFromClause As String

' Constants used for DataType property
' of the DSO.Column object.
' Note that these constants are identical to
' those used in ADO in the DataTypeEnum enumeration.
Const adInteger = 3
Const adWChar = 130

' Connect to the server on this computer.
dsoServer.Connect "LocalHost"

' Select the FoodMart 2000 database.
Set dsoDB = dsoServer.MDStores("FoodMart 2000")

' Retrieve the open and close quote characters for
' the FoodMart data source.
strLQuote = dsoDB.DataSources("FoodMart").OpenQuoteChar
strRQuote = dsoDB.DataSources("FoodMart").CloseQuoteChar

' The Customer table is the fact table for this
' relational data mining model; this variable will
' make it easier to understand the code that
' follows.
strFromClause = strLQuote & "customer" & strRQuote

' Check for the existence of the model on this computer.
If Not dsoDB.MiningModels("CustSalesModelRel") Is Nothing Then
' If this model exists, delete it.
dsoDB.MiningModels.Remove "CustSalesModelRel"
End If

' Create a new relational mining model
' called CustSalesModelRel.
Set dsoDMM = dsoDB.MiningModels.AddNew("CustSalesModelRel", _
sbclsRelational)

' Create a new mining model role called All Users
Set dsoRole = dsoDMM.Roles.AddNew("All Users")

' Set the needed properties for the new mining model.
With dsoDMM
.DataSources.AddNew "FoodMart", sbclsRegular
' Set the description of the model.
.Description = "Analyzes the salaries " & _
"of customers"
' Set the case table for the model to the
' Customer table.
.FromClause = strFromClause
' Select the algorithm provider for the model.
.MiningAlgorithm = "Microsoft_Decision_Trees"
' Let DSO define the training query.
.TrainingQuery = ""
' Save the existing structure.
.Update
End With

' Create the columns pertinent to the new model.

' Create the CustomerID column as a key column.
Set dsoColumn = dsoDMM.Columns.AddNew("CustomerID", _
sbclsRegular)
' Set the column properties for the new column.
With dsoColumn
' Set the source field from the case table for
' the column.
.SourceColumn = strFromClause & "." & strLQuote & _
"customer_id" & strRQuote
.DataType = adInteger
.IsKey = True
.IsDisabled = False
End With

' Create the Gender column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Gender", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"gender" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Marital Status column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Marital Status", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"marital_status" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Education column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Education", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"education" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Yearly Income column as an predictable column.
Set dsoColumn = dsoDMM.Columns.AddNew("Yearly Income", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"yearly_income" & strRQuote
.DataType = adWChar
.IsInput = False
.IsPredictable = True
.IsDisabled = False
End With

' Save the data mining model.
With dsoDMM
' Set the LastUpdated property of the new mining model
' to the present date and time.
.LastUpdated = Now
' Save the model definition.
.Update
End With

' Process the data mining model.
With dsoDMM
' Lock the mining model for processing
.LockObject olapLockProcess, _
"Processing the data mining model in sample code"
' Fully process the new mining model.
' This may take up to several minutes.
.Process processFull
' Unlock the model after processing is complete.
.UnlockObject
End With

' Clean up objects and close server connection
Set dsoRole = Nothing
Set dsoColumn = Nothing
Set dsoDMM = Nothing

dsoServer.CloseServer
Set dsoServer = Nothing

End Sub

thank's a lot

COMPLEXITY_PENALTY and MINIMUM_LEAF_CASES control the growth of decision trees - a higher complexity_penalty lowers the likelihood of splits; increasing minimum_leaf_cases prevents generation of leaf nodes with low support (number of cases that fall into that node).

You can add training parameters using the MiningModel object's Parameters collection. This download includes the source code for an Analysis Mgr Add-in that edits mining model parameters using DSO: http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=97

Tuesday, February 14, 2012

DSO Questions

DSO properties?

DSO properties?

DSO partitioning problem

DSO partitioning problem

DSO on AS2005

DSO in SQL2005

DSO in SQL2005

DSO in SQL2005

DSO in SQL2005

DSO in SQL2005

DSO in SQL2005

DSO in SQL2005

DSO Cell Calculation Problem

DSO

dts import agent

Blog Archive

About Me