BusinessObjects Board

Macro to fetch number of tables and columns in Universe

Author: nithya_raj
Author notes:

Discussion: NA
Platform: BO XI R2
Version: v 1.0
Code:

Dim des_app As Designer.Application
Dim des_unv As Designer.Universe
Dim des_class As Designer.Class
Dim des_obj As Designer.Object
Dim des_table As Designer.Table
Dim des_column As Designer.Column


Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim RowCount As Integer
Dim univ_name As Designer.Universe
Dim Wkst As Excel.Worksheet


Sub main()

'Logon and Open Universe
Set des_app = New Designer.Application

des_app.Visible = True

des_app.LogonDialog

Set des_unv = des_app.Universes.Open

'Create worksheet object
Set Wkst = ThisWorkbook.Sheets("New")
Wkst.Cells.Clear

ii = des_unv.Tables.Count
i = 0

For x = 1 To ii

    If des_unv.Tables(x).IsAlias = False Then
    i = i + 1
    End If

Next x

Wkst.Cells(1, 1) = des_unv.Name
Wkst.Cells(2, 1) = "Table Count"
Wkst.Cells(2, 2) = i

Wkst.Cells(3, 1) = "SNo"
Wkst.Cells(3, 2) = "Table Name"
Wkst.Cells(3, 3) = "Column Count"

RowCount = 4
b = 1

For j = 1 To ii

    k = des_unv.Tables(j).Columns.Count
    If des_unv.Tables(j).IsAlias = False Then
    
        Wkst.Cells(RowCount, 1) = b
        b = b + 1
        Wkst.Cells(RowCount, 2) = des_unv.Tables(j).Name
        Wkst.Cells(RowCount, 3) = k
        RowCount = RowCount + 1
         
    End If
    
Next j

des_unv.Close

des_app.Quit

MsgBox ("Done")

End Sub

Macro to fetch No of tables and columns in Universe.xls (48.0 KB)


nithya_raj (BOB member since 2007-02-03)

How do you run this?


rhodeislander (BOB member since 2006-06-17)

  1. Open the excel

  2. Under View > Toolbars, Check the Visual Basic tool bar

  3. Click on the Run macro botton. Chose the main macro and click on Run

  4. The macro will open the Designer application and will prompt for a user id and password

  5. Once the authentication is done, the Open Dialogue box will pop up, prompting for the Universe for which the tables and columns should be fetched


nithya_raj (BOB member since 2007-02-03)

Hi Nithyaraj,
When i run the Macro giving


Compile error: User-defined type is not defined 

i copied macro which is above.

Thanks in advance


Alapana :india: (BOB member since 2007-07-10)

Hi Alapana,

Did you copy-paste the macro in a new VBA window? Then the error could be because, you have not included the Business Objects references.

Try downloading the excel and use it as such.


nithya_raj (BOB member since 2007-02-03)

Its working fine

Thanks a lot,

MJ
:+1:


manasjena818 :india: (BOB member since 2007-08-13)

Hi Nithyaraj,
Does this work in BOXI R3 too? I’ve 175+ universes wanted to see number tables in each universe.

Our environment:
BOXI R3.1 SP3
Sun Solaris.
Thanks,


Stephen123 (BOB member since 2007-01-23)

Hi Stephen,
I am not able to check this on 3.1, I do not have access to a 3.1 environment. Sorry! :expressionless:


nithya_raj (BOB member since 2007-02-03)

working great in XIR3.1 too. But is there anyway can I I check all 175 universes in a single shot instead of checking each and every universe.


Stephen123 (BOB member since 2007-01-23)

I have a new version of the macro which will fetch all the Universes from the Root Universes folder. For including the Universes in the sub-folders, it would take me some time.
I am not sure how to upload it here though.
Dear Moderators,
Could I get some help on how to upload the new version in this post? Thanks.

Update: Created a new post on Bob’s uploads with the updated version
https://bobj-board.org/t/188909


nithya_raj (BOB member since 2007-02-03)

Author: nithya_raj
Author notes: This is an excel macro. This utility is used to obtain the number on tables in a universe and the number of columns in each table. We were able to utilise it during our quality audit when we had to submit this information. The utility also ensures that aliases are not taken into account while finding out the number of tables.
This version is an update from the earlier version. It no longer prompts the User to choose the Universe, instead obtains information for all the Universes in the Universe root folder in the Enterprise.
Discussion:
How to use the macro:

  1. Open the excel

  2. Under View > Toolbars, Check the Visual Basic tool bar

  3. Click on the Run macro botton. Chose the main macro and click on Run

  4. The macro will open the Designer application and will prompt for a user id and password

  5. Once the authentication is done, the macro will fetch the details of the tables and columns for all the Universes in the Universe folder in the Enterprise.

Platform: BO XI R2
Version: v 1.1
Code:

Dim des_app As Designer.Application
Dim des_unv As Designer.Universe
Dim des_class As Designer.Class
Dim des_obj As Designer.Object
Dim des_table As Designer.Table
Dim des_column As Designer.Column


Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x As Integer
Dim p As Integer
Dim q As Integer
Dim folder_name As String
Dim univ_name As String


Dim RowCount As Integer
'Dim univ_name As Designer.Universe
Dim Wkst As Excel.Worksheet


Sub main()

'Logon and Open Universe
Set des_app = New Designer.Application

des_app.Visible = True

des_app.LogonDialog

p = des_app.UniverseRootFolder.StoredUniverses.Count
folder_name = des_app.UniverseRootFolder

'Create worksheet object
Set Wkst = ThisWorkbook.Sheets("New")
Wkst.Cells.Clear
des_app.Interactive = False

RowCount = 1

For q = 1 To p

univ_name = des_app.UniverseRootFolder.StoredUniverses(q)


Set des_unv = des_app.Universes.OpenFromEnterprise(folder_name, univ_name)

ii = des_unv.Tables.Count
i = 0

For x = 1 To ii

    If des_unv.Tables(x).IsAlias = False Then
    i = i + 1
    End If

Next x

Wkst.Cells(RowCount, 1) = des_unv.Name
Wkst.Cells(RowCount + 1, 1) = "Table Count"
Wkst.Cells(RowCount + 1, 2) = i

Wkst.Cells(RowCount + 2, 1) = "SNo"
Wkst.Cells(RowCount + 2, 2) = "Table Name"
Wkst.Cells(RowCount + 2, 3) = "Column Count"

RowCount = RowCount + 3
b = 1

For j = 1 To ii

    k = des_unv.Tables(j).Columns.Count
    If des_unv.Tables(j).IsAlias = False Then
    
        Wkst.Cells(RowCount, 1) = b
        b = b + 1
        Wkst.Cells(RowCount, 2) = des_unv.Tables(j).Name
        Wkst.Cells(RowCount, 3) = k
        RowCount = RowCount + 1
         
    End If
    
Next j

des_unv.Close

Next q

des_app.Quit

MsgBox ("Done")

End Sub

Macro to fetch No of tables and columns in Universe_All_Universes.xls (48.0 KB)


nithya_raj (BOB member since 2007-02-03)

Moderator note:
I merged both topics so now the updated utility can be found right above my post.

Thanks for sharing.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

Thank you!


nithya_raj (BOB member since 2007-02-03)

When i try to run the Macro, i am getting the error. “Compile Error” Cannot find projetc or library"


nammu (BOB member since 2011-12-02)

Check which libraries are referenced and which of them are missing.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

no further info on the error. Just the above message i mentioned.


nammu (BOB member since 2011-12-02)

Hit Alt+F11 to open the MS Visual Basic Editor. Then go to menu Tools -> References. Here are the libraries referenced by the VBA project. Very likely you will see that some of the libraries have “MISSING” word in front of them. You need to reference new libraries, if you are using BO XI 3.1 since the libraries used by the original project (the missing ones) are libraries of BO XI R2.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

Thanks for instructions on libraries. But i have another issue now. When i run the macro, i get a prompted to Designer and then once i login, message shows “Done”. But after that i see blank spredsheet. Am i missing anything?


nammu (BOB member since 2011-12-02)

That’s a question for the author of the utility because I don’t know how it works.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

The utility picks up the table and column information for all the Universes in the Enterprise Universe folder. Try running the macro in debug mode to see if anything is being picked up.

  1. Open the excel
  2. Hit Alt+F11. The macro will open
  3. Place your mouse cursor on the line ‘Sub main()’
  4. Click on F5 to navigate through the code step by step

Please post back your results so that we can find out what is going wrong. Thanks.


nithya_raj (BOB member since 2007-02-03)