public static DateTime GetCubeLastProcessedDate()
{
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName = AMOHelpers.GetCurrentCubeName();
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try
{
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
dtTemp = cube.LastProcessed;
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start(); //run the delegate code
while (!td.Join(1000)) //wait for up to a second for the delegate to finish
{
Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
}
if (exDelegate != null)
{
throw exDelegate;
}
return(dtTemp);
//return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
}
private static string GetDrillthroughMDXInternal(Tuple tuple, string sReturnColumns, int?iMaxRows, bool skipDefaultMembers)
{
if (sReturnColumns != null)
{
//passed in a set of return columns
return("drillthrough " + (iMaxRows == null ? "" : "maxrows " + iMaxRows) + " select (" + CurrentCellAttributes(tuple, skipDefaultMembers) + ") on 0 from [" + AMOHelpers.GetCurrentCubeName() + "] return " + sReturnColumns);
}
else
{
//passed in a reference to a measure, so just do the default drillthrough for it
return("drillthrough " + (iMaxRows == null ? "" : "maxrows " + iMaxRows) + " select (" + CurrentCellAttributes(tuple, skipDefaultMembers) + ") on 0 from [" + AMOHelpers.GetCurrentCubeName() + "]");
}
}
//don't use this in the MDX script or it may return calculated measures from the previous processed version or blow up if the cube was not previously processed
public static Set GetMeasureGroupCalculatedMeasures(string sMeasureGroupName)
{
StringBuilder sb = new StringBuilder();
XmlaDiscover discover = new XmlaDiscover();
DataTable table = discover.Discover("MDSCHEMA_MEASURES", "<CUBE_NAME>" + AMOHelpers.GetCurrentCubeName() + "</CUBE_NAME><MEASUREGROUP_NAME>" + sMeasureGroupName + "</MEASUREGROUP_NAME>");
foreach (DataRow row in table.Rows)
{
if (Convert.ToInt32(row["MEASURE_AGGREGATOR"]) == 127)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(row["MEASURE_UNIQUE_NAME"].ToString());
}
}
return(new Expression("{" + sb.ToString() + "}").CalculateMdxObject(null).ToSet());
}
public static string CurrentCellAttributes(Tuple tuple, bool skipDefaultMembers)
{
if (tuple == null)
{
return(FindCurrentMembers.FindCurrentTuple()); // CurrentCellAttributes();
}
// start with empty string
StringBuilder coordinate = new StringBuilder();
bool first = true;
foreach (Dimension d in Context.Cubes[AMOHelpers.GetCurrentCubeName()].Dimensions)
{
foreach (Hierarchy h in d.AttributeHierarchies)
{
// skip user hierarchies - consider attribute and parent-child hierarchies
// (parent-child is both user and attribute hierarchy)
if (h.HierarchyOrigin == HierarchyOrigin.UserHierarchy)
{
continue;
}
// skip calculated measures
string sOverrideMeasure = null;
if (d.DimensionType == DimensionTypeEnum.Measure)
{
foreach (Member m in tuple.Members)
{
try
{
if (m.ParentLevel.ParentHierarchy.ParentDimension.DimensionType == DimensionTypeEnum.Measure)
{
sOverrideMeasure = m.UniqueName;
break;
}
}
catch (Exception ex)
{
// DPG 16 Jan 2015
// if we get an error trying to figure out if we are looking at the Measures Dimension
// we can just log it and continue. As far as I'm aware this only happens when one of the dimensions/attributes
// in the tuple is not visible and this should only occur for non-measures dimensions.
// so simply logging the exception and moving on to the next member should not do any harm.
int eventSubClass = 999;
Context.TraceEvent(eventSubClass, 0, string.Format("ERROR GetCustomDrillthroughMDX() Exception: {0}", ex.Message));
}
}
if (sOverrideMeasure == null && h.CurrentMember.Type == MemberTypeEnum.Formula)
{
continue;
}
}
var sCurrMbr = new Expression(h.UniqueName + ".CurrentMember.UniqueName").Calculate(tuple).ToString();
// If skipDefaultMembers is true and the current member is the default member
// the move on to the next hierarchy
if (sCurrMbr == null || (sCurrMbr == h.DefaultMember && skipDefaultMembers))
{
continue;
}
if (!first)
{
coordinate.Append("\r\n,");
}
else
{
first = false;
}
if (sOverrideMeasure != null)
{
coordinate.Append(sOverrideMeasure);
}
else //calculate it in the context of the tuple
{
coordinate.Append(sCurrMbr);
}
}
}
return(coordinate.ToString());
}
public static string CurrentCellAttributes()
{
return(CurrentCellAttributesForCube(AMOHelpers.GetCurrentCubeName()));
}
public static DateTime GetLastProcessedDateOverPartitions(string CubeName, string MeasureGroupName, string PartitionName, bool IncludeMeasureGroupLastProcessed)
{
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName;
if (string.IsNullOrEmpty(CubeName))
{
sCubeName = AMOHelpers.GetCurrentCubeName();
}
else
{
sCubeName = CubeName;
}
if (!string.IsNullOrEmpty(PartitionName) && string.IsNullOrEmpty(MeasureGroupName))
{
throw new Exception("Measure group may not be empty if partition is explicitly specified");
}
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try
{
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
//If measure group is specified - get it. Otherwise iterate over all measure groups
if (!string.IsNullOrEmpty(MeasureGroupName))
{
dtTemp = FindMaxLastProcessedDateInMeasureGroup(cube.MeasureGroups.GetByName(MeasureGroupName), PartitionName, IncludeMeasureGroupLastProcessed);
}
else
{
foreach (MeasureGroup curMeasureGroup in cube.MeasureGroups)
{
DateTime curLastProcessedDate = FindMaxLastProcessedDateInMeasureGroup(curMeasureGroup, PartitionName, IncludeMeasureGroupLastProcessed);
if (dtTemp < curLastProcessedDate)
{
dtTemp = curLastProcessedDate;
}
}
}
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start(); //run the delegate code
while (!td.Join(1000)) //wait for up to a second for the delegate to finish
{
Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
}
if (exDelegate != null)
{
throw exDelegate;
}
return(dtTemp);
}
public static string CurrentCellAttributes(Tuple tuple)
{
if (tuple == null)
{
return(CurrentCellAttributes());
}
// start with empty string
StringBuilder coordinate = new StringBuilder();
bool first = true;
foreach (Dimension d in Context.Cubes[AMOHelpers.GetCurrentCubeName()].Dimensions)
{
foreach (Hierarchy h in d.AttributeHierarchies)
{
// skip user hierarchies - consider attribute and parent-child hierarchies
// (parent-child is both user and attribute hierarchy)
if (h.HierarchyOrigin == HierarchyOrigin.UserHierarchy)
{
continue;
}
// skip calculated measures
string sOverrideMeasure = null;
if (d.DimensionType == DimensionTypeEnum.Measure)
{
foreach (Member m in tuple.Members)
{
if (m.ParentLevel.ParentHierarchy.ParentDimension.DimensionType == DimensionTypeEnum.Measure)
{
sOverrideMeasure = m.UniqueName;
break;
}
}
if (sOverrideMeasure == null && h.CurrentMember.Type == MemberTypeEnum.Formula)
{
continue;
}
}
if (!first)
{
coordinate.Append("\r\n,");
}
else
{
first = false;
}
if (sOverrideMeasure != null)
{
coordinate.Append(sOverrideMeasure);
}
else //calculate it in the context of the tuple
{
coordinate.Append(new Expression(h.UniqueName + ".CurrentMember.UniqueName").Calculate(tuple).ToString());
}
}
}
return(coordinate.ToString());
}