CALC DIM Vs AGG

If you have being using Calc Scripts in your Essbase database, I am sure you may have got confused when to use CALC DIM and when to use AGG for aggregation.

Here is the difference

CALC DIM (Product);
This line calculates the Product dimension doing both outline
aggregation (such as rolling all the Colas up into the parent
value) and member formulas, if they exist for members in that
dimension. If we want to calculate multiple dimensions using
this command (say, Market and Product), just separate them
with commas:
CALC DIM (Market, Product);
Remember how we said that “CALC DIM” not only does
aggregation but also member formulas? Well, how many member
formulas are there in the Market and Product dimensions?
That’s right: none, so “CALC DIM” is wasting time looking for
formulas that you know aren’t there. For sparse dimensions that
don’t have formulas, there’s a command that only does aggregation
and as such, is usually faster:
AGG (Market, Product);

Hope this would have made you some what comfortable about CALC DIM and AGG

So go on scripting

Excel Add-in Reporting : Tip#1

You may have got requirements from you client that once they retrieve data at the Top level of different dimension, they just need to see the data at the Bottom Level for the same dimension at the same time. However, if you are using Hyperion Reports/Hyperion Financial Reporting for the reporting, you may need to do lots of customization to achieve the above requirement and at the same time, if your dimensions are too deep, there can be performance issues too.

So, you can suggest an Excel Addin report wherein you can set the Excel Addin "Options" to Zoom to Bottom Level.



This will very quick and your client would love to work on excel rather than on Hyperion Reports PDFs

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