Steps to create Global Dimension

by

In myBMT

Table object

  • Identify Source Table name in Container
  • Check Table is in myBMT as a Data Source
    • or, copy table name and add new Data Object
  • In Information [More Actions] check ‘show in container’
    • (with shower), table data should appear
    • Back <<
  • Select [Edit]
    • Set Data Mart as Global
    • Set src_Code & src_Class
      • these make the code unique
    • Set Status to Yes
      • this will import the csv file to staging via the pipeline (at night)
    • [Save]

Check table has Data Fields objects

  • If not, and the table is already in staging
    • Return to information page and [More Actions] check ‘collate Table Fields’
    • If there are data Fields to add [Execute SQL]
  • If not, and the table is NOT already in staging
    • either, wait till tomorrow
    • or, go to SSMS and import as a flat file into staging using the ifs schema
    • Once table is in staging, action table is already in staging above

Mark Data Fields for Controller Snippet

  • If the Fields are newly imported they will be marked as REFERENCE
  • Identify the field that will be used for src_Class and src_Code
  • Ensure a Data Field is marked as NAME
  • Create new field for LABEL using the preferred object name,
    • This will be how the object will be known, and form part of the the exec command
    • <LABEL/mart ctrlr name>
    • remember two single quotes before and after (allows the string to be sent/posted)

Create Data Mart controller

  • Select [Data Mart] menu item
  • Select [Global]
  • [ADD NEW]
    • Set Description as the name of the Data Mart Controller
      • this should be the same name as the LABEL
      • <LABEL/mart ctrlr name>
    • Identify the table as the Data Source
    • Table Type = Entity
    • targ_Class and targ_Code are the same as src_Class and src_Code
    • set targ_context to ‘entity’
    • If you require to filter the source table, add a where clause,
      • remembering all quotes should be two single.

Export Data Mart Controller snippet

  • Select [Data Mart] menu item (with shower)
  • Select [Controller]
  • Find your data mart controller in the list
    • The list is alphabetical by your description name, but you can also see the parent object
  • [Get Data]
    • This action composes the snippet from the components
    • Check the Controller string for any obvious errors
      • i.e. missing or empty fields
  • Select your endpoint usually PRD/ dwh
  • [Execute SQL]
    • This action exports the snippet to SQL
    • The target table is [util].[list_snippet]
      • The next page was supposed to be a nice view of [util].[list_snippet]
      • However, there is a bug!
      • Rest assured the snippet has been exported

In VS-Code or SSMS

Execute Upsert

  • EXEC cont.ADF_upsert ‘global’,1,’entity’,'<LABEL/mart ctrlr name>’

Check Data

  • EXEC get.global ‘<LABEL/mart ctrlr name>’

Leave a Comment