public override bool ShouldDisplayCommand()
{
var f = GetSelectedFile();
if (f == null)
{
return(false);
}
if (f.Extension.ToLower() != ".bim")
{
return(false);
}
#if DENALI || SQL2014
return(true);
#else
var sb = TabularHelpers.GetTabularSandboxFromBimFile(this, false);
if (sb == null)
{
return(false);
}
if (sb.IsTabularMetadata)
{
return(false);
}
return(true);
#endif
}
private void AlterDatabase(List <SSAS.TabularTranslatedItem> translatedItems)
{
#if DENALI || SQL2014
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code;
#else
Microsoft.AnalysisServices.BackEnd.AMOCode code;
#endif
code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandboxWrapper.GetSandbox().CreateTransaction())
{
SSAS.TabularTranslationsAnnotation annotation = new SSAS.TabularTranslationsAnnotation();
List <SSAS.TabularTranslationObjectAnnotation> annotationsList = new List <SSAS.TabularTranslationObjectAnnotation>();
foreach (SSAS.TabularTranslatedItem item in translatedItems)
{
item.Save(annotationsList);
}
annotation.TabularTranslations = annotationsList.ToArray();
TabularHelpers.SaveXmlAnnotation(cube.Parent, TRANSLATIONS_ANNOTATION, annotation);
TabularHelpers.EnsureDataSourceCredentials(sandboxWrapper.GetSandbox());
cube.Parent.Update(UpdateOptions.ExpandFull);
tran.GetType().InvokeMember("Commit", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, tran, null); //The .Commit() function used to return a list of strings, but in the latest set of code it is a void method which leads to "method not found" errors
//tran.Commit();
}
};
#if DENALI || SQL2014
sandboxWrapper.GetSandbox().ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#else
sandboxWrapper.GetSandbox().ExecuteEngineCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#endif
}
public static SSAS.TabularDisplayFoldersAnnotation GetAnnotation(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox)
{
SSAS.TabularDisplayFoldersAnnotation annotation = new SSAS.TabularDisplayFoldersAnnotation();
if (sandbox.Database.Annotations.Contains(DISPLAY_FOLDER_ANNOTATION))
{
string xml = TabularHelpers.GetAnnotationXml(sandbox.Database, DISPLAY_FOLDER_ANNOTATION);
System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(typeof(SSAS.TabularDisplayFoldersAnnotation));
annotation = (SSAS.TabularDisplayFoldersAnnotation)serializer.Deserialize(new System.IO.StringReader(xml));
}
return(annotation);
}
private SSAS.TabularTranslationsAnnotation GetAnnotation(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox)
{
SSAS.TabularTranslationsAnnotation annotation = new SSAS.TabularTranslationsAnnotation();
#if DENALI || SQL2014
var db = sandbox.Database;
#else
var db = ((Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandbox.Impl).Database;
#endif
if (db.Annotations.Contains(TRANSLATIONS_ANNOTATION))
{
string xml = TabularHelpers.GetAnnotationXml(db, TRANSLATIONS_ANNOTATION);
System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(typeof(SSAS.TabularTranslationsAnnotation));
annotation = (SSAS.TabularTranslationsAnnotation)serializer.Deserialize(new System.IO.StringReader(xml));
}
return(annotation);
}
public override bool ShouldDisplayCommand()
{
var selectedFile = GetSelectedFile();
if (selectedFile != null && selectedFile.Extension == ".bim")
{
#if !DENALI && !SQL2014
var sb = TabularHelpers.GetTabularSandboxFromBimFile(this, false);
if (sb == null)
{
return(false);
}
return(!sb.IsTabularMetadata);
#else
return(true);
#endif
}
return(false);
}
public override ViewModelActionResult Do(IViewModelActionInstance actionInstance)
{
try
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox = TabularHelpers.GetTabularSandboxFromActiveWindow(_plugin.package);
if (sandbox == null)
{
throw new Exception("Can't get Sandbox!");
}
IServiceProvider provider = TabularHelpers.GetTabularServiceProviderFromActiveWindow(_plugin.package);
foreach (IViewModelNode node in actionInstance.Targets.OfType <IViewModelNode>())
{
_plugin.ExecuteSyncDescriptions(sandbox, provider, node.Text);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
return(new ViewModelActionResult(null, (IViewModelObject)null));
}
private void HookBimWindow(ProjectItem pi)
{
try
{
if (pi == null || pi.Name == null)
{
return;
}
string sFileName = pi.Name.ToLower();
if (!sFileName.EndsWith(".bim"))
{
return;
}
SandboxEditor editor = TabularHelpers.GetTabularSandboxEditorFromProjectItem(pi, false);
if (editor == null)
{
return;
}
Microsoft.AnalysisServices.Common.ERDiagram diagram = TabularHelpers.GetTabularERDiagramFromSandboxEditor(editor);
if (diagram != null)
{
SetupContextMenu(diagram);
}
else
{
if (!_hookedSandboxEditors.Contains(editor))
{
editor.DiagramObjectsSelected += new EventHandler <ERDiagramSelectionChangedEventArgs>(editor_DiagramObjectsSelected);
_hookedSandboxEditors.Add(editor);
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
public DataModelingSandboxWrapper(BIDSHelperPluginBase plugin)
{
_sandbox = TabularHelpers.GetTabularSandboxFromBimFile(plugin, true);
}
internal void ExecuteSyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, IServiceProvider provider, string tableName)
{
try
{
#if DENALI || SQL2014
var db = sandbox.Database;
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code;
#else
Database db = null;
if (!sandbox.IsTabularMetadata)
{
db = ((Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandbox.Impl).Database;
}
else
{
db = null;
}
Microsoft.AnalysisServices.BackEnd.AMOCode code;
#endif
code = delegate
{
int iDescriptionsSet;
Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties properties = new Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties();
properties.RecalcBehavior = Microsoft.AnalysisServices.BackEnd.TransactionRecalcBehavior.Default;
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction(properties))
{
if (!TabularHelpers.EnsureDataSourceCredentials(sandbox))
{
MessageBox.Show("Cancelling Sync Descriptions because data source credentials were not entered.", "BIDS Helper Tabular Sync Descriptions - Cancelled!");
tran.RollbackAndContinue();
return;
}
#if !(DENALI || SQL2014)
Microsoft.AnalysisServices.BackEnd.DataModelingTable table = sandbox.Tables[tableName];
if (table.IsStructuredDataSource)
{
MessageBox.Show("BI Developer Extensions does not yet support modern (Power Query) data sources.", "BI Developer Extensions");
return;
}
iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(table, true);
if (iDescriptionsSet > 0)
{
table.UpdateNowOrLater();
}
#else
Dimension d = db.Dimensions.GetByName(tableName);
iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(d, true, provider, true);
if (iDescriptionsSet > 0)
{
db.Update(UpdateOptions.ExpandFull);
}
#endif
tran.GetType().InvokeMember("Commit", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, tran, null); //The .Commit() function used to return a list of strings, but in the latest set of code it is a void method which leads to "method not found" errors
//tran.Commit();
}
MessageBox.Show("Set " + iDescriptionsSet + " descriptions successfully.", "BIDS Helper - Sync Descriptions");
};
#if DENALI || SQL2014
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#else
sandbox.ExecuteEngineCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#endif
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
private void ExecSandbox(DataModelingSandboxWrapper sandboxParam)
{
try
{
//#if DENALI || SQL2014
sandbox = sandboxParam.GetSandbox();
//#else
// sandbox = sandboxParam.GetSandboxAmo();
//#endif
if (sandbox == null)
{
throw new Exception("Can't get Sandbox!");
}
cube = sandboxParam.GetSandboxAmo().Cube;
if (cube == null)
{
throw new Exception("The workspace database cube doesn't exist.");
}
bool bRestoreDisplayFolders = false;
SSAS.TabularDisplayFoldersAnnotation annotationSaved = GetAnnotation(sandboxParam.GetSandbox());
if (GetPreBuildWarning(sandboxParam.GetSandbox()) != null)
{
if (MessageBox.Show("Some display folders have been blanked out by other editing operations. Restoring display folders may be possible except when a measures or columns have been renamed.\r\n\r\nWould you like BIDS Helper to attempt restore the display folders now?", "BIDS Helper Tabular Display Folders", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
bRestoreDisplayFolders = true;
}
}
SSAS.TabularDisplayFolderWindow form = new SSAS.TabularDisplayFolderWindow();
List <BIDSHelper.SSAS.TabularDisplayFolder> displayFolders = new List <BIDSHelper.SSAS.TabularDisplayFolder>();
Dictionary <string, string> restrictions = new Dictionary <string, string>();
restrictions.Add("CUBE_NAME", cube.Name);
DataSet datasetMeasures = sandboxParam.GetSchemaDataSet("MDSCHEMA_MEASURES", restrictions);
Database db = cube.Parent;
foreach (Dimension d in db.Dimensions)
{
foreach (DimensionAttribute da in d.Attributes)
{
if (da.Type != AttributeType.RowNumber && da.AttributeHierarchyVisible)
{
if (bRestoreDisplayFolders && string.IsNullOrEmpty(da.AttributeHierarchyDisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(da);
if (a != null)
{
da.AttributeHierarchyDisplayFolder = a.DisplayFolder;
}
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(d.Name, da));
}
}
foreach (Hierarchy h in d.Hierarchies)
{
if (bRestoreDisplayFolders && string.IsNullOrEmpty(h.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(h);
if (a != null)
{
h.DisplayFolder = a.DisplayFolder;
}
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(d.Name, h));
}
}
foreach (Cube c in db.Cubes)
{
foreach (MdxScript mdx in c.MdxScripts)
{
foreach (CalculationProperty calc in mdx.CalculationProperties)
{
if (calc.Visible && !calc.CalculationReference.StartsWith("KPIs.")) //TODO: display folder for KPIs have to be set inline in MDX script... do this in the future
{
string sTableName = null;
foreach (DataRow r in datasetMeasures.Tables[0].Rows)
{
if (Convert.ToString(r["MEASURE_UNIQUE_NAME"]) == "[Measures]." + calc.CalculationReference)
{
sTableName = Convert.ToString(r["MEASUREGROUP_NAME"]);
break;
}
}
if (bRestoreDisplayFolders && string.IsNullOrEmpty(calc.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(calc);
if (a != null)
{
calc.DisplayFolder = a.DisplayFolder;
}
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(sTableName, calc));
}
}
}
}
displayFolders.Sort();
form.dataGrid1.ItemsSource = displayFolders;
if (form.ShowDialog() == true)
{
//count dirty changes and create annotation
SSAS.TabularDisplayFoldersAnnotation annotation = new SSAS.TabularDisplayFoldersAnnotation();
List <SSAS.TabularDisplayFolderAnnotation> folderAnnotationsList = new List <SSAS.TabularDisplayFolderAnnotation>();
int iNumberOfChanges = 0;
foreach (BIDSHelper.SSAS.TabularDisplayFolder folder in displayFolders)
{
if (folder.Dirty)
{
iNumberOfChanges++;
}
if (!string.IsNullOrEmpty(folder.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation folderAnnotation = new SSAS.TabularDisplayFolderAnnotation();
folderAnnotation.TableID = cube.Parent.Dimensions.GetByName(folder.Table).ID;
folderAnnotation.ObjectID = folder.ObjectID;
folderAnnotation.ObjectType = folder.ObjectType;
folderAnnotation.DisplayFolder = folder.DisplayFolder;
folderAnnotationsList.Add(folderAnnotation);
}
}
annotation.TabularDisplayFolders = folderAnnotationsList.ToArray();
if (iNumberOfChanges > 0 || bRestoreDisplayFolders)
{
#if DENALI || SQL2014
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code;
#else
Microsoft.AnalysisServices.BackEnd.AMOCode code;
#endif
code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandboxParam.GetSandbox().CreateTransaction())
{
foreach (BIDSHelper.SSAS.TabularDisplayFolder folder in displayFolders)
{
folder.SaveDisplayFolder();
}
TabularHelpers.SaveXmlAnnotation(cube.Parent, DISPLAY_FOLDER_ANNOTATION, annotation);
TabularHelpers.EnsureDataSourceCredentials(sandboxParam.GetSandbox());
cube.Parent.Update(UpdateOptions.ExpandFull);
tran.GetType().InvokeMember("Commit", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, tran, null); //The .Commit() function used to return a list of strings, but in the latest set of code it is a void method which leads to "method not found" errors
//tran.Commit();
}
};
#if DENALI || SQL2014
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#else
sandboxParam.GetSandbox().ExecuteEngineCode(DataModelingSandbox.OperationType.Update, DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#endif
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
/// <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));
// string sFileName = ((ProjectItem)hierItem.Object).Name.ToLower();
// if (sFileName.EndsWith(".bim"))
// {
// return true;
// }
// return (((ProjectItem)hierItem.Object).Object is Cube);
// }
// 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;
string sFileName = ((ProjectItem)hierItem.Object).Name.ToLower();
#if DENALI || SQL2014
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox = null;
#else
//Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo sandbox = null;
DataModelingSandboxWrapper sandbox = null;
#endif
bool bIsTabular = false;
Cube cub = null;
if (projItem.Object is Cube)
{
cub = (Cube)projItem.Object;
}
else if (sFileName.EndsWith(".bim"))
{
#if DENALI || SQL2014
sandbox = TabularHelpers.GetTabularSandboxFromBimFile(this, true);
cub = sandbox.Cube;
bIsTabular = true;
Microsoft.AnalysisServices.BackEnd.IDataModelingObjectCollection <Microsoft.AnalysisServices.BackEnd.DataModelingMeasure> measures = sandbox.Measures;
#else
sandbox = new DataModelingSandboxWrapper(this);
//sandbox = TabularHelpers.GetTabularSandboxAmoFromBimFile(this, true);
//cub = sandbox.Cube;
bIsTabular = true;
//Microsoft.AnalysisServices.BackEnd.IDataModelingObjectCollection<Microsoft.AnalysisServices.BackEnd.DataModelingMeasure> measures = sandbox.Cube.AllMeasures;
#endif
}
else
{
//if you are launching this feature from the Dimension Usage tab, but some other item in Solution Explorer is highlighted, then this code works and the above doesn't
projItem = this.ApplicationObject.ActiveWindow.ProjectItem;
cub = (Cube)projItem.Object;
}
DialogResult res = MessageBox.Show("Would you like a detailed report?\r\n\r\nPress Yes to see a detailed dimension usage report.\r\n\r\nPress No to see a summary level Bus Matrix dimension usage report.", "BIDS Helper - Printer Friendly Dimension Usage - Which Report Type?", MessageBoxButtons.YesNo);
ReportViewerForm frm = new ReportViewerForm();
if (bIsTabular)
{
bool bIsBusMatrix = (res == DialogResult.No);
System.Collections.Generic.List <DimensionUsage> list = PrinterFriendlyDimensionUsage.GetTabularDimensionUsage(sandbox, bIsBusMatrix);
DeploymentSettings _deploymentSettings = new DeploymentSettings(projItem);
//reset the database and cube name per the deployment settings
foreach (DimensionUsage du in list)
{
du.DatabaseName = _deploymentSettings.TargetDatabase;
du.CubeName = _deploymentSettings.TargetCubeName;
}
frm.ReportBindingSource.DataSource = list;
}
else
{
frm.ReportBindingSource.DataSource = PrinterFriendlyDimensionUsage.GetDimensionUsage(cub);
}
if (res == DialogResult.No)
{
frm.Report = "SSAS.PrinterFriendlyDimensionUsageBusMatrix.rdlc";
}
else
{
frm.Report = "SSAS.PrinterFriendlyDimensionUsage.rdlc";
}
Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource();
reportDataSource1.Name = "BIDSHelper_DimensionUsage";
reportDataSource1.Value = frm.ReportBindingSource;
frm.ReportViewerControl.LocalReport.DataSources.Add(reportDataSource1);
if (res == DialogResult.No)
{
frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = "BIDSHelper.PrinterFriendlyDimensionUsageBusMatrix.rdlc";
frm.Caption = "Printer Friendly Dimension Usage Bus Matrix";
}
else
{
frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = "BIDSHelper.PrinterFriendlyDimensionUsage.rdlc";
frm.Caption = "Printer Friendly Dimension Usage";
}
frm.Show();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
}
}
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);
}
}
