Friday, February 5, 2010

Developing a Reporting Solution with Hyperion Essbase – Part1

Identifying the need of the Essbase

This is the first part in the series - Developing a Reporting Solution with Hyperion Essbase considering an ERP System PSGL as source.
we will discuss the issues and solution design approaches in all the phases of developing an Essbase application.

We could do all our reporting from the ERP system but why should we use Essbase to do it?
The answer is related to the necessity for Speed, Performance and complicated business logic processing. The need for multiple users to generate reports simultaneously and quickly.

How can we expect speed and performance from Hyperion Essbase?
Essbase — an OLAP Server, handling all data storage, caching, calculations, and data security. It supports drill down, drill up, slicing and dicing.
key strengths of Essbase - Fast reporting and analysis, powerful calculation engine for the complicated business logic processing, organized in dimensions of data – a.k.a the business view, write back ability.
Essbase is a blank sheet of paper and Essbase can be designed and tailored for any business, any industry, any application.

To explain OLAP - On-Line Analytical Processing, we will first look into OLTP.
This stands for On-Line Transaction Processing. ERP (Enterprise Resource Planning) systems, such as Oracle E-Business Suite, SAP, Peoplesoft etc, are considered to be OLTP systems. That is, they are designed and optimized primarily for Transaction Processing.

OLAP is a category of applications or technology for collecting, gathering, processing and presenting the multidimensional data for analysis and management purposes.
On-Line Analytical Processing (OLAP) differs from On-Line Transaction Processing (OLTP) in that its primary function is to perform analytical processing for the transactions occurring in the OLTP Enterprise Resource Planning system.
Although ERP systems provide a reporting capability, there are significant advantages of using an OLAP system for analyzing and reporting the transactional data.

In short, the functional requirements for OLAP are as follows:
-> Rich dimensional structuring with hierarchical referencing
-> Efficient specification of dimensions and dimensional calculations
-> Separation of structure and representation
-> Flexibility
-> Sufficient speed to support ad hoc analysis
-> Complex business logic processing
-> Multi-user support
-> Robust security

System Speed and Performance
Consider a typical General Ledger, which contains the raw data for most management and statutory reporting. Usually, ERP systems store the individual transactions (i.e. GL journals and postings from sub-modules such as Accounts Payable, Purchasing, Fixed Assets, and Inventory etc.) but they do not store the account balances.


When you run a report which requires account balances, these are calculated by adding up the individual transactions for the period(s) in question. Although this is usually a task undertaken by the underlying relational database, and is relatively efficient, this still places a considerable load on the system.

This might be acceptable outside of normal working hours (assuming that the system is not accessed 24/7 from multiple time zones) but if many such reports are run during the working day, performance can deteriorate for other users performing the tasks for which the ERP system is designed and optimised, namely entering transactions.

The relational database on which the ERP system sits is not designed to 'understand' the tree hierarchies for each segment of our GL accounts.

The report user typically understands that a number such as Cost of Goods is made up of a number of separate elements, such as Purchases, Stock Movement, Carriage and Settlement Discounts. These elements may also each be represented by a number of separate GL accounts.
If the user needs to analyse the detail behind one of these numbers on a report (i.e. drill-down), it is usually necessary to run a completely separate report to get the detail, placing a repeated load on the database and further reducing system performance.

Apart from the system performance problems with this approach, the production of the report itself can often be a time-consuming process. Certainly the generation of the numbers is not likely to be up to the speed at which the user can request and analyze them.

Considering Essbase as a Solution
Typically, data is extracted from the General Ledger (or other modules) either every night, or perhaps just at Period End, depending on the requirement.

The account balances (not the individual transactions) are loaded into the Essbase cube which not only knows the account codes, but also contains the groupings or consolidation levels to provide the higher-level 'roll-ups' such as the Cost of Sales example we discussed earlier. Not only are the individual account balances stored, but also the system calculates and stores the rolled-up numbers as well.

It is now possible for the report user to interrogate the OLAP system directly. He or she might start with a high-level report showing, for example, a complete Profit and Loss account. The report is generated almost instantaneously as all the required numbers are immediately available from the database without additional calculation.

If further detail is required on a number such as Cost of Goods, the user can drill-down on the number and get an immediate response showing the individual balances which make up the rolled-up number.

Lastly, the extra load on the ERP relational database caused by ad-hoc report generation is entirely removed and replaced by a single extraction routine which can be controlled centrally to ensure that it is run at an appropriate time to minimize user inconvenience.

Conclusion
The design of the OLAP reporting systems and the interfaces between them and the ERP systems are critically important - effort in this area should not be underestimated.
It is also sensible to integrate the thought processes behind OLAP system design, reporting requirements and GL Chart-Of-Accounts and data conversion at the earliest possible stage of an implementation project, particularly if the OLAP tools are to be used for Budgeting and Forecasting in addition to their more traditional reporting roles.

87 comments:

  1. Ohh glad i found your blog post i was struggling with BSO partitioning for soo long and was unable to figure out the solution but thanks to your post my struggle ended

    ReplyDelete
  2. Apollo Tree Surgeons Services is a totally guaranteed and experienced tree removal service company.
    We give out all varieties of tree surgery works like felling trees, stump grinding services, tree survey and also we offer our customers Garden Landscaping
    Services.

    TREE SURGEONS EDINBURGH

    ReplyDelete
  3. You wrote this post very carefully. The amount of information is stunning and also a gainful article for us. Keep sharing this kind of articles, Thank you.Epicor Erp Implementation Consultant Chicago

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Thank you for the useful information. The post was written with great knowledge and research. Scaled Solutions Group ERP Consultants

    ReplyDelete
  7. Great work on this blog! It provides lots of information. Scaled Solutions Group

    ReplyDelete
  8. This post is a job well done. Thank you for your insight on this topic. Scaled Solutions Group

    ReplyDelete
  9. Great article on information dealing with Hyperion. Very useful stuff. Scaled Solutions, LLC

    ReplyDelete
  10. The information here on developing reporting solutions is well thought out. Thanks. Scaled Solutions Independent Consultants

    ReplyDelete
  11. I have been looking for more information regarding OLAP systems and this article helped me greatly. Thanks. Scaled Solutions Chicago

    ReplyDelete
  12. Excellent amount of imformation on this article. We are very pleased with it. Scaled Solutions Group Chicago

    ReplyDelete
  13. Our company finds the information in this article to be very useful to us. Thanks for your work and keep it up. Epicor Independent ERP Consultants

    ReplyDelete
  14. Great work on the article, it is very well written. Are you able to produce more like it? Thanks. Scaled Solutions Consultants

    ReplyDelete
  15. Your writing skills are demonstrated very well here, along with your knowledge in the subject. Excellent work. Scaled Solutions Consultants

    ReplyDelete
  16. I would love to find more information on this topic. Do you have more written like this one? It would be very useful to us. Scaled Solutions Group

    ReplyDelete
  17. We are very impressed with how you wrote this. There is clear knowledge and good information. Please continue to produce articles such as these. Scaled Solutions Group

    ReplyDelete
  18. Our ERP consultants are very grateful for the information you provided. Spectacular work. Scaled Solutions Group

    ReplyDelete
  19. You are very skilled in this writing. You have satisfied us very well. We are interested in viewing more of your work. Scaled Solutions Group

    ReplyDelete
  20. It is clear that you put a good amount of thought, research, and time into this article. Great work. Scaled Solutions Group

    ReplyDelete
  21. Great article. We are very impressed with your skill. Keep it up! P21 Software Consultants

    ReplyDelete
  22. Wow. You are well versed in this topic and have written it with great detail and information. Great job! Digital Transformation Consultant Group

    ReplyDelete
  23. Your work here is of good value to our consultants. There is substantial information for us to follow and review. Thank you. Epicor Implementation Consultant Group

    ReplyDelete
  24. You have written this article with excellent creativity and useful information. ERP Consultant Group

    ReplyDelete
  25. The high level of knowledge this was written with is very apparent . Good job. ERP Software Consulant Group

    ReplyDelete
  26. The high level of knowledge this was written with is very apparent . Good job. Scaled ERP Software Consultant Group

    ReplyDelete
  27. Because of your attention to the information inserted in this article, I was able to find the answers I needed. Thanks. Scaled CPG ERP Solutions Consultant Group

    ReplyDelete
  28. There is great and thorough work here on this topic. Great job. Please continue the good work. Scaled ERP Solutions Consultant Group

    ReplyDelete
  29. Your attention to detail and clarity really captivates us! Awesome work here. Scaled ERP Solutions Group

    ReplyDelete
  30. This article is a great demonstration of part of a well -written, thought out, and informational blog post. Impressive! Scaled Solutions Consultant Group

    ReplyDelete
  31. This a great example of a well written blog post. It is full of very useful information. Come check out ours. Scaled Solutions Consultants

    ReplyDelete
  32. I have been doing research on this topic and am very glad to have found this information. Thanks for your help in this work. Scaled Solutions Consultants

    ReplyDelete
  33. This article is detailed enough that I feel I can bring it to others knowing they will benefit from it. Scaled Solutions Consultants

    ReplyDelete
  34. The concepts in the article are clear and concise and to the point. Great material covered here! ERP Consultants Near Me

    ReplyDelete
  35. You as the rhetor of this article reach the right audience using this genre and medium. Great work. ERP Consultant Group

    ReplyDelete
  36. I can come to this article knowing it is a reliable source with citations and background information. This is very pleasing to us. Thanks. Scaled Solutions ERP Consultant Group

    ReplyDelete
  37. When researching more information on this topic, people can come to this article and feel as though they have found an in depth summary of the topic. Very useful article! Scaled Solutions Digital Transformation Consultant Group

    ReplyDelete
  38. I think you caputure the intended audience very well with the wording and knowledge in this blog article. Very nice job. Scaled Solutions ERP Digital Transformation Consultant Group

    ReplyDelete
  39. I find the information you provided was well written, making it a smoother read for readers. It is also very knowledgeable and it hekps that backgound sources were also provided. Great post. Scaled Business Consultants

    ReplyDelete
  40. The format of this post is well thought out and allows for an easy read. It shows you are knowledgeable in the topic well enough to organize it in that way. Scaled ERP Business Solutions Consultants

    ReplyDelete
  41. I am very pleased with the word choices you used in your post. These good word chices and the format you chose allows for me to follow the content better. A job well done on this one. ERP Business Solutions Consultants Scaled

    ReplyDelete
  42. Choosing this modality was a great option. Would you try a different mode? Maybe a short youtube video? Never the less, good work. Growing Business ERP Solutions Consulting

    ReplyDelete
  43. This article post plays a role in a few information cycles. It would be in the months catergory and specific people would be looking for this information to satisfy their needs, whatever they might be. ERP Solutions Consulting for Organizations

    ReplyDelete
  44. If you are looking for a way to maximize your organization's business processes, we are here to help. Come check us out. Scaled Solutions ERP Consultants

    ReplyDelete
  45. When you wrote this article, it is clear that there was a good amount of effort and research put into it. I appluaud you for that. Scaled Solutions ERP Consultants

    ReplyDelete
  46. After viewing this article, I can say you have successfully satisfied my needs of looking for more information on the topic. Thanks. Scale Your Business

    ReplyDelete
  47. When taking a a look at the article, someone can find a good amount of useful information to satisfy their needs. You as the rhetor did a great job of this based on your audience. Scaled Business Consultants

    ReplyDelete
  48. I think when people search up this topic, and are at risk of being in a filter bubble, they can come upon this article and not worry about receiving false information. ERP Consultants for Organizations

    ReplyDelete
  49. Hey! The amount of information here is very helpful when I do asearch on the topic. A good amount is covered and is very valuable. ERP

    ReplyDelete
  50. Your use of information literacy here is verry efficient here. Scaled Solutions Group

    ReplyDelete
  51. Reading this article gets me thinking about the stakeholders involved. Most people involved should benefit, but there are those who might get stuck with more work than usual for a time period. SOP|EUP Consultants

    ReplyDelete
  52. The sources you included really help with your credibility on the article. That is very important when mking a claim. Implementation Project Management (Onsite| Virtual| Hybrid| Remote)

    ReplyDelete
  53. If you are looking for more information on ERP implementations or other similar services, come check out our website! scaledsolutionsgroup

    ReplyDelete
  54. Have you thought of expanding your thesis and research on this topic? You could make moree great content. Scaled Solutions

    ReplyDelete
  55. We are very impressed with your article here. The organization of it makes for a better read. Scaled Solutions ERP Consultants

    ReplyDelete
  56. Reading this article makes more questions come up for me such as who are the stakeholders involved and how would they be affected by this? Scaled Solutions

    ReplyDelete
  57. The medium chosen for the infomrmstion provided was well chosen. Would you consider choosing a different one? ERP Solutions

    ReplyDelete
  58. Completing any amount of research on a topic like this takes some skills such as information literacy. Good article. ERP Solutions

    ReplyDelete
  59. Taking a look at the exigence of this topic, your article falls not too far from it on the information cycle. Scaled Solutions

    ReplyDelete
  60. To others who may be interested in expanding their knowledge on this topic. Looking into your library's online resources may be of great use. Scaled Solutions

    ReplyDelete
  61. Taking a moment to think about the stakeholders involved, we can expand our research and knowledge of this topic. Scaled Solutions

    ReplyDelete
  62. Here we can get a better understanding of the topic from your useful article covering it. We appreciate that. Scaled Solutions Consultants

    ReplyDelete
  63. We can help expand on the information given here. We may have some additional information on our website. Please feel free to take a look! Scaled Solutions Consultants Group

    ReplyDelete
  64. The value of the information in this article is substantial. Very useful to anyone in search of answers. Scaled Solutions Consultants Group

    ReplyDelete
  65. What are some of the information literacy tools used to create this blog? This is very impressive. Scaled Solutions Consultants Group

    ReplyDelete
  66. When reading this blog, I can observe the amount of research, preparation, and organization put into it. Scaled Solutions Consultants Group

    ReplyDelete
  67. The genre chosen for this article is a very effective choice for the topic being discussed. Great job. Scaled Solutions Consultants Group

    ReplyDelete
  68. This blog is very informative. Did you use a mentor text to help guide you as you created this blog? Nice work here! Scaled Solutions Consultants Group

    ReplyDelete
  69. I can appreciate the amount of information given in this article. A craft article may have helped create this. Good job. Scaled Solutions Consultants Group

    ReplyDelete
  70. Good work with this article. If you ever have any questions with ERP implementations or digital transformations, we can help. Scaled Solutions Consultants Group

    ReplyDelete
  71. The article here is very informative of the topic. The reader can really appreciate coming to this source and finding answers to some of their inquiries. Scaled Solutions Consultants Group

    ReplyDelete
  72. We are very satisfied with the work you have put into this article! Nice work. Scaled Solutions Consultants Group

    ReplyDelete
  73. You did an excellent job here writing and organizing this blog! Impressive! Scaled Solutions Consultants Group

    ReplyDelete
  74. The information here in this blog really satisfies my needs and interests in this topic. Scaled Solutions Group

    ReplyDelete
  75. I am very pleased with your work laid out here in this blog article. I will be checking out some of your other works. Scaled Solutions Group

    ReplyDelete
  76. I can appreciate the information here because any further questions had may be based on what is already here. Giving us a kickstart. Scaled Solutions Group

    ReplyDelete
  77. There is a great deal of helpful information on this topic. Thanks for sharing. Scaled Solutions Group

    ReplyDelete
  78. Someone expanding their research on this topic can come here and find more answers and information. This is a successful and effective blog post. Scaled Solutions Group

    ReplyDelete
  79. This blog is a great resource to use when searching for additional information on this topic. Scaled Solutions Consulting Group

    ReplyDelete
  80. Great work here! I like how you organized the blog and chose your wording. Scaled Solutions Consulting Group

    ReplyDelete
  81. I am particularly impressed with this blog entry. The information here is plentiful and useful. Scaled Solutions Consulting Group

    ReplyDelete
  82. This is a very good article you have written here. Please consider making more one this topic. Thanks. Scaled Solutions Consulting Group

    ReplyDelete