Пример #1
0
        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;
                DsvColumnResult results     = null;
                string          sFileName   = ((ProjectItem)hierItem.Object).Name.ToLower();

                if (sFileName.EndsWith(".bim"))
                {
                    Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox = TabularHelpers.GetTabularSandboxFromBimFile(this, true);
#if DENALI || SQL2014
                    DataSourceViewCollection dsvs = sandbox.Database.DataSourceViews;
                    foreach (DataSourceView o in dsvs)
                    {
                        results = DsvHelpers.IterateDsvColumns(o);
                    }
#else
                    if (sandbox.IsTabularMetadata)
                    {
                        Microsoft.AnalysisServices.BackEnd.EditMappingUtility util = new Microsoft.AnalysisServices.BackEnd.EditMappingUtility(sandbox);
                        results = new DsvColumnResult(null);

                        foreach (Microsoft.AnalysisServices.BackEnd.DataModelingTable table in sandbox.Tables)
                        {
                            if (table.IsCalculated || table.IsPushedData)
                            {
                                continue;
                            }
                            if (table.IsStructuredDataSource)
                            {
                                MessageBox.Show("BI Developer Extensions does not yet support modern (Power Query) data sources.", "BI Developer Extensions");
                                return;
                            }

                            foreach (Microsoft.AnalysisServices.BackEnd.DataModelingColumn col in table.Columns)
                            {
                                if (col.IsRowNumber || col.IsCalculated)
                                {
                                    continue;
                                }
                                results.UsedColumns.Add(new UnusedColumnsPlugin.UsedColumn(col));
                            }
                        }
                    }
                    else //AMO Tabular
                    {
                        DataSourceViewCollection dsvs = sandbox.AMOServer.Databases[sandbox.DatabaseID].DataSourceViews;
                        foreach (DataSourceView o in dsvs)
                        {
                            results = DsvHelpers.IterateDsvColumns(o);
                        }
                    }
#endif
                }
                else
                {
                    DataSourceView dsv = (DataSourceView)projItem.Object;
                    results = DsvHelpers.IterateDsvColumns(dsv);
                }


                ReportViewerForm frm = new ReportViewerForm();
                frm.ReportBindingSource.DataSource = results.UsedColumns;
                frm.Report = "SSAS.UsedColumns.rdlc";
                Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource();
                reportDataSource1.Name  = "BIDSHelper_UsedColumn";
                reportDataSource1.Value = frm.ReportBindingSource;
                frm.ReportViewerControl.LocalReport.DataSources.Add(reportDataSource1);
                frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = "SSAS.UsedColumns.rdlc";

                frm.Caption     = "Used Columns Report";
                frm.WindowState = FormWindowState.Maximized;
                frm.Show();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Пример #2
0
        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;
                DsvColumnResult results     = null;
                string          sFileName   = ((ProjectItem)hierItem.Object).Name.ToLower();

                if (sFileName.EndsWith(".bim"))
                {
                    Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox = TabularHelpers.GetTabularSandboxFromBimFile(this, true);
#if DENALI || SQL2014
                    DataSourceViewCollection dsvs = sandbox.Database.DataSourceViews;
                    foreach (DataSourceView o in dsvs)
                    {
                        results = DsvHelpers.IterateDsvColumns(o);
                    }
#else
                    if (sandbox.IsTabularMetadata)
                    {
                        Microsoft.AnalysisServices.BackEnd.EditMappingUtility util = new Microsoft.AnalysisServices.BackEnd.EditMappingUtility(sandbox);
                        results = new DsvColumnResult(null);
                        TabularHelpers.EnsureDataSourceCredentials(sandbox);

                        foreach (Microsoft.AnalysisServices.BackEnd.DataModelingTable table in sandbox.Tables)
                        {
                            if (table.IsCalculated || table.IsPushedData)
                            {
                                continue;
                            }
                            if (table.IsStructuredDataSource)
                            {
                                MessageBox.Show("BI Developer Extensions does not yet support modern (Power Query) data sources.", "BI Developer Extensions");
                                return;
                            }

                            //new 1200 models don't appear to have an equivalent of the DSV where the list of columns from the SQL query are cached, so we will have to get the columns from executing (schema only) the SQL query
                            var conn = ((Microsoft.AnalysisServices.BackEnd.RelationalDataStorage)((util.GetDataSourceConnection(util.GetDataSourceID(table.Id), sandbox)))).DataSourceConnection;
                            conn.Open();
                            System.Data.Common.DbCommand cmd = conn.CreateCommand();
                            cmd.CommandText    = sandbox.GetSourceQueryDefinition(table.Id);
                            cmd.CommandTimeout = 0;
                            cmd.Prepare();
                            System.Data.Common.DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
                            DataTable tbl = reader.GetSchemaTable();
                            for (int i = 0; i < tbl.Rows.Count; i++)
                            {
                                string sColumnName = Convert.ToString(tbl.Rows[i]["ColumnName"]);
                                Type   oDataType   = (Type)tbl.Rows[i]["DataType"];
                                bool   bFound      = false;
                                foreach (Microsoft.AnalysisServices.BackEnd.DataModelingColumn col in table.Columns)
                                {
                                    if (col.IsCalculated || col.IsRowNumber)
                                    {
                                        continue;
                                    }
                                    if (sColumnName == col.DBColumnName)
                                    {
                                        bFound = true;
                                        break;
                                    }
                                }
                                if (!bFound)
                                {
                                    DataTable  t = new DataTable(table.Name);
                                    DataColumn c = t.Columns.Add(sColumnName, oDataType);

                                    results.UnusedColumns.Add("[" + t.TableName + "].[" + sColumnName + "]", new UnusedColumn(c, null));
                                }
                            }
                        }
                    }
                    else //AMO Tabular
                    {
                        DataSourceViewCollection dsvs = sandbox.AMOServer.Databases[sandbox.DatabaseID].DataSourceViews;
                        foreach (DataSourceView o in dsvs)
                        {
                            results = DsvHelpers.IterateDsvColumns(o);
                        }
                    }
#endif
                }
                else
                {
                    DataSourceView dsv = (DataSourceView)projItem.Object;
                    results = DsvHelpers.IterateDsvColumns(dsv);
                }

                if (results == null || results.UnusedColumns == null || results.UnusedColumns.Count == 0)
                {
                    MessageBox.Show("There are no unused columns.", "BIDS Helper - Unused Columns Report");
                    return;
                }

                ReportViewerForm frm = new ReportViewerForm();
                frm.ReportBindingSource.DataSource = results.UnusedColumns.Values;
                frm.Report = "SSAS.UnusedColumns.rdlc";
                Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource();
                reportDataSource1.Name  = "BIDSHelper_UnusedColumn";
                reportDataSource1.Value = frm.ReportBindingSource;
                frm.ReportViewerControl.LocalReport.DataSources.Add(reportDataSource1);
                frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = "SSAS.UnusedColumns.rdlc";

                frm.Caption     = "Unused Columns Report";
                frm.WindowState = FormWindowState.Maximized;
                frm.Show();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }