public MdxScriptDebugger(string servername, string dbname, string cubename)
{
_serverName = servername;
_dbName = dbname;
_cubeName = cubename;
_executionRunning = false;
_executionCancelled = false;
Verbose = false;
ScopeTracking = false;
ValueTracking = false;
PrintPerformanceAnalysis = false;
CreateMembersAndSetsFirst = false;
ClearCache = false;
PreExecuteQueryColdCache = true;
PreExecuteQueryNoCalcs = true;
PostExecuteQuery = true;
WarmCacheExecution = false;
IgnoreErrors = false;
QueryCommandTimeout = -1;
_commands = new SortedDictionary <int, MdxScriptCommand>();
base.WorkerSupportsCancellation = true;
base.WorkerReportsProgress = true;
Server server = new Server();
server.Connect(this.ConnectionString);
Database db = server.Databases.FindByName(_dbName);
Cube cube = db.Cubes.FindByName(_cubeName);
_commands = MdxScriptHelper.GetCommandsFromScript(cube.DefaultMdxScript);
}
public void CancelAsyncWithKill()
{
_executionCancelled = true;
base.CancelAsync();
if (_executionRunning && !string.IsNullOrEmpty(_currentSsasSessionID))
{
using (AdomdConnection con = new AdomdConnection(this.ConnectionString))
{
con.Open();
// ignore errors as the session may already has been cancelled or finished
MdxScriptHelper.ExecuteXMLA(con, MdxScriptHelper.CancelSessionCommand(_currentSsasSessionID), true);
}
}
}
public MdxScriptCommand(string cmd, int order, int nestingLevel)
{
_text = cmd.TrimEnd().TrimEnd(';') + ";";
_order = order;
_nestingLevel = nestingLevel;
_duration = -1;
_durationWarm = -1;
_durationAssignment = -1;
_warmCacheExecution = true;
_isEffective = false;
_isPerformanceRelevant = -1;
_ignoreErrors = true;
_commandType = MdxScriptHelper.ParseCommandType(_text);
}
public static SortedDictionary <int, MdxScriptCommand> GetCommandsFromScript(MdxScript script)
{
SortedDictionary <int, MdxScriptCommand> ret = new SortedDictionary <int, MdxScriptCommand>();
SortedDictionary <int, MdxScriptCommand> temp = new SortedDictionary <int, MdxScriptCommand>();
foreach (Command cmd in script.Commands)
{
temp = MdxScriptHelper.GetCommandsFromText(cmd.Text.Replace("\n", Environment.NewLine), ret.Count);
foreach (MdxScriptCommand cmd1 in temp.Values)
{
ret.Add(cmd1.CommandNumber, cmd1);
}
}
return(ret);
}
public string ToString(MdxScriptCommandDisplayOption displayOption)
{
string helper;
string ret = "";
switch (displayOption)
{
case MdxScriptCommandDisplayOption.Original:
ret = _text;
break;
case MdxScriptCommandDisplayOption.Original_Trimmed:
ret = _text.Trim();
break;
case MdxScriptCommandDisplayOption.NoComments:
ret = MdxScriptHelper.StripComments(_text);
break;
case MdxScriptCommandDisplayOption.NoComments_Trimmed:
ret = MdxScriptHelper.StripComments(_text).Trim();
break;
case MdxScriptCommandDisplayOption.SingleLine:
helper = this.ToString(MdxScriptCommandDisplayOption.NoComments_Trimmed);
ret = Regex.Replace(helper, "(\r\n|\r|\n)", "");
break;
case MdxScriptCommandDisplayOption.SingleLine_Indented:
helper = this.ToString(MdxScriptCommandDisplayOption.SingleLine);
ret = new string(' ', 4 * this._nestingLevel) + this.ToString(MdxScriptCommandDisplayOption.SingleLine);
break;
case MdxScriptCommandDisplayOption.Console:
helper = this.ToString(MdxScriptCommandDisplayOption.SingleLine);
ret = string.Format("{0,5} | {1,10:#,0} ms | {2}", CommandNumber, Duration, helper.SafeLeft(50));
break;
default:
return("");
}
return(ret);
}
public static SortedDictionary <int, MdxScriptCommand> GetCommandsFromText(string sMDX)
{
return(MdxScriptHelper.GetCommandsFromText(sMDX, 0));
}
public static MdxScriptCommandType ParseCommandType(string command)
{
string helper = MdxScriptHelper.StripComments(command).ToUpper().Trim();
if (helper.StartsWith("CALCULATE"))
{
return(MdxScriptCommandType.CALCULATE);
}
else if (helper.StartsWith("CLEAR CALCULATIONS"))
{
return(MdxScriptCommandType.CLEAR_CALCULATIONS);
}
else if (helper.StartsWith("ALTER"))
{
return(MdxScriptCommandType.ALTER_CUBE);
}
else if (helper.StartsWith("SCOPE"))
{
return(MdxScriptCommandType.SCOPE);
}
else if (helper.StartsWith("END SCOPE"))
{
return(MdxScriptCommandType.END_SCOPE);
}
else if (helper.StartsWith("THIS"))
{
return(MdxScriptCommandType.THIS_ASSIGNMENT);
}
else if (helper.StartsWith("(")) // ([Dim].[Hier].&[Key1]) = 1;
{
return(MdxScriptCommandType.DIRECT_ASSIGNMENT);
}
else if (helper.StartsWith("[")) // [Dim].[Hier].&[Key1] = 1;
{
return(MdxScriptCommandType.DIRECT_ASSIGNMENT);
}
else if (helper.StartsWith("CREATE MEMBER"))
{
return(MdxScriptCommandType.CREATE_MEMBER);
}
else if (helper.StartsWith("CREATE CELL CALCULATION"))
{
return(MdxScriptCommandType.CELL_CALCULATION);
}
else if (helper.StartsWith("CREATE"))
{
return(MdxScriptCommandType.CREATE_SET);
}
else if (helper.StartsWith("FREEZE"))
{
return(MdxScriptCommandType.FREEZE);
}
else if (helper.StartsWith("FORMAT_STRING"))
{
return(MdxScriptCommandType.FORMATTING);
}
else if (helper.StartsWith("FORE_COLOR"))
{
return(MdxScriptCommandType.FORMATTING);
}
else if (helper.StartsWith("BACK_COLOR"))
{
return(MdxScriptCommandType.FORMATTING);
}
else
{
return(MdxScriptCommandType.Other);
}
}
public void DebugMdxScript(string query)
{
MdxScriptCommand clearCalcs = MdxScriptCommand.ClearCalculationsCommand;
MdxScriptCommand cmdRec;
if (string.IsNullOrEmpty(query) && !string.IsNullOrEmpty(_query))
{
query = _query;
}
query = query.Trim().TrimEnd(';');
CheckQuery(query);
using (AdomdConnection con = new AdomdConnection(this.ConnectionString))
{
con.Open();
_currentSsasSessionID = con.SessionID;
if (this.ClearCache)
{
ReportProgress(0, new UserState("Clearing Cache ...", _clearCacheCmd, StateChangeType.ExecutionStarted));
MdxScriptHelper.ExecuteXMLA(con, MdxScriptHelper.ClearCacheCommand(_dbName, _cubeName));
ReportProgress(0, new UserState("Done!", _clearCacheCmd, StateChangeType.ExecutionFinished));
}
if (this.CreateMembersAndSetsFirst)
{
PreExecuteCreateStatements();
}
// execute reference query on cold cache
if (this.PreExecuteQueryColdCache && _preExecCmd_Cold != null)
{
ReportProgress(0, new UserState("Running Reference Query (Cold Cache) ...", _preExecCmd_Cold, StateChangeType.ExecutionStarted));
_preExecCmd_Cold.ExecuteTestQuery(con, query, _queryCommandTimeout);
ReportProgress(0, new UserState("Done!", _preExecCmd_Cold, StateChangeType.ExecutionFinished));
}
// execute reference query on warm cache
if (this.PreExecuteQueryWarmCache && _preExecCmd_Warm != null)
{
ReportProgress(0, new UserState("Running Reference Query (Cold Cache) ...", _preExecCmd_Warm, StateChangeType.ExecutionStarted));
_preExecCmd_Warm.ExecuteTestQuery(con, query, _queryCommandTimeout);
ReportProgress(0, new UserState("Done!", _preExecCmd_Warm, StateChangeType.ExecutionFinished));
}
// clear existing calculations
ReportProgress(0, new UserState("Running CLEAR CALCULATIONS ...", StateChangeType.PreExecute));
MdxScriptCommand.ClearCalculationsCommand.Execute(con);
// execute reference query without calculations
if (this.PreExecuteQueryNoCalcs && _preExecCmd_NoCalcs != null)
{
ReportProgress(0, new UserState("Running Reference Query (No Script) ...", _preExecCmd_NoCalcs, StateChangeType.ExecutionStarted));
_preExecCmd_NoCalcs.ExecuteTestQuery(con, query, _queryCommandTimeout);
ReportProgress(0, new UserState("Done!", _preExecCmd_NoCalcs, StateChangeType.ExecutionFinished));
}
if (this.ValueTracking)
{
MdxScriptCommand.CreateValueTrackingCommand.Execute(con);
query = MdxScriptHelper.EnsureCellProperty(query, MdxScriptCommand.CellPropertyValueTracking);
}
foreach (MdxScriptCommand cmd in _commands.Values)
{
if (base.CancellationPending)
{
_executionCancelled = true;
return;
}
ReportProgress((cmd.CommandNumber * 100) / this.CommandCount, new UserState("Executing MDX Script command ...", cmd, StateChangeType.ExecutionStarted));
cmd.Execute(con);
ReportProgress((cmd.CommandNumber * 100) / this.CommandCount, new UserState("Done!", cmd, StateChangeType.ExecutionFinished));
// execute the query if the current MdxScriptCommand is PerformanceRelevant or Verbose
if (cmd.IsPerformanceRelevant || this.Verbose)
{
if (this.ValueTracking)
{
MdxScriptCommand.DropValueTrackingCommand.Execute(con);
MdxScriptCommand.CreateValueTrackingCommand.Execute(con);
}
ReportProgress((cmd.CommandNumber * 100) / this.CommandCount, new UserState("Running Query ...", cmd, StateChangeType.ExecutionStarted));
cmd.ExecuteTestQuery(con, query, _queryCommandTimeout);
if (this.ValueTracking)
{
List <string> currentCellProperties = MdxScriptHelper.GetDistinctCellProperties(cmd.Results, MdxScriptCommand.CellPropertyValueTracking);
if (currentCellProperties.Contains(MdxScriptCommand.ValueChangeValueTracking))
{
cmd.IsEffective = true;
cmdRec = cmd;
// also set all parent SCOPE commands and their END SCOPE as Effective
while (cmdRec.ParentCommand != null)
{
cmdRec.ParentCommand.IsEffective = true;
cmdRec.ParentCommand.CorrespondingEndScopeCommand.IsEffective = true;
cmdRec = cmdRec.ParentCommand;
}
}
}
ReportProgress((cmd.CommandNumber * 100) / this.CommandCount, new UserState("Done!", cmd, StateChangeType.ExecutionFinished));
}
else
{
// report finished if not yet reported due to IsPerformanceRelevant
ReportProgress((cmd.CommandNumber * 100) / this.CommandCount, new UserState("Done!", cmd, StateChangeType.ExecutionFinished));
}
}
// execute reference query with all calculations again
if (this.PostExecuteQuery && _postExecCmd != null)
{
if (this.ClearCache)
{
ReportProgress(0, new UserState("Clearing Cache ...", _clearCacheCmd, StateChangeType.ExecutionStarted));
MdxScriptHelper.ExecuteXMLA(con, MdxScriptHelper.ClearCacheCommand(_dbName, _cubeName));
ReportProgress(0, new UserState("Done!", _clearCacheCmd, StateChangeType.ExecutionFinished));
}
ReportProgress(0, new UserState("Running Query (No Script) ...", _postExecCmd, StateChangeType.ExecutionStarted));
_postExecCmd.ExecuteTestQuery(con, query, _queryCommandTimeout);
ReportProgress(0, new UserState("Done!", _postExecCmd, StateChangeType.ExecutionFinished));
}
}
_currentSsasSessionID = null;
}
public void LoadCustomMdxScript(string customScript)
{
_commands.Clear();
_commands = MdxScriptHelper.GetCommandsFromText(customScript);
}
