Explosion Factor - Explanation

[darpan13]

SOURCE: ONESTREAM CHAMPIONS

Hi Team,

Can someone suggest a guide or explanation on how the Explosion Factor is Calculated ?

image.png1372×629 73.8 KB

Best Regards and Thanks

Darpan Bhansali

[MichaelSBrokaw]

Explosion factor is the number of cube rows divided by the number of imported staging rows.

Reference Application Reports Help > Standard Application Reports Setup Instructions (PV 610 SV 100) > Application Reports > Application Analysis Reports > Data Statistics.

[darpan13]

Hi Michael,

Agreed, Understand the same , but somehow it’s not matching with the count of records we upload.

i believe this report is taking some sample data and not the actual count of records against a specific scenario.

I am trying to find the formulae in the data adaptor for the same. so that i can derive the expected row counts once we are in production for all Sites ( Rite now we started with just 10% of Sites)

If some one have that Code or done some similar exercise, please share the code.

Regards and Thanks

Darpan Bhansali

[MichaelSBrokaw]

Calculation only applies to Origin member Import.

Based on your screenshot:

For Actual, 484,625 cube records divided by 91,251 imported records equals 5.31 explosion factor.

For ActualAR, 479,599 cube records (at Origin member Import) divided by 198,025 imported records equals 2.42 explosion factor

[darpan13]

thanks for the response, but problem here is we have imported 318,650 records where it shows only 91251 in this report which creates the confusion.

so if the count is 318650 in one stream and in cube view record count is 484625 then explosion factor is just 1.50 or something. which changes the stats.

so just trying to debug the details of 91251 in onestream

Also tried the below code, but no success yet 

SELECT SnT , Tmt, EtT, count(*) cnt
FROM [vStageSourceAndTargetData]
group by SnT , Tmt, EtT

Regards and Thanks

Darpan Bhansali

[MichaelSBrokaw]

Here are the SQL queries used in the Data Statistics report starting with getting imported record (row) counts:

Imported Row Counts By Cube, Scenario & Origin

SELECT
[Cube].Name AS CubeName,
Member.Name AS Scenario,
(CASE
WHEN OriginId = -999 THEN ‘Import’
WHEN OriginId = -30 THEN ‘Forms’
WHEN OriginId = -31 THEN ‘Adjustment’
WHEN OriginId = -32 THEN ‘AdjInput’
WHEN OriginId = -33 THEN ‘AdjConsolidated’
WHEN OriginId = -12 THEN ‘Elimination’
ELSE ‘Unknown’
END) AS ‘OriginName’,
SubString(Cast(TimeId As NVarchar), 1, 4) As YearId,
COUNT(StageToFinanceLoadResult.ScenarioId) AS ImportedRowCount

FROM StageToFinanceLoadResult INNER JOIN Member ON StageToFinanceLoadResult.ScenarioId = Member.MemberId INNER JOIN [Cube] ON StageToFinanceLoadResult.CubeId = [Cube].CubeId

Where Cast(SubString(Cast(TimeId As NVarchar), 1, 4) As Int) = |!Members_Time_Years_RPTA!|

GROUP BY
[Cube].Name,
Member.Name,
(CASE
WHEN OriginId = -999 THEN ‘Import’
WHEN OriginId = -30 THEN ‘Forms’
WHEN OriginId = -31 THEN ‘Adjustment’
WHEN OriginId = -32 THEN ‘AdjInput’
WHEN OriginId = -33 THEN ‘AdjConsolidated’
WHEN OriginId = -12 THEN ‘Elimination’
ELSE ‘Unknown’
END),
SubString(Cast(TimeId As NVarchar), 1, 4)

ORDER BY
CubeName,
Scenario,
OriginName

Data Row Counts By Cube, Scenario & Origin

Note: Parameter |!Members_Time_Years_RPTA!| is the Year at the top of the dashboard.

SELECT
[Cube].Name AS CubeName,
Member_1.Name AS Scenario,
(CASE
WHEN OriginId = -999 THEN ‘Import’
WHEN OriginId = -30 THEN ‘Forms’
WHEN OriginId = -31 THEN ‘Adjustment’
WHEN OriginId = -32 THEN ‘AdjInput’
WHEN OriginId = -33 THEN ‘AdjConsolidated’
WHEN OriginId = -12 THEN ‘Elimination’
ELSE ‘Unknown’
END) AS ‘OriginName’,
DataRecord|!Members_Time_Years_RPTA!|.YearId,
COUNT(DataRecord|!Members_Time_Years_RPTA!|.AccountId) AS DataUnitRowCount

FROM
DataRecord|!Members_Time_Years_RPTA!|
INNER JOIN Member ON DataRecord|!Members_Time_Years_RPTA!|.EntityId = Member.MemberId
INNER JOIN Member AS Member_1 ON DataRecord|!Members_Time_Years_RPTA!|.ScenarioId = Member_1.MemberId
INNER JOIN [Cube] ON DataRecord|!Members_Time_Years_RPTA!|.CubeId = [Cube].CubeId

GROUP BY
[Cube].Name,
Member_1.Name,
(CASE
WHEN OriginId = -999 THEN ‘Import’
WHEN OriginId = -30 THEN ‘Forms’
WHEN OriginId = -31 THEN ‘Adjustment’
WHEN OriginId = -32 THEN ‘AdjInput’
WHEN OriginId = -33 THEN ‘AdjConsolidated’
WHEN OriginId = -12 THEN ‘Elimination’
ELSE ‘Unknown’
END),
DataRecord|!Members_Time_Years_RPTA!|.YearId

ORDER BY
CubeName,
Scenario,
OriginName

[darpan13]

Thanks Michael,

Let me try to derive the rows and reconcile.

Regards and Thanks

Darpan Bhansali

[sdersnah]

Should we be worried if our Explosion factor for a given Scenario is really really high? 

Cube records 3,150,935 and imported records 42 to an explosion factor of 75022.26 in a top cube that is linked to sub cubes by entity dimension.