Databases Reference
In-Depth Information
Let us say you have a member called
TOTAL VEHICLES IN STOCK
and when
you are loading data you need to subtract the daily sold quantity in the data
feed from the quantity that already exists in the
TOTAL VEHICLES IN STOCK
member. If there are 1,000 units in the
TOTAL VEHICLES IN STOCK
member
and you sold 100 vehicles today, when you load the daily data the 100 sold
units will be subtracted from the 1,000 units in the
TOTAL VEHICLES IN
STOCK
member. This will leave 900 as the new value in the
TOTAL VEHICLES
IN STOCK
member.
Sign flip
Flipping the numeric sign is one of the features of the Essbase data load that can
be accomplished in the data load rule. Sometimes it is necessary for us to flip the
numeric sign of a set of values as they are being loaded into the database.
Consider that the basic profit calculation is
Sales - Discounts = Profits
. The
data elements you receive from the corporate finance department are all in positive
numbers. Sales and Costs are provided as positive numbers. Depending on how you
have your database outline structured and to keep things simple (simple is good)
for you to create a Profit member in your database outline, you would need it to
have two children. One child would be Sales and other would be Costs. When you
calculate the outline, the values would roll up correctly to a total Profit.
Another way would be to just have a Profit member to load the data values to and
use the
Add to existing values
setting so you would add a positive Sales value and
a negative Costs value to the Profit member as the data is being loaded. The only
drawback to this method is you would not have the component data handy and
loaded into the database in the event there are issues or questions with the data.
Debugging would also be more complicated as you would need to go back to the
input data for any validations.
There are two ways, during a data load to flip a numeric sign on a data value.
They are as follows:
1. Flip the numeric sign value based on an outline member UDA. For instance,
you can only flip the numeric sign on members that are tagged with the UDA
of Costs in the database outline. As you can see in the previous screenshot,
there is a UDA called
NegSign
. While we are doing the
Dynamic Calc
,
we check the UDA, if UDA is
NegSign
, we can do a negation. This is just
an example.
2. Arbitrarily flip the numeric sign on all data values in a particular column
or field by multiplying the number with a minus (-) sign.
