Essbase member name manipulation - Net Transshipments

Transshipments
In some Essbase models there is a need to capture two essentially identical elements in separate dimensions. One common example of this is a transshipment model, where it is necessary to identify both the origination and destination of a shipment.

Two Dimensions
The most straightforward, and often user-friendly, manner to accomplish this type of tracking is to have the same hierarchy in two dimensions, with each member prefixed differently (i.e. the Source location members might be prefixed with "TO:" while the Destination members would be prefixed with "FROM:").

Net Shipments
A simple metric to consider in this type of model is often Net Shipments. In a multidimensional database, to calculate Net Shipments, shipments out of a location needs to be subtracted from shipments in (or vice-versa, depending on your preference). Assuming this is being calculated for Location A, the formula might look like:
Shipments -> TO:A -> Total Sources - Shipments -> From:A -> Total Destinations
(I.E. take all of the shipments that are sent to "A", regardless of where they are from, and subtract all of the shipments from "A" regardless of where they go to).

The Problem
The formula is simple and straightforward for one location, and remains similar for all other locations, but, every time a location is added, renamed, or deleted the calculation must be updated manually.

The Solution
Assuming the hierarchies are carefully constructed to always require "TO:" before the source location and "FROM:" before the destination location, Essbase can programatically determine the corresponding member by removing and replacing the prefix.

Components
Several string manipulation functions are required:

• @NAME or @ALIAS: Used to pass either an Essbase Member Name or the respective member's Alias Name to another function as a string.
• @SUBSTRING: This function will return a portion of a string passed to it.
• @CONCATENATE: Used to join two strings together.
• @MEMBER: Turns a string into a reference to a member name.

For this example, I'll also utilize @CURRMBR. This function returns the current member being calculated from a given dimension.

So, to determine the corresponding destination from a member in the source dimension:

1. Turn the current member in the source dimension into a string: @NAME(@CURRMBR("Source"))
2. Remove the prefix "TO:" from the string: @SUBSTRING(@NAME(@CURRMBR("Source")),3)
3. Prefix the new string with "FROM:": @CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3))
4. Convert the string into a reference to a member: @MEMBER(@CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3)))

Using this formula, you can fix on the portion of a hierarchy in the Source dimension, and have access to each member's corresponding Destination member. The original example of calculating Net Shipments might look like:
Fix(@RELATIVE("Source",0))
"Net Shipments"(
"Shipments"->"Total Destination" - "Shipments"->"Total Source"->@MEMBER(@CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3)));
);
ENDFIX

The Impact of IFRS for EPM Reporting – Part 7

In Part 7, I want to provide more detail on the similarities and differences regarding Foreign Currency Matters. This week I'll keep it short due to other projects I am working on... but again the details below were from a presentation during a company sponsored educational seminar about IFRS.

Foreign Currency Matters

Similarities

• Similar approaches to foreign currency translation, guidance is different, but generally results in the same determination
• Both consider the same economies to be hyperinflationary
• Both required foreign currency transactions to be remeasured into functional currency with amounts resulting from changes in exchange rates being reported in income
• Both require assets and liabilities to be translated at period-end rate, and income statement amounts generally at average rates, with differences in equity

Differences

Translation in hyperinflationary economy

• US GAAP – Local functional currency remeasured as if it was the reporting currency (parent)
• IFRS – local functional currency financial statements are indexed using a general price index and then translated to the reporting currency at the current rate

Consolidation of foreign operations

• US GAAP – step by step method is used
• IFRS – method is not specified. Either the direct or the step by step method is used.

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

Similarities

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

Differences

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

Convergence

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.

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

Similarities

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

Differences

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

Revaluation

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

Property, plant and equipment

Similarities

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

Differences

Revaluation

• 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

Similarities

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

Differences

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

Goodwill

• 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

Leases

Similarities

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

Differences

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

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:
http://ugobi.site-ym.com/news/36688/User-Group-Communities-Merge-Efforts-To-Offer-One-Stop-Solution-For-Oracle-Business-Intelligence.htm

Read the Welcome Letter to UGOBI Members from the Presidents of the IOUG & ODTUG:
http://www.OracleBusinessIntelligence.org

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 http://msftdbprodsamples.codeplex.com/releases/view/4004 . 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