How to use an information mart script?

In the distribute module, you can create an information mart and its scripts to use the data from your data vault and create data products.

You can refer to the documentation to see how to create an information mart script. Information mart script | Create a new script

The scripts created are not directly applied to the databases.

To be able to use the script and execute them, you first need to create a new version and deploy it on an environment.

When deploying a new version, beVault automatically creates a state machine for each information mart to run the scripts in the order defined in the IM. The state machine is named :

[project]_[environment]_generate_[ IM name]

Alternatively, you can create your own state machine to use and execute the different scripts.

State Machine to run the script

To use a script and execute it in a database, you need to create a state machine in State that uses the “ExecuteDataVaultQuery” worker. The worker takes the following parameters:

• TimeoutSeconds

  • The maximum time the query can run before sending an error

  • Default: 2h

• QueryType

  • Reader: The query will return the query result set in the output.

  • Nonquery: The query does not return values.

  • Scalar: Returns the first value of the first column of the result set.

• QueryName

  • The name of the query in the Information Mart module

  • If the script has been created in the “Global” section, it should be prefixed by “Global/”

    • Example: “Global/myScript.sql”

  • If the script has been created in a Source System section, it should be prefixed by “SourceSystem/sourceCode/”

    • Example: “SourceSystem/crm/myScript.sql”

• EnvironmentName

  • The name of the environment where the script is deployed

• ProjectName

  • The technical name of the project of the script

• Parameters

  • You can add as many additional parameters that will be used for the liquid script

Here is an example of the state machine to run the script test.sql for the project “default” in the environment “testenv”

{
  "StartAt": "query",
  "TimeoutSeconds": 3600,
  "States": {
    "query": {
      "TimeoutSeconds": 3600,
      "Type": "Task",
      "Resource": "ExecuteDataVaultQuery",
      "Parameters": {
        "TimeoutSeconds": 1200,
        "QueryType": "Reader",
        "QueryName": "SourceSystem/sourceSystemCode/test.sql",
        "EnvironmentName": "testenv",
        "ProjectName": "default",
        "param1" : "value1"
      },
      "End": true
    }
  }
}

Output

The output varies depending on the query type

{
 {
  "RecordsAffected": 0,
  "Scalar": null,
  "Result": []
}