03-07-2022 03:33 PM - last edited on 05-24-2023 05:58 AM by JackLacava
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!
Solved! Go to Solution.
03-08-2022 10:41 AM
If you only want to review manually then here are some ideas.
All the above need some understanding of sql, views etc but better than relational blend.
03-07-2022 04:43 PM
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)
03-08-2022 09:32 AM
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
12-28-2022 04:25 AM
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?
08-23-2023 09:46 AM
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.
03-08-2022 10:41 AM
If you only want to review manually then here are some ideas.
All the above need some understanding of sql, views etc but better than relational blend.
03-16-2022 05:02 PM
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!
08-28-2023 04:36 AM
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.
03-09-2022 08:51 AM
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.
03-09-2022 10:14 AM
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.