Lab 1.1 Lab Environment And Installations - Essbase.ru

5. You can verify that all the servers/services started by opening another terminal window and typing: “ps –u oracle”You should see a list of processes with the Hyperion ones near the bottom as shown below.3 Stop Cisco VPN Client Service.If installed, verify that the status of the Cisco VPN Client Service is stopped as discussed in the Installation lab.1. Open Services console:Start- Settings- Control Panel- Administrative Tools- ServicesIf the Cisco VPN Client Service has started, right-click and select “Stop”.28

4 Connect to the Data WarehouseTo connect to an RDBMS, EIS supports ODBC, OCI for Oracle databases or DataDirect Wire Protocol drivers for DB2,Informix, Oracle, or Sybase. ASCII text files are also supported using a DataDirect Text driver.Before you begin, make sure that all the Hyperion servers are running on your Linux VM.You will perform the following tasks: Open the EIS ConsoleConnect to the OLAP Metadata Catalog and EssbaseConnect to the Data Warehouse1. Open the EIS Console by selecting the following shortcut:Start- Programs- Hyperion- Integration Services- Integration Services Console.The first time you start the console, you will be asked to create an OLAP Metadata Catalog. Since this has already beendone for you on the Linux VM, you can uncheck the box saying “Show this dialog at Startup” and click “Close”.29

2. Login to the OLAP Metadata Catalog, “ptsessbase” for the Server, “EIS CAT” for the OLAP Metadata Catalog, “eis”for the User Name, and “oracle” for the Password.Also, provide the login information to Essbase using “ptsessbase” for the Server, “essadmin” for the User Name, and“oracle” for the Password. Click “OK”3. In the “New” tab, double-click “OLAP Model” or select it and click “Open”.30

4. To open the data model you will be using, enter “TBC” for Data Source, “tbc” for User Name, and “oracle” forpassword. Click “OK”.5. Once connected, you will see Schemas/Tables going down the left side and an empty workspace to the right. You arenow ready to begin building your OLAP Model.5 Building the OLAP ModelOLAP models are based on the idea that values in a data source can be categorized as either facts or dimensions offacts. Facts are the numeric, variable values in the database, such as numbers of units sold. Associated with facts arerelated data values that provide additional information, such as store locations. An OLAP model contains a fact table,dimension tables, and dimension branches. An OLAP model may contain time and accounts dimensions.EIS creates an OLAP model that is a logical model, not a physical star schema, and is a logical representation of datavalues you select from the data sources.You will perform the following tasks: Create a logical model consisting of Fact and Dimension tables.Create the hierarchies for the dimensions.1. To enlarge your workspace area, turn off “Intelligent Help” by de-selecting Tools- Intelligent Help fromthe menu bar.31

2. Display the TBC tables by clicking on “ ” to the left of “TBC” and “TABLES”.3. Begin by dragging the fact table, “TBC.SALES” to the middle of the workspace.4. A dialog box will appear asking if you would like to create a Time dimension from the fact table, click“Yes”.Note: Since a Time dimension table does not currently exist, you are “creating” one using a copy of thefact table. This is not a recommended practice since fact tables are generally very large and joining twofact tables together could cause long query times. It is best practice to have a Time dimension table orview pre-existing in the schema. This exercise is merely to show that it can be done with EIS, but it isextremely uncommon in practice due to performance reasons.32

5. Another dialog box will appear asking if you would like to add an Accounts dimension from the fact table,click “No”.6. Double-click the line joining the “SALES” fact table to the “Time” dimension table.7. If the join does not get automatically created (as shown in the bottom box), highlight “TRANSDATE” inboth the “SALES” and “Time” tables and click “Add”. Click “Close” when the join exists in the CurrentJoin Information box.8. Drag “TBC.MEASURES” next to “SALES” (It can be placed anywhere in the Model workspace area,Measures is shown above Sales in the graphic below). A Create New Dimension dialog box will appear,click “OK”.33

Since a primary key/foreign key join has already been created between the two tables, the join willautomatically be set. You can verify this by double-clicking on the join.Note: You can set the Dimension Type here to be “Accounts”, but we will be doing this later in the lab, soyou can leave it as “Default”.9. Drag “TBC.SCENARIO” next to “SALES”. A Create New Dimension dialog box will appear, click “OK”.10. Drag “TBC.PRODUCT” next to “SALES”. A Create New Dimension dialog box will appear, click “OK”.Drag “TBC.PRODUCTDIM” next to “PRODUCT”. A dialog box will appear asking if you would like to addthis table to the current model, click “OK”. The header area for PRODUCTDIM will be blank at this point.This dialog box appears because no primary key/foreign key has been set up with the fact table. You willneed to manually create the join in order to incorporate “PRODUCTDIM” into the model.34

11. Enable Joins Mode by clicking on the “Join” iconas shown below. Drag a line from the“PRODUCT” table to the “PRODUCTDIM” table (the table name in the header area will be blank).12. Highlight “SKU” in both the “PRODUCT” and “PRODUCTDIM” tables and click “Add”. Click “Close”when the join has been added in the Current Join Information box.13. Click on the “Join” icon again to exit Joins Mode. The model should appear similar to what is shownbelow. Both tables are called “PRODUCT” to indicate that they both make up the PRODUCT dimension.35

14. Drag “TBC.MARKET” next to “SALES”. A Create New Dimension dialog box will appear, click “OK”.Drag “TBC.REGION” next to “MARKET”. A dialog box will appear asking if you would like to add thistable to the current model, click “OK”.15. As before, enable Joins Mode by clicking on the “Join” icon. Drag a line from the “MARKET” table tothe “REGION” table (the table name in the header area will be blank). If the join has not been createdalready, set the join by highlighting “REGIONID” in both tables and click “Add”. Click “Close” when thejoin has been added in the bottom box.36

16. Your model should now look similar to the one shown below. Now that the facts and dimensions havebeen identified, the next step is to create the hierarchies.17. Right-click the header area of the “Time” dimension table and select “View Columns”.Right-click “Time” in the area underneath the header and click “Properties ”.18. In the “Columns” tab, select “TRANSDATE” and click on “Date-Hierarchy”.37

19. Select “Year, Quarter, Month” and click “OK”. Four “new” fields appear in the “Columns” tab. TheDate fields are logical fields that have been created by EIS using SQL functions specific to the connectedRDBMS source. User defined SQL is also supported to create additional logical fields. Click “Close” toreturn to the OLAP Model.20. Right-click the header area of the “Time” dimension table and select “Properties ”. The Time hierarchyis created automatically using the previous method. Select “Year Quarter Month” in the DimensionProperties dialog and click “Edit ”.38

21. The hierarchy is made up of Year, Quarter and Month. In the Name field, change Year Quarter Monthto “Time”. Highlight “Time.Year” and click “Sort Order ”.In the Column dropdown box, select “Year”. In the Sort Order dropdown box, select “Ascending”. Thissets the sort order for the years in the Time hierarchy. Click “OK”.In the Edit Hierarchy dialog box, highlight “Time.Quarter” and click “Transform ”.39

Click on the “Prefix/Suffix” tab. The member names for Quarters are 1, 2, 3 & 4. Since there aremultiple years in the Time dimension, uniqueness for the Quarter names can be achieved by attaching theyear to the name as well as additional text before or after the member name (ex. Qtr1-2006). The radiobutton selections under Prefix and Suffix refer to ancestral member names that can be attached to theQuarter’s member name, while the Custom text boxes allow you to attach additional text. In our examplewe want to add the text “Qtr” as a prefix to the quarter member and then “-” Year as a suffix. So underPrefix, select the “None” radio button since we do not want to attach any ancestral member names to theprefix and type “Qtr” in the Custom Prefix box since we want to attach it to the beginning. Under Suffix,select the “Parent Name” radio button since we want the Year to be attached at the end and type “-” inthe Custom Prefix to separate the Quarter from the Year.Click on “Preview Results” to see what the Quarter member names will look like in Essbase. Click“Close”. Click “OK” in the Edit Transformations – Quarter dialog box.40

In the Edit Hierarchy dialog box, highlight “Time.Month” and click “Transform ”.Click on the “Prefix/Suffix” tab. As with Quarters uniqueness for the Month names can also beachieved using a similar method (ex. Jan-2006). In this example we want to add “-” Year as a suffix.So under Prefix, select the “None” radio button and leave the Custom Prefix box blank since we do notwant to attach anything before the Month name. Under Suffix, select the “Grandparent Name” radiobutton since we want the Year to be attached at the end and type “-” in the Custom Prefix to separatethe Month from the Year.41

Click on “Preview Results” to see what the Month member names will look like in Essbase. Click“Close”.Click “OK” in the Edit Transformations – Month dialog box. Click “OK” in the Edit Hierarchy dialogbox. Click “OK” in the Dimension Properties dialog box.Essbase supports non-unique member names, so it is not necessary to force uniqueness of membernames. However, for better end-user readability and improved query performance, it is recommended thatunique member names be enforced.22. Right-click the header area of the “MEASURES” dimension table and select “View Columns”. Right-click “MEASURES” in the area underneath the header and click “Properties ”. Go to the “PhysicalJoins” tab.23. Select “CHILD” in the Child column and “PARENT” in the Parent column. Click “Add”.The Recursion dialog box appears, click “OK”. A parent-child relationship has been created for thisdimension table. Click “Close”.42

24. Right-click the header area of the “MEASURES” dimension table and select “Properties ”. Click“Add” to create a hierarchy.25. Type “Measures” in the Name text box. Drag “CHILD” from the left panel to the right underneath“Measures”. Recursive hierarchies only require that the Child field be in the hierarchy. Click “OK” twiceto return to the model.43

26. Right-click the header area of the “PRODUCT” dimension table that is joined to “SALES” and select“Properties ”. Click “Add” to create a hierarchy. Type “Product” in the Name text box. Expand“PRODUCTDIM”. Drag the “FAMILY” field below “PRODUCTDIM” from the left panel to the rightunderneath “Product”. Then drag the “SKU” field below “PRODUCTDIM” from the left panel to the rightunderneath “PRODUCTDIM.FAMILY”.Click “OK” twice to return to the model.27. Right-click the header area of the “MARKET” dimension table that is joined to “SALES” and select“Properties ”. Click “Add” to create a hierarchy. Type “Market” in the Name text box. Expand“REGION” and then “MARKET”. Drag the “REGION” field below “REGION” from the left panel to theright underneath “Market”. Then drag the “STATE” field below “MARKET” from the left panel to theright underneath “REGION.REGION”.Click “OK” twice to return to the model.44

28. Verify the model by going to File- Verify. If no errors were found, proceed with the lab.29. Save the model by selecting File- Save, pressing Ctrl S or clicking the Save icon. Name the model“TBC Model” and click “OK”45

6 Building the MetaoutlineA metaoutline is a template containing the structure and rules for creating an Essbase model or outline. A metaoutline isbased on the structure of an OLAP model. Metaoutlines have several features: They can be the basis for multiple Essbase outlines.They can be defined at a central location and used to create multiple Essbase outlines in multiple locations.They enable you to create Essbase databases on demand.They enable you to view sample Essbase outlines before building them.They automatically generate SQL to retrieve data from an external source.They enable you to filter data before you build the associated Essbase outline.They enable you to transform data as you create an Essbase outline.1. Click File- New. Select “OLAP Metaoutline” in the New tab. In the dropdown where it says ‘Select the model thatthe metaoutline should be based on’, ensure that “TBC Model” is selected. Click “Open”.2. To enlarge your workspace area, turn off “Intelligent Help” by selecting Tools- Intelligent Help from the menu bar.Start by expanding the “Time” dimension (under TBC Model in the left pane). Drag the “Time” hierarchyuntil it is underneath “Untitled” in the right pane and a tree symbol with an “L” attached to it is showing.46

MEASURES” dimension and dragging the “Measures” hierarchy underneath3. Repeat by expanding the ““Untitled” (make sure you see the tree symbol with an “L” attached to it as shown below).SCENARIO” field (first expand the “SCENARIO”4. Since no Scenario hierarchy was defined, drag the “dimension and then expand the “ SCENARIO” table) and place it underneath “Untitled” as shown below (makesure you see the tree symbol with an “L” attached to it as shown below).47

Product” hierarchy underneath “Untitled” (make sure you see the5. For the “PRODUCT” dimension, drag the “tree symbol with an “L” attached to it as shown below).MARKET” dimension, drag the “Market” hierarchy underneath “Untitled” (make sure6. Do the same with the “you see the tree symbol with an “L” attached to it as shown below).48

7. Right-click on the top level of “Time” and select “Properties”.8. In the Member Info tab, select “Dynamic Calc” under the Data Storage section.49

9. Go to the Dynamic Time Series tab. Enable “H-T-D”, “Y-T-D” and “Q-T-D” by checking the boxes. In theGeneration column, use the drop down and select “1” for “H-T-D”, “2” for “Y-T-D” and “3” for “Q-T-D”. Click“OK”.10. Right-click on “Time.Year” and select “Properties”. In the Member Info tab, select “Dynamic Calc”. Click “OK”.50

11. Right-click on “Time.Quarter” and select “Properties”. In the Member Info tab, select “Dynamic Calc”. Click“OK”.12. Right-click on the top level of “MEASURES” and select “Properties”. In the Member Info tab, select “Accounts”under Dimension Type, “Dense” under Dimension Storage, and “Label Only” under Data Storage. Click “OK”.51

13. Right-click on “MEASURES.CHILD” and select “Properties”. In the Member Info tab, select “From DatabaseColumn” under Consolidation and choose “CONSOLIDATION” in the drop down. Select “From DatabaseColumn” under Two Pass Calculation and choose “TWOPASSCALC” in the drop down. Select “From DatabaseColumn” under Data Storage and choose “STORAGE” in the drop down.14. In the Account Info tab, select “From Database Column” under Time Balance and choose “TIMEBALANCE” inthe drop down. Select “From Database Column” under Skip and choose “SKIP” in the drop down. Select“Variance Reporting” under Data Storage and choose “VARIANCEREPORTING” in the drop down.52

15. . In the Formula tab, select “From Database Column” and choose “FORMULA” in the drop down.16. In the Alias tab, under the New Alias section in the Alias drop down, select “MEASURES ALIAS” and click “Add”.“MEASURES ALIAS” should now appear under Existing Alias. Click “OK”.53

17. Right-click on the top level of “SCENARIO” and select “Properties”. In the Member Info tab, select “Label Only”in the Data Storage section. Click “OK”.18. Highlight “SCENARIO.SCENARIO” and click on the Add a Member as a Sibling icon as shown below. Type“Variance” for the member name and click “OK”.54

20. Highlight “Variance” and click on the Add a Member as a Sibling again. Type “Variance%” for the member nameand click “OK”.21. Right-click on “Variance” and select “Properties”. In the Member Info tab, select “Two Pass Calculation” underTwo Pass Calculation and “Dynamic Calc” under Data Storage.55

22. In the Formula tab, type “@VAR(Budget,Actual);” in the Formula box. Click “OK”.Note: Even though the formula is correct, it will not Verify unless you place double quotes ( “ ” ) around Budget andActual.23. Right-click on “Variance%” and select “Properties”. In the Member Info tab, select “Two Pass Calculation”under Two Pass Calculation and “Dynamic Calc” under Data Storage. In the Formula tab, type“@VARPER(Budget,Actual);” in the Formula box. Click “OK”.56

24. Right-click on the top level of “PRODUCT” and select “Properties”. In the Outline Build tab, select “Add asShared Members” under Duplicate Member Names. Click “OK”.25. Right-click on “PRODUCTDIM.FAMILY” and select “Properties”. In the Alias tab, under the New Alias section inthe Alias drop down, select “FAMILY ALIAS” and click “Add”. “FAMILY ALIAS” should now appear underExisting Alias. Click “OK”.57

26. Right-click on “PRODUCTDIM.SKU” and select “Properties”. In the Alias tab, under the New Alias section in theAlias drop down, select “SKU ALIAS” and click “Add”. “SKU ALIAS” should now appear under Existing Alias.Click “OK”.27. Right-click on “Untitled” and select “Properties”. In the Data Storage Model tab, select “Block Storage” underData Storage Option.58

28. In the Database Measures tab, click “Add”. Select “AMOUNT” and click “OK” twice to return to the metaoutline.29. Verify the metaoutline by going to File- Verify. If no errors were found, proceed with the lab.Save the metaoutline by selecting File- Save, pressing Ctrl S or clicking the Save icon. Name the metaoutline“TBC Metaoutline” and click “OK”59

30. Now you are ready to build the cube. Right-click on “TBC Metaoutline” and select Member and Data Load .Type “TBC” in the Application Name box and “TBC” in the

Essbase Administration Services Console 2 Copy Class Files and Enable VMware Image In all of the labs you will be using a VMware image containing Oracle Enterprise Linux (version 4.0), an Oracle 10g Database, and an Essbase Server environment. You will co