Recently a saw a colleague asking for some information about the HFM table layout and how they could interface with the data in the system. While I recalled there were a few documents on the subject, I could not locate them via Google. While I’ve provided them at the bottom of this blog post, I thought I’d give a 10 second high level explanation of the primary tables in regards to the actual HFM data. (Pre 11.1.2.2)
Generally speaking, when you configured HFM, you provided a database for use by the system. This database will hold the data for all of your applications created in the envirionment. To keep the data separated, each application’s data tables will be prefixed with the application name. (i.e. SampleApp_CSE_11_2011)
Oracle is kind enough to publish a data model breakdown for most of the tables which can be found here:
http://www.oracle.com/technetwork/middleware/bi-foundation/resource-library-090986.html
Interestingly, they do not cover all of the tables as they appear to skip over the data tables. When it comes to the data, there are 3 primary tables that hold the financial information : DCE, DCN, and DCT tables. The DCE tables store <Entity Currency> and <Parent Currency> values and the related adjustments. The DCN tables store data for the remaining members of the value dimension. The DCT tables store the journal transactions – when the journals are posted, the data values flow to the DCE and/or DCN tables.
When viewing your database, you will see a series of tables that follow a relatively obvious naming convention:
<App_Name>_DCE_<PeriodNum>_<Year>
Elevator Trivia Note: The fact that the Period and Year and part of the naming convention is a key reason as to why you specify an ending year (and number of periods) during the App build process!
Looking at the structure of these tables, you’ll see the following fields used consistently:
lEntity
lParent
lValue
lAccount
lICP
lCustom1
lCustom2
lCustom3
lCustom4
With the exception of lValue, these are pretty straightforward and act as foreign keys back to the respective tables such as <AppName>_Entity_Item.
lValue – Is slightly different as it takes a little work to determine what this is. For each currency in your system, you will have Base Currency “Entity Currency”, Adjustments, and Total. This field is a combination of that AND the currency. The following formula will help you translate:
<AppName>_CURRENCIES.ItemID * 3 + 15 = “Currency Total”
<AppName>_CURRENCIES.ItemID * 3 + 16 = “Currency Adj”
<AppName>_CURRENCIES.ItemID * 3 + 17 = “Currency”
In addition to those fields, the tables all contain one or more Timestamp field. Minor conversion is needed on the Timestamp field to make it human readable. For instance in SQL Server you would return it as:
cast(TimeStamp–2 as smalldatetime)
After these columns, each of the 3 table sets have slightly different layouts; however, one additional pattern you should see is that there will be incrementing columns that correlate to the number of periods in your application. One of the columns will hold your actual data while the other one will hold a timestamp or some sort of indicator about the data (i.e. Trans Type)
Armed with this knowledge, one thing you can do is search all of your data tables to find orphaned information. While Oracle gives you a way to clear it out, it doesn’t give you a good way to see what you are clearing. With a basic query, we can not only find out if we have orphaned records, but what the data is and when it was created/last used/updated!
The following Microsoft SQL Query will dynamically go through ALL tables that reference lEntity and then determine undefined values.
NOTE: You should theoretically search for Account, ICP, and Custom orphans as well, but this is a start!
_________________________________
— Get List of Tables that refer to Entity ID and then search them all looking for undefined values
— NOTE: You would probably do the same for lValue, lAccount, lICP, lCustom1 – lCustom4,
— NOTE: lParent would correspond to entity table like lEntity
— NOTE: lSrc and lDes should also be checked against actual table. i.e. lSrcAccount checked against Accounts
–Initialize some working memory
Declare @tblSearchTable varchar(50)
Declare @tblSrcTable varchar(50)
Declare @tblSearchField varchar(50)
Declare @sql nvarchar(4000)
— Setup defaults
set @tblSearchField =‘lEntity’
set @tblSrcTable =‘<APPNAME>_entity_item’
–Setup Cursor
Declare tmp_cursor CURSOR
For Select distinct name from sys.tables where object_id in ( select object_id from sys.columns where name= @tblSearchField) order by name asc
Open tmp_cursor
–Retrieve Table name to process
Fetchnextfrom tmp_cursor
INTO @tblSearchTable
WHILE@@FETCH_STATUS= 0
BEGIN
SET @sql=‘select ”’+ @tblSearchTable +”’ as SrcTable, * from [‘+ @tblSearchTable +‘] where ‘+ @tblSearchField +‘ not in (select itemid from [‘+ @tblSrcTable +‘])’
EXECsp_executesql @sql
–Retrieve Table name to process
Fetchnextfrom tmp_cursor
INTO @tblSearchTable
END
CLOSE tmp_cursor
DEALLOCATE tmp_cursor
__________________________________________________
Additional Reading
Hyperion Subcube Architecture Document