public static string GetCurrentCommand()
{
AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP");
try
{
AdomdClient.AdomdRestrictionCollection restrictions = new AdomdClient.AdomdRestrictionCollection();
//a restriction on SESSION_ID causes it to return no rows: http://msdn.microsoft.com/en-us/library/ee301976(v=sql.105).aspx#id253
string sSessionID = Context.CurrentConnection.SessionID;
System.Data.DataSet dataSet = TimeoutUtility.GetSchemaDataSet(conn, "DISCOVER_SESSIONS", restrictions);
if (dataSet != null &&
dataSet.Tables.Count > 0 &&
dataSet.Tables[0].Rows.Count > 0)
{
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
if (string.Compare(sSessionID, Convert.ToString(row["SESSION_ID"]), true) == 0)
{
return(Convert.ToString(row["SESSION_LAST_COMMAND"]));
}
}
throw new Exception("Can't find the current command");
}
else
{
throw new Exception("Can't get the current command");
}
}
finally
{
conn.Close();
}
}
public DataTable Discover(string request, string restrictions, string properties)
{
Context.TraceEvent(100, 0, "Discover: Starting (" + request + ")");
XmlaClient client = createXmlaClientAndConnect();
DataTable dt = new DataTable();
// if no properties restriction is specified, default to using
// the current database
if (properties.Length == 0)
{
properties = string.Format("<CATALOG>{0}</CATALOG>", Context.CurrentDatabaseName);
}
try
{
string res;
TimeoutUtility.XmlaClientDiscover(client, request, restrictions, properties, out res, false, false, false);
dt = createDataTableFromXmla(res);
}
finally
{
client.Disconnect();
Context.TraceEvent(100, 0, "Discover: XML/A Connection disconnected");
}
Context.TraceEvent(100, 0, "Discover: Finished (" + dt.Rows.Count.ToString() + " rows returned");
return(dt);
}
public DataTable ForEachMeasureGroupInternal(string command, bool forEachPartition)
{
DataTable result = new DataTable();
Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
server.Connect("*");
Database db = server.Databases.GetByName(Context.CurrentDatabaseName);
foreach (Microsoft.AnalysisServices.Cube c in db.Cubes)
{
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + server.Name + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Cube=" + c.Name);
foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
{
if (forEachPartition)
{
foreach (Microsoft.AnalysisServices.Partition p in mg.Partitions)
{
//parameters don't appear to work with some DMV queries, so use string substitution
string sNewCommand = command;
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_NAME", p.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_ID", p.ID);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
cmd.CommandTimeout = 0;
Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, result);
}
}
else
{
//parameters don't appear to work with some DMV queries, so use string substitution
string sNewCommand = command;
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
cmd.CommandTimeout = 0;
Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, result);
}
}
conn.Close();
}
return(result);
}
public DataTable DiscoverXmlMetadataFull(string path, string whereClause, string restrictions)
{
XmlaClient xmlac = createXmlaClientAndConnect();
string xmlaResult;
string properties = "";
TimeoutUtility.XmlaClientDiscover(xmlac, "DISCOVER_XML_METADATA", restrictions, properties, out xmlaResult, false, false, false);
XmlaDiscoverParser dp = new XmlaDiscoverParser();
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlaResult);
return(dp.Parse(doc, path, Context.ExecuteForPrepare, whereClause));
}
public static DataTable ExecuteDrillthroughAndFixColumns(string sDrillthroughMDX)
{
AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP;");
try
{
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = sDrillthroughMDX;
DataTable tbl = new DataTable();
AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, tbl);
Dictionary <string, int> dictColumnNames = new Dictionary <string, int>(StringComparer.InvariantCultureIgnoreCase);
foreach (DataColumn col in tbl.Columns)
{
string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", "");
if (dictColumnNames.ContainsKey(sNewColumnName))
{
dictColumnNames[sNewColumnName]++;
}
else
{
dictColumnNames.Add(sNewColumnName, 1);
}
}
foreach (DataColumn col in tbl.Columns)
{
string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", "");
if (dictColumnNames[sNewColumnName] > 1)
{
sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('[') + 1).Replace("[", "").Replace("]", "").Replace("$", "");
}
if (!tbl.Columns.Contains(sNewColumnName))
{
col.ColumnName = sNewColumnName;
}
}
return(tbl);
}
finally
{
conn.Close();
}
}
public static DataTable ExecuteDrillthroughAndTranslateColumns(string sDrillthroughMDX)
{
Regex columnNameRegex = new Regex(@"\[(?<cube>[^]]*)]\.\[(?<level>[^]]*)]", RegexOptions.Compiled);
string connStr = "Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSP;Locale Identifier=" + Context.CurrentConnection.ClientCulture.LCID;
AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient(connStr);
Context.TraceEvent(999, 0, string.Format("ExecuteDrillthroughAndTranslateColumns ConnectionString: {0}", connStr));
try
{
Dictionary <string, string> translations = new Dictionary <string, string>();
// get level names
var resColl = new AdomdClient.AdomdRestrictionCollection();
resColl.Add("CUBE_SOURCE", "3"); // dimensions
resColl.Add("LEVEL_VISIBILITY", "3"); // visible and non-visible
resColl.Add("CUBE_NAME", Context.CurrentCube); // visible and non-visible
var dsLevels = conn.GetSchemaDataSet("MDSCHEMA_LEVELS", resColl);
foreach (DataRow dr in dsLevels.Tables[0].Rows)
{
var sColName = string.Format("[${0}.[{1}]", dr["DIMENSION_UNIQUE_NAME"].ToString().Substring(1), dr["LEVEL_NAME"].ToString());
if (!translations.ContainsKey(sColName))
{
translations.Add(sColName, dr["LEVEL_CAPTION"].ToString());
}
}
// get measure names
resColl.Clear();
resColl.Add("CUBE_NAME", Context.CurrentCube);
resColl.Add("MEASURE_VISIBILITY", 3); // visible and non-visible
var dsMeasures = conn.GetSchemaDataSet("MDSCHEMA_MEASURES", resColl);
foreach (DataRow dr in dsMeasures.Tables[0].Rows)
{
if (!translations.ContainsKey(string.Format("[{0}].[{1}]", dr["MEASUREGROUP_NAME"].ToString(), dr["MEASURE_NAME"].ToString())))
{
translations.Add(string.Format("[{0}].[{1}]", dr["MEASUREGROUP_NAME"].ToString(), dr["MEASURE_NAME"].ToString()), dr["MEASURE_CAPTION"].ToString());
}
}
// get dimension names
resColl.Clear();
resColl.Add("CUBE_NAME", Context.CurrentCube);
var dsDims = conn.GetSchemaDataSet("MDSCHEMA_DIMENSIONS", resColl);
AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = sDrillthroughMDX;
DataTable tbl = new DataTable();
AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, tbl);
// loop through the columns looking for duplicate translation names
Dictionary <string, int> dictColumnNames = new Dictionary <string, int>(StringComparer.InvariantCultureIgnoreCase);
foreach (DataColumn col in tbl.Columns)
{
var colKey = col.ColumnName.Substring(0, col.ColumnName.LastIndexOf(']') + 1);
if (translations.ContainsKey(colKey))
{
string sNewColumnName = translations[colKey];
if (dictColumnNames.ContainsKey(sNewColumnName))
{
dictColumnNames[sNewColumnName]++;
}
else
{
dictColumnNames.Add(sNewColumnName, 1);
}
}
else
{
Context.TraceEvent(999, 0, string.Format("The translation for the column '{0}' was not found", col.ColumnName));
}
}
foreach (DataColumn col in tbl.Columns)
{
var colKey = col.ColumnName.Substring(0, col.ColumnName.LastIndexOf(']') + 1);
var suffix = col.ColumnName.Substring(col.ColumnName.LastIndexOf("]") + 1);
if (translations.ContainsKey(colKey))
{
string sNewName = translations[colKey];
if (dictColumnNames[sNewName] > 1)
{
//if (string.IsNullOrWhiteSpace( suffix)){
//prefix with tablename
var m = columnNameRegex.Matches(col.ColumnName);
var dimName = m[0].Groups["cube"].Value.TrimStart('$');
var dimCaption = dsDims.Tables[0].Select(string.Format("DIMENSION_NAME = '{0}'", dimName))[0]["DIMENSION_CAPTION"].ToString();
sNewName = dimCaption + "." + sNewName + suffix;
//}
//else {
// col.ColumnName = sNewName + suffix;
//}
}
Context.TraceEvent(999, 0, string.Format("translating: '{0}' to '{1}'", col.ColumnName, sNewName));
col.ColumnName = sNewName;
}
}
//foreach (DataColumn col in tbl.Columns)
//{
// string sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('.') + 1).Replace("[", "").Replace("]", "");
// if (dictColumnNames[sNewColumnName] > 1)
// sNewColumnName = col.ColumnName.Substring(col.ColumnName.LastIndexOf('[') + 1).Replace("[", "").Replace("]", "").Replace("$", "");
// if (!tbl.Columns.Contains(sNewColumnName))
// col.ColumnName = sNewColumnName;
//}
return(tbl);
}
catch (Exception ex)
{
Context.TraceEvent(999, 0, string.Format("Unhandled Exception: {0}", ex.Message));
return(null);
}
finally
{
conn.Close();
}
}
