Issue with selecting "Number" and "String" members together in the rule file - What is an alternative solution?

As we all know, rule files are used for loading data to ASO,BSO cubes.

This Post is all about, how to make selection between two different types of members in rule file( Like between Numeric member and String member).

For Example, your Account dimension looks like below. Here, "100" is a numeric account and "control Accounts" is a string account member.
Account
    100
    101
    102
    103
    200
    201
    Control Accounts

If you want to select data for 1XX  accounts, “Control Accounts” and exclude 2XX from the data file.
Usually we add selection statements in “select record”  window in rule file, which looks like below. 

    

As we have selected  “OR” in the "Boolean" selection , it has to select accounts less than 200 (which will return 100 series accounts) along with Control Accounts records .

But this will not give you expected results, because ESSBASE will not allow us to make selection on a field using  "String" and "Number" types together. Workaround for this is ,go to the Field properties and replace your string member with some numeric number as shown below.

 

We are just replacing all the occurrences of "Control Accounts" with some dummy numeric number(Make sure it  will not exist in your outline ).

Go back to select record window(checkout the below screenshot) and make the changes .Here 12345 is nothing but "Control Accounts", but now its numeric data type.So that we can perform Boolean operation between these two selection.

Author : Deepa

Data Copy in ASO Applications using calculation script

Data copy in a BSO application ( Ex: copy data from one version to another ) is straight forward. You can write a calculation script, and use DATACOPY command. Easy, Peasy, Japaneesy!!!

But, ASO is a different story.

We usually export the required data from ASO application and import it back using rule file by changing the member names in rule file.

This approach is not faster and needs extra automation scripts.

But, things have changed after Oracle introduced calculation scripts in ASO application. We can do things like simple math, minor allocations and datacopy  using ASO calculation scripts.

Even though it is not a straight forward calculation script in BSO, you can achieve it with little MDX scripting knowledge and patience (Patience because, it will take some time to write MDX expression to make sure you are not "fixing" on derived, parent or dynamic cells in POV section).

Let’s see how we can do it.

Take ASOsamp:sample as an example.

If we want to copy all data from "Prev Year" to "Curr Year", We have to create a  MAXL script like below,

execute calculation on database ASOSamp.Sample with local script_file "/hyperion/Test_ASO_Calc.csc"

 POV " Crossjoin(Descendants([MTD],Time.Levels(0)),

Crossjoin({[Original Price],[Price Paid],[Returns],[Units],[Transactions]},

Crossjoin(Descendants([Transaction Type],[Transaction type].Levels(0)),

Crossjoin(Descendants([Payment Type], [Payment Type].Levels(0)),

Crossjoin(Descendants(Promotions, promotions.Levels(0)),

Crossjoin(Descendants(Age, Age.Levels(0)),

Crossjoin(Descendants([income Level],[Income Level].Levels(0)),

Crossjoin(Descendants(Geography, Geography.Levels(0)),

Crossjoin(Descendants(Stores,Stores.Levels(0)),

Descendants(Products,Products.Levels(0)))))))))))"

SourceRegion "{[Prev Year],[Curr Year]}";

My custom calculation script, Test_ASO_Calc.csc look like

[Curr Year] := [Prev Year];

This process runs faster like DATACOPY in BSO. 

Loading Data to ASO Cube - How "Override" Option Works.

Data Load Properties in Rule file like Overwrite and Add to existing will not have any impact on data load for ASO cubes. where as  subtract from existing will work as expected.We have to mention the load type while importing data from the “Load Buffer” in MAXL statement ( "Load data" window if you are loading from EAS). Below are some examples to explain how these properties will work.

1) Override (Default): By Default, ASO will use “override” property. Data will over write only for the intersections we load.

          

Syntax:

import database 'AppName'.'DBname' data from data_file '/Folder/Newfile.txt' using server rules_file 'Test' to load_buffer    with buffer_id 1  on error write to 'Newfile.txt';

import database 'AppName'.'DBname' data from load_buffer with buffer_id 1;

                                                                (or)

import database 'AppName'.'DBname' data from load_buffer with buffer_id 1 override values;

 For example,

 Existing Data: Jan-> Sales = 10

                                               Feb-> Sales =20

                                  Loading: Feb-> Sales =30

                                               Mar-> Sales =40

                                After Load:Jan-> Sales =10

                                                Feb-> Sales =30

Mar-> Sales =40.

2)Override all data:   It works in a different way. Unlike override, this will replace whole data exist in the cube with the data file we load . Data cells will be cleared in the cube if you are not loading to those cells.

Syntax:

 import database 'AppName'.'DBname' data from load_buffer with buffer_id 1 override all data;

For example, 

                          Existing data:Jan -> Sales = 25

                                              Feb -> Sales = 569

                                 Loading:Jan->Sales = 34

                              After Load:Jan -> Sales = 34

                                              Feb -> Sales = #Mi

3) Override incremental data :  Unlike above all the override options, override incremental data deals with slices of data. When you specify this option, data will be loaded to incremental slice. You will get sum of both primary slice and incremental slice when you pull the data.

Syntax:

import database 'AppName'.'DBname' data from load_buffer with buffer_id 1 override incremental data;

For examples, if we have data like below when are loading the first time

            

                        Existing data:Jan -> Sales = 25

                                            Feb -> Sales = 569

                                                 

                               Loading:Jan -> Sales = 34

                                           Mar -> Sales = 100

                           After Load:Jan -> Sales = 59 (25 Primary Slice + 34 Incremental Slice)

                                           Feb-> Sales = 569(Primary Slice)

                                           Mar - >Sales = 100 (Incr Slice)

When we are loading the same file with different data for the next time it looks like below.

                    Existing data:Jan -> Sales = 59  (25 Primary Slice + 34 Incremental Slice)

                                        Feb-> Sales = 569.

                                        Mar -> Sales = 100 (Incr Slice)

                           

                           Loading: Jan-> Sales = 5

                        After Load:Jan-> Sales = 30 (25 Primary Slice + 5 Incr Slice)

                                        Feb-> Sales = 569

                                        Mar -> Sales = #Mi

By looking at the above two examples we can conclude that, every time it will override all the data in incremental slice but it will not change primary slice. Data for Mar ->Sales set to #mi since the data was not loaded during second time.

The difference between “add values” and “override incremental data”  is, “add values” will always add the data to the primary slice where as the “override incremental data” will override the data within the incremental slice.

Author : Deepa.