Forum Discussion

NicoleBruno's avatar
NicoleBruno
Valued Contributor
3 years ago

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

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 wou...
Cube View
Dashboards
Excel Add In
  • Sai_Maganti's avatar
    Sai_Maganti
    3 years ago

    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.

TheJonG's avatar
TheJonG
Contributor III

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

 

kwojsz's avatar
kwojsz
New Contributor III
2 years ago

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?