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()); }
internal static int SyncDescriptions(Dimension d, bool bPromptForProperties, IServiceProvider provider, bool bIsTabular) { int iUpdatedDescriptions = 0; DataSource dataSource = d.DataSource; ColumnBinding colDimensionKey = null; #if DENALI || SQL2014 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); BIDSHelper.SSAS.SyncDescriptionsForm form = new BIDSHelper.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 DENALI || SQL2014 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; }
internal static int SyncDescriptions(Dimension d, bool bPromptForProperties, IServiceProvider provider, bool bIsTabular) { int iUpdatedDescriptions = 0; DataSource dataSource = d.DataSource; ColumnBinding colDimensionKey = null; #if DENALI || SQL2014 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); BIDSHelper.SSAS.SyncDescriptionsForm form = new BIDSHelper.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 DENALI || SQL2014 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 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(); }