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 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! 

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.

  • Sai_Maganti's avatar
    Sai_Maganti
    Contributor II
    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.

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      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! 

    • cons1's avatar
      cons1
      New Contributor III
      2 years ago

      Hello Sai,

      Can you share with me the BR for option 2? I would like to try and see results if it fits my requirements.

      • 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
  • ckattookaran's avatar
    ckattookaran
    VIP
    3 years ago

    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)

  • TheJonG's avatar
    TheJonG
    Contributor III
    3 years ago

    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?

    • cons1's avatar
      cons1
      New Contributor III
      2 years ago

      Hi Jon, 

      The script can be adjusted to pull any other stage column and can return multiple lines of data using BlendOperationType.Concatenate.

      Can you share more about this? I tried the BlendOperationType.Concatenate however it only returns the all in a joined string. I am thinking of pulling them out as rows.

       

  • ckattookaran's avatar
    ckattookaran
    VIP
    3 years ago

    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. 

    • Sai_Maganti's avatar
      Sai_Maganti
      Contributor II
      3 years ago

      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.