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