Excel reports: what cubes are used ?
- Aug. 31, 2013
 | Example files with this article: |
Introduction
Only recently, I had to audit a bunch of Excel reports containing several reports based off TM1 cubes. The usual approach having several Excel sheets in a workbook, all filled with a number of DBRW() functions (or DBR()). Due to a variety of reasons, it takes a long time to audit the reports:
- 10 workbooks containing at least 5 worksheets each, each worksheet about 100 DBRW-like formulas (not all the same slice and not even the same cube);
- DBRW functions refer to cubenames that are hidden;
- for server name and cube names, I had a mix of cell references, defined names, hard-coded names, formulas, …
VBA-code
You can download the code in an Excel file at the top of the page, but here is the code listing:
Sub ExcelReports_WhatCubesAreUsed()' Wim Gielis ' https://www.wimgielis.com''''' ' Excel reports: what cubes are used in them ? ' 08/31/13 '''''Dim wb As Workbook, ws As Worksheet Dim rng As Range, rCell As Range Dim rCellsToInspect As Range Dim sOutput_Value As String Dim iScopeToInspect As ScopeToInspect Dim iOutputType As OutputType '=================================================== 'set 2 options here using the Enumeration iScopeToInspect = All_Workbooks iOutputType = ServerName_And_Cubename '=================================================== 'DBR* is to be found 'but only 1 such function in a cell (i.e. not nested) sMyOutput = "\" On Error Resume Next 'loop through all workbooks in the application For Each wb In Application.Workbooks 'check the scope on workbook If (iScopeToInspect <= All_Workbooks) Or _ (iScopeToInspect >= Active_Workbook And wb.FullName = ActiveWorkbook.FullName) Then 'loop through the worksheets in this workbook For Each ws In wb.Sheets 'skip completely empty sheets If WorksheetFunction.CountA(ws.Cells) > 0 Then 'check the scope on worksheet If (iScopeToInspect <= Active_Workbook) Or _ (iScopeToInspect = Active_Sheet And ws.Index = ActiveSheet.Index) Then 'check the scope on area to be investigated If (iScopeToInspect = Active_Sheet) Then If TypeName(Selection) = "Range" Then If iScopeToInspect = Current_Selection Then If Selection.Count > 1 Then Set rCellsToInspect = Selection Else Set rCellsToInspect = ws.UsedRange End If Else Set rCellsToInspect = ws.UsedRange End If Else Set rCellsToInspect = ws.UsedRange End If Else Set rCellsToInspect = ws.UsedRange End If 'limit the area to only cells with formulas Set rCellsToInspect = rCellsToInspect.SpecialCells(-4123) 'do we find cells with formulas? If Not rCellsToInspect Is Nothing Then 'investigate each formula For Each rng In rCellsToInspect 'is it a DBRW or DBR or DBRA function ? (in fact, DBR*) If UCase(Left(Replace(Split(rng.Formula, "(")(0), " ", ""), 4)) = "=DBR" Then 'chop up the formula to get server and cube sCubeRef = Split(Split(rng.Formula, "(")(1), ",")(0) 'evaluate ranges and defined names (and constants) sServerAndCube = ws.Evaluate("=" & sCubeRef) 'chop off the last part in case of an TM1RPTFORM function sq = Split(sServerAndCube, ":") If UBound(sq) = 2 Then sServerAndCube = sq(0) & ":" & sq(1) End If 'what do we want to track for the user? cubename, or servername:cubename If iOutputType = ServerName_Only Then sMyOutput_Value = Split(sServerAndCube, ":")(0) ElseIf iOutputType = CubeName_Only Then sMyOutput_Value = Split(sServerAndCube, ":")(1) ElseIf iOutputType = ServerName_And_Cubename Then sMyOutput_Value = sServerAndCube Else sMyOutput_Value = "" End If 'concatenate to get the unique combinations of server name and cubename If InStr(UCase(sMyOutput), "\" & UCase(sOutput_Value) & "\") = 0 Then sMyOutput = sMyOutput & sOutput_Value & "\" End If End If Next End If End If End If Next End If Next 'output to the user If Len(sMyOutput) > 1 Then MsgBox Replace("I identified these cubes:" & Left(sMyOutput, Len(sMyOutput) - 1), "\", vbCr), vbInformation Else MsgBox "No DBR* cells were found.", vbInformation End IfEnd Sub
It is the first time in my (coding) life that I used an Enumeration. I consulted my fellow MVP Chip Pearson's web page on Enumerations. Here is the code that you should have at the top of the module:
Enum ScopeToInspect
All_Workbooks = 0
Active_Workbook = 1
Active_Sheet = 2
Current_Selection = 3
End Enum
Enum OutputType
CubeName_Only = 1
ServerName_Only = 2
ServerName_And_Cubename = 3
End Enum
The Enumerations allow you to use Intellisense on the variables and increase the readability of the code (ahum, my code is always readable, with or without Enumerations):
The Enumerations allow you to set, respectively,
- if you want to inspect all workbooks in the current Excel application;
- if you want to inspect all worksheets in (only) the active workbook;
- if you want to inspect all formula cells in (only) the active worksheet;
- if you want to inspect only all formulas in the selected cell(s).
- if you want to list the server names and the cube names;
- if you only want to list the cube names (without the server names).
- if you only want to list the server names (without the cube names).
When you have the time to do so, have a look at the use of the backslash in the code. Not only do I use it in an elegant way, the backslash cannot be used in the name of a cube. Hence, that character is a good candidate to concatenate individual cube names.
Caveats
As mentioned in the VBA-code, the code can only handle formulas with 1 DBR* formula. When 2 such formulas are nested within each other, the code could return false results (too many results or too little results). We can only hope that best practice is followed and that only a DBR* formula is in a cell - not DBS* or something like:
=DBRW(...) + 1500 - DBRW(...)
or:
=DBRW(DBRW(...))
I am afraid that tracing these constructions will remain to be manual work during an audit.
