Actuals data load issue - YTD adjustment being loaded

GedS1
New Contributor II

Hi all,

I have an issue whereby I'm loading our company actuals into OneStream, but if a General Ledger code has no data in it but had data in previous months, I think OneStream assumes the YTD value to be zero and so the opposite total of the previous months is being loaded.

So for example the YTD total of repairs & maintenance may be £100 at Apr-22, and in May-22 there are no further transactions so the YTD position is still £100. However as no transactions occurred in May, when the data is loaded      -£100 appears in May.

I've checked the DateSource and TransformationRules we have set up, I cant see a reason why this would be happening as it's all set up as 'Periodic', and so only the data from the initial load should be include.

Does anyone have any ideas as to where the problem my lie, or where I could look to try and see why this is happening?

Many thanks,

Ged

2 ACCEPTED SOLUTIONS

PeterFu
Contributor II

Hi,

 

Go to your Scenario dimension and have a look at your scenario. There are some settings on the scenario to control whether your missing data is periodic or YTD zero. See print screen below. In your case you will need to set it to periodic I think.

 

PeterFu_0-1655470279837.png

 

Peter

View solution in original post

TonyToniTone
Contributor II

Peter is spot on. 

To add more color in how OneStream stores data and handles No Data situations.  By default, any data records loaded to the Cube is stored as YTD in the database.  Periodic is always derived, even if your data source is identified as Periodic and the data records in the file/data integration is Periodic.  For Periodic, we store the YTD number and then when Periodic is requested for rendering, we dynamically take current period YTD - prior period YTD to derive a current Periodic number.  Every data record loaded to the Cube for the year will be stored in a database table called DataRecordXXXX (XXXX is identified as the Year being loaded).  For every data record in the DataRecordXXXX, the data record contains 12 value columns ( 1 for each period 1-12 ) and 12 cell status columns ( 1 for each period 1-12 ).  This cell status column holds values that dictate the status of the value for a specific period.  This includes Calc Status, Storage Type ( Stored/Not Stored ), whether data is real or derived data, and if using NoData Zero View Override.  

The real and derived data and NoData Zero View override is what is used when a value for a data record is loaded to the Cube in Jan, for example, but in Feb, a value is NOT loaded for the same data record.  In Jan, the cell status for the individual data record will indicate real data and stored.  Remember when stored, it is stored as YTD.  In Feb, when you don't load a value for that data record, the cell status will indicate that the value is derived and uses NoData Zero View to determine how to handle this no data situation.  

In this situation, the following order of operations happens:

1.  As Peter has explained, the 1st step is to check the setting called No Data Zero View for Non-Adjustments and No Data Zero View for Adjustments in the Scenario Property Settings.

2.  Step 2 is another level of granularity and can override the No Data Zero View for Non-Adjustments and No Data Zero View for Adjustments for the Scenario.  These settings exist through the Import from the Workflow Profile and name as the following:

TonyToniTone_0-1655476934299.png

An example of why to use these setting.  If you had an organization that mostly loads data from a source system that is YTD for Actuals, you would set the Scenario settings as YTD.  However, maybe you have a section of the organization that has a source system that can only produce Periodic values for Actuals.  Since the Scenario settings crosses across all data in Actuals, the Workflow settings can specifically override the Scenario settings during data importing and loading the cube.  The override logic would happen during the Load and Process of the Workflow process.  

3.  The final override can be done at the Account level.  Same settings exist at the Account Member Property level as you would see in the Scenario Member Property.  The default setting is set to (Use Default) which means the Account will use the Scenario Settings first.  If the need is to override at the Account level, then you can do it.  As I like to say, just because you can do it, doesn't mean that you should.  This override should be used as more of "break glass in case of emergency" scenario.  

Hope this helps

View solution in original post

4 REPLIES 4

PeterFu
Contributor II

Hi,

 

Go to your Scenario dimension and have a look at your scenario. There are some settings on the scenario to control whether your missing data is periodic or YTD zero. See print screen below. In your case you will need to set it to periodic I think.

 

PeterFu_0-1655470279837.png

 

Peter

GedS1
New Contributor II

Many thanks for your help on this Peter - that has solved the issue so big thanks! 🙂

TonyToniTone
Contributor II

Peter is spot on. 

To add more color in how OneStream stores data and handles No Data situations.  By default, any data records loaded to the Cube is stored as YTD in the database.  Periodic is always derived, even if your data source is identified as Periodic and the data records in the file/data integration is Periodic.  For Periodic, we store the YTD number and then when Periodic is requested for rendering, we dynamically take current period YTD - prior period YTD to derive a current Periodic number.  Every data record loaded to the Cube for the year will be stored in a database table called DataRecordXXXX (XXXX is identified as the Year being loaded).  For every data record in the DataRecordXXXX, the data record contains 12 value columns ( 1 for each period 1-12 ) and 12 cell status columns ( 1 for each period 1-12 ).  This cell status column holds values that dictate the status of the value for a specific period.  This includes Calc Status, Storage Type ( Stored/Not Stored ), whether data is real or derived data, and if using NoData Zero View Override.  

The real and derived data and NoData Zero View override is what is used when a value for a data record is loaded to the Cube in Jan, for example, but in Feb, a value is NOT loaded for the same data record.  In Jan, the cell status for the individual data record will indicate real data and stored.  Remember when stored, it is stored as YTD.  In Feb, when you don't load a value for that data record, the cell status will indicate that the value is derived and uses NoData Zero View to determine how to handle this no data situation.  

In this situation, the following order of operations happens:

1.  As Peter has explained, the 1st step is to check the setting called No Data Zero View for Non-Adjustments and No Data Zero View for Adjustments in the Scenario Property Settings.

2.  Step 2 is another level of granularity and can override the No Data Zero View for Non-Adjustments and No Data Zero View for Adjustments for the Scenario.  These settings exist through the Import from the Workflow Profile and name as the following:

TonyToniTone_0-1655476934299.png

An example of why to use these setting.  If you had an organization that mostly loads data from a source system that is YTD for Actuals, you would set the Scenario settings as YTD.  However, maybe you have a section of the organization that has a source system that can only produce Periodic values for Actuals.  Since the Scenario settings crosses across all data in Actuals, the Workflow settings can specifically override the Scenario settings during data importing and loading the cube.  The override logic would happen during the Load and Process of the Workflow process.  

3.  The final override can be done at the Account level.  Same settings exist at the Account Member Property level as you would see in the Scenario Member Property.  The default setting is set to (Use Default) which means the Account will use the Scenario Settings first.  If the need is to override at the Account level, then you can do it.  As I like to say, just because you can do it, doesn't mean that you should.  This override should be used as more of "break glass in case of emergency" scenario.  

Hope this helps

GedS1
New Contributor II

Many thanks for adding to Peter's answer above, now we have some additional ways we can handle No Data situations, this is very useful to know 🙂