Converting Data Manager Dim to SSIS Dim

[dbahiker]

I'm trying to understand how Data Manager Lookups, templates, and etc.
I'm tasked with manually converting a Data Manager dimension task into a SSIS Package.
Can someone help me understand the workflow of data manager's Lookup, Dimension, Template, and Table? See attachement (Screenshot)
I am comfortable with SSIS and it's components but I'm lost in DM.

[MFGF]

I'm trying to understand how Data Manager Lookups, templates, and etc.
I'm tasked with manually converting a Data Manager dimension task into a SSIS Package.
Can someone help me understand the workflow of data manager's Lookup, Dimension, Template, and Table? See attachement (Screenshot)
I am comfortable with SSIS and it's components but I'm lost in DM.

Hi,

For dimension builds, the trick is in realising that the data is defined and transferred in two stages.

1. A hierarchy or lookup is defined in the Dimensions folder of the library. A hierarchy is a multi-level structure, and a lookup is a single-level structure. The hierarchy or lookup describe the structure of the data you are reading. A template (or templates, possibly, for a hierarchy) holds the name of each attribute used in the hierarchy or lookup. Let's call this an Input Template for ease of understanding. Either an SQL data source (a) or "template access" (b) can be used to retrieve the data. a) SQL data sources deliver a set of columns, and these columns are then mapped to items in a datastream (where further calcs can be added if desired). These datastream items are then mapped to the attributes in the hierarchy or level (which are named in the template). b) "Template access" does away with the data source(s), datastream(s) and mapping, and simply uses the names defined in the template to retrieve columns with the same name as the attributes.  What you then end up with is a hierarchy or lookup that collects data and assembles it in memory.

2. A Dimension Build is then used to deliver the data described by a hierarchy / lookup to a dimension table in your data warehouse. It's source is the hierarchy or lookup (which populates the data in memory) and it then delivers this data to a table. The names and behaviours of the columns in the target table are described in another template. Let's call this an Output Template for ease of understanding. The attributes described in the template directly control the columns written to the table. There is another mapping - this one maps attributes from the source hierarchy or lookup to the attribute names in the output template. You can also have attributes in the output template (and therefore in the table) that are not sourced from the original hierarchy or lookup, but are generated by the build process. Examples of this are surrogate keys, effective start dates, effective end dates, current flags etc. The behaviour property in the output template is used to control whether an attribute has one of these special generated values, or is simply mapped from the source.

The graphic you displayed shows a lookup feeding data to a dimension build, which delivers that data via an output template to a dimension table.

Cheers!

MF.