AMO Class library: MdxScript Commands

Hi,

I have a question regarding the AMO Class MDX script.

As desribed here http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.mdxscriptcollection_members.aspx there seems to be a collection with several MDX scripts and a marker for the active script collection...

Does anyone know if BIDS always uses collectionitem(o) from the enumeration?
or is it possible to handle more than one item of the collection in BIDS? and if it is possible - where is the button to change the shown item in BIDS?

Thanks in advice!

Cheers,
Markus

I'm not sure about the first part of your question, but I do know that it isn't possible to see more than one item from the collection in BIDS. Sorry...

Chris

|||

Hello Chris,

thanks for the answer - hope that someone could clarify, the usage of the collection...

Have you seen this?

http://www.european-pass-conference.com/

Hope that we will meet there...

Cheers,

Markus

|||

hello Markus,

if in your cube, you have an MDXScript object with DefaultScript property set to true, then Calculations tab in BIDS's Cube Editor, should pick up that script. In case there is no such script, then then first script in the collection, would be chosen. It is not possible to switch to a different script in BIDS. BIDS will never create multiple scripts itself.

hope this helps,

|||

No, I hadn't seen that - thanks! I'm pleased to see that PASS Europe is back, and I'll definitely try to be there.

Chris

AMO "Scripter" Class - Help

Has anyone ever used Microsoft.AnalysisServices.Scripter class to programtically script SSAS objects? I am looking for a little bit of sample code to get me started. My goal here is to script some of the SSAS objects out into XML and store the XML in a table that can be queried to produce some meta data reports. I plan on doing this using AMO inside of a Script Task of a SSIS package.

So here are my two main questions:

Can anyone provide me with some sample code (I am not that good with .NET) using the Scripter class to script an SSAS object? Can anyone tell me a better way to build reports based on the meta data of my SSAS objects?

Thank you in advance!

Here is some sample code to use Scripter to script Alter command for your database into a file. You can later have SSIS to load content of the file into a table.

using Microsoft.AnalysisServices;

........

XmlTextWriter xmlwrite = new XmlTextWriter( "MyDatabase.XMLA", System.Text.Encoding.UTF8);

xmlwrite.Formatting = Formatting.Indented;

xmlwrite.Indentation = 2;

Scripter.WriteAlter(xmlwrite, myDataBase, true, true);

xmlwrite.Close();

|||

Edward,

Thanks for the code sample. This should do the trick. However, I have one more follow up question for you. How do I write the XML into a String variabe instead of to a file? Thanks again.

|||

Take a look at the functionality provided to you by XMLTextWriter.

http://msdn2.microsoft.com/en-us/system.xml.xmltextwriter.aspx

You shoudl be able to use different constructor for it.Search for sample of using XMLTextWriter.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Thanks again for the help. Here is the code that I came up with, it is in VB.NET and will work inside of a SSIS Script Task. It should help anyone trying to do the same thing. Thanks you for your help on this one.

' 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.IO

Imports System.Data

Imports System.Math

Imports System.Xml

Imports System.Data.SqlClient

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Class ScriptMain

Public Sub Main()

' Get Server and Database name from DTS connection object

Dim oDTSASConnection As ConnectionManager = Dts.Connections("AS Server")

Dim sASServer As String = CStr(oDTSASConnection.Properties("ServerName").GetValue(oDTSASConnection))

Dim oASServer As New Microsoft.AnalysisServices.Server

' Connect to the requested server

oASServer.Connect(sASServer)

' Loop trough all of the dbs on the server and write xmla to db

Dim oASDatabase As New Microsoft.AnalysisServices.Database

For Each oASDatabase In oASServer.Databases

Call ScriptASDatabase(oASServer, oASDatabase)

'' Loop trough all of the cubes in the db and write xmla to db

Dim oASCube As New Microsoft.AnalysisServices.Cube

For Each oASCube In oASDatabase.Cubes

Call ScriptASCube(oASServer, oASDatabase, oASCube)

Next

' Loop trough all of the dimensions in the db and write xmla to db

Dim oASDim As New Microsoft.AnalysisServices.Dimension

For Each oASDim In oASDatabase.Dimensions

Call ScriptASDim(oASServer, oASDatabase, oASDim)

Next

Next

' Clean Up

oASServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End Sub

Public Sub ScriptASDatabase(ByVal oASServer As Microsoft.AnalysisServices.Server, ByVal oASDatabase As Microsoft.AnalysisServices.Database)

' Create wrtiters and set formating for xml

Dim stringWriter As New System.IO.StringWriter()

Dim xmlWriter As New System.Xml.XmlTextWriter(stringWriter)

xmlWriter.Formatting = Formatting.Indented

xmlWriter.Indentation = 2

Dim sXMLA As String

' Script dbs on the server and write to db

Scripter.WriteCreate(xmlWriter, oASServer, oASDatabase, False, False)

sXMLA = stringWriter.ToString

Call DBUpsert(oASServer.Name.ToString, oASDatabase.Name.ToString, oASDatabase.Name.ToString, oASDatabase.ID.ToString, oASDatabase.ID.ToString, "Database", sXMLA.ToString)

' Clean Up

stringWriter.Close()

xmlWriter.Close()

End Sub

Public Sub ScriptASCube(ByVal oASServer As Microsoft.AnalysisServices.Server, ByVal oASDatabase As Microsoft.AnalysisServices.Database, ByVal oASCube As Microsoft.AnalysisServices.Cube)

'Create wrtiters and set formating for xml

Dim stringWriter As New System.IO.StringWriter()

Dim xmlWriter As New System.Xml.XmlTextWriter(stringWriter)

xmlWriter.Formatting = Formatting.Indented

xmlWriter.Indentation = 2

Dim sXMLA As String

' Script cubes in the db and write to db

Scripter.WriteCreate(xmlWriter, oASDatabase, oASCube, False, False)

sXMLA = stringWriter.ToString

Call DBUpsert(oASServer.Name.ToString, oASDatabase.Name.ToString, oASCube.Name.ToString, oASCube.ID.ToString, oASCube.Parent.ID.ToString, "Cube", sXMLA.ToString)

' Clean Up

stringWriter.Close()

xmlWriter.Close()

End Sub

Public Sub ScriptASDim(ByVal oASServer As Microsoft.AnalysisServices.Server, ByVal oASDatabase As Microsoft.AnalysisServices.Database, ByVal oASDim As Microsoft.AnalysisServices.Dimension)

'Create wrtiters and set formating for xml

Dim stringWriter As New System.IO.StringWriter()

Dim xmlWriter As New System.Xml.XmlTextWriter(stringWriter)

xmlWriter.Formatting = Formatting.Indented

xmlWriter.Indentation = 2

Dim sXMLA As String

' Script cubes in the db and write to db

Scripter.WriteCreate(xmlWriter, oASDatabase, oASDim, True, False)

sXMLA = stringWriter.ToString

Call DBUpsert(oASServer.Name.ToString, oASDatabase.Name.ToString, oASDim.Name.ToString, oASDim.ID.ToString, oASDim.Parent.ID.ToString, "Dimension", sXMLA.ToString)

' Clean Up

stringWriter.Close()

xmlWriter.Close()

End Sub

Public Sub DBUpsert(ByVal sASServer As String, ByVal sASDatabase As String, ByVal sASName As String, ByVal sID As String, ByVal sParentID As String, ByVal sObjectType As String, ByVal sXMLA As String)

' Create a connection to the db

Dim oDTSDBConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = Dts.Connections("Local.MetaData")

Dim sDBServer As String = CStr(oDTSDBConnection.Properties("ServerName").GetValue(oDTSDBConnection))

Dim sDBDatabase As String = CStr(oDTSDBConnection.Properties("InitialCatalog").GetValue(oDTSDBConnection))

Dim oBuilder As New SqlConnectionStringBuilder()

oBuilder.DataSource = sDBServer

oBuilder.InitialCatalog = sDBDatabase

oBuilder.ConnectTimeout = 1000

oBuilder.IntegratedSecurity = True

oBuilder.ApplicationName = "ImportSSASXml"

Dim oDBConnection As New SqlConnection(oBuilder.ConnectionString.ToString)

' Create Sql Command

Dim cmd As New SqlCommand("dbo.etl_ImportSSASXml", oDBConnection)

cmd.CommandTimeout = 60

cmd.Connection = oDBConnection

cmd.CommandType = CommandType.StoredProcedure

' Add parameters and their values

cmd.Parameters.Add(New SqlParameter("@.Server", SqlDbType.VarChar, 255)).Value = sASServer

cmd.Parameters.Add(New SqlParameter("@.Database", SqlDbType.VarChar, 255)).Value = sASDatabase

cmd.Parameters.Add(New SqlParameter("@.Name", SqlDbType.VarChar, 255)).Value = sASName

cmd.Parameters.Add(New SqlParameter("@.ID", SqlDbType.VarChar, 255)).Value = sID

cmd.Parameters.Add(New SqlParameter("@.ParentID", SqlDbType.VarChar, 255)).Value = sParentID

cmd.Parameters.Add(New SqlParameter("@.ObjectType", SqlDbType.VarChar, 255)).Value = sObjectType

cmd.Parameters.Add(New SqlParameter("@.XMLA", SqlDbType.VarChar)).Value = sXMLA

' Open the connection

oDBConnection.Open()

' Execute the command

cmd.ExecuteNonQuery()

' Clean Up

oDBConnection.Close()

End Sub

End Class

I will be posting a comprehensive solution to building meta data reports on/about ones' SSAS objects on my blog in the next few weeks. Check it out if you are interested!

Am having script Problems with Duplicates and Inserts

Ok, here is the situation, I have a view in one database and I want to insert all the data into a table on the same server but in a different database. With a no duplicate insert, cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

So I first wrote a script that looked for duplicates, this worked.

FROM Coffee.dbo.vueProductCase a
JOIN (SELECT ProductCode, COUNT(*) AS cnt
FROM coffee.dbo.vueProductCase
GROUP BY ProductCode
HAVING COUNT(*) > 1) b
ON a.ProductCode = b.ProductCode

It displayed a list of Duplicates, so I then tried to enter this script which doesnt seem to work at all, but it could be that it is because I dont know how to combine the scripts to insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

insert dbo.tblInItem
(ItemId,Descr,ProductLine,SalesCat,UomBase,UomDflt )
select
t1.ProductCode,
t1.[Description],
t1.'COFFEE',
t1.'CS',
t1.WeightMeasurement,
t1.'EACH'
from COFFEE.dbo.vueProductCase t1 left join dbo.tblInItem t2 on t1.ProductCode = t2.itemid
where t2.itemid is null

Can I get some help please??cause my target table field ItemID can not be duplicated, also if the ItemID already exists, then I dont want to import it either.

Doesn't make sense.

insert into the target table any productcode that doesnt already exist and even if it is duplicated, I still need to bring it into the target table if it doesnt exist once.

Nope...this one doesn't make sense either.

Give us a sample table create statement with insert statements for the data. Then, show us what you want the data to look like when complete. We should be able to help you pretty quickly then. Right now, someone else might be able to help you if they understand you. I'm not getting it though. [:)]|||My question is "What error message or incorrect results are you getting".

Because I don't see anything syntactically wrong with your insert statement, and "It don't work fer nuffin at all" doesn't give us a lot of clues...