public ADOMD.AdomdDataReader ExecuteReader(string qry, ADOMD.AdomdConnection cnn) { var cmd = new ADOMD.AdomdCommand(qry, cnn); ADOMD.AdomdDataReader reader = cmd.ExecuteReader(); return(reader); }
private static void RunAQuery(object o) { ParallelQueryThreadInfo info = null; try { info = (ParallelQueryThreadInfo)o; Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(info.connectionString); conn.Open(); try { CellSet queryCellset; Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(); queryCommand.CommandText = info.query; queryCommand.Connection = conn; queryCellset = queryCommand.ExecuteCellSet(); info.returnValue = queryCellset[0].Value.ToString(); } finally { conn.Close(); } } catch (Exception ex) { info.ex = ex; } finally { info.autoEvent.Set(); } }
public static object GetDBVal(string dbName) { object returnValue; string measuresText = ""; string dimensionsText = ""; Boolean addcomma = false; foreach (AMDS.Dimension d in Context.CurrentCube.Dimensions) { foreach (AMDS.Hierarchy h in d.AttributeHierarchies) { if (d.DimensionType == DimensionTypeEnum.Measure) { measuresText = h.CurrentMember.UniqueName; } else //eliminate comparison dimension from the query if ((h.CurrentMember.UniqueName != h.DefaultMember) && (!h.CurrentMember.UniqueName.Contains("[Comparison Operations]"))) { if (addcomma == false) { addcomma = true; } else { dimensionsText += ","; } dimensionsText += h.CurrentMember.UniqueName; } } } //if all the dimensions in default members do not compose where clause of the query if (dimensionsText != "") { dimensionsText = " where (" + dimensionsText + ")"; } string query = " select " + measuresText + " on 0 from [" + Context.CurrentCube.Name + "] " + dimensionsText; Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + Context.Server.Name + ";Catalog=" + dbName); conn.Open(); try { AMDC.CellSet queryCellset; Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(); queryCommand.CommandText = query; queryCommand.Connection = conn; queryCellset = queryCommand.ExecuteCellSet(); returnValue = queryCellset[0].Value; } finally { conn.Close(); } return(returnValue); }
private void PopulateCollection(MiningModel parentModel) { this.hashAttrIDs = new Hashtable(); AdomdCommand adomdCommand = new AdomdCommand(); adomdCommand.Connection = parentModel.ParentConnection; int num = 0; MiningModelColumnCollection.Enumerator enumerator = parentModel.Columns.GetEnumerator(); while (enumerator.MoveNext()) { MiningModelColumn current = enumerator.Current; if (current.IsTable) { MiningModelColumnCollection.Enumerator enumerator2 = current.Columns.GetEnumerator(); while (enumerator2.MoveNext()) { MiningModelColumn arg_54_0 = enumerator2.Current; num++; } } } adomdCommand.CommandText = string.Format(CultureInfo.InvariantCulture, MiningAttributeCollection.attribQueryStmt, new object[] { parentModel.Name }); AdomdDataReader adomdDataReader = adomdCommand.ExecuteReader(CommandBehavior.SequentialAccess); while (adomdDataReader.Read()) { int @int = adomdDataReader.GetInt32(MiningAttributeCollection.attIdIndex); string @string = adomdDataReader.GetString(MiningAttributeCollection.nameIndex); string string2 = adomdDataReader.GetString(MiningAttributeCollection.shortNameIndex); bool boolean = adomdDataReader.GetBoolean(MiningAttributeCollection.isInputIndex); bool boolean2 = adomdDataReader.GetBoolean(MiningAttributeCollection.isPredictableIndex); int int2 = adomdDataReader.GetInt32(MiningAttributeCollection.featureSelectionIndex); string string3 = adomdDataReader.GetString(MiningAttributeCollection.keyColumnIndex); string string4 = adomdDataReader.GetString(MiningAttributeCollection.valueColumnIndex); MiningAttribute miningAttribute = new MiningAttribute(parentModel); miningAttribute.attributeID = @int; miningAttribute.name = @string; miningAttribute.shortName = string2; miningAttribute.isInput = boolean; miningAttribute.isPredictable = boolean2; miningAttribute.featureSelection = (MiningFeatureSelection)int2; miningAttribute.keyColumn = this.ModelColumnFromName(string3, parentModel); miningAttribute.valueColumn = this.ModelColumnFromName(string4, parentModel); this.hashAttrIDs.Add(miningAttribute.name, miningAttribute.attributeID); this.arAttributesInternal.Add(miningAttribute); } adomdDataReader.Close(); adomdCommand.Dispose(); }
protected override int Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior) { AdomdCommand adomdCommand = command as AdomdCommand; if (adomdCommand != null) { adomdCommand.DataReaderConsumer = this; } int result = base.Fill(dataTable, command, behavior); this.AdjustDataTableName(dataTable); return(result); }
protected override int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior) { AdomdCommand adomdCommand = command as AdomdCommand; if (adomdCommand != null) { adomdCommand.DataReaderConsumer = this; } int result = base.Fill(dataSet, startRecord, maxRecords, srcTable, command, behavior); this.AdjustDataSet(dataSet); return(result); }
/* * Return results from query */ public AdomdDataReader GetQueryResult(string sQuery) { try { string sConnString = "Data Source=" + sServer + "; Initial Catalog=" + sCatalog; Microsoft.AnalysisServices.AdomdClient.AdomdConnection objConn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(sConnString); objConn.Open(); Microsoft.AnalysisServices.AdomdClient.AdomdCommand objCmd = objConn.CreateCommand(); objCmd.CommandText = sQuery; //Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objReader = objCmd.ExecuteReader(); //Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter objDataAdaptor = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(objCmd); Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection); /* * try * { * for (int i = 0; i < objDataReader.FieldCount; i++) * { * Console.Write(objDataReader.GetName(i) + "\t"); * } * Console.WriteLine(); * while (objDataReader.Read()) * { * for (int i = 0; i < objDataReader.FieldCount; i++) * { * object value = objDataReader.GetValue(i); * string strValue = (value == null) ? * string.Empty : value.ToString(); * Console.Write(strValue + "\t"); * } * Console.WriteLine(); * } * } * finally * { * objDataReader.Close(); * } */ return(objDataReader); } catch (Exception e) { Console.WriteLine(e.StackTrace); } return(null); }
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(); } }
internal MiningValueCollectionInternal(MiningModelColumn column) { this.internalObjectCollection = new ArrayList(); if (column.IsTable) { return; } AdomdCommand adomdCommand = new AdomdCommand(); adomdCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "SELECT DISTINCT {0} FROM [{1}]", new object[] { column.FullyQualifiedName, column.ParentMiningModel.Name }); adomdCommand.Connection = column.ParentMiningModel.ParentConnection; AdomdDataReader adomdDataReader = adomdCommand.ExecuteReader(); int num = -1; while (adomdDataReader.Read()) { num++; object objValue = adomdDataReader[0]; string content = column.Content; MiningValueType valueType = MiningValueType.Missing; if (num == 0 && content.IndexOf("key", StringComparison.OrdinalIgnoreCase) < 0) { valueType = MiningValueType.Missing; } else if (string.Compare(content, "discrete", StringComparison.OrdinalIgnoreCase) == 0 || string.Compare(content, "key", StringComparison.OrdinalIgnoreCase) == 0) { valueType = MiningValueType.Discrete; } else if (content.IndexOf("discretized", StringComparison.OrdinalIgnoreCase) == 0) { valueType = MiningValueType.Discretized; } else if (string.Compare(content, "continuous", StringComparison.OrdinalIgnoreCase) == 0) { valueType = MiningValueType.Continuous; } MiningValue newValue = new MiningValue(valueType, num, objValue); this.Add(newValue); } adomdDataReader.Close(); adomdDataReader.Dispose(); adomdCommand.Dispose(); }
private MemberCollection ExecuteMembersQuery(string memberMdxQuery, Level parentLevel, Member parentMember, int memberAxisPosition, int memberHierarhcyPosition) { if (memberAxisPosition < 0) { throw new ArgumentOutOfRangeException("memberAxisPosition"); } if (memberHierarhcyPosition < 0) { throw new ArgumentOutOfRangeException("memberHierarhcyPosition"); } AdomdConnection connection = this.Connection; if (connection == null) { throw new NotSupportedException(SR.NotSupportedWhenConnectionMissing); } AdomdUtils.CheckConnectionOpened(connection); AdomdCommand adomdCommand = new AdomdCommand(memberMdxQuery, connection); CellSet cellSet = adomdCommand.ExecuteCellSet(); if (memberAxisPosition >= cellSet.Axes.Count) { throw new ArgumentOutOfRangeException("memberAxisPosition"); } Axis axis = cellSet.Axes[memberAxisPosition]; IDSFDataSet axisDataset = axis.Set.AxisDataset; DataTable memberHierarchyDataTable = null; if (memberHierarhcyPosition != 0 || axisDataset.Count != 0) { if (memberHierarhcyPosition >= axisDataset.Count) { throw new ArgumentOutOfRangeException("memberHierarhcyPosition"); } memberHierarchyDataTable = axisDataset[memberHierarhcyPosition]; } return(new MemberCollection(connection, memberHierarchyDataTable, this.Name, parentLevel, parentMember)); }
internal AdomdParameterCollection(AdomdCommand parent) { this.parent = parent; this.items = new ArrayList(); }
public AdomdCommand(AsAdomdClient.AdomdCommand obj) { _obj = obj; }
public static object GetDBVal(string dbName) { object returnValue; string measuresText = ""; string dimensionsText = ""; Boolean addcomma = false; foreach (AMDS.Dimension d in Context.CurrentCube.Dimensions) { foreach (AMDS.Hierarchy h in d.AttributeHierarchies) { if (d.DimensionType == DimensionTypeEnum.Measure) { measuresText = h.CurrentMember.UniqueName; } else //eliminate comparison dimension from the query if ((h.CurrentMember.UniqueName != h.DefaultMember) && (!h.CurrentMember.UniqueName.Contains("[Comparison Operations]"))) { if (addcomma == false) addcomma = true; else dimensionsText += ","; dimensionsText += h.CurrentMember.UniqueName; } } } //if all the dimensions in default members do not compose where clause of the query if(dimensionsText!="") dimensionsText = " where (" + dimensionsText + ")"; string query=" select "+ measuresText+" on 0 from ["+Context.CurrentCube.Name+"] "+ dimensionsText; Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + Context.Server.Name+";Catalog="+dbName); conn.Open(); try { AMDC.CellSet queryCellset; Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(); queryCommand.CommandText = query; queryCommand.Connection = conn; queryCellset = queryCommand.ExecuteCellSet(); returnValue = queryCellset[0].Value; } finally { conn.Close(); } return returnValue; }
private static void CreateStringDistinctCountPartitions(string CubeName, string MeasureGroupName, string SetString, string PartitionGrouperExpressionString, string[] BoundaryValues) { int NumSubPartitions = BoundaryValues.Length + 1; if (AdomdServer.Context.ExecuteForPrepare) return; AdomdClient.Set s = null; AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName); conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114 conn.Open(); Server server = new Server(); server.Connect("*"); //connect to the current session... important to connect this way or else you will get a deadlock when you go to save the partition changes AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel try { AdomdServer.Context.TraceEvent(0, 0, "Retrieving Template Partition"); Database db = server.Databases.GetByName(AdomdServer.Context.CurrentDatabaseName); Cube cube = db.Cubes.GetByName(CubeName); MeasureGroup mg = cube.MeasureGroups.GetByName(MeasureGroupName); Partition template = mg.Partitions[0]; if (cube.State == AnalysisState.Unprocessed) throw new Exception("The cube [" + cube.Name + "] is unprocessed currently. Run ProcessStructure on it before partitioning it with ASSP."); //////////////////////////////////////////////////////////////// //Distinct Count stuff AdomdServer.Context.TraceEvent(0, 0, "Calculating min/max distinct count value"); Measure distinctMeasure = null; foreach (Measure m in mg.Measures) { if (m.AggregateFunction == AggregationFunction.DistinctCount) { if (distinctMeasure != null) throw new Exception("CreateStringDistinctCountPartitions does not support more than one distinct count measure on measure group " + mg.Name + "."); distinctMeasure = m; } } if (distinctMeasure == null) throw new Exception("Could not find a distinct count measure in measure group " + mg.Name + "."); ColumnBinding distinctColumnBinding = distinctMeasure.Source.Source as ColumnBinding; if (distinctColumnBinding == null) throw new Exception("Distinct count measure " + distinctMeasure.Name + " was not bound to a column."); AdomdServer.Context.TraceEvent(0, 0, "Boundary values: " + String.Join(", ", BoundaryValues)); AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel ////////////////////////////////////////////////////////////////// AdomdServer.Context.TraceEvent(0, 0, "Resolving Set"); //will show up under the User Defined trace event which is selected by default AdomdClient.CellSet cs; AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand(); cmd.Connection = conn; if (String.IsNullOrEmpty(PartitionGrouperExpressionString)) { cmd.CommandText = "select {} on 0, {" + SetString + "} on 1 " + "from [" + CubeName + "]"; try { cs = cmd.ExecuteCellSet(); } catch (Exception ex) { throw new Exception("The set specified was not valid: " + ex.Message); } } else { cmd.CommandText = "with member [Measures].[_ASSP_PartitionGrouper_] as " + PartitionGrouperExpressionString + " " + "select [Measures].[_ASSP_PartitionGrouper_] on 0, " + "{" + SetString + "} on 1 " + "from [" + CubeName + "]"; try { cs = cmd.ExecuteCellSet(); } catch (Exception ex) { throw new Exception("The set or partition grouper specified was not valid: " + ex.Message); } } s = cs.Axes[1].Set; AdomdServer.Context.TraceEvent(0, 0, "Determining Partition Scheme"); Dictionary<string, PartitionMetadata> dictPartitions = new Dictionary<string, PartitionMetadata>(); List<string> listPartitionNames = new List<string>(dictPartitions.Count); for (int iTuple = 0; iTuple < s.Tuples.Count; iTuple++) { AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel AdomdClient.Tuple t = s.Tuples[iTuple]; string tostring = t.ToString(); string sTupleUniqueName = GetTupleUniqueName(t); string sTupleName = GetTupleName(t); string sOriginalGrouper = sTupleUniqueName; if (!String.IsNullOrEmpty(PartitionGrouperExpressionString)) { //if a partition grouper has been specified, then group by it sTupleName = sOriginalGrouper = cs.Cells[0, iTuple].Value.ToString(); } for (int iSubPartition = 1; iSubPartition <= NumSubPartitions; iSubPartition++) { string sGrouper = sOriginalGrouper + " - DistinctCountSegment" + iSubPartition; if (!dictPartitions.ContainsKey(sGrouper)) { string sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition; sPartitionName = sPartitionName.Trim(); if (String.IsNullOrEmpty(PartitionGrouperExpressionString)) { //make sure partition name is unique int i = 1; while (listPartitionNames.Contains(sPartitionName)) { sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition + " - " + (++i); } } dictPartitions.Add(sGrouper, new PartitionMetadata(sPartitionName, t)); listPartitionNames.Add(sPartitionName); DataColumn dc = distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].Columns[distinctColumnBinding.ColumnID]; if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { dictPartitions[sGrouper].DistinctCountColumn = "[" + distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].ExtendedProperties["FriendlyName"].ToString() + "].[" + GetColumnName(dc) + "]"; } else { dictPartitions[sGrouper].DistinctCountColumn = dc.ExtendedProperties["ComputedColumnExpression"].ToString(); } dictPartitions[sGrouper].DistinctCountRangeEndInclusive = false; if (iSubPartition > 1) dictPartitions[sGrouper].DistinctCountRangeStart = "'" + BoundaryValues[iSubPartition - 2].Replace("'", "''") + "'"; if (iSubPartition < NumSubPartitions) dictPartitions[sGrouper].DistinctCountRangeEnd = "'" + BoundaryValues[iSubPartition - 1].Replace("'", "''") + "'"; } else { dictPartitions[sGrouper].tuples.Add(t); } } } //remove all existing partitions except template for (int iPartition = mg.Partitions.Count - 1; iPartition > 0; iPartition--) { mg.Partitions.RemoveAt(iPartition); } bool bNeedToDeleteTemplate = true; foreach (PartitionMetadata pm in dictPartitions.Values) { AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel AdomdServer.Context.TraceEvent(0, 0, "Building Partition: " + pm.PartitionName); if (template.ID == pm.PartitionName) { pm.Partition = template; bNeedToDeleteTemplate = false; pm.Partition.Process(ProcessType.ProcessClear); //unprocess it } else { pm.Partition = template.Clone(); } pm.Partition.Slice = pm.PartitionSlice; pm.Partition.Name = pm.PartitionName; pm.Partition.ID = pm.PartitionName; if (template.ID != pm.PartitionName) mg.Partitions.Add(pm.Partition); //if we're only building one partition, it must be the All member if (s.Tuples.Count == 1) pm.TupleMustBeOnlyAllMembers = true; string sQuery = ""; sQuery = pm.OldQueryDefinition; string sWhereClause = pm.NewPartitionWhereClause; sQuery += "\r\n" + sWhereClause; pm.Partition.Source = new QueryBinding(pm.Partition.DataSource.ID, sQuery); } if (bNeedToDeleteTemplate) mg.Partitions.Remove(template); AdomdServer.Context.TraceEvent(0, 0, "Saving changes"); mg.Update(UpdateOptions.ExpandFull); AdomdServer.Context.TraceEvent(0, 0, "Done creating partitions"); } finally { try { conn.Close(); } catch { } try { server.Disconnect(); } catch { } } }
public AdomdDataAdapter(AdomdCommand selectCommand) { this.SelectCommand = selectCommand; }
public static void CreateDistinctCountPartitions(string CubeName, string MeasureGroupName, string SetString, string PartitionGrouperExpressionString, int NumSubPartitions) { if (AdomdServer.Context.ExecuteForPrepare) return; AdomdClient.Set s = null; AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName); conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114 conn.Open(); Server server = new Server(); server.Connect("*"); //connect to the current session... important to connect this way or else you will get a deadlock when you go to save the partition changes AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel try { AdomdServer.Context.TraceEvent(0, 0, "Retrieving Template Partition"); Database db = server.Databases.GetByName(AdomdServer.Context.CurrentDatabaseName); Cube cube = db.Cubes.GetByName(CubeName); MeasureGroup mg = cube.MeasureGroups.GetByName(MeasureGroupName); Partition template = mg.Partitions[0]; if (cube.State == AnalysisState.Unprocessed) throw new Exception("The cube [" + cube.Name + "] is unprocessed currently. Run ProcessStructure on it before partitioning it with ASSP."); //////////////////////////////////////////////////////////////// //Distinct Count stuff AdomdServer.Context.TraceEvent(0, 0, "Calculating min/max distinct count value"); Measure distinctMeasure = null; foreach (Measure m in mg.Measures) { if (m.AggregateFunction == AggregationFunction.DistinctCount) { if (distinctMeasure != null) throw new Exception("CreateDistinctCountPartitions does not support more than one distinct count measure on measure group " + mg.Name + "."); distinctMeasure = m; } } if (distinctMeasure == null) throw new Exception("Could not find a distinct count measure in measure group " + mg.Name + "."); ColumnBinding distinctColumnBinding = distinctMeasure.Source.Source as ColumnBinding; if (distinctColumnBinding == null) throw new Exception("Distinct count measure " + distinctMeasure.Name + " was not bound to a column."); MeasureGroupAttribute distinctMGDimensionAttribute = null; foreach (MeasureGroupDimension mgDim in mg.Dimensions) { if (mgDim is RegularMeasureGroupDimension) { MeasureGroupAttribute mga = PartitionMetadata.GetGranularityAttribute(mgDim); if (mga.KeyColumns.Count == 1) { ColumnBinding cb = mga.KeyColumns[0].Source as ColumnBinding; if (cb.ColumnID == distinctColumnBinding.ColumnID && cb.TableID == distinctColumnBinding.TableID) { distinctMGDimensionAttribute = mga; break; } } AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel } } if (distinctMGDimensionAttribute == null) throw new Exception("Couldn't find a dimension joined to this measure group on the distinct count column [" + distinctColumnBinding.TableID + "].[" + distinctColumnBinding.ColumnID + "]."); if (distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.Integer && distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.BigInt && distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.UnsignedInt && distinctMGDimensionAttribute.KeyColumns[0].DataType != System.Data.OleDb.OleDbType.UnsignedBigInt) throw new Exception("ASSP encountered a problem partitioning on distinct count column [" + distinctColumnBinding.TableID + "].[" + distinctColumnBinding.ColumnID + "] as ASSP only allows partitioning on a distinct count measure of data type Int or BigInt (or the unsigned equivalents) with CreateDistinctCountPartitions. Use CreateStringDistinctCountPartitions which allows boundary values to be passed in."); string sDistinctCountCubeDim = "[" + distinctMGDimensionAttribute.Parent.CubeDimension.Name + "]"; string sDistinctCountAttribute = sDistinctCountCubeDim + ".[" + distinctMGDimensionAttribute.Attribute.Name + "]"; string sDistinctCountLevel = sDistinctCountAttribute + ".[" + distinctMGDimensionAttribute.Attribute.Name + "]"; AdomdClient.CellSet distinctKeysCS; AdomdClient.AdomdCommand distinctKeysCmd = new AdomdClient.AdomdCommand(); distinctKeysCmd.Connection = conn; //doing a min and max across 20 million members took 14 minutes, so only scan the top and bottom million should should give us a pretty good estimate in a reasonable time unless the sorting is completely strange distinctKeysCmd.CommandText = @" with member [Measures].[_MinKey_] as Min(Head(" + sDistinctCountLevel + ".Members,1000000), " + sDistinctCountAttribute + @".CurrentMember.Properties(""Key0"", TYPED)) member [Measures].[_MaxKey_] as Max(Tail(" + sDistinctCountLevel + ".Members,1000000), " + sDistinctCountAttribute + @".CurrentMember.Properties(""Key0"", TYPED)) select {[Measures].[_MinKey_], [Measures].[_MaxKey_]} on 0 from [" + CubeName + @"] "; try { distinctKeysCS = distinctKeysCmd.ExecuteCellSet(); } catch (Exception ex) { throw new Exception("Could not retrieve the min and max key value for the dimension matching the distinct count measure: " + ex.Message); } long lngMinDistinctValue = Convert.ToInt64(distinctKeysCS.Cells[0].Value); long lngMaxDistinctValue = Convert.ToInt64(distinctKeysCS.Cells[1].Value); AdomdServer.Context.TraceEvent(0, 0, "Min distinct value: " + lngMinDistinctValue); AdomdServer.Context.TraceEvent(0, 0, "Max distinct value: " + lngMaxDistinctValue); AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel ////////////////////////////////////////////////////////////////// AdomdServer.Context.TraceEvent(0, 0, "Resolving Set"); //will show up under the User Defined trace event which is selected by default AdomdClient.CellSet cs; AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand(); cmd.Connection = conn; if (String.IsNullOrEmpty(PartitionGrouperExpressionString)) { cmd.CommandText = "select {} on 0, {" + SetString + "} on 1 " + "from [" + CubeName + "]"; try { cs = cmd.ExecuteCellSet(); } catch (Exception ex) { throw new Exception("The set specified was not valid: " + ex.Message); } } else { cmd.CommandText = "with member [Measures].[_ASSP_PartitionGrouper_] as " + PartitionGrouperExpressionString + " " + "select [Measures].[_ASSP_PartitionGrouper_] on 0, " + "{" + SetString + "} on 1 " + "from [" + CubeName + "]"; try { cs = cmd.ExecuteCellSet(); } catch (Exception ex) { throw new Exception("The set or partition grouper specified was not valid: " + ex.Message); } } s = cs.Axes[1].Set; AdomdServer.Context.TraceEvent(0, 0, "Determining Partition Scheme"); Dictionary<string, PartitionMetadata> dictPartitions = new Dictionary<string, PartitionMetadata>(); List<string> listPartitionNames = new List<string>(dictPartitions.Count); for (int iTuple = 0; iTuple < s.Tuples.Count; iTuple++) { AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel AdomdClient.Tuple t = s.Tuples[iTuple]; string tostring = t.ToString(); string sTupleUniqueName = GetTupleUniqueName(t); string sTupleName = GetTupleName(t); string sOriginalGrouper = sTupleUniqueName; if (!String.IsNullOrEmpty(PartitionGrouperExpressionString)) { //if a partition grouper has been specified, then group by it sTupleName = sOriginalGrouper = cs.Cells[0, iTuple].Value.ToString(); } for (int iSubPartition = 1; iSubPartition <= NumSubPartitions; iSubPartition++) { string sGrouper = sOriginalGrouper + " - DistinctCountSegment" + iSubPartition; if (!dictPartitions.ContainsKey(sGrouper)) { string sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition; sPartitionName = sPartitionName.Trim(); if (String.IsNullOrEmpty(PartitionGrouperExpressionString)) { //make sure partition name is unique int i = 1; while (listPartitionNames.Contains(sPartitionName)) { sPartitionName = mg.Name + " - " + sTupleName + " - DistinctCountSegment" + iSubPartition + " - " + (++i); } } dictPartitions.Add(sGrouper, new PartitionMetadata(sPartitionName, t)); listPartitionNames.Add(sPartitionName); DataColumn dc = distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].Columns[distinctColumnBinding.ColumnID]; if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { dictPartitions[sGrouper].DistinctCountColumn = "[" + distinctMeasure.ParentCube.DataSourceView.Schema.Tables[distinctColumnBinding.TableID].ExtendedProperties["FriendlyName"].ToString() + "].[" + GetColumnName(dc) + "]"; } else { dictPartitions[sGrouper].DistinctCountColumn = dc.ExtendedProperties["ComputedColumnExpression"].ToString(); } if (iSubPartition > 1) dictPartitions[sGrouper].DistinctCountRangeStart = (((lngMaxDistinctValue - lngMinDistinctValue) / NumSubPartitions) * (iSubPartition - 1) + lngMinDistinctValue + 1).ToString(); if (iSubPartition < NumSubPartitions) dictPartitions[sGrouper].DistinctCountRangeEnd = (((lngMaxDistinctValue - lngMinDistinctValue) / NumSubPartitions) * iSubPartition + lngMinDistinctValue).ToString(); } else { dictPartitions[sGrouper].tuples.Add(t); } } } //remove all existing partitions except template for (int iPartition = mg.Partitions.Count - 1; iPartition > 0; iPartition--) { mg.Partitions.RemoveAt(iPartition); } bool bNeedToDeleteTemplate = true; foreach (PartitionMetadata pm in dictPartitions.Values) { AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel AdomdServer.Context.TraceEvent(0, 0, "Building Partition: " + pm.PartitionName); if (template.ID == pm.PartitionName) { pm.Partition = template; bNeedToDeleteTemplate = false; pm.Partition.Process(ProcessType.ProcessClear); //unprocess it } else { pm.Partition = template.Clone(); } pm.Partition.Slice = pm.PartitionSlice; pm.Partition.Name = pm.PartitionName; pm.Partition.ID = pm.PartitionName; if (template.ID != pm.PartitionName) mg.Partitions.Add(pm.Partition); //if we're only building one partition, it must be the All member if (s.Tuples.Count == 1) pm.TupleMustBeOnlyAllMembers = true; string sQuery = ""; sQuery = pm.OldQueryDefinition; string sWhereClause = pm.NewPartitionWhereClause; sQuery += "\r\n" + sWhereClause; pm.Partition.Source = new QueryBinding(pm.Partition.DataSource.ID, sQuery); } if (bNeedToDeleteTemplate) mg.Partitions.Remove(template); AdomdServer.Context.TraceEvent(0, 0, "Saving changes"); mg.Update(UpdateOptions.ExpandFull); AdomdServer.Context.TraceEvent(0, 0, "Done creating partitions"); } finally { try { conn.Close(); } catch { } try { server.Disconnect(); } catch { } } }
public static void CreatePartitions(string CubeName, string MeasureGroupName, string SetString, string PartitionGrouperExpressionString) { if (AdomdServer.Context.ExecuteForPrepare) return; AdomdClient.Set s = null; AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName); conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114 conn.Open(); Server server = new Server(); server.Connect("*"); //connect to the current session... important to connect this way or else you will get a deadlock when you go to save the partition changes AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel try { AdomdServer.Context.TraceEvent(0, 0, "Retrieving Template Partition"); Database db = server.Databases.GetByName(AdomdServer.Context.CurrentDatabaseName); Cube cube = db.Cubes.GetByName(CubeName); MeasureGroup mg = cube.MeasureGroups.GetByName(MeasureGroupName); Partition template = mg.Partitions[0]; if (cube.State == AnalysisState.Unprocessed) throw new Exception("The cube [" + cube.Name + "] is unprocessed currently. Run ProcessStructure on it before partitioning it with ASSP."); AdomdServer.Context.TraceEvent(0, 0, "Resolving Set"); //will show up under the User Defined trace event which is selected by default AdomdClient.CellSet cs; AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand(); cmd.Connection = conn; if (String.IsNullOrEmpty(PartitionGrouperExpressionString)) { cmd.CommandText = "select {} on 0, {" + SetString + "} on 1 " + "from [" + CubeName + "]"; try { cs = cmd.ExecuteCellSet(); } catch (Exception ex) { throw new Exception("The set specified was not valid: " + ex.Message); } } else { cmd.CommandText = "with member [Measures].[_ASSP_PartitionGrouper_] as " + PartitionGrouperExpressionString + " " + "select [Measures].[_ASSP_PartitionGrouper_] on 0, " + "{" + SetString + "} on 1 " + "from [" + CubeName + "]"; try { cs = cmd.ExecuteCellSet(); } catch (Exception ex) { throw new Exception("The set or partition grouper specified was not valid: " + ex.Message); } } s = cs.Axes[1].Set; AdomdServer.Context.TraceEvent(0, 0, "Determining Partition Scheme"); Dictionary<string, PartitionMetadata> dictPartitions = new Dictionary<string, PartitionMetadata>(); List<string> listPartitionNames = new List<string>(dictPartitions.Count); for (int iTuple = 0; iTuple < s.Tuples.Count; iTuple++) { AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel AdomdClient.Tuple t = s.Tuples[iTuple]; string tostring = t.ToString(); string sTupleUniqueName = GetTupleUniqueName(t); string sTupleName = GetTupleName(t); string sGrouper = sTupleUniqueName; if (!String.IsNullOrEmpty(PartitionGrouperExpressionString)) { //if a partition grouper has been specified, then group by it sTupleName = sGrouper = cs.Cells[0, iTuple].Value.ToString(); } if (!dictPartitions.ContainsKey(sGrouper)) { string sPartitionName = mg.Name + " - " + sTupleName; sPartitionName = sPartitionName.Trim(); if (String.IsNullOrEmpty(PartitionGrouperExpressionString)) { //make sure partition name is unique int i = 1; while (listPartitionNames.Contains(sPartitionName)) { sPartitionName = mg.Name + " - " + sTupleName + " " + (++i); } } dictPartitions.Add(sGrouper, new PartitionMetadata(sPartitionName, t)); listPartitionNames.Add(sPartitionName); } else { dictPartitions[sGrouper].tuples.Add(t); } } //remove all existing partitions except template for (int iPartition = mg.Partitions.Count - 1; iPartition > 0; iPartition--) { mg.Partitions.RemoveAt(iPartition); } bool bNeedToDeleteTemplate = true; foreach (PartitionMetadata pm in dictPartitions.Values) { AdomdServer.Context.CheckCancelled(); //could be a bit long running, so allow user to cancel AdomdServer.Context.TraceEvent(0, 0, "Building Partition: " + pm.PartitionName); if (template.ID == pm.PartitionName) { pm.Partition = template; bNeedToDeleteTemplate = false; pm.Partition.Process(ProcessType.ProcessClear); //unprocess it AdomdServer.Context.TraceEvent(0, 0, "ProcessClear partition"); } else { pm.Partition = template.Clone(); AdomdServer.Context.TraceEvent(0, 0, "cloned partition"); } pm.Partition.Slice = pm.PartitionSlice; pm.Partition.Name = pm.PartitionName; pm.Partition.ID = pm.PartitionName; if (template.ID != pm.PartitionName) mg.Partitions.Add(pm.Partition); //if we're only building one partition, it must be the All member if (s.Tuples.Count == 1) pm.TupleMustBeOnlyAllMembers = true; string sQuery = ""; sQuery = pm.OldQueryDefinition; string sWhereClause = pm.NewPartitionWhereClause; sQuery += "\r\n" + sWhereClause; pm.Partition.Source = new QueryBinding(pm.Partition.DataSource.ID, sQuery); } if (bNeedToDeleteTemplate) mg.Partitions.Remove(template); AdomdServer.Context.TraceEvent(0, 0, "Saving changes"); mg.Update(UpdateOptions.ExpandFull); AdomdServer.Context.TraceEvent(0, 0, "Done creating partitions"); } catch (Exception ex) { AdomdServer.Context.TraceEvent(0, 0, "error: " + ex.Message + " - " + ex.StackTrace); throw ex; } finally { try { conn.Close(); } catch { } try { server.Disconnect(); } catch { } } }
//as a workaround to a bug: https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=260636&siteid=68&wa=wsignin1.0 private static object[] GetMemberKeys(AdomdClient.Member m, int iNumKeys) { AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + AdomdServer.Context.CurrentDatabaseName); conn.ShowHiddenObjects = true; //ShowHiddenObjects=true allows you to see properties (like member.ParentLevel) of dimension attributes which aren't visible: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265114 conn.Open(); try { StringBuilder sCmd = new StringBuilder(); sCmd.AppendLine("with"); for (int i = 0; i < iNumKeys; i++) { sCmd.AppendLine("member [Measures].[_ASSP_MemberKey" + i + "] as " + m.ParentLevel.ParentHierarchy.UniqueName + ".CurrentMember.Properties('Key" + i + "')"); } sCmd.Append("select {"); for (int i = 0; i < iNumKeys; i++) { if (i > 0) sCmd.Append(", "); sCmd.Append("[Measures].[_ASSP_MemberKey" + i + "]"); } sCmd.AppendLine("} on 0,"); sCmd.Append(m.UniqueName).AppendLine(" on 1"); sCmd.Append("from [").Append(m.ParentLevel.ParentHierarchy.ParentDimension.ParentCube.Name).AppendLine("]"); AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand(); cmd.Connection = conn; cmd.CommandText = sCmd.ToString(); AdomdClient.CellSet cs = cmd.ExecuteCellSet(); List<object> keys = new List<object>(cs.Cells.Count); foreach (AdomdClient.Cell c in cs.Cells) { keys.Add(c.CellProperties["FORMATTED_VALUE"].Value); //the FORMATTED_VALUE will be null if it should be null } return keys.ToArray(); } finally { try { conn.Close(); } catch { } } }
private void LoadExistingMemoryDimensionSignatures() { Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 1, "Starting LoadExistingMemoryDimensionSignatures"); //retrieve all the existing dimension members AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand("with member [Measures].[Must Have A Measure] as null select [Measures].[Must Have A Measure] on 0, Leaves([Memory]) properties MEMBER_CAPTION, [Memory].[Memory Name].[Key0] on 1 from [$Memory]"); try { cmd.Connection = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + DATABASE_NAME); cmd.Connection.Open(); cmd.Properties.Add("ReturnCellProperties", true); AdomdClient.AdomdDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if (!Convert.IsDBNull(dr["[Memory].[Memory Name].[Memory Name].[Key]"])) //SSAS Unknown member will have null key { DateTime dt; string signature = ObjToString(dr["[Memory].[Memory Name].[Memory Name].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[User].[User].[MEMBER_CAPTION]"]) + "\r\n" + (string.IsNullOrEmpty(ObjToString(dr["[Memory].[Creation Time].[Creation Time].[MEMBER_CAPTION]"])) || !DateTime.TryParse(Convert.ToString(dr["[Memory].[Creation Time].[Creation Time].[MEMBER_CAPTION]"]), out dt) ? "" : Convert.ToDateTime(dr["[Memory].[Creation Time].[Creation Time].[MEMBER_CAPTION]"]).ToString("s")) + "\r\n" + ObjToString(dr["[Memory].[Shrinkable].[Shrinkable].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Folder 1].[Folder 1].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Level 1].[Level 1].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Folder 2].[Folder 2].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Level 2].[Level 2].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Folder 3].[Folder 3].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Level 3].[Level 3].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Folder 4].[Folder 4].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[Level 4].[Level 4].[MEMBER_CAPTION]"]) + "\r\n" + ObjToString(dr["[Memory].[File Extension].[File Extension].[MEMBER_CAPTION]"]); long key = Convert.ToInt64(dr["[Memory].[Memory Name].[Memory Name].[Key]"]); if (!dictExistingMemoryRowID.ContainsKey(signature)) { dictExistingMemoryRowID.Add(signature, key); } } } dr.Close(); } catch { Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 2, "Caught error in LoadExistingMemoryDimensionSignatures"); } finally { try { cmd.Connection.Close(); } catch { } } Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 3, "Finishing LoadExistingMemoryDimensionSignatures"); }
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 ExecuteMdx(string mdx) { AdomdCommand adomdCommand = new AdomdCommand(mdx, this.connection); adomdCommand.ExecuteNonQuery(); }
public void CreateMemoryUsageDatabase() { svr = new Microsoft.AnalysisServices.Server(); svr.Connect("*"); //"Data Source=" + AdomdServer.Context.CurrentServerID); //connect to a new session... otherwise the changes that are saved under this connection won't be committed until the sproc ends... and we need yet other connections to be able to see the changes prior to the sproc completing db = svr.Databases.FindByName(DATABASE_NAME); bool bExists = (db != null); if (bExists) //TODO: deal with upgrade { try { //find max MemoryRowID AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand("with member MaxMemoryRowID as Tail([Memory].[Memory Name].[Memory Name].Members,1).Item(0).Properties('Key0') select MaxMemoryRowID on 0 from [Memory Usage]"); cmd.Connection = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID + ";Initial Catalog=" + DATABASE_NAME); cmd.Connection.Open(); iMaxMemoryRowID = long.Parse(cmd.ExecuteCellSet().Cells[0].Value.ToString()); cmd.Connection.Close(); } catch { } //find the current partition MeasureGroup measureGroup = db.Cubes[DATABASE_NAME].MeasureGroups[DATABASE_NAME]; try { foreach (Partition p in measureGroup.Partitions) { if (int.Parse(p.ID.Split(new char[] { ' ' })[1]) > int.Parse(CurrentPartitionID.Split(new char[] { ' ' })[1])) { this.CurrentPartitionID = p.ID; } } } catch { } Partition currentPartition = measureGroup.Partitions[CurrentPartitionID]; int iNumSnapshots = int.Parse(currentPartition.Annotations[NUM_SNAPSHOTS_ANNOTATION_NAME].Value.InnerText); if (SNAPSHOTS_PER_PARTITION <= iNumSnapshots) { //create a new partition int iHighestPartitionNumber = int.Parse(CurrentPartitionID.Split(new char[] { ' ' })[1]); this.CurrentPartitionID = PARTITION_NAME + " " + (iHighestPartitionNumber + 1).ToString("0000"); CreateNewPartition(measureGroup); } else { //keep same partition... need to update the annotation currentPartition.Annotations[NUM_SNAPSHOTS_ANNOTATION_NAME].Value.InnerText = (iNumSnapshots + 1).ToString(); } measureGroup.Update(UpdateOptions.ExpandFull); return; //don't continue because it already exists } //create the database because it doesn't exist db = svr.Databases.Add(DATABASE_NAME); // Create the data source DataSource ds = db.DataSources.Add(DATABASE_NAME); ds.ConnectionString = "NotNeeded"; //all that's needed is a non-empty string // Create the data source view. Even thought it only uses out-of-line bindings, this is still necessary DataSourceView dsv = db.DataSourceViews.Add(DATABASE_NAME); dsv.DataSourceID = DATABASE_NAME; dsv.Schema = new DataSet(DATABASE_NAME); dsv.Schema.Locale = System.Globalization.CultureInfo.CurrentCulture; DataTable t = dsv.Schema.Tables.Add(TABLE_NAME); t.Columns.Add("MemoryID", typeof(long)); t.Columns.Add("MemoryName", typeof(string)); t.Columns.Add("SPID", typeof(int)); t.Columns.Add("CreationTime", typeof(DateTime)); t.Columns.Add("MemoryUsed", typeof(long)); t.Columns.Add("MemoryAllocated", typeof(long)); t.Columns.Add("MemoryAllocBase", typeof(long)); t.Columns.Add("MemoryAllocFromAlloc", typeof(long)); t.Columns.Add("ElementCount", typeof(long)); t.Columns.Add("Shrinkable", typeof(string)); t.Columns.Add("ShrinkableMemory", typeof(long)); t.Columns.Add("Folder1", typeof(string)); t.Columns.Add("Folder2", typeof(string)); t.Columns.Add("Folder3", typeof(string)); t.Columns.Add("Folder4", typeof(string)); t.Columns.Add("Level1", typeof(string)); t.Columns.Add("Level2", typeof(string)); t.Columns.Add("Level3", typeof(string)); t.Columns.Add("Level4", typeof(string)); t.Columns.Add("SnapshotDate", typeof(DateTime)); t.Columns.Add("MemoryCount", typeof(long)); t.Columns.Add("MemoryRowID", typeof(long)); t.Columns.Add("FileExtension", typeof(string)); t.Columns.Add("User", typeof(string)); t.Columns.Add("DiskUsed", typeof(long)); t.Columns.Add("DataDiskUsed", typeof(long)); t.Columns.Add("AggDiskUsed", typeof(long)); t.Columns.Add("IndexDiskUsed", typeof(long)); // Create the Date dimension Dimension dim = db.Dimensions.Add("Snapshot Date"); dim.Type = DimensionType.Time; dim.UnknownMember = UnknownMemberBehavior.Hidden; dim.AttributeAllMemberName = "All"; dim.Source = new DataSourceViewBinding(DATABASE_NAME); dim.StorageMode = DimensionStorageMode.Molap; DimensionAttribute attr; attr = dim.Attributes.Add("Snapshot Date"); attr.Usage = AttributeUsage.Key; attr.Type = AttributeType.Date; attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "SnapshotDate")); // Create the Memory dimension dim = db.Dimensions.Add("Memory"); dim.Type = DimensionType.Regular; dim.UnknownMember = UnknownMemberBehavior.Hidden; dim.AttributeAllMemberName = "All"; dim.Source = new DataSourceViewBinding(DATABASE_NAME); dim.StorageMode = DimensionStorageMode.Molap; attr = dim.Attributes.Add("Memory Name"); attr.Usage = AttributeUsage.Key; attr.OrderBy = OrderBy.Name; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "MemoryRowID")); //MemoryID won't be unique across snapshots... MemoryRowID is an incrementing ID field we create attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "MemoryName"); attr.AttributeHierarchyOptimizedState = OptimizationType.NotOptimized; //is expensive and not used in reports attr = dim.Attributes.Add("User"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "User")); attr = dim.Attributes.Add("Creation Time"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "CreationTime")); //BaseObjectType isn't helpful, and it causes the dimension size to increase because there are more distinct combinations which requires more MemoryRowID values //attr = dim.Attributes.Add("Base Object Type"); //attr.OrderBy = OrderBy.Key; //attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "BaseObjectType")); attr = dim.Attributes.Add("Shrinkable"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Shrinkable")); attr = dim.Attributes.Add("Folder 1"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr = dim.Attributes.Add("Folder 2"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder2")); attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "Folder2"); attr = dim.Attributes.Add("Folder 3"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder3")); attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "Folder3"); attr = dim.Attributes.Add("Folder 4"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder3")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level3")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder4")); attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "Folder4"); attr = dim.Attributes.Add("Level 1"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level1")); attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "Level1"); attr = dim.Attributes.Add("Level 2"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level2")); attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "Level2"); attr = dim.Attributes.Add("Level 3"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder3")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level3")); attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "Level3"); attr = dim.Attributes.Add("Level 4"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level1")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level2")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder3")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level3")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Folder4")); attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "Level4")); attr.NameColumn = CreateDataItem(dsv, TABLE_NAME, "Level4"); attr = dim.Attributes.Add("File Extension"); attr.OrderBy = OrderBy.Key; attr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "FileExtension")); dim.Attributes["Level 1"].AttributeRelationships.Add("Folder 1"); dim.Attributes["Level 2"].AttributeRelationships.Add("Folder 2"); dim.Attributes["Level 3"].AttributeRelationships.Add("Folder 3"); dim.Attributes["Level 4"].AttributeRelationships.Add("Folder 4"); dim.Attributes["Folder 2"].AttributeRelationships.Add("Level 1"); dim.Attributes["Folder 3"].AttributeRelationships.Add("Level 2"); dim.Attributes["Folder 4"].AttributeRelationships.Add("Level 3"); //no need to specify the attribute relationships that are from the key attribute... AMO takes care of those Hierarchy hier = dim.Hierarchies.Add("Tree"); hier.Levels.Add("Folder 1").SourceAttributeID = "Folder 1"; hier.Levels.Add("Level 1").SourceAttributeID = "Level 1"; hier.Levels.Add("Folder 2").SourceAttributeID = "Folder 2"; hier.Levels.Add("Level 2").SourceAttributeID = "Level 2"; hier.Levels.Add("Folder 3").SourceAttributeID = "Folder 3"; hier.Levels.Add("Level 3").SourceAttributeID = "Level 3"; hier.Levels.Add("Folder 4").SourceAttributeID = "Folder 4"; hier.Levels.Add("Level 4").SourceAttributeID = "Level 4"; foreach (Level l in hier.Levels) { l.HideMemberIf = HideIfValue.NoName; } // Create the Adventure Works cube Cube cube = db.Cubes.Add(DATABASE_NAME); cube.DefaultMeasure = "[Memory Used]"; cube.Source = new DataSourceViewBinding(DATABASE_NAME); cube.StorageMode = StorageMode.Molap; dim = db.Dimensions.GetByName("Snapshot Date"); cube.Dimensions.Add(dim.ID); dim = db.Dimensions.GetByName("Memory"); cube.Dimensions.Add(dim.ID); MeasureGroup mg = cube.MeasureGroups.Add(DATABASE_NAME); mg.StorageMode = StorageMode.Molap; mg.ProcessingMode = ProcessingMode.Regular; mg.Type = MeasureGroupType.Regular; Measure meas; meas = mg.Measures.Add("Memory Used"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "MemoryUsed"); meas = mg.Measures.Add("Memory Allocated"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "MemoryAllocated"); meas = mg.Measures.Add("Memory Alloc Base"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "MemoryAllocBase"); meas = mg.Measures.Add("Memory Alloc From Alloc"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "MemoryAllocFromAlloc"); meas = mg.Measures.Add("Element Count"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "ElementCount"); meas = mg.Measures.Add("Shrinkable Memory"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "ShrinkableMemory"); meas = mg.Measures.Add("Memory Count"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "MemoryCount"); meas = mg.Measures.Add("Disk Used"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "DiskUsed"); meas = mg.Measures.Add("Data Disk Used"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "DataDiskUsed"); meas = mg.Measures.Add("Agg Disk Used"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "AggDiskUsed"); meas = mg.Measures.Add("Index Disk Used"); meas.AggregateFunction = AggregationFunction.LastChild; meas.FormatString = "#,#"; meas.Source = CreateDataItem(dsv, TABLE_NAME, "IndexDiskUsed"); CubeDimension cubeDim; RegularMeasureGroupDimension regMgDim; MeasureGroupAttribute mgAttr; cubeDim = cube.Dimensions.GetByName("Snapshot Date"); regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); mg.Dimensions.Add(regMgDim); mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.KeyAttribute.ID); mgAttr.Type = MeasureGroupAttributeType.Granularity; mgAttr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "SnapshotDate")); cubeDim = cube.Dimensions.GetByName("Memory"); regMgDim = new RegularMeasureGroupDimension(cubeDim.ID); mg.Dimensions.Add(regMgDim); mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.KeyAttribute.ID); mgAttr.Type = MeasureGroupAttributeType.Granularity; mgAttr.KeyColumns.Add(CreateDataItem(dsv, TABLE_NAME, "MemoryRowID")); //MemoryID can have different meanings over time, so we need a composite key CreateNewPartition(mg); db.Update(UpdateOptions.ExpandFull); }
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(); } }