Populate SSAS data mining structure programmatically with c#

Question

Long time reader first time poster.

I am getting into SQL Server Analysis Services, but have been having great difficulty populating my newly created data mining structure.

I wish to work from my c# project which I am using as a central hub to control the learning. The data I wish to learn from is contained within a SQL Server database.

After creating an analysis services database in SSMS, I can create a mining structure -

CREATE MINING STRUCTURE [DON_TEST_000] ( [ID_NUM] LONG KEY, [TECK1] DOUBLE CONTINUOUS, [TECK2] DOUBLE CONTINUOUS ) WITH HOLDOUT(30 PERCENT)

And then append my model -

ALTER MINING STRUCTURE [DON_TEST_000] ADD MINING MODEL [DON_TEST_000_MODEL0] USING Microsoft_Clustering

Which seems to work okay. My problem is ->

1) I wish to do this from my c# project, using ADOMD.NET. I guess I can figure out how to create the SSAS database using this and execute the above queries.

But more critically,

2) I am unable to populate my mining structure with data at all - as the OPENQUERY (via INSERT) functionality requires a 'data source'. I have read on how to create this using Visual Studio, but I wish to do so programmatically from my c# project.

---

Surely there is a way for me to get Sql Server data into an Sql Server Analysis Services database using c#. It would seem to be so easy!

---

Thanks for any help, D

Answer 1

Not sure if I got you correctly but you can create/process SSAS databases, from normal SQL Jobs. You can write stored procedures which trigger these database jobs, and call them from within your c#.NET project.

Answer 2

You have to deploy from BIDS, but then to reprocess the cube you can have an SQL job executing XMLA

For instance you would create a job named TestJob, and in your XMLA database job you would have something similar to this :

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
    <Object>
      <DatabaseID>[Your SSAS Database]</DatabaseID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>
</Batch> 

And then you can use from C#.NET, EXEC msdb.dbo.sp_start_job 'TestJob' to run the reprocessing of the cube.

Not sure if that was what you are after.