private List <Measure> QueryMeasureValues()
{
List <Measure> list = new List <Measure>();
Measure[] measures = _cmd.Measures;
foreach (Measure m in measures)
{
MeasureQuery mq = null;
Aggregation root = _pool.GetRoot();
if (root == null)
{
root = new Aggregation(new Attribute[] { });
_pool.Aggregations.Add(root);
}
MeasureValues mvs = root[m];
if (mvs == null || mvs.Values.Count == 0)
{
if (mq == null)
{
mq = new MeasureQuery(Connection);
}
mq.Measure = m.Definition;
if (mvs == null)
{
mvs = new MeasureValues(m);
root.Values.Add(mvs);
}
mvs.Values.Add(mq.QueryValue());
}
list.Add(m);
}
return(list);
}
public CellSet Execute(QueryCommand cmd)
{
Joiner joiner = new Joiner(_pool, _connection, cmd);
joiner.Run();
if (_pool.Aggregations.Count == 0)
{
return(new CellSet());
}
CellSet cs = new CellSet();
DateTime t = DateTime.Now;
QuerySets.JoinedMembers columns = cmd.Columns.JoinMembers(_pool.FindAggregation(cmd.Columns.Attributes));
QuerySets.JoinedMembers rows = cmd.Rows.Count > 0 ?
cmd.Rows.JoinMembers(_pool.FindAggregation(cmd.Rows.Attributes)) :
new QuerySets.JoinedMembers();
QuerySets.JoinedMembers filters;
bool reverseFilter = cmd.Filters.ShouldReverse;
if (!reverseFilter)
{
filters = cmd.Filters.Count > 0 ?
cmd.Filters.JoinMembers(_pool.FindAggregation(cmd.Filters.Attributes), true) :
new QuerySets.JoinedMembers();
}
else
{
reverseFilter = true;
QuerySets reversed = cmd.Filters.ReverseMembers();
filters = reversed.Count > 0 ?
reversed.JoinMembers(_pool.FindAggregation(reversed.Attributes), true) :
new QuerySets.JoinedMembers();
}
// AggregationPointer is for total lookup
AggregationPointer root = null;
Aggregation defaultAggregation = _pool.FindAggregation(GetAttributes());
if (defaultAggregation != null)
{
List <Position> cps = cs.Axes[0].Positions;
List <Position> rps = cs.Axes[1].Positions;
for (int i = 0; i < rows.Count; i++)
{
rps.Add(new Position(rows[i], i));
}
for (int i = 0; i < columns.Count; i++)
{
cps.Add(new Position(columns[i], i));
}
if (filters.Count > 0)
{
if (reverseFilter)
{
// Query values without filters first
Attribute[] crIndex = cmd.Columns.Attributes.Concat(cmd.Rows.Attributes).ToArray();
DirectQuery(_pool.FindAggregation(crIndex));
// Then query values with filter
if (root != null)
{
root.Clear();
}
root = null;
IEnumerable <Cell> filtered = IncrementalQuery(defaultAggregation);
// Substract
int i = 0;
foreach (Cell cell in filtered)
{
if (cell.Value != null)
{
if (cell.DataType == typeof(int))
{
cs.Cells[i].Value = (int)cs.Cells[i].Value - (int)cell.Value;
}
else
{
cs.Cells[i].Value = (decimal)cs.Cells[i].Value - (decimal)cell.Value;
}
}
i++;
}
}
else
{
IncrementalQuery(defaultAggregation, cs.Cells);
}
}
else
{
DirectQuery(defaultAggregation);
}
}
cs.QueryTime = (DateTime.Now - t).TotalMilliseconds;
return(cs);
Attribute[] GetAttributes()
{
List <Attribute> attrs = new List <Attribute>();
attrs.AddRange(filters.Attributes);
attrs.AddRange(columns.Attributes);
attrs.AddRange(rows.Attributes);
return(attrs.ToArray());
}
IEnumerable <Cell> IncrementalQuery(Aggregation agg, List <Cell> cells = null)
{
List <CellAggregator> cas = new List <CellAggregator>();
if (rows.Count == 0)
{
for (int i = 0; i < filters.Count; i++)
{
int pointer = 0;
for (int j = 0; j < columns.Count; j++)
{
if (i == 0)
{
cas.Add(new CellAggregator(GetValueInfo(agg.GetValue(false, filters[i], columns[j]))));
}
else
{
cas[pointer].Add(GetValueInfo(agg.GetValue(false, filters[i], columns[j])));
pointer++;
}
}
}
}
else
{
for (int i = 0; i < filters.Count; i++)
{
int pointer = 0;
for (int j = 0; j < rows.Count; j++)
{
for (int k = 0; k < columns.Count; k++)
{
if (i == 0)
{
cas.Add(new CellAggregator(GetValueInfo(agg.GetValue(false, filters[i], columns[k], rows[j]))));
}
else
{
cas[pointer].Add(GetValueInfo(agg.GetValue(false, filters[i], columns[k], rows[j])));
pointer++;
}
}
}
}
}
//cas.ForEach(_ => cs.Cells.Add(new Cell(_.Calculate())));
if (cells != null)
{
cas.ForEach(_ => cells.Add(new Cell(_.Calculate())));
return(null);
}
else
{
return(cas.Select(_ => new Cell(_.Calculate(), _.DataType)));
}
}
void DirectQuery(Aggregation agg)
{
if (rows.Count == 0)
{
for (int i = 0; i < columns.Count; i++)
{
cs.Cells.Add(new Cell(GetValueInfo(agg.GetValue(false, columns[i])).Value));
}
}
else
{
for (int i = 0; i < rows.Count; i++)
{
for (int j = 0; j < columns.Count; j++)
{
cs.Cells.Add(new Cell(GetValueInfo(agg.GetValue(false, columns[j], rows[i])).Value));
}
}
}
}
Aggregation.ValueInfo GetValueInfo(Aggregation.ValueInfo vinfo)
{
// vinfo.IsTotal -> Place holder for total
// AggregationPointer -> Point to total aggregation indexed by total place holder's attributes
if (vinfo.IsTotal)
{
// Find or create AggregationPointer based on total place holder's attributes
int c = 0;
AggregationPointer cp = root;
foreach (AttributeMember[] members in vinfo.Members)
{
foreach (AttributeMember m in members)
{
if (!m.IsTotal && m.LinkedMeasure == null)
{
if (c == 0)
{
if (root == null)
{
root = new AggregationPointer(m.Attribute);
}
cp = root;
}
else
{
AggregationPointer p = cp[m.Attribute];
if (p == null)
{
p = new AggregationPointer(cp, m.Attribute);
cp.Children.Add(p);
}
cp = p;
}
c++;
}
}
}
// Find and assign total aggregation if it is a new pointer (cp.Aggregation == null)
if (cp.Aggregation == null)
{
cp.Aggregation = _pool.FindAggregation(cp.Attributes);
}
return(cp.Aggregation.GetValue(true, vinfo.Members));
}
else
{
return(vinfo);
}
}
}
public JoinedMembers JoinMembers(Aggregation agg, bool shouldCleanUp = false)
{
if (agg == null && Attributes.Length == 0)
{
if (Measures.Length > 0)
{
Measure[] ms = Measures;
JoinedMembers result = new JoinedMembers();
foreach (Measure m in ms)
{
AttributeMember mm = new AttributeMember(m.Name);
mm.LinkedMeasure = m;
result.Add(new AttributeMember[] { mm });
}
return(result);
}
else
{
return(JoinedMembers.Empty);
}
}
else
{
RearrangedSets sets = Rearrange();
if (shouldCleanUp)
{
sets.CleanUp();
}
// return empty if no sets left after cleaned up
if (sets.Count == 0)
{
return(JoinedMembers.Empty);
}
int depth = sets.Count;
int[] indices = new int[depth];
int[] ceilings = new int[depth];
List <List <AttributeMember> > list = new List <List <AttributeMember> >();
for (int i = 0; i < depth; i++)
{
indices[i] = 0;
ceilings[i] = sets[i].Count;
}
Join(0);
void Join(int level)
{
for (int i = 0; i < ceilings[level]; i++)
{
indices[level] = i;
if (level == depth - 1)
{
List <AttributeMember> members = new List <AttributeMember>();
for (int j = 0; j < depth; j++)
{
IQueryMember member = sets[j][indices[j]];
members.Add(member is AttributeMember ? (AttributeMember)member : ((HierarchyMember)member).Origin);
}
if (agg.GetIndex(members) > -1)
{
list.Add(members);
}
}
else
{
Join(level + 1);
}
}
}
JoinedMembers result = new JoinedMembers();
list.ForEach(_ => result.Add(_.ToArray()));
return(result);
}
}
public void Run()
{
if (_connection != null)
{
try
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open();
}
// Need to rename QueryAttributeMembers
List <Measure> measures = QueryMeasureValues();
if (measures.Count > 0)
{
PathFinder pf = new PathFinder(_cmd);
if (pf.Paths.Count > 0)
{
List <Aggregation> aggs = new List <Aggregation>();
// mgroups = measures grouped by fact table
IEnumerable <IGrouping <Table, Measure> > mgroups = measures.GroupBy(_ => _.Definition.Source.Table, _ => _);
Aggregate(_cmd.Permutate(false));
//Aggregate(_cmd.Permutate(true));
aggs.ForEach(_ => _.QueryTotals(_pool));
void Aggregate(List <Attribute[]> indices)
{
foreach (Attribute[] index in indices)
{
Aggregation agg = _pool.FindAggregation(index);
// All key columns are from the same table
IEnumerable <Table> dimTables = index.Select(_ => _.Definition.KeyColumns[0].Table);
foreach (IGrouping <Table, Measure> mgroup in mgroups)
{
Aggregator ag = new Aggregator(
_connection,
index,
mgroup.ToArray(),
pf.GetPath(dimTables, mgroup.Key)
);
if (agg == null)
{
agg = ag.Query();
aggs.Add(agg);
_pool.Aggregations.Add(agg);
}
else
{
if (ag.QueryMeasures(agg, agg.MergeMeasures(mgroup.ToArray())))
{
aggs.Add(agg);
}
}
}
}
}
}
}
}
finally
{
_connection.Close();
}
}
}
public Aggregation Query()
{
DateTime t = DateTime.Now;
int attrLen = Attributes.Length;
int ceiling = attrLen - 1;
int mLen = Measures.Length;
Aggregation agg = new Aggregation(Attributes, Measures);
IDataReader reader = null;
try
{
int rowIndex = 0;
reader = ExecuteReader(GetSql(null));
while (reader.Read())
{
AttributeMember[] tuple = new AttributeMember[attrLen];
for (int i = 0; i < attrLen; i++)
{
tuple[i] = (AttributeMember)Attributes[i].Levels[1].Members[reader[i]];
}
if (rowIndex > 0)
{
AddTotals(tuple, ref rowIndex);
}
agg.Tuples.Add(tuple);
int index = attrLen;
for (int i = 0; i < mLen; i++)
{
agg.Values[i].Values.Add(reader[index]);
index++;
}
rowIndex++;
}
AddTotals(new AttributeMember[attrLen], ref rowIndex);
BuildIndexTree();
agg.QueryTime = (DateTime.Now - t).TotalMilliseconds;
return(agg);
}
finally
{
CloseReader(reader);
}
void AddTotals(AttributeMember[] tuple, ref int ri)
{
AttributeMember[] last = agg.Tuples.Last();
for (int i = tuple.Length - 2; i >= 0; i--)
{
if (tuple[i] != last[i])
{
AttributeMember[] total = (AttributeMember[])last.Clone();
for (int j = i + 1; j < attrLen; j++)
{
Attribute attr = last[j].Attribute;
//Member tm = new Member(attr.Dimension.AttributeAllMemberName);
//tm.IsTotal = true;
//tm.Attribute = attr;
total[j] = (AttributeMember)attr.AllMember;
}
agg.Tuples.Add(total);
agg.TotalIndices.Add(ri);
int index = attrLen;
for (int j = 0; j < mLen; j++)
{
agg.Values[j].Values.Add(0);
index++;
}
ri++;
}
}
}
void BuildIndexTree()
{
IndexTreeNode[] currNodes = new IndexTreeNode[attrLen];
for (int i = 0; i < agg.Tuples.Count; i++)
{
bool changed = false;
for (int j = 0; j < attrLen; j++)
{
AttributeMember member = agg.Tuples[i][j];
// Transfers flat columns to IndexTree structure
// currNode == 1 => first pass
// currNode.Member != member => current member is different from the previous row
// changed => any previous columns is different from the previous row
IndexTreeNode currNode = currNodes[j];
if (currNode == null || currNode.Member != member || changed)
{
changed = true;
// Only leaf nodes hold row index
IndexTreeNode n = new IndexTreeNode(member);
if (j == 0)
{
agg.IndexTree.Nodes.Add(n);
if (attrLen == 1)
{
n.Index = i;
}
}
else
{
currNodes[j - 1].Children.Add(n);
if (j == ceiling)
{
n.Index = i;
}
}
currNodes[j] = n;
}
}
}
}
}