public Attribute(AttributeDef definition, string name) : base(name)
{
Definition = definition;
Levels = new AttributeLevelCollection(this);
Levels.Add(new AttributeLevel(AllLevelName));
Levels.Add(new AttributeLevel(Name));
}
private void FindPaths()
{
Paths.Clear();
// Scan measures to find fact tables
List <Table> facts = new List <Table>();
foreach (IDataElement elem in this)
{
if (elem is MeasureDef)
{
facts.Add(((MeasureDef)elem).Source.Table);
}
}
if (facts.Count > 0)
{
// Uses fact tables as root nodes
TableTree tree = new TableTree();
foreach (Table fact in facts)
{
TableTreeNode n = new TableTreeNode(fact);
tree.Nodes.Add(n);
Walkthrough(n);
}
// Scan attributes to find dimension tables
List <Table> dimTables = new List <Table>();
foreach (IDataElement elem in this)
{
if (elem is AttributeDef)
{
AttributeDef attr = (AttributeDef)elem;
if (!dimTables.Contains(attr.KeyColumns[0].Table))
{
dimTables.Add(attr.KeyColumns[0].Table);
}
}
}
foreach (Table table in dimTables)
{
TableTreeNode n = tree.FindNodeByTable(table);
if (n != null)
{
JoinPath jp = n.GetJoinPath();
if (jp != null)
{
Paths.Add(jp);
}
}
}
}
}
private string GetSql()
{
AttributeDef def = Attribute.Definition;
// Need to rewrite for multiple key columns
string key = def.KeyColumns.Expression;
string name = def.NameColumn == null ? key : string.Format("[{0}]", def.NameColumn.Name);
return(string.Format(
"select distinct {0} as [___aq_key], {1} as [___aq_name] from [{2}] order by [{3}]",
key,
name,
def.KeyColumns[0].Table.Name,
def.OrderBy == AttributeOrderBy.Key ? "___aq_key" : "___aq_name"
));
}
private string GetSql()
{
HierarchyDef def = Hierarchy.Definition;
List <Table> tables = new List <Table>();
def.Levels.ForEach(_ =>
{
Table t = _.Attribute.KeyColumns[0].Table;
if (!tables.Contains(t))
{
tables.Add(t);
}
});
List <Relationship> path = new List <Relationship>();
for (int i = tables.Count - 1; i > 0; i--)
{
Table table = tables[i];
Relationship rel = table.FindRelationshipByDestination(tables[i - 1]);
if (rel != null)
{
path.Add(rel);
}
}
for (int i = 0; i < tables.Count; i++)
{
tables[i].Alias = "t" + (i + 1).ToString();
}
StringBuilder sb = new StringBuilder();
sb.Append("select distinct ");
int index = 0;
List <string> names = new List <string>();
foreach (LevelDef level in def.Levels)
{
AttributeDef attr = level.Attribute;
if (index > 0)
{
sb.Append(",");
}
sb.AppendFormat("{0} as [___hq_key_{1}]", attr.KeyColumns.Expression, index);
if (attr.OrderBy == AttributeOrderBy.Name)
{
names.Add(string.Format("{0} as [___hq_name_{1}]", attr.NameColumn.Name, index));
}
index++;
}
names.ForEach(_ => sb.AppendFormat(",{0}", _));
sb.Append(" from ");
index = 0;
foreach (Table table in tables)
{
if (index > 0)
{
sb.Append(",");
}
sb.AppendFormat("[{0}] AS {1}", table.Name, table.Alias);
index++;
}
if (path.Count > 0)
{
sb.Append(" where ");
index = 0;
foreach (Relationship rel in path)
{
if (rel.SourceColumns.Count == rel.DestinationColumns.Count)
{
for (int i = 0; i < rel.SourceColumns.Count; i++)
{
if (index > 0)
{
sb.Append(" AND ");
}
sb.AppendFormat(
"{0} = {1} ",
rel.SourceColumns[i].QualifiedName,
rel.DestinationColumns[i].QualifiedName
);
index++;
}
}
else
{
throw new Exception("Inconsistent column counts in relationship.");
}
}
}
sb.Append(" order by ");
for (int i = 0; i < def.Levels.Count; i++)
{
AttributeDef attr = def.Levels[i].Attribute;
if (i > 0)
{
sb.Append(",");
}
if (attr.OrderBy == AttributeOrderBy.Key)
{
sb.AppendFormat("[___hq_key_{0}]", i);
}
else
{
sb.AppendFormat("[___hq_name_{0}]", i);
}
}
return(sb.ToString());
}
public LevelDef(string name, AttributeDef attribute) : base(name)
{
Attribute = attribute;
}