Essbase Tricks & Tips - #1

As Essbase is so vast that we do forget some of the basics of Essbase. So I thought to start the "Tricks & Tips" series on my blog.

Disclaimer:
Please be informed that the information posted here is solely based on my knowledge and experience. Please check the content before using it - OraHyperion

Tip #1: Sometimes we face an error of Essbase server ( using command prompt or Windows Services) not getting startup.
The reason may be that the server could have got crashed while performing some calculation/restructure on the outline. Do not need to panic if this happens. Essbase engine always create a Security Backup file which contains the Users/Groups/Application/Filter information of the server in \bin folder of Essbase server. It regularly updated the .BAK file with the contents of .SEC file in the Essbase Server BIN folder.

Just delete the .SEC file and rename .BAK file to .SEC file and restart Essbase server. This re-establishes the Essbase server to the point when the Essbase server was working perfectly.

MS Excel Macro to Essbase's Rescue

I never knew that MS Excel's "Macros" can be so beneficial as far as Essbase is concerned. Macro is such a powerful scripting utility in Excel that it can be used in multiple facets.

As most of the Essbase design is done keeping the Finance in mind and all of us know that Finance guys are generally good in Excel.

MS Excel can be used to create files in different format which can be a source for multiple other systems.

Here is a sample Macro for generating a Text file which prompts for file name and stores the data from Excel in a text file.

Sub Macro1()

Dim sName As String, sString As String
Dim rng As Range, cell As Range
sSting = """" ' for inserting " in the output file
sName = ActiveSheet.Name
sName = Application.GetSaveAsFilename( _
InitialFileName:=sName & ".txt", _
FileFilter:="Text Files (*.txt),*.txt")
If sName = "" Then Exit Sub
Open sName For Output As #1
Set rng = Range(Range("A1"), _
Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
If cell.Text = "Yes" Then
Print #1, cell.Text , cell.Offset(0, 1).Text ' Output 1st column & 2nd column
Print #1, sSting & cell.Text & sSting ' Output 1st column under "
End if
Next
Close #1
End Sub

Things to note: Column Values (offset) starts from 0