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(); }