01-12-2022
03:20 PM
- last edited
a week ago
by
JackLacava
01-12-2022 03:21 PM
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.
01-12-2022 03:21 PM
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
01-12-2022 03:22 PM
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
01-12-2022 03:22 PM
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
01-12-2022 03:23 PM
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
01-12-2022 03:24 PM
Thanks Michael,
Let me try to derive the rows and reconcile.
Regards and Thanks
Darpan Bhansali
07-12-2022 12:49 PM
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.