In some cases, many data intersections unintentionally explode in the Cube with unintended values. This can happen with multi-dimensional data structures.
For example, an application has one Entity, four Accounts, 100 Products (UD1) and 100 Customers (UD2). The user has entered three numbers in the system. (A#Sales:U1#Prod1:U2#Cust1=22.0, A#Sales:U1#Prod5:U2#Cust1=33.0, A#Sales:U1#Prod7:U2#Cust100=55.0).
The following cases explain what happens when writing the following Formulas.
Case 1: No Explosion
Use the following statement to set the value of a data intersection equal to another or a constant value.
api.Data.Calculate(“A#Cash1 = A#RestrictedCash”)
Cash1 Account will equal what was in the Restricted Cash Account. The only UD1 and UD2 Members populated would be None Members because it is not typical to delineate Cash by Product, or Customer.
Case 2: No Explosion
api.Data.Calculate(“A#Profit:U2#Cust1 = A#Sales * 1.05”)
Two of the three numbers are multiplied by 1.05 and copied from the Sales Account to the Profit Account which are only the source Sales numbers for Customer 1.
Case 3: Some Explosion
api.Data.Calculate(“A#Profit = A#Sales:U2#Cust1”)
The user specified a Customer for source data but did not specify which customer to use when writing to the destination. This means answers are written to every base-level member of the Customer dimension, resulting in some data explosion. The Profit account has a number for every Customer (UD2) using the same Product (UD1) member as the source numbers. Profit account will have 300 numbers, but because there were three different source products, there will be some data explosion.
Not specifying a Member for a Dimension is the same as specifying all for a Member. If All is specified on the left only, data explosion occurs. Some level of data explosion occurs if:
-
The right side of an equation identifies a specific member or amount.
-
The left side of an equation specifies All for one or more dimensions.
Case 4: Large Scale Explosion When Setting to a Constant
api.Data.Calculate(“A#Profit = 2.0”)
Profit will have 1000 numbers stored that are all Products by all Customers, causing large scale explosion. However, this example is overly simple. If all Dimensions are used, trillions of numbers may be stored. A constant is the same as not specifying All for every Dimension on the right side of the equation.
Writing Formulas to Avoid Data Explosion
If a member is specified for a dimension on the right side of the equation, explicitly specify a value for that Dimension on the left side.
The examples below are the same as Cases 3 and 4 from above, but this time the formulas are written to avoid data explosion.
Case 3: Specify a target on the left side of the equation
api.Data.Calculate(“A#Profit = A#Sales:U2#Cust1”) would be better written as api.Data.Calculate(“A#Profit:U2#Cust1 = A#Sales:U2#Cust1” ) if that is what is intended. If the user intended on copying Cust1’s Sales figures to every Customer intersection under the Profit Account, the formula would need to be written as follows:
api.Data.Calculate(“A#Profit:U2#All = A#Sales:U2#Cust1”
Case 4:
api.Data.Calculate(“A#Profit = 2.0”) will fail since the constant of 2.0 implies All Members from each Dimension. In order for this Formula to execute, it needs to be api.Data.Calculate(“A#Profit:F#All:O#All:I#All:U1#All:U2#All:U3#All:U4#All:U5#All:U6#All:U7#All:U8#
All = 2.0”), although the user may choose to do otherwise.
Avoiding Data Explosion in Stored Formulas
When writing stored calculations, the Member Script equations in api.Data.Calculate end up reading or calculating one or more Data Buffers for the purpose of saving a resulting Data Buffer. As described earlier, thousands of Data Cells could be processed using a seemingly simple one-line formula.
Data Explosion can occur when a formula is inadvertently written to read or calculate a Data Buffer and then copy all Data Cells in that Data Buffer multiple times to every base-level Member of a Dimension using the resulting Data Buffer (often causing hundreds of thousands of new numbers to be saved). We only apply calculation to intersections where data exists. Fortunately, OneStream protects the user from writing formulas that could result in data explosion, but the concepts are important to understand because it is possible to circumvent those protections.
Consider the formula from an earlier example:
api.Data.Calculate("A#Sales1 = A#Sales2 + A#Sales3”)
Now, the formula is changed, so it only copies the sales data for specific customers with UD2 being the Customer Dimension.
api.Data.Calculate("A#Sales1:U2#None = A#Sales2:U2#CustomerX + A#Sales3:U2#CustomerY”)
The above formula will not result in data explosion because there is the same level of detail (i.e., the same Dimensions) specified in the destination as in every source operand. The example now reads the Sales2 data for CustomerX, adds it to the Sales3 data for CustomerY, and saves the results in the Sales1 account and the UD2 None Member.
The following formula is written to cause Data Explosion:
api.Data.Calculate("A#Sales1 = A#Sales2:U2#CustomerX + A#Sales3:U2#CustomerY”)
The UD2 Dimension is specified for the source operands which is the right-hand side of the equals sign, but UD2 is not specified for the destination which is the left-hand side of the equals sign.
When processing this formula, OneStream will read the two Data Buffers and add them together correctly as before. However, it then needs to assign the combined Data Buffer to the Sales1 Account. However, the system cannot use U2#CustomerX or U2#CustomerY because Data Cells were created by adding those together and it cannot arbitrarily choose one over the other. The system could also have defaulted to use the U2#None Member, but history suggests that this type of rule is more often written in error, and the customer did not intend the results to be stored in the U2#None Member. From a maintenance perspective, OneStream feels that it is better to explicitly specify U2#None if that is the intended destination Member.
If something like this were to happen, OneStream will provide an error message notifying the user that data explosion will occur when trying to execute the above formula. Otherwise, the formula will copy the source Data Buffer to every base-level UD2 Member because #All is the default setting for each unspecified Member.
To circumvent the error message and force data explosion (please do not do this), explicitly specify U2#All in the destination as shown below. This should be avoided and #All should never be used in Member Scripts for stored formulas. However, the capability is provided for extremely rare circumstances where that functionality was relied upon using an older product. In this case, the consultant carefully analyzed the quantity of data and metadata settings to ensure the data explosion resulted in a manageable number of Data Cells.
CAUTION: This causes Data Explosion! Do not ever use #All explicitly in stored Member Formulas.
api.Data.Calculate("A#Sales1:U2#All = A#Sales2:U2#CustomerX + A#Sales3:U2#CustomerY”)
