Refresh of ASO Plan Type failing while setting up an Alternate Hierarchy in PBCS

I was working on PBCS application where the application had a BSO cube and ASO cube. After some initial setup and dimension creation we were creating an alternate hierarchy in Period dimension. Somehow Cube Refresh was failing for ASO plan type. It seemed that the issue was with the shared member but that was not the problem.

The error message I got for my ASOPlan (cube name) was as follows:

Cube refresh failed with Essbase Cube: ASOPlan, Error code: 1060053, Message:

The message was not clear but the issue was that I had one of the member defined as Ignore in the period hierarchy for ASO. It is suggested that all members must be set as Addition and then it will work (for ASO). This worked for me.

Following is the checklist if you encounter similar issue:
1. Check that the Hierarchy Type of Period Dimension is set to Multiple
2. Check the Hierarchy Type of hierarchies in Period dimension are set to "Stored"
3. Check all the members are set as "Addition" (for ASO)

If the point number 2 from above is not satisfied i.e. if the Hierarchy type is set to "Dynamic" then you may get the following error message:

ASO members with time balance members requires a time dimension to only contain stored hierarchies. Member: <Member name>

After appropriately updating the member / hierarchy, if the issue still persists then try re-creating the alternate hierarchy. I had similar problem post changing the properties and after re-creating the member(s) it worked.

Hope this helps.

SmartView - Install Cube Designer for OAC - Oracle Analytics Cloud

Steps to install Cube Designer is quiet simple but even after following all the steps and/or re-installing SmartView I was not able to see the cube designer under Extension tab / window.

For the Cube Designer to appear in the Extension tab / window, I had to first create a private connection to the cloud instance and then it worked.

So, the steps to install Cube Designer,
1. Create a private connection to the cloud instance.
2. From SmartView Tab, Click Options -> Extensions
3. If you see "Cube Designer", then click "Enable"
4. If you do not see the "Cube Designer", then click on the link "Check for Updates, New Installs and Uninstalls". "Cube Designer" should now appear, then click "Enable".

Hope this helps...

@CalcMgrDateDiff and @CalcMgrGetDatePart

@CalcMgrDateDiff:

Syntax:

@CalcMgrDateDiff(fromDate, toDate, date_part)

date_part options:

• "day"
• "month"
• "week"
• "year"

Example(s):

VAR V_Start = 20160701;
VAR V_End = 20170630;
VAR V_Diff = 0;

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "day");

Returns 364

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "month");

Returns 11

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "week");

Returns 52

V_Diff = @CalcMgrDateDiff(V_Start, V_End, @NAME("year"));

Returns 1

Note that when there exists dimension "Year" you may need to put the date_part inside "@NAME" as you see in the last example.



@CalcMgrGetDatePart:

Syntax:

@CalcMgrGetDatePart(date,date_part_ex)

date_part options:

• "dayofmonth"
• "dayofyear"
• "month"
• "weekofmonth"
• "weekofyear"
• "year

Example(s):

VAR V_Start = 20160701;
VAR V_Num = 0;

V_Num = @CalcMgrGetDatePart (V_Start, @NAME("year"));
 
Returns 2016

V_Num = @CalcMgrGetDatePart (V_Start, "dayofmonth");

Returns 1

"BegBalance" = @CalcMgrGetDatePart (V_Start, "dayofyear");

Returns 183

V_Num = @CalcMgrGetDatePart (V_Start, "month");

Returns 7

V_Num = @CalcMgrGetDatePart (V_Start, "weekofmonth");

Returns 1

V_Num = @CalcMgrGetDatePart (V_Start, "weekofyear");

Returns 27

Understanding @IDESCENDANTS function

It is always good to know as how certain function works so that we can get desired, appropriate and expected results.

@IDESCENDANTS ("Zone"):

@IDESCENDANTS function populates all descendants including the specified member however there is a way by which it retrieves the data.

The following screenshot shows how @IDESCENDANTS populates members. Sequence indicate the order in which it populates the members during calculation.

Also, note that this is same as @IDESCENDANTS ("Zone",0)

@IDESCENDANTS ("Zone",-1):

Using negative integer for example -1, system will fetch / populate all members from level 1 upto the selected member. In short, it will exclude level zeros.

See the screenshot below:

@IDESCENDANTS ("East",3):

When you provide a positive number it takes as generation number relative to hierarchy and not the selected member.

So based on the example shown in the screenshot, system will fetch all generation 3 members along with the selected members. 

Note that if the selected member's generation number is less than or equal to the parameter then system will only fetch the specified member.

For example, for @IDESCENDANTS("ENT_E5",2), system will only fetch "ENT_E5" member as generation number of "ENT_E5" is 3 which is less than 2 (parameter).

Loading Data in Essbase: Have Sparse Dimension Members before Dense Dimension Members

Even after working in Essbase for quite some time, I have always learnt something new every time.

I got an extract from SQL to load data in Essbase. As far as I remember this was the first time when the format of the file was not provided by me or by my other team member. So, the columns received were in random order and I wanted to load data in Essbase. As expected there were few columns which were not required which I had to ignore during load.

I created a rule file and validated it successfully. When I tried to load the data, I got the following error in the error file for each record:

\\ Invalid number in data column

There were many account members as part of field and these were not assigned as data field.

I reviewed the data for each column but could not find any fault and then it was observed that one of the dense dimension column was appearing before the sparse dimension member and so the error. I moved that (sparse dimension) field before the dense dimension field and it worked.

Clearly, the error message was misleading as in many other cases.

This shows that when you unknowingly follow some standards we may not know until we experience it :)

Rule / Script Performance: Condition on Sparse vs Dynamic Calc Member

Conditions in Business rules / Calc Script are quiet common but if not written correctly can take a very long time to execute (which may again depends on the database size and combinations it has to traverse).

Following is the classic example where when condition was placed on upper level of dynamically calculated dense dim member, it took substantial amount of time to execute and complete the script. Whereas when the same dynamic dense member data was copied to a temp stored member and then the stored member was compared, it executed within minutes.

Example Description:
Clear blocks depending on whether there exists any value in a mandatory assumptions and / or PAT.

Account, Period and View  are dense dimensions. The requirement is if there is no data in "Volume_Units" and "PAT" members at "Local" level, clear blocks from "Local","USD","AUD", and "NZD" members. Also, all upper levels of Account dimensions are dynamic calc members i.e. in our case "PAT" is dynamically calculated.

In the following code ,"PAT" is used in the condition and it took around 20 hours to complete.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;

FIX (/*Year*/ "FY15",
/*Scenario*/ "Forecast",
/* Version */ "Working",
/* Currency */ "Local"
)
FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/*Currency*/ "USD","AUD","NZD",
/* Period*/  @Relative("YearTotal",0), "BegBalance"
)
"MTD"
(
IF(("Local"->"Volume_Units" + "Local"->"PAT" + 0 ) == 0)
"MTD" = #MISSING;
  ENDIF
)
ENDFIX
ENDFIX

The following code first copies "PAT" to a temporary stored member and then the stored member is used in the condition. This code just took around 20 minutes to complete.

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;

FIX (/*Year*/ "FY15",
/*Scenario*/ "Forecast",
/* Version */ "Working",
/* Currency */ "Local"
)
FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/* Period*/  @Relative("YearTotal",0), "BegBalance",
/*View*/ "MTD"
)
"TEMP" = "PAT";
ENDFIX

FIX(/*Customer*/  @Relative("Customer",0),
/*Product*/ @Relative("Product",0),
/*Entity*/ @Relative("Entity",0),
/*Currency*/ "USD","AUD","NZD",
/* Period*/  @Relative("YearTotal",0), "BegBalance"
)
"MTD"
(
IF(("Local"->"Volume_Units" + "Local"->"TEMP" + 0 ) == 0)
"MTD" = #MISSING;
  ENDIF
)
ENDFIX
ENDFIX

MDX Queries - Misc

I have always find it difficult to have some examples ready to query using MDX. Here are some examples and will be adding more:

To get the leaf level members:

SELECT 

             {[Time].levels(0).members} 

ON ROWS

To get all members of one dimension:

SELECT 

             {[Time].members} 

ON ROWS

To get asymmetric columns:

SELECT {

([Dim1 Member1],[ Dim2 Member1]),

([Dim1 Member2], Dim2 Member2)

} ON COLUMNS

MDX Query - Count Level Zero Members

In one of our implementation I wanted to know the number of level zero members in a dimension and after some research finally ended up having following MDX query:

WITH

Member [Product].[ProCount] AS 'Count([Product].Levels(0).members)'

SELECT {[Year]} on AXIS(0),

{[Product].[ProCount]} on Axis(1)

FROM [Sample.Basic];

Essbase DataExport

We all know about the limitation on the Dataexport command as we do not have much control over the formatting and ordering of columns. But one is sure that this is one of the fastest way to get the output as compared to Report Script.

An interesting observation was when using DATAEXPORTOPTIONS.

I wanted to extract level zero data from all dimensions except one upper level member from measure dimension. Measure hierarchy was something as follows and wanted to extract value of Balance which is upper level member.

Balance
 - Calculated Balance (+)
 - Adjusted Balance (+)

To my surprise, following code gave the required output:

SET DATAEXPORTOPTIONS
{
DataExportLevel LEVEL0;
}

FIX("Balance")

  DATAEXPORT "File" "," "output1.out";

ENDFIX

Based on this, one can extract upper level member even when DataExportLevel is set to LEVEL0.

Function: @Return

@Return is one of the very useful function that is introduced in 11x. I have been using it more frequently now and some of the cases are listed below:
Syntax:

@RETURN ("ErrorMessage", ERROR)

1. In custom Workforce planning when business need to add TBH (To be Hired) with basic information such as Start Date, Grade, salary. While adding, system need to validate that date and salary is provided and this is where @return comes handy. If validation fails, one can use @Return function with appropriate message.

2. In one of the requirements, there was a need to archive existing projects to dummy members (user selected) and if required restore back to existing available project member. system must not allow user to move project to a member that already has data. So, it was necessary to give appropriate message to the user. I used the following command in case the validation fails:


@RETURN(@CONCATENATE
(
@CONCATENATE
(
            @CONCATENATE("ERROR: Failed to archive selected project [", @NAME({Project})),
@CONCATENATE("] under Entity [", @NAME(@CURRMBR("Entity")))
)
,
@CONCATENATE
(
@CONCATENATE("]. The target project [", @NAME({Archive_Project_to})),
"] is already in use. Try using another new / archived project."
)
)
, ERROR);

The message looks as follows:

"ERROR: Failed to archive selected project [Project1] under Entity [Entity1]. The target project [Temp_Proj1] is already in use. Try using another new / archived project."

Composite rule v/s ruleset

Recently, I was working extensively on developing business rules in calculation manager. Wherever necessary I have an habit of using variables. In one instance, I wanted to run 2 rules on a save of a web-form. So what I did was created a new rule and added the required 2 rules. I was not expecting any issues as both these rule were running as expected. But when I compiled, it threw an error of duplicate declaration of variable....I used / defined same variables in both the forms.

I was initially surprised but later realized that it considers it as one rule and so the problem.

I then created a rule-set, added the required rules and worked.