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(); } }