/// <summary>
/// Returns the columns in a given Table.
/// </summary>
/// <param Name="Table">The Name of the Table.</param>
/// <returns>A List of strings.</returns>
/// <author>Jannik Arndt</author>
public FactTable GetMetaData(string factTableName)
{
FactTable table = new FactTable(factTableName, GetColumnNamesOfTable("CASE"), GetColumnNamesOfTable("EVENT"));
table.AddDimension(new Dimension("--no selection--", isEmptyDimension: true));
table.AddDimensions(GetDimensionsOf(factTableName));
return(table);
}
public void ExtractFactTable(bool inc)
{
var Data = _context.Data.ToList();
if (inc == true)
{
if (Data.Count > _context.FactTable.ToList().Count)
{
for (int i = _context.FactTable.ToList().Count; i < Data.Count; i++)
{
var fact = Data[i];
var sensor = _context.Sensor.FirstOrDefault(s => s.sensorID == fact.sensorID);
if (sensor != null)
{
var Fact = new FactTable
{
D_ID = 0,
R_ID = 0,
S_ID = 0,
U_ID = 0,
Servosetting = sensor.servoSetting,
Humidity = fact.humidity,
CO2 = fact.CO2,
Temperature = fact.temperature
};
_context.FactTable.Add(Fact);
}
}
}
}
else
{
foreach (var fact in Data)
{
var sensor = _context.Sensor.FirstOrDefault(s => s.sensorID == fact.sensorID);
if (sensor != null)
{
var Fact = new FactTable
{
D_ID = 0,
R_ID = 0,
S_ID = 0,
U_ID = 0,
Servosetting = sensor.servoSetting,
Humidity = fact.humidity,
CO2 = fact.CO2,
Temperature = fact.temperature
};
_context.FactTable.Add(Fact);
}
}
}
_context.SaveChanges();
}
public StarModel Refine(StarModel starModel)
{
FactTable fact = starModel.FactTable;
// Create new dimension
List <StarColumn> stringColumns = new List <StarColumn>();
if (fact.Columns.Count(x => x.DataType.IsString()) == 0)
{
return(starModel);
}
var surrogateKey = new StarColumn(1, "SurKey", new DataType(OleDbType.Integer), StarColumnType.Key | StarColumnType.SurrogateKey);
stringColumns.Add(surrogateKey);
var junkColumns = fact.Columns.Where(x => x.DataType.IsString()).Select(c => new JunkColumn(c));
stringColumns.AddRange(junkColumns);
JunkDimension dimension = new JunkDimension($"Junk_{fact.Name}", stringColumns, fact.TableReference);
surrogateKey.TableRef = dimension;
dimension.Constraints.PrimaryKey.Columns.Add(surrogateKey);
dimension.Constraints.Uniques.Add(new Unique(stringColumns));
// Fact relation to new dimension
var foreignColumn = new StarColumn(0, $"{dimension.Name}_Key", new DataType(OleDbType.Integer), StarColumnType.Key);
foreignColumn.TableRef = fact;
fact.Columns.Add(foreignColumn);
// Remove string columns from fact table
starModel.FactTable.Columns.RemoveAll(c => c.DataType.IsString());
StarRelation r = new StarRelation(dimension, starModel.FactTable, new List <StarColumn>()
{
surrogateKey
}, new List <StarColumn>()
{
foreignColumn
}, Cardinality.ManyToOne);
fact.Relations.Add(r);
dimension.Relations.Add(r);
starModel.Dimensions.Add(dimension);
return(starModel);
}
private void FormatFactTable(FactTable fact)
{
fact.Name = FormatName(fact.Name,
_config.TableStripUnderscore,
_config.TableNameCasing,
_config.FactTableNameStructure);
foreach (var column in fact.Columns)
{
column.Name = FormatName(column.Name,
_config.ColumnStripUnderscore,
_config.ColumnNameCasing,
_config.ColumnNameStructure);
}
}
public override StarModel TranslateModel(StarModel starModel)
{
//All fact tables across all star models
FactTable factTable = starModel.FactTable;
List <Dimension> temporaryDimensions = starModel.Dimensions;
// Case 1, if dimensions have been found, simply flatten them
// Case 2, if dimensions have *not* been found, find all connected tables to the fact table and flatten them.
// Case 2
if (temporaryDimensions.Count == 0)
{
List <Table> tables =
factTable.TableReference.Relations
.Where(x => factTable.TableReference == x.LinkTable && x.Cardinality == Cardinality.ManyToOne &&
x.AnchorTable != factTable.TableReference)
.Select(x => x.AnchorTable)
.ToList();
temporaryDimensions = tables.Select(table => new Dimension(table)).ToList();
}
// Case 1
foreach (var dim in temporaryDimensions)
{
var tableRef = dim.TableReference;
var newColumns = Flatten(tableRef, new List <Table>()
{
tableRef
});
dim.Columns.AddRange(newColumns);
dim.Columns.ForEach(x => x.TableRef = dim);
}
factTable.Relations.Clear();
// Add relations from fact table to all dimensions
foreach (var dimension in temporaryDimensions)
{
var starRelation = new StarRelation(dimension, factTable, Cardinality.ManyToOne);
factTable.Relations.Add(starRelation);
dimension.Relations.Add(starRelation);
}
starModel.Dimensions.AddRange(temporaryDimensions);
return(starModel);
}
public List <StarModel> TranslateModel(CommonModel commonModel)
{
List <Table> tables = commonModel.Tables;
List <StarModel> factConfidence = new List <StarModel>();
if (tables.Count(x => x.RowCount != 0) == 0)
{
Logger.Log(Logger.Level.Debug, $"Heuristic '{Name}' skipped since no data was found in any table.");
return(factConfidence);
}
//Find average table row count across all tables of all databases
double avgRowCount = tables.Where(t => t.RowCount != 0).Average(x => x.RowCount);
if (tables.Count(x => x.RowCount == -1) != 0)
{
return(factConfidence);
}
foreach (Table table in tables)
{
//TODO: andet måde?
float confidence = 0.0f;
//Find how many times larger than the average
if (table.RowCount * 10 > avgRowCount)
{
confidence = 1.0f;
}
else if (table.RowCount * 5 > avgRowCount)
{
confidence = 0.5f;
}
else if (table.RowCount * 2 > avgRowCount)
{
confidence = 0.2f;
}
//Convert table -> fact table
FactTable factTable = new FactTable(table, confidence);
factConfidence.Add(new StarModel(factTable, commonModel));
}
return(factConfidence);
}
public StarModel Refine(StarModel starModel)
{
// Order columns
FactTable fact = starModel.FactTable;
fact.Columns = fact.Columns.OrderByDescending(x => (short)x.ColumnType & 3).ThenBy(x => x.Name).ToList();
foreach (Dimension dim in starModel.Dimensions)
{
if (dim is TimeDimension || dim is DateDimension)
{
continue;
}
dim.Columns = dim.Columns.OrderByDescending(x => (short)x.ColumnType & 3).ThenBy(x => x.Name).ToList();
}
return(starModel);
}
/// <summary>
/// Generate INSERT statement for fact table values
/// </summary>
/// <param name="factTable">Fact table to create statement for</param>
/// <returns>INSERT statement</returns>
private string GenerateInsert(FactTable factTable, List <Dimension> dimensions)
{
StringBuilder builder = new StringBuilder();
// Begin creation of insert statement
builder.AppendLine($"INSERT INTO {factTable.GetFullName()} ");
// Create a list of all columns used in projection. Starting with foreign keys and then measures
List <string> columnNames = new List <string>();
columnNames.AddRange(factTable.Relations.Where(r => Equals(r.LinkTable, factTable))
.SelectMany(r => r.LinkColumns.Select(c => c.GetName())));
columnNames.AddRange(factTable.Columns.Where(c => c.ColumnType.HasFlag(StarColumnType.NumericMeasure) || c.ColumnType.HasFlag(StarColumnType.DescriptiveMeasure)).Select(c => c.GetNameWithAlias()));
// Add project list to INSERT statement
builder.Append($"({string.Join(", ", columnNames)}) ");
// List of columns that are returned from SELECT statement. These include aliases for the column names.
List <string> selectColumns = new List <string>();
selectColumns.AddRange(factTable.Relations.Where(r => Equals(r.LinkTable, factTable))
.SelectMany(r => r.AnchorColumns.Select(c => c.GetNameAsAlias(r.AnchorTable))));
selectColumns.AddRange(factTable.Columns.Where(c => c.ColumnType.HasFlag(StarColumnType.NumericMeasure) || c.ColumnType.HasFlag(StarColumnType.DescriptiveMeasure)).Select(c => c.ColumnRef.GetNameWithAlias()));
// Generate the SELECT statement and append to query
var generateSelect = GenerateSelect(factTable.GetOriginalTables(), selectColumns);
builder.AppendLine(generateSelect);
// Add JOIN to SELECT statement
var generateJoin = GenerateJoin(factTable, dimensions);
builder.AppendLine(generateJoin);
return(builder.ToString());
}
public List <StarModel> TranslateModel(CommonModel commonModel)
{
List <Table> tables = commonModel.Tables;
// TODO: Change calculation to avoid keys and to different ratio (if Søren decides if it is okay)
//Find the ratio of numeric/not-numeric columns for each table
Dictionary <Table, double> ratios = new Dictionary <Table, double>();
foreach (Table table in tables)
{
int numericColumns = table.Columns.Select(c => c.DataType.IsNumeric()).Count();
//All other columns
int nonNumericColumns = table.Columns.Count - numericColumns;
double ratio;
if (nonNumericColumns != 0)
{
ratio = numericColumns / nonNumericColumns;
}
else
{
//TODO: Rigtige måde at gøre dette på? 0 non-numeric -> bare sig antallet af ratio = antallet af numeric cols.
ratio = numericColumns;
}
ratios.Add(table, ratio);
}
//get confidence
List <StarModel> factConfidence = new List <StarModel>();
foreach (KeyValuePair <Table, double> pair in ratios)
{
//Find confidence?
//TODO: better way to detemine confidence?
float confidence = 0.0f;
if (pair.Value >= 5)
{
confidence = 1.0f;
}
else if (pair.Value >= 3)
{
confidence = 0.5f;
}
else if (pair.Value >= 1)
{
confidence = 0.2f;
}
//Convert table -> fact table
FactTable factTable = new FactTable(pair.Key, confidence);
//Add new star model
factConfidence.Add(new StarModel(factTable, commonModel));
}
return(factConfidence);
//TODO: få confidence ind i det
//TODO: better way to determine threshold?
//If ratio >= 5 -> the table is a fact table
//List<FactTable> chosenTables = ratios.Where(r => r.Value >= 5.0)
// .Select(dic => new FactTable() {
// Name = dic.Key.Name,
// TableRef = dic.Key,
// Columns = dic.Key.Columns.Select( col => new StarColumn(col))
// .ToList()})
// .ToList();
//return chosenTables;
}
public FactNode(FactTable table) : base(table)
{
}
/// <summary>
/// Returns the columns in a given Table.
/// </summary>
/// <param Name="Table">The Name of the Table.</param>
/// <returns>A List of strings.</returns>
/// <author>Jannik Arndt</author>
public FactTable GetMetaData(string factTableName)
{
FactTable table = new FactTable(factTableName, GetColumnNamesOfTable("CASE"), GetColumnNamesOfTable("EVENT"));
table.AddDimension(new Dimension("--no selection--", isEmptyDimension: true));
table.AddDimensions(GetDimensionsOf(factTableName));
return table;
}
