CONSULTING
SOLUTIONS
PARTNERS
RESOURCES
CORPORATE
Twitter Facebook LinkedIn
Email [email protected]
Phone +1 713.457.7474

RESOURCES CATEGORY: Finance

IBM Cognos TM1 Feeders

BLG

What Are Feeders?

In a sparse cube with relatively few values compared to the number of cells in the cube, it can cause a severe drag on system performance to require the cube to calculate every cell when only a few values will result from those calculations. To combat this, TM1 allows you to use the SKIPCHECK function to tell the rules to only calculate values for cells that will result in non-zero values.

To put it briefly, feeders are merely markers signifying to the rules which values are to be calculated and which are to be ignored. Think of it in the context of a cubeview. The feeders essentially mark each cell that needs to be calculated. The rules then come behind that and actually perform the calculations on only the marked cells.

Feeder Misconceptions (Or, How To Overfeed)

One of the most common misconceptions – especially when you’re writing your first rules – is to feed all parts of a calculation. Take the following rule – calculating an employee’s monthly bonus in a generic headcount cube – as an example:

[‘Monthly Bonus’] = N: ([‘Annual Salary’] * DB(‘Calendar’, !Year, !Period, ‘Monthly’, ‘Percent of Year’) ) * [‘Bonus %’];

I’ve found that many developers will assume they need to feed all parts of the calculation. So, for the above example, they will feed “Monthly Bonus” with “Annual Salary” and “Bonus %” from the same cube, and “Percent of Year” from the “Calendar” cube. However, all they’re doing is “marking” the cell for calculation three separate times when all they had to do was mark it once. This is adding three times the amount of processing time per cell. Extrapolate that out to the number of cells in your cube and you’re talking about a serious performance issue in your model.

So the cell only needs to be fed once, and to do this in the most efficient way possible you feed it with the part of the calculation that will most likely signify that the cell will result in a value of zero.

To elaborate on this point a bit: what universal logic can we grasp that will help us in determining the best possible feeder? Well, first you have to separate these logical truths into two categories: multiplication/division calculations, and addition/subtraction calculations.

Feeders for Multiplication and Division Calculations

Multiplication and division calculations are our best friends. In these calculations, we truly need only one feeder. Take a simple example of calculating a monthly FICA amount at the most basic level – Monthly Salary * FICA %. If either of these amounts equals zero, the FICA amount is going to be zero.

So we take this logic and conclude that, in fact, we only need one feeder – the one most likely to be zero. The FICA percentage is a single number (a lot of companies take them as two – Social Security and Medicare, but for simplicity’s sake we’ll assume that it’s one percentage), provided by the Social Security Administration every year. This percentage will never be zero, so we can rule it out as a possible feeder. The Monthly Salary, however, is a good feeder because unless our company has an employee who loves his job so much that he’s willing to work for free, it should be a pretty good indicator that we need to calculate that employee’s FICA %.

This same logic can be applied to division calculations. If the numerator is zero, the calculation must result in zero. If the denominator is zero, the calculation is illogical because you cannot divide anything by zero, and this is handled by TM1 as a zero.

Feeders for Addition and Subtraction Calculations

Feeders for addition and subtraction calculations, while not trickier, are slightly more involved. Take FICA for instance, and now assume that we do prefer to split FICA into its’ two parts – Social Security and Medicare. If we didn’t want to include these as children of a consolidation, we would calculate “FICA” as:

[‘FICA’] = N: [‘Social Security’] + [‘Medicare’];

However, if either “Social Security” or “Medicare” were to be zero, this would not guarantee that “FICA” will be zero for the obvious reason that x + 0 = x. The solution to this, unfortunately for us, is to feed both “Social Security” and “Medicare”. The same can be said for subtraction calculations, as x – 0 = x. With this in mind, let’s examine each of the parts of the above rule to see which we should use to feed “Monthly Bonus”:

  • Annual Salary: every employee should have a salary, so if we run across a “Monthly Bonus” cell belonging to an employee slot that does not have a salary, we can safely assume they will have no monthly bonus since any value multiplied by a zero will result in a zero. But this does not necessarily mean they will have a Monthly Bonus since not all employees receive a bonus, so let’s continue to the other parts of the calculation.
  • Percent of Year: we can instantly eliminate this as an option because there will never be a non-zero “Percent of Year” amount. If we were to use this as a feeder every cell would be fed, negating the entire advantage of feeders.
  • Bonus %: assuming every employee who receives a bonus has their own “Bonus %” populated, this is a great indication that “Monthly Bonus” for a given employee will need to be calculated. We should use “Bonus %” to feed “Monthly Bonus”.

[‘Bonus %’] => [‘Monthly Bonus’];

However, another misconception is that a feeder even needs to be a part of the fed calculation. This is not at all true. Remember, you’re just “marking” a fed cell, in the easiest and most efficient way possible.

Say that we need to derive an hourly pay rate from an annual salary:

[‘Hourly Rate’] = N: [‘Annual Salary’] \ DB(‘Calendar’, !Year, !Period, ‘Annual’, ‘Hours’);

Based on our prior analysis of “Monthly Bonus”, we can eliminate “Hours” in the Calendar cube as a contender to feed “Hourly Rate”. We could definitely use “Annual Salary” to feed these cells.

However, say we have another element called “FTE”. This is basically used to signify a valid employee with a value of 1, and could also be used to calculate part-time employees with a value less than 1 – say, 0.5. But no matter what specific value it is, if it’s greater than zero it signifies a valid employee. And since any valid employee will have some value in “Annual Salary”, “FTE” would also be a great feeder for “Hourly Rate”:

[‘FTE’] => [‘Hourly Rate’];

At this point you might be asking yourself, “Why wouldn’t I just use ‘Annual Salary’?” Well, in the instance of a single rule such as “Hourly Rate”, yes it might not make much sense to confuse the situation by using a value outside of the components of the calculation you’re feeding. However, consider the situation (a very likely one) where you have a number of these elements that you can assume every valid employee will have in all situations. Instead of feeding them with parts of their calculations, which will all be different and obviously dependent upon how they’re calculated, you can potentially feed them all with “FTE”:

[‘FTE’] => [‘Hourly Rate’],
[‘Social Security’],
[‘Medicare’],
[‘FUTA’],
[‘SUI’];

This example is a purely aesthetic one, but it could potentially help performance in the event you are pulling amounts from another cube, one-for-one with no calculation on the amount. We’ll discuss this further in the following section on inter-cube feeders.

Inter-Cube Feeders and How to Avoid Them

In my experience inter-cube feeders are the biggest drag on performance, and they should be avoided if possible. Continuing our example with “FTE”, we can use this value to aid us in pulling amounts from another cube. Let’s say our originating cube (“PositionInfo”) is just a snapshot in time of employees (i.e. it does not have time dimensions) and we need to pull those values into our “Headcount” cube and apply them to Period and Year. We can feed “FTE” in the “Headcount” cube like this:

[‘FTE’] => DB(‘Headcount’, ‘Fcst Years’, ‘Year’, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

It (hopefully) goes without saying that when you’re dealing with inter-cube feeders you’re going to run into situations where your dimensions do not match and you’re forced to feed to consolidations, as we do in this example when we feed to all forecast years in the “Year” dimension and all months in the “Period” dimension. Feeding to consolidations is probably the single biggest reason as to why you should avoid inter-cube feeders as much as possible. When you’re feeding to a consolidation such as “Fcst Years”, you’re actually creating multiple feeders, equal to the amount of elements in the consolidation. To illustrate this, imagine we actually have time dimensions in our originating cube (“PositionInfo”); we would feed the “Headcount” cube with one feeder:

[‘FTE’] => DB(‘Headcount’, !Year, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

However, when we feed to “Fcst Years”, we’re essentially creating the following feeders:

[‘FTE’] =>

DB(‘Headcount’, ‘2011’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),
DB(‘Headcount’, ‘2012’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),
DB(‘Headcount’, ‘2013’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),
DB(‘Headcount’, ‘2014’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

As with feeding each component of a calculation, this creates a huge strain on performance and should be avoided. But it cannot always be avoided, and so you should take actions that minimize the adverse affects. So let us operate under the assumption that if we can feed our great “universal” feeder (“FTE”) from the “PositionInfo” cube to the “Headcount” cube, we can then create all our other feeders inside the “Headcount” cube. Here would be our feeder from the “FTE” element in the “PositionInfo” cube to the “FTE” element in the “Headcount” cube:

[‘FTE’] => DB(‘Headcount’, ‘Fcst Years’, ‘Year’, !Scenario, !Company, !CostCenter, !Location, !Employee, ‘FTE’);

We would then be able to use that newly-fed “FTE” element in the “Headcount” cube to feed all of the elements for which we otherwise would have written inter-cube feeders:

[‘FTE’] => [‘Hourly Rate’],
[‘Social Security’],
[‘Medicare’],
[‘FUTA’],
[‘SUI’];

Conclusion

Hopefully this can help some of you to improve your model’s performance through making your feeders more efficient. We’re never going to completely eliminate the headaches that come with feeders, but we can at least minimize the heartburn that comes with a poorly-performing system.

EPM Success for Geophysical Survey Company

NWS

PREDICTif  successfully assists major geophysical survey company to select the right Enterprise Performance Management product for its financial reporting and analysis needs

CUSTOMER:

A leading seismic data-acquisition imaging and software systems company who assists petroleum exploration companies to identify and measure subsurface geological structures.

REQUIRED CAPABILITIES

  • Financial consolidation and reporting
  • Interactive analysis
  • Forecasting and Budgeting
  • Oracle Hyperion Planning, Essbase, Financial Reporting, Financial Data Management

CHALLENGES

The Customer used an out-dated solution that was under-utilized and deficient in terms of data mining and planning automation. As a result, the business was heavily dependent upon IT for both support of its existing solution and development of new functionality when the enterprise required. The software deployed was also 2 major versions behind that of the publicly released version and had run out of vendor support. Significant growth in its Solutions business units served as a catalyst to replace its manual, error-prone, labor intensive financial reporting and planning solution with a more automated, streamlined and efficient one.

The company, comprised of multiple legal entities that span the globe, was challenged with having to manage all of its financial operations in a single, multicurrency environment. It also had needs to transform individual financial plans from a group of entities into a single, consolidated corporate level plan.  All this made the existing solution severely limiting in terms of the ability to predict its revenue and costs for each of its business units.

SCOPE

Needing objective and unbiased professional advice on various EPM solutions on the market, the Customer engaged with PREDICTif who led a rigorous product selection process to ensure that they procured the right EPM product for its current and future needs. The overall goal was to assist the Customer in identifying an EPM product that met its current and future business needs:

  • Integrate seamlessly with its main ERP solution (Infor’s Syteline), its existing Microsoft SQL Server based data warehouse and miscellaneous data sources;
  • Upgrade its antiquated and unsupported EPM architecture to a modern and sustainable one;
  • Automate and streamline its financial reporting and planning processes eliminating manual, redundant and time-consuming steps;
  • Enable drill-downs and what-if analysis for gaining insight and better financial plans;
  • Establish a single version of the truth and generate accurate/ up-to-date reports;
  • Leverage both web and Excel as available interfaces for ad-hoc analysis, interactive report authoring and viewing;
  • Offer an easy-to-learn and easy-to use tool that enables the business to achieve self-sufficiency; and
  • Ensure long term, advantageous total cost of ownership.

 

SOLUTION

PREDICTif led a systematic product selection process leveraging its extensive experience with EPM solutions, vertical specific evaluation criteria and its own, proprietary production selection templates to ensure a thorough evaluation was undertaken. PREDICTif led the preparation of the PoC, the coordination of the vendors and finally, the short-listing and evaluation/selection of vendors who best addressed Customer’s business requirements.

Over the course of the evaluation, PREDICTif:

  • Assisted the Customer in its identification of the appropriate use cases that addressed all critical business needs;
  • Prepared the use case documentation and accompanying data;
  • Worked with Customer personnel in order to facilitate software vendors’ involvement in the PoC;
  • Supported the Customer as needed throughout their participation in the evaluation process;
  • Administered the different software vendors’ PoC’s and validated their results;
  • Incorporated PREDICTif’s proprietary software evaluation process and compared/contrasted its weighted scoring criteria against that of the differing, competing vendor technologies;
  • Led vendor’ demos and meetings in an effort to contribute towards the information gathering process;
  • Provided cost estimates for license and implementation costs; and
  • Produced a high level roadmap and long term implementation plan for each technology so that the Customer understood not only the current state, but also the future vision of the chosen technology.

The PoC was conducted in parallel by three vendors over the course of a two-week period.  We aggregated all the gathered information and then, used PREDICTif’s weighted software selection criteria to contrast that against the Customer’s business objectives to render an option on a best of breed solution.

After evaluating several leading vendors, Oracle Hyperion/Essbase was selected for a number of reasons:

  • Oracle Hyperion Enterprise Performance Management (EPM) software meets the key business requirements, enables the automation of planning and reporting processes and powers the finance team to perform data mining and ad-hoc analysis;
  • Oracle Hyperion is easy to use, easy to implement and easy to support/maintain;
  • It is a cost effective, high return investment, conducive to quick wins, resulting in lower total cost of ownership;
  • It establishes a solid platform for future reporting and analysis needs; and
  • Oracle utilizes updated and modern technologies, presents a strategic product development roadmap and enjoys both financial as well as organizational stability.

RESULTS

Post product selection, the Customer negotiated with Oracle and ultimately procured the Hyperion product stack including Essbase, Hyperion Financial Reporting, Hyperion Planning and Financial Data Manager (FDM). Utilizing the acquired Oracle stack of products, the PREDICTif team successfully implemented and replaced the customer’s outdated solution with that of a new one fully automating countless functions and in the process, eliminating nearly half of the Excel worksheets it previously utilized resulting in increased productivity. Additionally, PREDICTif:

  • Upgraded the customer’s financial reporting and analysis architecture to a modern and fully supported technology;
  • Increased the architectures performance resulting in a more robust and stable environment;
  • Eliminated a multitude of manual, error-prone and time-consuming steps resulting in a more efficient and accurate financial analysis and reporting process;
  • Delivered to the business users the ability to do ad-hoc analysis and create their own reports;
  • Delivered a solution to the business that the customer personnel were able to support and maintain without significant IT support or involvement; and
  • Provided the business a solution that resulted in a significant return on investment.

JB Poindexter makes substantial hardware cost savings thanks to IBM Cognos Express

NWS

With help from IBM Cognos Express and PREDICTif Solutions, JB Poindexter and Co. was able to upgrade, consolidate, and standardize their diverse reporting systems, saving at least $50,000-75,000 a year in hardware costs, and increasing productivity by 100-125%.

CUSTOMER:

J.B. Poindexter & Co., Inc. is a privately held diversified manufacturing company with operating subsidiaries engaged in the production of commercial van bodies, step-vans, funeral coaches, limousines, mid-sized buses, pick-up truck bed enclosures and tonneau covers, precision machined components and expandable foam plastics.

The company operates with a decentralized management and operating structure. Subsidiaries have complete operational, financial and administrative responsibility, with professional and executive management support provided by the corporate staff.

“The highly knowledgeable consultants at PREDICTif understand business intelligence. That’s why they recommended IBM Cognos technology to fit our business needs, technology that we are very happy with. IBM Cognos is intuitive, can be owned and managed by the business, and has amazing reporting, planning, and dashboard features. It also happens to be one of the best bargains on the market.”

Jay Krishnamurthy Vice President-IT, J.B. Poindexter and Co.

CHALLENGES

J.B. Poindexter & Co. has grown significantly in the last few years, acquiring six separate business units. As each unit maintained its own staff, product lines, customers, as well as its own ERP system, reporting processes were difficult to manage. The business and corporate units had limited visibility into crucial arenas, such as performance at plant levels, costs, margins, overhead, material savings, etc. Due to the nature of their sustainable growth, they lacked consistency in information and standardization processes.

In addition, the information reporting and planning processes that were in place were antiquated. With manual and laborious systems in place that included spreadsheet formatting, crucial business information was often inconsistent and difficult to come by in a timely manner.

J.B. Poindexter & Co. needed away to upgrade their system into an automated, consolidated, and standardized. That way, corporate and business units would be able to quickly and easily view crucial information at both a detailed and broad level, allowing them to manage effectively and respond to any potential problems or risks immediately.

APPROACH

  • Leverage PREDICTif’s recognized experience and expertise to implement a business intelligence solution tailored to JB Poindexter and CO.’s needs.
  • Purchase multiple IBM Cognos Express modules.
  • Consolidate, standardize, and automate reporting and forecasting processes across all distinct business and corporate units.

RESULTS

  • Implementing IBM Cognos and PREDICTif services, provided end users with an intuitive budgeting, forecasting, and reporting solution with BI capabilities.
  • Created a system of easy-to-understand dashboards that automatically provide critical information across all business units, allowing management and corporate to assess performance and risk in a timely manner.
  • Implemented a cost-effective solution, saving $50-75,000 a year in hardware costs alone.
  • Eliminated the need for report builders by empowering end users with an easy-to-use system.
  • Allowed IT staff to focus on IT needs instead of spending several hours a day loading data and fixing reporting issues.
  • Increased productivity by 100-125%.
  • Reduced time to deliver manual reports by well over 200%.

BENEFITS AT A GLANCE

  • Upgrade and consolidate budgeting, forecasting, and reporting systems into one easy-to-use solution, IBM Cognos Express.
  • Using IBM Cognos Express, significantly reduce manual reporting from the separate business units by over 200%.
  • Generate consolidated, standardized reports that include product line costs and margins, fast and efficiently to make better business decisions.
  • Create easy-to-use reporting dashboards across all business units and corporate units, so that all levels of management can assess performance and risk in real time.
  • Increased productivity by 100-125%.
  • Employ PREDICTif Solutions  consultants to install a cost-effective solution, and even complete phase one of the project early and on-budget.
  • Leverage PREDICTif’s proven expertise to build a solution specifically tailored to J.B. Poindexter’s needs.

Financial reporting & consolidations solution for a leading Oil and Gas Equipment services company

NWS

Delivered a financial reporting and consolidations solution in a multi-currency and multi-account environment

CUSTOMER:

A leading Oil and Gas Equipment services company.

REQUIRED CAPABILITIES

  • Financial consolidation and reporting
  • Finance group’s self sufficiency
  • User friendly interfaces

CHALLENGES

As one of the leading global equipment service and pipeline construction companies, the customer faced numerous challenges related to monthly financial consolidation processing. In preparation for the opportunities and demands of being part of a publicly held company, the customer and its 25 plus legal entities had to manage all of their financial operations in a multicurrency environment, transforming individual financial statements from a group of entities into a single financial statement.  Their legacy solution which consisted of putting consolidated statements together using spreadsheets had severe limitations.

  • Disparate spreadsheets were not a scalable solution to implement across multiple lines of business;
  • Manual consolidations processes were complex, error prone and left no time for analysis; and
  • Currency translation with exception rates for items like intercompany receivables and common stock became a maintenance dilemma.

SCOPE

Efficiency, user-friendliness and self sufficiency were the three main objectives of this project.

  • Deliver a Cognos TM1 based solution to streamline the monthly financial consolidation process;
  • Provide a user-friendly user interface for analysis, ,reporting and dissection of data in an interactive manner; and
  • Establish self sufficiency so that the customer’s business users can easily support and upgrade the solution independent of its IT group.

SOLUTION

PREDICTif was engaged to provide an end-to-end solution and after evaluating several potential software vendors, IBM Cognos TM1 was selected for a number of reasons most notably,  the ability to provide a real-time approach to consolidating, viewing, and editing enormous volumes of multidimensional data.

Cubes having dimensions ranging from 3 to 10 were created. GAAP entries, Eliminations and Adjusting entries were made through the TM1 Consolidations product.   Reports created both in TM1 and Excel were readily accessible through the web with no additional effort.

  • TM1 is an in-memory OLAP server, which means that can typically respond to queries more quickly than its competing products;
  • By implementing TM1, the customer was able to compress the consolidations process into a matter of hours, giving analysts much more time for data analysis; and
  • The customer found out that it could eliminate numerous steps from the process itself further contributing towards more time on analytic activity rather than the mechanics of generating month-end reports.

PREDICTif provided a training curriculum that was tailored to the customer’s needs and culture. Power users received comprehensive technology and solution specific training and as a result, quickly became proficient enough to instruct other end users.

RESULTS

PREDICTif delivered significant value to the customer’s return on investment related to the software and IT infrastructure investment. The solution received positive feedback from the customer’s user population and significantly improved the financial consolidation and reporting process.

  • The deployment of the TM1 Consolidations and Reporting solution reduced the number of hours spent generating the consolidated financial reports and freed up considerable more time for analysis;
  • The network traffic due to large volumes of attachment in the email has decreased;
  • Financial analysts can generate reports and dissect large volumes of data using an interface they are familiar with; and
  • The customer’s finance group uses, supports and enhances the solution without dependence on the IT support.

crc1
Consolidations Process After TM1

crc2

email [email protected]
phone +1 713.457.7474
Twitter Facebook LinkedIn
© PREDICTif Solutions. All rights reserved.