21 September, 2018
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
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)
After creating the rules file, right click on the database and
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 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
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 )
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 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
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:
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(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.
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .