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
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.
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:
Open the excel
Under View > Toolbars, Check the Visual Basic tool bar
Click on the Run macro botton. Chose the main macro and click on Run
The macro will open the Designer application and will prompt for a user id and password
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
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.
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?
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.
Open the excel
Hit Alt+F11. The macro will open
Place your mouse cursor on the line ‘Sub main()’
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.