Count ZeroDataCells by Account Dimension Member
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.
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)")
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.
