/// <summary> /// Clear both the Analysis Services cache and the active+standby system file cache in one sproc call /// </summary> public static void ClearAllCaches() { XmlaDiscover xmla = new XmlaDiscover(); xmla.ClearCache(); ClearFileSystemCache(true); }
//don't use this in the MDX script or it may return calculated measures from the previous processed version or blow up if the cube was not previously processed public static Set GetMeasureGroupCalculatedMeasures(string sMeasureGroupName) { StringBuilder sb = new StringBuilder(); XmlaDiscover discover = new XmlaDiscover(); DataTable table = discover.Discover("MDSCHEMA_MEASURES", "<CUBE_NAME>" + AMOHelpers.GetCurrentCubeName() + "</CUBE_NAME><MEASUREGROUP_NAME>" + sMeasureGroupName + "</MEASUREGROUP_NAME>"); foreach (DataRow row in table.Rows) { if (Convert.ToInt32(row["MEASURE_AGGREGATOR"]) == 127) { if (sb.Length > 0) { sb.Append(","); } sb.Append(row["MEASURE_UNIQUE_NAME"].ToString()); } } return(new Expression("{" + sb.ToString() + "}").CalculateMdxObject(null).ToSet()); }
//don't use this in the MDX script or it may return calculated measures from the previous processed version or blow up if the cube was not previously processed public static Set GetMeasureGroupCalculatedMeasures(string sMeasureGroupName) { StringBuilder sb = new StringBuilder(); XmlaDiscover discover = new XmlaDiscover(); DataTable table = discover.Discover("MDSCHEMA_MEASURES", "<CUBE_NAME>" + AMOHelpers.GetCurrentCubeName() + "</CUBE_NAME><MEASUREGROUP_NAME>" + sMeasureGroupName + "</MEASUREGROUP_NAME>"); foreach (DataRow row in table.Rows) { if (Convert.ToInt32(row["MEASURE_AGGREGATOR"]) == 127) { if (sb.Length > 0) sb.Append(","); sb.Append(row["MEASURE_UNIQUE_NAME"].ToString()); } } return new Expression("{" + sb.ToString() + "}").CalculateMdxObject(null).ToSet(); }
public DataTable DiscoverPartitionSlices(string cubeName, string measureGroupName) { DataTable dt; DataTable dtTemp; string props; XmlaDiscover xd = new XmlaDiscover(); int dimCount; int i; DataRow[] sameDimRows; string overlapText; bool notFirstDim = false; Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server(); server.Connect("*"); Database db = server.Databases.GetByName(Context.CurrentDatabaseName); Cube cube = db.Cubes.GetByName(cubeName); MeasureGroup mg = cube.MeasureGroups.GetByName(measureGroupName); props = "<DATABASE_NAME>" + Context.CurrentDatabaseName + "</DATABASE_NAME>"; props += "<CUBE_NAME>" + cubeName + "</CUBE_NAME><MEASURE_GROUP_NAME>" + measureGroupName + "</MEASURE_GROUP_NAME>"; //get info for the first partition in the measure group dt = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[0].Name + "</PARTITION_NAME>"); dt.Columns.Add("Overlap", System.Type.GetType("System.String")); dt.AcceptChanges(); dimCount = dt.Rows.Count; //get info for other partitions, if they exist if (mg.Partitions.Count > 1) { for (i = 1; i < mg.Partitions.Count; i++) { Context.CheckCancelled(); dtTemp = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[i].Name + "</PARTITION_NAME>"); dtTemp.Columns.Add("Overlap", System.Type.GetType("System.String")); dtTemp.AcceptChanges(); dt.Merge(dtTemp); } //work out if partitions overlap foreach(DataRow currentRow in dt.Rows) { Context.CheckCancelled(); if (currentRow["ATTRIBUTE_INDEXED"].ToString() == "true") { overlapText = ""; sameDimRows = dt.Select("DIMENSION_NAME='" + currentRow["DIMENSION_NAME"] + "' AND ATTRIBUTE_NAME='" + currentRow["ATTRIBUTE_NAME"] + "' AND PARTITION_NAME<>'" + currentRow["PARTITION_NAME"] + "' AND ATTRIBUTE_INDEXED='true'"); notFirstDim = false; foreach (DataRow dr in sameDimRows) { Context.CheckCancelled(); if ( ( (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) || ( (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) || ( (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) >= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) ) { if (notFirstDim) overlapText+=", "; overlapText+=dr["PARTITION_NAME"]; notFirstDim = true; } } currentRow["Overlap"] = overlapText; } dt.AcceptChanges(); } } server.Disconnect(); return dt; }
public DataTable DiscoverPartitionSlices(string cubeName, string measureGroupName) { DataTable dt; DataTable dtTemp; string props; XmlaDiscover xd = new XmlaDiscover(); int dimCount; int i; DataRow[] sameDimRows; string overlapText; bool notFirstDim = false; Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server(); server.Connect("*"); Database db = server.Databases.GetByName(Context.CurrentDatabaseName); Cube cube = db.Cubes.GetByName(cubeName); MeasureGroup mg = cube.MeasureGroups.GetByName(measureGroupName); props = "<DATABASE_NAME>" + Context.CurrentDatabaseName + "</DATABASE_NAME>"; props += "<CUBE_NAME>" + cubeName + "</CUBE_NAME><MEASURE_GROUP_NAME>" + measureGroupName + "</MEASURE_GROUP_NAME>"; //get info for the first partition in the measure group dt = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[0].Name + "</PARTITION_NAME>"); dt.Columns.Add("Overlap", System.Type.GetType("System.String")); dt.AcceptChanges(); dimCount = dt.Rows.Count; //get info for other partitions, if they exist if (mg.Partitions.Count > 1) { for (i = 1; i < mg.Partitions.Count; i++) { Context.CheckCancelled(); dtTemp = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[i].Name + "</PARTITION_NAME>"); dtTemp.Columns.Add("Overlap", System.Type.GetType("System.String")); dtTemp.AcceptChanges(); dt.Merge(dtTemp); } //work out if partitions overlap foreach (DataRow currentRow in dt.Rows) { Context.CheckCancelled(); if (currentRow["ATTRIBUTE_INDEXED"].ToString() == "true") { overlapText = ""; sameDimRows = dt.Select("DIMENSION_NAME='" + currentRow["DIMENSION_NAME"] + "' AND ATTRIBUTE_NAME='" + currentRow["ATTRIBUTE_NAME"] + "' AND PARTITION_NAME<>'" + currentRow["PARTITION_NAME"] + "' AND ATTRIBUTE_INDEXED='true'"); notFirstDim = false; foreach (DataRow dr in sameDimRows) { Context.CheckCancelled(); if ( ( (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) || ( (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) || ( (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) >= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) ) { if (notFirstDim) { overlapText += ", "; } overlapText += dr["PARTITION_NAME"]; notFirstDim = true; } } currentRow["Overlap"] = overlapText; } dt.AcceptChanges(); } } server.Disconnect(); return(dt); }