Tuesday, July 24, 2012

Read partition QueryDefinition (query) using AMO

I found some similar questions on msdn forums where some guys are interested to know how to get the SQL query using AMO which is used there in partition Querydefinition. I have answered one of the similar question on MSDN forum (MSDN thread) but I thought lets share the same on blog too so others can check if they need.


For using AMO, you will need a "Microsoft.AnalysisServices.dll". Reference the dll in your project so you can use all the classes of AnalysisServices namespace. After referencing, you can use following code to get the query used in partition Querydefinition.



        Dim objServer As Server
        Dim objDatabase As Database
        Dim objCube As Cube
        Dim objMeasureGroup As MeasureGroup
        Dim objPartitionSource As QueryBinding
        Dim strQuery As String

        objServer = New Server
        objServer.Connect("localhost")

        objDatabase = objServer.Databases.FindByName("Adventure Works DW 2008R2")
        objCube = objDatabase.Cubes.FindByName("Adventure Works")
        objMeasureGroup = objCube.MeasureGroups.FindByName("Internet Sales")

        For Each objPartition As Partition In objMeasureGroup.Partitions
            objPartitionSource = objPartition.Source
            strQuery = objPartitionSource.QueryDefinition
        Next

        objServer.Disconnect()

While executing above code,  strQuery will return the query from partition  QueryDefinition

1 comment: