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);
}
public void Close()
{
if (_type == AdomdType.AnalysisServices)
{
_conn.Close();
}
else
{
ExcelAdoMdConnections.VoidDelegate f = delegate
{
_connExcel.Close();
};
f();
}
}
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();
}
}
public TabularItems.Measure GetMeasureFromDMV(string measureName)
{
if (!server.Connected)
{
throw new InvalidOperationException("You must be connected to the server");
}
var dmv = string.Format(
@"SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [TABLE],[MEASURE_CAPTION] AS [MEASURE],
[MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME ='Model'
AND MEASURE_CAPTION = '{0}'" ,
measureName);
var daxClient = new DaxHelpers.DaxClient();
System.Data.DataTable dtResult = null;
using (var cnn = new ADOMD.AdomdConnection(connectionString))
{
cnn.Open();
dtResult = daxClient.ExecuteTable(dmv, cnn);
cnn.Close();
}
TabularItems.Measure measure = null;
foreach (System.Data.DataRow drow in dtResult.Rows)
{
if (Convert.ToString(drow["MEASURE"]) == measureName)
{
measure = new TabularItems.Measure(Convert.ToString(drow["TABLE"]), measureName);
break;
}
}
if (measure == null)
{
throw new InvalidOperationException("Measure " + measureName + " was not found in database " + this.databaseName);
}
return(measure);
}
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 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 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 { }
}
}
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 { }
}
}
private void LoadSessions()
{
Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 4, "Starting LoadSessions");
AdomdClient.AdomdConnection conn = new AdomdClient.AdomdConnection("Data Source=" + AdomdServer.Context.CurrentServerID);
conn.Open();
DataSet ds = conn.GetSchemaDataSet("DISCOVER_SESSIONS", null);
conn.Close();
foreach (DataRow dr in ds.Tables[0].Rows)
{
sessions.Add(Convert.ToInt32(dr["SESSION_SPID"]), Convert.ToString(dr["SESSION_USER_NAME"]));
}
Microsoft.AnalysisServices.AdomdServer.Context.TraceEvent(0, 5, "Finishing LoadSessions");
}
public static void Main(string[] args)
{
string varSRV = "SRV-OLAP";
string varDB = "dw_olap";
string varCube = "Рух товарів";
for (int i = 0; i < args.Length; i++)
{
if (args[i].ToUpper().StartsWith("/SERVER:"))
{
varSRV = args[i].Substring(8);
}
else if (args[i].ToUpper().StartsWith("/DB:"))
{
varDB = args[i].Substring(4);
}
else if (args[i].ToUpper().StartsWith("/CUBE:"))
{
varCube = args[i].Substring(6);
}
}
string varSeparator = "\t";
Proc p = new Proc();
p.CreateLog("d:\\Cube_" + varCube + ".txt");
Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
s.Connect(@"Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
Microsoft.AnalysisServices.Database d = s.Databases.FindByName(varDB);
Microsoft.AnalysisServices.Cube c = d.Cubes.FindByName(varCube);
p.Log("База:" + varDB + " Куб:" + varCube);
p.Log("Розмірності");
foreach (Microsoft.AnalysisServices.CubeDimension dim in c.Dimensions)
{
p.Log(dim.Name + varSeparator + dim.DimensionID + varSeparator + dim.Description);
foreach (Microsoft.AnalysisServices.CubeAttribute attr in dim.Attributes)
{
p.Log(varSeparator + attr.Attribute + varSeparator + attr.AttributeID + "\t" + attr.Attribute.Description);
}
}
p.Log("Групи мір");
foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
{
p.Log(mg.Name + varSeparator + mg.ID + varSeparator + mg.Description);
foreach (Microsoft.AnalysisServices.Measure m in mg.Measures)
{
p.Log(varSeparator + m.ID + varSeparator + m.Name + varSeparator + m.Description);
}
}
p.Log("Калькульовані міри");
Microsoft.AnalysisServices.AdomdClient.AdomdConnection cn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + varSRV + ";Provider=msolap;Initial Catalog=" + varDB);
cn.Open();
/*foreach ( Microsoft.AnalysisServices.AdomdClient.CubeDef tt in cn.Cubes)
* p.Log(tt.Name+varSeparator+tt.Caption );*/
try
{
foreach (Microsoft.AnalysisServices.AdomdClient.Measure m in cn.Cubes[varCube].Measures)
{
if (string.IsNullOrEmpty(m.Expression) == false)
{
p.Log(m.UniqueName + varSeparator); //+m.Expression +varSeparator+ m.Description );
}
}
//Console.WriteLine("{0}: {1}",m.UniqueName,m.Expression );
}
catch { }
finally { cn.Close(); };
//Console.ReadLine();
p.CloseLog();
}
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();
}
}