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

Sub read_lgf_files()

'---------------------------------------------------------

' 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"

FD.Show

'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)

Else

GoTo SKIPFILE

End If

'this is the bit that reads each file line by line

Set objTextFile = fso.OpenTextFile _

(nextfile, ForReading)

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

Loop

SKIPFILE:

Application.StatusBar = "Complete...All scripts read"

End Sub