Macro to read the dimension tree from *.mdc 1

[ggiridhar]

help in powerplay

I have to compare the dimensions of 2 data cubes manually...is there a way to write a macro to extract the dimension tree hierarchy so that i can write a code to compare cubes automatically

 

[drlex]

Yes; have a look at the Dimension object & associated commands in the Macro Reference file (In CognosScrip Editor > Help > Macro Language Help).

To start you off, here's a simple piece of code that loops through the dimensions of a cube and shows the first level of each:

Sub Main()
   Dim x, y, counter
   Dim objPPRep as Object
   Dim objPPDim as Object
   Set objPPRep = CreateObject("CognosPowerPlay.Report")
   objPPRep.New "D:\cubes\Acme sales.mdc", -1
   counter = objPPRep.DimensionLine.Count
      For x = 1 to counter 
         MsgBox objPPRep.DimensionLine.Item(x).Name
         Set objPPDim = objPPRep.DimensionLine.Item(x)
         For y = 1 to objPPDim.Count
            MsgBox objPPDim.Children.Item(y).Name  
         Next y
      Next x
   objPPRep.Close    
   Set objPPRep = Nothing
End Sub

Note that the last dimension will be the MEASURES folder, not a 'true' dimension, and only the default measure will be returned.

soi là, soi carré

 

[ggiridhar]

tanks drlex. This help means a lot to me. Tanks a lot

 

[drlex]

You're welcome; thanks for the accolade.

soi là, soi carré

 

[ggiridhar]

@drlex
am not able to retrive the lower levels...plz help me on it

 

[drlex]

Your question was whether there was a way to extract the dimension tree hierarchy with a macro; I gave you some code to start you off, saying that it would show the first level of each dimension.

Have you had a look at the Dimension object? I think you should look at the Children method and the Levels method (and note that the Levels do not show special categories).

Since Dimensions will have varying numbers of levels, consider also using a subroutine that you can call in a recursive manner to get all categories in each level of a dimension - the CALL function supports recursion.

Here's a variant of the initial code that uses the LEVELS Method to bring back the a listing of the top level of each dimension:

Sub Main()
   Dim x, y, counter
   Dim objPPRep as Object
   Dim objPPDim as Object
   Dim objPPDimLevel as Object
   Dim strLevel as String
   Set objPPRep = CreateObject("CognosPowerPlay.Report")
   objPPRep.New "D:\cubes\Acme sales.mdc", -1
   counter = objPPRep.DimensionLine.Count
      For x = 1 to counter-1 'Last dimension is the MEASURES folder
         Set objPPDim = objPPRep.DimensionLine.Item(x)
         For y = 1 to objPPDim.Levels.Count
           Set objPPDimLevel = objPPDim.Levels.Item(y)
           strLevel = strLevel & chr$(10) & objPPDimLevel.Name
           Set objPPDimLevel = Nothing  
         Next y
         MsgBox "The levels in the " & objPPDim.Name & " dimension are:" & chr$(10) & strLevel   
      strLevel = ""
      Next x   
   objPPRep.Close    
   Set objPPRep = Nothing
   Set objPPDim = Nothing
   Set objPPDimLevel = Nothing
End Sub

Once again, this is an example - it is not your solution coded for you.

soi là, soi carré

 

[ggiridhar]

i did recurly but it dint work...
i found some other way...
I could get the column object and drill down to the lower level there by ctreating the hierarchy but the problem which am facing now is

say if "Total Copany" is dimensioin_1 & "Total Period" dimension_2 when i do objPPRep.Columns.Item it only returns the dimension_2 entries.is there a way to retrive other dimension entries??

 

[drlex]

The "Columns" Object is for the columns of a report; your initial post states that you want to examine a cube.
In order to do that, the easiest method is to create a new report against the cube, and then work through all the dimensions, as opposed to just the rows and columns of the report.

soi là, soi carré

 

[ggiridhar]

thats what am doing now...but facing few problems
is there a way to catch the method not fount error or property not found error ??

 

[drlex]

Ignore the code that I posted in your other thread - I looked at it again and made a couple of changes in the iteration subroutine.
If you have a special/calculated category with the same name as a regular category, it can cause the output to be incorrect, as it will record the regular category details.

Allows for 9 levels. Large hierarchies may throw memory errors in VB.

Declare Sub getlevels(objPPDim, level)
   Dim x
   Dim Levelloop(9) as integer
   Dim level
   Dim objPPRep as Object
   Dim objPPDim as Object
   Dim objPPMeasure as Object
   Dim strItem as String
   Dim AuditFile as string
   Dim strCube as string
   
Sub Main
   strCube = "E:\cubes\Acme sales.mdc"
   AuditFile = "C:\CubeLog.csv"
   Open Auditfile For APPEND As #1
   Write #1, "Analysing " + strCube
   Set objPPRep = CreateObject("CognosPowerPlay.Report")
   objPPRep.New "E:\cubes\" + strCube, -1
   objPPRep.Visible = 1
      For x = 1 to objPPRep.DimensionLine.Count-1 'Last dimension is the MEASURES folder
         Set objPPDim = objPPRep.DimensionLine.Item(x)
         Write #1, objPPDim.Name & " Dimension"
         level = 0
            call getlevels(objPPDim, level)   
         Write #1, Chr$(10)
      Next x  
      Set objPPDim = objPPRep.DimensionLine.Item(objPPRep.DimensionLine.Count)
      objPPDim.ChangeToParent
          For x = 1 to objPPDim.Children.Count
             Set objPPMeasure = objPPDim.Children.Item(x)
             Write #1, "Measure " + Cstr(x) + " = " + objPPMeasure.Name
             Set objLevel = Nothing
          Next x
   objPPRep.Close    
   Set objPPRep = Nothing
   Set objPPDim = Nothing
End Sub
'
Sub getlevels(objPPDim, level)
   Dim z
   Dim objLevel as Object
      Set objLevel = objPPDim
         If objLevel.Children.Count = 0 then goto skip 'No children, so leave subroutine
         level = level + 1
         levelloop(level) = objLevel.Children.Count
            For z = 1 to levelloop(level)
               strItem = objLevel.Children.Item(z).Name
               strtext = "Level " + Cstr(level) + " Name: " + objLevel.Level.Name 
               Write #1, strtext + "; item no." + Cstr(z) + " of " + Cstr(levelloop(level)) + " = " + strItem
               objLevel.Change(strItem)
               test = 0
               If objLevel.IsAlternate = True or objLevel.IsCalculatedCategory = True then test = 1
               If test = 0 Then call getlevels(objLevel, level)  'iterate   
               If test = 1 then Write #1, "Alternate/Calculated/Special - skipping"
               If test = 1 then objLevel.ChangeToTop             
               If test = 1 then level = 1
            Next z   
         level = level - 1        
skip:    If objLevel.HasParent <> 0 then objLevel.ChangeToParent 'Up a level if required
End Sub

soi là, soi carré

 

[ggiridhar]

i agree with u that when v use Dimension object its drilling all the levels.
and for me if i have year dimension i can drill down till yyyy/month with my present code...
and if i use the columnObj.drilldown property its working properly.
ok say A and B r objects and am doing
Set B = A
if i change B will it affect A?
means if i do B.DrillDown then ll A also be DrilledDowned???

 

[drlex]

Yes; you can test this with a small piece of code:

Sub Main
   Dim objPPRep as Object
   Dim objPPDim as Object
   Dim objPPDim2 as Object
   Dim strItem as String
   '
   Set objPPRep = CreateObject("CognosPowerPlay.Report")
   objPPRep.New "E:\cubes\Acme sales.mdc", -1
   'objPPRep.Visible = 1
         Set objPPDim = objPPRep.DimensionLine.Item(1)
         Set objPPDim2 = objPPDim
         MsgBox "objPPDim is " + ObjPPDim.Name  + " and objPPDim2 is " + ObjPPDim2.Name 
         strItem = objPPDim.Children.Item(1).Name
         objPPDim.Change(strItem) 
         MsgBox "After Change to " + strItem + " on objPPDim, objPPDim is " + ObjPPDim.Name  + " and objPPDim2 is " + ObjPPDim2.Name 
         objPPDim.ChangeToParent
         MsgBox "After ChangetoParent on objPPDim, objPPDim is " + ObjPPDim.Name  + " and objPPDim2 is " + ObjPPDim2.Name 
   objPPRep.Close    
   Set objPPRep = Nothing
   Set objPPDim = Nothing
End Sub

soi là, soi carré

 

[ggiridhar]

ok is there a way to avoid that

 

[drlex]

To be honest, I've never needed to, so never tried.
Perhaps you could ask that question in the VBA forum, given the high similarity between that and CognosScript?

soi là, soi carré