What would happen if I did…in HFM?
February 19, 2016
Chris Barbieri, Matt Spencer
©2016
©2016
Finit EPM Services
Page 2
Full Cycle Implementations
Upgrades
Dedicated Support
©2016
Why is Finit here?
Page 3
Finit takes pride in being a company of makers and Doers; people who dig in and get their hands dirty
We make and deliver personalized financial reporting solutions and experiences
…Not Company to Company, but Person to
Person
By listening, questioning, and analyzing
©2016
The Finit Family
Page 4
Fully aligned with our clients, not just bottom line $
No debt or external ownership
Work with Finit employees, no subcontractors
Compensationbased on CLIENT SATISFACTION
PRIVATELY OWNED
CLIENT SATISFACTION
NOSUBCONTRACTORS
DEBT FREE
©2016
Finit Values
Page 5
Our values on which we ask you to provide consultant feedback:
• Approach & Collaboration
• Integrity & Honesty
• Client Betterment
• Self Betterment
• Colleague Betterment
• Finit Betterment
• Work Effectiveness
• Impact & Innovation
• Proactiveness& Passion
Service Solutions Betterment
©2016
Finit Customer Success
Page 6
Our values, culture, and approach to becoming a trusted advisor to our
customers has led to
100% customer success
for every Finit client (250+) and for every Oracle Hyperion project (500+)
©2016
Some Finit Customers
©2016
Questions
©2016
About the Presenter
Page 9
Chris Barbieri([email protected])
Certification/Education
• Oracle ACE• HFM Certified Consultant, all releases• Hyperion Enterprise Certified• MBA – Babson College, Concentration in
Information Systems and Entrepreneurship• BS – Boston College, major in Finance, minor in
Accounting
Experience
• 15+ years with HFM• 17+ years with Enterprise• 4 years as Product Issues Manager for HFM,
Shared Services, Smart View, and DRM
Client TestimonialBy Tuesday you accomplished everything we planned to cover this week and then you blew us away! Finit has gone beyond simply "teaching" how the applications work, they have provided a context to how best (and how NOT) to utilize the applications. I knew I could count on the Finit team!
©2016
Have you ever wondered what would happen if you…
• Changed an entity’s currency?
• Changed the IsCalculated flag on an account?
• Changed the Zero View settings?
• Renamed an account?
©2016
What would happen if I…
©2016
Changed an Entity’s Currency?
• Data and journals are stored by currency ID
• UKSales is GBP• <Entity Currency> is GBP
• Parent EastRegion is USD
©2016
Behind the Scenes
Data for “< * >” is stored in DCE tables
Decode this…
©2016
The Currency Change
Page 14
Before After
©2016
Classic Mode Metadata Change
• Error during load• Metadata Integrity Check
• Won’t load…
• … skip integrity check?
• Un-post affected journal(s)11.1.2.4
feature
©2016
Un-post Journal(s), then load metadata
Page 16
©2016
Successfully Changed?
Before After
• GBP no longer input-able
• EUR now input-able
©2016
About that Journal…
Page 18
• Is now posted as EUR data, not GBP• Is this what you want?
• 1,000 EUR is not the same as 1,000 GBP…• Either go back, translate offline from GBP to EUR and recreate
the journals• Or leave it if you intended it to be EUR from the start
• What about the loaded data?
©2016
Consolidate EastRegion
• GBP status is still TR• Is not the entity’s currency
• Is not the parent’s currency
• Translate EUR to GBP• Original GBP data is replaced
• USD changes accordingly
• Time to re-load all data!
Status after change Original data Re-load as EUR
*
©2016
What would happen if I…
©2016
Changed the IsCalculated flag on an account/custom?
• Sample account “CalculatedAccount”
• Begin our journey with this un-checked
©2016
Enter Data
• Enter “100” in <Entity Currency> which is EUR
• Naturally “101” is calculated (100 EUR translated to USD)
©2016
The Underlying Science
• Comma4Dim_DCE_1_2005 table• Stores currency data
• … basically any value dimension data not in “[ ]”
©2016
Transaction Type?
• Dp*_InputTransType tells HFM what sort of number it is• Input 16
• Calculated 32
• Derived 128
• Dp*_Input stores YTD amount• Each record holds 12 months, numbered “0” through “11”
• NoData 2• Prior to a period that has data
• NoData 96• After a period that has data
©2016
Extract Data
Include Type 16
Include type 32
Include type 128
©2016
Change Flag to IsCalculated
• IsCalculated does two things:• Built-in No Input rule
• HFM automatically clears the values prior to Sub Calculate, Sub Translate
• Enable IsCalculated
• Rules are independent of this setting• A rule can write to an input account
• Nothing forces you to have a rule when it’s IsCalculated
©2016
Load Metadata Change
• Database still shows Input (16)• Metadata change does not alter existing data
• Grid shows “no input”
• Data extract still includes “CalculatedAccount”• … because the
InputTransType = 16
©2016
This is Critical to Understand
The InputTransType stores the metadata attribute
at the time the number is written to the database!
If the ‘IsCalculated’ attribute changes on the Account/Custom, this is notautomatically updated in the data tables!
…until a new number is written to that cell
©2016
Consolidate Data
• HFM automatically clears only cells with type “32”, not “16”
Despite the IsCalculated flag
• Must clear it manually afterward• Using HS.Clear in rules or Database Clear, or some other
means
• Or remove data before adding flag• Can load “NoData” value
©2016
Let’s Add a Rule
Page 31
Don’t try this at home! Please don’t write constants to the database!
©2016
Consolidate All With Data
• Now it’s considered calculated
• Remove the rule and HFM will clear it outBeginning of next Calculate
©2016
What would happen if I…
©2016
Changed the Zero View settings?
• “How should I treat missing data?”• Zero YTD
• HFM will derive a periodic value so we arrive at zero on a YTD basis, reversing out the entire YTD balance from prior period
• Zero Periodic• HFM adds the periodic zero to prior period YTD, essentially carrying
prior period forward
• Applies only to Revenue, Expense, Flow type accounts
Scenario settings
©2016
Sample Sales Data for BrazilSales
• Notice February Periodic and YTD derived values?
• Periodic derived data is used for translation
“Let’s change Zero View to Periodic”
©2016
Change Actual Zero View to “Periodic”
• Metadata load prevented
• Un-post impacted journals and re-load metadata• Re-post same journals
Metadata change only Translate
©2016
Notice February Changes?
Re-post journals or re-load data will change your results!
YTD
Re-post JE Re-post JE and re-load data
Periodic Periodic
Original
©2016
What would happen if I…
©2016
Renamed an Account*?
• “Sales” should be “Revenue”
• Data and journals exist *Classic metadata
©2016
Merge vs. Replace Mode
Page 40
• Merge: leaves existing metadata in place and adds new members
• Replace: removes existing metadata and adds incoming• We’ll use this, and is most widely used
• Only for dimension(s) included in the file• Leaves all other dimensions alone
©2016
Metadata Load Fails
• As before, journals prevent the changes from going in
• Un-post and proceed
©2016
Spot the Missing Account Label?
Page 42
©2016
Re-post the Journals
• Previously extracted journals still refer to “Sales”• Need to edit all of these
• Mass change *.jlf to “Revenue” and re-load
©2016
What About the Saved Grid?
Page 44
Before
Error when opening
“Sales” column missing
©2016
Behind the Scenes: the Database
• Members are stored by ItemID
• Connect the tables• Dimension_Item
• Connects ItemID to Label
• lDimension field in other tables
Before
©2016
Table: After the Change
Page 46
• Account_Item table• ItemID 130 is gone• New ItemID 176 is in its place
• DCE: data table• Data for ItemID 130 remains• Data for ItemID 176 also exists, from the journal entry
Records for ItemID 130 are invalid
©2016
The Data
Page 47
• No way to recover “Sales” data• Must re-load the data, but into “Revenue”
• Must manually update objects that use the label “Sales”• Rules and MemberLists• Grids and forms• Reports
• Metadata re-load will create a new itemID, perhaps 177
• *EPMA will preserve the data• But you still have to manually update
objects
©2016
Questions?
Use the code “Finit” for $100 discount
when you register for Kscope16
©2016
Upcoming Webinars
Coming Soon - Intro to HPCM (Hyperion Profitability & Cost Management) – Srinivas Salguti
Coming Soon - Intro to Planning Part II -Cindy Eichner
Thank you for attending!
Questions?
Email us for a copy of the slides or link to the recording
Past webinars:
www.finitsolutions.com/webinars
Presenter:
Chris Barbieri
General Questions:
Cathy Lawton
Joe Mizerk