OracleBIBlog Search

Friday, February 26, 2010

The Impact of IFRS for EPM Reporting – Part 6

In Part 6, I want to provide more detail on the similarities and differences for reporting Financial Instruments. The details below were from notes I took during a presentation during a company sponsored educational seminar about IFRS.

Financial Instruments


Both require financial instruments to be classified into specific categories to determine measurement

Both require the recognition of all derivatives on the balance sheet

Hedge accounting is permitted under both

Both require detailed disclosures in the footnotes Differences

Fair value measurement

  • US GAAP – one measurement model (FAS 157) based on exit price
  • IFRS – various standards use slightly varying wording to define fair value – transaction price at inception date is generally considered fair value

Use of fair value option

  • US GAAP – financial instruments can be measured at fair value with changes in income
  • IFRS – financial instruments can be measured at fair value with changes in income, when certain criteria (more restrictive) are met


Day one profits

  • US GAAP – can recognize day one gains on financial instruments even when all inputs to the measurement model are not observable
  • IFRS – only recognized when all inputs are observable

Debt vs. equity classification

  • US GAAP – certain instruments with characteristics of both debt and equity must be classified as liabilities
  • IFRS – classification focuses on the contractual obligation

Compound (hybrid) financial instruments

  • US GAAP – not bifurcated into debt and equity components, but may be bifurcated into debt and derivative components
  • IFRS – required to be split into a debt and equity component, and if applicable a derivative component

Hedge effectiveness – short cut method

  • US GAAP – permitted
  • IFRS – not permitted

Hedging a component of a risk in a financial instrument

  • US GAAP – risk components that may be hedged are specifically defined – no additional flexibility
  • IFRS – allows entities to hedge components of risk that give rise to changes in fair value

Impairment recognition – available for sale debt instrument

  • US GAAP – may have an impairment due solely to a change in interest rate if the entity does not have the positive ability and intent to hold the asset
  • IFRS – generally only evidence of a credit default results in impairment of an AFS debt instrument


In 2007, the IASB exposed a discussion paper to propose one measurement model for fair value whenever fair value is required. This paper was consistent with the concepts in FAS 157. Both boards appear to be moving towards ultimately measuring all financial instruments at fair value with changes in fair value reported through income.

Thursday, February 18, 2010

The Impact of IFRS for EPM Reporting – Part 5

In Part 5, I want to provide more detail on the similarities and differences regarding Assets: intangible assets, long-term assets, impairment of assets and Leases. The details below were from a presentation during a company sponsored educational seminar about IFRS.

Intangible Assets


Same definition: nonmonetary assets without physical substance

Recognition criteria require that there be probable future economic benefits and costs that can be reliably measured

Start up costs are never capitalized as intangible assets

Goodwill only recognized in business combinations

Internal costs related to the research phase of R&D are expensed

Amortize over the useful life

Goodwill never amortized


Development costs

  • US GAAP – expensed, some software developed for internal use can be capitalized (SOP 98-1)
  • IFRS – can be capitalized when technical and economic feasibility can be demonstrated. No separate guidance addressing computer software


  • US GAAP – not permitted
  • IFRS – permitted, but reference to an active market required, therefore, rare

Property, plant and equipment


Costs to be capitalized are similar

Both require a provision for asset retirement costs when there is a legal obligation, although IFRS requires provision in certain other circumstances as well

Depreciate on a systematic basis

Assets held for sale are measured at lower of carrying amount or fair value less costs to sell



  • US GAAP – not permitted
  • IFRS – may be applied to an entire class of assets to fair value

Capitalization of borrowing costs

  • US GAAP – generally, capitalize. Can include certain equity method investments
  • IFRS – policy choice: capitalize or expense, but must be consistent to all. Equity method investments are not qualifying assets. (NOTE: choice will be eliminated in 2009, when the costs must be capitalized)

Investment property

  • US GAAP – not separately defined, so accounted for as held for use or held for sale
  • IFRS – defined as an asset held to earn rent or for capital appreciation. May be accounted for at cost or at fair value

Impairment of Assets


Similarly defined impairment indicators

Both require goodwill and intangibles with indefinite lives to be reviewed annually for impairment

Despite similarity in overall objectives, differences exist in the way in which impairment is reviewed, recognized and measured


Review for impairment indicators – long-term assets

  • US GAAP – whenever events or changes in circumstances indicate
  • IFRS – assessed at each reporting date

Method of determining impairment

  • US GAAP – 2 step approach – determine recoverability, then loss
  • IFRS – one step approach – calculate loss if indicators exist

Impairment loss calculation

  • US GAAP – Amount carrying amount exceeds fair value (FAS 157)
  • IFRS – Amount carrying amount exceeds its recoverable amount


  • US GAAP – recoverability test first at the reporting unit level, then calculate loss
  • IFRS – impairment test at the cash generating unit level

Reversal of loss

  • US GAAP – prohibited
  • IFRS – prohibited for goodwill. Other long-term assets reviewed annually for reversal indicators



Party that bears substantially all of the risks/rewards of ownership recognizes a lease asset and corresponding obligation (capital lease)

  • US GAAP has bright line tests, IFRS doesn’t – but generally follows the US GAAP test

Operating leases expense recognized straight line over lease term

Lessor accounting essentially the same


Lease of land and building

  • US GAAP – if fair value of land is greater than 25% of the total fair value – consider the land and building components separately
  • IFRS – land and building elements considered separately – no 25% test

Recognition of a gain/loss on a sale and leaseback

  • US GAAP – gain or loss is generally deferred and amortized over the lease term
  • IFRS – For an operating lease, the gain/loss is recognized immediately. For a capital lease, the gain/loss is deferred and amortized over the lease term
  • IFRS does not have a leveraged lease classification

Monday, February 15, 2010

Oracle BI User Groups Combine Forces

It was announced today that the User Group for Oracle Business Intelligence (UGOBI) is combining efforts with the IOUG and ODTUG to provide a one-stop shop for all user group information related to Oracle BI.

UGOBI posted the following news item on their website:

Read the Welcome Letter to UGOBI Members from the Presidents of the IOUG & ODTUG:

XOLAP - Virtual cubes against a Data Warehouse Part 2

As mentioned in my previous blog, "XOLAP - Virtual Cubes Against a Data Warehouse Part 1", I'll address the following in this installment:

  • Completing the Time Hierarchy
  • Developing the rest of the standard dimensions
  • Developing a measures dimension
  • Creating the cube schema
  • Deploying the cube
  • Querying the data
  • Showing real time data updates with XOLAP

When we previously left off we had just finished creating new meta data elements within DimTime. The representation of the "Total Time" hierarchy is exhibited below. Create this hierarchy leveraging the steps used to create the "Total Sales Territory" in Part 1.

Leveraging the hierarchy depicted below, create the "Total Currency" hierarchy

Leveraging the hierarchy depicted below, create the "Measures" hierarchy

Leveraging the hierarchy depicted below, create the "Total Customer" hierarchy

To create the "Total Product" hierarchy, a meta data element, "EnglishProductSubcategoryName" needs to be copied from the DimProductSubCategory table to the DimProduct table.

This can be simply accomplished by right clicking on "EnglishProductSubcategoryName" element within the Metadata Navigator window within Essbase Studio and selecting "Copy".

Pasting this element is as equally simple, highlight the DimProduct table, right click and select Paste.

You are now ready to create the "Total Product" hierarchy as shown below.

Create the "Total Promotion" hierarchy as shown below

You are now ready to create the cube schema.

Access the "Cube Schema Wizard" hotlink from the Essbase Studio "Welcome Page."

The "Cube Schema Wizard" should be displayed as shown below.

Within the "Choose Measures and Hierarchies" dialog window specify a name for the cube schema and then select each of the newly created hierarchies from the left panel and move them to the appropriate panel on the right hand side.

After clicking "Next", the "Cube Schema Options" dialog box should be displayed.

Toggle on the "Create Essbase Model" radial button and provide a name for the model.

In this case I have named my model "XOLAP Adventure WorksModel."

After clicking "Next" the Cube Schema Model should be displayed as depicted below

You are now ready to deploy the cube to Essbase.

Access the "Cube Deployment Wizard" hotlink from the Essbase Studio "Welcome Page."

The "Essbase Server Information" dialog box should now be displayed.

Leverage the previously created withing Part 1 of this blog and select this connection name within the Essbase Server Connection drop down box.

Now specify and Essbase Application Name and Database name. These names are restricted to 8 characters and can not be currently used within your Essbase environment.

Ensure that only the "Build Outline" radial box is the only box toggled on at this point and then select the "Model Properties" button from the lower left of the dialog box.

The "View, edit, and save properties" should now be displayed.

With the "XOLAP Adventure WorksModel" highlighted, select the "General" tab and activate the "XOLAP Model" radial button.

With "Total Time" highlighted, select the "Info" tab and set the dimension type to "Time" and dimension storage to "Dense"

With "Measures" highlighted within the "Info" tab, ensures that measures is set to a dimension type to "Accounts" and dimension storage to "Dense"

Select "Close" and then "Finish"

The following image will be displayed while the cube is being deployed

When successfully completed, a notification of successful deployment will be presented.

Navigate to Oracle Essbase Administration Services and review the application and database just created. Your application should look much the image below:

Remember at this point, the outline is the only thing that has been built, no data has been loaded to the application, nor has an calculation been executed.

Leveraging the Hyperion Add-in, connect to the XOLAP database that you have just created, notice data is present and aggregated. Format your query as exhibited, focusing on the following members:

  • Customer:Yang, Jon V
  • Measure: Unit Price
  • Sales Territory: Australia
  • Time: Total Time
  • Promotion: No Discount
  • Currency: Australian Dollar
  • Measures: Fenders, Helmets, Jerseys, Mountain Bikes, Tires and Tubes, Touring Bikes

Notice the Unit Price for the data intersection of Mountain Bikes (3399.99)

Now access the underlying relational database, I have leveraged Microsoft SQL Server Management Studio in this instance.

Open the table FactInternetSales and go to row 88, it should agree with the information depicted in the exhibit below:

Update the Unit Cost for row 88 from 3399.9900 to 999999.99 and commit this value to the database

Execute a retrieve against the spreadsheet set up just moments ago.

Notice the data has changed in the underlying relational repository and also through your ad hoc query tool.

While some restrictions do exist in structuring a XOLAP model, which were mentioned in Part 1 of this blog, the robustness of delivering an application of this nature is pretty self evident.
When asked previously by customers, "Can I do ad hoc, real time analysis against transactional data in my data warehouse?" I often struggled to provide an answer that really meet each of those criteria. Now with XOLAP a definitive approach can certainly be presented to the customer.

XOLAP - Virtual cubes against a Data Warehouse Part 1

Can it be true? Real time ad hoc analysis against a Data Warehouse using an Essbase cube that contains no data?

Well with XOLAP, these capabilities a being brought together. I have created a brief tutorial within this article to demonstrate to overall concept relating to XOLAP.

In Part 1 of this article, I'll discuss:

  • Setup that needs to occur to emulate sample
  • Background into XOLAP
  • Current restrictions relating to XOLAP
  • Creating Data Sources in Essbase Studio
  • Defining a MiniSchema
  • Defining Standard Hierarchies

Due to the number of screen shots and the size of this blog article, I have set the image properties to small. While the screens may be difficult to decipher within the article, each can be clicked on to be rendered in a much larger resolution for viewing.

Setup The Needs to Occur to Emulate Sample

The example delivered in this article involves leveraging AdventureWorksBI.msi on SQL Server 2005 as the Data Warehouse. This database can be downloaded from . The installer for this download requires you to manually attach the database after installation.

Within the dbo.DimCustomer table add a new column called "ProperName" with a property of "nvarchar(100)."

Update the ProperName column within dbo.DimCustomer using a SQL statement similar to the following :

Update dbo.DimCustomer
Set ProperName = LastName + ', ' + FirstName + ' ' + MiddleName

Within the dbo.DimTime table add 3 new columns called "Month", "Day" and "Year" with each having a property of "nchar(10)."

Update the newly added columns within dbo.DimCustomer using a SQL statement similar to the following :

Update dbo.DimTime
Set Month = DatePart(Month,FullDateAlternateKey)
Set Day = DatePart(Day,FullDateAlternateKey)
Set Year = DatePart(Year,FullDateAlternateKey)

A little background into XOLAP

XOLAP (extended online analytic processing) is a variation on the role of OLAP in business intelligence. Specifically, XOLAP is an Essbase multidimensional database that stores only the outline metadata and retrieves data from a relational database at query time. XOLAP thus integrates a source relational database with an Essbase database, leveraging the scalability of the relational database with the more sophisticated analytic capabilities of a multidimensional database.

OLAP and XOLAP store the metadata outline and the underlying data in different locations:

  • In OLAP, the metadata and the underlying data are located in the Essbase database.
  • In XOLAP, the metadata is located in the Essbase database and the underlying data remains in your source relational database.
  • Restrictions For XOLAP

    • No editing of an XOLAP cube is allowed. To modify an outline, you must create a new outline in Essbase Studio. XOLAP operations will not automatically incorporate changes in the structures and the contents of the dimension tables after an outline is created.
    • When derived text measures are used in cube schemas to build an Essbase model, XOLAP is not available for the model.
    • XOLAP can be used only with aggregate storage. The database is automatically duplicate-member enabled.
    • Alternate hierarchies and attribute dimensions are supported; however, attribute hierarchies are not supported.
    • XOLAP supports dimensions that do not have a corresponding schema-mapping in the catalog; however, in such dimensions, only one member can be a stored member.
    • A model that is designated as XOLAP-enabled must be deployed to a new Essbase database because incremental builds for XOLAP are not supported.

    Creating a Data Source

    • From the "Essbase Studio - Getting Started" page within Essbase Studio, select the hot link "Data Source Wizard", the "Define Connection" portion of the Connection Wizard is displayed.

    • Enter a Connection Name.
    • Enter an optional Description.
    • Select the appropriate Data Source Type. For example, if you are creating a connection to a Microsoft SQL Server data source, select Microsoft SQL Server from the drop-down list.
    • In Server Name, enter the name of server where the database resides.
    • To use a port number other than the default, clear the Default check box next to Port and enter the correct port number in the text box.If you are using the default port number, you can skip this step.
    • Enter the User Name and Password for this database.
    • In Database Name, select "AdventureWorksDB"
    • Click Test Connection. If the information you entered in the wizard is correct, a message confirms a successful connection.If you entered incorrect information in the wizard, a message is displayed explaining that invalid credentials have been provided. Correct the errors and retest until the connection is successful.
    • Clicking Next takes you to the Select Tables page of the wizard

    Select the following tables for the Select Tables dialog box and then select "Next":

    • dbo.DimCurrency
    • dbo.DimCustomer
    • dbo.DimProduct
    • dbo.DimProductSubCategory
    • dbo.DimPromotion
    • dbo.DimSalesTerritory
    • dbo.DimTime
    • dbo.FactInternetSales

    The Select MiniSchema dialog is now presented.

    • Select the radial button for "Create a new schema diagram"
    • Enter a name for this schema, In this instance I used "XOLAP Adventure Works DWSchema"
    • Leave "Skip Schema" and "Use Introspection to Detect Hierarchies" unchecked
    • Select Next

    • The "Populate Schema" dilog box is displayed. Each of the tables that were previosly selected should be displayed on the right hand panel.
    • Select "Next"

    • The "Create Metadata Element" dialog box should now be displayed.
    • Toggle on the radial button next to the "XOLAP Adventure Works DW", this should toggle on all members displayed in this dialog window
    • Select "Next"

    • The "XOLAP Adventure Works DWSchema" should now be displayed.

    • Navigate back to the "Welcome" screen
    • Highlight the "DimSalesTerritory" within the Metadata Navigator. Once highlighted, select the "Hierarchies" hotlink from the "Welcome" screen.

    • Above is a representation of the contents from the DimSalesTerritory table. This is provided to deliver an understanding of how the hierarchy will be developed from the underlying relational table

    • The hierarchy wizard should now be displayed at this point.
    • Specify the "Dimension Head Name", in this case "Total Sales Territory" and then drag the "SalesTerritoryGroup" data element from the Metadata Navigator into the Data grid.
    • The grid should should now contain the "SalesTerritoryGroup" element. Highlight this element and click "Add" and select "Child"

    • Within the "Select Entity" dialog box, select "SalesTerritoryCountry" and then "OK"

    • Leveraging the same approach for you used for adding "SalesTerritoryCountry", now add "SalesTerritoryRegion" to deliver a hierarchy as depicted above.

    • By right clicking on the "Total Sales Territory" hierarchy data element in the MetaData Navigator panel and selecting "Preview Hierarchy" the above sampling of data should be displayed

    • Next, we will create the Time hierarchy for the model. This step will be slightly different, we will leverage the newly create columns in SQL server to build out a time hierarchy.
    • With the DimTime metadata element highlighted in the MetaData Navigator, right click and select "New" and "Dimension Element"

    • The "Edit Properties" dialog window for the new data element should appear.
    • Enter "Month Day Year" in the name window
    • Paste the following syntax into the Caption Binding window:
    • 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Month' ) "/" 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Day' ) "/" 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Year' )

    • Repeat the following steps for "Month Year" and place the following syntax in the caption binding window for "Month Year":
    • trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Month' ) "/" 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Year' )

    • Repeat the following steps for "Year" and place the following syntax in the caption binding window for "Year":
    • 'trim'( connection : \'XOLAP Adventure Works DW'::'AdventureWorksDW.dbo.DimTime'.'Year' )

    In my next blog, which should be published shortly I'll address the following:

    • Completing the Time Hierarchy
    • Developing the rest of the standard dimensions
    • Developing a measures dimension
    • Creating the cube schema
    • Deploying the cube
    • Querying the data
    • Showing real time data updates

    Friday, February 12, 2010

    Essbase Analytics: Where to calculate metrics?

    One of the most often missed enterprise information improvements with Essbase is the calculation of organizational metrics. All Essbase frontends have the ability to perform calculations on the fly, but often this is overused.

    The Problem (in the beginning)
    Essbase is often implemented to standardize an organization's financial data. In addition to extending analysts' capacity to review financial data beyond the limits of Excel and traditional reporting tools, having standardized metrics ensures all decision makers are comparing similar values. You've probably heard the cliche 'Single Version of the Truth'... this centralized storage of financial information alongside centralized metrics is the embodiment a single version of the truth.

    The Deployment
    Great... so once an organization realizes the need to standardize and centralize, step one of any successful implementation is to define their requirements. In the simplest terms, this includes the data to be housed in the application, as well as the definitions for the metrics to be applied to that data. Utilizing this blueprint results in an implementation that solves the key goals: having everyone utilizing the same data and the same definitions.

    There is always something that isn't captured. This isn't a problem, this is a reality. Often, a metric isn't included either due to time constraints, foresight constraints, or conflicting priorities.

    The front end to the rescue!!!
    All modern Essbase front ends have the ability to do calculations. Some are limited to rudimentary calculations, others have robust calculation languages. Regardless, the inevitable suggestion of "Lets just accommodate that in the front end" is brought up. Which is great, but...

    Back to square one
    Back during the evaluation phase, one of the key decision points to implement an Essbase application was to centralize and standardize all business logic. As soon as the decision is made to move away from centralized business logic, the long term value of the implementation begins to erode.

    What to do
    I'd be a fool if I heavy-handed recommended that any and all business logic is only ever developed in the backend. This is going squarely against one of the key benefits of Essbase- power to the analysts' to perform their own operations, without being at the mercy of someone else to implement logic. But, that's where the distinction ends. One of the key rules to any successful Essbase, or Performance Management, or BI, implementation is that all logic that will be shared amongst multiple users must be centralized. A key principle that an organization must adopt is a requirement that any business logic that is to be distributed to the masses must be implemented at the point at which it is sourced to the masses. This doesn't necessarily imply that every piece of business logic is sourced solely in Essbase; it may be that the logic is created in a system that feeds Essbase. In addition, the realities of a modern complex organization must be recognized, and, situations where the full audience for a piece of business logic are served by multiple systems, there may be justification for business logic configuration in multiple systems.

    Thursday, February 11, 2010

    Hyperion Planning Data Forms

    Hyperion Planning Data Forms are the main input mechanism to gather end user budgets and forecasts, and oftentimes, are the only component an end user interacts with in Planning. Because of this, there is a lot of functionality built into the forms that help them both mimic the use of a spreadsheet and enhance basic data entry and usability. Following are some of the features you will find when using data input forms.

    Entering Data

    Like a spreadsheet, you can copy and paste data into a cell, row, or column. If you enter data to a summary time period (quarter or year), values will automatically spread back into the months accordingly. If no months have data, values will be spread equally. If data does exist in the months, the summary time period spread will happen according to the previous spread pattern. You also can use the lock cells feature here to lock the spread from occurring in certain months. The result is that the spread would occur evenly to only the unlocked months.

    Ad-Hoc Calculations
    Data forms allow you to perform cell “what-if” scenarios to determine the impact of a number change. In a cell, you can add +, +-, *, /, or % along with a number and Planning will return an updated value. For example, if a cell contains 1,000 and you input “+500”, the cell would update to show 1,500. Or, you could enter “%40” and the result would be 400.

    Supporting Detail
    Supporting detail allows users to enter additional lines of detail below the lowest level of account contained in the application. For example, if you had a general Travel Expenses account, you could add additional rows of detail to itemize out airfare, meals, ground transportation, and hotel. Supporting detail can be added to a cell, cells, or an entire row. The supporting detail feature allows you to build hierarchies into the rows and contains row logic to add, subtract, multiply, divide, or ignore lines when rolling them up.

    Adjust, Grid Spread, and Mass Allocation
    The Adjust feature lets you adjust a cell or cells by a value or percent.

    Grid Spread allows you to adjust a parent number by either a value or percent and then spread that value back to its descendants on the web form, either proportionally based on existing values, evenly, or filling all children with the parent value. The user need to have appropriate access to the target cells in order to write back to them. The administrator can also create other spreading options, if desired.

    Mass Allocation is similar to Grid Spread, with the following differences:

    • Data is allocated to all descendants of a member, even to those intersections not displayed on the web form.
    • Access to the target cells is not required.
    • Mass allocated values cannot be undone.
    Exporting to a Spreadsheet
    There are two options for moving a data form to Excel. There’s the spreadsheet export that copies the POV, page, row, and column members into an Excel spreadsheet, along with the data grid. The result is a file that is disconnected from Planning, although it could be used as a lock and send sheet by connecting through either the Essbase Add-In or Smart View. The second choice is the Open in Smart View option. This option opens the data form in Excel where you can essentially perform all the same functions as in Planning web, all while remaining connected to the system.

    Notes and Annotations
    Cell Text lets you add a textual note to a cell, similar to the Comments feature in Excel. This allows you to provide explanations for cell amounts and/or variances.

    Account Annotations let you add descriptive information or a URL web link to an entire account row.

    Instructions can be added to an entire data form to provide direction and information to an end user.

    Other Functionality
    With version 11, you now have the ability to add a document to a cell in a data form via a hyperlink or a Workspace object. You could either have a URL link to an Excel spreadsheet or Word document or provide a link to a Financial Reporting Studio report.

    Another new feature in version 11 is the ability to drill from a data form cell down to FDM loaded source data. This allows a user to view data at a more granular level than is available in the Planning application.

    Finally, calculations can be attached to and launched from a data form. For example, you could have a calculation that aggregates values up the hierarchy. A nice feature here is that the calculations can be set to run automatically, either upon opening a form or saving it.

    Wednesday, February 10, 2010

    The Impact of IFRS for EPM Reporting – Part 4

    In Part 4, I want to provide more detail on the similarities and differences regarding: Business Combinations and Inventory. The details below were from a presentation I viewed during a company sponsored educational seminar about IFRS.

    Business Combinations


    • All accounted for using the purchase, or acquisition method
    • Recognized at fair value, but currently differing definitions of fair value
    • Acquisition date is the date that the acquirer obtains control
    • Contingent consideration recognized at fair value at acquisition date, subsequent changes in earnings
    • Negative goodwill recognized immediately as income
    • Acquired in-process R&D recognized at acquisition date fair value
    • Restructuring liabilities only recognized if criteria have been met, and is recognized at the acquiree level at the acquisition date
    • Net identifiable assets of acquiree are recognized at their full fair value
    • All transaction costs expensed as incurred


    Acquisition of less than 100% of acquiree

    • US GAAP – noncontrolling interest is measured at fair value, including goodwill
    • IFRS – Choice of measuring noncontrolling interest at either full fair value including goodwill or at its proportionate share of the fair value, exclusive of goodwill



    Same principle that the primary basis of accounting for inventory is at cost

    Both define inventory as assets held for sale in the ordinary course of business, in the process of production for such sale, or to be consumed in the production of goods or services

    Permitted techniques for cost measurement, such as standard cost method or retail margin method are similar

    Cost of inventory includes all direct expenditures to ready inventory for sale, including allocable overhead


    Costing methods

    • US GAAP – LIFO permitted
    • IFRS – LIFO is prohibited


    • US GAAP – carried at lower of cost or market
    • IFRS – carried at lower of cost or net realizable value

    Reversal of inventory writedowns

    • US GAAP – cannot be reversed
    • IFRS – can be reversed up to the amount of the original impairment loss

    Saturday, February 6, 2010

    Offline Budgeting and Planning

    Have you ever been on a plane and said to yourself, “there aren’t enough Jack Daniel's miniatures aboard for me to have to sit through Santa Clause 3 again, but if I could just enter my Q4 forecast numbers, I’d be all set for the remainder of this ten hour flight.” Or, maybe you were driving down the Pacific Coast Highway thinking, “if I could only finish annotating my annual budget right now, my trip to the beach would be so much more enjoyable.” [Disclaimer here that I would never condone driving and budgeting or driving and doing anything else besides just driving, for that matter…] Or, maybe you’ve stayed at that Motel 17 out in nowhere with a download speed of 1 kb (+/- .5 kb) per hour and lost your budget updates no less than 20 times.

    Ok, maybe not… But certainly, there is value in being able to access a budgeting system while disconnected from the system. Whether you’re on a plane or staying in a remote location, there’s inevitably a time when a deadline is looming and you just have to finish your work. Hyperion Planning provides a solution for this dilemma through the offline capabilities of the Smart View Excel add-in tool.

    Smart View is a tool that allows you to pull Hyperion Planning data web forms into Excel. Once a form is in Excel, you can essentially perform all the same functions that you can in Planning web. With Smart View, you can input data and save it to Essbase, use the adjust data feature, enter supporting detail and cell text, and run business rules and calc scripts. You even have the same look and feel as a web form with expandable parent members, page drop-down boxes, green read-only cells, and yellow updated cells.

    The process for taking a Planning data form offline in version 11 is fairly simple:

    1. The application has to first be enabled for offline usage. Set this by going to Administration -> Manage Properties -> Application Properties and setting ENABLE_FOR_OFFLINE to true. This is the default setting.
    2. Next, you need to enable individual data forms for offline usage. Edit a form, go to the Other Options tab, and click the Enable Offline Usage box.
    3. Open the data form(s) in Smart View and click Take Offline. You can choose to take a single form, multiple forms, or an entire form folder and its contents offline.
    4. Select the page dimension members to store offline. You can either choose to take all page members or just a subset.
    5. Provide a connection name and click Finish.
    At this point, the necessary application components are copied from the server to your local machine. Basically, a smaller version of the application is created on your system, including necessary outlines, calc scripts, dimension members, data, substitution variables, and web forms. The download can be quite large depending on the number of items you choose to include. To minimize the download time and system footprint, you will want to limit the web forms and page dimensions to only those that you will truly need.

    Once all the components complete their download, you can work in your subset of the application to your heart’s content, all while being disconnected from the network. You can essentially perform the same operations as if you were still connected, with the following exceptions:
    • When you save information, you are saving it to your local machine.
    • When you log into the application in Smart View, you no longer log in through the Common Provider. You now go to Independent Provider Connections and open your offline connection there (named in step 5 above).
    • Currency conversion is not supported for offline usage.
    This offline connection can be accessed as many times as you’d like, continually opening, saving, and closing it until it’s synchronized back to the server.

    Take the following steps to push any updates in your offline connection back up to the network server:
    1. Open the offline connection.
    2. Click Sync Back To Server.
    3. Log into the application on the server.
    4. Select which forms, folders, and page members to sync back.
    After the upload is complete, I generally delete the offline Independent Provider Connection to remove the application files from my machine.

    That's pretty much it in a nutshell. So, the next time you're in the middle of the Gobi Desert or standing on the edge of a crater rim on Mount Kilimanjaro, you'll have the comfort of knowing you can still get your work done. If, on the other hand, your motto is "when I'm away from work, I'm away from work", then just forget everything you just read.

    Friday, February 5, 2010

    Hierarchical Metadata Relationships in Essbase – Pro’s and Con’s

    A question that often has to be considered during design of an Essbase database is to establish a relationship between metadata points that could possibility be two separate dimensions. For instance, combining Entity and Project into a single entity dimension with Projects rolling into the entities that are responsible for managing those Projects. The first litmus test that needs to be passed is whether your project has a direct correlation to a specific entity. Essentially, does a one for one relationship exist? Can the Project only be managed by one Entity?

    In instances where a Project may roll to many different Entities the debate as to whether combining the dimensions is often eliminated since the size of the dimension could eventually become very large due the numbers of combinations of metadata points that would need to be supported. In earlier versions, member uniqueness would be a constraining factor, that at that time, could only be addressed through member concatenation. Duplicate member names capabilities have since eliminated that constraint.

    If the project is only managed by a single entity then Pro's and Con's need to be evaluated.

    The Single Dimension Approach

    • Con's relating to a Single Entity dimension:
      • What-if Modeling would need to be a function of an administrator since they would need to stage the alternate roll-up based upon end-user feedback
    • Pro's with a Single Entity dimension:
      • Relationships between contracts and divisions could be clearly understood by viewing the hierarchical structure
      • No need to copy, clear, input or move data by end-users when What-If modeling is being performed since organizational changes could be modeled via alternate hierarchies, leveraging shared members.
      • Sparseness of the database is reduced allowing for better performance
      • One less dimension for users to have to navigate, reducing the possibility of user getting lost in their data
      • One less dimension is necessary to aggregate enhancing application performance and promoting a more dynamic application
      • Easier administration of security

    The Separate Dimension Approach

    • Pro's relating to the multiple dimension approach:
      • End-users would need no administrator involvement in modeling What-if analysis since they would leverage separate slices of the database through scenario and version combinations to model.
    • Con's with the multiple dimension approach:
      • Relationships between contracts and divisions cannot be inferred by viewing the hierarchical structure
      • Data would need to be copied, cleared, input or moved by end-users to stage the what-if.
      • Sparseness of the database increases which could lead to diminished performance
      • Another dimension for users to have to navigate, increasing the possibility of user getting lost in their data
      • One more dimension necessary to aggregate reducing application performance
      • More complex administration of security. Flat dimensions don't readily allow the use of relationship functions when administering security.

    So what is the right approach? Either approach is acceptable as long as the Pro's and Con's of each approach is understood.

    MUD and Aliases

    A common issue encountered when using MUD is the appearance of columns in the presentation layer with a "#1" appended to the end of the column name. The most likely of reasons this is occurring stems from the existence of aliases in the presentation layer. For example, say there was a column named AccountName at a previous time in the repository. When it was renamed to something new, AccountDescription, an alias of AccountName was retained in AccountDescription, unless it was manually removed. So when a new column is added to the presentation layer in the checked out repository named AccountName and merged into the master repository, the merge process sees that AccountName already exists and appends the #1 to it in order to differentiate.

    Remedying this issue is fairly straightforward and simple. After merging the checked out repository to the master repository but before publishing, check the presentation layer in the master repository for any #1 columns and either remove them, remove the alias from the other column, and/or rename them if the alias is needed. Remember to do this right after the merge but before publishing to the network so that you avoid getting #1 columns in the master repository. Best practice is to not keep aliases around but sometimes one may have good reason to retain them or they may slip through unnoticed until a situation like #1 occurs.

    Tips for using Smartview and Excel Add-in for Essbase

    Smartview for Office and the traditional Essbase Excel Add-in offer analysts a powerful adhoc capability to query an Essbase database. Both tools allow for real time drilldowns, pivots, and custom reporting directly from within Excel. In addition, users can utilize all of Excel's built in calculation commands against retrieved Essbase data to perform unique analysis and problem solving. However, getting started with either adhoc tool can be daunting. Here I'll list several options every new user of the Excel add-in should be aware of to help them navigate their Essbase databases.

    Color coding of Parent members
    One common question is simply "Where can I drill down?" Utilizing styles, it is possible to highlight all members that can be drilled down upon a differently than those which can not be expanded (i.e. the Level Zero members).

    Smartview for Office:

    1. Connect to an Essbase data source in adhoc mode
    2. Select "Options" on the Hyperion menu
    3. On the "Cell Styles" tab, expand Analytic Services -> Member Cells
    4. Check "Parent Cells"
    5. Right-click "Parent Cells" and select the manner in which you'd like to format them (I prefer to adjust the font, making Parent cells Bold and Black).
    Excel Add-in for Essbase
    1. Connect to an Essbase database
    2. Select "Options" on the Essbase menu
    3. On the "Display" tab, ensure "Use Styles" is checked (under "Cells")
    4. On the "Styles" tab, check "Parent" under "Members"
    5. Click the Format button. Set formatting to your preference (I make Parent members Black and Bold)

    Identification of Read-only vs. Writable data cells
    Users utilizing Excel to write to their database can have cells they have access to write to highlighed in a different color than read-only cells.
    Smartview for Office:
    1. Connect to an Essbase data source in adhoc mode
    2. Select "Options" on the Hyperion menu
    3. On the "Cell Styles" tab, expand Analytic Services -> Data Cells
    4. Check "Writable"
    5. Right-click "Writable" and select the manner in which you'd like to format them (I prefer to adjust the font, making Writable cells Bold and Green).
    6. You may also want to highlight read-only cells differently. I prefer making these values Red.
    Excel Add-in for Essbase
    1. Connect to an Essbase database
    2. Select "Options" on the Essbase menu
    3. On the "Display" tab, ensure "Use Styles" is checked (under "Cells")
    4. On the "Styles" tab, select "Read/Write" under "Data Cells"
    5. Click the Format button. Set formatting to your preference (I make writable values Green and Bold)
    6. You may also want to make Read-only values a special color. I prefer Red.

    Retaining Excel formulas during a retrieve
    (This applies to the Excel Add-in only; Smartview for Office retains formulas during a retrieve.)
    One of the greatest powers of both tools is the ability to utilize Excel's built-in calculations against Essbase data. However, by default, all calculations are deleted from the worksheet whenever a blanket retrieve is performed. One option to prevent this is to highlight only the cells necessary for the retrieve (both the data cells and member cells). Alternatively, both tools have an option to preserve formulas during a retrieve.
    1. Connect to an Essbase database
    2. On the "Model" tab, select "Retain on Retrieval" under "Formula Preservation". This will retain your formulas when doing a simple retrieval.
    3. To have Excel adjust your formulas when performing Zooms or Keep/Remove only operations, use the second and third options.

    Some other settings I've found useful when using the older Excel Add-in (some I use all the time, some only occasionally):
    1. On the "Display" tab, select "Adjust Columns". This setting will automatically adjust the width of each column with either an Essbase data point or an Essbase member to the width of the largest item.
    2. On the "Display" tab, change "Indentation" to "Subitems". I find it easier to read an adhoc spreadsheet if a member's component members are indented, as opposed to the default.
    3. On the "Zoom" tab, select "Formulas". This will not show me the exact formula utilize in the Essbase database, but will show me all members that appear in the formula from the same dimension as the member drilled upon.
    4. On the "Mode" tab, select "Update Mode" when doing a number of data write-backs. This will automatically lock all writable data cells when doing a retrieve, so instead of having to perform a lock, then a send, only a send is necessary.
    5. On the "Global" tab, unselect either "Enable Secondary Button" or both "Enable Secondary Button" and "Enable Double-Clicking" if using Excel but not against Essbase. This will give you back full functionality of your right and left mouse buttons.
    Hopefully you've found these tips helpful for getting started with adhoc analysis directly against an Essbase database.