Exemple #1
0
        public TransformationGraph(IEnumerable <AstTransformationNode> transformations)
        {
            PermitDuplicateNodeItems       = false;
            ErrorOnDuplicateNodeAddAttempt = false;

            AstTransformationNode previousTransformation = null;

            foreach (var transformation in transformations)
            {
                var multipleIn = transformation as AstMultipleInTransformationNode;
                var singleIn   = transformation as AstSingleInTransformationNode;

                if (multipleIn != null)
                {
                    foreach (var inputPath in multipleIn.InputPaths)
                    {
                        AstDataflowOutputPathNode outputPath = inputPath.OutputPath;
                        AddEdge(outputPath.Transformation, multipleIn, outputPath.Name, outputPath, inputPath);
                    }
                }
                else if (singleIn != null && singleIn.InputPath != null)
                {
                    AstDataflowMappedInputPathNode inputPath  = singleIn.InputPath;
                    AstDataflowOutputPathNode      outputPath = inputPath.OutputPath;
                    AddEdge(outputPath.Transformation, singleIn, outputPath.Name, outputPath, inputPath);
                }
                else if (singleIn != null && previousTransformation != null)
                {
                    AstDataflowOutputPathNode outputPath = previousTransformation.PreferredOutputPath;
                    AddEdge(previousTransformation, singleIn, outputPath.Name, outputPath, null);
                }
                else
                {
                    AddNode(transformation);
                }

                previousTransformation = transformation;
            }
        }
        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);
        }
        private static void BuildInsertPath(AstTableNode targetTable, IFrameworkItem parentItem, List <AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdInsertPath)
        {
            var insertTransform = new AstDerivedColumnListNode(parentItem)
            {
                Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDerivedColumns")
            };

            insertTransform.InputPath = new AstDataflowMappedInputPathNode(insertTransform)
            {
                OutputPath = scdInsertPath
            };
            insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
            {
                Name              = "_scdFrom",
                Expression        = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
                Scale             = 7,
                DerivedColumnType = ColumnType.DateTime2,
                ReplaceExisting   = false
            });
            insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
            {
                Name              = "_scdTo",
                Expression        = "NULL(DT_DBTIMESTAMP2,7)",
                Scale             = 7,
                DerivedColumnType = ColumnType.DateTime2,
                ReplaceExisting   = false
            });
            workflowFragment.Add(insertTransform);

            var insertDestination = new AstDestinationNode(parentItem);

            insertDestination.Table      = targetTable;
            insertDestination.DisableScd = true;
            insertDestination.ValidateExternalMetadata = false;
            insertDestination.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDestination");
            workflowFragment.Add(insertDestination);
        }
        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 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);
            }
        }
        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);
        }