Skip to main content
N
NicoleBruno
Veteran
March 7, 2022
Solved

Possible to set up a CV or QV showing stage data?

  • March 7, 2022
  • 4 replies
  • 0 views

Hi all, 

We have data coming into Import/stage that subtotals to zero when it validates/maps into the cube. We need to review this data in import/stage before the validation and the easiest way would be a QV or CV instead of having the user manually go into the workflow and export the data. I thought accessing stage data in a QV/CV was possible but I can't find specifics. Any direction would be useful - thanks! 

Best answer by Sai_Maganti

If you only want to review manually then here are some ideas.

  • A simple SQL data adapter using standard views vStageSourceAndTargetData or vStageSourceAndTargetDataWithAttributes. These views contain all the scenarios and time and hence you need to filter out the scenario and time in your case. You can do with only source columns as this is before validation. Attach this to either a Data Grid or Pivot Grid for some quick slice and dice.
  • Or use Table Views in OneStream excel (will need a BR to fetch data from the above mentioned views) in a dashboard and use this dashboard in workflow profile where the data can be reviewed easily without leaving the workflow screen
  • Or export to a csv using BRAPi.Import.Data.ExportStageData (only source columns)

All the above need some understanding of sql, views etc but better than relational blend.

4 replies

C
ckattookaran
Newcomer
March 7, 2022

You cannot do that directly in cubeview or QV. If that is the requirement, you might have to create UD8 members and use relational blending to show that value. (each column in the stage will need a member in cube view)

I'm not sure even doing that is going to work, as that would mean you break the process of Import into multiple steps.

They can still go ahead with the validation (not stopping them from going forward) unless an event handler does something behind the scene that fails the validation.

But if you are looking to view it, you can use the relational blend approach. What about a confirmation rule that checks if the subtotal is zero. (again, that will fail the confirmation, not the load to the cube)

T
OneStream Employee
TheJonGOneStream Employee
OneStream Employee
March 8, 2022

Hi Nicole - as Celven said, you can use the Relational Blend functions to pull stage data into a Cube View. The below code pulls the Attribute1 column from Stage into a Cube View. It should be attached to a UD8 Dynamic Member Formula and would be called into a Cube View with V#Annotation. The script can be adjusted to pull any other stage column and can return multiple lines of data using BlendOperationType.Concatenate. 

 

Dim objViewMember As ViewMember = ViewMember.GetItem(api.Pov.View.MemberId)

If Not api.Entity.HasChildren Then
	If objViewMember.IsAnnotationType Then
		Dim criteria As New Text.StringBuilder
		criteria.Append("AcT = '" & api.Pov.Account.Name & "' ")
		criteria.Append("And U1T = '" & api.Pov.UD1.Name & "' ")
		Return api.Functions.GetStageBlendTextUsingCurrentPOV(BlendCacheLevelTypes.WfProfileScenarioTime, " & _
		""DU", "Load History.MultiMonthImport","AcT,U1T,A1", criteria.ToString, "A1",BlendTextOperationTypes.FirstValue)
	Else
		Return Nothing
	End If
Else
	Return Nothing
End If

 

K
kwojsz
Contributor
December 28, 2022

Hi Jon, do you know maybe whether this function works with Import workflow type with Can Load Unrelated Entities = True and no entities assigned? I'm not able to get any result when no entities are assigned to the workflow and during the development I noticed that there's no problem if I assign an entity to this WF - everything works as intended. Is there any workaround for this, like playing with BlendCacheLevel (I tried to test it, but without any positive result) or anything else?

S
Sai_MagantiAnswer
Newcomer
March 8, 2022

If you only want to review manually then here are some ideas.

  • A simple SQL data adapter using standard views vStageSourceAndTargetData or vStageSourceAndTargetDataWithAttributes. These views contain all the scenarios and time and hence you need to filter out the scenario and time in your case. You can do with only source columns as this is before validation. Attach this to either a Data Grid or Pivot Grid for some quick slice and dice.
  • Or use Table Views in OneStream excel (will need a BR to fetch data from the above mentioned views) in a dashboard and use this dashboard in workflow profile where the data can be reviewed easily without leaving the workflow screen
  • Or export to a csv using BRAPi.Import.Data.ExportStageData (only source columns)

All the above need some understanding of sql, views etc but better than relational blend.

    N
    NicoleBrunoAuthor
    Veteran
    March 16, 2022

    Thanks - the SQL suggestion is perfect for the dashboard and was super easy to set up with no extraneous UD8 master data. Very much appreciated! 

      C
      ckattookaran
      Newcomer
      March 9, 2022

      Thinking about this, if you want the laod to fail, why not do this in the connector itself. If the subtle sum is not zero then don't send anything back to parser. I'm throwing stuff out there without much idea. So the idea is you get the data to a datatable. Perform a sum of the rows matching your condition, if it is not zero remove those rows or send an empty datatable back so that whole load fails. 

      S
      Sai_Maganti
      Newcomer
      March 9, 2022

      The OP might want to review the data coming in and hence the data need to come in to the stage.

      I would bring the data into the stage and set up a source derivative rule (Derivative Type = Check Rule) for checksum and make it fail at validation stage if the subtotal is not zero.

      Terms & ConditionsAccessibility statement