private static void RecurseMiningStructureColumnsAndProcessDataItemInLists(DsvColumnResult result, MiningStructureColumnCollection cols)
{
foreach (MiningStructureColumn col in cols)
{
if (col is ScalarMiningStructureColumn)
{
ScalarMiningStructureColumn scalar = (ScalarMiningStructureColumn)col;
foreach (DataItem di in scalar.KeyColumns)
{
ProcessDataItemInLists(result, di, "Mining Structure Column Key");
}
ProcessDataItemInLists(result, scalar.NameColumn, "Mining Structure Column Name");
}
else if (col is TableMiningStructureColumn)
{
TableMiningStructureColumn tblCol = (TableMiningStructureColumn)col;
RecurseMiningStructureColumnsAndProcessDataItemInLists(result, tblCol.Columns);
}
}
}
private static void ProcessDataItemInLists(DsvColumnResult result, DataItem di, string usageType)
{
if (di == null)
{
return;
}
ColumnBinding cb = di.Source as ColumnBinding;
if (cb == null)
{
return;
}
string sColUniqueName = "[" + cb.TableID + "].[" + cb.ColumnID + "]";
if (result.UnusedColumns.ContainsKey(sColUniqueName))
{
result.UnusedColumns.Remove(sColUniqueName);
}
result.UsedColumns.Add(new UsedColumn(di, cb, result.DSV, usageType, di.Parent.FriendlyPath));
}
public static DsvColumnResult IterateDsvColumns(DataSourceView dsv)
{
//DataSourceView m_dsv = dsv;
DsvColumnResult results = new DsvColumnResult(dsv);
//add all DSV columns to a list
//unusedColumns.Clear();
//usedColumns.Clear();
foreach (DataTable t in dsv.Schema.Tables)
{
foreach (DataColumn c in t.Columns)
{
results.UnusedColumns.Add("[" + t.TableName + "].[" + c.ColumnName + "]", new UnusedColumn(c, dsv));
}
}
//remove columns that are used in dimensions
foreach (Dimension dim in dsv.Parent.Dimensions)
{
if (dim.DataSourceView != null && dim.DataSourceView.ID == dsv.ID)
{
foreach (DimensionAttribute attr in dim.Attributes)
{
foreach (DataItem di in attr.KeyColumns)
{
ProcessDataItemInLists(results, di, "Dimension Attribute Key");
}
ProcessDataItemInLists(results, attr.NameColumn, "Dimension Attribute Name");
ProcessDataItemInLists(results, attr.ValueColumn, "Dimension Attribute Value");
ProcessDataItemInLists(results, attr.UnaryOperatorColumn, "Dimension Attribute Unary Operator");
ProcessDataItemInLists(results, attr.SkippedLevelsColumn, "Dimension Attribute Skipped Levels");
ProcessDataItemInLists(results, attr.CustomRollupColumn, "Dimension Attribute Custom Rollup");
ProcessDataItemInLists(results, attr.CustomRollupPropertiesColumn, "Dimension Attribute Custom Rollup Properties");
foreach (AttributeTranslation tran in attr.Translations)
{
ProcessDataItemInLists(results, tran.CaptionColumn, "Dimension Attribute Translation");
}
}
}
}
foreach (Cube cube in dsv.Parent.Cubes)
{
if (cube.DataSourceView != null && cube.DataSourceView.ID == dsv.ID)
{
foreach (MeasureGroup mg in cube.MeasureGroups)
{
//remove columns that are used in measures
foreach (Measure m in mg.Measures)
{
ProcessDataItemInLists(results, m.Source, "Measure");
}
//remove columns that are used in dimension relationships
foreach (MeasureGroupDimension mgdim in mg.Dimensions)
{
if (mgdim is ManyToManyMeasureGroupDimension)
{
//no columns to remove
}
else if (mgdim is DataMiningMeasureGroupDimension)
{
//no columns to remove
}
else if (mgdim is RegularMeasureGroupDimension)
{
//Degenerate dimensions and Reference dimensions
RegularMeasureGroupDimension regMDdim = (RegularMeasureGroupDimension)mgdim;
foreach (MeasureGroupAttribute mga in regMDdim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
foreach (DataItem di3 in mga.KeyColumns)
{
ProcessDataItemInLists(results, di3, "Fact Table Dimension Key");
}
}
}
}
}
}
}
}
//remove mining structure columns
foreach (MiningStructure structure in dsv.Parent.MiningStructures)
{
if (structure.DataSourceView != null && structure.DataSourceView.ID == dsv.ID)
{
RecurseMiningStructureColumnsAndProcessDataItemInLists(results, structure.Columns);
}
}
return(results);
}
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);
}
}
