Database Reference
In-Depth Information
7.6.2.4 Loading “Balance Data” or “Flow Data”
As we have seen, ASo cubes are highly
sensitive to the number of input-level data cells in the database. Therefore, it makes
sense to minimize this number, assuming no data is lost and no excessive penalties are
incurred in recreating the lost data. many cubes contain “Balance” type data, which has
a balance at a point-in-time rather than over a period of time (referred to as transactional
or “Flow” data). Accounting balance sheet data is the primary example. many other
cubes contain balance data in the form of readings, such as temperature or odometer, etc.
Frequently, the balances or readings do not change in every time period in your cube. not
every balance sheet account reflects activity every day or month. Certainly, there are some
of them that do, but you will find that the overwhelming majority do not. This is also true of
many types of “reading” data. If you store the actual balance or reading, you will be adding a
data cell every time period whether the data has changed or not. Instead, create a “Beginning
of year” (Boy) value as a new stored member in your time hierarchy. Load the changes (if
any) into the monthly members. now balance sheet balances will be reconstructed in your
ytD members (balance sheet data without the ytD time Span will be perfect for Cash
Flow statements). transactional or flow data will always have a beginning balance of zero.
In my experience, this technique has reduced the number of input cells by as much as 95%.
There are two basic techniques for this load. The simplest is to extract the data as
monthly changes instead of as balances. If it is not possible to change the input, there is
a second technique: Load each month twice. First, load it in its normal location (Boy,
Jan, Feb, etc.). For example, let us say we are loading [Jun] now; the next month, before
you load the new data, load the prior month with its sign reversed. Thus, in June you
would load ([may] times -1) into [Jun] followed by the normal monthly load for [Jun].
of course, the second loading of the may data into [Jun] should only be done for those
accounts that are Balance type accounts.
This means that you will have to add a uDA indicating Balance type accounts. In fact,
if you also are adding uDAs for flipping of the natural Sign, you can combine your
efforts. Instead of creating uDAs that say simply “Flip” or “noFlip” as required in the
previous section, create more meaningful uDAs (they were probably in your accounting
system anyway), such as “asset, liability, equity, income, and expense” and then code your
sign-flip logic to look for multiple uDAs with an or function, as in Figure 7.19.
Figure 7.19
Flow Data Processing. (From Oracle Essbase Administration Services. With permission.)
