public static void ProcessSlowlyChangingDimensionTransformations(SymbolTable symbolTable) ////HashSet<AstEtlRootNode> astEtlRootNodes, HashSet<AstTaskNode> astTaskNodes)
{
var snapshotSymbolTable = new List <IReferenceableItem>(symbolTable);
foreach (var astNamedNode in snapshotSymbolTable)
{
if (astNamedNode.FirstThisOrParent <ITemplate>() == null)
{
var destination = astNamedNode as AstDestinationNode;
if (destination != null)
{
if (destination.Table.HasScdColumns && !destination.DisableScd)
{
Utility.Replace(destination, CreateScdWorkflowFragment(destination.Table, destination.ParentItem, destination.InputPath == null ? null : destination.InputPath.OutputPath));
}
}
var merge = astNamedNode as AstMergeTaskNode;
if (merge != null)
{
var sourceTable = merge.TargetConstraint.ParentItem as AstTableNode;
// TODO: Must we do anything special for the UpdateTargetTable attribute?
if (sourceTable != null && sourceTable.HasScdColumns && !merge.DisableScd)
{
var targetTable = merge.TargetConstraint.ParentItem as AstTableNode;
if (targetTable == null)
{
continue;
}
var scdMergeEtl = new AstEtlRootNode(merge.ParentItem)
{
Name = Utility.NameCleanerAndUniqifier(merge.Name + "_scdEtl")
};
// TODO: Do we need to limit query to non-computed columns?
var scdMergeSource = new AstQuerySourceNode(scdMergeEtl)
{
Connection = sourceTable.Connection,
Name = Utility.NameCleanerAndUniqifier(merge.Name + "_scdEtlSource"),
Query = new AstVariableParameterMappingQueryNode(null)
{
Body = TableLowerer.EmitSelectAllStatement(sourceTable)
}
};
scdMergeSource.Query.ParentItem = scdMergeSource;
scdMergeEtl.Transformations.Add(scdMergeSource);
foreach (var transformation in CreateScdWorkflowFragment(targetTable, scdMergeEtl, scdMergeSource.OutputPath))
{
scdMergeEtl.Transformations.Add(transformation);
}
}
}
}
}
}
private static void BuildChangePath(AstTableNode targetTable, IFrameworkItem parentItem, List <AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdChangePath)
{
// Change Path should only be built if we actually have a column that takes a Type-1 Update
bool doUpdate = false;
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
{
doUpdate = true;
break;
}
}
if (doUpdate)
{
var changeCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_ChangeCommand"),
Connection = targetTable.Connection
};
changeCommand.Query = new AstTransformationMappedQueryNode(changeCommand);
int changeIndex = 0;
var changeSetColumnValueMappings = new List <TableColumnValueMapping>();
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
{
changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand)
{
SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++)
});
changeSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
}
}
var changeWhereColumnValueMappings = new List <TableColumnValueMapping>();
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand)
{
SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++)
});
changeWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
changeWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
changeCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, changeSetColumnValueMappings, changeWhereColumnValueMappings);
changeCommand.ValidateExternalMetadata = false;
changeCommand.InputPath = new AstDataflowMappedInputPathNode(changeCommand)
{
OutputPath = scdChangePath
};
workflowFragment.Add(changeCommand);
}
}
// TODO: We've made the call to just use the staging node as the lowered container rather than creating a new one and copying everything over
public static void ProcessContainers(SymbolTable symbolTable)
{
var snapshotSymbolTable = new List <IReferenceableItem>(symbolTable);
foreach (var astNamedNode in snapshotSymbolTable)
{
var stagingNode = astNamedNode as AstStagingContainerTaskNode;
if (stagingNode != null && astNamedNode.FirstThisOrParent <ITemplate>() == null)
{
var stagingCreateContainer = new AstContainerTaskNode(stagingNode)
{
Name = String.Format(CultureInfo.InvariantCulture, Properties.Resources.CreateStaging, stagingNode.Name),
Log = false,
};
var stagingDropContainer = new AstContainerTaskNode(stagingNode)
{
Name = String.Format(CultureInfo.InvariantCulture, Properties.Resources.DropStaging, stagingNode.Name),
Log = false,
};
stagingNode.Tasks.Insert(0, stagingCreateContainer);
stagingNode.Tasks.Add(stagingDropContainer);
foreach (var baseTable in stagingNode.Tables)
{
var table = baseTable as AstTableNode;
if (table != null)
{
TableLowerer.LowerTable(
stagingCreateContainer,
table,
String.Format(CultureInfo.InvariantCulture, Properties.Resources.CreateStagingTable, table.Name),
stagingNode.ExecuteDuringDesignTime);
var dropStagingTemplate = new TemplatePlatformEmitter("DropStagingTable", table.SchemaQualifiedName);
var dropTableExecuteSqlTask = new AstExecuteSqlTaskNode(stagingNode)
{
Name = StringManipulation.NameCleanerAndUniqifier(String.Format(CultureInfo.InvariantCulture, Properties.Resources.DropStagingTable, table.Name)),
Connection = table.Connection,
ExecuteDuringDesignTime = stagingNode.ExecuteDuringDesignTime,
};
dropTableExecuteSqlTask.Query = new AstExecuteSqlQueryNode(dropTableExecuteSqlTask)
{
QueryType = QueryType.Standard, Body = dropStagingTemplate.Emit()
};
stagingDropContainer.Tasks.Add(dropTableExecuteSqlTask);
}
else
{
throw new System.NotSupportedException("AstLowering - StagingContainer - a Table Template node was found when lowering staging containers and I don't know what to do with it.");
}
}
}
}
}
private static AstDataflowOutputPathNode BuildHistoricalSubpath(AstTableNode targetTable, IFrameworkItem parentItem, List <AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdHistoricalPath)
{
var historicalTransform = new AstDerivedColumnListNode(parentItem);
historicalTransform.InputPath = new AstDataflowMappedInputPathNode(historicalTransform)
{
OutputPath = scdHistoricalPath
};
historicalTransform.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalDerivedColumns");
historicalTransform.Columns.Add(new AstDerivedColumnNode(historicalTransform)
{
Name = "_scdTo",
Expression = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
Scale = 7,
DerivedColumnType = ColumnType.DateTime2,
ReplaceExisting = false
});
workflowFragment.Add(historicalTransform);
var historicalCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalCommand"),
Connection = targetTable.Connection
};
historicalCommand.Query = new AstTransformationMappedQueryNode(historicalCommand);
var historicalSetColumnValueMappings = new List <TableColumnValueMapping>();
var historicalWhereColumnValueMappings = new List <TableColumnValueMapping>();
historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand)
{
SourceName = "_scdTo", TargetName = "Param_0"
});
historicalSetColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "?", MappingOperator.Assign));
historicalWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
int historicalIndex = 1;
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand)
{
SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", historicalIndex++)
});
historicalWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
historicalCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, historicalSetColumnValueMappings, historicalWhereColumnValueMappings);
historicalCommand.ValidateExternalMetadata = false;
workflowFragment.Add(historicalCommand);
return(historicalCommand.OutputPath);
}
private static void BuildLateArrivingPath(AstTableNode targetTable, IFrameworkItem parentItem, List <AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdLateArrivingPath)
{
var inferredCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InferredCommand"),
Connection = targetTable.Connection
};
inferredCommand.Query = new AstTransformationMappedQueryNode(inferredCommand);
int inferredIndex = 0;
var inferredSetColumnValueMappings = new List <TableColumnValueMapping>();
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && !column.IsAutoGenerated)
{
inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand)
{
SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++)
});
inferredSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
}
}
if (targetTable.LateArriving)
{
inferredSetColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.Assign));
}
var inferredWhereColumnValueMappings = new List <TableColumnValueMapping>();
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand)
{
SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++)
});
inferredWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
if (targetTable.LateArriving)
{
inferredWhereColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.CompareIsNot));
}
inferredCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, inferredSetColumnValueMappings, inferredWhereColumnValueMappings);
inferredCommand.ValidateExternalMetadata = false;
inferredCommand.InputPath = new AstDataflowMappedInputPathNode(inferredCommand)
{
OutputPath = scdLateArrivingPath
};
workflowFragment.Add(inferredCommand);
}
private static AstOleDBCommandNode CreateInsertNode(AstLateArrivingLookupNode lookup, AstLookupNode codegenLookup)
{
var insertPlaceholder = new AstOleDBCommandNode(lookup.ParentItem)
{
Name = String.Format(CultureInfo.InvariantCulture, "__LALookupPlaceholderInsert_{0}", lookup.Name),
Connection = lookup.Table.Connection,
};
insertPlaceholder.Query = new AstTransformationMappedQueryNode(insertPlaceholder)
{
Body = TableLowerer.EmitInsertDefaultRowStatement(lookup.Table)
};
insertPlaceholder.InputPath = new AstDataflowMappedInputPathNode(insertPlaceholder)
{
OutputPath = codegenLookup.NoMatchPath
};
return(insertPlaceholder);
}
private static AstLookupNode CreateLookupNode(AstLateArrivingLookupNode lookup)
{
var codegenLookup = new AstLookupNode(lookup.ParentItem)
{
Name = String.Format(CultureInfo.InvariantCulture, "__LALookupEntryPoint_{0}", lookup.Name),
Connection = lookup.Table.Connection
};
var requiredColumns = new List <string>();
foreach (var input in lookup.Inputs)
{
if (!requiredColumns.Contains(input.RemoteColumnName))
{
requiredColumns.Add(input.RemoteColumnName);
}
codegenLookup.Inputs.Add(input);
}
foreach (var output in lookup.Outputs)
{
if (!requiredColumns.Contains(output.RemoteColumnName))
{
requiredColumns.Add(output.RemoteColumnName);
}
codegenLookup.Outputs.Add(output);
}
codegenLookup.Query = new AstQueryNode(codegenLookup)
{
Body = TableLowerer.EmitSelectAllStatement(lookup.Table, requiredColumns)
};
if (lookup.InputPath != null)
{
codegenLookup.InputPath = new AstDataflowMappedInputPathNode(codegenLookup)
{
OutputPath = lookup.InputPath.OutputPath
};
}
return(codegenLookup);
}
private static List <AstTransformationNode> CreateScdWorkflowFragment(AstTableNode targetTable, IFrameworkItem parentItem, AstDataflowOutputPathNode outputPath)
{
var workflowFragment = new List <AstTransformationNode>();
AstTableColumnBaseNode lateArrivingStatusColumn = targetTable.Columns.FirstOrDefault(item => item.Name == "_IsLate");
var scd = new AstSlowlyChangingDimensionNode(parentItem);
scd.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_scd");
scd.Connection = targetTable.Connection;
scd.CurrentRowWhere = "[_scdFrom] IS NOT NULL AND [_scdFrom] IS NULL";
scd.EnableInferredMember = targetTable.LateArriving;
scd.FailOnFixedAttributeChange = true;
scd.FailOnLookupFailure = false;
scd.IncomingRowChangeType = 1;
scd.InferredMemberIndicator = lateArrivingStatusColumn;
// TODO:
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && !column.IsAutoGenerated)
{
ScdColumnMappingType mappingType;
switch (column.ScdType)
{
case ScdType.Error:
mappingType = ScdColumnMappingType.FixedAttribute;
break;
case ScdType.Historical:
mappingType = ScdColumnMappingType.HistoricalAttribute;
break;
case ScdType.Key:
mappingType = ScdColumnMappingType.Key;
break;
case ScdType.Other:
mappingType = ScdColumnMappingType.Other;
break;
case ScdType.Update:
mappingType = ScdColumnMappingType.ChangingAttribute;
break;
default:
mappingType = ScdColumnMappingType.Other;
break;
}
scd.Mappings.Add(new AstScdTypeColumnMappingNode(scd)
{
MappingType = mappingType, QueryColumnName = column.Name
});
}
}
scd.Query = TableLowerer.EmitSelectAllStatement(targetTable);
if (outputPath != null)
{
scd.InputPath = new AstDataflowMappedInputPathNode(scd)
{
OutputPath = outputPath
};
}
workflowFragment.Add(scd);
// Late Arriving Path
if (targetTable.LateArriving)
{
BuildLateArrivingPath(targetTable, parentItem, workflowFragment, scd.InferredMemberPath);
}
// Change Path
BuildChangePath(targetTable, parentItem, workflowFragment, scd.ChangingAttributePath);
// Historical Path
var historicalOutput = BuildHistoricalSubpath(targetTable, parentItem, workflowFragment, scd.HistoricalAttributePath);
// Union Historical and New Paths
var insertUnionAll = new AstUnionAllNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertUnionAll")
};
insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll)
{
OutputPath = scd.NewPath
});
insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll)
{
OutputPath = historicalOutput
});
workflowFragment.Add(insertUnionAll);
// Insert Path
BuildInsertPath(targetTable, parentItem, workflowFragment, insertUnionAll.OutputPath);
return(workflowFragment);
}