AMO: How to process after adding a measure?

Hi, friends, please look this:

dim tDatabase as Microsoft.AnalysisServices.DataBase

tDatabase=Mainform.AnalysisDB 'here to refer it to an object

tDatabase..Cubes(0).MeasureGroups(0).Measures.Add() 'here to add a measure

tDatabase..Cubes(0).Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull)

tDatabase.Process(Microsoft.AnalysisServices.ProcessType.ProcessUpdate)

I want to know if it's correct to use the ProcessType.ProcessUpdate option?

After adding/dropping a measure/KPI, what ProcessType need I use?

After update a dsv, what ProcessType need I use?

Thanks!

To the best of my knowledge processUpdate is only valid for dimensions (see http://msdn2.microsoft.com/en-us/library/ms345142.aspx)

If you add a measure you are changing the structure of the cube which will invalidate the cube, the easiest way to get the cube "fully operational" again would be to do a processFull. The same thing applies when removing a measure.

I don't think you need to do any processing for calculated measures or KPI's as these elements do not have storage structures associated with them.

As far as the DSV is concerned, it depends on the type of change you are making. Changing the DSV will not invalidate the cube structure as such. The DSV is an abstraction for the data source(s) of the cube. It depends on the type of changes you are making. If the data that comes out of the DSV is affected in any way, you would need to re-process the cube to get it to reflect these changes. More importantly, if you change data types or remove elements that other objects such as dimensions, partitions, measures etc. are dependant upon, then you would need to change those objects too and that would most likely require full re-processing.

|||

Thank you Gosbell ,

Please tell me if I drop or add a measure, if I always need to ProcessFull?

As you know, ProcessFull will take a very long time, specially when the cube is huge. I don't think the client could stand the long time just because he/she add or delete a measure thourgh my application with AMO.

How to deal with this problem?

|||

Please tell me if I drop or add a measure, if I always need to ProcessFull?

Yes, you do. You can add and remove calculations easy enough, but adding and removing measures that are linked to fact table columns changes the fundamental data structures. Adding and removing measures to a measureGroup is not really something to be done lightly, especially once a large cube has been deployed.

The bare minimum that you could do to get the cubes "queryable" again would be a processStructure followed by a processData, but the cubes would not have any indexes or aggregations, so they could be quite slow. Doing a processIndex would add the indexes and aggregations, but processFull is the effectively the same as doing all three of these operations individually. You might see a slight performance advantage by doing the three operations individually, but it would not be a dramatic change.

|||I see that. Thanks again, Gosbell!

AMO: how to get a SourceColumnName from a measure object?

Hi, friends, please look this:

In AMO, I can get a measure object:

Dim meas As Microsoft.AnalysisServices.Measure

meas = tDatabase.Cubes(0).MeasureGroups(0).Measures(0)

Dim dt as DataItem= meas.source

Now I want to find the meas' SourceColumnName, that is, the ColumnName of the measure mapped to.

But how? I tried the meas.source's property, but did not find anyone like a Name.

Please help me.

Thanks!

Hi there,

Try

tDatabase.Cubes(0).MeasureGroups(0).Measures(0).Source.Source.ToString()

C

|||

Thanks!|||

The source of a Measure can be either a:

1. ColumnBinding (the common case): this points (with the TableID and ColumnID properties) to a System.Data.DataColumn in the DataSourceView of the Cube (specified in the Cube.Source property). This DataColumn can be either a regular column or a calculated column, there are these special extended properties (in the DataColumn.ExtendedProperties collection) to consider: DbColumnName (provides the real column name in the relational table, this is usually the same with the DataColumn.ColumnName) and DbExpression (provides the SQL expression for a calculated column)

2. RowBinding: used for row count measures, points (with the TableID property) to the System.Data.DataTable in the DataSourceView

3. MeasureBinding: used for linked measures (in a linked measure group), points (with the MeasureID property) to the measure to link

Adrian Dumitrascu

|||

I am trying to , via AMO, get the type of Binding for a measure (column, row, measure) and then return, when the type is columnbinding, the columnid and tableid from the DSV. Could you provide an example?

AMO: how to get a SourceColumnName from a measure object?

Hi, friends, please look this:

In AMO, I can get a measure object:

Dim meas As Microsoft.AnalysisServices.Measure

meas = tDatabase.Cubes(0).MeasureGroups(0).Measures(0)

Dim dt as DataItem= meas.source

Now I want to find the meas' SourceColumnName, that is, the ColumnName of the measure mapped to.

But how? I tried the meas.source's property, but did not find anyone like a Name.

Please help me.

Thanks!

Hi there,

Try

tDatabase.Cubes(0).MeasureGroups(0).Measures(0).Source.Source.ToString()

C

|||

Thanks!|||

The source of a Measure can be either a:

1. ColumnBinding (the common case): this points (with the TableID and ColumnID properties) to a System.Data.DataColumn in the DataSourceView of the Cube (specified in the Cube.Source property). This DataColumn can be either a regular column or a calculated column, there are these special extended properties (in the DataColumn.ExtendedProperties collection) to consider: DbColumnName (provides the real column name in the relational table, this is usually the same with the DataColumn.ColumnName) and DbExpression (provides the SQL expression for a calculated column)

2. RowBinding: used for row count measures, points (with the TableID property) to the System.Data.DataTable in the DataSourceView

3. MeasureBinding: used for linked measures (in a linked measure group), points (with the MeasureID property) to the measure to link

Adrian Dumitrascu

|||

I am trying to , via AMO, get the type of Binding for a measure (column, row, measure) and then return, when the type is columnbinding, the columnid and tableid from the DSV. Could you provide an example?

AMO: About Update

hi,friend, please have look:

I want to know, if I do:

dim tdatabase as Microsoft.AnalysisServices.database

' let tdatabase = someone existed on the server

tdatabase.update()

Will it cause the associated object be updated too ?

I mean, if I need NOT to do:

dsv.update()

dim.update()

cube.update()

measure.update()

and so on.

And, I can't find an update method on KPI. If that means I just need to update the cube object after I change a KPI object?

And, If I add/Remove a measure, need I do Process? Or just need to do Update?

Thanks!

ivanchain wrote:

I want to know, if I do:

dim tdatabase as Microsoft.AnalysisServices.database

' let tdatabase = someone existed on the server

tdatabase.update()

Will it cause the associated object be updated too ?

No it will not update all associated objects, see my next statment below.

ivanchain wrote:

I mean, if I need NOT to do:

dsv.update()

dim.update()

cube.update()

measure.update()

and so on.

The following is from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1842622&SiteID=1

Adrian Dumitrascu [MSFT]

The .Update() method only saves the minor properties and collections (thus not the major children) of an object. The major children are the AMO objects derived from the MajorObject class (Database, DataSource, Dimension, Cube, MeasureGroup, Partition, MiningStructure, MiningModel and others).

In our case, the database.Update() will save the database Name, ID, Description, Translations and other few minor properties, but won't save anything about the Role, which is a major object.

So you would need to call .Update on any major objects, so probably yes to dsv, dim, cube, but no to measure (I think a measure is a minor property of the measuregroup)

ivanchain wrote:

And, I can't find an update method on KPI. If that means I just need to update the cube object after I change a KPI object?

And, If I add/Remove a measure, need I do Process? Or just need to do Update?

If you add/remove a measure it will invalidate the cube structure, so once you do an update, your cube will be in an unprocessed state and you will need to process it.

|||

ivanchain wrote:

And, I can't find an update method on KPI. If that means I just need to update the cube object after I change a KPI object?

Yes, you need to call .Update() on the parent Cube. The Update method is only available on major objects (the ones that can be created, changed and deleted by themselves on the Analysis Services server - in AMO they are derived from the MajorObject class). To add/modify/delete a minor object (like Kpi, Measure, DimensionAttribute, Hierarchy, Level) you need to call .Update() on the parent major object.

|||

Very clear answer!

Thank you!

AMO: About the measure object''s remove method.

hi,friend, please look this:

I have a measure object in AMO.

Dim meas As Microsoft.AnalysisServices.Measure
Meas =tDatabase.Cubes(0).MeasureGroups(0).Measures(0)

tDatabase.Cubes(0).MeasureGroups(0).Measures.Remove(Meas, True)

I just want to know the meaning of the Remove method's second para. Measures.Remove(item as Microsoft.AnalysisServices.Measure, CleanUp as boolean)

What's the CleanUP? The remove method also support to only use the item para. What happened if I set the CleanUP to True/False, or just missing it?

Thanks!

Hi,

The 'cleanUp' boolean argument specifies if the dependent objects will be removed. For a Measure, the dependents include the PerspectiveMeasure objects. For a DimensionAttribute, the dependents include the CubeAttribute objects.

If you do not specify the 'cleanUp' parameter, the dependents are removed.

If you want for example to replace one object with another (having the same ID), then you can use the 'cleanUp'=false argument.

When working connected to the server, the 'cleanUp' argument has limited use, because you cannot save an invalid object or an object that would invalidate other objects in the database. For example, if you chose to remove a Measure with 'cleanUp'=false and dependent PerspectiveMeasures do exist, then you will get an error when trying to save the MeasureGroup (because it will leave the Perspective(s) with broken references).

Adrian Dumitrascu

|||

Thank you very much, Adrian.

As you said,The 'cleanUp' boolean argument specifies if the dependent objects will be removed.

I want to konw, the KPI object is the dependent object to the Measure? When I remove a measure with CleanUp= True, will the KPI associated be removed too? Or Just I need to remove the KPI by myself? If I don't remove the KPI, but removed the measure which associated, will it report an error when I save it to the server?

Thanks again.

|||

> I want to konw, the KPI object is the dependent object to the Measure? When I remove a measure with CleanUp= True, will the KPI associated be removed too?

No, the KPI objects are not removed. If the Value property of the KPI is referring to a certain measure, when you delete that measure, AMO will not parse the Value (nor any Command objects from an MdxScript in the Cube, or other MDX fragments in other objects) to see if the measure is used.

When checking for dependents, AMO only sees direct-defined metadata references; for example: a PerspectiveMeasure has the MeasureID property to reference the Measure, a CubeAttribute has the AttributeID property to reference a DimensionAttribute, a CubeDimension has the DimensionID property to reference a Dimension.

> If I don't remove the KPI, but removed the measure which associated, will it report an error when I save it to the server?

The .Update() method will succeed (because the Analysis Services engine will do a similar dependencies analysis as AMO), but you will get MDX errors when browsing.

Adrian Dumitrascu

|||

very clear!

Thank you, Adrian!

AMO: About the measure object's remove method.

hi,friend, please look this:

I have a measure object in AMO.

Dim meas As Microsoft.AnalysisServices.Measure
Meas =tDatabase.Cubes(0).MeasureGroups(0).Measures(0)

tDatabase.Cubes(0).MeasureGroups(0).Measures.Remove(Meas, True)

I just want to know the meaning of the Remove method's second para. Measures.Remove(item as Microsoft.AnalysisServices.Measure, CleanUp as boolean)

What's the CleanUP? The remove method also support to only use the item para. What happened if I set the CleanUP to True/False, or just missing it?

Thanks!

Hi,

The 'cleanUp' boolean argument specifies if the dependent objects will be removed. For a Measure, the dependents include the PerspectiveMeasure objects. For a DimensionAttribute, the dependents include the CubeAttribute objects.

If you do not specify the 'cleanUp' parameter, the dependents are removed.

If you want for example to replace one object with another (having the same ID), then you can use the 'cleanUp'=false argument.

When working connected to the server, the 'cleanUp' argument has limited use, because you cannot save an invalid object or an object that would invalidate other objects in the database. For example, if you chose to remove a Measure with 'cleanUp'=false and dependent PerspectiveMeasures do exist, then you will get an error when trying to save the MeasureGroup (because it will leave the Perspective(s) with broken references).

Adrian Dumitrascu

|||

Thank you very much, Adrian.

As you said,The 'cleanUp' boolean argument specifies if the dependent objects will be removed.

I want to konw, the KPI object is the dependent object to the Measure? When I remove a measure with CleanUp= True, will the KPI associated be removed too? Or Just I need to remove the KPI by myself? If I don't remove the KPI, but removed the measure which associated, will it report an error when I save it to the server?

Thanks again.

|||

> I want to konw, the KPI object is the dependent object to the Measure? When I remove a measure with CleanUp= True, will the KPI associated be removed too?

No, the KPI objects are not removed. If the Value property of the KPI is referring to a certain measure, when you delete that measure, AMO will not parse the Value (nor any Command objects from an MdxScript in the Cube, or other MDX fragments in other objects) to see if the measure is used.

When checking for dependents, AMO only sees direct-defined metadata references; for example: a PerspectiveMeasure has the MeasureID property to reference the Measure, a CubeAttribute has the AttributeID property to reference a DimensionAttribute, a CubeDimension has the DimensionID property to reference a Dimension.

> If I don't remove the KPI, but removed the measure which associated, will it report an error when I save it to the server?

The .Update() method will succeed (because the Analysis Services engine will do a similar dependencies analysis as AMO), but you will get MDX errors when browsing.

Adrian Dumitrascu

|||

very clear!

Thank you, Adrian!

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!