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