private static DimensionError[] Check(Dimension d) { if (d.MiningModelID != null) return new DimensionError[] { }; List<DimensionError> problems = new List<DimensionError>(); IDbConnection openedDataSourceConnection = GetOpenedDbConnectionFromDataSource(d.DataSource); 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(); IDbConnectionFill(openedDataSourceConnection, ds, sql); if (ds.Tables[0].Rows.Count > 0) { string problem = "Attribute [" + da.Name + "] has key values with multiple names."; DimensionError err = new DimensionError(); err.ErrorDescription = problem; err.ErrorTable = ds.Tables[0]; problems.Add(err); } } } 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(); IDbConnectionFill(openedDataSourceConnection, 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."; DimensionError err = new DimensionError(); 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); } } } openedDataSourceConnection.Close(); return problems.ToArray(); }
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()); }
public static DataTable ListDimensionErrors(string DimensionName) { DataTable tableReturn = new DataTable(); tableReturn.Columns.Add("Dimension"); tableReturn.Columns.Add("ErrorNumber", typeof(int)); tableReturn.Columns.Add("ErrorDescription"); tableReturn.Columns.Add("NumColumns", typeof(int)); int iNumPriorColumns = tableReturn.Columns.Count; int iMaxNumColumns = 9; for (int i = 1; i <= iMaxNumColumns; i++) { tableReturn.Columns.Add("Column" + i + "Name"); tableReturn.Columns.Add("Column" + i); } if (AdomdServer.Context.ExecuteForPrepare) { return(tableReturn); } Server server = new Server(); server.Connect("*"); Dimension d = server.Databases.GetByName(AdomdServer.Context.CurrentDatabaseName).Dimensions.GetByName(DimensionName); DimensionError[] errors = Check(d); server.Disconnect(); for (int i = 0; i < errors.Length; i++) { DimensionError error = errors[i]; if (error.ErrorTable == null || (error.ErrorTable != null && error.ErrorTable.Rows.Count == 0)) { DataRow row = tableReturn.NewRow(); row["Dimension"] = d.Name; row["ErrorNumber"] = i + 1; row["ErrorDescription"] = error.ErrorDescription; row["NumColumns"] = 0; tableReturn.Rows.Add(row); } else { foreach (DataRow errorRow in error.ErrorTable.Rows) { DataRow row = tableReturn.NewRow(); row["Dimension"] = d.Name; row["ErrorNumber"] = i + 1; row["ErrorDescription"] = error.ErrorDescription; row["NumColumns"] = error.ErrorTable.Columns.Count; if (iMaxNumColumns < error.ErrorTable.Columns.Count) { throw new Exception("Dimension error dataset contained more than " + iMaxNumColumns + " columns which is not allowed with the current code. Change the code of the sproc to allow more, refresh the report dataset, and change the report layout to allow more."); } for (int j = 0; j < error.ErrorTable.Columns.Count; j++) { row[j * 2 + iNumPriorColumns] = error.ErrorTable.Columns[j].ColumnName; if (!Convert.IsDBNull(errorRow[j])) { row[j * 2 + iNumPriorColumns + 1] = errorRow[j].ToString(); } } tableReturn.Rows.Add(row); } } } return(tableReturn); }
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 DimensionError[] Check(Dimension d) { if (d.MiningModelID != null) { return new DimensionError[] { } } ; List <DimensionError> problems = new List <DimensionError>(); IDbConnection openedDataSourceConnection = GetOpenedDbConnectionFromDataSource(d.DataSource); 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(); IDbConnectionFill(openedDataSourceConnection, ds, sql); if (ds.Tables[0].Rows.Count > 0) { string problem = "Attribute [" + da.Name + "] has key values with multiple names."; DimensionError err = new DimensionError(); err.ErrorDescription = problem; err.ErrorTable = ds.Tables[0]; problems.Add(err); } } } 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(); IDbConnectionFill(openedDataSourceConnection, 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."; DimensionError err = new DimensionError(); 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); } } } openedDataSourceConnection.Close(); return(problems.ToArray()); }