Load Cube Metadata Into SQL database

  • Article

Howdy Folks !
Hope this will be Helpful

Way of Finding Cube Meta Data Using SSIS Script Task
Context: To Find the Metadata about the Analysis Services Cube Many Approaches can be taken.
This Approach Doesn’t Store Cube Metadata in the Cube itself as Hidden Measure but in a Log Table Which is More Accessible.
Step 1: Create a New table in SQL Database
TABLENAME(LOG.CUBEMETADATA)
Column Name LastProcessedDate LastSchemaUpdate RequiredFurtherParameter*
Column Type String String As per Requirement

Step 2: Create a New Script Task in SSIS Package.
Create a new ADO.net Connection in Your Package before you do this
Don’t Forget to Add Required Assemblies in Script Task

Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices
Imports System.Data.SqlClient
Imports System.Xml

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Dim asServer As Server = New Server()
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As SqlDataAdapter
Dim Query As String
Dim CubeLastProcessed As String
Dim LastCubeSchemaUpdate As String
Dim StrLastProcessed As String
Dim StrLastSchemaUpdate As String
Public Sub Main()

'For Logging all the Connection Managers in the Existing Package-Omit if You Dont Need it
If Dts.Connections("(YourADO.net Connection Name)").ConnectionString Is Nothing Or Dts.Connections("(YourADO.net Connection Name)").ConnectionString = String.Empty Then
MsgBox("No Connection In Package found")
Else
connection = CType(Dts.Connections("(YourADO.net Connection Name)").AcquireConnection(Dts.Transaction), SqlConnection)
End If

asServer.Connect(Dts.Connections("(Your Analysis Services Connection Name)").ConnectionString)

'If Your Database Has Many Cubes find the RElevent cube you Need
For Each cube As Cube In asServer.Databases("(Your Analysis Services Database Name)")
If (cube.Name = "(Your Cube Unique Name)") Then
LastProcessed = cube.LastProcessed
LastSchemaUpdate = cube.LastSchemaUpdate

Else
Me.LogMessage("No Update Found !")
End If
Next

StrLastProcessed = (CType(CubeLastProcessed, String))
StrLastSchemaUpdate = (CType(LastCubeSchemaUpdate, String))
Query = "Insert into LOG.CUBEMETADATA(LastProcessedDate,LastSchemaUpdate) Values ('" & StrLastProcessed & "','" & StrLastSchemaUpdate & "')"
command = New SqlCommand(Query, connection)
command.ExecuteReader()
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Step3:Change the variable Names As per Requirement.
Step 4:run and Voila Your Table has been Updated !