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 CellSet ExecuteCellSet() { if (_obj != null) { _obj.Parameters.Clear(); foreach (AdomdParameter param in _parameters) { _obj.Parameters.Add(new AsAdomdClient.AdomdParameter(param.Name, param.Value)); } return(new CellSet(_obj.ExecuteCellSet())); } else { ExcelAdoMdConnections.ReturnDelegate <CellSet> f = delegate { _objExcel.Parameters.Clear(); foreach (AdomdParameter param in _parameters) { _objExcel.Parameters.Add(new ExcelAdomdClient.AdomdParameter(param.Name, param.Value)); } return(new CellSet(_objExcel.ExecuteCellSet())); }; return(f()); } }
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 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)); }
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 { } } }
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 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 { } } }
//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 { } } }
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 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; }