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());
}
/// <summary>
/// Determines if the command should be displayed or not.
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
//public override bool DisplayCommand(UIHierarchyItem item)
//{
// try
// {
// UIHierarchy solExplorer = this.ApplicationObject.ToolWindows.SolutionExplorer;
// if (((System.Array)solExplorer.SelectedItems).Length != 1)
// return false;
// UIHierarchyItem hierItem = ((UIHierarchyItem)((System.Array)solExplorer.SelectedItems).GetValue(0));
// return (((ProjectItem)hierItem.Object).Object is Dimension);
// }
// catch
// {
// return false;
// }
//}
public override void Exec()
{
try
{
UIHierarchy solExplorer = this.ApplicationObject.ToolWindows.SolutionExplorer;
UIHierarchyItem hierItem = (UIHierarchyItem)((System.Array)solExplorer.SelectedItems).GetValue(0);
ProjectItem projItem = (ProjectItem)hierItem.Object;
Dimension d = (Dimension)projItem.Object;
if (d.DataSource == null)
{
if (d.Source is TimeBinding)
{
MessageBox.Show("Dimension Health Check is not supported on a Server Time dimension.");
}
else
{
MessageBox.Show("The data source for this dimension is not set. Dimension Health Check cannot be run.");
}
return;
}
else if (d.Source is DimensionBinding)
{
MessageBox.Show("Dimension Health Check is not supported on a linked dimension.");
return;
}
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Checking Dimension Health...", 0, d.Attributes.Count * 2);
DimensionError[] errors = Check(d);
if (errors == null)
{
return;
}
this.oLastDimension = d;
this.changesvc = (IComponentChangeService)d.Site.GetService(typeof(IComponentChangeService));
int iErrorCnt = 0;
string sCaption = d.Name + ": Dimension Health Check";
#if YUKON || KATMAI
EnvDTE80.Windows2 toolWins;
object objTemp = null;
toolWins = (Windows2)ApplicationObject.Windows;
if (toolWin == null)
{
toolWin = toolWins.CreateToolWindow2(AddInInstance, typeof(WebBrowser).Assembly.Location, typeof(WebBrowser).FullName, sCaption, "{" + typeof(WebBrowser).GUID.ToString() + "}", ref objTemp);
}
else
{
objTemp = toolWin.Object;
toolWin.Caption = sCaption;
}
WebBrowser browser = (WebBrowser)objTemp;
#else
//appear to be having some problems with .NET controls inside tool windows, even though this issue says fixed: http://connect.microsoft.com/VisualStudio/feedback/details/512181/vsip-vs-2010-beta2-width-of-add-in-toolwindow-not-changed-with-activex-hosted-control#tabs
//so just create this inside a regular WinForm
WebBrowser browser = new WebBrowser();
#endif
browser.AllowNavigation = true;
if (browser.Document != null) //idea from http://geekswithblogs.net/paulwhitblog/archive/2005/12/12/62961.aspx
{
browser.Document.OpenNew(true);
}
else
{
browser.Navigate("about:blank");
}
Application.DoEvents();
browser.Document.Write("<font style='font-family:Arial;font-size:10pt'>");
browser.Document.Write("<h3>" + d.Name + ": Dimension Health Check</h3>");
browser.Document.Write("<i>Checks whether attribute relationships hold true according to the data.<br>Also checks definition of attribute keys to determine if they are unique.<br>Also checks whether any obvious attribute relationships are missing.</i><br><br>");
if (errors.Length > 0)
{
browser.Document.Write("<b>Problems</b><br>");
foreach (DimensionError e in errors)
{
iErrorCnt++;
browser.Document.Write("<li>");
browser.Document.Write(e.ErrorDescription);
DimensionDataError de = e as DimensionDataError;
DimensionRelationshipWarning rw = e as DimensionRelationshipWarning;
if (de != null && de.ErrorTable != null)
{
browser.Document.Write(" <a href=\"javascript:void(null)\" id=expander" + iErrorCnt + " ErrorCnt=" + iErrorCnt + " style='color:blue'>Show/hide problem rows</a><br>\r\n");
browser.Document.Write("<table id=error" + iErrorCnt + " cellspacing=0 style='display:none;font-family:Arial;font-size:10pt'>");
browser.Document.Write("<tr><td></td>");
for (int i = 0; i < de.ErrorTable.Columns.Count; i++)
{
browser.Document.Write("<td nowrap><b>");
browser.Document.Write(System.Web.HttpUtility.HtmlEncode(de.ErrorTable.Columns[i].ColumnName));
browser.Document.Write("</b></td><td> </td>");
}
browser.Document.Write("</tr>\r\n");
foreach (DataRow dr in de.ErrorTable.Rows)
{
browser.Document.Write("<tr><td> </td>");
for (int i = 0; i < de.ErrorTable.Columns.Count; i++)
{
browser.Document.Write("<td nowrap>");
if (!Convert.IsDBNull(dr[i]))
{
browser.Document.Write(System.Web.HttpUtility.HtmlEncode(dr[i].ToString()));
}
else
{
browser.Document.Write("<font color=lightgrey>(null)</font>");
}
browser.Document.Write("</td><td> </td>");
}
browser.Document.Write("</tr>");
}
browser.Document.Write("</table>");
}
else if (rw != null)
{
browser.Document.Write(" <a href=\"javascript:void(null)\" id=expander" + iErrorCnt + " Attribute=\"" + rw.Attribute.ID + "\" RelatedAttribute=\"" + rw.RelatedAttribute.ID + "\" style='color:blue'>Change attribute relationship</a>\r\n");
}
}
}
else
{
browser.Document.Write("<b>No problems found</b>");
}
browser.Document.Write("</font>");
browser.IsWebBrowserContextMenuEnabled = false;
browser.AllowWebBrowserDrop = false;
Application.DoEvents();
for (int i = 1; i <= iErrorCnt; i++)
{
//in some of the newer versions of Internet Explorer, javascript is not enabled
//so do the dynamic stuff with C# events and code
try
{
browser.Document.GetElementById("expander" + i).Click += new HtmlElementEventHandler(Expander_Click);
}
catch { }
}
#if YUKON || KATMAI
//setting IsFloating and Linkable to false makes this window tabbed
toolWin.IsFloating = false;
toolWin.Linkable = false;
toolWin.Visible = true;
#else
Form descForm = new Form();
descForm.Icon = BIDSHelper.Resources.Common.BIDSHelper;
descForm.Text = "BIDS Helper - " + sCaption;
descForm.MaximizeBox = true;
descForm.MinimizeBox = false;
descForm.Width = 600;
descForm.Height = 500;
descForm.SizeGripStyle = SizeGripStyle.Show;
descForm.MinimumSize = new System.Drawing.Size(descForm.Width / 2, descForm.Height / 2);
browser.Top = 10;
browser.Left = 10;
browser.Width = descForm.Width - 30;
browser.Anchor = AnchorStyles.Left | AnchorStyles.Right | AnchorStyles.Top | AnchorStyles.Bottom;
browser.Dock = DockStyle.Fill;
browser.Height = descForm.Height - 60;
descForm.Controls.Add(browser);
descForm.Show();
#endif
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Checking Dimension Health...", 2, 2);
}
}
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();
}
