1. AMO(Analysis Management Objects) with .Net CLR Procedure
2. Power Shell Script
3. SSIS ETL Package.
Here i explained my Experience with
[1.] AMO(Analysis Management Objects) with .Net CLR Procedure this is More flexible and easy to handle Cube Objects.
Step 1.Enable CLR in SqlDataBase
SP_CONFIGURE 'CLR ENABLED',1 GO RECONFIGURE
Step2. Create New .Net DataBase SqlServerProject(CLR Project) with your Required DataBase connection
Step3: Create Microsoft.AnalysisServices.DLL" Reference in [Cube Processing] Project
1. Find "Microsoft.AnalysisServices.DLL" in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\ and copy paste to some Folder D:\CLR Project\
2. Register Assembly in SQLServer DataBase
CREATE ASSEMBLY [AnalysisServices]
FROM D:\CLR Project\Microsoft.AnalysisServices.DLL'
WITH PERMISSION_SET = UNSAFE
3. Refer [AnalysisServices] Assembly
Right click [Cube Processing] Project and click Add Reference and Select "Sql Server Tab" select
[AnalysisServices] Assebly.
Note1. Assembly will be avaliable in "SqlServer Tab" only after Creating Assembly in Datbase .
as well as it can be seen in SQLManagementStudio -> Database->Programmabillity -> Assembly
Step 4: Create Method ProcessPartition Using Microsoft.AnalysisServices Classs
/*
Process the Cube Partition using given Input Parameter Values. If ProcessDimensions is True then
process all MeasureGroup Related Dimension
-- Simple Code and Commented Briefly
*/
1. Copy Paste the code
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Microsoft.AnalysisServices;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ProcessPartition(SqlString ServerName, SqlString CatalogName, SqlString CubeName, SqlString MeasureGroupName, SqlString PartitionName, SqlBoolean ProcessDimensions)
{
try
{
// Create Instance for AnalysisServer
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
// Create Instance for MeasureGroup
MeasureGroup measureGroup = new MeasureGroup();
// Connect Analysis Server Instance
svr.Connect(ServerName.ToString());
if ((svr != null) && (svr.Connected))
{
// Connect the Analysis Server DataBase
Microsoft.AnalysisServices.Database db = svr.Databases.FindByName(CatalogName.ToString());
Cube cub;
// Find the Analysis Server DataBase Cube
cub = db.Cubes.FindByName(CubeName.ToString());
// Find the Analysis Server DataBase Cube MeasureGroup
measureGroup = cub.MeasureGroups.FindByName(MeasureGroupName.ToString());
// Check ProcessDimensions Flag If its True Process All MeasureGroup Related Dimesnion
if (ProcessDimensions == true)
{
foreach (MeasureGroupDimension Dim in measureGroup.Dimensions)
{
// Proces Dimension
Dim.Dimension.Process();
}
}
// Find the Analysis Server DataBase Cube MeasureGroup Partttion
Partition par = measureGroup.Partitions.FindByName(PartitionName.ToString());
// Process Partition
par.Process();
}
// DisConnect Server Connection
svr.Disconnect();
}
catch (Exception Ex)
{
// To Send Error Message to Database
SqlContext.Pipe.Send(Ex.Message);
throw new ApplicationException(Ex.Message);
}
}
};
/*
This code will process the Cube up to low Level object. If you wish to Process only the DataBase,Cube,MeasureGroup then
use DataBase.Process(),Cube.Process(),MeasureGroup.Process() .
*/
2. Build the Project
3. Copy the [Cube Processing] DLL from Solution \bin\Debug folder and Paste to D:\CLR Project\
4. Create Assembly in SqlServer DataBase:
CREATE ASSEMBLY [Cube Processing]
FROM 'D:\CLR Project\Cube Processing.dll'
WITH PERMISSION_SET = UNSAFE
5. View the Assembly
Now the Registered [Cube Processing] Assembly wl be abaliable in SQLManagementStudio -> Database->Programmabillity -> Assembly
Step5: Create Extented StoreProcedure Using Registed Assembly (Cube Processing])
CREATE PROCEDURE [dbo].[ProcessPartition]
(@ServerName NVARCHAR(50)
, @CatalogName NVARCHAR(50)
, @CubeName NVARCHAR(50)
, @MeasureGroupName NVARCHAR(50)
, @PartitionName NVARCHAR(50)
, @ProcessDimensions BIT
)
AS
EXTERNAL NAME [Cube Processing].[StoredProcedures].[ProcessPartition]
[Cube Processing] -->Registered Assembly Name
[StoredProcedures] --> Class Name of the above Code
[ProcessPartition] --> Method Name of he Class
Step6: Execute Procedure [dbo].[ProcessPartition]
EXEC [ProcessPartition] @ServerName = 'MyServer\Instance1'
, @CatalogName = 'MyDatabase'
, @CubeName = 'MyCube'
, @MeasureGroupName = 'MyCube'
, @PartitionName = 'MyCube_2008_08'
, @ProcessDimensions = 1
!! Any help and sugestion about this article are welcome !!!
