/// <summary>
/// Add regular dim usage
/// </summary>
/// <param name="cube"></param>
/// <param name="measureGroup"></param>
/// <param name="factDataItem"></param>
/// <param name="dimID"></param>
/// <param name="dimAttrId"></param>
/// <returns>Regular dim usage</returns>
internal static RegularMeasureGroupDimension ADD_DIM_USAGE_REGULAR_RELATIONSHIP(
DB_SQLHELPER_BASE sqlHelper,
Cube cube,
MeasureGroup measureGroup,
DataItem factDataItem,
String dimID,
String dimAttrId)
{
RegularMeasureGroupDimension regMgDim = null;
CubeDimension curDim = cube.Dimensions.Find(dimID);
if (curDim == null)
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("Cann't find dimension {0}", dimID),
MESSAGE_TYPE.DIM_USAGE_REGULAR, MESSAGE_RESULT_TYPE.Error);
}
regMgDim = new RegularMeasureGroupDimension(curDim.Name);
regMgDim.CubeDimensionID = dimID;
measureGroup.Dimensions.Add(regMgDim);
MeasureGroupAttribute mgAttr = regMgDim.Attributes.Add(dimAttrId);
mgAttr.Type = MeasureGroupAttributeType.Granularity;
mgAttr.KeyColumns.Add(factDataItem);
return(regMgDim);
}
public static List <DimensionUsage> GetDimensionUsage(Cube c)
{
List <CubeDimension> listCubeDimensions = new List <CubeDimension>();
foreach (CubeDimension cd in c.Dimensions)
{
listCubeDimensions.Add(cd);
}
List <DimensionUsage> dimUsage = new List <DimensionUsage>();
foreach (MeasureGroup mg in c.MeasureGroups)
{
System.Diagnostics.Trace.Write("mg " + mg.Name);
foreach (MeasureGroupDimension mgdim in mg.Dimensions)
{
System.Diagnostics.Trace.Write(",mgdim " + mgdim.Dimension.Name);
if (mgdim is ReferenceMeasureGroupDimension)
{
ReferenceMeasureGroupDimension refMgDim = (ReferenceMeasureGroupDimension)mgdim;
getReferencedMeasureGroupAttributeUsage(dimUsage, mg, refMgDim);
}
else if (mgdim is DegenerateMeasureGroupDimension)
{
DegenerateMeasureGroupDimension degMgDim = (DegenerateMeasureGroupDimension)mgdim;
getFactMeasureGroupAttributeUsage(dimUsage, mg, degMgDim);
}
else if (mgdim is ManyToManyMeasureGroupDimension)
{
ManyToManyMeasureGroupDimension m2mMgDim = (ManyToManyMeasureGroupDimension)mgdim;
getManyToManyMeasureGroupAttributeUsage(dimUsage, mg, m2mMgDim);
}
else if (mgdim is DataMiningMeasureGroupDimension)
{
DataMiningMeasureGroupDimension dmMgDim = (DataMiningMeasureGroupDimension)mgdim;
getDataMiningMeasureGroupAttributeUsage(dimUsage, mg, dmMgDim);
}
else if (mgdim is RegularMeasureGroupDimension)
{
RegularMeasureGroupDimension regMDdim = (RegularMeasureGroupDimension)mgdim;
getRegularMeasureGroupAttributeUsage(dimUsage, mg, regMDdim);
}
if (listCubeDimensions.Contains(mgdim.CubeDimension))
{
listCubeDimensions.Remove(mgdim.CubeDimension);
}
}
}
//add any cube dimensions which aren't related to any measure groups
foreach (CubeDimension cd in listCubeDimensions)
{
DimensionUsage du = new DimensionUsage(string.Empty, null, cd, cd.Dimension);
dimUsage.Add(du);
}
return(dimUsage);
}
private MeasureGroupAttribute GetGranularityAttribute(RegularMeasureGroupDimension rmgd)
{
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
return(mga);
}
}
throw new Exception("Can't find granularity attribute for dimension " + rmgd.CubeDimension.Name + " in measure group " + rmgd.Parent.Name);
}
private void EnumerateMeasureGroupDimensions(Dictionary <DataSourceView, DsvRepositoryInformation> dsvToRepositoryMap, int connectionID, Dictionary <CubeDimension, int> cubeDimensionToIDMap, MeasureGroup mg, int mgID)
{
foreach (MeasureGroupDimension mgDim in mg.Dimensions)
{
if (mgDim.Dimension != null)
{
Dictionary <string, int> dsvTableNameToIdMap = dsvToRepositoryMap[mgDim.Dimension.DataSourceView].dsvTableNameToIDMap;
Dictionary <int, bool> distinctTableIDsList = new Dictionary <int, bool>();
int mgDimID = repository.AddObject(mgDim.Dimension.Name, mgDim.Dimension.Description, ObjectTypes.MeasureGroupDimension, mgID);
// add a Use dependency from cubeDim->mgdim
repository.AddUseDependency(cubeDimensionToIDMap[mgDim.CubeDimension], mgDimID);
// this handles ReferenceMeasureGroupDimension and DegenerateMeasureGroupDimension as well.
RegularMeasureGroupDimension rmgdim = mgDim as RegularMeasureGroupDimension;
if (rmgdim != null)
{
foreach (MeasureGroupAttribute mgDimAttr in rmgdim.Attributes)
{
foreach (DataItem keyColumn in mgDimAttr.KeyColumns)
{
string query;
int sourceTableID = GetSourceIDForBinding(connectionID, dsvTableNameToIdMap, keyColumn.Source, out query);
if (sourceTableID > 0)
{
distinctTableIDsList[sourceTableID] = true;
}
else
{
// if we don't have a table ID but have a query, store that as an attribute
// so that users can at least take a look at the query itself.
if (query != null)
{
repository.AddAttribute(mgDimID, Repository.Attributes.QueryDefinition, query);
}
}
}
}
}
// add a lineage map from the tables to the measure group dim
foreach (int tableID in distinctTableIDsList.Keys)
{
repository.AddMapping(tableID, mgDimID);
}
}
}
}
public static MeasureGroupAttribute GetGranularityAttribute(RegularMeasureGroupDimension mgDim)
{
if (mgDim != null)
{
foreach (MeasureGroupAttribute mga in mgDim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
return(mga);
}
}
}
return(null);
}
/// <summary>
/// Generate cube.
/// </summary>
/// <param name="objCube">Cube instance.</param>
/// <param name="objDimension">Dimension instance.</param>
/// <param name="objMeasureGroup">MeasureGroup instance.</param>
/// <param name="strFactTableName">FactTable Name.</param>
/// <param name="strTableKey">Table Key.</param>
private static void GenerateCube(Cube objCube, Dimension objDimension, MeasureGroup objMeasureGroup, string strFactTableName, string strTableKey)
{
try
{
CubeDimension objCubeDim = new CubeDimension();
RegularMeasureGroupDimension objRegMGDim = new RegularMeasureGroupDimension();
MeasureGroupAttribute objMGA = new MeasureGroupAttribute();
//Add Dimension to the Cube
objCubeDim = objCube.Dimensions.Add(objDimension.ID);
//Use Regular Relationship Between Dimension and FactTable Measure Group
objRegMGDim = objMeasureGroup.Dimensions.Add(objCubeDim.ID);
//Link TableKey in DimensionTable with TableKey in FactTable Measure Group
objMGA = objRegMGDim.Attributes.Add(objDimension.KeyAttribute.ID);
objMGA.Type = MeasureGroupAttributeType.Granularity;
objMGA.KeyColumns.Add(strFactTableName, strTableKey, OleDbType.Integer);
}
catch (Exception ex)
{
Console.WriteLine("Error in Creating the Cube, MeasureGroup, Measure, and Partition Objects - GenerateCube. Error Message -> " + ex.Message);
}
}
private void AddLinkDimensionMeasureGroup(CbMeasure_M aCbMeasure, CbMeasureGroup_M aCbMeasureGroup)
{
try
{
CbDimension_M aCbDimension = aCbMeasure.ReferenceToDimension;
RegularMeasureGroupDimension objRegMGDim = new RegularMeasureGroupDimension();
MeasureGroupAttribute objMGA = new MeasureGroupAttribute();
objRegMGDim = aCbMeasureGroup.MeasureGroupObj.Dimensions.Add(aCbDimension.CubeDimensionObj.ID);
//Link TableKey in DimensionTable with TableKey in FactTable Measure Group
objMGA = objRegMGDim.Attributes.Add(aCbDimension.DimensionObj.KeyAttribute.ID);
objMGA.Type = MeasureGroupAttributeType.Granularity;
objMGA.KeyColumns.Add(
CreateDataItem(aCbMeasureGroup.ReferenceToDsDwTableMap.DwTableName,
aCbMeasure.ReferenceToDsDwColumnMap.DwColumn.Name,
NullProcessing.UnknownMember,
aCbMeasure.ReferenceToDsDwColumnMap.DwColumn.DataType));
}
catch (Exception ex)
{
AppendLogLine("Error in creating link between measure group and dimension. Error Message -> " + ex.Message);
throw;
}
}
/*
* All relationships - Relationship Type
* - Measure Group Name
* - Dim Name
*/
/*
* Regular Col1 - Ganularity Attribute Name
* Col2 - Dimension Column(s) Name
* Col3 - Measure Group Columns(s) Name
*/
private static void getRegularMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, RegularMeasureGroupDimension regMDdim)
{
string tableId = string.Empty;
DimensionUsage usage = new DimensionUsage("Regular", mg, regMDdim.CubeDimension, regMDdim.Dimension);//, cb.TableID, cb.ColumnID);
usage.Column1Name = "Granularity Attribute";
usage.Column2Name = "Dimension Column";
usage.Column3Name = "Measure Group Columns";
foreach (MeasureGroupAttribute mga in regMDdim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
tableId = string.Empty;
usage.Column1Value = mga.Attribute.Name;
System.Diagnostics.Trace.Write(",mga " + mga.CubeAttribute.Attribute.Name);
//foreach (DataItem di in mga.KeyColumns)
//{
//ColumnBinding cb = (ColumnBinding)di.Source;
// TODO - get the key columns for the attribute, not just its name
//System.Diagnostics.Trace.WriteLine(",di " + di.Source.ToString());
foreach (DataItem di2 in mga.Attribute.KeyColumns)
{
if (di2.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di2.Source).TableID;
DataSourceView dsv = mga.Parent.Dimension.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column2Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column2Value += ((ColumnBinding)di2.Source).TableID + ".";
}
usage.Column2Value += ((ColumnBinding)di2.Source).ColumnID + "\n";
}
}
foreach (DataItem di3 in mga.KeyColumns)
{
if (di3.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di3.Source).TableID;
DataSourceView dsv = mga.ParentCube.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column3Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column3Value += ((ColumnBinding)di3.Source).TableID + ".";
}
usage.Column3Value += ((ColumnBinding)di3.Source).ColumnID;
usage.Column3Value += " (" + di3.NullProcessing.ToString().Substring(0, 1) + ")";
usage.Column3Value += "\n";
}
}
//}
}
}
dimUsage.Add(usage);
}
public List <M2MMatrixCompressionStat> BuildQueries(Cube cube)
{
List <M2MMatrixCompressionStat> listStats = new List <M2MMatrixCompressionStat>();
foreach (MeasureGroup mg in cube.MeasureGroups)
{
if (mg.IsLinked)
{
continue;
}
Dictionary <MeasureGroup, List <ManyToManyMeasureGroupDimension> > dictM2M = new Dictionary <MeasureGroup, List <ManyToManyMeasureGroupDimension> >();
foreach (MeasureGroupDimension mgd in mg.Dimensions)
{
if (mgd is ManyToManyMeasureGroupDimension)
{
ManyToManyMeasureGroupDimension m2mmgd = (ManyToManyMeasureGroupDimension)mgd;
if (!dictM2M.ContainsKey(m2mmgd.MeasureGroup))
{
dictM2M.Add(m2mmgd.MeasureGroup, new List <ManyToManyMeasureGroupDimension>());
}
dictM2M[m2mmgd.MeasureGroup].Add(m2mmgd);
}
}
if (dictM2M.Count > 0)
{
//there are m2m dimensions used by this data measure group
foreach (MeasureGroup intermediateMG in dictM2M.Keys)
{
if (intermediateMG.IsLinked)
{
continue;
}
try
{
List <CubeAttribute> commonDimensions = new List <CubeAttribute>();
foreach (CubeDimension cd in cube.Dimensions)
{
if (mg.Dimensions.Contains(cd.ID) && intermediateMG.Dimensions.Contains(cd.ID))
{
if (mg.Dimensions[cd.ID] is RegularMeasureGroupDimension &&
intermediateMG.Dimensions[cd.ID] is RegularMeasureGroupDimension)
{
//it's a common dimension
RegularMeasureGroupDimension rmgdData = (RegularMeasureGroupDimension)mg.Dimensions[cd.ID];
MeasureGroupAttribute mgaData = GetGranularityAttribute(rmgdData);
RegularMeasureGroupDimension rmgdIntermediate = (RegularMeasureGroupDimension)intermediateMG.Dimensions[cd.ID];
MeasureGroupAttribute mgaIntermediate = GetGranularityAttribute(rmgdIntermediate);
CubeAttribute ca = mgaData.CubeAttribute;
if (mgaData.AttributeID != mgaIntermediate.AttributeID)
{
if (IsParentOf(mgaIntermediate.Attribute, mgaData.Attribute))
{
ca = mgaIntermediate.CubeAttribute;
}
}
commonDimensions.Add(ca);
}
}
}
//fine while we're just doing this for SQL server
MeasureGroupHealthCheckPlugin.sq = "[";
MeasureGroupHealthCheckPlugin.fq = "]";
DsvTableBinding oTblBinding = new DsvTableBinding(intermediateMG.Parent.DataSourceView.ID, MeasureGroupHealthCheckPlugin.GetTableIdForDataItem(intermediateMG.Measures[0].Source));
string sFactQuery = "(" + MeasureGroupHealthCheckPlugin.GetQueryDefinition(intermediateMG.ParentDatabase, intermediateMG, oTblBinding, null) + ")";
List <string> listCommonDimensionsSeen = new List <string>();
string sCommonDimensions = "";
string sCommonDimensionsJoin = "";
foreach (CubeAttribute ca in commonDimensions)
{
RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)intermediateMG.Dimensions[ca.Parent.ID];
if (rmgd is ReferenceMeasureGroupDimension)
{
if (mg.Dimensions.Contains(((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID) &&
mg.Dimensions[((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID] is RegularMeasureGroupDimension)
{
continue; //skip reference dimensions in the intermediate measure group because it won't change the cardinality
}
else
{
throw new Exception(rmgd.CubeDimension.Name + " dimension in intermediate measure group " + intermediateMG.Name + " is not supported by BIDS Helper M2M Matrix Compression");
}
}
MeasureGroupAttribute mga = rmgd.Attributes[ca.AttributeID];
foreach (DataItem di in mga.KeyColumns)
{
if (di.Source is ColumnBinding)
{
if (!listCommonDimensionsSeen.Contains("[" + ((ColumnBinding)di.Source).ColumnID + "]")) //if this column is already mentioned, then don't mention it again
{
listCommonDimensionsSeen.Add("[" + ((ColumnBinding)di.Source).ColumnID + "]");
if (sCommonDimensionsJoin.Length == 0)
{
sCommonDimensionsJoin += "WHERE ";
}
else
{
sCommonDimensionsJoin += "\r\nAND ";
sCommonDimensions += ", ";
}
sCommonDimensionsJoin += "f.[" + ((ColumnBinding)di.Source).ColumnID + "] = s.[" + ((ColumnBinding)di.Source).ColumnID + "]";
sCommonDimensions += "[" + ((ColumnBinding)di.Source).ColumnID + "]";
}
}
}
}
List <string> listM2MDimensionsSeen = new List <string>();
string sM2MDimensions = "";
string sM2MDimensionsOrderBy = "";
foreach (ManyToManyMeasureGroupDimension m2mmgd in dictM2M[intermediateMG])
{
if (intermediateMG.Dimensions[m2mmgd.CubeDimensionID] is RegularMeasureGroupDimension)
{
RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)intermediateMG.Dimensions[m2mmgd.CubeDimensionID];
if (rmgd is ReferenceMeasureGroupDimension)
{
continue; //won't change
}
if (rmgd is ReferenceMeasureGroupDimension)
{
if (mg.Dimensions.Contains(((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID) &&
mg.Dimensions[((ReferenceMeasureGroupDimension)rmgd).IntermediateCubeDimensionID] is ManyToManyMeasureGroupDimension)
{
continue; //skip reference dimensions in the intermediate measure group because it won't change the cardinality
}
else
{
throw new Exception(rmgd.CubeDimension.Name + " dimension in intermediate measure group " + intermediateMG.Name + " is not supported by BIDS Helper M2M Matrix Compression");
}
}
MeasureGroupAttribute mga = GetGranularityAttribute(rmgd);
foreach (DataItem di in mga.KeyColumns)
{
if (di.Source is ColumnBinding)
{
if (!listM2MDimensionsSeen.Contains("[" + ((ColumnBinding)di.Source).ColumnID + "]")) //if this column is already mentioned, then don't mention it again
{
listM2MDimensionsSeen.Add("[" + ((ColumnBinding)di.Source).ColumnID + "]");
if (sM2MDimensions.Length > 0)
{
sM2MDimensions += " + '|' + ";
sM2MDimensionsOrderBy += ", ";
}
sM2MDimensions += "isnull(cast([" + ((ColumnBinding)di.Source).ColumnID + "] as nvarchar(max)),'')";
sM2MDimensionsOrderBy += "[" + ((ColumnBinding)di.Source).ColumnID + "]";
}
}
}
}
}
string sSQL = @"
SELECT (SELECT COUNT(*) FROM " + sFactQuery + @" x) OriginalRecordCount
, COUNT(MatrixKey) MatrixDimensionRecordCount
, SUM(cast(KeyCount AS FLOAT)) CompressedRecordCount
FROM (
SELECT DISTINCT COUNT(*) KeyCount
, MatrixKey = (
SELECT " + sM2MDimensions + @" AS [data()]
FROM " + sFactQuery + @" f
" + sCommonDimensionsJoin + @"
ORDER BY " + sM2MDimensionsOrderBy + @"
FOR XML PATH ('')
)
FROM " + sFactQuery + @" s
GROUP BY " + sCommonDimensions + @"
) SUBQ
";
M2MMatrixCompressionStat stat = new M2MMatrixCompressionStat();
stat.IntermediateMeasureGroup = intermediateMG;
stat.DataMeasureGroup = mg;
stat.SQL = sSQL;
listStats.Add(stat);
}
catch (Exception ex)
{
M2MMatrixCompressionStat stat = new M2MMatrixCompressionStat();
stat.IntermediateMeasureGroup = intermediateMG;
stat.DataMeasureGroup = mg;
stat.Error = ex.Message + "\r\n" + ex.StackTrace;
listStats.Add(stat);
}
}
}
}
return(listStats);
}
public void CreateInternetSalesMeasureGroup(Cube cube)
{
// Create the Internet Sales measure group
Database db = cube.Parent;
MeasureGroup mg = cube.MeasureGroups.Add("Internet Sales");
mg.StorageMode = StorageMode.Molap;
mg.ProcessingMode = ProcessingMode.LazyAggregations;
mg.Type = MeasureGroupType.Sales;
#region Create measures
Measure meas;
//meas = mg.Measures.Add("Internet Sales Amount"); //加一个维度
//meas.AggregateFunction = AggregationFunction.Sum; //维度的计算方法
//meas.MeasureExpression = "[Internet Sales Amount] * [Average Rate]"; //维度表达式
//meas.FormatString = "Currency"; //维度格式字符
//meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesAmount"); //
meas = mg.Measures.Add("Internet Order Quantity");
meas.AggregateFunction = AggregationFunction.Sum;
meas.FormatString = "#,#";
meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "OrderQuantity");
meas = mg.Measures.Add("Internet Unit Price");
meas.AggregateFunction = AggregationFunction.Sum;
meas.FormatString = "Currency";
meas.Visible = false;
meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "UnitPrice");
//meas = mg.Measures.Add("Internet Total Product Cost");
//meas.AggregateFunction = AggregationFunction.Sum;
//meas.MeasureExpression = "[Internet Total Product Cost] * [Average Rate]";
//meas.FormatString = "Currency";
//meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "TotalProductCost");
meas = mg.Measures.Add("Internet Order Count");
meas.AggregateFunction = AggregationFunction.Count;
meas.FormatString = "#,#";
meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ProductKey");
#endregion
#region Create measure group dimensions
CubeDimension cubeDim;
RegularMeasureGroupDimension regMgDim;
ManyToManyMeasureGroupDimension mmMgDim;
MeasureGroupAttribute mgAttr;
//cubeDim = cube.Dimensions.GetByName("Date");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "OrderDateKey"));
//cubeDim = cube.Dimensions.GetByName("Ship Date");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ShipDateKey"));
//cubeDim = cube.Dimensions.GetByName("Delivery Date");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "DueDateKey"));
cubeDim = cube.Dimensions.GetByName("Customer");
regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
mg.Dimensions.Add(regMgDim);
mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Full Name").ID);
mgAttr.Type = MeasureGroupAttributeType.Granularity;
mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "CustomerKey"));
//cubeDim = cube.Dimensions.GetByName("Product");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Product Name").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ProductKey"));
//cubeDim = cube.Dimensions.GetByName("Source Currency");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Currency").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "CurrencyKey"));
//cubeDim = cube.Dimensions.GetByName("Sales Reason");
//mmMgDim = new ManyToManyMeasureGroupDimension();
//mmMgDim.CubeDimensionID = cubeDim.ID;
//mmMgDim.MeasureGroupID = cube.MeasureGroups.GetByName("Sales Reasons").ID;
//mg.Dimensions.Add(mmMgDim);
//cubeDim = cube.Dimensions.GetByName("Internet Sales Order Details");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Sales Order Key").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesOrderNumber"));
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesOrderLineNumber"));
#endregion
#region Create partitions
Partition part;
part = mg.Partitions.Add("Internet_Sales_184");
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey <= '184'");
part.Annotations.Add("LastOrderDateKey", "184");
part = mg.Partitions.Add("Internet_Sales_549");
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '184' AND OrderDateKey <= '549'");
part.Annotations.Add("LastOrderDateKey", "549");
part = mg.Partitions.Add("Internet_Sales_914");
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '549' AND OrderDateKey <= '914'");
part.Annotations.Add("LastOrderDateKey", "914");
#endregion
cube.Update(UpdateOptions.ExpandFull);
}
private void FillTree()
{
Dictionary <string, long> dictHierarchyCardinality = new Dictionary <string, long>();
try
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
restrictions.Add(new AdomdRestriction("CATALOG_NAME", this.liveDB.Name));
restrictions.Add(new AdomdRestriction("CUBE_NAME", this.liveCube.Name));
restrictions.Add(new AdomdRestriction("HIERARCHY_VISIBILITY", 3)); //visible and non-visible hierarchies
foreach (System.Data.DataRow r in adomdConnection.GetSchemaDataSet("MDSCHEMA_HIERARCHIES", restrictions).Tables[0].Rows)
{
dictHierarchyCardinality.Add(Convert.ToString(r["HIERARCHY_UNIQUE_NAME"]), Convert.ToInt64(r["HIERARCHY_CARDINALITY"]));
}
}
catch { }
StringBuilder sbExport = new StringBuilder();
sbExport.Append("Cube Dimension Name").Append("\t").Append("Attribute Name").Append("\t").Append("Number of Slices on this Attribute").Append("\t").Append("Attribute Cardinality").Append("\t").Append("Related Partition Count").Append("\t").Append("Currently Effective Optimized State").Append("\t").Append("Recommendation").AppendLine();
foreach (CubeDimension cd in cloneCube.Dimensions)
{
TreeNode parentNode = treeViewAggregation.Nodes.Add(cd.Name);
parentNode.Tag = cd;
parentNode.ImageIndex = parentNode.SelectedImageIndex = 0;
bool bAllIndexesChecked = true;
foreach (CubeAttribute ca in cd.Attributes)
{
if (ca.AttributeHierarchyEnabled && ca.Attribute.AttributeHierarchyEnabled)
{
CubeAttribute caSliced = null;
foreach (CubeAttribute sliced in dictHitIndexes.Keys)
{
if (sliced.Parent.ID == ca.Parent.ID && sliced.AttributeID == ca.AttributeID)
{
caSliced = sliced;
break;
}
}
string sNodeName = ca.Attribute.Name;
int iIndexHits = 0;
if (caSliced != null)
{
iIndexHits = dictHitIndexes[caSliced];
sNodeName += " (Index hits: " + iIndexHits.ToString("g") + ")";
}
TreeNode attributeNode = parentNode.Nodes.Add(sNodeName);
attributeNode.Tag = ca;
attributeNode.ImageIndex = attributeNode.SelectedImageIndex = 1;
attributeNode.Checked = (caSliced == null);
bAllIndexesChecked = bAllIndexesChecked && attributeNode.Checked;
bool bInEnabledHierarchy = false;
foreach (CubeHierarchy ch in ca.Parent.Hierarchies)
{
foreach (Microsoft.AnalysisServices.Level l in ch.Hierarchy.Levels)
{
if (l.SourceAttributeID == ca.AttributeID && ch.Enabled && ch.OptimizedState == OptimizationType.FullyOptimized)
{
bInEnabledHierarchy = true;
}
}
}
OptimizationType optimized = ca.Attribute.AttributeHierarchyOptimizedState;
if (optimized == OptimizationType.FullyOptimized)
{
optimized = ca.AttributeHierarchyOptimizedState;
}
if (optimized == OptimizationType.NotOptimized && bInEnabledHierarchy)
{
optimized = OptimizationType.FullyOptimized;
}
string sRecommendation = "";
if (optimized == OptimizationType.FullyOptimized && iIndexHits == 0)
{
attributeNode.ForeColor = Color.Black;
sRecommendation = "Disable";
attributeNode.BackColor = Color.Green;
attributeNode.ToolTipText = "Currently indexed but the index was not used during the profiler trace. If left checked, BIDS Helper will disable the indexes when you click OK.";
}
else if (optimized == OptimizationType.NotOptimized)
{
attributeNode.ForeColor = Color.DarkGray;
attributeNode.ToolTipText = "Indexes not currently being built.";
if (iIndexHits > 0)
{
sRecommendation = "Enable";
attributeNode.ForeColor = Color.Black;
attributeNode.BackColor = Color.Red;
attributeNode.ToolTipText = "Currently not indexed but the queries observed during the profiler trace would have used the index if it had been built. If left unchecked, BIDS Helper will re-enable indexing when you click OK.";
}
}
else
{
attributeNode.ForeColor = Color.Black;
attributeNode.ToolTipText = "Indexes are being built and are used during the queries observed during the profiler trace.";
}
long? iCardinality = null;
string sAttributeUniqueName = "[" + ca.Parent.Name + "].[" + ca.Attribute.Name + "]";
if (dictHierarchyCardinality.ContainsKey(sAttributeUniqueName))
{
iCardinality = dictHierarchyCardinality[sAttributeUniqueName];
}
int iPartitions = 0;
try
{
foreach (MeasureGroup mg in liveCube.MeasureGroups)
{
if (mg.Dimensions.Contains(cd.ID))
{
try
{
RegularMeasureGroupDimension rmgd = mg.Dimensions[cd.ID] as RegularMeasureGroupDimension;
if (rmgd == null)
{
continue;
}
if (!AggManager.ValidateAggs.IsAtOrAboveGranularity(ca.Attribute, rmgd))
{
continue;
}
iPartitions += mg.Partitions.Count;
}
catch { }
}
}
}
catch { }
sbExport.Append(cd.Name).Append("\t").Append(ca.Attribute.Name).Append("\t").Append(iIndexHits).Append("\t").Append(iCardinality).Append("\t").Append(iPartitions).Append("\t").Append(optimized.ToString()).Append("\t").Append(sRecommendation).AppendLine();
}
}
parentNode.Checked = bAllIndexesChecked;
}
treeViewAggregation.Sort();
sExport = sbExport.ToString();
}
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 MeasureGroupAttribute GetGranularityAttribute(RegularMeasureGroupDimension mgDim)
{
if (mgDim != null)
{
foreach (MeasureGroupAttribute mga in mgDim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
return mga;
}
}
}
return null;
}
private static List <AggValidationWarning> CheckAggDesign(AggregationDesign aggDesign, string sCorrectAggregationDesignName, string sReportTitle)
{
List <AggValidationWarning> masterWarnings = new List <AggValidationWarning>();
//check for m2m agg problems
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ManyToManyMeasureGroupDimension)
{
ManyToManyMeasureGroupDimension m2mDim = (ManyToManyMeasureGroupDimension)aggDim.MeasureGroupDimension;
MeasureGroup intermediateMG = m2mDim.MeasureGroup;
List <MeasureGroupAttribute> missing = new List <MeasureGroupAttribute>();
foreach (MeasureGroupDimension commonDim in intermediateMG.Dimensions)
{
RegularMeasureGroupDimension regCommonDim = commonDim as RegularMeasureGroupDimension;
if (commonDim.CubeDimensionID != aggDim.CubeDimensionID || regCommonDim == null)
{
if (!aggDim.ParentMeasureGroup.Dimensions.Contains(commonDim.CubeDimensionID))
{
continue; //this isn't a shared dimension
}
MeasureGroupDimension dataMeasureGroupDim = aggDim.ParentMeasureGroup.Dimensions[commonDim.CubeDimensionID];
if (dataMeasureGroupDim is ManyToManyMeasureGroupDimension)
{
continue; //this shared dimension is m2m on the data measure group so don't include it
}
//this is a common dimension and the granularity attribute on the intermediate measure group needs to be in the agg
bool bFoundGranularityAgg = false;
MeasureGroupAttribute mga = GetGranularityAttribute(regCommonDim);
AggregationDimension aggCommonDim = agg.Dimensions.Find(commonDim.CubeDimensionID);
if (aggCommonDim != null)
{
if (aggCommonDim.Attributes.Find(mga.AttributeID) != null)
{
bFoundGranularityAgg = true;
}
}
if (!bFoundGranularityAgg && mga != null)
{
missing.Add(mga);
}
}
}
string sWarning = "This aggregation contains many-to-many dimension [" + m2mDim.CubeDimension.Name + "]. It will not be used unless it also contains ";
for (int i = 0; i < missing.Count; i++)
{
MeasureGroupAttribute mga = missing[i];
if (i > 0)
{
sWarning += " and ";
}
sWarning += "[" + mga.Parent.CubeDimension.Name + "].[" + mga.Attribute.Name + "]";
}
if (missing.Count == 0)
{
sWarning = "";
}
else
{
sWarning += ". ";
}
sWarning += "The many-to-many dimension [" + m2mDim.CubeDimension.Name + "] itself should not be included in the aggregation to workaround a bug.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
//check for non-materialized reference dimensions
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
if (aggDim.Attributes.Count > 0 && aggDim.MeasureGroupDimension is ReferenceMeasureGroupDimension)
{
ReferenceMeasureGroupDimension refDim = (ReferenceMeasureGroupDimension)aggDim.MeasureGroupDimension;
if (refDim.Materialization == ReferenceDimensionMaterialization.Indirect)
{
string sWarning = "This aggregation contains a non-materialized reference dimension [" + refDim.CubeDimension.Name + "] which is not supported.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check whether all measures are semi-additive
bool bAllMeasuresAreSemiAdditive = true;
foreach (Measure m in aggDesign.Parent.Measures)
{
if (m.AggregateFunction == AggregationFunction.Count || m.AggregateFunction == AggregationFunction.DistinctCount || m.AggregateFunction == AggregationFunction.Sum || m.AggregateFunction == AggregationFunction.Min || m.AggregateFunction == AggregationFunction.Max || m.AggregateFunction == AggregationFunction.None)
{
bAllMeasuresAreSemiAdditive = false;
break;
}
else if (m.AggregateFunction == AggregationFunction.ByAccount)
{
//if it's a ByAccount measure, we need to check the list of AggregationFunctions on each account
foreach (Account acct in aggDesign.ParentDatabase.Accounts)
{
if (acct.AggregationFunction == AggregationFunction.Sum) //Sum is the only additive AggregationFunction allowed in account intelligence
{
bAllMeasuresAreSemiAdditive = false;
break;
}
}
}
}
//if all measures are semi-additive, find the Time dimension the semi-additive behavior operates on (which we think is the first Time dimension)
if (bAllMeasuresAreSemiAdditive)
{
CubeDimension semiAdditiveDim = null;
MeasureGroupDimension semiAdditiveMgDim = null;
foreach (CubeDimension cd in aggDesign.ParentCube.Dimensions)
{
MeasureGroupDimension mgd = aggDesign.Parent.Dimensions.Find(cd.ID);
if (mgd != null && mgd.Dimension.Type == DimensionType.Time)
{
semiAdditiveDim = mgd.CubeDimension;
semiAdditiveMgDim = mgd;
break;
}
}
if (semiAdditiveDim == null || semiAdditiveMgDim == null || !(semiAdditiveMgDim is RegularMeasureGroupDimension))
{
//TODO: should we warn about this?
}
else
{
foreach (Aggregation agg in aggDesign.Aggregations)
{
AggregationDimension semiAdditiveAggDim = agg.Dimensions.Find(semiAdditiveDim.ID);
MeasureGroupAttribute granularity = GetGranularityAttribute((RegularMeasureGroupDimension)semiAdditiveMgDim);
if (semiAdditiveAggDim == null || semiAdditiveAggDim.Attributes.Find(granularity.AttributeID) == null)
{
string sWarning = "This measure group contains only semi-additive measures. This aggregation will not be used when semi-additive measure values are retrieved because it does not include the granularity attribute of the semi-additive dimension ([" + semiAdditiveDim.Name + "].[" + granularity.Attribute.Name + "]). (The Exists-with-a-measure-group function can still run off this aggregation, though.)";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check for aggs on parent-child attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (attr.Attribute.Usage == AttributeUsage.Parent)
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is a parent-child attribute. This is not allowed. The aggregation should include [" + aggDim.CubeDimension.Name + "].[" + aggDim.Dimension.KeyAttribute.Name + "] instead.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//check for aggs on AttributeHierarchyEnabled=false attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (!attr.CubeAttribute.AttributeHierarchyEnabled)
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is not enabled as an attribute hierarchy. This is not allowed.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
//find a list of ALTER statements that alter the DEFAULT_MEMBER property in the calc script
List <string> attributesWithDefaultMemberAlteredInCalcScript = new List <string>();
System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex(@"ALTER\s+CUBE\s+(CURRENTCUBE|\[?" + aggDesign.ParentCube.Name + @"\]?)\s+UPDATE\s+DIMENSION\s+(.+?)\s*\,\s*DEFAULT_MEMBER\s+", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Multiline);
foreach (MdxScript script in aggDesign.ParentCube.MdxScripts)
{
if (script.DefaultScript)
{
StringBuilder sCommands = new StringBuilder();
foreach (Command cmd in script.Commands)
{
sCommands.AppendLine(cmd.Text);
}
foreach (System.Text.RegularExpressions.Match match in regEx.Matches(sCommands.ToString()))
{
try
{
attributesWithDefaultMemberAlteredInCalcScript.Add(match.Groups[2].Captures[0].Value.ToLower());
}
catch { }
}
break;
}
}
//build list of cube dimension attributes that have default members, are not aggregatable, or are marked as AggregationUsage=Full
foreach (MeasureGroupDimension mgDim in aggDesign.Parent.Dimensions)
{
if (mgDim is ManyToManyMeasureGroupDimension)
{
continue; //don't suggest adding any m2m dimensions
}
CubeDimension cd = mgDim.CubeDimension;
foreach (CubeAttribute ca in cd.Attributes)
{
if (!ca.AttributeHierarchyEnabled)
{
continue;
}
if (!IsAtOrAboveGranularity(ca.Attribute, mgDim))
{
continue;
}
foreach (Aggregation agg in aggDesign.Aggregations)
{
AggregationDimension aggDim = agg.Dimensions.Find(cd.ID);
if (ca.Attribute.Usage == AttributeUsage.Parent)
{
if (!(mgDim is RegularMeasureGroupDimension))
{
continue;
}
if (!IsAtOrAboveGranularity(cd.Dimension.KeyAttribute, mgDim))
{
continue;
}
//if this is a parent-child attribute and the key isn't in the agg, then check whether the parent-child attribute has a DefaultMember or is aggregatable
if (aggDim == null || aggDim.Attributes.Find(cd.Dimension.KeyAttribute.ID) == null)
{
string sWarning = "";
if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower()))
{
sWarning += "has a DefaultMember";
}
if (!ca.Attribute.IsAggregatable)
{
if (!string.IsNullOrEmpty(sWarning))
{
sWarning += " and ";
}
sWarning += "is not aggregatable";
}
if (!string.IsNullOrEmpty(sWarning))
{
sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + cd.Dimension.KeyAttribute.Name + "] because [" + cd.Name + "].[" + ca.Attribute.Name + "] is a parent-child attribute which " + sWarning + ".";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
//for non-parent-child attributes...
else if (aggDim == null || aggDim.Attributes.Find(ca.AttributeID) == null)
{
string sWarning = "";
if (!string.IsNullOrEmpty(ca.Attribute.DefaultMember) || attributesWithDefaultMemberAlteredInCalcScript.Contains(((string)("[" + cd.Name + "].[" + ca.Attribute.Name + "]")).ToLower()))
{
sWarning += "has a DefaultMember";
}
if (!ca.Attribute.IsAggregatable)
{
if (!string.IsNullOrEmpty(sWarning))
{
sWarning += " and ";
}
sWarning += "is not aggregatable";
}
if (ca.AggregationUsage == AggregationUsage.Full)
{
if (!string.IsNullOrEmpty(sWarning))
{
sWarning += " and ";
}
sWarning += "is marked as AggregationUsage=Full";
}
if (aggDim != null && AggContainsChild(aggDim, ca.Attribute))
{
continue; //if this attribute is redundant, then no need to warn about it
}
if (!string.IsNullOrEmpty(sWarning))
{
sWarning = "This aggregation should probably contain [" + cd.Name + "].[" + ca.Attribute.Name + "] which " + sWarning + ".";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
}
//look for aggs with redundant attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
bool bHasRedundancy = false;
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (AggContainsParent(aggDim, attr.Attribute))
{
bHasRedundancy = true;
break;
}
}
if (bHasRedundancy)
{
break;
}
}
if (bHasRedundancy)
{
string sWarning = "This aggregation contains redundant attributes which unnecessarily bloat the size of the aggregation.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
//check for aggs on below granularity attributes
foreach (Aggregation agg in aggDesign.Aggregations)
{
foreach (AggregationDimension aggDim in agg.Dimensions)
{
foreach (AggregationAttribute attr in aggDim.Attributes)
{
if (!IsAtOrAboveGranularity(attr.Attribute, aggDim.MeasureGroupDimension))
{
string sWarning = "This aggregation contains [" + aggDim.CubeDimension.Name + "].[" + attr.Attribute.Name + "] which is below granularity. This is not allowed.";
masterWarnings.Add(new AggValidationWarning(agg, sCorrectAggregationDesignName, sWarning, sReportTitle));
}
}
}
}
return(masterWarnings);
}
static void PartitionInfo()
{
string srv_name = @"SCRBMSBDK000660";
string db_name = "FBR_FYPnL_DPRD";
string cube_name = "FYPnL Cube";
string mg_name = "USD";
//TextWriter tw = new StreamWriter("date.txt", true);
Microsoft.AnalysisServices.Server srv;
Microsoft.AnalysisServices.Database db;
srv = new Microsoft.AnalysisServices.Server();
try
{
srv.Connect(srv_name);
logMessageFmt("Databases on [{0}]: {1}", srv_name, srv.Databases.Count);
db = srv.Databases.FindByName(db_name);
Cube cube = db.Cubes.FindByName(cube_name);
CubeDimension cubedim = cube.Dimensions[0];
Dimension dbdim = cubedim.Dimension;
DimensionAttribute dbattr = dbdim.Attributes[0];
//var Source = dbattr.Source;
System.Data.DataSet ds = dbdim.DataSourceView.Schema;
string dsid = db.DataSourceViews[0].DataSourceID;
DataTable dt = db.DataSourceViews[0].Schema.Tables[0];
//db.DataSources[0].
//DataTable dt = ds.Tables["SHARED_DimBrand"];
//ep = dt.ExtendedProperties.
MeasureGroup mg = cube.MeasureGroups.FindByName(mg_name);
MeasureGroupDimension mgd = mg.Dimensions[0];
List <MeasureGroupAttribute> alist = new List <MeasureGroupAttribute>();
if (mgd is RegularMeasureGroupDimension)
{
RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)mgd;
foreach (MeasureGroupAttribute mgattr in rmgd.Attributes)
{
if (mgattr.Type == MeasureGroupAttributeType.Granularity)
{
alist.Add(mgattr);
}
}
//MeasureGroupAttribute mgattr = rmgd.Attributes.f["Key"];
}
Type t = alist[0].KeyColumns[0].Source.GetType();
Measure msr = mg.Measures[0];
foreach (Partition part in mg.Partitions)
{
string src;
TabularBinding tb = part.Source;
if (tb is QueryBinding)
{
src = String.Format("QUERY: {0}", ((QueryBinding)tb).QueryDefinition);
}
else if (tb is TableBinding)
{
src = String.Format("TABLE: {0}.{1}", ((TableBinding)tb).DbSchemaName, ((TableBinding)tb).DbTableName);
}
else if (tb is DsvTableBinding)
{
src = String.Format("DSV: {0}.{1}", ((DsvTableBinding)tb).DataSourceViewID, ((DsvTableBinding)tb).TableID);
}
else
{
src = String.Empty;
}
logMessageFmt("Partition [{0}]: {1}", part.Name, src /*part.EstimatedRows*/);
//part.Process()
}
//Partition part = mg.Partitions[0]; //.FindByName(part_name);
logMessage("Done.");
Console.ReadKey();
}
finally
{
if (srv.Connected == true)
{
srv.Disconnect();
}
}
}
void SetAllEstimatedCounts(ToolBarButton button)
{
//grab the objects I need before the user has a chance to flip to another active window
Project proj = ApplicationObject.ActiveWindow.Project;
Window window = ApplicationObject.ActiveWindow;
Cube cube = (Cube)this.ApplicationObject.ActiveWindow.ProjectItem.Object;
IDesignerHost designer = (IDesignerHost)ApplicationObject.ActiveWindow.Object;
if (MessageBox.Show("Updating all estimated counts with exact counts for all partitions and dimensions\r\ncould take an extremely long time.\r\n\r\nAre you sure you want to continue?", "BIDS Helper - Update All Estimated Counts", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
button.ImageIndex = button.Parent.ImageList.Images.IndexOfKey(STOP_ICON_KEY); //change to a stop icon to allow the user to cancel
Application.DoEvents();
try
{
using (WaitCursor cursor1 = new WaitCursor())
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationGeneral);
IComponentChangeService changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService));
changesvc.OnComponentChanging(cube, null);
System.Collections.Generic.List <string> errors = new System.Collections.Generic.List <string>();
int iProgress = 0;
foreach (MeasureGroup mg in cube.MeasureGroups)
{
ApplicationObject.StatusBar.Progress(true, "Setting Estimated Counts on Measure Group: " + mg.Name, ++iProgress, cube.MeasureGroups.Count + cube.Parent.Dimensions.Count);
if (mg.Partitions.Count > 0)
{
foreach (Partition p in mg.Partitions)
{
p.EstimatedRows = 0;
}
foreach (AggregationDesign aggd in mg.AggregationDesigns)
{
//make sure each measure group dimension and attribute is in each agg design... fixes issue 21220
foreach (MeasureGroupDimension mgd in mg.Dimensions)
{
if (mgd is RegularMeasureGroupDimension)
{
if (!aggd.Dimensions.Contains(mgd.CubeDimensionID))
{
aggd.Dimensions.Add(mgd.CubeDimensionID);
}
AggregationDesignDimension aggdd = aggd.Dimensions[mgd.CubeDimensionID];
foreach (DimensionAttribute da in mgd.Dimension.Attributes)
{
if (da.AttributeHierarchyEnabled && mgd.CubeDimension.Attributes[da.ID].AttributeHierarchyEnabled && !aggdd.Attributes.Contains(da.ID))
{
aggdd.Attributes.Add(da.ID);
}
}
}
}
foreach (AggregationDesignDimension aggdim in aggd.Dimensions)
{
foreach (AggregationDesignAttribute attr in aggdim.Attributes)
{
try
{
attr.EstimatedCount = 0;
attr.Attribute.EstimatedCount = 0;
}
catch { }
}
}
foreach (MeasureGroupDimension mgd in mg.Dimensions)
{
if (mgd is RegularMeasureGroupDimension)
{
RegularMeasureGroupDimension dim = (RegularMeasureGroupDimension)mgd;
foreach (Partition p in mg.Partitions)
{
if (p.AggregationDesignID == aggd.ID)
{
try
{
SetEstimatedCountsOnPartitionThreadInfo info = new SetEstimatedCountsOnPartitionThreadInfo();
info.instance = this;
info.aggDesign = aggd;
info.measureGroupDimension = dim;
info.partition = p;
//run as a separate thread so that the main app stays responsive (so you can click the cancel button)
System.Threading.ThreadPool.QueueUserWorkItem(new System.Threading.WaitCallback(StartSetEstimatedCountsOnPartition), info);
while (!info.done)
{
System.Threading.Thread.Sleep(100);
Application.DoEvents(); //keeps main app responsive
if (CheckCancelled())
{
return;
}
}
errors.AddRange(info.errors);
}
catch (Exception ex)
{
errors.Add("BIDS Helper error setting estimated counts on partition " + p.Name + " of measure group " + mg.Name + ": " + ex.Message);
}
}
}
}
}
}
//now fill in the count on partitions without agg designs
foreach (Partition p in mg.Partitions)
{
if (p.AggregationDesign == null)
{
try
{
SetEstimatedCountsOnPartitionThreadInfo info = new SetEstimatedCountsOnPartitionThreadInfo();
info.instance = this;
info.partition = p;
//run as a separate thread so that the main app stays responsive (so you can click the cancel button)
System.Threading.ThreadPool.QueueUserWorkItem(new System.Threading.WaitCallback(StartSetEstimatedCountsOnPartition), info);
while (!info.done)
{
System.Threading.Thread.Sleep(100);
Application.DoEvents(); //keeps main app responsive
if (CheckCancelled())
{
return;
}
}
errors.AddRange(info.errors);
}
catch (Exception ex)
{
errors.Add("BIDS Helper error setting estimated counts on partition " + p.Name + " of measure group " + mg.Name + ": " + ex.Message);
}
}
}
long iMeasureGroupRowsCount = 0;
foreach (Partition p in mg.Partitions)
{
iMeasureGroupRowsCount += p.EstimatedRows;
}
mg.EstimatedRows = iMeasureGroupRowsCount;
}
}
changesvc.OnComponentChanged(cube, null, null, null); //marks the cube designer as dirty
foreach (ProjectItem pi in proj.ProjectItems)
{
try
{
if (!(pi.Object is Dimension))
{
continue;
}
}
catch
{
continue; //doing the above seems to blow up on certain objects because of threading? this fixes the problem
}
Dimension dim = (Dimension)pi.Object;
ApplicationObject.StatusBar.Progress(true, "Setting Estimated Counts on Dimension: " + dim.Name, ++iProgress, cube.MeasureGroups.Count + cube.Parent.Dimensions.Count);
//open but don't show the dimension designer so you can get at the change service so you can mark it dirty
bool bIsOpen = pi.get_IsOpen(EnvDTE.Constants.vsViewKindDesigner);
Window win = null;
if (bIsOpen)
{
foreach (Window w in ApplicationObject.Windows)
{
if (w.ProjectItem != null && w.ProjectItem.Document != null && w.ProjectItem.Document.FullName == pi.Document.FullName)
{
win = w;
break;
}
}
}
if (win == null)
{
win = pi.Open(EnvDTE.Constants.vsViewKindDesigner);
if (!bIsOpen)
{
win.Visible = false;
}
}
designer = (IDesignerHost)win.Object;
changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService));
changesvc.OnComponentChanging(dim, null);
if (dim.DataSource != null)
{
try
{
DataSourceConnection openedDataSourceConnection = DSVUtilities.GetOpenedDataSourceConnection(dim.DataSource);
foreach (DimensionAttribute attr in dim.Attributes)
{
SetEstimatedCountsOnDimensionThreadInfo info = new SetEstimatedCountsOnDimensionThreadInfo();
info.instance = this;
info.attribute = attr;
info.connection = openedDataSourceConnection;
//run as a separate thread so that the main app stays responsive (so you can click the cancel button)
System.Threading.ThreadPool.QueueUserWorkItem(new System.Threading.WaitCallback(StartSetEstimatedCountsOnDimension), info);
while (!info.done)
{
System.Threading.Thread.Sleep(100);
Application.DoEvents(); //keeps main app responsive
if (CheckCancelled())
{
return;
}
}
errors.AddRange(info.errors);
}
}
catch (Exception ex)
{
errors.Add("BIDS Helper error setting estimated counts on dimension " + dim.Name + ": " + ex.Message);
}
}
changesvc.OnComponentChanged(dim, null, null, null);
}
AddErrorsToVSErrorList(window, errors.ToArray());
}
}
finally
{
try
{
button.ImageIndex = button.Parent.ImageList.Images.IndexOfKey(SET_ESTIMATED_COUNTS_ICON_KEY);
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationGeneral);
ApplicationObject.StatusBar.Progress(false, "", 1, 1);
}
catch { }
}
}
protected void IterateDsvColumns(DataSourceView dsv)
{
m_dsv = dsv;
//add all DSV columns to a list
unusedColumns.Clear();
usedColumns.Clear();
foreach (DataTable t in dsv.Schema.Tables)
{
foreach (DataColumn c in t.Columns)
{
unusedColumns.Add("[" + t.TableName + "].[" + c.ColumnName + "]", new UnusedColumn(c, dsv));
}
}
//remove columns that are used in dimensions
foreach (Dimension dim in dsv.Parent.Dimensions)
{
if (dim.DataSourceView != null && dim.DataSourceView.ID == dsv.ID)
{
foreach (DimensionAttribute attr in dim.Attributes)
{
foreach (DataItem di in attr.KeyColumns)
{
ProcessDataItemInLists(di, "Dimension Attribute Key");
}
ProcessDataItemInLists(attr.NameColumn, "Dimension Attribute Name");
ProcessDataItemInLists(attr.ValueColumn, "Dimension Attribute Value");
ProcessDataItemInLists(attr.UnaryOperatorColumn, "Dimension Attribute Unary Operator");
ProcessDataItemInLists(attr.SkippedLevelsColumn, "Dimension Attribute Skipped Levels");
ProcessDataItemInLists(attr.CustomRollupColumn, "Dimension Attribute Custom Rollup");
ProcessDataItemInLists(attr.CustomRollupPropertiesColumn, "Dimension Attribute Custom Rollup Properties");
foreach (AttributeTranslation tran in attr.Translations)
{
ProcessDataItemInLists(tran.CaptionColumn, "Dimension Attribute Translation");
}
}
}
}
foreach (Cube cube in dsv.Parent.Cubes)
{
if (cube.DataSourceView != null && cube.DataSourceView.ID == dsv.ID)
{
foreach (MeasureGroup mg in cube.MeasureGroups)
{
//remove columns that are used in measures
foreach (Measure m in mg.Measures)
{
ProcessDataItemInLists(m.Source, "Measure");
}
//remove columns that are used in dimension relationships
foreach (MeasureGroupDimension mgdim in mg.Dimensions)
{
if (mgdim is ManyToManyMeasureGroupDimension)
{
//no columns to remove
}
else if (mgdim is DataMiningMeasureGroupDimension)
{
//no columns to remove
}
else if (mgdim is RegularMeasureGroupDimension)
{
//Degenerate dimensions and Reference dimensions
RegularMeasureGroupDimension regMDdim = (RegularMeasureGroupDimension)mgdim;
foreach (MeasureGroupAttribute mga in regMDdim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
foreach (DataItem di3 in mga.KeyColumns)
{
ProcessDataItemInLists(di3, "Fact Table Dimension Key");
}
}
}
}
}
}
}
}
//remove mining structure columns
foreach (MiningStructure structure in dsv.Parent.MiningStructures)
{
if (structure.DataSourceView != null && structure.DataSourceView.ID == dsv.ID)
{
RecurseMiningStructureColumnsAndProcessDataItemInLists(structure.Columns);
}
}
}
public void CreateInternetSalesMeasureGroup(Cube cube)
{
// Create the Internet Sales measure group
Database db = cube.Parent;
MeasureGroup mg = cube.MeasureGroups.Add("Internet Sales");
mg.StorageMode = StorageMode.Molap;
mg.ProcessingMode = ProcessingMode.LazyAggregations;
mg.Type = MeasureGroupType.Sales;
#region Create measures
Measure meas;
//meas = mg.Measures.Add("Internet Sales Amount"); //加一个维度
//meas.AggregateFunction = AggregationFunction.Sum; //维度的计算方法
//meas.MeasureExpression = "[Internet Sales Amount] * [Average Rate]"; //维度表达式
//meas.FormatString = "Currency"; //维度格式字符
//meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesAmount"); //
meas = mg.Measures.Add("Internet Order Quantity");
meas.AggregateFunction = AggregationFunction.Sum;
meas.FormatString = "#,#";
meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "OrderQuantity");
meas = mg.Measures.Add("Internet Unit Price");
meas.AggregateFunction = AggregationFunction.Sum;
meas.FormatString = "Currency";
meas.Visible = false;
meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "UnitPrice");
//meas = mg.Measures.Add("Internet Total Product Cost");
//meas.AggregateFunction = AggregationFunction.Sum;
//meas.MeasureExpression = "[Internet Total Product Cost] * [Average Rate]";
//meas.FormatString = "Currency";
//meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "TotalProductCost");
meas = mg.Measures.Add("Internet Order Count");
meas.AggregateFunction = AggregationFunction.Count;
meas.FormatString = "#,#";
meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ProductKey");
#endregion
#region Create measure group dimensions
CubeDimension cubeDim;
RegularMeasureGroupDimension regMgDim;
ManyToManyMeasureGroupDimension mmMgDim;
MeasureGroupAttribute mgAttr;
//cubeDim = cube.Dimensions.GetByName("Date");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "OrderDateKey"));
//cubeDim = cube.Dimensions.GetByName("Ship Date");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ShipDateKey"));
//cubeDim = cube.Dimensions.GetByName("Delivery Date");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Date").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "DueDateKey"));
cubeDim = cube.Dimensions.GetByName("Customer");
regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
mg.Dimensions.Add(regMgDim);
mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Full Name").ID);
mgAttr.Type = MeasureGroupAttributeType.Granularity;
mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "CustomerKey"));
//cubeDim = cube.Dimensions.GetByName("Product");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Product Name").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "ProductKey"));
//cubeDim = cube.Dimensions.GetByName("Source Currency");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Currency").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "CurrencyKey"));
//cubeDim = cube.Dimensions.GetByName("Sales Reason");
//mmMgDim = new ManyToManyMeasureGroupDimension();
//mmMgDim.CubeDimensionID = cubeDim.ID;
//mmMgDim.MeasureGroupID = cube.MeasureGroups.GetByName("Sales Reasons").ID;
//mg.Dimensions.Add(mmMgDim);
//cubeDim = cube.Dimensions.GetByName("Internet Sales Order Details");
//regMgDim = new RegularMeasureGroupDimension(cubeDim.ID);
//mg.Dimensions.Add(regMgDim);
//mgAttr = regMgDim.Attributes.Add(cubeDim.Dimension.Attributes.GetByName("Sales Order Key").ID);
//mgAttr.Type = MeasureGroupAttributeType.Granularity;
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesOrderNumber"));
//mgAttr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], "FactInternetSales", "SalesOrderLineNumber"));
#endregion
#region Create partitions
Partition part;
part = mg.Partitions.Add("Internet_Sales_184");
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey <= '184'");
part.Annotations.Add("LastOrderDateKey", "184");
part = mg.Partitions.Add("Internet_Sales_549");
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '184' AND OrderDateKey <= '549'");
part.Annotations.Add("LastOrderDateKey", "549");
part = mg.Partitions.Add("Internet_Sales_914");
part.StorageMode = StorageMode.Molap;
part.Source = new QueryBinding(db.DataSources[0].ID, "SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey > '549' AND OrderDateKey <= '914'");
part.Annotations.Add("LastOrderDateKey", "914");
#endregion
cube.Update(UpdateOptions.ExpandFull);
}
private MeasureGroupAttribute GetGranularityAttribute(RegularMeasureGroupDimension rmgd)
{
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
return mga;
}
}
throw new Exception("Can't find granularity attribute for dimension " + rmgd.CubeDimension.Name + " in measure group " + rmgd.Parent.Name);
}
/*
* All relationships - Relationship Type
* - Measure Group Name
* - Dim Name
*/
/*
* Regular Col1 - Ganularity Attribute Name
* Col2 - Dimension Column(s) Name
* Col3 - Measure Group Columns(s) Name
*/
private static void getRegularMeasureGroupAttributeUsage(List<DimensionUsage> dimUsage, MeasureGroup mg, RegularMeasureGroupDimension regMDdim)
{
string tableId = string.Empty;
DimensionUsage usage = new DimensionUsage("Regular", mg, regMDdim.CubeDimension, regMDdim.Dimension);//, cb.TableID, cb.ColumnID);
usage.Column1Name = "Granularity Attribute";
usage.Column2Name = "Dimension Column";
usage.Column3Name = "Measure Group Columns";
foreach (MeasureGroupAttribute mga in regMDdim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
tableId = string.Empty;
usage.Column1Value = mga.Attribute.Name;
System.Diagnostics.Trace.Write(",mga " + mga.CubeAttribute.Attribute.Name);
//foreach (DataItem di in mga.KeyColumns)
//{
//ColumnBinding cb = (ColumnBinding)di.Source;
// TODO - get the key columns for the attribute, not just its name
//System.Diagnostics.Trace.WriteLine(",di " + di.Source.ToString());
foreach (DataItem di2 in mga.Attribute.KeyColumns)
{
if (di2.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di2.Source).TableID;
DataSourceView dsv = mga.Parent.Dimension.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column2Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column2Value += ((ColumnBinding)di2.Source).TableID + ".";
}
usage.Column2Value += ((ColumnBinding)di2.Source).ColumnID + "\n";
}
}
foreach (DataItem di3 in mga.KeyColumns)
{
if (di3.Source is ColumnBinding)
{
tableId = ((ColumnBinding)di3.Source).TableID;
DataSourceView dsv = mga.ParentCube.DataSourceView;
if (dsv.Schema.Tables.Contains(tableId))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(tableId)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column3Value += oTable.ExtendedProperties["FriendlyName"] + ".";
}
}
else
{
usage.Column3Value += ((ColumnBinding)di3.Source).TableID + ".";
}
usage.Column3Value += ((ColumnBinding)di3.Source).ColumnID;
usage.Column3Value += " (" + di3.NullProcessing.ToString().Substring(0, 1) + ")";
usage.Column3Value += "\n";
}
}
//}
}
}
dimUsage.Add(usage);
}
