create mining structure from tsql?

On 20 Dec., 07:58, "Dejan Sarka"
wrote:
> > I want to execute dynamic generated dmx-ddl
> > from tsql. How to do that?
> > Openquery doesn't work, because "create mining structure..."
> > does not return result set.
>
> CLR procedure?
>
> --
> Dejan Sarkahttp://blogs.solidq.com/EN/dsarka/default.aspx

That would be fine also.
I just prefer to write/generate dmx-ddl which is short than using
som object oriented solution (AMO, ADOMD.NET?) or XMLA.

It is more "natural" to me (yes I often also prefer write/generate
"create table"- ddl by hand ;^)

but would that works.
Let say have my DMX-ddl; how to send it from tsql stored proc (or clr
proc) to Analysis Server?
Greetins
Bjorn
 >> Stay informed about: create mining structure from tsql? 

Hi!
Well, maybe I at the end have to use your way
But I actually ask for a way to avoid AMO, so I could
write DMX.

Your example showed a solution for the opposite direction,
but I need to go from SQL Server to SSAS.

How to do that?
Greetings
Bjorn
 >> Stay informed about: create mining structure from tsql? 

Bellow is a VB example using AMO. You can also look at
http://blogs.solidq.com/EN/dsarka/Lists/Posts/Post.aspx?ID=31 and
http://blogs.solidq.com/EN/dsarka/Lists/Posts/Post.aspx?ID=30 to see how you
can create a data source and a data source view.

--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx


Imports AMO = Microsoft.AnalysisServices

' .....

Friend Function CreateMiningStructure(ByVal database As AMO.Database,
ByVal rdsvAdvWorks As AMO.DataSourceView) As AMO.MiningStructure
'Mining structure
Dim ms As AMO.MiningStructure = New
AMO.MiningStructure("TargetMail")
Dim DSVBinding As AMO.DataSourceViewBinding = New
AMO.DataSourceViewBinding()
DSVBinding.DataSourceViewID = dataSourceViewName
ms.Source = DSVBinding
database.MiningStructures.Add(ms)

'Key column
Dim CustomerKey As New
AMO.ScalarMiningStructureColumn("CustomerKey", "CustomerKey")
CustomerKey.Type = AMO.MiningStructureColumnTypes.Long
CustomerKey.Content = AMO.MiningStructureColumnContents.Key
CustomerKey.IsKey = True
'Add data binding to the column
CustomerKey.KeyColumns.Add("vTargetMail", "CustomerKey",
OleDbType.Integer)
'Add column to the structure
ms.Columns.Add(CustomerKey)

'Other columns
Dim NumberCarsOwned As New
AMO.ScalarMiningStructureColumn("NumberCarsOwned", "NumberCarsOwned")
NumberCarsOwned.Type = AMO.MiningStructureColumnTypes.Long
NumberCarsOwned.Content = AMO.MiningStructureColumnContents.Discrete
NumberCarsOwned.KeyColumns.Add("vTargetMail", "NumberCarsOwned",
OleDbType.Integer)
ms.Columns.Add(NumberCarsOwned)
Dim CommuteDistance As New
AMO.ScalarMiningStructureColumn("CommuteDistance", "CommuteDistance")
CommuteDistance.Type = AMO.MiningStructureColumnTypes.Text
CommuteDistance.Content = AMO.MiningStructureColumnContents.Discrete
CommuteDistance.KeyColumns.Add("vTargetMail", "CommuteDistance",
OleDbType.WChar)
ms.Columns.Add(CommuteDistance)
Dim BikeBuyer As New AMO.ScalarMiningStructureColumn("BikeBuyer",
"BikeBuyer")
BikeBuyer.Type = AMO.MiningStructureColumnTypes.Long
BikeBuyer.Content = AMO.MiningStructureColumnContents.Discrete
BikeBuyer.KeyColumns.Add("vTargetMail", "BikeBuyer",
OleDbType.WChar)
ms.Columns.Add(BikeBuyer)

Return ms

End Function

Friend Function CreateMiningModel(ByVal ms As AMO.MiningStructure) As
AMO.MiningModel
'Mining model
Dim dt As AMO.MiningModel
'Dim mmc As AMO.MiningModelColumn
dt = ms.CreateMiningModel(True, "TargetMailDT")
dt.Algorithm = "Microsoft_Decision_Trees"

'Model columns usage
dt.Columns("CustomerKey").Usage = "Key"
dt.Columns("BikeBuyer").Usage = "Predict"

Return dt

End Function



"B D Jensen" wrote in message

> On 20 Dec., 07:58, "Dejan Sarka"
> wrote:
>> > I want to execute dynamic generated dmx-ddl
>> > from tsql. How to do that?
>> > Openquery doesn't work, because "create mining structure..."
>> > does not return result set.
>>
>> CLR procedure?
>>
>> --
>> Dejan Sarkahttp://blogs.solidq.com/EN/dsarka/default.aspx
>
> That would be fine also.
> I just prefer to write/generate dmx-ddl which is short than using
> som object oriented solution (AMO, ADOMD.NET?) or XMLA.
>
> It is more "natural" to me (yes I often also prefer write/generate
> "create table"- ddl by hand ;^)
>
> but would that works.
> Let say have my DMX-ddl; how to send it from tsql stored proc (or clr
> proc) to Analysis Server?
> Greetins
> Bjorn
 >> Stay informed about: create mining structure from tsql? 

> Well, maybe I at the end have to use your way
> But I actually ask for a way to avoid AMO, so I could
> write DMX.

Well, AMO are intended for creating and managing models. however, if you
want to use DMX, you have to use ADOMD.NET. You will find an example bellow.

> Your example showed a solution for the opposite direction,
> but I need to go from SQL Server to SSAS.
>
> How to do that?

I guess you can write a CLR procedure in Analysis Services by using
ADOMD.NET Server (Server side ADOMD.NET exists as well). Then you can create
a linked served from SQL Server to Analysis Services and then call the AS
CLR procedure with 4-part name. Or you could try to write a CLR procedure in
SQL Server using ADOMD.NET client to connect to AS and do whatever you want
to do. However, I do not know whether ADOMD.NET client classes are supported
for CLR procedures in SQL Server.

--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx

Private Sub btnContent_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnContent.Click
'Connection
Dim oConnection As New AdomdConnection
Dim sConnectionString As String
sConnectionString = _
"Provider=MSOLAP;Data Source=localhost;"
oConnection.ConnectionString = sConnectionString
oConnection.Open()
lstContent.Items.Add("Connected. DT Content:")
lstContent.Items.Add("----------------------")
'Change the database to ASAMO
Dim sDatabase As String = "ASAMO"
oConnection.ChangeDatabase(sDatabase)
'DMX query
Dim sDMX As String = _
"SELECT NODE_DESCRIPTION" & _
" FROM [TargetMailDT].Content" & _
" WHERE NODE_DESCRIPTION <> ''"
'Populate an AdoMdDataReader
Dim oCommand As AdomdCommand = _
oConnection.CreateCommand()
oCommand.CommandText = sDMX
'Use AdomdDataReader
Dim oDataReader As AdomdDataReader = oCommand.ExecuteReader
Do While oDataReader.Read()
lstContent.Items.Add(oDataReader.GetString(0))
Loop
oDataReader.Dispose()
'finish
oConnection.Dispose()
lstContent.Items.Add("-------------")
lstContent.Items.Add("Disconnected.")
End Sub
 >> Stay informed about: create mining structure from tsql?