Automatic Generation of Cube - Possible??Help..

[Rafic]

Hai All,
My MD wants to Automatically Generate Cubes by any possible way in Sql Server. Any anyone suggest/reply if this is possible, if possible, give the solution.

thanks
Rafic

 

[JayKusch]

You can look into using DTS which has a Data Mining Prediction and Analysis Services Processing Task tasks.

Leveraging that you can schedule DTS packages via a SQL Server Job, you can build data warehousing/marts with all the connections and tasks included in DTS.

You can also create custom tasks and surface them in DTS if you so desire.

Thanks

J. Kusch

 

[MDXer]

If you have the skills or the resources the best way of automating cube processes and tasks is through the use of the DSO Object model. DSO Is the Analysis Services API it is fairly easy to pick up and the automation possibilities are endless.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Rafic]

Hi,
Thx Duck & Kusch.
Can Duck explain some steps involved in DSO Model
Thanks
Rafic

 

[MDXer]

To utilize the DSO Model you do it in either VB Script, VB or VB.NET may preffered method is VB.NET. You create a VB.NET project the interop the MS Decision Support Objects. After you have the DLLs then you simply import the DSO object into your code. A method I like to use is to have a table that has the columns.

OlapServer - Server name for the Analysis Services Server
OlapDatabase - Database on the server
OlapCube - Cube to process
OlapObject - ObjectName either the dimension or Partition Name
OlapObjectType Cube, Dimension or Partition
ProcessType Full, Incremental or Update
ProcessStatus 1 = Awaiting Process, 2 = Inprocess, 3 = Completed

all Of the above information is required to process. your app could just read in the information and use it to automatically process your cubes.

If your really good you can make the app multithreaded and process multiple partitions, or cubes. If you utilize partitions you can also build the processes to automatically create new partitions based upon some criteria and clone the aggregations which makes life really nice.

Hope THis Helps

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Rafic]

Hai Duck,
Thx.
i have asked my .net programmer to handle the situation.
Thx for ur solution

with regards & thx.
Rafic