public void Process(string tempDirectory, IEnumerable <TableData> sourceTables, IJobSpecification job) { var tables = sourceTables.Select(t => t.Schema).ToArray(); using (var server = new Server()) { server.Connect(ConnectionString); var db = server.Databases.Find(DbName); if (!Update) { if (db != null) { db.Drop(); } CreateSchema(db, server, tables); db = server.Databases.Find(DbName); } //server.CaptureXml = true; <- Doesn't work with QueryBinding and errors from empty partitions marked as never processed. foreach (var table in sourceTables) { var processingType = !Update && IncrementalUpdate.HasFlag( table.Schema.IsDimension() ? SqlClearOptions.Dimensions : SqlClearOptions.Facts) ? ProcessType.ProcessAdd : ProcessType.ProcessFull; if (table.Schema.IsDimension()) { ProcessPartition(db, table.Name, table.Name, processingType); } else { var partition = SqlUpdateUtil.GetPartitionField(table.Schema); if (partition != null) { ProcessPartition(db, table.Name, GetTransientPartitionName(table.Schema), ProcessType.ProcessFull, string.Format("SELECT [{0}].* FROM [{0}] {1}", table.Name, SqlUpdateUtil.GetUpdateCriteria(table.Schema, partition, true, date: ReferenceDate))); ProcessPartition(db, table.Name, table.Name, processingType, string.Format("SELECT [{0}].* FROM [{0}] {1}", table.Name, SqlUpdateUtil.GetUpdateCriteria(table.Schema, partition, false, date: ReferenceDate, cutoff: CutOff))); } else { ProcessPartition(db, table.Name, table.Name, processingType); } } } //server.ExecuteCaptureLog(true, true); } }
private TimeSpan GetStaleTime(IEnumerable <TableData> tables) { var staleTime = TimeSpan.Zero; foreach (var table in tables) { if (table.Schema.IsCenterTable()) { var partitionField = SqlUpdateUtil.GetPartitionField(table.Schema); if (partitionField != null && partitionField.Item2.StaleTime > staleTime) { staleTime = partitionField.Item2.StaleTime; } } } return(staleTime); }
private void InsertOrUpdateData(TableData table, SqlConnection conn, SqlTransaction tran, string sourceTable = null) { var insert = false; if (Update) { var clear = SqlClearOptions.HasFlag(table.Schema.IsDimension() ? SqlClearOptions.Dimensions : SqlClearOptions.Facts); if (!clear && _cutoff.HasValue) { var pfield = SqlUpdateUtil.GetPartitionField(table.Schema); if (pfield != null) { var sql = string.Format("DELETE {0} FROM {0} {1}", Escape(table.Name), SqlUpdateUtil.GetUpdateCriteria(table.Schema, pfield, true, _cutoff)); new SqlCommand(sql, conn, tran) { CommandTimeout = Timeout }.ExecuteNonQuery(); insert = true; } } else { new SqlCommand(string.Format("DELETE FROM {0}", Escape(table.Name)), conn, tran).ExecuteNonQuery(); insert = true; } } SqlCommand cmd; //Merge new dimension values if (Update && !insert) { var sql = new StringBuilder(); sql.AppendFormat(@"MERGE [{0}] WITH (TABLOCK) AS Target USING {1} AS Source ON {2} ", table.Name, sourceTable ?? "@Data", //Join criteria string.Join(" AND ", (table.Schema.Keys.Length > 0 ? table.Schema.Keys : table.Schema.Dimensions).Select( field => string.Format("{0} = {1}", Escape("Target", field.Value.Name), Escape("Source", field.Value.Name))))); if (table.Schema.Fields.Any(f => !table.Schema.IsKey(f))) { sql.AppendFormat(@"WHEN Matched THEN UPDATE SET {0}", //Update fields string.Join(", ", table.Schema.Fields.Where(f => !table.Schema.IsKey(f)).Select(field => string.Format( field.FieldType == FieldType.Fact ? "{0} = [Target].{0} + {1}" : "{0} = {1}", // <- Consider this. What if dimensions have measures? Escape(field.Name), Escape("Source", field.Name))))); } sql.AppendFormat("WHEN NOT MATCHED THEN INSERT ({0}) VALUES ({1});", //Insert fields string.Join(", ", table.Schema.Fields.Select(field => Escape(field.Name))), string.Join(", ", table.Schema.Fields.Select(field => Escape("Source", field.Name))) ); cmd = new SqlCommand(sql.ToString(), conn, tran); } else { cmd = new SqlCommand( string.Format("INSERT {0} WITH (TABLOCK) SELECT * FROM {1}", Escape(table.Name), sourceTable ?? "@Data"), conn, tran); } cmd.CommandTimeout = Timeout; if (sourceTable == null) { var p = cmd.Parameters.AddWithValue("@Data", new SqlRecordAdapter(table)); p.SqlDbType = SqlDbType.Structured; p.TypeName = GetTableTypeName(table); } try { cmd.ExecuteNonQuery(); } catch (ArgumentException ex) { //Ignore. SqlCommand throws ArgumentException when table is empty. } }
private void CreateSchema(Database db, Server server, TableDataSchema[] tables) { using (db = AMO2Tabular.TabularDatabaseAdd(server, DbName, SourceConnectionString, "Sql Server")) { var i = 0; foreach (var table in tables) { if (i++ == 0) { AMO2Tabular.TableAddFirstTable(db, "Model", table.Name, table.Name); } else { AMO2Tabular.TableAdd(db, table.Name, table.Name); } foreach (var field in table.Fields) { if (!string.IsNullOrEmpty(field.FriendlyName)) { AMO2Tabular.ColumnAlterColumnName(db, table.Name, field.Name, field.FriendlyName, false); } if (field is PartitionField) { AMO2Tabular.ColumnDrop(db, table.Name, field.Name, false); } else if (field.FieldType == FieldType.Fact) { var measureName = PostFixMeasureName(table, string.Format("Total {0}", field.Name)); AMO2Tabular.MeasureAdd(db, table.Name, measureName, "SUM([" + GetFieldName(table, field.Name) + "])", updateInstance: false); var isInteger = field.ValueType == typeof(int) || field.ValueType == typeof(long); SetMeasureFormat(db, measureName, isInteger ? CalculatedFieldFormat.Integer : CalculatedFieldFormat.Decimal); } else if (!string.IsNullOrEmpty(field.SortBy)) { AMO2Tabular.ColumnAlterSortByColumnName(db, table.Name, GetFieldFriendlyName(table, field.Name), GetFieldFriendlyName(table, field.SortBy), updateInstance: false); } else if (field.Hide) { AMO2Tabular.ColumnAlterVisibility(db, table.Name, GetFieldName(table, field.Name), false, false); } } if (SqlUpdateUtil.GetPartitionField(table) != null) { AMO2Tabular.PartitionAdd(db, table.Name, GetTransientPartitionName(table), string.Format("SELECT * FROM [{0}] WHERE 1=0", table.Name), false); } if (table.TableType == "Date") { //AMO2Tabular doesn't make the field the time dimension's key. This code does that. var dateField = table.Fields.First(f => f.ValueType == typeof(DateTime) && !f.Hide); var dim = db.Dimensions.GetByName(table.Name); dim.Type = DimensionType.Time; var attr = db.Dimensions.GetByName(table.Name).Attributes.GetByName(GetFieldName(table, dateField.Name)); attr.Usage = AttributeUsage.Key; attr.FormatString = "General Date"; var rowNumber = dim.Attributes.Cast <DimensionAttribute>().First(a => a.Type == AttributeType.RowNumber); rowNumber.Usage = AttributeUsage.Regular; rowNumber.AttributeRelationships.Remove(attr.ID); var rel = attr.AttributeRelationships.Add(rowNumber.ID); rel.Cardinality = Cardinality.One; attr.KeyColumns[0].NullProcessing = NullProcessing.Error; attr.KeyUniquenessGuarantee = true; ((RegularMeasureGroupDimension)db.Cubes[0].MeasureGroups[dim.ID].Dimensions[dim.ID]).Attributes [attr.ID].KeyColumns[0].NullProcessing = NullProcessing.Error; //attr.AttributeRelationships } } foreach (var table in tables) { foreach (var relation in table.RelatedTables) { if (relation.RelationType == RelationType.Dimension || relation.RelationType == RelationType.Parent) { AMO2Tabular.RelationshipAdd(db, relation.RelatedTable.Name, relation.RelatedFields.First().Name, table.Name, relation.Fields.First().Name, updateInstance: false); } } foreach (var calculatedField in table.CalculatedFields) { var dax = CalculatedField.FormatDax(calculatedField.DaxPattern, table); if (!string.IsNullOrEmpty(dax)) { var measureName = PostFixMeasureName(table, calculatedField.Name); AMO2Tabular.MeasureAdd(db, table.Name, measureName, dax, updateInstance: false); if (!string.IsNullOrEmpty(calculatedField.FormatString)) { SetMeasureFormat(db, measureName, calculatedField.FormatString); } } if (!string.IsNullOrEmpty(calculatedField.ChildDaxPattern)) { //TODO: Deeper nested tables foreach (var rel in table.RelatedTables.Where(r => r.RelationType == RelationType.Child)) { var childDax = CalculatedField.FormatDax(calculatedField.ChildDaxPattern, rel.RelatedTable); if (!string.IsNullOrEmpty(childDax)) { var measureName = PostFixMeasureName(rel.RelatedTable, calculatedField.Name); AMO2Tabular.MeasureAdd(db, rel.RelatedTable.Name, measureName, childDax, updateInstance: false); if (!string.IsNullOrEmpty(calculatedField.FormatString)) { SetMeasureFormat(db, measureName, calculatedField.FormatString); } } } } } db.Update(UpdateOptions.ExpandFull, UpdateMode.Default); } } }