8 Replies Latest reply on Jan 5, 2018 12:31 AM by ORCLSendsMeToEarlyGrave

    Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?

    ORCLSendsMeToEarlyGrave

      Over  8 years ago our consultant from TUSK/Rolta installed our Hyperion Planning, HFM, EPM workspace, FDMEE etc using 5 different instances of Oracle databases.

      Over 5 years ago our EPM consultants from a "Big 3" recommended the same 5 repositories.

       

      1) Why do consultants recommend using 5 different instances of Oracle databases instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?  Our Oracle DB wants to know why this crazy configuration?

      Are our consultants installing 5 instances because that's what always works - i.e. when they ONLY install Planning they install its repository to one instance.  When they install HFM only, they install its repository to one instance.  Or is this to enable high availability?  Are the consultants trying to protect crashes in HFM repositories from affecting Planning or FDMEE's repositories?

       

      2) We are thinking of upgrading to 11.1.2.4 our new oracle DB wants to know if we can combine the 5 instances into 1 instance with 5 different schemas.  Will that complicate or upgrade ? For example, will the tables in these EPM  repositories be different if we had 5 instances vs 1 instance?

        • 1. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
          CBarbieri

          I believe there are two aspects in the answer: backup/restore procedures, and tuning parameters. As I understand Oracle databases must be backed up and restored at the instance level, not the schema level; and that some tuning parameters apply only at the instance level and may be different between products. Off the top of my head I know historically FDM and HFM had different and conflicting tuning parameters. I don't know if that's changed in 11.1.2.4 though. I am aware of the problem of having to restore only one application/product in an environment, so if you have everything in a single database I don't know how you can restore just one and not all products/applications.

           

          - Chris

          1 person found this helpful
          • 2. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
            Srini+K

            Work load each component puts on the Oracle database is main concern with which people suggest different databases.

             

            Sometimes rabbit only have 3 feet because someone said so.

                      ver  8 years ago our consultant from TUSK/Rolta installed our Hyperion Planning, HFM, EPM workspace, FDMEE etc using 5 different instances of Oracle databases.

                      Over 5 years ago our EPM consultants from a "Big 3" recommended the same 5 repositories.

             

             

             

            • HFM/FDM Case:
              •    HFM/FDM were not that efficient until 11.1.2.4..  We could not make EPM System busy with full consolidation in 2.2.  If the EPM repository is in the same database as HFM the storage load goes single threaded. How much ever magic Oracle does to keep the parallelism the buffer pool is hogged by HFM/FDM.
              •     RDB heavy HFM/FDM may be better off on separate DB regardless of parameters (even with recent improvements in 11.1.2.4) on a different storage, machine and database independent of Original EPM repository.
            • Planning/Essbase/EPMA does not take too much bandwidth from RDB so could be hosted in one database.
            • The main concern with SS and Workspace is how fast the user credentials are obtained from directory and show the "Explore" (RA?).
            • The machines have got more powerful in the recent past. some of the assumptions about load bearing capacity need to be re-thought.
            • The DBA in your case is right. 5 databases is a crazy configuration.
            • The high availability is one word I always found strange .  I worked with 500 company consolidations never had trouble (slow in 11.1.2.2) but good enough. 11.1.2.4 is atleast 4 times faster to reasoning behind high availability may have to be evaluated for merit.
            • If Planning gets its own instance of EPM system and HFM gets its own EPM instance instance... could be a design to have separate groups given control of the tool (based on the size of the company).  But if the Hyperion is centralized  do not see any reason why they have to be separate.
            • Ask DBA to check the load on the HFM and FDM supporting databases. OR have them compare the time and load chart for 1 past quarter.  That will give a perspective of the load.
            1 person found this helpful
            • 3. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
              ORCLSendsMeToEarlyGrave

              Thanks Chris.  I totally see your point.  So it's best practice to keep them separate.

              I'm sure to get more questions as to why I want to keep each app in separate Oracle instances. So I better find out if it is possible to restore just the schema for one EPM system (e.g. Planning) without affecting the other routines of other EPM systems.  We certainly can't have other schemas affecting the performance and availability of HFM schema.

              • 4. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
                ORCLSendsMeToEarlyGrave

                Thanks Srini.  Your statements "If the EPM repository is in the same database as HFM the storage load goes single threaded. How much ever magic Oracle does to keep the parallelism the buffer pool is hogged by HFM/FDM." is over my head, so I have to investigate it some more to confirm and understand them.

                 

                So those consultants are right in assigning an entire Oracle instance to each HFM, DRM, FDMEE, EPM and Planning systems.

                 

                How what are some of the different times  it take for you to consolidate HFM at different companies?  We have about 20 ledgers from different companies (some are acquired) to consolidate.

                 

                That's a great idea you gave: "Ask DBA to check the load on the HFM and FDM supporting databases. OR have them compare the time and load chart for 1 past quarter.  That will give a perspective of the load."  That's the first thing I do.

                 

                • 5. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
                  Srini+K

                  DRM always installed as separate instance until 11.1.2.3.  So separate DB may be prompted by it(older case). Again how much load DRM puts is a very interesting question. (size of it).

                   

                  Oracle made big changes to FDM(EE?) 11.1.2.4. ODI became the backed processor for skelitel remnants of the upstream fishes. Licensing has some control on usage of backend ODI as regular ODI.  But the FDMEE interface is very promising for transformations from multiple ledgers from HFM perspective. Great tool to control data transfer inside the suit. Not sure why we have datasync?.

                   

                  HFM application server (generally separate) in the distributed install always had big spikes in memory usage and CPU corresponding to the disk reads HFM "Records". May be improved in 11.1.2.4 never had chance to test out.

                   

                  The export and import database utilities let the DBAs export all the database structures by schema or "FULL" export.  They can always selectively import tables etc. https://docs.oracle.com/cd/B10501_01/server.920/a96652/ch02.htm#1005081

                   

                  The recovery of application instance data for HFM is separate task

                      than that of EPM Repository recovery (NEVER DO THIS).

                   

                  All the companies I saw earlier used same database for EPM system and Planning... up to 500 user planning system..

                   

                  As for the timing it is not good for me put in writing as data and rules drive how much things could consume from HFM standpoint.

                   

                  In my old life was 1/2 DBA.. those Buffer pools and the wording will be understood by DBA team, sorry do not intend to write something difficult to read but have to state the reasoning for separate database.

                   

                  As much as people believe having separate database makes everything separate these days..

                       it may not be true.. it is based on how database is handled on DBA side.

                       DBA teams need to be asked about ...(my thoughts) These questions will answer physical isolation high usage dbs vs low usage once

                       (planning/EPMA/Workspace etc.)

                                *  physical machine on which database resides.

                                *  physical storage array the machine is connected to.. (how it is connected.. FC etc. what speed).

                                *  How many independent storage paths or emulations the storage array is doing.

                                *  Dependence of one Database vs the other in the above three ways.

                  1 person found this helpful
                  • 6. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
                    ORCLSendsMeToEarlyGrave

                    Srini, I have no problems understanding your statement now that you explained it better:  "Buffer pools and the wording will be understood by DBA team, sorry do not intend to write something difficult ".  I've over 15 years post-relational SQL and DBA experience.

                    • 7. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
                      Srini+K

                      Hi ORCLSendsMeToEarlyGrave,

                      Good to know. sometimes my dry writing look childish.  appreciate you forgive that.

                      Thanks

                      • 8. Re: Why do consultants recommend using 5 instances of Oracle DB, instead of 5 schemas for Hyperion Planning, HFM, EPM workspace, FDMEE?
                        ORCLSendsMeToEarlyGrave

                        Your writing is Not dry at all, nor childish.  Your info is above is most useful to me and a lot of readers on this forum. I learned a new adage from you -  "Sometimes rabbit only have 3 feet because someone said so."