private DataTable getMeasureGroupPartitions(MeasureGroup MG)
{
DataTable PT = new DataTable();
PT.Columns.Add("Partition Name");
PT.Columns.Add("Source");
// PT.Columns.Add("Estimated Size in Bytes");
// PT.Columns.Add("Estimated Rows");
PT.Columns.Add("Processing Status");
//PT.Columns.Add("DataSource");
//PT.Columns.Add("DataSourceView");
//PT.Columns.Add("DirectQueryUsage");
//PT.Columns.Add("ID");
//PT.Columns.Add("IsLoaded");
//PT.Columns.Add("LastProcessed");
//PT.Columns.Add("LastSchemaUpdate");
//PT.Columns.Add("Parent");
//PT.Columns.Add("Slice");
//PT.Columns.Add("Type");
PartitionsGrid.Visible = true;
PartitionCollection PC = MG.Partitions;
foreach (Partition P in PC)
{
if (P.Source.ToString() == "Microsoft.AnalysisServices.QueryBinding")
{
PT.Rows.Add(P.Name, ((QueryBinding)P.Source).QueryDefinition.ToString() //, P.EstimatedSize, P.EstimatedRows
, P.State
//, P.DataSource, P.DataSourceView, P.DirectQueryUsage
//, P.ID
//, P.IsLoaded, P.LastProcessed
//, P.LastSchemaUpdate, P.Parent, P.Slice, P.Type
);
}
else
{
//MessageBox.Show("Test");
//MeasureGroupBinding MGB = MG.Source;
DsvTableBinding DVB = (DsvTableBinding)P.Source; //(DsvTableBinding)MGB.Clone();
//MessageBox.Show(DVB.TableID.ToString());
//MessageBox.Show(P.DataSourceView.Schema.Tables[DVB.TableID].ToString());
PT.Rows.Add(P.Name, P.DataSourceView.Schema.Tables[DVB.TableID].ToString() //, P.EstimatedSize, P.EstimatedRows
, P.State
//, P.DataSource, P.DataSourceView, P.DirectQueryUsage
//, P.ID
//, P.IsLoaded, P.LastProcessed
//, P.LastSchemaUpdate, P.Parent, P.Slice, P.Type
);
}
}
return(PT);
}
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 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);
}
void backgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
try
{
MeasureGroup mg = currentStat.IntermediateMeasureGroup;
DataSource oDataSource = mg.Parent.DataSource;
DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, MeasureGroupHealthCheckPlugin.GetTableIdForDataItem(mg.Measures[0].Source));
DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID];
//check whether this fact table uses an alternate datasource
if (dtTable.ExtendedProperties.ContainsKey("DataSourceID"))
{
oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()];
}
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false);
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = 0;
}
else
{
throw new Exception("Couldn't open connection from data source " + oDataSource.Name);
}
command = openedDataSourceConnection.CreateCommand();
command.CommandText = currentStat.SQL;
if (backgroundWorker.CancellationPending)
{
return;
}
System.Data.Common.DbDataReader reader = null;
try
{
try
{
reader = command.ExecuteReader();
}
catch (Exception innerEx)
{
if (backgroundWorker.CancellationPending)
{
return;
}
else
{
throw innerEx;
}
}
if (!backgroundWorker.CancellationPending && reader.Read())
{
lock (command)
{
if (Convert.IsDBNull(reader["OriginalRecordCount"]))
currentStat.OriginalRecordCount = null;
else
currentStat.OriginalRecordCount = Convert.ToInt64(reader["OriginalRecordCount"]);
if (Convert.IsDBNull(reader["CompressedRecordCount"]))
currentStat.CompressedRecordCount = null;
else
currentStat.CompressedRecordCount = Convert.ToInt64(reader["CompressedRecordCount"]);
if (Convert.IsDBNull(reader["MatrixDimensionRecordCount"]))
currentStat.MatrixDimensionRecordCount = null;
else
currentStat.MatrixDimensionRecordCount = Convert.ToInt64(reader["MatrixDimensionRecordCount"]);
currentStat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete;
}
foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list)
{
if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL)
{
stat.OriginalRecordCount = currentStat.OriginalRecordCount;
stat.CompressedRecordCount = currentStat.CompressedRecordCount;
stat.MatrixDimensionRecordCount = currentStat.MatrixDimensionRecordCount;
stat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete;
}
}
}
}
finally
{
try
{
if ((reader != null) && !reader.IsClosed)
{
reader.Close();
}
}
catch { }
command = null;
}
}
finally
{
try
{
openedDataSourceConnection.Close();
}
catch { }
}
}
catch (Exception ex)
{
currentStat.Error = ex.Message + "\r\n" + ex.StackTrace;
foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list)
{
if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL)
{
stat.Error = currentStat.Error;
}
}
}
}
internal static string GetQueryDefinition(Database d, NamedComponent nc, Microsoft.AnalysisServices.Binding b, List <DataItem> columnsNeeded)
{
StringBuilder sQuery = new StringBuilder();
if (b is DsvTableBinding)
{
DsvTableBinding oDsvTableBinding = (DsvTableBinding)b;
DataSourceView oDSV = d.DataSourceViews[oDsvTableBinding.DataSourceViewID];
DataTable oTable = oDSV.Schema.Tables[oDsvTableBinding.TableID];
if (oTable == null)
{
throw new Exception("DSV table " + oDsvTableBinding.TableID + " not found");
}
else if (!oTable.ExtendedProperties.ContainsKey("QueryDefinition") && oTable.ExtendedProperties.ContainsKey("DbTableName"))
{
foreach (DataColumn oColumn in oTable.Columns)
{
bool bFoundColumn = false;
if (columnsNeeded == null)
{
bFoundColumn = true;
}
else
{
foreach (DataItem di in columnsNeeded)
{
if (GetColumnBindingForDataItem(di).TableID == oTable.TableName && GetColumnBindingForDataItem(di).ColumnID == oColumn.ColumnName)
{
bFoundColumn = true;
}
}
}
if (bFoundColumn)
{
if (sQuery.Length == 0)
{
sQuery.Append("select ");
}
else
{
sQuery.Append(",");
}
if (!oColumn.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
sQuery.Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq);
}
else
{
sQuery.Append(oColumn.ExtendedProperties["ComputedColumnExpression"].ToString()).Append(" as ").Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq);
}
}
}
if (sQuery.Length == 0)
{
throw new Exception("There was a problem constructing the query.");
}
sQuery.Append("from ");
if (oTable.ExtendedProperties.ContainsKey("DbSchemaName"))
{
sQuery.Append(sq).Append(oTable.ExtendedProperties["DbSchemaName"].ToString()).Append(fq).Append(".");
}
sQuery.Append(sq).Append(oTable.ExtendedProperties["DbTableName"].ToString());
sQuery.Append(fq).Append(" ").Append(sq).Append(oTable.ExtendedProperties["FriendlyName"].ToString()).AppendLine(fq);
}
else if (oTable.ExtendedProperties.ContainsKey("QueryDefinition"))
{
sQuery.AppendLine("select *");
sQuery.AppendLine("from (");
sQuery.AppendLine(oTable.ExtendedProperties["QueryDefinition"].ToString());
sQuery.AppendLine(") x");
}
else
{
throw new Exception("Current the code does not support this type of query.");
}
}
else if (b is QueryBinding)
{
QueryBinding oQueryBinding = (QueryBinding)b;
sQuery.Append(oQueryBinding.QueryDefinition);
}
else if (b is ColumnBinding)
{
ColumnBinding cb = (ColumnBinding)b;
object parent = cb.Parent;
DataTable dt = d.DataSourceViews[0].Schema.Tables[cb.TableID];
if (nc is DimensionAttribute)
{
DimensionAttribute da = (DimensionAttribute)nc;
if (da.Parent.KeyAttribute.KeyColumns.Count != 1)
{
throw new Exception("Attribute " + da.Parent.KeyAttribute.Name + " has a composite key. This is not supported for a key attribute of a dimension.");
}
string sDsvID = ((DimensionAttribute)nc).Parent.DataSourceView.ID;
columnsNeeded.Add(new DataItem(cb.Clone()));
columnsNeeded.Add(da.Parent.KeyAttribute.KeyColumns[0]);
return(GetQueryDefinition(d, nc, new DsvTableBinding(sDsvID, cb.TableID), columnsNeeded));
}
else
{
throw new Exception("GetQueryDefinition does not currently support a ColumnBinding on a object of type " + nc.GetType().Name);
}
}
else
{
throw new Exception("Not a supported query binding type: " + b.GetType().FullName);
}
return(sQuery.ToString());
}
public static void Check(MeasureGroup mg, IDesignerHost designer)
{
if (mg.Measures.Count == 0)
{
throw new Exception(mg.Name + " has no measures.");
}
if (mg.IsLinked)
{
throw new Exception(mg.Name + " is a linked measure group. Run this Measure Group Health Check on the source measure group.");
}
DataSource oDataSource = mg.Parent.DataSource;
DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, GetTableIdForDataItem(mg.Measures[0].Source));
DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID];
//check whether this fact table uses an alternate datasource
if (dtTable.ExtendedProperties.ContainsKey("DataSourceID"))
{
oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()];
}
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false);
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = (int)oDataSource.Timeout.TotalSeconds;
}
}
catch { }
if (openedDataSourceConnection == null)
{
MessageBox.Show("Unable to connect to data source [" + oDataSource.Name + "]");
return;
}
sq = openedDataSourceConnection.Cartridge.IdentStartQuote;
fq = openedDataSourceConnection.Cartridge.IdentEndQuote;
string sBitSqlDatatype = "bit";
string sCountBig = "count_big(";
string sCountBigEnd = ")";
string sFloorFunctionBegin = "floor(";
string sFloorFunctionEnd = ")";
if (openedDataSourceConnection.DBServerName == "Oracle")
{
sBitSqlDatatype = "number(1,0)";
sCountBig = "count(";
}
else if (openedDataSourceConnection.DBServerName == "Teradata")
{
sBitSqlDatatype = "numeric(1,0)";
sCountBig = "cast(count(";
sCountBigEnd = ") as bigint)";
sFloorFunctionBegin = "cast(";
sFloorFunctionEnd = " as bigint)";
}
string sFactQuery = GetQueryDefinition(mg.ParentDatabase, mg, oTblBinding, null);
StringBuilder sOuterQuery = new StringBuilder();
foreach (Measure m in mg.Measures)
{
//TODO: measure expressions
if ((m.AggregateFunction == AggregationFunction.Sum && !(m.Source.Source is RowBinding)) ||
m.AggregateFunction == AggregationFunction.AverageOfChildren ||
m.AggregateFunction == AggregationFunction.ByAccount ||
m.AggregateFunction == AggregationFunction.FirstChild ||
m.AggregateFunction == AggregationFunction.FirstNonEmpty ||
m.AggregateFunction == AggregationFunction.LastChild ||
m.AggregateFunction == AggregationFunction.LastNonEmpty ||
m.AggregateFunction == AggregationFunction.Min ||
m.AggregateFunction == AggregationFunction.Max ||
m.AggregateFunction == AggregationFunction.None)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (col.DataType == typeof(DateTime))
{
continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates
}
if (sOuterQuery.Length > 0)
{
sOuterQuery.Append(",");
}
else
{
sOuterQuery.Append("select ");
}
string sNegativeSign = string.Empty;
if (col.DataType == typeof(bool))
{
sNegativeSign = "-"; //true in sql is 1, but SSAS treats true as -1
}
if (m.AggregateFunction == AggregationFunction.Min ||
m.AggregateFunction == AggregationFunction.Max ||
m.AggregateFunction == AggregationFunction.None)
{
sOuterQuery.Append("max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
}
else
{
sOuterQuery.Append("sum(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
}
sOuterQuery.Append(",min(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append(sFloorFunctionEnd).Append(" <> ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0").Append(sFloorFunctionEnd).Append(" <> cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0 as float) then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
else if (m.AggregateFunction == AggregationFunction.Count ||
m.AggregateFunction == AggregationFunction.DistinctCount ||
(m.AggregateFunction == AggregationFunction.Sum && m.Source.Source is RowBinding))
{
if (sOuterQuery.Length > 0)
{
sOuterQuery.Append(",");
}
else
{
sOuterQuery.Append("select ");
}
if (m.Source.Source is RowBinding)
{
if (m.AggregateFunction == AggregationFunction.DistinctCount)
{
throw new Exception("RowBinding on a distinct count not allowed by Analysis Services");
}
else
{
sOuterQuery.Append(sCountBig).Append("*").Append(sCountBigEnd).AppendLine();
}
sOuterQuery.Append(",0").AppendLine();
sOuterQuery.Append(",1").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
else
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (m.AggregateFunction == AggregationFunction.DistinctCount)
{
sOuterQuery.Append(sCountBig).Append("distinct ").Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine();
}
else if (col.DataType == typeof(Byte[]))
{
sOuterQuery.Append("sum(cast(case when ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" is not null then 1 else 0 end as float))").AppendLine();
}
else
{
sOuterQuery.Append(sCountBig).Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine();
}
sOuterQuery.Append(",0").AppendLine();
sOuterQuery.Append(",1").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
}
else
{
throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported!");
}
}
if (sOuterQuery.Length == 0)
{
return;
}
sOuterQuery.AppendLine("from (").Append(sFactQuery).AppendLine(") fact");
DataSet ds = new DataSet();
//openedDataSourceConnection.QueryTimeOut = 0; //just inherit from the datasource
openedDataSourceConnection.Fill(ds, sOuterQuery.ToString());
DataRow row = ds.Tables[0].Rows[0];
openedDataSourceConnection.Close();
List <MeasureHealthCheckResult> measureResults = new List <MeasureHealthCheckResult>();
int i = 0;
foreach (Measure m in mg.Measures)
{
if (m.AggregateFunction == AggregationFunction.Sum ||
m.AggregateFunction == AggregationFunction.AverageOfChildren ||
m.AggregateFunction == AggregationFunction.ByAccount ||
m.AggregateFunction == AggregationFunction.FirstChild ||
m.AggregateFunction == AggregationFunction.FirstNonEmpty ||
m.AggregateFunction == AggregationFunction.LastChild ||
m.AggregateFunction == AggregationFunction.LastNonEmpty ||
m.AggregateFunction == AggregationFunction.Count ||
m.AggregateFunction == AggregationFunction.DistinctCount ||
m.AggregateFunction == AggregationFunction.Min ||
m.AggregateFunction == AggregationFunction.Max ||
m.AggregateFunction == AggregationFunction.None)
{
double dsvColMaxValue = 0;
bool dsvColAllowsDecimals = false;
if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (col.DataType == typeof(DateTime))
{
continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates
}
MeasureDataTypeOption dsvOption = GetMeasureDataTypeOptionForType(col.DataType);
if (dsvOption != null)
{
dsvColMaxValue = dsvOption.max;
dsvColAllowsDecimals = dsvOption.allowsDecimals;
}
}
double?total = (!Convert.IsDBNull(row[i * 5]) ? Convert.ToDouble(row[i * 5]) : (double?)null);
double?min = (!Convert.IsDBNull(row[i * 5 + 1]) ? Convert.ToDouble(row[i * 5 + 1]) : (double?)null);
double?max = (!Convert.IsDBNull(row[i * 5 + 2]) ? Convert.ToDouble(row[i * 5 + 2]) : (double?)null);
bool hasDecimals = (!Convert.IsDBNull(row[i * 5 + 3]) ? Convert.ToBoolean(row[i * 5 + 3]) : false);
bool hasMoreThan4Decimals = (!Convert.IsDBNull(row[i * 5 + 4]) ? Convert.ToBoolean(row[i * 5 + 4]) : false);
MeasureDataTypeOption oldDataTypeOption = GetMeasureDataTypeOptionForMeasure(m);
double recommendedMaxValue = double.MaxValue;
List <MeasureDataTypeOption> possible = new List <MeasureDataTypeOption>();
foreach (MeasureDataTypeOption option in dataTypeOptions)
{
if (
(total == null || (option.max >= total && option.min <= total)) &&
(max == null || option.max >= max) &&
(min == null || option.min <= min) &&
(!hasDecimals || option.allowsDecimals) &&
(!hasMoreThan4Decimals || option.allowsMoreThan4Decimals)
)
{
possible.Add(option);
if (
(total == null || (total * FreeSpaceFactor < option.max && total * FreeSpaceFactor > option.min)) &&
option.max < recommendedMaxValue &&
option.max >= dsvColMaxValue &&
(dsvColAllowsDecimals == option.allowsDecimals) &&
(option.oleDbType != OleDbType.Single) //never recommend Single
)
{
recommendedMaxValue = option.max;
}
}
}
foreach (MeasureDataTypeOption option in dataTypeOptions)
{
if (option.max == recommendedMaxValue)
{
Type dsvDataType = null; //don't bother getting the DSV datatype for count or DistinctCount measures
if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
dsvDataType = col.DataType;
}
MeasureHealthCheckResult result = new MeasureHealthCheckResult(m, FormatDouble(total), FormatDouble(min), FormatDouble(max), hasDecimals, hasMoreThan4Decimals, possible.ToArray(), option, oldDataTypeOption, dsvDataType);
measureResults.Add(result);
break;
}
}
i++;
}
else
{
throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported");
}
}
BIDSHelper.SSAS.MeasureGroupHealthCheckForm form = new BIDSHelper.SSAS.MeasureGroupHealthCheckForm();
form.measureDataTypeOptionBindingSource.DataSource = dataTypeOptions;
form.measureHealthCheckResultBindingSource.DataSource = measureResults;
form.Text = "Measure Group Health Check: " + mg.Name;
DialogResult dialogResult = form.ShowDialog();
if (dialogResult == DialogResult.OK)
{
foreach (MeasureHealthCheckResult r in measureResults)
{
if (r.CurrentDataType != r.DataType)
{
//save change
if (r.Measure.AggregateFunction == AggregationFunction.Count ||
r.Measure.AggregateFunction == AggregationFunction.DistinctCount)
{
r.Measure.DataType = r.DataType.dataType;
}
else
{
r.Measure.Source.DataType = r.DataType.oleDbType;
r.Measure.DataType = MeasureDataType.Inherited;
}
//mark cube object as dirty
IComponentChangeService changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService));
changesvc.OnComponentChanging(mg.Parent, null);
changesvc.OnComponentChanged(mg.Parent, null, null, null); //marks the cube designer as dirty
}
}
}
}
public static void Check(MeasureGroup mg, IDesignerHost designer)
{
if (mg.Measures.Count == 0) throw new Exception(mg.Name + " has no measures.");
if (mg.IsLinked) throw new Exception(mg.Name + " is a linked measure group. Run this Measure Group Health Check on the source measure group.");
DataSource oDataSource = mg.Parent.DataSource;
DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, GetTableIdForDataItem(mg.Measures[0].Source));
DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID];
//check whether this fact table uses an alternate datasource
if (dtTable.ExtendedProperties.ContainsKey("DataSourceID"))
{
oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()];
}
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false);
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = (int)oDataSource.Timeout.TotalSeconds;
}
}
catch { }
sq = openedDataSourceConnection.Cartridge.IdentStartQuote;
fq = openedDataSourceConnection.Cartridge.IdentEndQuote;
string sBitSqlDatatype = "bit";
string sCountBig = "count_big(";
string sCountBigEnd = ")";
string sFloorFunctionBegin = "floor(";
string sFloorFunctionEnd = ")";
if (openedDataSourceConnection.DBServerName == "Oracle")
{
sBitSqlDatatype = "number(1,0)";
sCountBig = "count(";
}
else if (openedDataSourceConnection.DBServerName == "Teradata")
{
sBitSqlDatatype = "numeric(1,0)";
sCountBig = "cast(count(";
sCountBigEnd = ") as bigint)";
sFloorFunctionBegin = "cast(";
sFloorFunctionEnd = " as bigint)";
}
string sFactQuery = GetQueryDefinition(mg.ParentDatabase, mg, oTblBinding, null);
StringBuilder sOuterQuery = new StringBuilder();
foreach (Measure m in mg.Measures)
{
//TODO: measure expressions
if ((m.AggregateFunction == AggregationFunction.Sum && !(m.Source.Source is RowBinding))
|| m.AggregateFunction == AggregationFunction.AverageOfChildren
|| m.AggregateFunction == AggregationFunction.ByAccount
|| m.AggregateFunction == AggregationFunction.FirstChild
|| m.AggregateFunction == AggregationFunction.FirstNonEmpty
|| m.AggregateFunction == AggregationFunction.LastChild
|| m.AggregateFunction == AggregationFunction.LastNonEmpty
|| m.AggregateFunction == AggregationFunction.Min
|| m.AggregateFunction == AggregationFunction.Max
|| m.AggregateFunction == AggregationFunction.None)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (col.DataType == typeof(DateTime))
continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates
if (sOuterQuery.Length > 0)
sOuterQuery.Append(",");
else
sOuterQuery.Append("select ");
string sNegativeSign = string.Empty;
if (col.DataType == typeof(bool))
sNegativeSign = "-"; //true in sql is 1, but SSAS treats true as -1
if (m.AggregateFunction == AggregationFunction.Min
|| m.AggregateFunction == AggregationFunction.Max
|| m.AggregateFunction == AggregationFunction.None)
sOuterQuery.Append("max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
else
sOuterQuery.Append("sum(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",min(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine();
sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append(sFloorFunctionEnd).Append(" <> ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0").Append(sFloorFunctionEnd).Append(" <> cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0 as float) then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
else if (m.AggregateFunction == AggregationFunction.Count
|| m.AggregateFunction == AggregationFunction.DistinctCount
|| (m.AggregateFunction == AggregationFunction.Sum && m.Source.Source is RowBinding))
{
if (sOuterQuery.Length > 0)
sOuterQuery.Append(",");
else
sOuterQuery.Append("select ");
if (m.Source.Source is RowBinding)
{
if (m.AggregateFunction == AggregationFunction.DistinctCount)
throw new Exception("RowBinding on a distinct count not allowed by Analysis Services");
else
sOuterQuery.Append(sCountBig).Append("*").Append(sCountBigEnd).AppendLine();
sOuterQuery.Append(",0").AppendLine();
sOuterQuery.Append(",1").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
else
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (m.AggregateFunction == AggregationFunction.DistinctCount)
sOuterQuery.Append(sCountBig).Append("distinct ").Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine();
else if (col.DataType == typeof(Byte[]))
sOuterQuery.Append("sum(cast(case when ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" is not null then 1 else 0 end as float))").AppendLine();
else
sOuterQuery.Append(sCountBig).Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine();
sOuterQuery.Append(",0").AppendLine();
sOuterQuery.Append(",1").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine();
}
}
else
{
throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported!");
}
}
if (sOuterQuery.Length == 0) return;
sOuterQuery.AppendLine("from (").Append(sFactQuery).AppendLine(") fact");
DataSet ds = new DataSet();
//openedDataSourceConnection.QueryTimeOut = 0; //just inherit from the datasource
openedDataSourceConnection.Fill(ds, sOuterQuery.ToString());
DataRow row = ds.Tables[0].Rows[0];
openedDataSourceConnection.Close();
List<MeasureHealthCheckResult> measureResults = new List<MeasureHealthCheckResult>();
int i = 0;
foreach (Measure m in mg.Measures)
{
if (m.AggregateFunction == AggregationFunction.Sum
|| m.AggregateFunction == AggregationFunction.AverageOfChildren
|| m.AggregateFunction == AggregationFunction.ByAccount
|| m.AggregateFunction == AggregationFunction.FirstChild
|| m.AggregateFunction == AggregationFunction.FirstNonEmpty
|| m.AggregateFunction == AggregationFunction.LastChild
|| m.AggregateFunction == AggregationFunction.LastNonEmpty
|| m.AggregateFunction == AggregationFunction.Count
|| m.AggregateFunction == AggregationFunction.DistinctCount
|| m.AggregateFunction == AggregationFunction.Min
|| m.AggregateFunction == AggregationFunction.Max
|| m.AggregateFunction == AggregationFunction.None)
{
double dsvColMaxValue = 0;
bool dsvColAllowsDecimals = false;
if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (col.DataType == typeof(DateTime))
continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates
MeasureDataTypeOption dsvOption = GetMeasureDataTypeOptionForType(col.DataType);
if (dsvOption != null)
{
dsvColMaxValue = dsvOption.max;
dsvColAllowsDecimals = dsvOption.allowsDecimals;
}
}
double? total = (!Convert.IsDBNull(row[i * 5]) ? Convert.ToDouble(row[i * 5]) : (double?)null);
double? min = (!Convert.IsDBNull(row[i * 5 + 1]) ? Convert.ToDouble(row[i * 5 + 1]) : (double?)null);
double? max = (!Convert.IsDBNull(row[i * 5 + 2]) ? Convert.ToDouble(row[i * 5 + 2]) : (double?)null);
bool hasDecimals = (!Convert.IsDBNull(row[i * 5 + 3]) ? Convert.ToBoolean(row[i * 5 + 3]) : false);
bool hasMoreThan4Decimals = (!Convert.IsDBNull(row[i * 5 + 4]) ? Convert.ToBoolean(row[i * 5 + 4]) : false);
MeasureDataTypeOption oldDataTypeOption = GetMeasureDataTypeOptionForMeasure(m);
double recommendedMaxValue = double.MaxValue;
List<MeasureDataTypeOption> possible = new List<MeasureDataTypeOption>();
foreach (MeasureDataTypeOption option in dataTypeOptions)
{
if (
(total == null || (option.max >= total && option.min <= total))
&& (max == null || option.max >= max)
&& (min == null || option.min <= min)
&& (!hasDecimals || option.allowsDecimals)
&& (!hasMoreThan4Decimals || option.allowsMoreThan4Decimals)
)
{
possible.Add(option);
if (
(total == null || (total * FreeSpaceFactor < option.max && total * FreeSpaceFactor > option.min))
&& option.max < recommendedMaxValue
&& option.max >= dsvColMaxValue
&& (dsvColAllowsDecimals == option.allowsDecimals)
&& (option.oleDbType != OleDbType.Single) //never recommend Single
)
{
recommendedMaxValue = option.max;
}
}
}
foreach (MeasureDataTypeOption option in dataTypeOptions)
{
if (option.max == recommendedMaxValue)
{
Type dsvDataType = null; //don't bother getting the DSV datatype for count or DistinctCount measures
if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount)
{
ColumnBinding cb = GetColumnBindingForDataItem(m.Source);
DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
dsvDataType = col.DataType;
}
MeasureHealthCheckResult result = new MeasureHealthCheckResult(m, FormatDouble(total), FormatDouble(min), FormatDouble(max), hasDecimals, hasMoreThan4Decimals, possible.ToArray(), option, oldDataTypeOption, dsvDataType);
measureResults.Add(result);
break;
}
}
i++;
}
else
{
throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported");
}
}
BIDSHelper.SSAS.MeasureGroupHealthCheckForm form = new BIDSHelper.SSAS.MeasureGroupHealthCheckForm();
form.measureDataTypeOptionBindingSource.DataSource = dataTypeOptions;
form.measureHealthCheckResultBindingSource.DataSource = measureResults;
form.Text = "Measure Group Health Check: " + mg.Name;
DialogResult dialogResult = form.ShowDialog();
if (dialogResult == DialogResult.OK)
{
foreach (MeasureHealthCheckResult r in measureResults)
{
if (r.CurrentDataType != r.DataType)
{
//save change
if (r.Measure.AggregateFunction == AggregationFunction.Count
|| r.Measure.AggregateFunction == AggregationFunction.DistinctCount)
{
r.Measure.DataType = r.DataType.dataType;
}
else
{
r.Measure.Source.DataType = r.DataType.oleDbType;
r.Measure.DataType = MeasureDataType.Inherited;
}
//mark cube object as dirty
IComponentChangeService changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService));
changesvc.OnComponentChanging(mg.Parent, null);
changesvc.OnComponentChanged(mg.Parent, null, null, null); //marks the cube designer as dirty
}
}
}
}
void backgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
try
{
MeasureGroup mg = currentStat.IntermediateMeasureGroup;
DataSource oDataSource = mg.Parent.DataSource;
DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, MeasureGroupHealthCheckPlugin.GetTableIdForDataItem(mg.Measures[0].Source));
DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID];
//check whether this fact table uses an alternate datasource
if (dtTable.ExtendedProperties.ContainsKey("DataSourceID"))
{
oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()];
}
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false);
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = 0;
}
else
{
throw new Exception("Couldn't open connection from data source " + oDataSource.Name);
}
command = openedDataSourceConnection.CreateCommand();
command.CommandText = currentStat.SQL;
if (backgroundWorker.CancellationPending)
{
return;
}
System.Data.Common.DbDataReader reader = null;
try
{
try
{
reader = command.ExecuteReader();
}
catch (Exception innerEx)
{
if (backgroundWorker.CancellationPending)
{
return;
}
else
{
throw innerEx;
}
}
if (!backgroundWorker.CancellationPending && reader.Read())
{
lock (command)
{
if (Convert.IsDBNull(reader["OriginalRecordCount"]))
{
currentStat.OriginalRecordCount = null;
}
else
{
currentStat.OriginalRecordCount = Convert.ToInt64(reader["OriginalRecordCount"]);
}
if (Convert.IsDBNull(reader["CompressedRecordCount"]))
{
currentStat.CompressedRecordCount = null;
}
else
{
currentStat.CompressedRecordCount = Convert.ToInt64(reader["CompressedRecordCount"]);
}
if (Convert.IsDBNull(reader["MatrixDimensionRecordCount"]))
{
currentStat.MatrixDimensionRecordCount = null;
}
else
{
currentStat.MatrixDimensionRecordCount = Convert.ToInt64(reader["MatrixDimensionRecordCount"]);
}
currentStat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete;
}
foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list)
{
if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL)
{
stat.OriginalRecordCount = currentStat.OriginalRecordCount;
stat.CompressedRecordCount = currentStat.CompressedRecordCount;
stat.MatrixDimensionRecordCount = currentStat.MatrixDimensionRecordCount;
stat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete;
}
}
}
}
finally
{
try
{
if ((reader != null) && !reader.IsClosed)
{
reader.Close();
}
}
catch { }
command = null;
}
}
finally
{
try
{
openedDataSourceConnection.Close();
}
catch { }
}
}
catch (Exception ex)
{
currentStat.Error = ex.Message + "\r\n" + ex.StackTrace;
foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list)
{
if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL)
{
stat.Error = currentStat.Error;
}
}
}
}
/// <summary>
/// returns information about the binding for table/dsvTable or ColumnBinding, we return the table ID
/// for query binding, the returned value is -1, but the query out parameter is set to the query definition
/// </summary>
/// <param name="connectionID"></param>
/// <param name="dsvTableNameToIdMap"></param>
/// <param name="binding"></param>
/// <param name="?"></param>
/// <returns></returns>
private int GetSourceIDForBinding(int connectionID, Dictionary <string, int> dsvTableNameToIdMap, Binding binding, out string query)
{
int sourceID = -1;
query = null;
if (binding != null)
{
if (binding is ColumnBinding)
{
ColumnBinding colBinding = (ColumnBinding)binding;
// get the id of the dsv table colBinding.TableID
if (dsvTableNameToIdMap.ContainsKey(colBinding.TableID))
{
sourceID = dsvTableNameToIdMap[colBinding.TableID];
}
}
else
{
if (binding is DsvTableBinding)
{
DsvTableBinding dsvTableBinding = (DsvTableBinding)binding;
if (dsvTableNameToIdMap.ContainsKey(dsvTableBinding.TableID))
{
sourceID = dsvTableNameToIdMap[dsvTableBinding.TableID];
}
}
else
{
if (binding is TableBinding)
{
TableBinding tableBinding = (TableBinding)binding;
string tableName = GetFullyQualifiedTableName(tableBinding.DbSchemaName, tableBinding.DbTableName);
if (threePartNames)
{
tableName = String.Format("[{0}].{1}", repository.RetrieveDatabaseNameFromConnectionID(connectionID), tableName);
}
// get the table name from the repository itself
sourceID = repository.GetTable(connectionID, tableName);
if (sourceID == -1)
{
sourceID = repository.AddObject(tableName, string.Empty, RelationalEnumerator.ObjectTypes.Table, connectionID);
}
}
else
{
if (binding is MeasureGroupDimensionBinding)
{
// the caller will handle this since we need the list of cb dimensions
}
else
{
if (binding is QueryBinding)
{
QueryBinding queryBinding = (QueryBinding)binding;
query = queryBinding.QueryDefinition;
}
}
}
}
}
}
return(sourceID);
}
