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