7 View Essbase Outline And Query Data
7 View Essbase Outline and Query DataEssbase Administration Services (EAS) is the cross-platform administration tool for Essbase. EAS consists of a Javamiddle-tier server, called Administration Server, and a Java client console, called Administration Services Console. Theconsole is the graphical user interface that enables administrators to manage the Essbase environment. The consoleprovides wizards, editors, and other tools to help administrators view, manage, and maintain Essbase Servers.The Essbase Spreadsheet Add-in adds a new menu, an Essbase toolbar, and mouse shortcuts with which you canaccess Essbase cubes. It was the first front-end to Essbase and it is still the most popular by a wide margin.1. Open the EAS Console by selecting the following shortcut:Start- Programs- Hyperion- Administration Services- Start Administration Services Console.2. Type in “ptsessbase” for the Administration Server, “admin” for Username and “password” for Password. Click“OK”.62
3. Right-click on Essbase Servers and select “Add Essbase Server”.Type in “ptsessbase” for the Essbase Server, “essadmin” for Username and “oracle” for Password and Confirmpassword. Click “OK”.63
4. Expand the tree from ptsessbase to Applications down to the second TBC. Double-click Outline. On the right, theEssbase outline will appear.5. Expand the dimensions and examine the outline.64
6. To query the cube, right-click on the second TBC and select “Preview data”.7. Double-click on “Time” to drilldown the Time dimension. Since no data exists for 2006 and 2008, double-click on “2007”and then “Qtr1-2007”. Double-click on “Market”. Pivot the Time dimension by doing a right-click drag on one of themembers in Time. You can navigate through the data by double-clicking to drilldown, double-right-clicking to drillup, rightclick drag to pivot a dimension from the row to the column or vice versa.65
8. To query the cube using the Excel Add-in, open Excel, verify that the Menu Item Essbase is showing on the Menu Bar(if not, refer to the installation lab for instructions on installing the Add-in manually), click on “Essbase” and select“Connect ”.9. At the login prompt, type in “ptsessbase” in the Server box, “essadmin” for Username and “oracle” for Password.Click “OK”.66
10. Select “TBC/Tbc” in the Application/Database window and click “OK”.11. Double-click anywhere on the blank worksheet or click on “Essbase- Retrieve” from the Menu Bar.When double-clicking (or retrieving) on a blank worksheet, the query will consist of every root (top level) member fromeach dimension.12. Click “Essbase- Options ” from the Menu Bar.67
In the Display tab, ensure that Subitems, #Missing Rows, Adjust Columns and Use Aliases are all selected and that“0” (zero) is typed into the #Missing Label text box. Click “OK”.13. Double-click on “Time”, “2007” and then “Qtr1-2007” to drilldown on the Time dimension.Notice that 2006 and 2008 do not appear when you did a drilldown on Time. #Missing is the null indicator for Essbase,so when we checked the Suppress #Missing option, the members with no data are not displayed.68
14. Double-click on “MARKET” to drilldown to the regions.15. Drag the Time dimension by clicking down on any Time member with your right mouse button (do not release the buttonyet)) and drag to where the column dimensions are located (see image below) and release the right mouse button.69
16. Double-click on “East”, “West”, “South”, and “Central” to drilldown to the states.17 Type over “MEASURES” with “Sales” and “SCENARIO” with “Actual”. Click “Essbase- Retrieve” from the MenuBar.70
The query now reads Actual Sales by State and Region across Time periods for total Products. DO NOT close theExcel workbook, if you are continuing with the Drill-through Reporting exercise.8 Advanced EIS – Drill-through Reporting1. From the EIS Console, open the TBC Metaoutline. Select “Edit- Drill-Through Reports ” from the Menu Bar.71
2. Click “Add” to create a new Drill-through report3. Highlight “Report 1” and click “Edit ”. For the name of the Drill-through report, type “POS Detail”. Click “OK”.4. Highlight “OLAP Intersection Levels” and click “Add ”.The OLAP Intersection Levels represent which data cells in the cube can extract the relational information from the datasource. It is recommended that these be the lowest levels (Level 0) in each dimension so as to minimize the number ofrows returned by the report.72
5. Expand the Time dimension until you reach Time.Month. Highlight “Time.Month” and click the “ ” button to add it asan intersection level.6. Expand the MEASURES dimension. Highlight “MEASURES.CHILD” and click the “ ” button. The Recursive DrillThrough Setting dialog box will appear. In the Generation/Level dropdown, select “Level”. In the Value text box, type“0”. Click “OK”.7. Expand the SCENARIO dimension. Highlight “SCENARIO.SCENARIO” and click the “ ” button to add it as anintersection level.73
8. Expand the PRODUCT dimension until you reach PRODUCTDIM.SKU. Highlight “PRODUCTDIM.SKU” and click the“ ” button to add it as an intersection level.9. Expand the MARKET dimension until you reach MARKET.STATE. Highlight “MARKET.STATE” and click the “ ” buttonto add it as an intersection level. Click “Close”.10. Highlight “Drill-Through Report Column” and click “Add ” to add the fields to your Drill-through report.74
11. Expand Time, highlight “TRANSDATE”, and click the “ ” button to add the field to the Drill-through report.12. Expand MEASURES, highlight “CHILD”, and click the “ ” button to add the field to the Drill-through report.13. Expand MARKET, highlight “STATE”, and click the “ ” button to add the field to the Drill-through report.75
14. Expand PRODUCTDIM, highlight “SKU ALIAS”, and click the “ ” button to add the field to the Drill-through report.15. Expand PRODUCT, highlight “CAFFEINATED”, and click the “ ” button to add the field to the Drill-through report.Repeat for “INTRODATE”, “OUNCES”, and “PKGTYPE”.16. Expand SALES, highlight “AMOUNT”, and click the “ ” button. Click “Close”.76
17. Verify that your OLAP Intersection Levels and Drill-Through Report Columns look similar to that shown below.Click “Close”.18. Save TBC Metaoutline by selecting “File- Save”, pressing “Ctrl S” or clicking on the Save icon on the toolbar.19. From the menu bar, select “Outline- Update Drill-Through Data ”77
20. Select “TBC” for both the Application Name and Database Name. Click “Next”.21. Click “Finish” on the Schedule Essbase Load screen.78
22. A Member Load screen will appear. Click “OK” when the Drill-through data has been successfully loaded.Click “Close” to close the Member Load screen.23. Return to the Excel workbook that you were working on earlier or open the Lab Queries.xls file from the In Class Filesfolder and select tab Lab 1.2.7 (if you opened the file, you will first need to connect to TBC/TBC). From the Menu Bar,select “Essbase- Options ”79
24. In the Display tab, check the boxes for “Use Styles” and “Use Aliases”.25. In the Style tab, check “Integration Server Drill-Through”. Click on the “Format ” button if you would like to changefont, font size, style and/or color to change the format of the cells that contain Drill-through reports.80
The default lime green color can be a little harsh on the eyes, so you can choose a different color. Click “OK” whendone.Click “OK” again to close the Essbase Options dialog box.26. Back in the Excel worksheet, double-click on “PRODUCT” to drilldown to its children.27. Double-click on “Colas” to drilldown to its children.81
28. Double-click on one of the Drill-through report cells such as Cola- New York- Jan-2007- Sales- Actual.29. When the Linked Objects Browser dialog opens, click “View/Launch” to view the Drill-through report.82
30. If more than one Drill-through report appears on the list, select “POS Detail” and click “Execute”.31. The Drill-through report will appear on a separate worksheet. Verify that the sum of the AMOUNT field matches the valueof the Essbase data cell where the Drill-through report was initiated from. Launch Drill-through reports from other datacells and verify the information.DO NOT close the Excel workbook if you are continuing with the Visual Explorer exercise.9 Advanced Add-in – Visual Explorer1. Return to Sheet1 (or the sheet where you have been doing all the Essbase queries). From the Excel Menu Bar, select“Essbase- Visualize & Explore ”83
2. Expand Margin in the Measures panel so that Sales is visible.3. Drag “Sales” to the Rows box.84
A single bar will appear representing Sales revenue for all Products across all Markets for the entire Year.4. Drag “Gen2,MARKET” to the Columns box.85
Four bars will now appear representing Sales revenue for all Products for the entire Year by each Market Region.5. Drag “Gen2,PRODUCT” to the Color box.86
The four bars turn to stacked bars with different colors representing each of the Product Lines.6. Right-click on the Y-axis of the bar chart and select “Add Reference Lines ”. When the Reference Line dialog boxappears, accept the default selections and click “OK”.87
An average line will appear representing the average Sales revenue for the Regions.7. Drag “Gen2,PRODUCT” from the Color box to the Columns box to the right of “Gen2,MARKET”.88
The bar chart now represents Sales by Region and Product Line.8. If you would like to see an average line across all Sales categories, right-click on one of the average lines and select“Edit ”. In the Edit Reference Line dialog box, select the radio button for “Entire Table” and click “OK”.89
The average line will now represent the average for all the Sales categories.9. Scroll down the Measures panel until you see Profit % and drag “Profit %” to the Color box.90
Each of the Sales bars will now turn to various shades of green, the darker the green the greater the Profit %. Note thatin the West the Profit % of some Product Lines are relatively low despite the high Sales revenue. Further analysis couldreveal the reasons for this anomaly.10. Now let’s take a look at how Sales revenue gets impacted based on the amount of Marketing expenditures. First,remove Profit % by right-clicking on Profit % and selecting “Remove”. Next, remove the average line by right-clickingon it and selecting “Remove”.91
11. Expand Total Expenses in the Measures panel and drag “Marketing” to the Columns box to the right of“Gen2,PRODUCT”.12. Drag “Gen2,MARKET” from the Columns box to the Color box.92
13. Drag “Gen2,PRODUCT” from the Columns box to the Shape box.Your chart should now look similar to the one below. The marks on the chart represent the correlation betweenMarketing expenditures and Sales (as Marketing expenses increase, Revenue should also increase by some fixedfactor). Each mark corresponds to a Region/Product Line combination. Color coding and the different shapes of themarks make it easy to identify which Region and Product Line the marks represent.93
14. Adding a Trend Line will show the exact correlation between Sales and Marketing across all the data points. To add aTrend Line, right-click on a white area in the chart and select “Trend Lines”.A trend line will appear for each Region.94
15. To show only one Trend Line, right-click on any Trend Line and select “Edit Trend Lines ”. In the Trend LinesOptions dialog box, uncheck “Gen2,MARKET” and click “OK”.You should now see one Trend Line. The marks below the Trend Line are the Region/Product Line combinations thatare underperforming (where the Marketing expenses are high relative to Sales). The marks above the Line areoverperformers (those where Sales are higher relative to their Marketing expenses).95
16. To add more data points to the chart, drag “Gen2,MARKET” and “Gen2,PRODUCT” to the Level of Detail box.The chart should now look similar to the one below.96
17. Drag a box around the worse performers (those with the highest Marketing expenses relative to Sales). The highlightedmarks should all turn red.18. Click on the “Update Excel (New Worksheet)” icon on the toolbar.19. Return to Excel to view the data points.97
20. Since you are still online to Essbase, you can continue with the analysis. Double-click on “Year”, “2007”and “Qtr12007”. Pivot the Time dimension to the Column.21. Type “Actual” in place of SCENARIO.98
From the Excel Menu Bar, select “Essbase- Retrieve”22. Double-click on a drill-through cell.23. Click “View/Launch” at the Linked Objects Browser dialog box. Select “POS Detail” and click “Execute”.24. The Drill-through report will appear on a separate worksheet.99
100
Lab 1.3 Cube Building (ASO) with Essbase Administration Services1 OverviewThis lab steps through the process of building an Essbase ASO cube using Essbase Administration Services (EAS) andLoad Rules. EAS is the cross-platform administration tool for Essbase. EAS consists of a Java middle-tier server, calledAdministration Server, and a Java client console, called Administration Services Console. The console is the graphicaluser interface that enables administrators to manage the Essbase environment. The console provides wizards, editors,and other tools to help administrators view, manage, and maintain Essbase Servers.The console simplifies Essbase administrative tasks by providing a graphical representation of the Essbase environment.You can operate on Essbase objects quickly from a familiar tree view. You can perform multiple tasks simultaneously, runprocesses in the background, perform cross-server operations, and manage active user activity.In this lab, you will perform the following tasks:Create Application and DatabaseBuild a Time dimension using Date/Time wizardBuild dimensions using load rulesLoad data using load ruleConstruct MDX formulasQuery the ASO cube 2 Create ASO CubeBefore you can begin this lab, ensure the following servers have started:1.2.3.4.Linux VMShared ServicesEssbaseEAS ServerRefer to Lab 1.2 for instructions on starting the above servers.1. Open the EAS Console by selecting the following shortcut:Start- Programs- Hyperion- Administration Services- Start Administration Services Console.101
2. At the login screen, type username “admin” and password “password” and click “OK”.3. Expand Essbase Servers until you see Applications. Right-click on Applications and select “Create application Using aggregate storage”.4. For the Application Name, type “TBC ASO”. Click “OK”.102
5. Expand Applications. Right-click on TBC ASO and select “Create database ”.6. For the Database Name, type “TBC ASO”. Click “OK”.103
7. Expand TBC ASO until you see Outline. Double-click on “Outline”. You should see a blank outline to the right of theTree View.8. From the Menu Bar, select “Outline- Create Date-Time dimension ”.104
9. You may need to expand the Wizard in order to see the Date-Time Dimension Name box. Change the Date-TimeDimension Name to “Time”. Use the drop downs to select the Start and End Dates. For Start Date, select “Jan 1,2002” and for End Date, select “Dec 31, 2006”. Click “Next”.10. Click “Add” to insert a hierarchy.105
Under Time Depths, de-select Week so that Year, Quarter, Month and Day are the only ones checked.Expand on Year and click on the Labeling Rule, “Gregorian Year 2006”. Select “Year 2007” and click “OK”.106
Expand on Quarter and click on the Labeling Rule, “Quarter 1 of Gregorian Year 2006”. In the Define LabelingRules dialog box, check the box “Use a 2 digit year” and select “Quarter 1 2007”. Click “OK”.Expand on Month and click on the Labeling Rule, “Month 1 of Gregorian Year 2006”. In the Define Labeling Rulesdialog box, check the box “Use a 2 digit year” and select “January 07”. Click “OK”.107
Expand on Day, the Labeling Rule should read “Jan 15 2006”. If it does not, click on the Labeling Rule and select“Jan 15 2006” and click “OK”.Your hierarchy definition should look similar to the one below. Click “OK” if no further changes are necessary. Click“Next” to go to the next step in the Wizard.108
Attributes for the Date-Time Dimension can be set up for additional types of analysis. In outlines with day-levelgranularity, attributes can be assigned that would allow you to analyze statistics by the day of the week or weekday vs.weekend. You can also specify certain holidays that may impact performance of certain measurements. For our exercisewe will not be utilizing these capabilities, so click “Finish” to complete the dimension.Expand the Time dimension, it should look similar to the one below. Click “Save” to save your changes.109
11. Collapse the Time dimension and highlight the top member of the Time dimension. Click on the “Add a sibling to theselected member”icon or from the Menu Bar select “Edit- Add sibling”.Type “Measures” in the empty text box and hit Enter .110
Type “Product” in the empty text box and hit Enter .Type “Market” in the empty text box and hit Enter twice.Right-click on Measures and select “Edit member properties”.111
Next to Dimension Type choose “Accounts” from the drop down. Click “OK”.Your outline should look similar to the one below. Click “Save” to save your changes. Click “Close” to close the outline.112
12. Right-click on the second TBC ASO in the tree view and select “Create- Rules file”.The Data Prep Editor will appear on the right.113
13. From the Menu Bar select “File- Open SQL”.Verify that “ptsessbase” is the Essbase Server and that “TBC ASO” is in the Application and Database drop downwindows. Click “OK”.For SQL Data Sources, choose “TBC” from the drop down and in the FROM clause, type “MEASURES”. Click“OK/Retrieve”.114
For Username, type “tbc” and for Password, type “oracle”.The Data Prep Editor should now be populated with the contents of the MEASURES table as shown below.This is the same MEASURES table that you were using in the previous exercise with EIS. This SQL Interface torelational data sources uses an ODBC connection only, while EIS can also connect natively to some databases (i.e. OCI).Load Rules can not join tables together, so everything must be included in a single table or view in order to build out thedimension or to load data into the cube.115
14. From the Menu Bar select “Options- Dimension build settings”.In the Dimension Build Settings tab, under Dimension double-click on “Measures” so that Measures appears just tothe right of Dimension. Under Build Method select “Use parent/child references”. Click “OK”.15. Click on the “Dimension build fields”icon or from the Menu Bar select “View- Dimension build fields”.116
116. Before doing this step, you may want to resize your panels or resize the Data Prep Editor so that it takes up /3 of theviewing area on the right side of the screen (see figure in Step 17).Click on the “Field properties”icon or from the Menu Bar select “Field- Properties”.17. Arrange the Field Properties dialog box so that it is on the left side of your screen and the Data Prep Editor is on theright side. Go to the Dimension Build Properties tab. Ensure that you are on Field 1, the field number is indicated onthe upper left of the Field Properties dialog box. If you are not on Field 1, click on the “ Prev” button at the bottomuntil you see Field number: 1.Scroll to the bottom of list and check the box “Ignore field during dimension build”. Click “Next ”.117
18. For Field 2, scroll to the bottom of list and check the box “Ignore field during dimension build”. Click “Next ”.19. For Field 3, under Dimension double-click “Measures” so that Measures shows up next to Dimension. Under FieldType double-click “Parent” so that Parent shows up next to Type. Click “Next ”.118
20. For Field 4, under Dimension double-click “Measures” so that Measures shows up next to Dimension. Under FieldType double-click “Child” so that Child shows up next to Type. Click “Next ”.21. For Field 5, under Dimension double-click “Measures” so that Measures shows up next to Dimension. Under FieldType double-click “Alias” so that Alias shows up next to Type. Click “Next ”.119
22. For Field 6, under Dimension double-click “Measures” so that Measures shows up next to Dimension. Under FieldType double-click “Property” so that Property shows up next to Type. Click “Next ”.Repeat for Field 7, 8, 9 & 10. Double-click “Measures” so that Measures shows up next to Dimension. Under FieldType double-click “Property” so that Property shows up next to Type. Click “Next ”.23. For Field 11, scroll to the bottom of list and check the box “Ignore field during dimension build”. Click “Next ”.Repeat for Field 12 & 13, scroll to the bottom of list and check the box “Ignore field during dimension build”. Click“Next ”. Click “OK” after completing Field 13.120
Essbase Administration Services (EAS) is the cross-platform administration tool for Essbase. EAS consists of a Java middle-tier server, called Administration Server, and a Java client console, called Administration Services Console. The console is the graphical user interface that enables administrators to
Oracle Essbase Administration Services, MaxL, or ESSCMD. See theOracle Essbase Database Administrator's Guide and the Oracle Essbase Technical Reference. With SQL Interface, you can load data from a Unicode-mode relational database to a Unicode-mode Oracle Essbase application. For information on the Essbase implementation of Unicode,
When upgrading to Essbase Release 9.3.3, you do not need to remove Essbase Release 9.3.1.x from your computer. Simply run the installation programs provided in this release. For information on running Essbase installation programs, see the Hyperion Essbase - System 9 Release 9.3.1 Installation Guide for the platform you are using. Caution!
Essbase Cloud vs Essbase On-premise Feature Essbase Cloud On-Premise Essbase BSO, ASO, Hybrid Y Y Load rules (Dimension & Data) Y Y Calculations: formulas, calculations scripts, MDX scripts Y Y Unicode supported Y (Default) Y Locks (application, database, objects) Y (Blocks, objects) Y (database, objects) Security filters Y Y Migration of on-premises cubes (*) Y (Export Utility, LCM utility .
A Hyperion Product Update What's New in Hyperion System 9 BI Essbase Analytics and Enterprise Analytics? Release summary Hyperion System 9 BI Essbase Analytics (Essbase Analytics) and Hyperion System 9 BI Enterprise Analytics (Enterprise Analytics) are analytic database engines within Hyperion System 9that allow our customers to develop and deploy custom applications.With .
Essbase as an OBI data source Smartview as the standard Office integration technology -Mix BI, Essbase and EPM content on a single sheet, document or PowerPoint slide -Fully interactive Essbase content with dynamic BI views EPM Workspace Integration -Consume, Create and Edit BI content within Workspace: BI Catalog, BI
About Essbase Oracle's Hyperion Essbase - System 9 provides companies with the ability to deliver critical business information to the right people at the right time. With Essbase, companies can leverage and integrate data from multiple existing data sources and distribute filtered information to end-user communities.
What is Essbase? Oracle Essbase is the market leading online analytical processing (OLAP) server for enterprise performance management (EPM) applications Designed specifically for business users, Oracle Essbase supports forecasting, variance analysis, root cause identification, scenario planning and what-if modeling for both
Hyperion/OBIEE Training by Amit Sharma learnhyperion.wordpress.com II. Essbase Client/Server Architecture 1. What server agent will do? The server runs a Server Agent (ESSBASE) process that acts as a traffic coordinator for all user requests to Essbase applications. Adm
