public static string FormatDax(string daxPattern, TableDataSchema schema)
{
//TODO: Parents at higher levels
var allReferencesFound = true;
var dax = _refParser.Replace(daxPattern.Replace("@TableName", "'" + schema.Name + "'"), match =>
{
var parent =
schema.RelatedTables.Where(r => r.RelationType == RelationType.Parent)
.Select(r => r.RelatedTable)
.FirstOrDefault();
if (parent != null)
{
var refField = parent.Fields.FirstOrDefault(f => f.ValueKind == match.Groups["ValueKind"].Value) ??
parent.Fields.FirstOrDefault(f => f.Name == match.Groups["ValueKind"].Value);
if (refField != null)
{
return string.Format("'{0}'[{1}]", parent.Name, refField.Name);
}
}
allReferencesFound = false;
return "";
});
return allReferencesFound ? dax : null;
}
public static string FormatDax(string daxPattern, TableDataSchema schema)
{
//TODO: Parents at higher levels
var allReferencesFound = true;
var dax = _refParser.Replace(daxPattern.Replace("@TableName", "'" + schema.Name + "'"), match =>
{
var parent =
schema.RelatedTables.Where(r => r.RelationType == RelationType.Parent)
.Select(r => r.RelatedTable)
.FirstOrDefault();
if (parent != null)
{
var refField = parent.Fields.FirstOrDefault(f => f.ValueKind == match.Groups["ValueKind"].Value) ??
parent.Fields.FirstOrDefault(f => f.Name == match.Groups["ValueKind"].Value);
if (refField != null)
{
return(string.Format("'{0}'[{1}]", parent.Name, refField.Name));
}
}
allReferencesFound = false;
return("");
});
return(allReferencesFound ? dax : null);
}
public CsvTableData(TableDataSchema schema, string path, string delimiter = "\t", CultureInfo numberCulture = null)
: base(schema)
{
Path = path;
Delimiter = delimiter;
NumberCulture = numberCulture ?? CultureInfo.CurrentCulture;
DateFormat = "yyyy-MM-dd";
DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
}
private WritableTableData CreateTableData(TableDataSchema schema, string path)
{
if (BinaryOutput)
{
return new BinaryTableData(schema, path.Replace(".txt", ".bin"));
}
return new CsvTableData(schema, path,
Delimiter, _numberCulture);
}
public static Tuple<TableDataSchema, PartitionField> GetPartitionField(TableDataSchema schema)
{
if (schema.IsCenterTable())
{
var field = schema.Fields.OfType<PartitionField>().FirstOrDefault();
return field != null ? new Tuple<TableDataSchema, PartitionField>(schema, field) : null;
}
var parent = schema.RelatedTables.FirstOrDefault(r => r.RelationType == RelationType.Parent);
return parent != null ? GetPartitionField(parent.RelatedTable) : null;
}
public static string GetUpdateCriteria(TableDataSchema table, Tuple<TableDataSchema, PartitionField> partitionField, bool stale, DateTime? date = null, DateTime? cutoff = null)
{
var field = string.Format("[{0}].[{1}]", partitionField.Item1.Name, partitionField.Item2.Name);
var refDate = GetPartitionDate(date ?? DateTime.UtcNow.Add(-partitionField.Item2.StaleTime), partitionField.Item2.StaleTime);
var criteria = new StringBuilder(field);
criteria.Append(stale ? ">=" : "<");
criteria.AppendFormat("'{0}'", refDate.ToString("o"));
if (cutoff.HasValue && !stale)
{
criteria.AppendFormat(" AND {0} >= '{1}'", field, cutoff.Value.ToString("o"));
}
return GetJoinToFactAncestor(table, criteria.ToString());
}
/// <summary>
/// Associates another table with this table in the way specified
/// </summary>
/// <param name="referencedTable">The table to reference</param>
/// <param name="fields">The foreign key fields</param>
/// <param name="referencedFields">The primary key fields</param>
/// <param name="dimensionTable">true if the referenced table is a dimension table (0..1). Otherwise, a parent/child relationship is defined (1..1)</param>
public void Associate(TableDataSchema referencedTable, IEnumerable <Field> fields, IEnumerable <Field> referencedFields, bool dimensionTable = false)
{
RelatedTables.Add(new TableDataRelation
{
Fields = fields.ToArray(),
RelationType = dimensionTable ? RelationType.Dimension : RelationType.Parent,
RelatedFields = referencedFields.ToArray(),
RelatedTable = referencedTable
});
referencedTable.RelatedTables.Add(new TableDataRelation
{
Fields = referencedFields.ToArray(),
RelationType = dimensionTable ? RelationType.DimensionReference : RelationType.Child,
RelatedFields = fields.ToArray(),
RelatedTable = this
});
}
public static void GetJoinToFactAncestor(TableDataSchema schema, string ancestorCriteria, StringBuilder sb)
{
var parent = schema.RelatedTables.FirstOrDefault(r => r.RelationType == RelationType.Parent);
sb.AppendLine();
if (parent == null)
{
sb.Append("WHERE ").Append(ancestorCriteria);
}
else
{
sb.AppendFormat("INNER JOIN [{0}] ON ", parent.RelatedTable.Name);
for (var i = 0; i < parent.Fields.Count; i++)
{
if (i > 0) sb.Append(" AND ");
sb.AppendFormat("[{0}].[{1}] = [{2}].[{3}]",
parent.RelatedTable.Name, parent.RelatedFields[i].Name,
schema.Name, parent.Fields[i].Name);
}
GetJoinToFactAncestor(parent.RelatedTable, ancestorCriteria, sb);
}
}
public static bool IsDimension(this TableDataSchema table)
{
return(table.RelatedTables.All(
r => r.RelationType == RelationType.DimensionReference));
}
public static bool IsKey(this TableDataSchema table, Field field)
{
return(table.Keys.Length == 0 ? field.FieldType == FieldType.Dimension : field.FieldType == FieldType.Key);
}
public static bool IsCenterTable(this TableDataSchema table)
{
return(table.RelatedTables.All(
r => r.RelationType == RelationType.Dimension || r.RelationType == RelationType.Child));
}
static string PostFixMeasureName(TableDataSchema table, string name)
{
var hasParent = table.RelatedTables.Any(r => r.RelationType == RelationType.Parent);
return hasParent ? name + " (" + table.Name + ")" : name;
}
static string GetFieldFriendlyName(TableDataSchema table, string fieldName)
{
return GetFieldName(table, fieldName);
}
static string GetFieldName(TableDataSchema table, string fieldName)
{
var field = table.Fields.FirstOrDefault(f => f.Name == fieldName);
if (field == null)
{
throw new KeyNotFoundException(string.Format("No field with name '{0}' in table '{1}", fieldName, table.Name));
}
return string.IsNullOrEmpty(field.FriendlyName) ? field.Name : field.FriendlyName;
}
string GetTransientPartitionName(TableDataSchema schema)
{
return schema.Name + "_Transient";
}
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);
}
}
}
/// <summary> /// Adds a table with the specified schema to this partition and returns a <see cref="ITableDataWriter"/> for writing data to it /// </summary> /// <param name="schema">The schema to add a table for</param> /// <returns></returns> public abstract ITableDataWriter CreateTableDataWriter(TableDataSchema schema);
void WriteCreateTable(TextWriter writer, TableDataSchema table, string name = null, bool asType = false)
{
name = name ?? Escape(table.Name);
var i = 0;
if (asType)
{
writer.Write("CREATE TYPE {0} AS TABLE (", name);
}
else
{
writer.Write("CREATE TABLE {0} (", name);
}
foreach (var field in table.Fields)
{
writer.WriteLine(i++ > 0 ? "," : "");
writer.Write(" [{0}] {1} {2}", field.Name,
GetColumnType(field.ValueType, field.FieldType == FieldType.Key ? 60 : (int?)null),
field.FieldType == FieldType.Key || (field.ValueType.IsValueType && Nullable.GetUnderlyingType(field.ValueType) == null) ? "NOT NULL" : "NULL");
}
if (table.Keys.Length > 0)
{
writer.WriteLine(i++ > 0 ? "," : "");
//IGNORE_DUP_KEY to ignore hash collisions.
writer.Write(" PRIMARY KEY CLUSTERED ({0}) WITH (IGNORE_DUP_KEY = ON)", string.Join(",", table.Keys.Select(pos => string.Format("[{0}]", pos.Value.Name))));
}
writer.WriteLine();
writer.WriteLine(")");
writer.WriteLine();
}
public BinaryTableData(TableDataSchema schema, string path)
: base(schema)
{
Path = path;
}
/// <summary>
/// Associates another table with this table in the way specified
/// </summary>
/// <param name="referencedTable">The table to reference</param>
/// <param name="fields">The foreign key fields</param>
/// <param name="referencedFields">The primary key fields</param>
/// <param name="dimensionTable">true if the referenced table is a dimension table (0..1). Otherwise, a parent/child relationship is defined (1..1)</param>
public void Associate(TableDataSchema referencedTable, IEnumerable<Field> fields, IEnumerable<Field> referencedFields, bool dimensionTable = false)
{
RelatedTables.Add(new TableDataRelation
{
Fields = fields.ToArray(),
RelationType = dimensionTable ? RelationType.Dimension : RelationType.Parent,
RelatedFields = referencedFields.ToArray(),
RelatedTable = referencedTable
});
referencedTable.RelatedTables.Add(new TableDataRelation
{
Fields = referencedFields.ToArray(),
RelationType = dimensionTable ? RelationType.DimensionReference : RelationType.Child,
RelatedFields = fields.ToArray(),
RelatedTable = this
});
}
public override ITableDataWriter CreateTableDataWriter(TableDataSchema table)
{
var csvTable = _owner.CreateTableData(table, Path.Combine(Directory, table.Name + ".txt"));
AddTableData(csvTable);
return csvTable;
}
public override ITableDataWriter CreateTableDataWriter(TableDataSchema schema)
{
var data = new BinaryTableData(schema, Path.Combine(Directory, schema.Name + ".bin"));
AddTableData(data);
return data;
}
/// <summary>
/// Returns true if the fields in this schema are equivalent to the fields in the other table (i.e. the two tables can contain the same data)
/// </summary>
/// <param name="other"></param>
/// <returns></returns>
public bool FieldsAreEqual(TableDataSchema other)
{
return Fields.SequenceEqual(other.Fields);
}
public MergedTableData(TableDataSchema schema, IEnumerable<TableData> sources = null)
: base(schema)
{
Sources = sources != null ? sources.ToList() : new List<TableData>();
}
/// <summary>
/// Returns true if the fields in this schema are equivalent to the fields in the other table (i.e. the two tables can contain the same data)
/// </summary>
/// <param name="other"></param>
/// <returns></returns>
public bool FieldsAreEqual(TableDataSchema other)
{
return(Fields.SequenceEqual(other.Fields));
}
protected WritableTableData(TableDataSchema schema) : base(schema)
{
}
public static string GetJoinToFactAncestor(TableDataSchema schema, string ancestorCriteria)
{
var sb = new StringBuilder();
GetJoinToFactAncestor(schema, ancestorCriteria, sb);
return sb.ToString();
}
