SQL - Business logic¶

In this example we create user defined functions implementing business logic. If you haven't done already, please read the Simple staging example first for a basic explanation of concepts used in the template and config. The functions implemented in this example are used in the SSIS DataVault example.

Model¶

In this example we will be generating the business logic (business rules) defined in the DWH model.

Template¶

We create a template for each type of business rule. The example model contains two types of rules: a derived field and a lookup.

DeriveRule_owner.udf_BR_DeriveRule_name.sql¶

The derived field template creates a function with the appropriate signature based on the parameters and attributes defined in the model. For the implementation the business rule child element expression is referenced.

Note

When a model attribute is accessed an underscore is used as accessor (for example Parameter_name) a child element is accessed using the dollar sign (for example DeriveRule$expression). The accessors can be configured in the config.

-- @XGenComment(This is the function template for all derive rules in the DWH)
CREATE FUNCTION [DeriveRule_owner].[udf_BR_DeriveRule_name] (
  -- @XGenTextSection(name="Parameter" suffix=",")
  @Parameter_name Parameter_fulldatatype
)
RETURNS TABLE
RETURN
  SELECT
    -- @XGenTextSection(name="Attribute" suffix=",")
    [Rule].[Attribute_name]
  FROM DeriveRule$expression AS [Rule]
GO

LookupRule_owner.udf_BR_LookupRule_name.sql¶

In the model a lookup business rule has a mapped source entity. With the information in the model an implementation can be generated that performs a lookup query on the mapped source entity.

-- @XGenComment(This is the function template for all lookup rules in the DWH)
CREATE FUNCTION [LookupRule_owner].[udf_BR_LookupRule_name] (
  -- @XGenTextSection(name="Parameter" suffix=",")
  @Parameter_name Parameter_fulldatatype
)
RETURNS TABLE
RETURN
  WITH [Source_CTE] (
    -- @XGenTextSection(name="Parameter")
    [Parameter_name],
    -- @XGenTextSection(name="Attribute" suffix=",")
    [Attribute_name]
  ) AS (
    SELECT
      -- @XGenTextSection(name="MappedParameter")
      MappedParameter_expression AS [MappedParameter_targetAttributeName],
      -- @XGenTextSection(name="MappedAttribute" suffix=",")
      MappedAttribute_expression AS [MappedAttribute_targetAttributeName]
    -- @XGenTextSection(name="MappableObjectMapping")
    FROM MappableObjectMapping_mappedObjectJoiner
  )
  SELECT
    -- @XGenTextSection(name="Attribute" suffix=",")
    [Attribute_name]
  FROM [Source_CTE]
  WHERE
    -- @XGenTextSection(name="Parameter" prefix="AND ")
    [Parameter_name] = @Parameter_name
;
GO

Documentation¶

For documentation on templates, please see Template.

Config¶

Each type of business rule requires it's specific configuration, for this example we therefore have two configs.

Config for derived field¶

<?xml version="1.0" encoding="UTF-8"?>
<XGenConfig>
  <Model/>
  <TextTemplate rootSectionName="DeriveRule">
    <FileFormat currentAccessor="_" childAccessor="$" singleLineCommentPrefix="--" annotationPrefix="@XGen" annotationArgsPrefix="(" annotationArgsSuffix=")" />
    <Output type="output_per_element" />
  </TextTemplate>
  <Binding>
        <SectionModelBinding section="DeriveRule" modelXPath="/modeldefinition/system/mappableObjects/businessRule[@stereotype='derive']" placeholderName="DeriveRule">
            <SectionModelBinding section="Parameter" modelXPath="attributes/parameter" placeholderName="Parameter" />
            <SectionModelBinding section="Attribute" modelXPath="attributes/attribute" placeholderName="Attribute" />
        </SectionModelBinding>
  </Binding>
</XGenConfig>

Config for lookup rule¶

<?xml version="1.0" encoding="UTF-8"?>
<XGenConfig>
  <Model/>
  <TextTemplate rootSectionName="LookupRule">
    <FileFormat currentAccessor="_" childAccessor="$" singleLineCommentPrefix="--" annotationPrefix="@XGen" annotationArgsPrefix="(" annotationArgsSuffix=")" />
    <Output type="output_per_element" />
  </TextTemplate>
  <Binding>
        <SectionModelBinding section="LookupRule" modelXPath="/modeldefinition/system/mappableObjects/businessRule[@stereotype='lookup']" placeholderName="LookupRule">
            <SectionModelBinding section="Parameter" modelXPath="attributes/parameter" placeholderName="Parameter" />
            <SectionModelBinding section="Attribute" modelXPath="attributes/attribute" placeholderName="Attribute" />
            <SectionModelBinding section="MappableObjectMapping" modelXPath="mappableObjectMappings/mappableObjectMapping" placeholderName="MappableObjectMapping" />
            <SectionModelBinding section="MappedParameter" modelXPath="mappableObjectMappings/mappableObjectMapping/attributeMappings/attributeMapping[@targetAttributeStereotype='parameter']" placeholderName="MappedParameter" />
            <SectionModelBinding section="MappedAttribute" modelXPath="mappableObjectMappings/mappableObjectMapping/attributeMappings/attributeMapping[@targetAttributeStereotype='attribute']" placeholderName="MappedAttribute" />
        </SectionModelBinding>
  </Binding>
</XGenConfig>

Documentation¶

For documentation on the configuration, please see Config.

Output¶

When running CrossGenerate the output using the given Model, Templates and Configs will be as follows:

BusinessRule.udf_BR_Derive_Customer_FullName.sql¶

CREATE FUNCTION [BusinessRule].[udf_BR_Derive_Customer_FullName] (
  @FirstName varchar(50),
  @LastName varchar(100)
)
RETURNS TABLE
RETURN
  SELECT
    [Rule].[FullName]
  FROM (SELECT @FirstName + ' ' + @LastName AS [FullName]) AS [Rule]
GO

BusinessRule.udf_BR_Lookup_Country.sql¶

CREATE FUNCTION [BusinessRule].[udf_BR_Lookup_Country] (
  @Country_Code varchar(3)
)
RETURNS TABLE
RETURN
  WITH [Source_CTE] (
    [Country_Code],
    [Country_Description],
    [Country_Continent]
  ) AS (
    SELECT
      Reference_Country.Country_Code AS [Country_Code],
      Reference_Country.Country_Description AS [Country_Description],
      Reference_Country.Country_Continent AS [Country_Continent]
    FROM [$(DWH_Staging_Reference)].[dbo].[Country] AS [Reference_Country]
  )
  SELECT
    [Country_Description],
    [Country_Continent]
  FROM [Source_CTE]
  WHERE
        [Country_Code] = @Country_Code
;
GO

Attachments¶

The entire DataVault example, including SQL and SSIS templates, can be found in the SSIS DataVault example.