extended spreadsheet database. this is an appropriate...
TRANSCRIPT
1
ESSBASE 101
Essbase is short for Extended SpreadSheet DataBASE. This is an appropriate name for what Essbase does: It extends the
Spreadsheet into a database. It is really just a fancier, more functional spreadsheet. However, the data no longer lives in
the spreadsheet. The data source is now Essbase, and the mechanism through which data is viewed, presented and
reported is Excel. Most people new to Essbase are already familiar with the way Essbase organizes data, because users of
accounting information have been assembling and reporting data within Excel using the perspectives or dimensions
required in reporting. Think of Dimensions the “by” and “for” designations in a report. A report BY Account FOR Jan, Feb,
Mar FOR Actuals BY Fiscal Year. In the following screenshot, data is represented in terms of the following 4 Perspectives:
1. Accounts 2. Time 3. Scenario 4. Year
When retrieving or refreshing data from Essbase inside of a workbook, Essbase uses dimensions in order to deliver the
data and give it context for the user. There are 8 dimensions and Essbase requires each of them to be completed in order
to return data. There is a 9th dimension called “Attributes” that can provide further segmenting of the data. The dimensions
and attributes in Essbase are shown below:
Dimensions Description
Accounts Actual Cost, Earned Cost, Earned Revenue, Actual Revenue, Overbilled, Underbilled,
etc. (See “Accounts Cheat Sheet” on Page 15 for a full list of accounts)
Period Abbreviated Month Names (e.g. Jan, Feb, etc.)
Time View Periodic, QTD (Quarter to Date), YTD (Year to Date), ITD (Inception to Date)
Year 2013, 2014, 2015, etc.
Scenario JDE Actual, Topside Adjustments, JDE Adjustments
Entity Company Numbers (e.g. 01031, 01011, etc.)
Projects Project Number
Project Status New or Existing
2
ESSBASE 101
Attributes Description
State Attribute dimension to report project related data based on the location defined by
the State, e.g. CA, AL, AZ
End Market Attribute dimension to report project related data based on the project’s end market
e.g. AIR, BRI
Contract Type Attribute dimension to report project related data based on the project’s contract
type e.g. CP (Cost Plus), FP (Fixed Price)
Client Source Attribute dimension to report project related data based on the project’s client
source e.g. FE (Federal), PV (Private)
Job Stage Attribute dimension to report project related data based on the project’s job stage
e.g. CLD (Closed), OPN (Open)
ICP Attribute dimension to report project related data based on the project’s
intercompany tagging e.g ICP-Y (Intercompany project), ICP-N (Not an intercompany
project)
Job Type Attribute dimension to report project related data based on the project’s job type e.g.
JA (Job Cost Administrative), JB (Job Cost)
Ownership Type Attribute dimension to report project related data based on the project’s ownership
type e.g. JVN (Joint Venture Non-Sponsored), WO (Wholly Owned)
Insurance Type Attribute dimension to report project related data based on the project’s insurance
type e.g. CAL, SEL
Tutor Perini uses a combination of pre-defined templates and Smart View Ad-Hoc spreadsheets for reporting and
analysis. The pre-defined templates are the Performance Review Templates. Each exhibit of the Performance Review is a
pre-defined layout of an associated Ad-Hoc spreadsheet. The Ad-Hoc is a raw presentation of the data in the database
and it is pulled into the Performance Review via a series of lookups so it presents in a certain way.
Ad-Hoc analysis – starting with a blank workbook and drilling into details from upper levels (e.g. starting at the Total Vendors and drilling into specific Vendor for Specific accounts, and reorienting the data as the analyst iterates through the data sets. This is a very fluid type of analysis.)
Refreshing data in Performance Review Template - This is a static kind of analysis. This usually entails opening up an existing work book, and simply retrieving the data from Essbase.
Everything a user needs in order to interact with the data in Essbase comes from Hyperion Smart View.
3
ESSBASE 101
Setting up Smart View to Connect to Essbase and HFM
Log on to Citrix and launch Hyperion Smart View
Go to the Smart View tab at the top of the screen and click on the button titled Panel
Click on ‘Private Connections’
On the right, click on the drop down box for “Select Server or enter…”
4
ESSBASE 101
If you see EssbaseCluster-1, select it.
Expand Projects and right click on ‘Projects – EssbaseCluster-1…’ and click ‘Remove Connection.
On the warning box, select ‘Yes’
Next, from the drop down box, if you see HFMCluster, select it.
Right click on ‘TPCHFM – TPCHFM_PROD’ and click ‘Remove Connection’.
On the warning pop-up, select ‘Yes’
5
ESSBASE 101
Next, from the drop down box next to the arrow ‘->’ icon, select ‘Clear Quick Connect URL Entries’
Go to Smart View->Options
The Smart View options panel will open. Click on the “Advanced” tab and enter the environment URL to connect.
Here’s the Hyperion Production environment URL for Tutor Perini.
http://hy-pweb01.tutorperini.com/workspace/SmartViewProviders
6
ESSBASE 101
Next, go to Smart View -> Panel
Click on ‘Shared Connections’
Enter your Network/Citrix login information (e.g. username = first name.last name) and click Connect
From the Shared Connections drop down list, select Oracle ‘Essbase’
7
ESSBASE 101
Expand EssbaseCluster-1 to see Projects.
Expand Projects.
Select the second instance of ‘Projects’ and then click Connect.
Then, from Smart-View menu, click on ‘Refresh’
8
ESSBASE 101
Next, Right click on the second instance of Projects and select ‘Add to Private Connections’
Type in EssbaseCluster-1_Projects_Projects in the name and click ok.
From the drop-down box next to the home icon, click on ‘Disconnect All’
9
ESSBASE 101
From the drop down box, next to the arrow ‘->’ icon, click on ‘Create new connection’
On the pop-up box, select ‘Smart View HTTP Provider’
Enter the following URL in the pop-up box and then click “Next”.
http://HY-PWEB01:80/hfmadf/../hfmofficeprovider/HFMOfficeProvider.aspx
10
ESSBASE 101
Expand, Servers->HFMCluster, select TPCHFM and then click ‘Next’
Enter TPCHFM_PROD in the name field of the pop-up box and then click ‘Finish’
At this stage, both HFM and Essbase have been configured on your machine so you can start using the Performance Review
Templates.
Open the Business Review Template. Enter the logon details and click ‘Connect’
11
ESSBASE 101
Select any Exhibit and from within the Smart View tab, click Refresh
If you do not get any error messages and if you see the data correctly, it means you are successfully connected to both
HFM and Essbase.
12
ESSBASE 101
Working with the Performance Review Template
1. Open the quarterly Business review file sent to you & go to the Input (1st) tab of the Excel template.
2. Select Application - Essbase: from drop down list
For Citrix smart-view users select: EssbaseCluster-1_Projects_Projects
For regular XL smart-view users select: WSFN|Essbase|EssbaseCluster-1|Projects|Projects
Select Period – Current
Select Period – Prior. Currently the Performance Review templates are for quarterly analysis so Period – Prior would be
the month of the previous quarter end.
13
ESSBASE 101
Select Year - Current
Select Year - Prior
The quarterly review file sent to you is populated with quarter end project data as of the template creation date. If
there is difference, it could be due to new projects added after the template was created. In order to make the
difference zero you need to add the new projects.
Follow the below steps to add projects:
Ungroup columns to level 2
14
ESSBASE 101
Insert a row in the sheet where you want the new project to be located
Highlight the row above your inserted row & copy (Ctrl + C, or right click and choose Copy)
Highlight the inserted blank row & paste
Enter the new project number (cell C85 in the above example)
THE ONLY FIELDS THAT NEED UPDATING ON THE INSERTED ROW ARE JOB STATUS (Existing vs. New), PROJECT NUMBER AND JOB STAGE (OPN, WUP, LIT, etc.). ALL OTHER FORMULAS WILL BE UPDATED BY EXCEL TO CORRESPOND TO THE NEW ROW.
If a Job Stage has changed (for example, from OPN to WUP), you must make sure that you update the Job Stage in the Exhibit. All attributes on the Performance Review template must match the information in Essbase to get results to appear.
Refresh each Exhibit and ungroup “CROSS CHECK WITH TOTAL PROJECTS IN ESSBASE”. The difference in each should be
zero. If the difference is not zero, new projects are still missing & need to be added. If there are still differences after
adding new projects, please review the Essbase 201 Intermediate training documents and training call video to learn
how to research differences using custom Ad Hoc worksheets.
If you still need help after reviewing the Intermediate training, contact a Business Analyst or FP&A to learn about when
Open Office Hours are for addressing your individual issues.
Accounts Cheat Sheet
Account Balances Periodic QTD YTD ITD Period N/A Notes
Actual Cost X X X X
Actual Revenue X X X X
Backlog Cost X
Backlog Revenue X
Earned Cost X X X X
Earned Revenue X X X X
Overbilled X X X X
Project Completion % X
Projected Final Cost X
Projected Final Revenue X
Provision for Loss X X X X
Revised Cost Budget X
Revised Revenue Budget X
Underbilled X X X X
Profit Members Periodic QTD YTD ITD Period N/A Notes
Actual Profit X X X X
Backlog Profit X
Earned Profit X X X X
Projected Final Profit X
Revised Profit Budget X
AP/AR Periodic QTD YTD ITD Period N/A Notes
Accruals X
AP Aging X
AP Current X
AP 60 X
AP 90 X
AP 120 X
AP 120+ X
AP Retainage X
AR Aging X
AR Current X
AR 60 X
AR 90 X
AR 120 X
AR 120+ X
AR Retainage X
Cash Disbursed X
Cash Position X
Cash Received X
Claims Info Periodic QTD YTD ITD Period N/A Notes
Change Orders X
Claims X
Timing X
Other Project Attributes Periodic QTD YTD ITD Period N/A Notes
Inception Date X Year N/A, Entity N/A, Project Status N/A, JDE
Actual, Periodic
Original Contract Cost X
Original Contract Profit X
Original Contract Value X
Smartview URL: http://hy-pweb01.tutorperini.com/workspace/SmartViewProviders
15