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