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