Tools for scripting Analysis Svcs maintenance?

Question

Tools for scripting Analysis Svcs maintenance?

paulchernoch

SSC-Addicted

Points: 441
More actions
January 3, 2004 at 1:45 pm

#160582

What tools are available for scripting common maintenance tasks for Analysis Services? I have seen some references to a few DTS Tasks for processing cubes, but how about extracting the cube definition into a DDL script that can be run to create new cubes with the same dimensions, facts, etc?
I have also seen a good write-up on how to use the TSQL function OpenQuery to query cubes as though they were RDBMS tables. Are there any other things besides issuing select statements that can be done from TSQL?
Also, somewhere in my surfing I came across a reference to using Perl with DSO (Decision Support Objects), but can find no perl modules related to this.
Help!
(BTW: I am reading MDX Solutions by Spofford. It is good and fairly comprehensive, but some of the more complicated tasks are not described in enough detail for my liking, such as how to merge dimensions.)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

re: Scripting tools, if you have the SQL Server Resource Kit there is a MetaDataScripter that is an (VB6 dll) add-in to the AS (MMC) console and generates VBScript file/s that can be used to recreate the scripted object.

Steve.

paulchernoch SSC-Addicted Points: 441 More actions · Answer 2

Thanks for the suggestion. I have not yet been able to get this to work, however.

I use .NET and not VB6, so I ran the conversion utility. There were some syntax changes I had to rectify (many of which were in CRegistry.vb, which I removed from the project). As others have discovered, the registry functions from VB6 do not work with .NET, so I replaced the 500+ lines of Code in CRegistry.vb with about 20 - 30 lines in a rewritten RegisterAddIn:

Imports Microsoft.Win32

...

Public Sub RegisterAddIn()

' This procedure can be called in design-time to write the correct

' Registry settings for this Analysis Services add-in. This procedure

' must be run once, otherwise the Analysis Manager will not be able to

' start or reference this add-in.

On Error GoTo ErrRoutine

' Establish error handling information.

Dim strRoutineName, strRoutineParameters As String

strRoutineName = "RegisterAddIn"

strRoutineParameters = "Identifying parameters:" & vbCrLf & " None"

'--------------------------------------

Dim rk As RegistryKey

Dim subKey1 As String

Dim subKey2 As String

subKey1 = "Microsoft\OLAP Server\Olap Manager Info\Addins"

subKey2 = "Microsoft\OLAP Server\Olap Manager Info\Addins" & "\" & System.Reflection.Assembly.GetExecutingAssembly.GetName.Name

rk = Registry.LocalMachine.OpenSubKey("SOFTWARE", True)

rk.CreateSubKey(subKey1)

rk.Close()

rk = Registry.LocalMachine.OpenSubKey("SOFTWARE\" & subKey1, True)

rk.CreateSubKey(System.Reflection.Assembly.GetExecutingAssembly.GetName.Name)

rk.Close()

rk = Registry.LocalMachine.OpenSubKey("SOFTWARE\" & subKey2, True)

rk.SetValue("ClassName", System.Reflection.Assembly.GetExecutingAssembly.GetName.Name & ".COLAPAddIn")

rk.SetValue("Name", System.Diagnostics.FileVersionInfo.GetVersionInfo(System.Reflection.Assembly.GetExecutingAssembly.Location).ProductName)

rk.SetValue("Description", System.Diagnostics.FileVersionInfo.GetVersionInfo(System.Reflection.Assembly.GetExecutingAssembly.Location).FileDescription)

rk.SetValue("Priority", "2")

rk.Close()

'--------------------------------------

ExitRoutine:

MsgBox("MetaDataScripter add-in registered.")

'UPGRADE_NOTE: Object objRegistry may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC/commoner/redir/redirect.htm?keyword="vbup1029"'

rk = Nothing

Exit Sub

ErrRoutine:

MsgBox(Err.Number & " - " & Err.Description, MsgBoxStyle.Exclamation, "Error - " & System.Diagnostics.FileVersionInfo.GetVersionInfo(System.Reflection.Assembly.GetExecutingAssembly.Location).ProductName & "." & strRoutineName)

WriteErrorLog(strRoutineName, strRoutineParameters, Err.Number, Err.Description)

Resume ExitRoutine

End Sub

I then noticed that the Microsoft Documentation on how to register an add-in mentioned that an additional value needed to be set:

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Olap Manager Info\Addins

Name: MetaDataScripter

Value: True

But even after doing this, when I follow the procedure of right clicking on a Cube (or any other icon in Analysis Manager) I do not see a submenu named "All tasks" which should appear in the popup menu. This submenu should exist and have an item labeled "Meta Data Scripter", but it does not. What am I doing wrong? Help!

- Paul

Tiago Rente Default port Points: 1403 More actions · Answer 3

Hi,

I am also not able to install the metadatascripter . Actually, I was able to installed it on my PC. However, I had to change the DLL to an EXE file to be able to run the RegisterAddIn subroutine .

Now the problem is to install the metadatascripter on other PC's, which most of them do not have VB6 installed.

Any help is welcomed.

Thanks in advance.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

If the machines have the /Net framework on them, you could try the DSO-XML conversion utility instead. This has both GUI and cmdline apps.

Steve.