Searchable Catalog of BPC Script Logic Files
When you have created a BPC application that includes many script logic files it could be very useful to be able to easily search through every program in the environment for references to any specific dimension values or properties. This would be particularly useful when hierarchies change or when considering changing dimension property values.
Fortunately it is relatively simple to create a searchable database containing every line of script logic code with reference to the model,program name,program line number. I have used Excel plus a bit of vb script to create this searchable code database, you could choose other more elegant solutions and add more bells and whistles but this does the job for me.
The code can now be easily searched using standard functions like Ctrl F or using filters on columns.
How to create the script catalog
Download the metadata to a zip file using transaction UJBR
Unzip the files from the folder ADMINAPP
Open the VB editor in a new Excel file and paste the code from this blog page into it.
Create a button on Sheet 1 of the workbook and link it to the code
Run the code to create a catalog of all your script logic
Now you have all the LGF and LGX files from your environment downloaded to a regular folder on your PC
The next step is to use the Excel application to read every .LGF file in these directories and write the results into a single searchable page, together with the metadata , file name, model and row id.
VB Code Download
The VB code required can be downloaded from the Downloads area or copy pasted from the section below
VB Code - copy and paste into Excel
' VB code to read through directories of text files and write the results 'into a single excel sheet Sheet1
' Written by Peter Warren BI Consultancy Solutions Ltd Dec 2017
Const ForReading = 1
Dim fso, f, FldrC, fl, fc, fldr, nextfile, FD
RowID = 3
ColID = 4
ProgRow = 1
'Clear 100k rows from first 4 columns of Sheet1
Range(Cells(1, 1), Cells(100000, 4)).Clear
'Write the headers in row 1
Sheet1.Cells(1, 1) = "Model"
Sheet1.Cells(1, 2) = "Script File"
Sheet1.Cells(1, 3) = "Program Row ID"
Sheet1.Cells(1, 4) = "Code line "
Range(Cells(1, 1), Cells(1, 4)).Font.Italic = True
Range(Cells(1, 1), Cells(1, 4)).Font.Bold = True
'Set the status bar on so we can see the progress of the program execution
Application.DisplayStatusBar = True
'Create a File Dialog object so we can navigate to any directory from where we want to read files
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
' Set the Help text description for the FolderBrowserDialog.
FD.Title = _
"Select the ADMINAPP directory or other top level directory for the script files"
'After selecting a folder store the selected item to a variable called FolderName and add a \ to the end
FolderName = FD.SelectedItems(1) & "\"
'Create fso as a FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'object f is the selected folder from the file system object
Set f = fso.GetFolder(FolderName)
' Create Folder Collection object FldrC
Set FldrC = f.SubFolders
'This loop with count up the total number of files in each sub folder of the main folder f and produce a total
For Each FileCount In FldrC
FcountTotal = FileCount.Files.Count + FcountTotal
'Now perform a double loop which will go through each folder in the collection and read each file within each folder
' for each Folder in Folder collection
For Each fldr In FldrC 'loop through the folders
'create file collection object
Set fc = fldr.Files
' for each file in file collection
For Each fl In fc 'loop through the files
ProgressCounter = ProgressCounter + 1
ProgressPCT = ProgressCounter / FcountTotal
Application.StatusBar = "Reading scripts in model..." & fldr.Name & " | Percent Complete = " & Format(ProgressPCT, "Percent")
'trap to skip reading the LGX files as they are not useful -
'could also just delete them from the directory instead
If Right(fl.Name, 4) = ".LGF" Then
Set nextfile = fso.GetFile(fldr.Path & "\" & fl.Name)
'this is the bit that reads each file line by line
Set objTextFile = fso.OpenTextFile _
ProgRow = 1
'The Do loop writes the result of each strNextLine to column 4 of sheet 1 and adds 1 to the row count
Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.Readline
Sheet1.Cells(RowID, 1) = fldr.Name
Sheet1.Cells(RowID, 2) = fl.Name
Sheet1.Cells(RowID, 3) = ProgRow
Sheet1.Cells(RowID, 4) = strNextLine
RowID = RowID + 1
ProgRow = ProgRow + 1
Application.StatusBar = "Complete...All scripts read"