Sunday, January 13, 2013
Peloton $100 Discount Code for KScope13 Registration
Peloton is the official Gold Sponsor of KScope13. The conference takes place June 23-27 at the Sheraton New Orleans, and we hope you will be able to attend. This is the place to be to learn from the world’s leading experts on Oracle technology. KScope offers great content from developers, administrators, architects, and business users. Don’t miss any of our exciting upcoming presentations on Business Intelligence and Planning.
Register before the March 25, 2013 Early Bird deadline and save money off the Advanced Rate. For an additional $100 discount, email insights@pelotongroup.com or fill out the form on the link below to receive the Peloton discount code immediately.
http://pelotongroup.com/clients-and-insights/events/kscope-13
Hope to see you there!
Thursday, January 3, 2013
Enabling / Disabling Connects in Essbase During Separate App Copy (v11.1.1.3)
I found something interesting recently in our EPM 11.1.1.3 environment regarding app copies affecting unrelated processes on the same Essbase server. For this example lets assume I have an Essbase server with 3 applications on it; App1, App2, and App3.
When running an app copy from App1 to App2 using the syntax below, you cannot enable or disable connects in App3.
create or replace application App2 as App1;
When executing the following MaxL to disable connects, the system will allow me to log into the Server and log out any active users in the App, but will stall on the line that disables the connects until the app copy has completed. This is very strange behavior since App3s commands should have nothing to do with the app copy going on between App1 and App2.
login Scott MyPassword on MyServer; /* Logout current users */ alter system logout session on application App3 force; alter application App3 disable connects; alter application App3 disable commands; logout; exit;
This is also the same for enabling connections as well. I have not yet came across another MaxL function that is affected by this app copy other than the 2 mentioned above. In Essbase v6.5, there was an issue with app copies that froze up all users on the server. I am wondering if this is a leftover bug from back in those days.
Currently, I have an Oracle SR open that will hopefully shine some light on the subject. So far, the response I am getting is that I should time my batch scripts differently... I will keep this post updated as I find out more information about this and whether or not it affects the latest version of the EPM suite.
Thanks for reading!
Thursday, December 27, 2012
Flat File Modifier Header Modification Option (v3.0.2)
Hey Everyone,
The Flat File Modifier Utility has been updated! I have fixed a few minor bugs and added a very exciting new feature. The utility now allows for Header modification on extremely large data files!
This new feature will allow you to Add, Remove, or Replace header rows in files that could not normally be opened in a text editor due to their size.
The screenshot below shows the sample data file that I used.
There are 2 main sections in the header options tab; "Rows to Remove" and "Add Header". These sections work independently of each other to give maximum flexibility in modifying the header of these files.
The Rows to Remove section will delete the specified number of rows from the top of the loaded file and the Add Header section will add the specified text to the top of the file.
The screenshots below show the preview capabilities of the header modification section.
You can get the latest version from the Utilities Section Link below.
Flat File Modifier in the Utilities Section
Friday, December 21, 2012
Properly Passing Commands With Your Batch and MaxL Scripts
I know that this may be a bit of a beginner topic for some of you, but I feel this is an essential part of creating the brotherly bond between your batch scripts and your MaxL scripts. This post will also contain some of my best practices around this topic which will make your life much easier down the road.
Passing commands from one batch script to another is relatively simple, as long as you adhere to a standard when doing so. For this example, we will pass the following information from one batch to another.
- Server Name
- Application Name
- Database Name
- Username
- Password
First we will create a "parent" batch script. This script will use the call function to start up another "child" batch script within the same cmd window. Below is an example of the "parent" script we will use.
Example 1 (Parent Batch Script):
REM ******************************************************* REM Script Name: parent.bat REM Purpose: Example of how to pass commands REM Process: High level process list REM ******************************************************* @ECHO ON REM ****************** Call Child Batch ******************* CALL child.bat server1 application1 database1 scott password
This example above should be pretty self explanatory The first thing we are doing is turning on the ECHO command to enable screen output and then we are calling the child.bat to execute. You will notice in this example how we pass the connection information to the child.
It is very important to note that the space represents a divider among multiple commands. So if you are passing a value to the child script that has a space in it, you must put it in double quotes. See the example line below where I have changed my password to one with a space in it.
CALL child.bat server1 application1 database1 scott "pass word"
It also does not hurt to put the double quotes around commands that do not have a space either. (see below)
CALL child.bat "server1" "application1" "database1" "scott" "pass word"
Example 2 (Child Batch Script):
REM ******************************************************* REM Script Name: child.bat REM Purpose: Example of how to receive commands REM and pass them to a MaxL Script REM Process: High level process list REM Passed Commands: 1) Server Name REM 2) Application Name REM 3) Database Name REM 4) UserName REM 5) Password REM ******************************************************* REM ****************** Set Variables ********************** SET ssd_server_name=$1 SET ssd_app_name=$2 SET ssd_db_name=$3 SET ssd_user_name=$4 SET ssd_password=$5 REM ****************** End Variables ********************** @ECHO ON REM ****************** Call Child Maxl Script ************* CALL essmsh child_maxl.mxl %ssd_server_name% %ssd_app_name% %ssd_db_name% "%ssd_user_name%" "%ssd_password%"
You will notice a few things in this example. First, the way that the child batch script accepts the passed commands is with a dollar sign followed by a number. This number represents the numeric position of the passed command. Second, you will notice that I have assigned all of these passed commands to local variables. This will help determine what the batch is expecting to receive from the parent batch script and also make referencing these command values much more user friendly. Lastly, I have put quotes around some variables in the MaxL script call that could potentially have spaces in them.
MaxL scripts are a bit different in how they handle and accept these commands. Below, I have posted the script of my test MaxL (child_maxl.mxl) that we called above.
Example 3 (Child MaxL Script):
/*******************************************************
Script Name: child_maxl.mxl
Purpose: Example of how to receive commands
in a MaxL script
Process: High level process list
Passed Commands: 1) Server Name
2) Application Name
3) Database Name
4) UserName
5) Password
*******************************************************/
/****************** Set Variables **********************/
SET ssd_server_name=$1;
SET ssd_app_name=$2;
SET ssd_db_name=$3;
SET ssd_user_name=$4;
SET ssd_password=$5;
SET mxl_logfile_path="C:\Logs\child_maxl.log"
/****************** End Variables **********************/
spool on to $mxl_logfile_path;
login $ssd_user_name $ssd_password on $ssd_server_name;
logout;
spool off;
exit;
You can see that the MaxL script is similar to the batch syntax with some slight variations. In this case, I passed 5 commands to the MaxL script, but only used 3 of them.
That's about it for a high level overview of how to pass commands from batch scripts to MaxL scripts.
As always, post your questions or comments in the comment section below and I will respond.
Friday, December 14, 2012
Scott's Flat File Modifier Utility Released
Hey Everyone,
I am proud to announce that the initial release of my Flat File Modifier utility is now available for download. This utility is a non-destructive search and replace utility on steroids.
The Flat File Modifier will analyze any existing text file line by line and give you the following modification options:
- Replace (Single Pass)
- This option will read the text file line by line and do a single pass on that line looking for a value and replacing it. The single pass option will only replace the first instance of the search value that it encounters on that line then continue to the next.
- Replace (Multi Pass)
- This option has the same functionality as the first but does a multiple pass on each line. This option will ensure that all occurrences of the search value will be replaced.
- Remove Row
- The Remove Row option will search through the file and when it finds the search value, it will remove that row from the destination file.
- Keep Only Row
- This option will perform similarly to the Remove Row option but it will only keep rows in the destination file where it finds the search text.
Since this application is non-destructive, it will only read from the original input file and create a copy of this file with the modifications specified.
Full list of features of the Flat File Modifier:
- Finds and replaces text in any size file without loading it into RAM
- Line by line, non-destructive text replacement
- Analyzes file for Row Count, File Size, and Modified Date
- Allows preview of any size text file
- Optional advanced sequencing of text replacement capabilities
- Exporting and importing of proprietary sequence files
- High Performance Mode
Where to get the latest version:
I hope this utility helps you out! I know it has saved me many times.
Friday, December 7, 2012
How Not to Structure Your Essbase Fix Statements
Fix statements... The most essential concept to understand when writing calc scripts in Essbase. I am going to be talking a little bit about best practices around fix statements and what not to do with them. I will be referencing the Sample.Basic database that we all love so much just to keep the code simple.
Below is a high level screenshot of the Sample.Basic outline that we will reference for this discussion:
fix(jan,feb,mar);
fix(actual);
fix("100-10");
fix("new york");
fix(sales,cogs);
clearblock all;
endfix;
endfix;
endfix;
endfix;
endfix;
#1: "The Zipper" or "Pyramid"
I am sure the first thing you notice about this example is that Jimmy has nested all of his FIX statements separately, calling out a member from each dimension in order to clear a select set of blocks.This method may look nice to some, but to an experienced Essbase developer, this is not nice. In fact, this is actually a less efficient way of clearing these blocks. By opening a new fix statement for every dimension he is actually slowing down the calculation unnecessarily. You can simply put all of the members in one statement to make this much more efficient.
#2: "Manic Semicolons"
Jimmy has added more sloppiness to his calc script. The Manic Semicolon happens more often than you would think. Some developers, like Jimmy, do not realize that a FIX statement does not need a semicolon. This really doesn't hurt performance, but it sure does make Jimmy look like he does not understand what he is doing.#3: "Unrestrained Members"
The last thing you ever want to do is leave any member unrestrained like Jimmy has done here. Members should always be contained within quotes. This may just sound like a pet peeve, but it can save headaches down the road. You will notice that "New York" and "100-10" are contained in quotes. This is not by accident. These members would cause the calc script to fail validation if they did not have these protective quotes around them. The reason for this is because New York has a space in the member name and the system will try to resolve it as two separate members, "New" and "York". The product 100-10 also would fail validation because the system assumes this is the number 100 minus 10 or 90. No good...#4: "Underutilized Functions"
Jimmy is what I would qualify as a caveman coder, similar to the "guy" that unplugs a lamp to turn it off when he could have just flipped the switch on the wall. You will notice in his first FIX Statement, he is trying to include the months in the first quarter of the year in his script. Lucky for him, there is an easy to use function called "@CHILDREN" that he can use instead of hard coding the first 3 months. I realize that this is not an extreme case, but it is a good idea to get in the habit of thinking about your calc scripts in this dynamic way. The syntax that should be used in this case is @CHILDREN("Qtr1") which will automatically grab Jan, Feb, and Mar.#5: No Respect for Next Person
Clearly Jimmy has no respect for the next developer that will be modifying this script in the future. You can tell this from 2 key facts, proper capitalization not used and the lack of comments.Proper capitalization may not seem very important, but it really improves the overall readability of the script syntax. It really lets the developer after you know that you took your time with the script. Some guidelines for properly capitalizing items within year script are as follows. Member names should always match the case of how they are stored in your outline. Functions, like FIX and @CHILDREN should be in a caps to make them easier to read.
Comments, need I say more? Commenting code is something that every programmer should have ingrained in their coding techniques. This not only helps the developer after you figure out what the script is doing, but helps you debug as you are developing. Another thing to add to the script is a header comment that describes which script you are looking at, what is is suppose to do, when it was written, who wrote it, and what the high level process is of the calc script. This will save many headaches down the road.
#6: Overruled Outline Order
Looks like Jimmy has done it again... His nested FIX statements are not in outline order. This order does not affect the script from a technical standpoint, but it sure does make it easier to figure out which members are from each dimension (see #5 above). What Jimmy should have done is put all of his members in one FIX statement and group them by dimension. You will notice in the best practice example below, this has been organized properly.How Should It Really Look?
After coaching Jimmy through my best practices, he re-wrote his script and gave it back to me. Much better! What do you think?/************************************************
Script Name: CLR_EX.csc
Script Purpose: This script will clear Actual Sales and COGS for New York for the "100-10" product
Date Created: 2012-06-21
Created By: Jimmy Smith (ACME Consulting)
Process:
1. Clear data
************************************************/
FIX(
@CHILDREN("Qtr1"), /* Year */
"Sales", "COGS", /* Measure */
"100-10", /* Product */
"New York", /* Market */
"Actual" /* Scenario */
)
CLEARBLOCK ALL;
ENDFIX /* Year, Measure, Product, Market, Scenario */
Please note that the best practices discussed here are the ones that I have collected over the years that that I personally believe are the best ones. There is no law of the land in this space, but these are the best practices that I drive on all of my projects.
Wednesday, December 5, 2012
Innovative Way to Structure Essbase/Planning Account Dimension
Hey Everyone,
I wanted to share with you today a pretty innovative, and different way to structure an account dimension in Essbase or Planning. This method is not new and has been passed down from generation to generation from ancient times when planning could be installed from 3.5" floppy disks, slowly evolving into what I am going to describe below. This will not be ideal to use in all situations, but when it fits, it fits very well.
I am going to cut to the chase and just get right into it. Below you will find a sample structure of this method for use in a planning application and some notes below about why I personally like and dislike about this structure.
- Account
- Transferred_Accounts (~)
- Loaded_Sales (Stored) (~)
- Loaded_COGS (Stored) (~)
- Input_Accounts (~)
- Sales_Adjustment (Stored) (~)
- COGS_Adjustment (Stored) (~)
- Calculated_Accounts (~)
- Calculated_Sales (Stored) (~)
- Calculated_COGS (Stored) (~)
- Reporting_Accounts (~)
- Total_Earnings (Dynamic Calc) (+)
- Sales (Dynamic Calc) (+)
- Loaded_Sales (Shared) (+)
- Total_Adjusted_Sales (Dynamic Calc) (+)
- Calculated_Sales (Shared) (+)
- Sales_Adjustment (Shared) (+)
- COGS (Dynamic Calc) (-)
- Loaded_COGS (Shared) (+)
- Total_Adjusted_COGS (Dynamic Calc) (+)
- Calculated_COGS (Shared) (+)
- COGS_Adjustment (Shared) (+)
So obviously this example above is pretty simple, but what I am trying to show here is how you can bucketize most of the accounts in a flat list and use them as shared members in the Reporting Hierarchy. With a flat list of Transferred, Input, and Calculated Accounts it makes processing and locating accounts much easier.
Something to note is that all members under the Reporting_Accounts parent, are either dynamic calc or shared.
Something to note is that all members under the Reporting_Accounts parent, are either dynamic calc or shared.
I know this is going to get asked, so I will address it right now... "But Scott, you are telling me that in the reporting account structure my Sales = Loaded_Sales + Calculated_Sales + Sales_Adjustment? That is not right..." The answer is like Schrödinger's cat, yes and no. Yes, the outline physically aggregates these members. No, because loaded data should be in the Actuals scenario and calculated/adjustment data should be in the Plan scenario.
Below is my list of Pro's and Con's for this method.
Pro's
- Simplifies calc scripts. For example, if you had a calc script that cleared all of the input accounts for a specific period, you could use @CHILDREN("Input_Accounts") within the fix statement. This will avoid having to maintain the calc script if a new input account is added to the structure.
- This works for data exports and transfers as well.
- This method makes locating accounts much easier as you can sort the flat lists of accounts by name in Essbase.
- This method also simplifies security. For example, you can apply security easily to all Input accounts in one place without having to pick through a large reporting hierarchy. --Thanks MG!
Con's
- This adds complexity for end-users since they need to be trained to only pull from the Reporting hierarchy at the bottom of the dimension.
- Since this in a non-conventional way to structure your accounts, some administrators might think you are crazy. There is a very small gap between crazy and innovative.
I would love to hear some feedback on this approach to see what some of you other experts think of this. Also as always, let me know if you find any errors in what I am posting. Thanks for reading!
Subscribe to:
Posts (Atom)