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.
}
}
public void Validate(IEnumerable <TableData> tables, IJobSpecification job)
{
var hasPartitionKey = false;
var staleTime = GetStaleTime(tables);
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
if (!string.IsNullOrEmpty(CreateDatabaseName))
{
if (Update)
{
conn.ChangeDatabase(CreateDatabaseName);
//Check lock. Release immediately upon success.
AcquireLock(conn, null);
ReleaseLock(conn, null);
ValidateSchema(conn, tables);
}
if (staleTime > TimeSpan.Zero)
{
hasPartitionKey = true;
if (Update && !Rebuild)
{
var lastCutoff =
new SqlCommand(@"SELECT TOP 1 LastCutoff FROM Sitecore.JobInfo", conn).ExecuteScalar();
_cutoff = DBNull.Value.Equals(lastCutoff) ? (DateTime?)null : ((DateTime)lastCutoff).SpecifyKind(DateTimeKind.Utc);
if (_cutoff.HasValue)
{
_cutoff = SqlUpdateUtil.GetPartitionDate(_cutoff.Value.Add(-staleTime), staleTime);
}
}
_nextCuttoff = DateTime.UtcNow;
}
}
}
if (!string.IsNullOrEmpty(SsasConnectionString))
{
var connectionStringBuilder = new SqlConnectionStringBuilder(ConnectionString);
if (!string.IsNullOrEmpty(CreateDatabaseName))
{
connectionStringBuilder.InitialCatalog = CreateDatabaseName;
}
else
{
throw new Exception("Database must be specified either in the connection string or as the Database parameter");
}
_ssasExporter = new SsasExporter(SsasConnectionString, SsasDbName,
"Provider=SQLOLEDB;" + connectionStringBuilder.ConnectionString);
_ssasExporter.Update = Update;
_ssasExporter.Validate(tables, job);
_ssasExporter.ReferenceDate = (_nextCuttoff ?? DateTime.UtcNow).Add(-staleTime);
if (Rebuild)
{
_ssasExporter.IncrementalUpdate = SqlClearOptions.None;
}
else
{
_ssasExporter.IncrementalUpdate = SqlClearOptions;
if (hasPartitionKey)
{
_ssasExporter.IncrementalUpdate |= SqlClearOptions.Facts;
}
}
}
else if (!string.IsNullOrEmpty(SsasDbName))
{
throw new Exception("A connection string for SSAS Tabular is needed");
}
}
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);
}
}
}