public ImageTextCell (int imageColumn, int textColumn)
: this()
{
ImageBinding = new ColumnBinding (imageColumn);
TextBinding = new ColumnBinding (textColumn);
}
private static string DBServerName = ""; //will say Oracle or Microsoft SQL Server
private DimensionError[] Check(Dimension d)
{
if (d.MiningModelID != null)
{
return new DimensionError[] { }
}
;
List <DimensionError> problems = new List <DimensionError>();
//TODO: need to add in code to allow you to cancel such that it will stop an executing query
DataSource dataSource = d.DataSource;
try
{
//if the key attribute points to a table with a different data source than the default data source for the DSV, use it
ColumnBinding col = GetColumnBindingForDataItem(d.KeyAttribute.KeyColumns[0]);
DataTable table = d.DataSourceView.Schema.Tables[col.TableID];
if (table.ExtendedProperties.ContainsKey("DataSourceID"))
{
dataSource = d.Parent.DataSources[table.ExtendedProperties["DataSourceID"].ToString()];
}
}
catch { }
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, dataSource.ID, dataSource.Name, dataSource.ManagedProvider, dataSource.ConnectionString, dataSource.Site, false);
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = (int)dataSource.Timeout.TotalSeconds;
}
}
catch { }
if (openedDataSourceConnection == null)
{
DimensionError err = new DimensionError();
err.ErrorDescription = "Unable to connect to data source [" + dataSource.Name + "] to test attribute relationships and key uniqueness.";
problems.Add(err);
}
else
{
sq = openedDataSourceConnection.Cartridge.IdentStartQuote;
fq = openedDataSourceConnection.Cartridge.IdentEndQuote;
DBServerName = openedDataSourceConnection.DBServerName;
cartridge = openedDataSourceConnection.Cartridge;
int iProgressCount = 0;
String sql = "";
bool bGotSQL = false;
foreach (DimensionAttribute da in d.Attributes)
{
try
{
bGotSQL = false;
if (da.Usage != AttributeUsage.Parent)
{
sql = GetQueryToValidateKeyUniqueness(da);
}
else
{
sql = null;
}
if (sql != null)
{
bGotSQL = true;
DataSet ds = new DataSet();
openedDataSourceConnection.Fill(ds, sql);
if (ds.Tables[0].Rows.Count > 0)
{
string problem = "Attribute [" + da.Name + "] has key values with multiple names.";
DimensionDataError err = new DimensionDataError();
err.ErrorDescription = problem;
err.ErrorTable = ds.Tables[0];
problems.Add(err);
}
}
ApplicationObject.StatusBar.Progress(true, "Checking Attribute Key Uniqueness...", ++iProgressCount, d.Attributes.Count * 2);
}
catch (Exception ex)
{
string problem = "Attempt to validate key and name relationship for attribute [" + da.Name + "] failed:" + ex.Message + ex.StackTrace + (bGotSQL ? "\r\nSQL query was: " + sql : "");
DimensionError err = new DimensionError();
err.ErrorDescription = problem;
problems.Add(err);
}
}
foreach (DimensionAttribute da in d.Attributes)
{
foreach (AttributeRelationship r in da.AttributeRelationships)
{
try
{
bGotSQL = false;
if (da.Usage != AttributeUsage.Parent)
{
sql = GetQueryToValidateRelationship(r);
}
else
{
sql = null;
}
if (sql != null)
{
bGotSQL = true;
DataSet ds = new DataSet();
openedDataSourceConnection.Fill(ds, sql);
if (ds.Tables[0].Rows.Count > 0)
{
string problem = "Attribute relationship [" + da.Name + "] -> [" + r.Attribute.Name + "] is not valid because it results in a many-to-many relationship.";
DimensionDataError err = new DimensionDataError();
err.ErrorDescription = problem;
err.ErrorTable = ds.Tables[0];
problems.Add(err);
}
}
}
catch (Exception ex)
{
string problem = "Attempt to validate attribute relationship [" + da.Name + "] -> [" + r.Attribute.Name + "] failed:" + ex.Message + ex.StackTrace + (bGotSQL ? "\r\nSQL query was: " + sql : "");
DimensionError err = new DimensionError();
err.ErrorDescription = problem;
problems.Add(err);
}
}
ApplicationObject.StatusBar.Progress(true, "Checking Attribute Relationships...", ++iProgressCount, d.Attributes.Count * 2);
}
cartridge = null;
openedDataSourceConnection.Close();
}
//check obvious attribute relationship mistakes
foreach (DimensionAttribute da in d.Attributes)
{
foreach (DimensionAttribute child in d.Attributes)
{
try
{
if (child.ID != da.ID && da.AttributeHierarchyEnabled && ContainsSubsetOfKeys(da, child) && !IsParentOf(child, da))
{
if (ContainsSubsetOfKeys(child, da) && (IsParentOf(da, child) || (child.Name.CompareTo(da.Name) < 0 && child.AttributeHierarchyEnabled)))
{
//if the keys for both are the same, then skip this one if the opposite attribute relationship is defined... otherwise, only return one direction based on alphabetic order
continue;
}
DimensionRelationshipWarning warn = new DimensionRelationshipWarning();
if (d.KeyAttribute.AttributeRelationships.Contains(child.ID))
{
warn.ErrorDescription = "Attribute [" + child.Name + "] has a subset of the keys of attribute [" + da.Name + "]. Therefore, those attributes can be related which is preferable to leaving [" + child.Name + "] related directly to the key.";
}
else
{
warn.ErrorDescription = "Attribute [" + child.Name + "] has a subset of the keys of attribute [" + da.Name + "]. Therefore, those attributes can be related. However, this may not be necessary since [" + child.Name + "] is already part of a set of attribute relationships.";
}
warn.Attribute = da;
warn.RelatedAttribute = child;
problems.Add(warn);
}
}
catch (Exception ex)
{
string problem = "Attempt to check for obvious attribute relationship oversights on [" + da.Name + "] and [" + child.Name + "] failed:" + ex.Message + ex.StackTrace;
DimensionError err = new DimensionError();
err.ErrorDescription = problem;
problems.Add(err);
}
}
}
return(problems.ToArray());
}
private static string GetQueryToValidateUniqueness(DataSourceView dsv, DataItem[] child, DataItem[] parent)
{
//need to do GetColumnBindingForDataItem
StringBuilder select = new StringBuilder();
StringBuilder outerSelect = new StringBuilder();
StringBuilder groupBy = new StringBuilder();
StringBuilder orderBy = new StringBuilder();
StringBuilder join = new StringBuilder();
StringBuilder topLevelColumns = new StringBuilder();
Dictionary <DataTable, JoinedTable> tables = new Dictionary <DataTable, JoinedTable>();
List <string> previousColumns = new List <string>();
foreach (DataItem di in child)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = GetNextUniqueColumnIdentifier();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
groupBy.Append((select.Length == 0 ? "group by " : ","));
outerSelect.Append((select.Length == 0 ? "select " : ","));
select.Append((select.Length == 0 ? "select distinct " : ","));
string sIsNull = "";
string sForceDataTypeOnJoin = "";
if (dc.DataType == typeof(string))
{
if (DBServerName == "Oracle")
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "' '"; //oracle treats empty string as null
}
}
else
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "''";
}
}
}
else if (dc.DataType == typeof(DateTime))
{
if (DBServerName == "Oracle")
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "to_date('1/1/1899 01:02:03','MM/DD/YYYY HH:MI:SS')"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "to_date('12/30/1899','MM/DD/YYYY')"; //think this is what SSAS converts null dates to
}
}
else if (DBServerName == "Teradata")
{
sForceDataTypeOnJoin = "timestamp";
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "cast('1899/12/30 01:02:03.456789' as timestamp)"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "cast('1899/12/30 00:00:00' as timestamp)"; //think this is what SSAS converts null dates to
}
}
else
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'1/1/1899 01:02:03 AM'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "'12/30/1899'"; //think this is what SSAS converts null dates to
}
}
}
else if (dc.DataType == typeof(Guid)) // Guid
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'" + (new Guid()).ToString() + "'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "'" + Guid.Empty.ToString() + "'";
}
}
else //numeric
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "-987654321.123456789"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "0";
}
}
if (!string.IsNullOrEmpty(sForceDataTypeOnJoin))
{
join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(cast(y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sForceDataTypeOnJoin).Append("),").Append(sIsNull).Append(") = coalesce(cast(z.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sForceDataTypeOnJoin).Append("),").Append(sIsNull).AppendLine(")");
}
else
{
join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(y.").Append(sq).Append(colAlias).Append(fq).Append(",").Append(sIsNull).Append(") = coalesce(z.").Append(sq).Append(colAlias).Append(fq).Append(",").Append(sIsNull).AppendLine(")");
}
groupBy.Append(sq).Append(colAlias).AppendLine(fq);
outerSelect.Append(sq).Append(colAlias).AppendLine(fq);
if (topLevelColumns.Length > 0)
{
topLevelColumns.Append(",");
}
topLevelColumns.Append("y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sq).Append(dc.ColumnName).AppendLine(fq);
orderBy.Append((orderBy.Length == 0 ? "order by " : ","));
orderBy.Append("y.").Append(sq).Append(colAlias).AppendLine(fq);
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append(sq).Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append(fq).Append(".").Append(sq).Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).Append(fq).Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
else
{
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
select.Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
foreach (DataItem di in parent)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = GetNextUniqueColumnIdentifier();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append(",");
//use the __PARENT__ prefix in case there's a column with the same name but different table
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append(sq).Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append(fq).Append(".").Append(sq).Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).Append(fq).Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
else
{
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
select.Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
if (topLevelColumns.Length > 0)
{
topLevelColumns.Append(",");
}
topLevelColumns.Append("y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sq).Append(dc.ColumnName).AppendLine(fq);
orderBy.Append((orderBy.Length == 0 ? "order by " : ","));
orderBy.Append("y.").Append(sq).Append(colAlias).AppendLine(fq);
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
int iLastTableCount = 0;
while (iLastTableCount != tables.Values.Count)
{
iLastTableCount = tables.Values.Count;
JoinedTable[] arrJt = new JoinedTable[iLastTableCount];
tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing
foreach (JoinedTable jt in arrJt)
{
TraverseParentRelationshipsAndAddNewTables(tables, jt.table);
}
}
//check that all but one table have a valid join path to them
DataTable baseTable = null;
foreach (JoinedTable t in tables.Values)
{
if (!t.Joined)
{
if (baseTable == null)
{
baseTable = t.table;
}
else
{
throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins.");
}
}
}
if (baseTable == null)
{
//there are joins to and from the base table, so guess at the base table based on the child parameter to this function
ColumnBinding col = GetColumnBindingForDataItem(child[0]);
baseTable = dsv.Schema.Tables[col.TableID];
}
//by now, all tables needed for joins will be in the dictionary
select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable));
select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable));
outerSelect.AppendLine("\r\nfrom (").Append(select).AppendLine(") x").Append(groupBy).AppendLine("\r\nhaving count(*)>1");
string invalidValuesInner = outerSelect.ToString();
outerSelect = new StringBuilder();
outerSelect.Append("select ").AppendLine(topLevelColumns.ToString()).AppendLine(" from (").Append(select).AppendLine(") y");
outerSelect.AppendLine("join (").AppendLine(invalidValuesInner).AppendLine(") z").AppendLine(join.ToString()).AppendLine(orderBy.ToString());
return(outerSelect.ToString());
}
/// <summary>
/// Initializes a new instance of the <see cref="Eto.Forms.ImageTextCell"/> class when binding to an indexed-based data item.
/// </summary>
/// <param name="imageColumn">Index of the image column in the data item.</param>
/// <param name="textColumn">Index of the text column in the data item.</param>
public ImageTextCell(int imageColumn, int textColumn)
{
ImageBinding = new ColumnBinding<Image>(imageColumn);
TextBinding = new ColumnBinding<string>(textColumn);
}
/// <summary>
/// Initializes a new instance of the <see cref="DsBindComboBoxCell"/> class with the column index to bind.
/// </summary>
/// <param name="column">Column index to bind to.</param>
public DsBindComboBoxCell(int column)
: this()
{
Binding = new ColumnBinding <object>(column);
}
public UsedColumn(DataItem di, ColumnBinding column, DataSourceView dsv, string usageType, string usageObjectName)
{
m_dataItem = di;
m_column = column;
m_dsv = dsv;
m_usageType = usageType;
m_usageObjectName = usageObjectName;
}
public ComboBoxCell (int column)
: this()
{
Binding = new ColumnBinding (column);
}
/// <summary>
/// Add measure into a measure group
/// </summary>
/// <param name="measureGroup"></param>
/// <param name="tableID"></param>
/// <param name="columnID"></param>
/// <param name="measureName"></param>
/// <param name="measureID"></param>
/// <param name="displayFolder"></param>
/// <param name="formatStr"></param>
/// <param name="aggregationFunction"></param>
/// <param name="visible"></param>
/// <param name="sourceColDataType"></param>
/// <param name="measureDataType"></param>
internal static void ADD_MEASURE_TO_MEASURE_GROUP(
DB_SQLHELPER_BASE sqlHelper
, MeasureGroup measureGroup
, String tableID
, String columnID
, String measureName
, String measureID
, String displayFolder
, String formatStr
, String aggregationFunction
, bool visible = true
, String sourceColDataType = "double"
, String measureDataType = "double")
{
Microsoft.AnalysisServices.DataItem source = new Microsoft.AnalysisServices.DataItem();
source.NullProcessing = NullProcessing.Preserve;
Measure measure = new Measure(measureName, measureID);
String aggType = aggregationFunction.ToLower();
measure.DataType = AS_API_HELPER.GET_SSAS_MEASURE_DATA_TYPE_BY_NAME(measureDataType);
if (aggType == "count*")
{
RowBinding rowBind = new RowBinding();
rowBind.TableID = tableID;
measure.AggregateFunction = AggregationFunction.Count;
source.Source = rowBind;
measure.Source = source;
measure.DataType = MeasureDataType.Integer;
//source.DataType = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(sourceColDataType);
}
else
{
ColumnBinding colBind = new ColumnBinding();
colBind.TableID = tableID;
colBind.ColumnID = columnID;
source.DataType = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(sourceColDataType);
source.Source = colBind;
measure.AggregateFunction = AS_API_HELPER.GET_SSAS_AGGREGATION_FUNCTION_BY_NAME(aggType.ToLower());
if (aggType.ToLower() == "distinctcount")
{
source.NullProcessing = NullProcessing.Automatic;
source.DataType = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME("integer");
measure.DataType = MeasureDataType.Integer;
}
measure.Source = source;
}
String dataType = sourceColDataType.ToLower();
measure.DisplayFolder = displayFolder;
//measure.FormatString = formatStr
measure.Visible = visible;
Measure measureEx = measureGroup.Measures.Find(measureID);
if (measureEx != null)
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("measure {0} exists", measureName),
MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Warning);
measureEx.Name = measure.Name;
measureEx.AggregateFunction = measure.AggregateFunction;
measureEx.DataType = AS_API_HELPER.GET_SSAS_MEASURE_DATA_TYPE_BY_NAME(measureDataType);
measureEx.DisplayFolder = measure.DisplayFolder;
measureEx.Visible = measure.Visible;
measureEx.FormatString = measure.FormatString;
measureEx.Source = source.Clone();
}
else
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("Added measure {0} into measure group {1}", measureName, measureGroup.Name),
MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Succeed);
measureGroup.Measures.Add(measure);
}
}
public void Build(IDbCommand adoCommand)
{
adoCommand.CommandText = this.Data.CommandText;
Dictionary <string, IDbDataParameter> adoMap = new Dictionary <string, IDbDataParameter>();
foreach (IParameter parameter in this.Data.Parameters.Where(p => !adoMap.ContainsKey(p.Name)))
{
IDbDataParameter adoParameter = ParameterHelper.CreateAdoParameter(adoCommand, parameter);
if (parameter.Field != null)
{
FieldData fieldData = this.Map.Get(parameter.Field);
if (fieldData != null)
{
adoParameter.Value = fieldData.GetValue();
}
}
adoMap.Add(parameter.Name, adoParameter);
adoCommand.Parameters.Add(adoParameter);
}
foreach (var g in this.Data.Bindings.GroupBy(b => b.Field).Select(g => g.ToArray()))
{
ParameterBinding parameterBinding = g.OfType <ParameterBinding>().FirstOrDefault();
ColumnBinding columnBinding = g.OfType <ColumnBinding>().FirstOrDefault();
if (parameterBinding == null && columnBinding == null)
{
throw new CommandException("ICommandBinding must be a ColumnBinding or ParameterBinding instance.");
}
IField field = columnBinding?.Field ?? parameterBinding.Field;
FieldData fieldData = this.Map.Get(field);
if (fieldData == null)
{
fieldData = this.Map.Add(field);
}
if (columnBinding != null)
{
this.headingMap[columnBinding.ColumnName] = fieldData;
}
else
{
IDbDataParameter adoParameter = adoMap.TryGetValue(parameterBinding.ParameterName);
if (adoParameter == null)
{
adoParameter = ParameterHelper.CreateAdoParameter(adoCommand, new Parameter(parameterBinding.ParameterName, field: parameterBinding.Field));
this.SetParameterDirection(adoParameter, ParameterDirection.Output);
adoMap.Add(parameterBinding.ParameterName, adoParameter);
adoCommand.Parameters.Add(adoParameter);
}
fieldData.SetValue(adoParameter);
if (adoParameter.Direction == ParameterDirection.Input)
{
this.SetParameterDirection(adoParameter, ParameterDirection.InputOutput);
}
}
}
}
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 ImageTextCell(int imageColumn, int textColumn)
: this()
{
ImageBinding = new ColumnBinding(imageColumn);
TextBinding = new ColumnBinding(textColumn);
}
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
}
}
}
}
public TextBoxCell(int column)
: this()
{
Binding = new ColumnBinding(column);
}
/// <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);
}
/*
* cavaets:
* doesn't handle varchar(max) DSV columns as the DSV doesn't report the length. The length needs to be set manually on the KeyColumn or NameColumn
*/
private void CheckDataTypeDiscrepancies(DataItem di, ColumnType ColumnType)
{
if (di == null)
{
return;
}
ColumnBinding cb = di.Source as ColumnBinding;
if (cb == null)
{
return;
}
IModelComponent parent = di.Parent;
while (parent != null && !(parent is DimensionAttribute))
{
parent = parent.Parent;
}
DimensionAttribute da = (DimensionAttribute)parent;
if (!da.Parent.DataSourceView.Schema.Tables.Contains(cb.TableID))
{
return;
}
if (!da.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns.Contains(cb.ColumnID))
{
return;
}
DataColumn col = da.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (ColumnType == ColumnType.NameColumn)
{
if (col.MaxLength <= 0)
{
return;
}
if (col.DataType != typeof(string))
{
return;
}
if (di.DataType != OleDbType.WChar)
{
return;
}
if (col.MaxLength != di.DataSize)
{
DataTypeDiscrepancy discrepancy = new DataTypeDiscrepancy();
discrepancy.AnalysisServicesColumnType = ColumnType;
discrepancy.AnalysisServicesColumn = di;
discrepancy.DimensionAttribute = da;
discrepancy.DSVColumn = col;
listDiscrepancies.Add(discrepancy);
}
}
else //KeyColumn
{
bool bDiscrepancy = false;
if (Microsoft.AnalysisServices.OleDbTypeConverter.Convert(di.DataType) != col.DataType && Microsoft.AnalysisServices.OleDbTypeConverter.GetRestrictedOleDbType(col.DataType) != di.DataType)
{
bDiscrepancy = true;
}
if (di.DataSize >= 0 && col.MaxLength >= 0 && di.DataSize != col.MaxLength)
{
bDiscrepancy = true;
}
if (bDiscrepancy)
{
DataTypeDiscrepancy discrepancy = new DataTypeDiscrepancy();
discrepancy.AnalysisServicesColumnType = ColumnType;
discrepancy.AnalysisServicesColumn = di;
discrepancy.DimensionAttribute = da;
discrepancy.DSVColumn = col;
listDiscrepancies.Add(discrepancy);
}
}
}
private static string GetSnowflakeQuery(DataSourceView dsv, ColumnBinding[] child, ColumnBinding[] parent, ColumnBinding prefer)
{
StringBuilder select = new StringBuilder();
Dictionary<DataTable, JoinedTable> tables = new Dictionary<DataTable, JoinedTable>();
List<string> previousColumns = new List<string>();
foreach (ColumnBinding col in child)
{
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append((select.Length == 0 ? "select " : ","));
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]");
}
else
{
select.Append("[").Append(GetColumnName(dc)).Append("] = ");
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
foreach (ColumnBinding col in parent)
{
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append(",");
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]");
}
else
{
select.Append("[").Append(GetColumnName(dc)).Append("] = ");
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
int iLastTableCount = 0;
while (iLastTableCount != tables.Values.Count)
{
iLastTableCount = tables.Values.Count;
JoinedTable[] arrJt = new JoinedTable[iLastTableCount];
tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing
foreach (JoinedTable jt in arrJt)
{
TraverseParentRelationshipsAndAddNewTables(tables, jt.table);
}
}
//check that all but one table have a valid join path to them
DataTable baseTable = null;
foreach (JoinedTable t in tables.Values)
{
if (!t.Joined)
{
if (baseTable == null)
{
baseTable = t.table;
}
else
{
throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins.");
}
}
}
//by now, all tables needed for joins will be in the dictionary
select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable));
select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable, prefer));
return select.ToString();
}
internal static int SyncDescriptions(Dimension d, bool bPromptForProperties, IServiceProvider provider, bool bIsTabular)
{
int iUpdatedDescriptions = 0;
DataSource dataSource = d.DataSource;
ColumnBinding colDimensionKey = null;
#if !(YUKON || KATMAI)
if (d.KeyAttribute.KeyColumns[0].Source is RowNumberBinding)
{
foreach (DimensionAttribute a in d.Attributes)
{
if (a.KeyColumns != null && a.KeyColumns.Count > 0 && a.KeyColumns[0].Source is ColumnBinding)
{
colDimensionKey = GetColumnBindingForDataItem(a.KeyColumns[0]);
break;
}
}
if (colDimensionKey == null)
{
throw new Exception("Couldn't find an attribute with a ColumnBinding, so couldn't find DSV table.");
}
}
else
{
colDimensionKey = GetColumnBindingForDataItem(d.KeyAttribute.KeyColumns[0]);
}
#else
colDimensionKey = GetColumnBindingForDataItem(d.KeyAttribute.KeyColumns[0]);
#endif
DataTable oDimensionKeyTable = d.DataSourceView.Schema.Tables[colDimensionKey.TableID];
//if this is a Tabular model, the Dimension.DataSource may point at the default data source for the data source view
if (oDimensionKeyTable.ExtendedProperties.ContainsKey("DataSourceID"))
{
dataSource = d.Parent.DataSources[oDimensionKeyTable.ExtendedProperties["DataSourceID"].ToString()];
}
IServiceProvider settingService = dataSource.Site;
if (settingService == null)
{
settingService = provider;
}
Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, dataSource.ID, dataSource.Name, dataSource.ManagedProvider, dataSource.ConnectionString, settingService, false);
try
{
if (d.MiningModelID != null)
{
return(iUpdatedDescriptions);
}
try
{
if (openedDataSourceConnection != null)
{
openedDataSourceConnection.QueryTimeOut = (int)dataSource.Timeout.TotalSeconds;
}
}
catch { }
if (openedDataSourceConnection == null)
{
throw new Exception("Unable to connect to data source [" + d.DataSource.Name + "].");
}
else
{
sq = openedDataSourceConnection.Cartridge.IdentStartQuote;
fq = openedDataSourceConnection.Cartridge.IdentEndQuote;
DBServerName = openedDataSourceConnection.DBServerName;
cartridge = openedDataSourceConnection.Cartridge;
if (DBServerName != "Microsoft SQL Server")
{
MessageBox.Show("Data source [" + d.DataSource.Name + "] connects to " + DBServerName + " which may not be supported.");
}
String sql = "select distinct Name from sys.extended_properties order by Name";
if (bPromptForProperties)
{
DataSet dsExtendedProperties = new DataSet();
openedDataSourceConnection.Fill(dsExtendedProperties, sql);
SSAS.SyncDescriptionsForm form = new SSAS.SyncDescriptionsForm();
form.cmbDescriptionProperty.DataSource = dsExtendedProperties.Tables[0];
form.cmbDescriptionProperty.DisplayMember = "Name";
form.cmbDescriptionProperty.ValueMember = "Name";
foreach (DataRow row in dsExtendedProperties.Tables[0].Rows)
{
form.listOtherProperties.Items.Add(row["Name"].ToString());
}
DialogResult result = form.ShowDialog();
if (result != DialogResult.OK)
{
return(iUpdatedDescriptions);
}
DescriptionPropertyName = form.cmbDescriptionProperty.GetItemText(form.cmbDescriptionProperty.SelectedItem);
List <string> listOtherProperties = new List <string>();
for (int i = 0; i < form.listOtherProperties.CheckedItems.Count; i++)
{
listOtherProperties.Add(form.listOtherProperties.GetItemText(form.listOtherProperties.CheckedItems[i]));
}
OtherPropertyNamesToInclude = listOtherProperties.ToArray();
OverwriteExistingDescriptions = form.chkOverwriteExistingDescriptions.Checked;
}
if ((string.IsNullOrEmpty(d.Description) || OverwriteExistingDescriptions) &&
(!oDimensionKeyTable.ExtendedProperties.ContainsKey("QueryDefinition") || bIsTabular) && //Tabular always has a QueryDefinition, even when it's just a table binding
oDimensionKeyTable.ExtendedProperties.ContainsKey("DbTableName") &&
oDimensionKeyTable.ExtendedProperties.ContainsKey("DbSchemaName"))
{
sql = "SELECT PropertyName = p.name" + "\r\n"
+ ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n"
+ "FROM sys.all_objects AS tbl" + "\r\n"
+ "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n"
+ "INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1" + "\r\n"
+ "where sch.name = '" + oDimensionKeyTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n"
+ "and tbl.name = '" + oDimensionKeyTable.ExtendedProperties["DbTableName"].ToString().Replace("'", "''") + "'\r\n"
+ "order by p.name";
string sNewDimensionDescription = "";
DataSet dsTableProperties = new DataSet();
openedDataSourceConnection.Fill(dsTableProperties, sql);
foreach (DataRow row in dsTableProperties.Tables[0].Rows)
{
if (string.Compare((string)row["PropertyName"], DescriptionPropertyName, true) == 0)
{
sNewDimensionDescription = (string)row["PropertyValue"];
}
}
foreach (DataRow row in dsTableProperties.Tables[0].Rows)
{
foreach (string sProp in OtherPropertyNamesToInclude)
{
if (string.Compare((string)row["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)row["PropertyValue"]))
{
if (sNewDimensionDescription.Length > 0)
{
sNewDimensionDescription += "\r\n";
}
sNewDimensionDescription += (string)row["PropertyName"] + ": " + (string)row["PropertyValue"];
}
}
}
if (!string.IsNullOrEmpty(sNewDimensionDescription))
{
d.Description = sNewDimensionDescription;
iUpdatedDescriptions++;
}
}
foreach (DimensionAttribute a in d.Attributes)
{
ColumnBinding col = null;
#if !(YUKON || KATMAI)
if (a.Type == AttributeType.RowNumber)
{
continue;
}
#endif
if (a.NameColumn != null)
{
if (!(a.NameColumn.Source is ColumnBinding))
{
continue;
}
col = GetColumnBindingForDataItem(a.NameColumn);
}
else if (a.KeyColumns.Count == 1)
{
if (!(a.KeyColumns[0].Source is ColumnBinding))
{
continue;
}
col = GetColumnBindingForDataItem(a.KeyColumns[0]);
}
else
{
continue; //skip this attribute since we don't know which column to use
}
DataTable oDsvTable = d.DataSourceView.Schema.Tables[col.TableID];
if ((string.IsNullOrEmpty(a.Description) || OverwriteExistingDescriptions) &&
(!oDsvTable.ExtendedProperties.ContainsKey("QueryDefinition") || bIsTabular) &&
oDsvTable.ExtendedProperties.ContainsKey("DbTableName") &&
oDsvTable.ExtendedProperties.ContainsKey("DbSchemaName"))
{
sql = "SELECT PropertyName = p.name" + "\r\n"
+ ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n"
+ "FROM sys.all_objects AS tbl" + "\r\n"
+ "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n"
+ "INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id" + "\r\n"
+ "INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.object_id AND p.minor_id=clmns.column_id AND p.class=1" + "\r\n"
+ "where sch.name = '" + oDsvTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n"
+ "and tbl.name = '" + oDsvTable.ExtendedProperties["DbTableName"].ToString().Replace("'", "''") + "'\r\n"
+ "and clmns.name = '" + oDsvTable.Columns[col.ColumnID].ColumnName.Replace("'", "''") + "'\r\n"
+ "order by p.name";
string sNewDescription = "";
DataSet dsProperties = new DataSet();
openedDataSourceConnection.Fill(dsProperties, sql);
foreach (DataRow row in dsProperties.Tables[0].Rows)
{
if (string.Compare((string)row["PropertyName"], DescriptionPropertyName, true) == 0)
{
sNewDescription = (string)row["PropertyValue"];
}
}
foreach (DataRow row in dsProperties.Tables[0].Rows)
{
foreach (string sProp in OtherPropertyNamesToInclude)
{
if (string.Compare((string)row["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)row["PropertyValue"]))
{
if (sNewDescription.Length > 0)
{
sNewDescription += "\r\n";
}
sNewDescription += (string)row["PropertyName"] + ": " + (string)row["PropertyValue"];
}
}
}
if (!string.IsNullOrEmpty(sNewDescription))
{
a.Description = sNewDescription;
iUpdatedDescriptions++;
}
}
}
if (d.Site != null) //if not Tabular
{
//mark dimension as dirty
IComponentChangeService changesvc = (IComponentChangeService)d.Site.GetService(typeof(IComponentChangeService));
changesvc.OnComponentChanging(d, null);
changesvc.OnComponentChanged(d, null, null, null);
}
}
}
finally
{
try
{
cartridge = null;
openedDataSourceConnection.Close();
}
catch { }
}
return(iUpdatedDescriptions);
}
private static string TraverseParentRelationshipsAndGetFromClause(Dictionary<DataTable, JoinedTable> tables, DataTable t, ColumnBinding prefer)
{
StringBuilder joins = new StringBuilder();
foreach (DataRelation r in t.ParentRelations)
{
if (prefer != null && r.ChildColumns[0].Table.TableName == prefer.TableID && r.ChildColumns[0].ColumnName != prefer.ColumnID)
{
//this is the right table but is not the preferred column, so skip it
continue;
}
if (r.ParentTable != r.ChildTable && tables.ContainsKey(r.ParentTable) && !tables[r.ParentTable].AddedToQuery)
{
joins.Append("join ").AppendLine(GetFromClauseForTable(r.ParentTable));
for (int i = 0; i < r.ParentColumns.Length; i++)
{
joins.Append((i == 0 ? " on " : " and "));
joins.Append("[").Append(r.ParentTable.ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(r.ParentColumns[i].ColumnName).Append("]");
joins.Append(" = [").Append(r.ChildTable.ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(r.ChildColumns[i].ColumnName).AppendLine("]");
}
joins.Append(TraverseParentRelationshipsAndGetFromClause(tables, r.ParentTable, prefer));
}
}
tables[t].AddedToQuery = true;
return joins.ToString();
}
public ImageViewCell (int column)
: this()
{
Binding = new ColumnBinding (column);
}
public CheckBoxCell(int column)
: this()
{
Binding = new ColumnBinding(column);
}
public TextBoxCell (int column)
: this()
{
Binding = new ColumnBinding (column);
}
public void PersistAndFind()
{
var bindings = Emf.Configuration.Binding;
Assert.IsTrue(bindings.RegisterKeyBinding(typeof(EntityA), new KeyBindingEntityA()));
Assert.IsFalse(bindings.RegisterKeyBinding(typeof(EntityA), new KeyBindingEntityA()));
Assert.IsTrue(bindings.RegisterKeyBinding(typeof(EntityB), new KeyBindingEntityB()));
{
var cb = new ColumnBinding("e");
Assert.IsTrue(bindings.RegisterColumnBinding(typeof(EntityC), cb));
Assert.IsTrue(bindings.RegisterKeyBinding(typeof(EntityC), new KeyBinding <EntityC>(e => e.Name)));
}
var eb1 = new EntityB();
TestBase.TestSerialization(eb1);
var eb2 = new EntityB {
A = new EntityA()
};
TestBase.TestSerialization(eb2);
using (var em = Emf.CreateEntityManager())
{
em.Persist(eb1);
Assert.IsNotNull(eb1.Key);
Assert.IsFalse(string.IsNullOrEmpty(eb1.Key.Row));
Assert.IsFalse(string.IsNullOrEmpty(eb1.Key.ColumnFamily));
Assert.IsTrue(string.IsNullOrEmpty(eb1.Key.ColumnQualifier));
Assert.IsTrue(eb1.Key.Row.StartsWith(typeof(EntityB).Name));
em.Persist(eb2);
Assert.IsNotNull(eb2.Key);
Assert.IsFalse(string.IsNullOrEmpty(eb2.Key.Row));
Assert.IsFalse(string.IsNullOrEmpty(eb2.Key.ColumnFamily));
Assert.IsTrue(string.IsNullOrEmpty(eb2.Key.ColumnQualifier));
Assert.IsTrue(eb2.Key.Row.StartsWith(typeof(EntityB).Name));
Assert.IsTrue(eb2.A.Name.StartsWith(typeof(EntityA).Name));
}
using (var em = Emf.CreateEntityManager())
{
var _eb1 = em.Find <EntityB>(eb1.Key);
Assert.AreEqual(eb1, _eb1);
Assert.IsTrue(_eb1.Key.Row.StartsWith(_eb1.GetType().Name));
var _ea2 = em.Find <EntityA>(eb2.A.Name); // EntityA key binding uses the Name property as row key
Assert.AreEqual(eb2.A, _ea2);
var _eb2 = em.Find <EntityB>(eb2.Key);
Assert.AreEqual(eb2, _eb2);
Assert.IsTrue(_eb2.Key.Row.StartsWith(typeof(EntityB).Name));
Assert.AreEqual(eb2.A.Name, _eb2.A.Name);
Assert.IsTrue(eb2.A.Name.StartsWith(typeof(EntityA).Name));
}
var ec1 = new EntityC {
Name = "11", A = new EntityA()
};
TestBase.TestSerialization(ec1);
var ec2 = new EntityC {
Name = "222", A = new EntityA(), B = new EntityB()
};
TestBase.TestSerialization(ec2);
using (var em = Emf.CreateEntityManager())
{
em.Persist(ec1);
Assert.AreEqual(ec1.Id, ec1.Name);
em.Persist(ec2);
Assert.AreEqual(ec2.Id, ec2.Name);
}
using (var em = Emf.CreateEntityManager())
{
var _ec1 = em.Find <EntityC>("11");
Assert.AreEqual(ec1, _ec1);
var _ec2 = em.Find <EntityC>(ec2);
Assert.AreEqual(ec2, _ec2);
}
bindings.UnregisterBinding(typeof(EntityA));
bindings.UnregisterBinding(typeof(EntityB));
bindings.UnregisterBinding(typeof(EntityC));
}
