Skip to main content
A
AndreaF
July 31, 2023
Solved

Connector rule - Drill back on dimension using a business rule logical operator

  • July 31, 2023
  • 1 reply
  • 1 view

Hi,

in a connector business rule, when a dimension has a business rule logical operator, is there a better way to build the SQL drill back query than reverse engineer what the business rule is doing?

In the example below the business rule brings "Zero" to the stage when the source UD1 is null. The code in the drill back is reversing that to get the right source data:

'UD1
If sourceValues.Item(StageTableFields.StageSourceData.DimUD1).ToString.XFEqualsIgnoreCase("Zero") Then
	whereClause.Append("And (Department IS NULL Or Department = '') ")
Else
	whereClause.Append("And (Department = '" & SqlStringHelper.EscapeSqlString(sourceValues.Item(StageTableFields.StageSourceData.DimUD1).ToString) & "') ")
End If

However, this is a simple business rule. I am wondering if there is a way of getting the source data before it is passed through the logical operator business rule, to reduce code complexity in the drill back. Thank you

Best answer by JackLacava

OneStream doesn't have a "pre-Stage" area, no. I mean, most systems don't even have a Stage area 🙂

If you want to keep track of "pure", pre-transformation values, you have to do it explicitly yourself at Import time. You could do that by mapping original values into an Attribute or Attribute Value field, effectively copying it. When you drillback, you could then look up that AV field to retrieve them. You can do the same for any other identifier that you plan to leverage for drillback purposes (rowIDs or whatever).

1 reply

J
JackLacavaAnswer
July 31, 2023

OneStream doesn't have a "pre-Stage" area, no. I mean, most systems don't even have a Stage area 🙂

If you want to keep track of "pure", pre-transformation values, you have to do it explicitly yourself at Import time. You could do that by mapping original values into an Attribute or Attribute Value field, effectively copying it. When you drillback, you could then look up that AV field to retrieve them. You can do the same for any other identifier that you plan to leverage for drillback purposes (rowIDs or whatever).

A
AndreaFAuthor
July 31, 2023

Mapping the original values into an Attribute is a very good idea, thank you for this

Terms & ConditionsAccessibility statement