internal static int SyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingTable d, bool bPromptForProperties)
{
int iUpdatedDescriptions = 0;
Microsoft.AnalysisServices.BackEnd.EditMappingUtility util = new Microsoft.AnalysisServices.BackEnd.EditMappingUtility(d.Sandbox);
var conn = ((Microsoft.AnalysisServices.BackEnd.RelationalDataStorage)((util.GetDataSourceConnection(util.GetDataSourceID(d.Id), d.Sandbox)))).DataSourceConnection;
conn.Open();
System.Data.Common.DbCommand cmd = conn.CreateCommand();
string sDBTableName = d.SourceTableName;
sq = conn.Cartridge.IdentStartQuote;
fq = conn.Cartridge.IdentEndQuote;
//cartridge = conn.Cartridge;
if (conn.SourceType != Microsoft.AnalysisServices.BackEnd.DataSourceType.SqlServer && conn.SourceType != Microsoft.AnalysisServices.BackEnd.DataSourceType.SqlAzure)
{
MessageBox.Show("Data source [" + conn.ConnectionName + "] connects to " + conn.SourceType.ToString() + " which may not be supported.");
}
String sql = "select distinct Name from sys.extended_properties order by Name";
if (bPromptForProperties)
{
SSAS.SyncDescriptionsForm form = new SSAS.SyncDescriptionsForm();
cmd.CommandText = sql;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
List <string> listNames = new List <string>();
while (reader.Read())
{
listNames.Add(Convert.ToString(reader["Name"]));
form.listOtherProperties.Items.Add(Convert.ToString(reader["Name"]));
}
reader.Close();
form.cmbDescriptionProperty.DataSource = listNames;
DialogResult result = form.ShowDialog();
if (result != DialogResult.OK)
{
return(iUpdatedDescriptions);
}
DescriptionPropertyName = form.cmbDescriptionProperty.GetItemText(form.cmbDescriptionProperty.SelectedItem);
List <string> listOtherProperties = new List <string>();
for (int i = 0; i < form.listOtherProperties.CheckedItems.Count; i++)
{
listOtherProperties.Add(form.listOtherProperties.GetItemText(form.listOtherProperties.CheckedItems[i]));
}
OtherPropertyNamesToInclude = listOtherProperties.ToArray();
OverwriteExistingDescriptions = form.chkOverwriteExistingDescriptions.Checked;
}
if ((string.IsNullOrEmpty(d.Description) || OverwriteExistingDescriptions) &&
!string.IsNullOrEmpty(sDBTableName))
{
sql = "SELECT PropertyName = p.name" + "\r\n"
+ ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n"
+ "FROM sys.all_objects AS tbl" + "\r\n"
+ "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n"
+ "INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1" + "\r\n"
//+ "where sch.name = '" + oDimensionKeyTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n"
+ "where tbl.object_id = object_id('" + sDBTableName.Replace("'", "''") + "')\r\n"
+ "order by p.name";
string sNewDimensionDescription = "";
//DataSet dsTableProperties = new DataSet();
cmd.CommandText = sql;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (string.Compare((string)reader["PropertyName"], DescriptionPropertyName, true) == 0)
{
sNewDimensionDescription = (string)reader["PropertyValue"];
}
}
reader.Close();
reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (string sProp in OtherPropertyNamesToInclude)
{
if (string.Compare((string)reader["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)reader["PropertyValue"]))
{
if (sNewDimensionDescription.Length > 0)
{
sNewDimensionDescription += "\r\n";
}
sNewDimensionDescription += (string)reader["PropertyName"] + ": " + (string)reader["PropertyValue"];
}
}
}
reader.Close();
if (!string.IsNullOrEmpty(sNewDimensionDescription))
{
d.Description = sNewDimensionDescription;
iUpdatedDescriptions++;
}
}
foreach (Microsoft.AnalysisServices.BackEnd.DataModelingColumn a in d.Columns)
{
if (a.IsRowNumber || a.IsCalculated)
{
continue;
}
if ((string.IsNullOrEmpty(a.Description) || OverwriteExistingDescriptions) &&
(!string.IsNullOrEmpty(sDBTableName)))
{
sql = "SELECT PropertyName = p.name" + "\r\n"
+ ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n"
+ "FROM sys.all_objects AS tbl" + "\r\n"
+ "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n"
+ "INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id" + "\r\n"
+ "INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.object_id AND p.minor_id=clmns.column_id AND p.class=1" + "\r\n"
//+ "where sch.name = '" + oDsvTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n"
+ "where tbl.object_id = object_id('" + sDBTableName.Replace("'", "''") + "')\r\n"
+ "and clmns.name = '" + a.DBColumnName.Replace("'", "''") + "'\r\n"
+ "order by p.name";
string sNewDescription = "";
cmd.CommandText = sql;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (string.Compare((string)reader["PropertyName"], DescriptionPropertyName, true) == 0)
{
sNewDescription = (string)reader["PropertyValue"];
}
}
reader.Close();
cmd.CommandText = sql;
reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (string sProp in OtherPropertyNamesToInclude)
{
if (string.Compare((string)reader["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)reader["PropertyValue"]))
{
if (sNewDescription.Length > 0)
{
sNewDescription += "\r\n";
}
sNewDescription += (string)reader["PropertyName"] + ": " + (string)reader["PropertyValue"];
}
}
}
reader.Close();
if (!string.IsNullOrEmpty(sNewDescription))
{
a.Description = sNewDescription;
iUpdatedDescriptions++;
}
}
}
return(iUpdatedDescriptions);
}
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);
}
}