public void StartTest()
{
try
{
Dictionary<Aggregation, long> dictAggRowCount = new Dictionary<Aggregation, long>();
Dictionary<AggregationDesign, long> dictAggDesignRowCount = new Dictionary<AggregationDesign, long>();
AdomdConnection conn = new AdomdConnection("Data Source=" + _currentAggD.ParentServer.Name + ";Initial Catalog=" + _currentAggD.ParentDatabase.Name);
conn.Open();
_sessionID = conn.SessionID;
if (_cancelled) return;
foreach (Partition p in _currentAggD.Parent.Partitions)
{
if (p.AggregationDesignID != _currentAggD.ID) continue;
RaiseProgressEvent(0, "Retrieving list of processed aggs in partition " + p.Name + "...");
AdomdRestrictionCollection coll = new AdomdRestrictionCollection();
coll.Add("DATABASE_NAME", _currentAggD.ParentDatabase.Name);
coll.Add("CUBE_NAME", _currentAggD.ParentCube.Name);
coll.Add("MEASURE_GROUP_NAME", p.Parent.Name);
coll.Add("PARTITION_NAME", p.Name);
DataSet aggDS = conn.GetSchemaDataSet("DISCOVER_PARTITION_STAT", coll);
foreach (DataRow row in aggDS.Tables[0].Rows)
{
if (!string.IsNullOrEmpty(Convert.ToString(row["AGGREGATION_NAME"])))
{
Aggregation a = p.AggregationDesign.Aggregations.FindByName(Convert.ToString(row["AGGREGATION_NAME"]));
if (a == null) throw new Exception("Couldn't find aggregation [" + row["AGGREGATION_NAME"] + "]");
long lngAggRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]);
if (lngAggRowCount > 0)
{
if (!dictAggRowCount.ContainsKey(a))
dictAggRowCount.Add(a, lngAggRowCount);
else
dictAggRowCount[a] += lngAggRowCount;
}
}
else
{
long lngPartitionRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]);
if (!dictAggDesignRowCount.ContainsKey(p.AggregationDesign ?? _emptyAggregationDesign))
dictAggDesignRowCount.Add(p.AggregationDesign ?? _emptyAggregationDesign, lngPartitionRowCount);
else
dictAggDesignRowCount[p.AggregationDesign ?? _emptyAggregationDesign] += lngPartitionRowCount;
}
if (_cancelled) return;
}
}
if (dictAggRowCount.Count == 0) return;
//figure out any DefaultMember that aren't the all member
string sDefaultMembersCalcs = "";
string sDefaultMembersCols = "";
foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions)
{
RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension;
if (rmgd == null) continue;
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled)
{
sDefaultMembersCalcs += "MEMBER [Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|] as iif([" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.Level.Name = \"(All)\", null, [" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.UniqueName)\r\n";
if (sDefaultMembersCols.Length > 0) sDefaultMembersCols += ",";
sDefaultMembersCols += "[Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|]\r\n";
}
}
}
RaiseProgressEvent(1, "Detecting DefaultMember on each dimension attribute...");
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = "with\r\n"
+ sDefaultMembersCalcs
+ "select {\r\n"
+ sDefaultMembersCols
+ "} on 0\r\n"
+ "from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]";
CellSet cs = cmd.ExecuteCellSet();
int iCol = 0;
_dictDefaultMembers.Clear();
foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions)
{
RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension;
if (rmgd == null) continue;
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled)
{
string sValue = Convert.ToString(cs.Cells[iCol++].Value);
if (!string.IsNullOrEmpty(sValue))
{
_dictDefaultMembers.Add(mga, sValue);
}
}
}
}
conn.Close(false);
if (_cancelled) return;
RaiseProgressEvent(2, "Starting trace...");
Server s = new Server();
s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID);
Server sAlt = new Server();
sAlt.Connect("Data Source=" + _currentAggD.ParentServer.Name);
MeasureGroup mgAlt = sAlt.Databases.GetByName(_currentAggD.ParentDatabase.Name).Cubes.GetByName(_currentAggD.ParentCube.Name).MeasureGroups.GetByName(_currentAggD.Parent.Name);
try
{
Database db = s.Databases.GetByName(_currentAggD.ParentDatabase.Name);
string sTraceID = "BIDS Helper Aggs Performance Trace " + System.Guid.NewGuid().ToString();
_trc = s.Traces.Add(sTraceID, sTraceID);
_trc.OnEvent += new TraceEventHandler(trace_OnEvent);
_trc.Stopped += new TraceStoppedEventHandler(trace_Stopped);
_trc.AutoRestart = false;
TraceEvent te;
te = _trc.Events.Add(TraceEventClass.QueryEnd);
te.Columns.Add(TraceColumn.Duration);
te.Columns.Add(TraceColumn.SessionID);
te = _trc.Events.Add(TraceEventClass.GetDataFromAggregation);
te.Columns.Add(TraceColumn.ObjectPath);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.SessionID);
te.Columns.Add(TraceColumn.ConnectionID);
_trc.Update();
_trc.Start();
if (_cancelled) return;
s.BeginTransaction();
UnprocessOtherPartitions(s);
int i = 0;
if (_testAgg)
{
foreach (Aggregation a in dictAggRowCount.Keys)
{
RaiseProgressEvent(3 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with agg " + i + " of " + dictAggRowCount.Count + " (" + a.Name + ")...");
AggregationPerformance aggP = new AggregationPerformance(a);
aggP.AggregationRowCount = dictAggRowCount[a];
aggP.PartitionRowCount = dictAggDesignRowCount[a.Parent];
aggP.MeasureGroupRowCount = aggP.PartitionRowCount; //if there are multiple aggregation designs, outside code will fix that
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.ScriptPerformanceWithAgg = _queryDuration;
_queryEnded = false;
//don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query
ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.QueryPerformanceWithAgg = _queryDuration;
if (_dictHitAggs.ContainsKey(a))
aggP.HitAggregation = true;
aggP.AggHits = _dictHitAggs;
_dictHitAggs = new Dictionary<Aggregation, int>();
_listAggPerf.Add(aggP);
if (_cancelled) return;
}
}
if (_testWithoutSomeAggs && _listAggPerf.Count > 0)
{
RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping some aggs inside a transaction...");
//build list of all aggs which were hit, and which are contained within another agg
List<AggregationPerformance> allAggs = new List<AggregationPerformance>();
foreach (AggregationPerformance ap in _listAggPerf)
{
if (!ap.HitAggregation) continue;
foreach (AggregationPerformance ap2 in _listAggPerf)
{
if (ap.Aggregation != ap2.Aggregation && ap.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(ap.Aggregation, ap2.Aggregation, false))
{
allAggs.Add(ap);
break;
}
}
}
allAggs.Sort(delegate(AggregationPerformance a, AggregationPerformance b)
{
int iCompare = 0;
try
{
if (a == b || a.Aggregation == b.Aggregation) return 0;
iCompare = a.AggregationRowCount.CompareTo(b.AggregationRowCount);
if (iCompare == 0)
{
//if the aggs are the same rowcount, then sort by whether one is contained in the other
if (SearchSimilarAggs.IsAggregationIncluded(a.Aggregation, b.Aggregation, false))
return -1;
else if (SearchSimilarAggs.IsAggregationIncluded(b.Aggregation, a.Aggregation, false))
return 1;
else
return 0;
}
}
catch { }
return iCompare;
});
List<AggregationPerformance> deletedAggregationPerfs = new List<AggregationPerformance>();
List<AggregationPerformance> nextAggs = new List<AggregationPerformance>();
List<AggregationPerformance> aggsToSkipTesting = new List<AggregationPerformance>();
System.Diagnostics.Stopwatch timerProcessIndexes = new System.Diagnostics.Stopwatch();
long lngLastProcessIndexesTime = 0;
AggregationPerformance lastDeletedAggregationPerf = null;
while (allAggs.Count > 0)
{
AggregationPerformance aggP = null;
if (nextAggs.Count == 0)
{
aggP = allAggs[0];
allAggs.RemoveAt(0);
}
else
{
aggP = nextAggs[0];
nextAggs.RemoveAt(0);
allAggs.Remove(aggP);
}
deletedAggregationPerfs.Add(aggP);
//capture XMLA for deleting aggs
AggregationDesign aggD = mgAlt.AggregationDesigns.GetByName(aggP.Aggregation.Parent.Name);
aggD.ParentServer.CaptureXml = true;
foreach (AggregationPerformance ap in deletedAggregationPerfs)
{
if (aggD.Aggregations.ContainsName(ap.Aggregation.Name))
{
aggD.Aggregations.RemoveAt(aggD.Aggregations.IndexOfName(ap.Aggregation.Name));
}
}
aggD.Update(UpdateOptions.ExpandFull);
string sAlterXMLA = aggD.ParentServer.CaptureLog[0];
aggD.ParentServer.CaptureLog.Clear();
aggD.ParentServer.CaptureXml = false;
aggD.Refresh(true); //get the deleted aggs back
ServerExecute(s, sAlterXMLA);
if (_cancelled) return;
RaiseProgressEvent(5 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Processing aggs without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")...");
timerProcessIndexes.Reset();
timerProcessIndexes.Start();
//process aggs to delete existing aggs
ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Parallel>" + "\r\n"
+ " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <Type>ProcessIndexes</Type>" + "\r\n"
+ " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n"
+ " </Process>" + "\r\n"
+ " </Parallel>" + "\r\n"
+ "</Batch>" + "\r\n");
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
timerProcessIndexes.Stop();
//record time it took to process aggs... compare how long the prior one took, then you can determine how much incremental time was spent on the newly deleted agg
if (lastDeletedAggregationPerf != null)
lastDeletedAggregationPerf.ProcessIndexesDuration = lngLastProcessIndexesTime - timerProcessIndexes.ElapsedMilliseconds;
lngLastProcessIndexesTime = timerProcessIndexes.ElapsedMilliseconds;
lastDeletedAggregationPerf = aggP;
if (_cancelled) return;
int j = 0;
foreach (AggregationPerformance deleteAP in deletedAggregationPerfs)
{
RaiseProgressEvent(6 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Testing performance without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + "\r\nTesting agg " + (++j) + " of " + deletedAggregationPerfs.Count + " (" + deleteAP.AggregationName + ")...");
if (aggsToSkipTesting.Contains(deleteAP)) continue; //skip this agg if we've already determined it won't hit another agg
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
long lngScriptDuration = _queryDuration;
_queryEnded = false;
//don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query
ServerExecuteMDX(db, deleteAP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
long lngQueryDuration = _queryDuration;
List<Aggregation> deletedAggregations = new List<Aggregation>();
foreach (AggregationPerformance a in deletedAggregationPerfs)
{
deletedAggregations.Add(a.Aggregation);
}
MissingAggregationPerformance missingAggPerf = new MissingAggregationPerformance(deleteAP, deletedAggregations.ToArray(), _dictHitAggs);
_listMissingAggPerf.Add(missingAggPerf);
missingAggPerf.QueryPerformance = lngQueryDuration;
missingAggPerf.ScriptPerformance = lngScriptDuration;
foreach (Aggregation a in missingAggPerf.AggHitsDiff)
{
foreach (AggregationPerformance ap in allAggs)
{
if (ap.Aggregation == a && !nextAggs.Contains(ap))
nextAggs.Add(ap);
}
}
if (missingAggPerf.AggHitsDiff.Length == 0)
{
aggsToSkipTesting.Add(deleteAP);
}
else
{
bool bThisAggContainedInRemainingAgg = false;
foreach (AggregationPerformance ap2 in allAggs)
{
if (deleteAP.Aggregation != ap2.Aggregation && deleteAP.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(deleteAP.Aggregation, ap2.Aggregation, false))
{
bThisAggContainedInRemainingAgg = true;
break;
}
}
if (!bThisAggContainedInRemainingAgg)
aggsToSkipTesting.Add(deleteAP); //stop testing this agg when it's not contained in any remaining aggs that need to be tested
}
_dictHitAggs = new Dictionary<Aggregation, int>();
}
if (_cancelled) return;
s.RollbackTransaction();
s.BeginTransaction();
UnprocessOtherPartitions(s);
}
}
if (_testNoAggs)
{
//ensure the counter is where it's supposed to be since the "test with some aggs" test may not have done iterations for every agg
i = Math.Max(i, dictAggRowCount.Count * (_totalIterations - 1));
RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping all aggs inside a transaction...");
//delete all aggs in all aggregation designs
string sXMLA = "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:as=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:dwd=\"http://schemas.microsoft.com/DataWarehouse/Designer/1.0\">" + "\r\n"
+ " <Alter AllowCreate=\"true\" ObjectExpansion=\"ExpandFull\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.Parent.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.Parent.Parent.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " <AggregationDesignID>" + _currentAggD.ID + "</AggregationDesignID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <ObjectDefinition>" + "\r\n"
+ " <AggregationDesign>" + "\r\n"
+ " <ID>" + _currentAggD.ID + "</ID>" + "\r\n"
+ " <Name>" + _currentAggD.Name + "</Name>" + "\r\n"
+ " <Aggregations>" + "\r\n"
+ " </Aggregations>" + "\r\n"
+ " </AggregationDesign>" + "\r\n"
+ " </ObjectDefinition>" + "\r\n"
+ " </Alter>" + "\r\n"
+ "</Batch>" + "\r\n";
ServerExecute(s, sXMLA);
RaiseProgressEvent(5 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Processing empty aggregation design...");
if (_cancelled) return;
//process aggs to delete existing aggs
ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Parallel>" + "\r\n"
+ " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <Type>ProcessIndexes</Type>" + "\r\n"
+ " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n"
+ " </Process>" + "\r\n"
+ " </Parallel>" + "\r\n"
+ "</Batch>" + "\r\n");
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
if (_cancelled) return;
foreach (AggregationPerformance aggP in _listAggPerf)
{
RaiseProgressEvent(10 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with no aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")...");
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
if (_cancelled) return;
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.ScriptPerformanceWithoutAggs = _queryDuration;
_queryEnded = false;
ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.QueryPerformanceWithoutAggs = _queryDuration;
}
} //end of testing with no aggs
RaiseProgressEvent(100, "Finished measure group " + _currentAggD.Parent.Name);
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
}
finally
{
try
{
if (!s.Connected)
{
s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID);
}
}
catch
{
try
{
if (!s.Connected)
{
s.Connect("Data Source=" + _currentAggD.ParentServer.Name); //can't connect to that session, so just reconnect
}
}
catch { }
}
try
{
s.RollbackTransaction();
}
catch { }
try
{
_trc.Drop();
}
catch { }
try
{
s.Disconnect();
}
catch { }
}
}
catch (Exception ex)
{
if (!_cancelled)
{
_errors += ex.Message + "\r\n" + ex.StackTrace + "\r\n";
System.Windows.Forms.MessageBox.Show(_errors);
}
}
}
