Hyperion Essbase architecture

  Hyperion Essbase architecture Hyperion Essbase follows 3-tier architecture. It contists of 3 layers.Hyperion Essbase Architecture

• Database Tier
• Middle Tier
• Client Tier

Database Tier : (Analytic Server) One or more Analytical servers that store and process multi dimensional DB information. Client Tier : (Essbase Administration services console, EAS console) A Java-based client console that provides user interface to manage the analytic service environment. Middle Tier (EAS Server) : One or more Analytic Servers store and process multi dimensional database information. Analytic Servers are installed separately from Administration Services.   A version EAS server, Essbase server and EAS Console and in version 9.3,all these are called AAS server, analytical server AAS console AAS server  Analytical Server And AAS console can be on single machine or they can be on separate machine .AAS server or ESSBASE server can also be installed on windows/AIX platform. Apart from these Hyperion shared services server and Hyperion licensing server were also there in version, but we need not  install these servers in version 7. In version seven we can use either  licensing file or licensing  sever. APPLICTION AND DATABASES:- An applications is a folder structure that contain one are more analytic services databases and related files. Analytic services applications and databases usually reside on the Analytical server . the server computer can store multiple applications. To create application, log on to Essbase server using AAS console through AAS server

• Double click on the Essbase server
• Right click on the Application
• Click create new application
• Enter application Name .the application Name can take a maximum of 8 characters. For uni-code applications Name can take maximum of 30 characters.

When an application is created, a folder get appeared on the server under the following path. ARBORPATH/app/ ARBORPATH:- when Essbase software is installed on the Essbase server, Essbase path gets created. This path is called ARBORPATH. To seethe ARBORPATH, Right click on the Essbase server And click Edi properties, click environment tab ARBORPATH will be seen. EX:c:/Hyperion/analytic services DATABASE: A database is a folder that contains objects such as outline, rules files, calculation script, report scripts and multi dimensional data. To create Database: Double click on the applications and right click on database . Enter the database name can take a maximum of 8 characters for non-uni-code applications. If it is uni-code application the name of the database can take maximum of 30 characters. The database can be created in the following calendar. ARBORPATH/APP/app-name/ OUTLINE:- As soon as Database is created. Double click on the outline and click add children to add dimensions. Outline consist of hierarchies. A Dimension consist of members . A Hierarchy is nothing but structural relationship among members. It gives parent child relation ship among members. The out line will also gives consolidation operators. The file is stored with OTL extension under the following path. ARBORPATH/APP? app-name/database name/databasename.otl The name of the outline can be changed only by re-naming the database the name of the outline can not be changed directly.

• A parent is member that has a branch below it for example margin is a parent member for sales and cost of goods sold
• A child is a member that has a parent above it for example, sales and cost of goods sold are children of the parent margin.
• SIBLINGS are child members of the same immediate parent, at the same generation. for example, same and cost of good sold are siblings(they both have the parent margin), but marketing at the same branch level is not a Sibling because its parent is total expresses.
• DESCENDANTS are a members in the branches below a parent For example, profit, inventory, and Ratios a Descendants of measures. The children of profit, inventory and ratios are also descendants of measures.
• ANCESTORS are all member in branches above a member. For Example, margin, profit and measures are Ancestors of sales.
• The Roots the top member in a branch .measures is the root of profit, inventory, ratios and the children of profit, inventory and ratios.
• Leaf member have no children. They are also referred to detail members level 0  member, and leaf nodes. for example, opening inventory, additions and ending inventory are leaf members.
• Generation refers to a consolidation level with in a dimension. A root branch of the tree, i.e. dimension is generation 1. Generation numbers increase as you count from the root toward the leaf member .
• Levels refer to a consolidation level with in a dimension. that leaf member is at    level 0  and the parent member of level 0 is at level 1 and so on .

RULES FILES:- Rules files are used to build the dimensions and to load data into Essbase database. rules files are stored with .RUL extension on the server in the following path . ARBORPATH/APP/app-name/*.RUL

• To create the rules file, right click on the database that is that is created and click create rule file.

Building dimensions using rules files:- We can create the hierarchies in the outline manually or using rules files. there 3 ways to build rules files.

• Generation reference method
• Level reference method
• Parent-child reference method

Generation reference method(using notepad or excel as data source) Make sure the source is ready. A sample file to build geography dimension is as below. The fields are separated By TAB (delimiter). The delimiter can be changed to any other value(explained later) INDIA   SI  AP INDIA   SI  TN INDIA   NI  UP INDIA   NI  DELHI

• Open data prep editor (Right click on the rules files and click create rules files)
• Click file- open data file and locate file
• Click dimension build fields button
• Click field properties
• Click dimension build properties.
• Make sure you are In first field
• Select dimension name ,by double clicking it (for example geography)
• Select field type as Generation
• Enter 2 in the number field.
• Click next and select the dimension(geography)and select the field type as generation.

The number will increase to 3. Repeat the process for all columns .

• Click OK click validate and save the rules file. The Rules file by default will get saved in ARBORPATH/APP/app-name/*.RUL

Once the rules file is created ,double click the outline, click outline> update outline. Another screen appears. Click find data file .locate the source file click find rules file and locate the rules file. Select overwrite check box. Click OK. NOTE:-After creating the rules files, “rules files” also appears under the database . Level reference method:(using notepad or excel as the data source) Make sure the source is already . A sample file to build geography diversion is as below The fields are separated by TAB (delimiter)the delimiter  can be changed to any any other value(explained later) AP      SI      INDIA TN      SI      INDIA UP              NI   INDIA DELHI       NI   INDIA NOTE:- the order of the fields in the source file for the level reference method is opposite when compared with generation reference method.

• Open data prep editor right click on the rules files, and click create rules file
• Click file to open data file and locate the file
• Click dimension build fields button
• Click field properties
• Click dimension build properties
• Make sure are in first field.
• Select dimension name, by double clicking it(for example geography)
• Select field type as level
• Enter 0 in the number field
• Click next and select the dimension (geography)and select the field type as level the number will be increased to 1. Repeat the process for all columns.
• Click OK.
• Click options ,dimension build settings and dimension build settings tab again.
• Select the dimension (geography) by double clicking on it. select “use level reference method” under build method .
• Click OK.
• Click validate and save the rules file by default will get saved in ARBORPATH/APP/app-name/db-name/*.RUL

Once the rule file is created double click the outline ,click, outline->update outline Another screen appears. Click find data file .locate the source file .click find rules file and located the rule file. Select overwrite check box. Click OK Parent child reference method:(using notepad or excel as the data source) Make sure the source is ready. A sample file to build geography dimension is as below the fields are separated by TAB (delimiter) the delimiter can be changed any other value.(explained later) INDIA        SI INDIA        SI SI       AP SI       TN NI       UP NI      DELHI The source file contains minimum TWO fields

• Open data prep editor (right click on the rules file ,and click create rules file)
• Click files-> open data and locate the file
• Click dimension build fields button
• Click field properties
• Click dimension build properties
• Make sure you are in first field
• Select dimension name, by double clicking it (for example geography)
• Select field type as parent. No need to change the number in the number field.
• Click next and select the dimension (geography)and select the field type as child the number will remain same
• Click OK.
• Click options, dimensions build settings and dimension build settings tab again.
• Select the dimension(geography)by double clicking on it.
• Select “use parent/child reference method” under build method.
• Click OK.
• Click validate and Save the rules file. The Rules file by default will get saved in AP.BORPATH/APP/app-name/db-name/*.RUL.

  Once the rules file is created, Double click the outline, Click, Outline-> update outline. Another screen appears. Click find data file. Locate the source file. Click find rules. File and located the rules file. Select overwrite check box. Click OK. Handling de-limiters: If the delimiter in the source file is tab, then the rules files can handle the delimiter as the default delimiter is tab. If the source file contain any other de-limiter, other than tab, for example comma(,)etc. To modify the delimiter,

• Open data prep editor
• Click Options-> Data source properties
• Click Delimiter tab and select the appropriate delimiter. Save the rules file.

Handling Header records in the source file: Some times the source file may contain header records or comment records for information purpose. To ignore these records,

• Open data prep editor
• Click Options-> Data source properties
• Click header tab
• Enter a number in “number of lines to skip”
• Save the rules file.

Building Dimensions from SQL DB:

• To Connect to SQL Database (For example SQL server, Oracle etc), create A DSN using ODBC drive.

Select control panel Double click on the Administration Tools. Double click on Data sources(ODBC) Click System DSN. Click add button. Select the appropriate ODBC driver. Click Finish Another screen appears. Enter a DSN name. For Oracle enter the TSN name(also called host string). In the lab, it is ORCL.

• Open Data prep editor. Click File-> open SQL another window appears. Enter the select statement in Select window. Select the DSN created in the above step. For example, For generation reference method, SELECT country, Region, State from Geography dim; (no need to enter SELECT verb in the SELECT statement). For Level reference method, SELECT state, Region, Country from Geography dim:
• Enter User name and password for SQL database.
• Select Dimension build fields.
• Select field properties, Select dimension build properties.
• Select the dimension name. Select generation in the field type. Enter 2 in the number field. Click next and repeat the process for all fields.
• For level reference enter 0 in the number field and click NEXT. Repeat the process for all columns. Either Generation reference method or level reference can be used. No need for both.

  Double click the outline->update outline. Select type as SQL and enter username and password for database (for example scott, tiger) .click find rules file and locate the rules file. click overwrite check box . click OK.