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