LOADING FROM SQL DB in Hyperion EssBase

LOADING FROM SQL DB:-

  SIMPLE SELECT STATEMENTS:- SELECT MONTH,YEAR, SNAME,PNAME,SELES COST FROM SALES_FACTA, MARKET B,PRODUCTS C WHARE A.MID=B.MID AND A.PID=C.PID.   The data will be look like as below. JAN   AP COKE 1000   900 JAN   TN COKE 1000   900

• To connect to SQL database (for example SQL server , oracle server etc ), create a DSN using ODBC drivers

Select control panel Double click on the administration tools. Double click the data source (ODBC) Click system DSN. Click add button select appropriate ODBC driver . Click Finish. Another screen appears . enter a DSN name . for oracle enter the TNS Name (also called as lost string in the lab it is ORCL)

• Open data prep editor. Select file ->open SQL
• Enter the select statement the select window , select appropriate DSN.
• Click OK/ RETRIVE. Enter the user name and password . make sure data load field button is selected.
• Select field properties . select data load properties tab.
• For this example , select time for the first column (by double click it ) click NEXT. Select products.
• Click NEXT select SELES , click NEXT select COST . no need to select DATA field CHECK box . click validates and save the rules file.

  After creating the rules file, right click on the database and

• Click load data. Another screen appears.
• Select SQL under type enter username and password.
• Select overwrite check box, click OK.

Calculations:-

In Essbase you have to first build all the dimensions, then we have to load data. after that we have to calculate the database . To calculate the database right click on the database and click execute calculations. By default , when the database is created , a default calculations script get created . this script calculates all possible combinations in Essbase . the data is updated in the page file Sample data for loading:- JAN 2007             AP   COKE   SALES     1000 JAN 2007             AP   PEPSI   SALES     1200 JAN 2007             TN   COKE   SALES   1100 JAN 2007             TN   PEPSI   SALES     1000 JAN 2007             AP   COKE   SALES     900 JAN 2007             AP   PEPSI   SALES     900 JAN 2007             TN   COKE   SALES     950 JAN 2007             TN   PEPSI   SALES     950   Assume the above data for FEB also . after calculating the database ,we can see this data is high level combinations sample calculated data   JAN 2007             SI     COKE       SALES   2100 JAN 2007             SI     COKE     SALES     1850 JAN 2007             AP[   COKE     SALES     100 JAN 2007             SI     COKE       SALES   2200 JAN 2007             SI     COKE     SALES     1800 JAN 2007             AP     COKE     SALES       400   Like we this can get so many other combination.   Member consolidation properties by default when a member is added in the outline the member is added in the outline the member will get as the operator. The consolidation symbol will affect the parent . for example QTRJ 2007 is PERENT and JAN 2007 , FEB 2007, and MAR 2007 are child members . then QRT1 2007 is calculated as JAN 2007+FEB 2007 (by default) they are 6 consolidation operators available in the tool. Those are +,-,*,%,/,~. If a child members has Tilde (~)operator , then the child member is not ADDED to the parent. For example , if MARCH 2007 has ~ operator , then QTR1 2007 is Calculated from JAN 2007 and FEB 2007 only. MAR 2007 will not be added .  

Calculation scripts:-

Calculation scripts are used to partially calculate the database or copy data from same member combinations . calculation scripts can also be used to clear data in some member combination. To create calculation script , right click on the calculation script and click create calculation script . editor appears . enter the necessary commands. Validate and save the script , the extension of the script is csc and it is saved in ARBORPATH/app/app-name/db-name/* .csc

• The member name must be enclosed in double quotes if it has spaces on special characters
• Each command must be ended with semi colon.

Calculating a parent member:- after loading data , if we want to calculate only parent member we can simply type the parent member in the script . when we execute , the parent member will get calculated ,but the members remaining dimensions are level 0 members. EX:”QTR1 2007”, An this example “QTR1 2007” parent member will get calculated for level 0 members from products , geography and measures. QTRL 2007 , coke , ap sales QTRL 2007 , coke , tn sales QTRL 2007 , pepsi , ap sales QTRL 2007 , pepsi, tn   sales   These are some sample record that calculated after running the above command . the below record QTRL 2007, soda, ap, sales, will not get calculated , because soda is level 1 member from products dimension (only level o members from remaining dimensions will get calculated ) EX:- QTRL 200 SODA; In this example analytical services calculates all QTR1 2007 combinations (level 0 members from containing dimensions) apart from the above combinations, it also calculates QTRL 2007 ,SODA combinations(level 0 members remaining and measures dimensions )  

CALC DIM:-

This command calculates the formulas the aggregation associated with each member of all specified dimensions . the order in which dimensions are calculated depends on whether they are dense or sparse. All of the dense dimensions are calculated first, in the order that dense dimensions appear in dim list . the sparse dimensions are then calculated in a similar order . CALC DIM (dense1, sparse2, dense2); In the above example, the calculation order would be : dense1,dense2,sparse1,spase2. If your dimensions need to be calculated in particular order use separate CALCDIM commands, as in CALC DIM (dense1); CALC DIM (sparse1); CALC DIM (sparse2); CALC DIM (dense2); Calculation order:-Essbase first calculates the dimension that is tagged as accounts whether is sparse or dense . then calculates “time” whether it is sparse or dense then it calculates all dense dimensions , then calculates all sparse dimensions if time and accounts are not there, it calculates dense dimensions in the order in the outline and then it calculates all sparse dimensions in the same order as they appear in the outline  

CLEARDATA:-

CLEARDATA mbrname; This command clears data for all combinations of the member name . EX:-CLEAR DATA sales; Clears all sales data from the database (for all markets (ap,si,tn,ni,up,delhi) products(coke, pepsi, soda),time(for all months quarter and years etc )) CLEARDAT SELES->A.P; Clear data from only AP member only (for all products (ap,si,ni,tn,delhi) time (for all months and quarter and years etc)). CLEARDAT SELES > AP COKE:- Clears all sales data for ap member and for products COKE only! (for all time (for all months quarters and years only)) CLEARDAT SALES ->AP->COKE->”JAN 2007”, (CLEARS ONLY ONE RECORD ) DATA COPY MBRNAME1 TO MBRNAME2; Copies data from one member combination to other member combinations. DATACOPY ACTUAL TO BUDGET; Copies all actual data (for all combinations )to all budget data. DATACOPY TN-> ACTUAL TO AP -> BUDGET. Copies actual tn data to budget -> ap combinations. DATACOPY TN->ACTUAL->SALES TO AP -> BUDGET->SALES If we don’t mention some combination, in to the clause, then system assumes the same members that are mentioned in the form clause

1. DATACOPYTN->ACTUAL->SELES TO BUDGET.THIS IS SAME AS GIVING

DATACOPY TN ->ACTUAL->SALES TO TN->BUDGET->SALES. NOTE: calculations functions are explained in the course material. For more examples, refer technical reference, pdf   file FIX   ENDFIX The fix command restricts the calculations or any commands to the part of the DB(subse of the database). All commands nested between fix and end fix are restricted to these member that are mentioned in the FIX statement. Rules:

• More than one member from the same dimension can be then EIX statement.
• The members in a fix statement can be separated by comma(,).
• We can use and /or operators in fix statement.
• The fix command can only be used in calculation script. It cannot be used in a formula. Use IF command can be nested with in another FIX command. Each fix statement must end with END fix command.
• We cannot use calc all in a FIX statement.
• We cannot use CALC DIM in a FIX statement, if we restrict a member from the same dimension(That means if we mention the member in the FIX statement)
• If the FIX statement returns statement , then the FIX is ignored and the commands will run on the entire database

EXAMPLES:

Fix (“JAN 2007, FEB 2007, MAR 2007”) BUDGET=ACTUAL*1. ENDFIX The above FIX command can also be written as mentioned below FIX(@CHILDREN (“QTR1 2007”)) BUDGET=ACTUAL*1.1; ENDFIX NESTEDFIX:- FIX(“JAN2007”, COKE) COMM=SALES*0.1; ENDFIX FIX( ‘JAN2007’, PEPSI) COMM=SALES*0.2; ENDFIX We can write the above two separate FIX commands   is a nested FIX FIX(“JAN 2007”) FIX(COKE) COMM=SALES*0.1; ENDFIX FIX(PEPSI) COMM=SALES*0.2; ENDFIX ENDFIX IF…..END IF:- IF(CONDITION)STATEMENT; [ELSE….STETEMENT ]; ENDIF; Each if statement must be ended with ENDIF command Some functions that we can use with IF statement :@ISMBR(MBRNAME), @ISCHILD(MBRNAME),@ISDESC(MBRNAME),@ISICHILD(MBRNAME),@ISIDESC (MBRNAME)etc Entire IF statement must be enclosed with in a member name . SET   CALCPARALLEL SET CALCPARALLEL n;(write this command in calculation script ) By default Essbase calculates the database in serial mode. We can use the above command to make use of multi processing or multi threading concept of the operating system . Number can take any value between 1 and 4 can be considered as 4 only We can also write CALCPARALLEL n in a cfg file . when we modify the configuration file we need to restart the server .

@RELATIVE:-

@RELATIVE(MBRNAME, NUMBER) Number is a number that takes any value either negative , zero or positive numbers. Negative means level numbers . positive means generation numbers. Zero means level 0 numbers. Intelligent calculation: analytic services, after calculating the database, flags the blocks with clean status . when we load data in the child member combination, or when modify the data value in the child member , automatically, the parent becomes dirty . after loading database ,the status on all blocks will be clean for subsequent data loading ,analytic services, calculates only dirty blocks and it doesn’t calculate clean blocks . this process of calculating only dirty block is called intelligent calculation.