public DataSet GetSchemaDataSet(string schemaName, AdomdRestrictionCollection restrictions)
{
if (_type == AdomdType.AnalysisServices)
{
AsAdomdClient.AdomdRestrictionCollection coll = new AsAdomdClient.AdomdRestrictionCollection();
foreach (AdomdRestriction res in restrictions)
{
coll.Add(new AsAdomdClient.AdomdRestriction(res.Name, res.Value));
}
return(_conn.GetSchemaDataSet(schemaName, coll));
}
else
{
ExcelAdoMdConnections.ReturnDelegate <DataSet> f = delegate
{
ExcelAdomdClient.AdomdRestrictionCollection coll = new ExcelAdomdClient.AdomdRestrictionCollection();
foreach (AdomdRestriction res in restrictions)
{
coll.Add(new ExcelAdomdClient.AdomdRestriction(res.Name, res.Value));
}
return(_connExcel.GetSchemaDataSet(schemaName, coll));
};
return(f());
}
}
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();
}
}
private void LoadSessions()
{
Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 4, "Starting LoadSessions");
AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID);
conn.Open();
DataSet ds = conn.GetSchemaDataSet("DISCOVER_SESSIONS", null);
conn.Close();
foreach (DataRow dr in ds.Tables[0].Rows)
{
sessions.Add(Convert.ToInt32(dr["SESSION_SPID"]), Convert.ToString(dr["SESSION_USER_NAME"]));
}
Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 5, "Finishing LoadSessions");
}