Skip to main content
J
jmohl
October 29, 2024
Solved

Count ZeroDataCells by Account Dimension Member

  • October 29, 2024
  • 2 replies
  • 0 views

Is there a way to determine the number of data cells by account-type dimension member? In our example, we are reviewing an implemented application with millions of ZeroData cells and are trying to identify which account formulas are producing the most zero data cells.

System Diagnostics has a "Data Volume Stats" tab that lists data cell status counts (i.e., RealCellCount, ZeroCellCount, etc) by data unit. However, there does not appear to be any reports that display this information by account. Has anyone had success retrieving this information via a different application report or custom SQL query (against the application database)? This is probably unrealistic to retrieve (given the data volumes required), but maybe there is a way I haven't yet considered.

Best answer by acrespo

One approach to managing Zero Data Cells is to use the RemoveZeros funtion on the member formulas.

A simplistic example would be: api.data.calculate(A#TargetAccount = RemoveZeros(A#SourceAccount1 + A#SourceAccount2)")

2 replies

T
T_Kress
October 29, 2024

Have you checked out the Data Record Analysis tool in OpenPlace?   I think the "Detail" report may show it by account:


T_Kress_0-1730238725277.png

 

J
jmohlAuthor
October 30, 2024

Hi T_Kress ,

Thanks for the quick reply and suggestion. The "Data Record Analysis" solution was helpful for this.

While it took a bit of trial and error, I was able to back into calculated "ZeroData" cells by Account via the following steps (in case this is useful for anyone else looking to replicate the analysis).

1. Filter records with cell amounts that are >= -0.01 AND <= 0.01

2. Select the "IsRealData - Calculation" check box.

3. Select a sample of entities with the most data (to prevent timeout issue caused by querying all entities). An intermediate entity parent could be chosen as well.

4. Select the parent account under calculations in question reside

5. Run the "Detail" report view.  This will return all base intersections (i.e., if you select A#Top, it will show all accounts meeting the parameter criteria).

6. Export to CSV and remove and records with a cell amount of -0.01 OR 0.01 (this can potentially be avoided by filtering >=-0.001 AND <= 0.0.01). 

7. Create a Pivot Table by with AccountName in the rows and the "COUNT of Amount" in the values.

This allowed us to extrapolate and identify which account formulas are writing the most ZeroDataCells to the database. As acrespo alluded to, account member formulas producing ZeroDataCell values should be adjusted to utilize the "RemoveZeros" function.


jmohl_1-1730303300380.png

 

 

T
T_Kress
October 30, 2024

Excellent, glad this OpenPlace tool helped you trace the issue.  So many great tools in OpenPlace!!!

As mentioned, you will want to typically always use RemoveZeros and/or RemoveNoData in nearly all formulas.  It needs to be purposeful as to why you may not want to use those in member formulas.  It is rare that you want to calculate data without using one of those two conditions, so it should be the exception to the member formula rule that you do NOT use those.

Sounds like you have a lead on solving and good luck!

A
acrespoAnswer
October 30, 2024

One approach to managing Zero Data Cells is to use the RemoveZeros funtion on the member formulas.

A simplistic example would be: api.data.calculate(A#TargetAccount = RemoveZeros(A#SourceAccount1 + A#SourceAccount2)")

Terms & ConditionsAccessibility statement