public static void TraceException(ProcedureContext procedureContext, Exception exception)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
if (exception == null)
{
throw new ArgumentNullException("exception");
}
#endregion
var updateCommand = @"UPDATE [asqa].[Execution] SET [ExecutionError] = @executionError WHERE [BatchID] = CAST(@batchID AS UNIQUEIDENTIFIER) AND [ExecutionID] = CAST(@executionID AS UNIQUEIDENTIFIER)";
using (procedureContext.ImpersonationIdentity.Impersonate())
{
using (var connection = new SqlConnection(procedureContext.BatchConnectionString))
{
connection.Open();
using (var command = new SqlCommand(updateCommand, connection))
{
command.Parameters.Add(new SqlParameter("@batchID", SqlDbType.UniqueIdentifier)).Value = procedureContext.BatchID;
command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID;
command.Parameters.Add(new SqlParameter("@executionError", SqlDbType.NVarChar)).Value = exception.ToString();
command.ExecuteNonQuery();
}
}
}
}
public static void Finalize(ProcedureContext procedureContext)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
var updateCommand = @"UPDATE [asqa].[Execution] SET [ExecutionEndTime] = @executionEndTime WHERE [BatchID] = CAST(@batchID AS UNIQUEIDENTIFIER) AND [ExecutionID] = CAST(@executionID AS UNIQUEIDENTIFIER)";
using (procedureContext.ImpersonationIdentity.Impersonate())
{
using (var connection = new SqlConnection(procedureContext.BatchConnectionString))
{
connection.Open();
using (var command = new SqlCommand(updateCommand, connection))
{
command.Parameters.Add(new SqlParameter("@batchID", SqlDbType.UniqueIdentifier)).Value = procedureContext.BatchID;
command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID;
command.Parameters.Add(new SqlParameter("@executionEndTime", SqlDbType.DateTime)).Value = DateTime.UtcNow;
command.ExecuteNonQuery();
}
}
}
}
public static void WriteToServer(ProcedureContext procedureContext, IDataReader reader, string destinationTableName, string beforeActionCommandText = null, string afterActionCommandText = null)
{
using (procedureContext.ImpersonationIdentity.Impersonate())
{
using (var connection = new SqlConnection(procedureContext.BatchConnectionString))
{
connection.Open();
if (beforeActionCommandText != null)
{
using (var command = connection.CreateCommand())
{
command.CommandText = beforeActionCommandText;
command.ExecuteNonQuery();
}
}
// http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, externalTransaction: null))
{
var fields = Enumerable.Range(0, reader.FieldCount);
foreach (var columnName in fields.Select((i) => reader.GetName(i)))
{
bulkCopy.ColumnMappings.Add(columnName, columnName);
}
bulkCopy.BatchSize = BatchHelper.BulkCopyBatchSize;
bulkCopy.BulkCopyTimeout = BatchHelper.BulkCopyTimeout;
bulkCopy.DestinationTableName = destinationTableName;
do
{
bulkCopy.WriteToServer(reader);
}while (reader.NextResult());
}
if (afterActionCommandText != null)
{
using (var command = connection.CreateCommand())
{
command.CommandText = afterActionCommandText;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
}
}
public static DataTable GetConfigurationFor(ConfigurationType configurationType)
{
switch (configurationType)
{
case ConfigurationType.Engine:
return(ProcedureContext.GetConfiguration());
case ConfigurationType.TraceEvents:
return(ProfilerCollector.GetConfiguration());
case ConfigurationType.PerformanceCounters:
return(PerformanceCollector.GetConfiguration());
default:
throw new ApplicationException("Unknown ConfigurationType [{0}]".FormatWith(configurationType));
}
}
public static DataTable ToExecutionInfoTable(this ProcedureContext procedureContext)
{
#region Argument exception
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
var table = new DataTable("ExecutionInfo", "Common");
{
table.Columns.Add("key", typeof(string));
table.Columns.Add("value", typeof(string));
}
if (procedureContext.ExecutionMode == ProcedureExecutionMode.Batch)
{
table.Rows.Add("batch_id", procedureContext.BatchID.ToString());
table.Rows.Add("batch_name", procedureContext.BatchName);
table.Rows.Add("sql_instance_name", procedureContext.SQLInstanceName);
table.Rows.Add("sql_instance_version", procedureContext.SQLInstanceVersion);
table.Rows.Add("sql_instance_edition", procedureContext.SQLInstanceEdition);
}
table.Rows.Add("client_version", procedureContext.ClientVersion);
table.Rows.Add("client_type", procedureContext.ClientType);
table.Rows.Add("connection_user_name", procedureContext.ConnectionUserName);
table.Rows.Add("asqa_server_version", procedureContext.ASQAServerVersion);
table.Rows.Add("asqa_server_config", procedureContext.ASQAServerConfig);
table.Rows.Add("ssas_instance_name", procedureContext.SSASInstanceName);
table.Rows.Add("ssas_instance_version", procedureContext.SSASInstanceVersion.ToString());
table.Rows.Add("ssas_instance_edition", procedureContext.SSASInstanceEdition);
table.Rows.Add("ssas_instance_config", procedureContext.SSASInstanceConfig);
table.Rows.Add("statement", procedureContext.Statement);
table.Rows.Add("database_name", procedureContext.DatabaseName);
table.Rows.Add("cube_name", procedureContext.CubeName);
table.Rows.Add("cube_metadata", procedureContext.CubeMetadata);
table.Rows.Add("system_operative_system_name", procedureContext.SystemOperativeSystemName);
table.Rows.Add("system_physical_memory", procedureContext.SystemPhysicalMemory);
table.Rows.Add("system_logical_cpu_core", procedureContext.SystemLogicalCpuCore);
return(table);
}
public static void ClearCache(this ProcedureContext procedureContext)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException(nameof(procedureContext));
}
if (procedureContext.ClearCacheMode == ClearCacheMode.Default)
{
throw new ArgumentException("Invalid ClearCacheMode[ClearCacheMode.Default]");
}
#endregion
if (procedureContext.IsCancellationRequested)
{
return;
}
if (procedureContext.ClearCacheMode != ClearCacheMode.Nothing)
{
EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepClearCache);
}
if (procedureContext.ClearCacheMode == ClearCacheMode.CurrentCube ||
procedureContext.ClearCacheMode == ClearCacheMode.CurrentCubeAndFileSystem ||
procedureContext.ClearCacheMode == ClearCacheMode.CurrentDatabase ||
procedureContext.ClearCacheMode == ClearCacheMode.CurrentDatabaseAndFileSystem ||
procedureContext.ClearCacheMode == ClearCacheMode.AllDatabases ||
procedureContext.ClearCacheMode == ClearCacheMode.AllDatabasesAndFileSystem)
{
XmlaHelper.ClearCache(procedureContext);
}
if (procedureContext.ClearCacheMode == ClearCacheMode.FileSystemOnly ||
procedureContext.ClearCacheMode == ClearCacheMode.CurrentCubeAndFileSystem ||
procedureContext.ClearCacheMode == ClearCacheMode.CurrentDatabaseAndFileSystem ||
procedureContext.ClearCacheMode == ClearCacheMode.AllDatabasesAndFileSystem)
{
FileSystemHelper.ClearFileSystemCache(clearStandbyCache: true);
}
}
private static void WriteToServer(ProcedureContext procedureContext, DataTable table, string destinationTableName)
{
using (procedureContext.ImpersonationIdentity.Impersonate())
{
// http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf
using (var bulkCopy = new SqlBulkCopy(procedureContext.BatchConnectionString, SqlBulkCopyOptions.TableLock))
{
foreach (DataColumn column in table.Columns)
{
bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
bulkCopy.BatchSize = BatchHelper.BulkCopyBatchSize;
bulkCopy.BulkCopyTimeout = BatchHelper.BulkCopyTimeout;
bulkCopy.DestinationTableName = destinationTableName;
bulkCopy.WriteToServer(table);
}
}
}
public static void ValidateStatement(this ProcedureContext procedureContext)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
if (procedureContext.IsCancellationRequested)
{
return;
}
EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepValidateStatement);
AdomdClientHelper.ExecutePrepare(procedureContext.ConnectionString, procedureContext.Statement);
}
public static Tuple <string, string, string, string> GetServerInfo(ProcedureContext procedureContext)
{
var selectCommand = @"
SELECT
[ServerName] = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)),
[ProductVersion] = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(256)),
[Edition] = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(256)),
[ASQADatabaseVersion] =
(
SELECT [ASQADatabaseVersion] = ep.value
FROM sys.extended_properties AS ep
WHERE ep.class = 0 AND ep.name = N'ASQA_DatabaseVersion'
)
";
using (procedureContext.ImpersonationIdentity.Impersonate())
{
using (var connection = new SqlConnection(procedureContext.BatchConnectionString))
{
connection.Open();
using (var command = new SqlCommand(selectCommand, connection))
{
using (var reader = command.ExecuteReader())
using (var table = new DataTable())
{
table.Load(reader);
var serverName = Convert.ToString(table.Rows[0][0]);
var productVersion = Convert.ToString(table.Rows[0][1]);
var edition = Convert.ToString(table.Rows[0][2]);
var asqaDatabaseVersion = Convert.ToString(table.Rows[0][3]);
return(Tuple.Create(serverName, productVersion, edition, asqaDatabaseVersion));
}
}
}
}
}
/// <summary>
/// Force reload MDX script code in the cube executing a query that return an empty cellset.
/// </summary>
public static void LoadCubeScript(this ProcedureContext procedureContext)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
if (procedureContext.IsCancellationRequested)
{
return;
}
if (procedureContext.ClearCacheMode != ClearCacheMode.Nothing)
{
EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepLoadCubeScript);
AdomdClientHelper.ExecuteNonQuery(procedureContext.ConnectionString, "SELECT {{}} ON 0 FROM [{0}]".FormatWith(procedureContext.CubeName));
}
}
public static void Calculate(ProcedureContext procedureContext)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
Func <string, string> loadResource = (name) =>
{
using (var stream = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(name))
using (var reader = new StreamReader(stream))
return(reader.ReadToEnd());
};
const string BatchCalculateCommonEnginePerformance = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonEnginePerformance.sql";
const string BatchCalculateCommonAggregationsRead = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonAggregationsRead.sql";
const string BatchCalculateCommonPartitionsRead = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonPartitionsRead.sql";
const string BatchCalculateCommonCachesRead = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonCachesRead.sql";
using (procedureContext.ImpersonationIdentity.Impersonate())
{
using (var connection = new SqlConnection(procedureContext.BatchConnectionString))
{
connection.Open();
if (procedureContext.IsCancellationRequested)
{
return;
}
using (var command = new SqlCommand(loadResource(BatchCalculateCommonEnginePerformance), connection))
{
command.CommandTimeout = CalculateTimeout;
command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID;
command.ExecuteNonQuery();
}
if (procedureContext.IsCancellationRequested)
{
return;
}
using (var command = new SqlCommand(loadResource(BatchCalculateCommonAggregationsRead), connection))
{
command.CommandTimeout = CalculateTimeout;
command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID;
command.ExecuteNonQuery();
}
if (procedureContext.IsCancellationRequested)
{
return;
}
using (var command = new SqlCommand(loadResource(BatchCalculateCommonPartitionsRead), connection))
{
command.CommandTimeout = CalculateTimeout;
command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID;
command.ExecuteNonQuery();
}
if (procedureContext.IsCancellationRequested)
{
return;
}
using (var command = new SqlCommand(loadResource(BatchCalculateCommonCachesRead), connection))
{
command.CommandTimeout = CalculateTimeout;
command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID;
command.ExecuteNonQuery();
}
}
}
}
public static void WriteToServer(this List <PerformanceAggregate> aggreagates, ProcedureContext procedureContext)
{
#region Argument exceptions
if (aggreagates == null)
{
throw new ArgumentNullException("aggreagates");
}
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
// TODO: LINQ to IDataReader, avoid DataTables
foreach (var table in aggreagates.ToDataTables(executeForPrepare: false))
{
using (table)
{
if (procedureContext.IsCancellationRequested)
{
break;
}
if (table.Rows.Count == 0)
{
continue;
}
table.Columns.Add(new DataColumn("ExecutionID", typeof(Guid))
{
DefaultValue = procedureContext.ExecutionID
});
table.Columns.Add(new DataColumn("CategoryName", typeof(string))
{
DefaultValue = table.Namespace.Split(':').Last()
});
table.Columns.Add(new DataColumn("CounterName", typeof(string))
{
DefaultValue = table.TableName
});
WriteToServer(procedureContext, table, PerformanceTableName);
}
}
}
private ProcedureContext(ProcedureExecutionMode executionMode, string statement, ClearCacheMode clearCacheMode, int queryResultRowLimit = 0, string clientVersion = null, string clientType = null, string batchID = null, string batchConnectionString = null, string batchName = null)
{
#region Argument exception
if (statement == null)
{
throw new ArgumentNullException("statement");
}
if (executionMode == ProcedureExecutionMode.Batch ^ batchID != null)
{
throw new ArgumentException("ExecutionMode XOR batchID");
}
if (executionMode == ProcedureExecutionMode.Batch ^ batchConnectionString != null)
{
throw new ArgumentException("ExecutionMode XOR BatchConnectionString");
}
#endregion
_batchConnectionString = batchConnectionString;
_cancellationSource = new CancellationTokenSource();
Statement = statement;
ExecutionMode = executionMode;
BatchID = Guid.Parse(batchID ?? Guid.Empty.ToString());
ExecutionID = Guid.NewGuid();
BatchName = batchName;
CubeName = statement.GetCubeName();
QueryResultRowLimit = queryResultRowLimit;
DatabaseName = AdomdServer.Context.CurrentDatabaseName;
ConnectionUserName = AdomdServer.Context.CurrentConnection.User.Name;
ImpersonationIdentity = WindowsIdentity.GetCurrent();
ExecuteForPrepare = AdomdServer.Context.ExecuteForPrepare;
CurrentTraceEvents = ProfilerCollector.CurrentTraceEvents();
ASQAServerVersion = GetAsqaServerVersion().ToString();
ASQAServerConfig = GetRawConfiguration();
ClientVersion = clientVersion;
ClientType = clientType;
SystemPhysicalMemory = PInvokeHelper.GetPhysicalSystemMemory();
SystemOperativeSystemName = PInvokeHelper.GetOperatingSystemName();
SystemLogicalCpuCore = Environment.ProcessorCount;
GetConfigurationDetails(out int configuredTraceEventsThreshold, out ClearCacheMode configuredClearCacheMode);
TraceEventsThreshold = configuredTraceEventsThreshold;
ClearCacheMode = clearCacheMode == ClearCacheMode.Default ? configuredClearCacheMode : clearCacheMode;
var ssasInfo = GetServerInfo();
SSASInstanceName = AdomdServer.Context.CurrentServerID;
SSASInstanceVersion = ssasInfo.Item1;
SSASInstanceEdition = ssasInfo.Item2;
SSASInstanceConfig = ssasInfo.Item3;
if (executionMode == ProcedureExecutionMode.Batch)
{
var sqlInfo = BatchHelper.GetServerInfo(this);
SQLInstanceName = sqlInfo.Item1;
SQLInstanceVersion = sqlInfo.Item2;
SQLInstanceEdition = sqlInfo.Item3;
CheckBatchVersionCompatibility(version: sqlInfo.Item4);
}
CubeMetadata = XmlaHelper.RetrieveCubeMetadata(this);
ProcedureContext.CheckVersionCompatibility(SSASInstanceVersion);
}
public static void WriteToSqlite(ProcedureContext procedureContext, IDataReader reader, Action <IDataReader> afterCompleted)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
if (reader == null)
{
throw new ArgumentNullException("reader");
}
if (afterCompleted == null)
{
throw new ArgumentNullException("afterCompleted");
}
#endregion
var createTableCommandText = @"
CREATE TABLE Trace
(
ID NUMERIC,
EventClass TEXT,
EventSubclass TEXT,
IntegerData INTEGER,
StartTime DATETIME,
CurrentTime DATETIME,
Duration INTEGER,
ObjectName TEXT,
CpuTime INTEGER,
EndTime DATETIME,
ObjectID TEXT,
ObjectPath TEXT,
ObjectType TEXT,
ObjectReference TEXT,
ProgressTotal INTEGER,
TextData TEXT
)";
var insertCommandText = @"
INSERT INTO Trace
(
ID,
EventClass,
EventSubclass,
IntegerData,
StartTime,
CurrentTime,
Duration,
ObjectName,
CpuTime,
EndTime,
ObjectID,
ObjectPath,
ObjectType,
ObjectReference,
ProgressTotal,
TextData
)
VALUES
(
@ID,
@EventClass,
@EventSubclass,
@IntegerData,
@StartTime,
@CurrentTime,
@Duration,
@ObjectName,
@CpuTime,
@EndTime,
@ObjectID,
@ObjectPath,
@ObjectType,
@ObjectReference,
@ProgressTotal,
@TextData
)";
var selectCommandText = @"
SELECT
ID,
EventClass,
EventSubclass,
IntegerData,
StartTime,
CurrentTime,
Duration,
ObjectName,
CpuTime,
EndTime,
ObjectID,
ObjectPath,
ObjectType,
ObjectReference,
ProgressTotal,
TextData
FROM
Trace";
System.Action action = () =>
{
var path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), ProcedureContext.SQLiteFolderPath);
Environment.SetEnvironmentVariable("PreLoadSQLite_BaseDirectory", Path.Combine(path, "bin"));
var interopPath = Path.Combine(path, "bin", "x64");
var interopFile = Path.Combine(interopPath, "SQLite.Interop.dll");
if (!File.Exists(interopFile))
{
Directory.CreateDirectory(interopPath);
using (var stream = new StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("SSASQueryAnalyzer.Server.Resources.SQLite.Interop.dll")))
using (var file = File.Create(interopFile))
{
stream.BaseStream.Seek(0, SeekOrigin.Begin);
stream.BaseStream.CopyTo(file);
}
}
var databasePath = Path.Combine(path, "db");
var databaseFileName = Path.Combine(databasePath, "asqa-{0:N}-{1:N}.db".FormatWith(procedureContext.BatchID, procedureContext.ExecutionID));
Directory.CreateDirectory(databasePath);
foreach (var file in Directory.EnumerateFiles(databasePath, "asqa-*.db"))
{
File.Delete(file);
}
var connectionStringBuilder = new SQLiteConnectionStringBuilder();
connectionStringBuilder.JournalMode = SQLiteJournalModeEnum.Off;
connectionStringBuilder.SyncMode = SynchronizationModes.Off;
connectionStringBuilder.DataSource = databaseFileName;
connectionStringBuilder.FailIfMissing = false;
connectionStringBuilder.LegacyFormat = false;
connectionStringBuilder.Version = 3;
connectionStringBuilder.DateTimeKind = DateTimeKind.Utc;
connectionStringBuilder.DateTimeFormat = SQLiteDateFormats.ISO8601;
using (var connection = new SQLiteConnection(connectionStringBuilder.ToString()))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = createTableCommandText;
command.ExecuteNonQuery();
}
using (var transaction = connection.BeginTransaction())
using (var command = connection.CreateCommand())
{
command.CommandText = insertCommandText;
command.Prepare();
#region Params binding
var paramID = command.Parameters.Add("@ID", DbType.Int32);
var paramEventClass = command.Parameters.Add("@EventClass", DbType.String);
var paramEventSubclass = command.Parameters.Add("@EventSubclass", DbType.String);
var paramIntegerData = command.Parameters.Add("@IntegerData", DbType.Int64);
var paramStartTime = command.Parameters.Add("@StartTime", DbType.DateTime);
var paramCurrentTime = command.Parameters.Add("@CurrentTime", DbType.DateTime);
var paramDuration = command.Parameters.Add("@Duration", DbType.Int32);
var paramObjectName = command.Parameters.Add("@ObjectName", DbType.String);
var paramCpuTime = command.Parameters.Add("@CpuTime", DbType.Int32);
var paramEndTime = command.Parameters.Add("@EndTime", DbType.DateTime);
var paramObjectID = command.Parameters.Add("@ObjectID", DbType.String);
var paramObjectPath = command.Parameters.Add("@ObjectPath", DbType.String);
var paramObjectType = command.Parameters.Add("@ObjectType", DbType.String);
var paramObjectReference = command.Parameters.Add("@ObjectReference", DbType.String);
var paramProgressTotal = command.Parameters.Add("@ProgressTotal", DbType.Int64);
var paramTextData = command.Parameters.Add("@TextData", DbType.String);
var columnOrdinalID = reader.GetOrdinal("ID");
var columnOrdinalEventClass = reader.GetOrdinal("EventClass");
var columnOrdinalEventSubclass = reader.GetOrdinal("EventSubclass");
var columnOrdinalIntegerData = reader.GetOrdinal("IntegerData");
var columnOrdinalStartTime = reader.GetOrdinal("StartTime");
var columnOrdinalCurrentTime = reader.GetOrdinal("CurrentTime");
var columnOrdinalDuration = reader.GetOrdinal("Duration");
var columnOrdinalObjectName = reader.GetOrdinal("ObjectName");
var columnOrdinalCpuTime = reader.GetOrdinal("CpuTime");
var columnOrdinalEndTime = reader.GetOrdinal("EndTime");
var columnOrdinalObjectID = reader.GetOrdinal("ObjectID");
var columnOrdinalObjectPath = reader.GetOrdinal("ObjectPath");
var columnOrdinalObjectType = reader.GetOrdinal("ObjectType");
var columnOrdinalObjectReference = reader.GetOrdinal("ObjectReference");
var columnOrdinalProgressTotal = reader.GetOrdinal("ProgressTotal");
var columnOrdinalTextData = reader.GetOrdinal("TextData");
#endregion
do
{
while (reader.Read())
{
#region Params values
paramID.Value = reader[columnOrdinalID];
paramEventClass.Value = reader[columnOrdinalEventClass];
paramEventSubclass.Value = reader[columnOrdinalEventSubclass];
paramIntegerData.Value = reader[columnOrdinalIntegerData];
paramStartTime.Value = reader[columnOrdinalStartTime];
paramCurrentTime.Value = reader[columnOrdinalCurrentTime];
paramDuration.Value = reader[columnOrdinalDuration];
paramObjectName.Value = reader[columnOrdinalObjectName];
paramCpuTime.Value = reader[columnOrdinalCpuTime];
paramEndTime.Value = reader[columnOrdinalEndTime];
paramObjectID.Value = reader[columnOrdinalObjectID];
paramObjectPath.Value = reader[columnOrdinalObjectPath];
paramObjectType.Value = reader[columnOrdinalObjectType];
paramObjectReference.Value = reader[columnOrdinalObjectReference];
paramProgressTotal.Value = reader[columnOrdinalProgressTotal];
paramTextData.Value = reader[columnOrdinalTextData];
#endregion
command.ExecuteNonQuery();
}
}while (reader.NextResult());
transaction.Commit();
}
using (var command = connection.CreateCommand())
{
command.CommandText = selectCommandText;
using (var select = command.ExecuteReader())
afterCompleted(select);
}
}
};
using (var task = Task.Factory.StartNew(action))
task.Wait();
}
public static string RetrieveCubeMetadata(ProcedureContext procedureContext)
{
#region Argument exception
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
var path = default(string);
var document = new XDocument(new XDeclaration("1.0", "utf-8", standalone: null), new XElement("CubeMetadata"));
using (var server = new Server())
{
server.Connect(procedureContext.ConnectionString);
#region Find database/cube/dimension
var database = server.Databases.FindByName(procedureContext.DatabaseName);
if (database == null)
{
throw new ApplicationException("Database not found [{0}]".FormatWith(procedureContext.DatabaseName));
}
var cube = database.Cubes.FindByName(procedureContext.CubeName);
if (cube == null)
{
throw new ApplicationException("Cube not found [{0}]".FormatWith(procedureContext.CubeName));
}
var dimension = cube.Dimensions.Cast <CubeDimension>().FirstOrDefault((d) => d.Visible);
if (dimension == null)
{
throw new ApplicationException("CubeDimension for metadata not found");
}
#endregion
path = "[{0}]".FormatWith(dimension.Name);
var attribute = dimension.Attributes.Cast <CubeAttribute>().FirstOrDefault((a) => a.AttributeHierarchyVisible && a.AttributeHierarchyEnabled);
if (attribute != null)
{
path += ".[{0}]".FormatWith(attribute.Attribute.Name);
}
else
{
var hierarchy = dimension.Hierarchies.Cast <CubeHierarchy>().FirstOrDefault((h) => h.Visible && h.Enabled);
if (hierarchy == null)
{
throw new ApplicationException("CubeAttribute/CubeHierarchy for metadata not found");
}
path += ".[{0}]".FormatWith(hierarchy.Hierarchy.Name);
}
var mdxScript = cube.MdxScripts.Cast <MdxScript>().First();
document.Root.Add(new XElement("Cube",
new XAttribute("ID", cube.ID ?? string.Empty),
new XAttribute("Name", cube.Name ?? string.Empty),
new XAttribute("LastSchemaUpdate", cube.LastSchemaUpdate.ToUniversalTime().ToString("yyyyMMdd-HHmmss", CultureInfo.InvariantCulture)),
new XAttribute("EstimatedRows", cube.EstimatedRows),
new XAttribute("StorageMode", cube.StorageMode),
new XAttribute("DefaultMeasure", cube.DefaultMeasure ?? string.Empty),
new XElement("MdxScript",
new XAttribute("ID", mdxScript.ID ?? string.Empty),
new XAttribute("Name", mdxScript.Name ?? string.Empty),
new XAttribute("DefaultScript", mdxScript.DefaultScript),
new XElement("Commands", mdxScript.Commands.Cast <Command>()
.Select((c) => { var e = new XElement("Command"); e.Value = c.Text; return(e); }))),
new XElement("CubeDimensions", cube.Dimensions.Cast <CubeDimension>()
.Select((d) => new XElement("CubeDimension",
new XAttribute("ID", d.ID ?? string.Empty),
new XAttribute("Name", d.Name ?? string.Empty),
new XAttribute("Visible", d.Visible),
new XAttribute("AttributesCount", d.Attributes.Count),
new XAttribute("HierarchiesCount", d.Hierarchies.Count),
new XAttribute("DimensionID", d.DimensionID ?? string.Empty)))),
new XElement("MeasureGroups", cube.MeasureGroups.Cast <MeasureGroup>()
.Select((m) => new XElement("MeasureGroup",
new XAttribute("ID", m.ID ?? string.Empty),
new XAttribute("Name", m.Name),
new XAttribute("Type", m.Type),
new XAttribute("StorageMode", m.StorageMode),
new XAttribute("EstimatedRows", m.EstimatedRows),
new XAttribute("EstimatedSize", m.EstimatedSize),
new XAttribute("DataAggregation", m.DataAggregation),
new XAttribute("ProcessingMode", m.ProcessingMode),
new XElement("AggregationDesigns", m.AggregationDesigns.Cast <AggregationDesign>()
.Select((a) => new XElement("AggregationDesign",
new XAttribute("ID", a.ID ?? string.Empty),
new XAttribute("Name", a.Name),
new XAttribute("EstimatedRows", a.EstimatedRows),
new XAttribute("EstimatedPerformanceGain", a.EstimatedPerformanceGain)))),
new XElement("Partitions", m.Partitions.Cast <Partition>()
.Select((p) => new XElement("Partition",
new XAttribute("ID", p.ID ?? string.Empty),
new XAttribute("Name", p.Name),
new XAttribute("EstimatedRows", p.EstimatedRows),
new XAttribute("EstimatedSize", p.EstimatedSize),
new XAttribute("ProcessingMode", p.ProcessingMode),
new XAttribute("Slice", p.Slice ?? string.Empty),
new XAttribute("StorageMode", p.StorageMode),
new XAttribute("Type", p.Type),
new XAttribute("AggregationDesignID", p.AggregationDesignID ?? string.Empty)))),
new XElement("Measures", m.Measures.Cast <Measure>()
.Select((s) => new XElement("Measure",
new XAttribute("ID", s.ID ?? string.Empty),
new XAttribute("Name", s.Name),
new XAttribute("Visible", s.Visible),
new XAttribute("DataType", s.DataType),
new XAttribute("FormatString", s.FormatString ?? string.Empty),
new XAttribute("MeasureExpression", s.MeasureExpression ?? string.Empty),
new XAttribute("SourceNullProcessing", s.Source.NullProcessing),
new XAttribute("SourceNullDataType", s.Source.DataType)))))))));
}
using (var table = AdomdClientHelper.ExecuteDataTable(procedureContext.ConnectionString, commandText: MdxMeasuresMetadata.FormatWith(procedureContext.CubeName, path)))
{
if (table.Columns.Count != 3)
{
throw new ApplicationException("Invalid columns for MdxMeasuresMetadata");
}
table.Columns[0].ColumnName = "Name";
table.Columns[1].ColumnName = "IDOfCurrentMember";
table.Columns[2].ColumnName = "ID";
document.Root.Add(new XElement("MeasuresExtended",
table.AsEnumerable().Skip(1)
.Select((r) => new XElement("Measure", table.Columns.Cast <DataColumn>().Select((c) => new XAttribute(c.ColumnName, r[c])))))
);
}
return(document.ToString(SaveOptions.DisableFormatting));
}
/// <summary>
/// Code from ASStoredProcedures [ASStoredProcs.XmlaDiscover.ClearCache(string cubeName)]
/// </summary>
/// <see cref="http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures"/>
public static void ClearCache(ProcedureContext procedureContext)
{
#region Argument exception
if (procedureContext == null)
{
throw new ArgumentNullException(nameof(procedureContext));
}
#endregion
using (var server = new Server())
{
server.Connect(procedureContext.ConnectionString);
switch (procedureContext.ClearCacheMode)
{
case ClearCacheMode.CurrentCube:
case ClearCacheMode.CurrentCubeAndFileSystem:
{
var database = server.Databases.FindByName(procedureContext.DatabaseName);
if (database == null)
{
throw new ApplicationException("Database not found [{0}]".FormatWith(procedureContext.DatabaseName));
}
var cube = database.Cubes.FindByName(procedureContext.CubeName);
if (cube == null)
{
throw new ApplicationException("Cube not found [{0}]".FormatWith(procedureContext.DatabaseName));
}
server.Execute(XmlaBatchClearCubeCacheTemplate.FormatWith(database.ID, cube.ID));
}
break;
case ClearCacheMode.CurrentDatabase:
case ClearCacheMode.CurrentDatabaseAndFileSystem:
{
var database = server.Databases.FindByName(procedureContext.DatabaseName);
if (database == null)
{
throw new ApplicationException("Database not found [{0}]".FormatWith(procedureContext.DatabaseName));
}
server.Execute(XmlaBatchClearDatabaseCacheTemplate.FormatWith(database.ID));
}
break;
case ClearCacheMode.AllDatabases:
case ClearCacheMode.AllDatabasesAndFileSystem:
{
foreach (Database database in server.Databases)
{
server.Execute(XmlaBatchClearDatabaseCacheTemplate.FormatWith(database.ID));
}
}
break;
default:
throw new ApplicationException("Invalid ClearCacheMode [{0}]".FormatWith(procedureContext.ClearCacheMode));
}
}
}
public static void Initialize(ProcedureContext procedureContext)
{
#region Argument exceptions
if (procedureContext == null)
{
throw new ArgumentNullException("procedureContext");
}
#endregion
var insertCommand = @"
INSERT INTO [asqa].[Execution]
(
[BatchID],
[ExecutionID],
[BatchName],
[ExecutionStartTime],
[ConnectionUserName],
[ImpersonationUserName],
[SSASInstanceName],
[SSASInstanceVersion],
[SSASInstanceEdition],
[SSASInstanceConfig],
[SQLInstanceName],
[SQLInstanceVersion],
[SQLInstanceEdition],
[DatabaseName],
[CubeName],
[CubeMetadata],
[ClearCacheMode],
[Statement],
[ASQAServerVersion],
[ASQAServerConfig],
[ClientVersion],
[ClientType],
[SystemOperativeSystemName],
[SystemPhysicalMemory],
[SystemLogicalCpuCore]
)
VALUES
(
@batchID,
@executionID,
@batchName,
@executionStartTime,
@connectionUserName,
@impersonationUserName,
@ssasInstanceName,
@ssasInstanceVersion,
@ssasInstanceEdition,
@ssasInstanceConfig,
CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)),
CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(256)),
CAST(SERVERPROPERTY('Edition') AS NVARCHAR(256)),
@databaseName,
@cubeName,
@cubeMetadata,
@clearCacheMode,
@statement,
@asqaServerVersion,
@asqaServerConfig,
@clientVersion,
@clientType,
@systemOperativeSystemName,
@systemPhysicalMemory,
@systemLogicalCpuCore
)";
using (procedureContext.ImpersonationIdentity.Impersonate())
{
using (var connection = new SqlConnection(procedureContext.BatchConnectionString))
{
connection.Open();
using (var command = new SqlCommand(insertCommand, connection))
{
command.Parameters.Add(new SqlParameter("@batchID", SqlDbType.UniqueIdentifier)).Value = procedureContext.BatchID;
command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID;
command.Parameters.Add(new SqlParameter("@batchName", SqlDbType.NVarChar)).Value = procedureContext.BatchName;
command.Parameters.Add(new SqlParameter("@executionStartTime", SqlDbType.DateTime)).Value = DateTime.UtcNow;
command.Parameters.Add(new SqlParameter("@connectionUserName", SqlDbType.NVarChar)).Value = procedureContext.ConnectionUserName;
command.Parameters.Add(new SqlParameter("@impersonationUserName", SqlDbType.NVarChar)).Value = procedureContext.ImpersonationIdentity.Name;
command.Parameters.Add(new SqlParameter("@ssasInstanceName", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceName;
command.Parameters.Add(new SqlParameter("@ssasInstanceVersion", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceVersion.ToString();
command.Parameters.Add(new SqlParameter("@ssasInstanceEdition", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceEdition;
command.Parameters.Add(new SqlParameter("@ssasInstanceConfig", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceConfig;
command.Parameters.Add(new SqlParameter("@databaseName", SqlDbType.NVarChar)).Value = procedureContext.DatabaseName;
command.Parameters.Add(new SqlParameter("@cubeName", SqlDbType.NVarChar)).Value = procedureContext.CubeName;
command.Parameters.Add(new SqlParameter("@cubeMetadata", SqlDbType.NVarChar)).Value = procedureContext.CubeMetadata;
command.Parameters.Add(new SqlParameter("@clearCacheMode", SqlDbType.NVarChar)).Value = procedureContext.ClearCacheMode;
command.Parameters.Add(new SqlParameter("@statement", SqlDbType.NVarChar)).Value = procedureContext.Statement;
command.Parameters.Add(new SqlParameter("@asqaServerVersion", SqlDbType.NVarChar)).Value = procedureContext.ASQAServerVersion;
command.Parameters.Add(new SqlParameter("@asqaServerConfig", SqlDbType.NVarChar)).Value = procedureContext.ASQAServerConfig;
command.Parameters.Add(new SqlParameter("@clientVersion", SqlDbType.NVarChar)).Value = procedureContext.ClientVersion;
command.Parameters.Add(new SqlParameter("@clientType", SqlDbType.NVarChar)).Value = procedureContext.ClientType;
command.Parameters.Add(new SqlParameter("@systemOperativeSystemName", SqlDbType.NVarChar)).Value = procedureContext.SystemOperativeSystemName;
command.Parameters.Add(new SqlParameter("@systemPhysicalMemory", SqlDbType.BigInt)).Value = procedureContext.SystemPhysicalMemory;
command.Parameters.Add(new SqlParameter("@systemLogicalCpuCore", SqlDbType.Int)).Value = procedureContext.SystemLogicalCpuCore;
command.ExecuteNonQuery();
}
}
}
}
