public void TestNewDeploymentMeasureError()
{
var newDbName = "AdventureWorks_UT_New2";
var server = new TOM.Server();
server.Connect(Constants.ServerName);
if (server.Databases.ContainsName(newDbName))
{
server.Databases[newDbName].Drop();
}
var handler = new TabularModelHandler("TestData\\AdventureWorks.bim");
handler.Model.Tables["Employee"].AddMeasure("ErrorTest", "xxx");
var results = TabularDeployer.Deploy(handler, Constants.ServerName, newDbName);
Assert.AreNotEqual(0, results.Issues.Count);
Assert.AreNotEqual(0, results.Unprocessed.Count);
Assert.AreEqual(0, results.Warnings.Count);
server.Refresh();
if (server.Databases.ContainsName(newDbName))
{
server.Databases[newDbName].Drop();
}
}
private void ProcessAnalysisServicesDatabase()
{
var accessToken = GetAccessToken("https://" + AasService);
var startTime = DateTime.UtcNow;
using (Tab.Server aas = new Tab.Server()) {
string connStr = $"Provider=MSOLAP;Data Source=asazure://{AasService}/{AasServer};Password={accessToken};Persist Security Info=True;Impersonation Level=Delegate;";
aas.Connect(connStr);
Tab.Database db = null;
if (!aas.Databases.ContainsName(AasDatabase))
{
// note: in case database is not found, verify service principal has *admin* rights - read/process rights are insufficent
throw new ApplicationException($"Database '{AasDatabase}' not found. Make sure service principal has database Admin rights.");
}
db = aas.Databases[AasDatabase];
db.Model.RequestRefresh(Tab.RefreshType.Full);
db.Model.SaveChanges(); // commit executes the refresh
aas.Disconnect();
}
var processTime = DateTime.UtcNow.Subtract(startTime);
_logger.LogInformation($"database processed in {processTime.TotalSeconds:n1} s");
}
public void TestNewDeploymentNoErrors()
{
var newDbName = "AdventureWorks_UT_New1";
var server = new TOM.Server();
server.Connect("localhost");
if (server.Databases.ContainsName(newDbName))
{
server.Databases[newDbName].Drop();
}
var handler = new TabularModelHandler("AdventureWorks.bim");
var results = TabularDeployer.Deploy(handler, "localhost", newDbName);
Assert.AreEqual(0, results.Issues.Count);
Assert.AreNotEqual(0, results.Unprocessed.Count);
Assert.AreEqual(0, results.Warnings.Count);
server.Refresh();
if (server.Databases.ContainsName(newDbName))
{
server.Databases[newDbName].Drop();
}
}
public void TestNewDeploymentCalcColumnError()
{
var newDbName = "AdventureWorks_UT_New3";
var server = new TOM.Server();
server.Connect("localhost");
if (server.Databases.ContainsName(newDbName))
{
server.Databases[newDbName].Drop();
}
var handler = new TabularModelHandler("AdventureWorks.bim");
handler.Model.Tables["Employee"].AddCalculatedColumn("ErrorTest", "xxx");
var results = TabularDeployer.Deploy(handler, "localhost", newDbName);
Assert.AreNotEqual(0, results.Issues.Count);
Assert.AreNotEqual(0, results.Unprocessed.Count);
Assert.AreNotEqual(0, results.Warnings.Count);
server.Refresh();
if (server.Databases.ContainsName(newDbName))
{
server.Databases[newDbName].Drop();
}
}
/// <summary>
/// Deploys the specified database to the specified target server and database ID, using the specified options.
/// Returns a list of DAX errors (if any) on objects inside the database, in case the deployment was succesful.
/// </summary>
/// <param name="db"></param>
/// <param name="targetConnectionString"></param>
/// <param name="targetDatabaseName"></param>
/// <param name="options"></param>
/// <returns></returns>
internal static DeploymentResult Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseName, DeploymentOptions options, CancellationToken cancellationToken)
{
if (string.IsNullOrWhiteSpace(targetConnectionString))
{
throw new ArgumentNullException("targetConnectionString");
}
var destinationServer = new TOM.Server();
destinationServer.Connect(targetConnectionString);
if (!destinationServer.SupportedCompatibilityLevels.Contains(db.CompatibilityLevel.ToString()))
{
throw new DeploymentException($"The specified server does not support Compatibility Level {db.CompatibilityLevel}");
}
var tmsl = GetTMSL(db, destinationServer, targetDatabaseName, options, true);
cancellationToken.Register(destinationServer.CancelCommand);
var result = destinationServer.Execute(tmsl);
if (result.ContainsErrors)
{
throw new DeploymentException(string.Join("\n", result.Cast <XmlaResult>().SelectMany(r => r.Messages.Cast <XmlaMessage>().Select(m => m.Description)).ToArray()));
}
// Refresh the server object to make sure we get an updated list of databases, in case a new database was made:
destinationServer.Refresh();
// Fully refresh the deployed database object, to make sure we get updated error messages for the full object tree:
var deployedDB = destinationServer.Databases.GetByName(targetDatabaseName);
deployedDB.Refresh(true);
return(GetLastDeploymentResults(deployedDB));
}
/// <summary>
/// Deploys the specified database to the specified target server and database ID, using the specified options.
/// Returns a list of DAX errors (if any) on objects inside the database, in case the deployment was succesful.
/// </summary>
/// <param name="db"></param>
/// <param name="targetConnectionString"></param>
/// <param name="targetDatabaseID"></param>
/// <param name="options"></param>
/// <returns></returns>
internal static DeploymentResult Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseID, DeploymentOptions options, CancellationToken cancellationToken)
{
if (string.IsNullOrWhiteSpace(targetConnectionString))
{
throw new ArgumentNullException("targetConnectionString");
}
var s = new TOM.Server();
s.Connect(targetConnectionString);
var tmsl = GetTMSL(db, s, targetDatabaseID, options, true);
cancellationToken.Register(s.CancelCommand);
var result = s.Execute(tmsl);
if (result.ContainsErrors)
{
throw new Exception(string.Join("\n", result.Cast <XmlaResult>().SelectMany(r => r.Messages.Cast <XmlaMessage>().Select(m => m.Description)).ToArray()));
}
s.Refresh();
var deployedDB = s.Databases[targetDatabaseID];
return
(new DeploymentResult(
TabularModelHandler.CheckErrors(deployedDB).Select(t => string.Format("Error on {0}: {1}", GetName(t.Item1), t.Item2)),
TabularModelHandler.GetObjectsNotReady(deployedDB).Where(t => t.Item2 == TOM.ObjectState.DependencyError || t.Item2 == TOM.ObjectState.EvaluationError || t.Item2 == TOM.ObjectState.SemanticError)
.Select(t => string.Format("Warning! Object not in \"Ready\"-state: {0} ({1})", GetName(t.Item1), t.Item2.ToString())),
TabularModelHandler.GetObjectsNotReady(deployedDB).Where(t => t.Item2 == TOM.ObjectState.CalculationNeeded || t.Item2 == TOM.ObjectState.NoData)
.Select(t => string.Format("Information: Unprocessed object: {0} ({1})", GetName(t.Item1), t.Item2.ToString()))
));
}
/// <summary>
/// Connects to a SQL Server 2016 Analysis Services instance and loads a tabular model
/// from one of the deployed databases on the instance.
/// </summary>
/// <param name="serverName"></param>
/// <param name="databaseName"></param>
public TabularModelHandler(string serverName, string databaseName, TabularModelHandlerSettings settings = null)
{
this.serverName = serverName;
_disableUpdates = true;
Settings = settings ?? TabularModelHandlerSettings.Default;
Singleton = this;
server = new TOM.Server();
var connectionString = TabularConnection.GetConnectionString(serverName, applicationName);
server.Connect(connectionString);
if (databaseName == null)
{
if (server.Databases.Count >= 1)
{
database = server.Databases[0];
}
else
{
throw new InvalidOperationException("This instance does not contain any databases, or the user does not have access.");
}
}
else
{
database = server.Databases.GetByName(databaseName);
}
if (CompatibilityLevel < 1200)
{
throw new InvalidOperationException("Only databases with Compatibility Level 1200 or higher can be loaded in Tabular Editor.");
}
SourceType = ModelSourceType.Database;
Source = database.Server.Name + "." + database.Name;
Status = "Connected succesfully.";
Version = database.Version;
Init();
Model.ClearTabularEditorAnnotations();
_disableUpdates = false;
UndoManager.Enabled = true;
PowerBIGovernance.UpdateGovernanceMode(this);
CheckErrors();
trace = new ExternalChangeTrace(database, applicationName, XEventCallback);
if (Settings.ChangeDetectionLocalServers)
{
trace.Start();
}
}
private void cmdConnect_Click(object sender, EventArgs e)
{
try
{
cboDatabaseName.Items.Clear();
cboDatabaseName.Text = "";
cboCubeName.Items.Clear();
cboCubeName.Text = "";
String ConnStr;
OLAPServerName = txtServerName.Text;
txtProgress.AppendText("");
if (checkBoxCurrentCreds.Checked == true)
{
ConnStr = "Provider=MSOLAP;Data Source=" + OLAPServerName + ";";
}
else
{
// Provider = MSOLAP.8; Persist Security Info = True; User ID = sg3\msorn; Initial Catalog = SSASTOM; Data Source = sg3\sql2017; MDX Compatibility = 1; Safety Options = 2; MDX Missing Member Mode = Error; Update Isolation Level = 2
ConnStr = "Provider=MSOLAP;Data Source=" + OLAPServerName + ";User Id = " + textBoxUserName.Text + "; Password = "******";";
}
//Initial Catalog=Adventure Works DW 2008R2;";
OLAPServer = new AMO.Server();
TOMServer = new TOM.Server();
OLAPServer.Connect(ConnStr);
TOMServer.Connect(ConnStr);
Console.WriteLine("ServerName : " + OLAPServerName);
cboDatabaseName.Items.Clear();
foreach (AMO.Database OLAPDatabase in OLAPServer.Databases)
{
ComboboxItem item = new ComboboxItem();
item.Text = OLAPDatabase.Name;
item.Value = OLAPDatabase.ID;
cboDatabaseName.Items.Add(item);
}
}
catch (Exception err)
{
string errormsg = err.InnerException.ToString();
txtProgress.AppendText("--------------------------------------------------------------------------------------" + Environment.NewLine);
txtProgress.AppendText("Error Occured" + Environment.NewLine);
txtProgress.AppendText(err.InnerException.ToString() + Environment.NewLine);
MessageBox.Show(errormsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void AddSalesRegionMeasures()
{
// DAX query to be submitted totabuar database engine
String query = "EVALUATE( VALUES(Customers[Sales Region]) )";
AdomdConnection adomdConnection = new AdomdConnection(connectString);
adomdConnection.Open();
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
AdomdDataReader reader = adomdCommand.ExecuteReader();
// open connection use TOM to create new measures
TOM.Server server = new TOM.Server();
server.Connect(connectString);
TOM.Model model = server.Databases[0].Model;
TOM.Table salesTable = model.Tables["Sales"];
String measureDescription = "Auto Measures";
// delete any previously created "Auto" measures
foreach (TOM.Measure m in salesTable.Measures)
{
if (m.Description == measureDescription)
{
salesTable.Measures.Remove(m);
model.SaveChanges();
}
}
// Create the new measures
while (reader.Read())
{
String SalesRegion = reader.GetValue(0).ToString();
String measureName = $"{SalesRegion} Sales";
TOM.Measure measure = new TOM.Measure()
{
Name = measureName,
Description = measureDescription,
DisplayFolder = "Auto Measures",
FormatString = "$#,##0",
Expression = $@"CALCULATE( SUM(Sales[SalesAmount]), Customers[Sales Region] = ""{SalesRegion}"" )"
};
salesTable.Measures.Add(measure);
}
model.SaveChanges();
reader.Dispose();
adomdConnection.Close();
}
public void TableCount1200()
{
using (Tom.Server server = new Tom.Server())
{
server.Connect("localhost\\tb");
Tom.Database db = server.Databases.FindByName("Test1200_Target");
Assert.IsNotNull(db);
Assert.AreEqual(6, db.Model.Tables.Count);
server.Disconnect();
}
}
public void SaveModelMetadataBackup(string connectionString, string targetDatabaseName, string backupFilePath)
{
using (var s = new TOM.Server())
{
s.Connect(connectionString);
var db = s.Databases.Find(targetDatabaseName) ?? s.Databases.FindByName(targetDatabaseName);
if (db != null)
{
var dbcontent = TOM.JsonSerializer.SerializeDatabase(db);
WriteZip(backupFilePath, dbcontent);
}
s.Disconnect();
}
}
public static void SaveModelMetadataBackup(string connectionString, string targetDatabaseID, string backupFilePath)
{
using (var s = new TOM.Server())
{
s.Connect(connectionString);
if (s.Databases.Contains(targetDatabaseID))
{
var db = s.Databases[targetDatabaseID];
var dbcontent = TOM.JsonSerializer.SerializeDatabase(db);
}
s.Disconnect();
}
}
/// <summary>
/// Connect to analysis services instance and database, throw if error is encountered
/// </summary>
/// <param name="serverName"></param>
private void Connect(string serverName)
{
//string connectionString = $"Provider=MSOLAP;Data Source={args.Server};";
string connectionString = $"Provider=MSOLAP;Data Source={serverName};Integrated Security=SSPI;Persist Security Info=True;";
server.Connect(connectionString);
database = server.Databases.FindByName(aggsConfig.Database.Name);
if (database == null)
{
throw new Microsoft.AnalysisServices.ConnectionException($"Could not find database [{aggsConfig.Database.Name}]");
}
}
private void Connect()
{
try
{
Connect connForm = new Connect();
connForm.StartPosition = FormStartPosition.CenterParent;
connForm.ShowDialog();
if (connForm.DialogResult == DialogResult.OK)
{
_timer.Enabled = false;
if (connForm.IntegratedAuth)
{
_serverConnectionString = $"Provider=MSOLAP;Data Source={connForm.ServerName};";
}
else
{
_serverConnectionString = $"Provider=MSOLAP;Data Source={connForm.ServerName};User ID={connForm.UserName};Password={connForm.Passwrod};Persist Security Info=True;Impersonation Level=Impersonate;";
}
_server = new Tom.Server();
_server.Connect(_serverConnectionString);
_connectionTime = DateTime.Now;
if (_server.ServerProperties.Count == 0)
{
throw new ConnectionException("User is not AS admin.");
}
chartControl.Titles.Clear();
chartControl.Titles.Add(connForm.ServerName);
chartControl.Titles[0].Font = new Font(Font.FontFamily, _smallestFont + 4);
InitializeChart(true);
_timer.Interval = connForm.SampleInterval;
_timer.Enabled = true;
Settings.Default.ServerName = connForm.ServerName;
Settings.Default.UserName = connForm.UserName;
Settings.Default.SampleInterval = connForm.SampleInterval;
Settings.Default.IntegratedAuth = connForm.IntegratedAuth;
Settings.Default.Save();
}
}
catch (Exception exc)
{
MessageBox.Show($"Cannot connect.\n{exc.Message}", "AS PerfMon", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Connects to a SQL Server 2016 Analysis Services instance and loads a tabular model
/// from one of the deployed databases on the instance.
/// </summary>
/// <param name="serverName"></param>
/// <param name="databaseId"></param>
public TabularModelHandler(string serverName, string databaseId, TabularModelHandlerSettings settings = null)
{
this.serverName = serverName;
_disableUpdates = true;
Settings = settings ?? TabularModelHandlerSettings.Default;
Singleton = this;
server = new TOM.Server();
server.Connect(serverName);
if (databaseId == null)
{
if (server.Databases.Count >= 1)
{
database = server.Databases[0];
}
else
{
throw new InvalidOperationException("This instance does not contain any databases, or the user does not have access.");
}
}
else
{
database = server.Databases[databaseId];
}
database.RemoveTabularEditorTag();
CompatibilityLevel = database.CompatibilityLevel;
if (CompatibilityLevel < 1200)
{
throw new InvalidOperationException("Only databases with Compatibility Level 1200 or higher can be loaded in Tabular Editor.");
}
SourceType = ModelSourceType.Database;
Source = database.Server.Name + "." + database.Name;
Status = "Connected succesfully.";
Version = database.Version;
Init();
Model.ClearTabularEditorAnnotations();
_disableUpdates = false;
UndoManager.Enabled = true;
PowerBIGovernance.UpdateGovernanceMode(this);
}
/// <summary>
/// Deploys the specified database to the specified target server and database ID, using the specified options.
/// Returns a list of DAX errors (if any) on objects inside the database, in case the deployment was succesful.
/// </summary>
/// <param name="db"></param>
/// <param name="targetConnectionString"></param>
/// <param name="targetDatabaseName"></param>
/// <param name="options"></param>
/// <returns></returns>
internal static DeploymentResult Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseName, DeploymentOptions options, CancellationToken cancellationToken)
{
if (string.IsNullOrWhiteSpace(targetConnectionString))
{
throw new ArgumentNullException("targetConnectionString");
}
var destinationServer = new TOM.Server();
destinationServer.Connect(targetConnectionString);
if (!destinationServer.SupportedCompatibilityLevels.Contains(db.CompatibilityLevel.ToString()))
{
throw new DeploymentException($"The specified server does not support Compatibility Level {db.CompatibilityLevel}");
}
var tmsl = GetTMSL(db, destinationServer, targetDatabaseName, options, true);
cancellationToken.Register(destinationServer.CancelCommand);
var result = destinationServer.Execute(tmsl);
if (result.ContainsErrors)
{
throw new DeploymentException(string.Join("\n", result.Cast <XmlaResult>().SelectMany(r => r.Messages.Cast <XmlaMessage>().Select(m => m.Description)).ToArray()));
}
// Refresh the server object to make sure we get an updated list of databases, in case a new database was made:
destinationServer.Refresh();
// Fully refresh the deployed database object, to make sure we get updated error messages for the full object tree:
var deployedDB = destinationServer.Databases.GetByName(targetDatabaseName);
deployedDB.Refresh(true);
return
(new DeploymentResult(
TabularModelHandler.CheckErrors(deployedDB).Select(t => string.Format("Error on {0}: {1}", GetName(t.Item1), t.Item2)),
TabularModelHandler.GetObjectsNotReady(deployedDB).Where(t => t.Item2 == TOM.ObjectState.DependencyError || t.Item2 == TOM.ObjectState.EvaluationError || t.Item2 == TOM.ObjectState.SemanticError)
.Select(t => string.Format("Warning! Object not in \"Ready\"-state: {0} ({1})", GetName(t.Item1), t.Item2.ToString())),
TabularModelHandler.GetObjectsNotReady(deployedDB).Where(t => t.Item2 == TOM.ObjectState.CalculationNeeded || t.Item2 == TOM.ObjectState.NoData)
.Select(t => string.Format("Information: Unprocessed object: {0} ({1})", GetName(t.Item1), t.Item2.ToString())),
destinationServer
));
}
static void Main(string[] args)
{
Console.WriteLine("Started");
Boolean success = false;
XMLreader.XMLinit();//XML load and parameters initialization
string connStringTabular = XMLreader.tabularConn;
string stagingSchemaName = XMLreader.stagingSchemaName;
string tabularDB = XMLreader.currentTabularDB;
string connectionStringORA = XMLreader.dbOracle;
string connectionStringSQLserver = XMLreader.dbSQLserver;
string[] tableArr = XMLreader.tablesToProcess;
string midDefautldate = XMLreader.minDefaultDate;
string maxdefautldate = XMLreader.maxDefaultDate;
DbConnection dbConn;
int isOracle = 0;
SimpleLogger.Info(XMLreader.printData());
DWHconnection[] tableInfoArr = new DWHconnection[tableArr.Length];//get the list of tables need processing
var svr = new Microsoft.AnalysisServices.Tabular.Server();
try
{
svr.Connect(connStringTabular);
Microsoft.AnalysisServices.Tabular.Database db = svr.Databases[tabularDB];
Model m = db.Model;
SimpleLogger.Info("Connected to DB: " + db.Name.ToString() + " MODEL: " + m.Name.ToString());
// Check dwh db type
if (connectionStringORA is null && !(connectionStringSQLserver is null))
{
dbConn = new SqlConnection();
dbConn.ConnectionString = connectionStringSQLserver;
}
else if (!(connectionStringORA is null) && connectionStringSQLserver is null)
{
dbConn = new OracleConnection();
dbConn.ConnectionString = connectionStringORA;
isOracle = 1;
}
/// <summary>
/// Deploys the specified database to the specified target server and database ID, using the specified options.
/// Returns a list of DAX errors (if any) on objects inside the database, in case the deployment was succesful.
/// </summary>
/// <param name="db"></param>
/// <param name="targetConnectionString"></param>
/// <param name="targetDatabaseID"></param>
/// <param name="options"></param>
/// <returns></returns>
internal static void Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseID, DeploymentOptions options)
{
if (string.IsNullOrWhiteSpace(targetConnectionString))
{
throw new ArgumentNullException("targetConnectionString");
}
var s = new TOM.Server();
s.Connect(targetConnectionString);
var tmsl = GetTMSL(db, s, targetDatabaseID, options, true);
var result = s.Execute(tmsl);
if (result.ContainsErrors)
{
throw new Exception(string.Join("\n", result.Cast <XmlaResult>().SelectMany(r => r.Messages.Cast <XmlaMessage>().Select(m => m.Description)).ToArray()));
}
s.Refresh();
var deployedDB = s.Databases[targetDatabaseID];
}
public ConflictInfo CheckConflicts()
{
if (database == null || database?.Server == null)
{
return new ConflictInfo {
DatabaseVersion = -1
}
}
;
var s = new TOM.Server();
s.Connect(database?.Server.ConnectionString);
var db = s.Databases[database?.ID];
return(new ConflictInfo {
DatabaseVersion = db.Version,
LoadedVersion = Version,
DatabaseLastUpdate = db.LastUpdate,
Conflict = db.Version != Version
});
}
public ConflictInfo CheckConflicts()
{
if (string.IsNullOrEmpty(this.serverName))
{
return new ConflictInfo {
DatabaseVersion = -1
}
}
;
var s = new TOM.Server();
s.Connect(this.serverName);
var db = s.Databases[database?.ID];
return(new ConflictInfo
{
DatabaseVersion = db.Version,
LoadedVersion = Version,
DatabaseLastUpdate = db.LastUpdate,
Conflict = db.Version != Version
});
}
private static XmlaResultCollection DynamicXMLAParser(
string serverAddress, string database, string XMLAQuery, bool outputToCsv)
{
string ConnectionString = @"Provider=MSOLAP;Data Source=" + serverAddress +
@";Initial Catalog=" + database + ";Integrated Security=SSPI;ImpersonationLevel = Impersonate; persist security info = True; ";
var XMLA = XMLAQuery;
var objServer = new Microsoft.AnalysisServices.Tabular.Server();
objServer.Connect(ConnectionString);
var reader = objServer.ExecuteReader(XMLA, out XmlaResultCollection
resultsOut, null, true);
// Add all roles to Cached List
//while (reader.Read())
//{
//}
reader.Close();
reader.Dispose();
objServer.Dispose();
return(resultsOut);
}
public static double GetDadNumber(Dictionary <string, object> kvp, DeviceType deviceType)
{
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
double dad = 0;
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagamentTable = tabularDatabase.Model.Tables.Find(tableName);
string measureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var dadMeasure = oasisProductEngagamentTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
Console.WriteLine(dadMeasure.Expression);
dad = ExecuteQuery(dadMeasure.Expression, userId, algtelPassword, oasisProductEngagamentTable.Name, ssasServer, kvp, databaseName);
dad = Math.Round(dad, 0);
}
return(dad);
}
/// <summary>
/// Connects to a SQL Server 2016 Analysis Services instance and loads a tabular model
/// from one of the deployed databases on the instance.
/// </summary>
/// <param name="serverName"></param>
/// <param name="databaseId"></param>
public TabularModelHandler(string serverName, string databaseId, TabularModelHandlerSettings settings = null)
{
Settings = settings ?? TabularModelHandlerSettings.Default;
Singleton = this;
server = new TOM.Server();
server.Connect(serverName);
if (databaseId == null)
{
if (server.Databases.Count >= 1)
{
database = server.Databases[0];
}
else
{
throw new InvalidOperationException("This instance does not contain any databases, or the user does not have access.");
}
}
else
{
database = server.Databases[databaseId];
}
CompatibilityLevel = database.CompatibilityLevel;
if (CompatibilityLevel < 1200)
{
throw new InvalidOperationException("Only databases with Compatibility Level 1200 or higher can be loaded in Tabular Editor.");
}
SourceType = ModelSourceType.Database;
Source = database.Server.Name + "." + database.Name;
Status = "Connected succesfully.";
Version = database.Version;
Init();
}
public static void CreateNewDadMeasure(string measureName, string measureDescription, Dictionary <string, object> filterKeyValuePairs, DeviceType deviceType)
{
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagementTable = tabularDatabase.Model.Tables.Find(tableName);
var newDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
if (newDadMeasure != null)
{
oasisProductEngagementTable.Measures.Remove(newDadMeasure);
}
StringBuilder stbr = new StringBuilder();
foreach (var k in filterKeyValuePairs)
{
if (k.Key == "Is Mainstream")
{
stbr.Append($"'{tableName}'[" + k.Key + "] = " + k.Value);
}
else
{
stbr.Append($"'{tableName}'[" + k.Key + "] = \"" + k.Value + "\"");
}
stbr.Append(" , ");
}
string str = stbr.ToString();
str = str.TrimEnd();
str = str.Remove(str.LastIndexOf(","));
string oldDadMeasureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var oldDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == oldDadMeasureName).FirstOrDefault();
string newDadMeasureExpression = string.Empty;
if (deviceType == DeviceType.Hololens)
{
newDadMeasureExpression = GetLatestDadExpression(oldDadMeasure.Expression, filterKeyValuePairs, tableName);
}
else
{
string oldDadMeasureExpression = oldDadMeasure.Expression;
oldDadMeasureExpression = oldDadMeasureExpression.Substring(oldDadMeasureExpression.IndexOf("AVERAGEX"));
oldDadMeasureExpression = oldDadMeasureExpression.Remove(oldDadMeasureExpression.LastIndexOf(")"));
newDadMeasureExpression = GetLatestDadExpression(oldDadMeasureExpression, filterKeyValuePairs, tableName);
}
string displayFolder = deviceType == DeviceType.Hololens ? @"[Measures]\Device Count" : @"[Measures]\PC Counting";
oasisProductEngagementTable.Measures.Add(CreateMeasure(measureName, newDadMeasureExpression, measureDescription, displayFolder));
tabularDatabase.Model.SaveChanges();
}
}
/// <summary>
/// Connects to a SQL Server 2016 Analysis Services instance and loads a tabular model
/// from one of the deployed databases on the instance.
/// </summary>
/// <param name="serverName"></param>
/// <param name="databaseName"></param>
public TabularModelHandler(string serverName, string databaseName, TabularModelHandlerSettings settings = null) : this(settings)
{
this.serverName = serverName;
_disableUpdates = true;
server = new TOM.Server();
var connectionString = TabularConnection.GetConnectionString(serverName, applicationName);
server.Connect(connectionString);
if (string.IsNullOrEmpty(databaseName))
{
if (server.Databases.Count >= 1)
{
database = server.Databases[0];
}
else
{
throw new InvalidOperationException("This instance does not contain any databases, or the user does not have access.");
}
}
else
{
database = server.Databases.FindByName(databaseName);
if (database == null)
{
database = server.Databases[databaseName];
}
}
if (CompatibilityLevel < 1200)
{
throw new InvalidOperationException("Only databases with Compatibility Level 1200 or higher can be loaded in Tabular Editor.");
}
SourceType = ModelSourceType.Database;
Source = database.Server.Name + "." + database.Name;
Status = "Connected successfully.";
Version = database.Version;
Init();
UndoManager.Suspend();
Model.ClearTabularEditorAnnotations();
_disableUpdates = false;
UndoManager.Resume();
PowerBIGovernance.UpdateGovernanceMode();
CheckErrors();
try
{
ExternalChangeTrace.Cleanup();
trace = new ExternalChangeTrace(database, applicationName, XEventCallback);
if (Settings.ChangeDetectionLocalServers)
{
trace.Start();
}
}
catch (Exception ex)
{
Log("Exception while configuring AS trace: " + ex.Message);
}
}
public static SortedDictionary <string, double> ExecuteGroupByDad(List <string> slicerList, Dictionary <string, object> filterList, DeviceType deviceType)
{
SortedDictionary <string, double> dadDictionary = new SortedDictionary <string, double>();
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagamentTable = tabularDatabase.Model.Tables.Find(tableName);
string measureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var dadMeasure = oasisProductEngagamentTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
StringBuilder stbr = new StringBuilder();
foreach (var slicer in slicerList)
{
stbr.Append($"'{tableName}'[" + slicer + "]");
stbr.Append(" , ");
}
string str = stbr.ToString();
str = str.TrimEnd();
str = str.Remove(str.LastIndexOf(","));
string newTableName = (filterList.Count > 0) ? GetStringForGroupBy(tableName, filterList) : $"'{tableName}'";
string queryString = $"EVALUATE SUMMARIZE({newTableName}, {str}, \"Group by measure\"," + dadMeasure.Expression + ")";
string msolapConnectionString = $"Provider=MSOLAP;Data Source={ssasServer};Initial Catalog={databaseName};User ID = {userId};Password = {algtelPassword};Persist Security Info=True; Impersonation Level=Impersonate;";
using (var connection = new OleDbConnection(msolapConnectionString))
{
connection.Open();
using (var command = new OleDbCommand(queryString, connection))
{
using (var reader = command.ExecuteReader())
{
int columns = reader.FieldCount;
while (reader.Read())
{
string keyString = string.Empty;
for (int i = 0; i < columns - 1; i++)
{
keyString += reader[i].ToString() + ", ";
}
keyString = keyString.TrimEnd();
keyString = keyString.Remove(keyString.LastIndexOf(","));
double value = double.Parse(reader[columns - 1].ToString());
value = Math.Round(value, 0);
dadDictionary.Add(keyString, value);
}
}
}
}
}
return(dadDictionary);
}
private void OnTimedEvent(Object source, ElapsedEventArgs e)
{
try
{
if (chartControl.InvokeRequired)
{
SetTimerCallback callback = new SetTimerCallback(OnTimedEvent);
//todo safer invoke look up bism
try
{
Invoke(callback, new object[] { source, e });
}
catch { }
}
else
{
//Todo delete this once http stability fix is done
if ((DateTime.Now - _connectionTime).TotalMinutes > 8)
{
_server.Disconnect();
_server.Connect(_serverConnectionString);
_connectionTime = DateTime.Now;
}
_seriesData[0] = GetNewXAxisMarkerPointsFromAs();
//rebind
chartControl.Series.Clear();
//for each X axis marker
for (int i = _seriesData.Length - 1; i > 0; i--)
{
//push back one category
_seriesData[i] = _seriesData[i - 1];
//foreach series for the X axis marker we are populating: ...
foreach (KeyValuePair <string, SeriesPoint> entry in _seriesData[i])
{
//add the series if not there yet
if (chartControl.Series.FindByName(entry.Key) == null)
{
Series series = new Series(entry.Key);
series.ChartType = SeriesChartType.StackedColumn;
chartControl.Series.Add(series);
}
//add the data point for the series
DataPoint point = new DataPoint();
point.SetValueXY(entry.Value.XAxisLabel, entry.Value.MemoryUsedMegabytes);
point.ToolTip = string.Format($"{entry.Value.XAxisLabel} - {entry.Key}: {string.Format("{0:#,###0}", entry.Value.MemoryUsedMegabytes)} MB");
chartControl.Series[entry.Key].Points.Add(point);
}
}
PaintChart();
}
}
catch (Exception exc)
{
//Workaround for timeout over HTTP. Try to reconnect - todo delete
try
{
System.Diagnostics.Debug.WriteLine($"Exception occurred at {DateTime.Now}: {exc.Message}");
_server.Disconnect();
_server.Connect(_serverConnectionString);
_connectionTime = DateTime.Now;
}
catch
{
_timer.Enabled = false;
MessageBox.Show($"Error: {exc.Message}", "AS PerfMon", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
void Deploy(string serverName, string databaseID, int doDeploy)
{
// Perform direct save:
if (serverName == null)
{
var nextSwitch = upperArgList.Skip(doDeploy + 1).FirstOrDefault();
var deploySwitches = new[] { "-L", "-LOGIN", "-O", "-OVERWRITE", "-C", "-CONNECTIONS", "-P", "-PARTITIONS", "-Y", "-SKIPPOLICY", "-R", "-ROLES", "-M", "-MEMBERS", "-X", "-XMLA" };
if (deploySwitches.Contains(nextSwitch))
{
Error("Invalid argument syntax.");
OutputUsage();
throw new CommandLineException();
}
Console.WriteLine("Saving model metadata back to source...");
if (Handler.SourceType == ModelSourceType.Database)
{
try
{
Handler.SaveDB();
Console.WriteLine("Model metadata saved.");
var deploymentResult = Handler.GetLastDeploymentResults();
foreach (var err in deploymentResult.Issues)
{
if (errorOnDaxErr)
{
Error(err);
}
else
{
Warning(err);
}
}
foreach (var err in deploymentResult.Warnings)
{
Warning(err);
}
foreach (var err in deploymentResult.Unprocessed)
{
if (warnOnUnprocessed)
{
Warning(err);
}
else
{
Console.WriteLine(err);
}
}
}
catch (Exception ex)
{
Error("Save failed: " + ex.Message);
}
}
else
{
try
{
Handler.Save(Handler.Source, Handler.SourceType == ModelSourceType.Folder ? SaveFormat.TabularEditorFolder : Handler.SourceType == ModelSourceType.Pbit ? SaveFormat.PowerBiTemplate : SaveFormat.ModelSchemaOnly, Handler.SerializeOptions, true);
Console.WriteLine("Model metadata saved.");
}
catch (Exception ex)
{
Error("Save failed: " + ex.Message);
}
}
throw new CommandLineException();
}
var conn = upperArgList.IndexOf("-CONNECTIONS");
if (conn == -1)
{
conn = upperArgList.IndexOf("-C");
}
if (conn > -1)
{
var replaces = argList.Skip(conn + 1).TakeWhile(s => s[0] != '-').ToList();
if (replaces.Count > 0 && replaces.Count % 2 == 0)
{
// Placeholder replacing:
for (var index = 0; index < replaces.Count; index = index + 2)
{
replaceMap.Add(replaces[index], replaces[index + 1]);
}
}
}
string userName = null;
string password = null;
var options = DeploymentOptions.StructureOnly;
var switches = argList.Skip(doDeploy + 1).Where(arg => arg.StartsWith("-")).Select(arg => arg.ToUpper()).ToList();
if (string.IsNullOrEmpty(serverName) || string.IsNullOrEmpty(databaseID))
{
Error("Invalid argument syntax.\n");
OutputUsage();
throw new CommandLineException();
}
if (switches.Contains("-L") || switches.Contains("-LOGIN"))
{
var switchPos = upperArgList.IndexOf("-LOGIN"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-L");
}
userName = argList.Skip(switchPos + 1).FirstOrDefault(); if (userName != null && userName.StartsWith("-"))
{
userName = null;
}
password = argList.Skip(switchPos + 2).FirstOrDefault(); if (password != null && password.StartsWith("-"))
{
password = null;
}
if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password))
{
Error("Missing username or password.\n");
OutputUsage();
throw new CommandLineException();
}
switches.Remove("-L"); switches.Remove("-LOGIN");
}
if (switches.Contains("-O") || switches.Contains("-OVERWRITE"))
{
options.DeployMode = DeploymentMode.CreateOrAlter;
switches.Remove("-O"); switches.Remove("-OVERWRITE");
}
else
{
options.DeployMode = DeploymentMode.CreateDatabase;
}
if (switches.Contains("-P") || switches.Contains("-PARTITIONS"))
{
options.DeployPartitions = true;
switches.Remove("-P"); switches.Remove("-PARTITIONS");
if (switches.Contains("-Y") || switches.Contains("-SKIPPOLICY"))
{
options.SkipRefreshPolicyPartitions = true;
switches.Remove("-Y"); switches.Remove("-SKIPPOLICY");
}
}
if (switches.Contains("-C") || switches.Contains("-CONNECTIONS"))
{
options.DeployConnections = true;
switches.Remove("-C"); switches.Remove("-CONNECTIONS");
}
if (switches.Contains("-R") || switches.Contains("-ROLES"))
{
options.DeployRoles = true;
switches.Remove("-R"); switches.Remove("-ROLES");
if (switches.Contains("-M") || switches.Contains("-MEMBERS"))
{
options.DeployRoleMembers = true;
switches.Remove("-M"); switches.Remove("-MEMBERS");
}
}
var xmla_scripting_only = switches.Contains("-X") || switches.Contains("-XMLA");
string xmla_script_file = null;
if (xmla_scripting_only)
{
var switchPos = upperArgList.IndexOf("-XMLA"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-X");
}
xmla_script_file = argList.Skip(switchPos + 1).FirstOrDefault(); if (String.IsNullOrWhiteSpace(xmla_script_file) || xmla_script_file.StartsWith("-"))
{
xmla_script_file = null;
}
if (string.IsNullOrEmpty(xmla_script_file))
{
Error("Missing xmla_script_file.\n");
OutputUsage();
throw new CommandLineException();
}
switches.Remove("-X");
switches.Remove("-XMLA");
}
try
{
if (replaceMap.Count > 0)
{
Console.WriteLine("Switching connection string placeholders...");
foreach (var map in replaceMap)
{
Handler.Model.DataSources.SetPlaceholder(map.Key, map.Value);
}
}
var cs = string.IsNullOrEmpty(userName) ? TabularConnection.GetConnectionString(serverName, Program.ApplicationName) :
TabularConnection.GetConnectionString(serverName, userName, password, Program.ApplicationName);
if (xmla_scripting_only)
{
Console.WriteLine("Generating XMLA/TMSL script...");
var s = new TOM.Server();
s.Connect(cs);
var xmla = Handler.TabularDeployer.GetTMSL(Handler.Database, s, databaseID, options);
using (var sw = new StreamWriter(xmla_script_file))
{
sw.Write(xmla);
}
Console.WriteLine("XMLA/TMSL script generated.");
}
else
{
Console.WriteLine("Deploying...");
Handler.Model.UpdateDeploymentMetadata(DeploymentModeMetadata.CLI);
var deploymentResult = Handler.TabularDeployer.Deploy(Handler.Database, cs, databaseID, options, CancellationToken.None);
Console.WriteLine("Deployment succeeded.");
foreach (var err in deploymentResult.Issues)
{
if (errorOnDaxErr)
{
Error(err);
}
else
{
Warning(err);
}
}
foreach (var err in deploymentResult.Warnings)
{
Warning(err);
}
foreach (var err in deploymentResult.Unprocessed)
{
if (warnOnUnprocessed)
{
Warning(err);
}
else
{
Console.WriteLine(err);
}
}
}
}
catch (Exception ex)
{
Error($"{(xmla_scripting_only ? "Script generation" : "Deployment")} failed! {ex.Message}");
}
}
internal bool HandleCommandLine(string[] args)
{
var upperArgList = args.Select(arg => arg.ToUpper()).ToList();
var argList = args.Select(arg => arg).ToList();
if (upperArgList.Contains("-?") || upperArgList.Contains("/?") || upperArgList.Contains("-H") || upperArgList.Contains("/H") || upperArgList.Contains("HELP"))
{
OutputUsage();
return(true);
}
EnableVSTS = upperArgList.IndexOf("-VSTS") > -1 || upperArgList.IndexOf("-V") > -1;
var warnOnUnprocessed = upperArgList.IndexOf("-WARN") > -1 || upperArgList.IndexOf("-W") > -1;
var errorOnDaxErr = upperArgList.IndexOf("-ERR") > -1 || upperArgList.IndexOf("-E") > -1;
TabularModelHandler h;
if (args.Length == 2 || args[2].StartsWith("-"))
{
// File argument provided (either alone or with switches), i.e.:
// TabularEditor.exe myfile.bim
// TabularEditor.exe myfile.bim -...
if (!File.Exists(args[1]) && !File.Exists(args[1] + "\\database.json"))
{
Error("File not found: {0}", args[1]);
return(true);
}
else
{
// If nothing else was specified on the command-line, open the UI:
if (args.Length == 2)
{
return(false);
}
}
try
{
h = new TOMWrapper.TabularModelHandler(args[1]);
}
catch (Exception e)
{
Error("Error loading file: " + e.Message);
return(true);
}
}
else if (args.Length == 3 || args[3].StartsWith("-"))
{
// Server + Database argument provided (either alone or with switches), i.e.:
// TabularEditor.exe localhost AdventureWorks
// TabularEditor.exe localhost AdventureWorks -...
// If nothing else was specified on the command-line, open the UI:
if (args.Length == 3)
{
return(false);
}
try
{
h = new TOMWrapper.TabularModelHandler(args[1], args[2]);
}
catch (Exception e)
{
Error("Error loading model: " + e.Message);
return(true);
}
}
else
{
// Otherwise, it's nonsensical
return(false);
}
string script = null;
string scriptFile = null;
var doTestRun = upperArgList.IndexOf("-T");
string testRunFile = null;
if (doTestRun == -1)
{
doTestRun = upperArgList.IndexOf("-TRX");
}
if (doTestRun > -1)
{
if (upperArgList.Count <= doTestRun || upperArgList[doTestRun + 1].StartsWith("-"))
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
Program.testRun = new TestRun(h.Database?.Name ?? h.Source);
testRunFile = argList[doTestRun + 1];
}
var doScript = upperArgList.IndexOf("-SCRIPT");
if (doScript == -1)
{
doScript = upperArgList.IndexOf("-S");
}
if (doScript > -1)
{
if (upperArgList.Count <= doScript)
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
scriptFile = argList[doScript + 1];
script = File.Exists(scriptFile) ? File.ReadAllText(scriptFile) : scriptFile;
}
var doCheckDs = upperArgList.IndexOf("-SCHEMACHECK");
if (doCheckDs == -1)
{
doCheckDs = upperArgList.IndexOf("-SC");
}
string saveToFolderOutputPath = null;
string saveToFolderReplaceId = null;
var doSaveToFolder = upperArgList.IndexOf("-FOLDER");
if (doSaveToFolder == -1)
{
doSaveToFolder = upperArgList.IndexOf("-F");
}
if (doSaveToFolder > -1)
{
if (upperArgList.Count <= doSaveToFolder)
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
saveToFolderOutputPath = argList[doSaveToFolder + 1];
if (doSaveToFolder + 2 < argList.Count && !argList[doSaveToFolder + 2].StartsWith("-"))
{
saveToFolderReplaceId = argList[doSaveToFolder + 2];
}
var directoryName = new FileInfo(saveToFolderOutputPath).Directory.FullName;
Directory.CreateDirectory(saveToFolderOutputPath);
}
string buildOutputPath = null;
string buildReplaceId = null;
var doSave = upperArgList.IndexOf("-BUILD");
if (doSave == -1)
{
doSave = upperArgList.IndexOf("-B");
}
if (doSave == -1)
{
doSave = upperArgList.IndexOf("-BIM");
}
if (doSave > -1)
{
if (upperArgList.Count <= doSave)
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
buildOutputPath = argList[doSave + 1];
if (doSave + 2 < argList.Count && !argList[doSave + 2].StartsWith("-"))
{
buildReplaceId = argList[doSave + 2];
}
var directoryName = new FileInfo(buildOutputPath).Directory.FullName;
Directory.CreateDirectory(directoryName);
}
if (doSaveToFolder > -1 && doSave > -1)
{
Error("-FOLDER and -BUILD arguments are mutually exclusive.\n");
OutputUsage();
return(true);
}
// Load model:
Console.WriteLine("Loading model...");
if (!string.IsNullOrEmpty(script))
{
Console.WriteLine("Executing script...");
System.CodeDom.Compiler.CompilerResults result;
Scripting.ScriptOutputForm.Reset(false);
var dyn = ScriptEngine.CompileScript(script, out result);
//nUnit.StartSuite("Script Compilation");
if (result.Errors.Count > 0)
{
Error("Script compilation errors:");
var errIndex = 0;
foreach (System.CodeDom.Compiler.CompilerError err in result.Errors)
{
errIndex++;
ErrorX(err.ErrorText, scriptFile, err.Line, err.Column, err.ErrorNumber);
//nUnit.Failure("Script Compilation", $"Compilation Error #{errIndex}", err.ErrorText, $"{scriptFile} line {err.Line}, column {err.Column}");
}
return(true);
}
try
{
h.BeginUpdate("script");
dyn.Invoke(h.Model, null);
h.EndUpdateAll();
}
catch (Exception ex)
{
Error("Script execution error: " + ex.Message);
return(true);
}
finally
{
h.Model.Database.CloseReader();
}
}
if (doCheckDs > -1)
{
Console.WriteLine("Checking source schema...");
ScriptHelper.SchemaCheck(h.Model);
}
if (!string.IsNullOrEmpty(buildOutputPath))
{
Console.WriteLine("Building Model.bim file...");
if (buildReplaceId != null)
{
h.Database.Name = buildReplaceId; h.Database.ID = buildReplaceId;
}
h.Save(buildOutputPath, SaveFormat.ModelSchemaOnly, SerializeOptions.Default);
}
else if (!string.IsNullOrEmpty(saveToFolderOutputPath))
{
Console.WriteLine("Saving Model.bim file to Folder Output Path ...");
if (buildReplaceId != null)
{
h.Database.Name = buildReplaceId; h.Database.ID = buildReplaceId;
}
//Note the last parameter, we use whatever SerializeOptions are already in the file
h.Save(saveToFolderOutputPath, SaveFormat.TabularEditorFolder, null, true);
}
var replaceMap = new Dictionary <string, string>();
var analyze = upperArgList.IndexOf("-ANALYZE");
if (analyze == -1)
{
analyze = upperArgList.IndexOf("-A");
}
if (analyze > -1)
{
var rulefile = analyze + 1 < argList.Count ? argList[analyze + 1] : "";
if (rulefile.StartsWith("-") || string.IsNullOrEmpty(rulefile))
{
rulefile = null;
}
Console.WriteLine("Running Best Practice Analyzer...");
Console.WriteLine("=================================");
var analyzer = new BPA.Analyzer();
analyzer.SetModel(h.Model, h.SourceType == ModelSourceType.Database ? null : FileSystemHelper.DirectoryFromPath(h.Source));
BPA.BestPracticeCollection suppliedRules = null;
if (!string.IsNullOrEmpty(rulefile))
{
if (File.Exists(rulefile))
{
try
{
suppliedRules = BPA.BestPracticeCollection.GetCollectionFromFile(Environment.CurrentDirectory, rulefile);
}
catch
{
Error("Invalid rulefile: {0}", rulefile);
return(true);
}
}
else
{
suppliedRules = BPA.BestPracticeCollection.GetCollectionFromUrl(rulefile);
if (suppliedRules.Count == 0)
{
Error("No rules defined in specified URL: {0}", rulefile);
return(true);
}
}
}
IEnumerable <BPA.AnalyzerResult> bpaResults;
if (suppliedRules == null)
{
bpaResults = analyzer.AnalyzeAll();
}
else
{
var effectiveRules = analyzer.GetEffectiveRules(false, false, true, true, suppliedRules);
bpaResults = analyzer.Analyze(effectiveRules);
}
bool none = true;
foreach (var res in bpaResults.Where(r => !r.Ignored))
{
if (res.InvalidCompatibilityLevel)
{
Console.WriteLine("Skipping rule '{0}' as it does not apply to Compatibility Level {1}.", res.RuleName, h.CompatibilityLevel);
}
else
if (res.RuleHasError)
{
none = false;
Error("Error on rule '{0}': {1}", res.RuleName, res.RuleError);
}
else
{
none = false;
if (res.Object != null)
{
var text = string.Format("{0} {1} violates rule \"{2}\"",
res.Object.GetTypeName(),
(res.Object as IDaxObject)?.DaxObjectFullName ?? res.ObjectName,
res.RuleName
);
if (res.Rule.Severity <= 1)
{
Console.WriteLine(text);
}
else if (res.Rule.Severity == 2)
{
Warning(text);
}
else if (res.Rule.Severity >= 3)
{
Error(text);
}
}
}
}
if (none)
{
Console.WriteLine("No objects in violation of Best Practices.");
}
Console.WriteLine("=================================");
}
var deploy = upperArgList.IndexOf("-DEPLOY");
if (deploy == -1)
{
deploy = upperArgList.IndexOf("-D");
}
if (deploy > -1)
{
var serverName = argList.Skip(deploy + 1).FirstOrDefault(); if (serverName == null || serverName.StartsWith("-"))
{
serverName = null;
}
var databaseID = argList.Skip(deploy + 2).FirstOrDefault(); if (databaseID != null && databaseID.StartsWith("-"))
{
databaseID = null;
}
// Perform direct save:
if (serverName == null)
{
var nextSwitch = upperArgList.Skip(deploy + 1).FirstOrDefault();
var deploySwitches = new[] { "-L", "-LOGIN", "-O", "-OVERWRITE", "-C", "-CONNECTIONS", "-P", "-PARTITIONS", "-R", "-ROLES", "-M", "-MEMBERS", "-X", "-XMLA" };
if (deploySwitches.Contains(nextSwitch))
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
Console.WriteLine("Saving model metadata back to source...");
if (h.SourceType == ModelSourceType.Database)
{
try
{
h.SaveDB();
Console.WriteLine("Model metadata saved.");
var deploymentResult = h.GetLastDeploymentResults();
foreach (var err in deploymentResult.Issues)
{
if (errorOnDaxErr)
{
Error(err);
}
else
{
Warning(err);
}
}
foreach (var err in deploymentResult.Warnings)
{
Warning(err);
}
foreach (var err in deploymentResult.Unprocessed)
{
if (warnOnUnprocessed)
{
Warning(err);
}
else
{
Console.WriteLine(err);
}
}
}
catch (Exception ex)
{
Error("Save failed: " + ex.Message);
}
}
else
{
try
{
h.Save(h.Source, h.SourceType == ModelSourceType.Folder ? SaveFormat.TabularEditorFolder : h.SourceType == ModelSourceType.Pbit ? SaveFormat.PowerBiTemplate : SaveFormat.ModelSchemaOnly, h.SerializeOptions, true);
Console.WriteLine("Model metadata saved.");
}
catch (Exception ex)
{
Error("Save failed: " + ex.Message);
}
}
return(true);
}
var conn = upperArgList.IndexOf("-CONNECTIONS");
if (conn == -1)
{
conn = upperArgList.IndexOf("-C");
}
if (conn > -1)
{
var replaces = argList.Skip(conn + 1).TakeWhile(s => s[0] != '-').ToList();
if (replaces.Count > 0 && replaces.Count % 2 == 0)
{
// Placeholder replacing:
for (var index = 0; index < replaces.Count; index = index + 2)
{
replaceMap.Add(replaces[index], replaces[index + 1]);
}
}
}
string userName = null;
string password = null;
var options = DeploymentOptions.StructureOnly;
var switches = args.Skip(deploy + 1).Where(arg => arg.StartsWith("-")).Select(arg => arg.ToUpper()).ToList();
if (string.IsNullOrEmpty(serverName) || string.IsNullOrEmpty(databaseID))
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
if (switches.Contains("-L") || switches.Contains("-LOGIN"))
{
var switchPos = upperArgList.IndexOf("-LOGIN"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-L");
}
userName = argList.Skip(switchPos + 1).FirstOrDefault(); if (userName != null && userName.StartsWith("-"))
{
userName = null;
}
password = argList.Skip(switchPos + 2).FirstOrDefault(); if (password != null && password.StartsWith("-"))
{
password = null;
}
if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password))
{
Error("Missing username or password.\n");
OutputUsage();
return(true);
}
switches.Remove("-L"); switches.Remove("-LOGIN");
}
if (switches.Contains("-O") || switches.Contains("-OVERWRITE"))
{
options.DeployMode = DeploymentMode.CreateOrAlter;
switches.Remove("-O"); switches.Remove("-OVERWRITE");
}
else
{
options.DeployMode = DeploymentMode.CreateDatabase;
}
if (switches.Contains("-P") || switches.Contains("-PARTITIONS"))
{
options.DeployPartitions = true;
switches.Remove("-P"); switches.Remove("-PARTITIONS");
}
if (switches.Contains("-C") || switches.Contains("-CONNECTIONS"))
{
options.DeployConnections = true;
switches.Remove("-C"); switches.Remove("-CONNECTIONS");
}
if (switches.Contains("-R") || switches.Contains("-ROLES"))
{
options.DeployRoles = true;
switches.Remove("-R"); switches.Remove("-ROLES");
if (switches.Contains("-M") || switches.Contains("-MEMBERS"))
{
options.DeployRoleMembers = true;
switches.Remove("-M"); switches.Remove("-MEMBERS");
}
}
var xmla_scripting_only = switches.Contains("-X") || switches.Contains("-XMLA");
string xmla_script_file = null;
if (xmla_scripting_only)
{
var switchPos = upperArgList.IndexOf("-XMLA"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-X");
}
xmla_script_file = argList.Skip(switchPos + 1).FirstOrDefault(); if (String.IsNullOrWhiteSpace(xmla_script_file) || xmla_script_file.StartsWith("-"))
{
xmla_script_file = null;
}
if (string.IsNullOrEmpty(xmla_script_file))
{
Error("Missing xmla_script_file.\n");
OutputUsage();
return(true);
}
switches.Remove("-X");
switches.Remove("-XMLA");
}
/*if(switches.Count > 0)
* {
* Error("Unknown switch {0}\n", switches[0]);
* OutputUsage();
* return true;
* }*/
try
{
if (replaceMap.Count > 0)
{
Console.WriteLine("Switching connection string placeholders...");
foreach (var map in replaceMap)
{
h.Model.DataSources.SetPlaceholder(map.Key, map.Value);
}
}
var cs = string.IsNullOrEmpty(userName) ? TabularConnection.GetConnectionString(serverName) :
TabularConnection.GetConnectionString(serverName, userName, password);
if (xmla_scripting_only)
{
Console.WriteLine("Generating XMLA/TMSL script...");
var s = new TOM.Server();
s.Connect(cs);
var xmla = TabularDeployer.GetTMSL(h.Database, s, databaseID, options);
using (var sw = new StreamWriter(xmla_script_file))
{
sw.Write(xmla);
}
Console.WriteLine("XMLA/TMSL script generated.");
}
else
{
Console.WriteLine("Deploying...");
h.Model.UpdateDeploymentMetadata(DeploymentModeMetadata.CLI);
var deploymentResult = TabularDeployer.Deploy(h, cs, databaseID, options);
Console.WriteLine("Deployment succeeded.");
foreach (var err in deploymentResult.Issues)
{
if (errorOnDaxErr)
{
Error(err);
}
else
{
Warning(err);
}
}
foreach (var err in deploymentResult.Warnings)
{
Warning(err);
}
foreach (var err in deploymentResult.Unprocessed)
{
if (warnOnUnprocessed)
{
Warning(err);
}
else
{
Console.WriteLine(err);
}
}
}
}
catch (Exception ex)
{
Error($"{(xmla_scripting_only ? "Script generation" : "Deployment")} failed! {ex.Message}");
}
}
if (Program.testRun != null)
{
Program.testRun.SerializeAsVSTest(testRunFile);
Console.WriteLine("VSTest XML file saved: " + testRunFile);
}
return(true);
}
private static void DynamicRoleCreation(string roleName,
string serverAddress, string databaseId, string filterExpression)
{
string ConnectionString = @"Provider=MSOLAP;Data Source=" + serverAddress +
@";Initial Catalog=" + databaseId + ";Integrated Security=SSPI;ImpersonationLevel = Impersonate; persist security info = True; ";
var RoleIDNamePair = new Dictionary <int, string>();
// Get Roles Where Rolename == 'NewlycreatedRoleName=@Param'
var GetRolesIDXMLA = @"<Batch xmlns
=""http://schemas.microsoft.com/analysisservices/2003/engine""
Transaction=""true""><Discover xmlns = ""urn:schemas-microsoft-com:xmlanalysis""><RequestType>TMSCHEMA_ROLES</RequestType><Restrictions/><Properties/></Discover
></Batch>";
// Take Param: @RoleName for use in XMLA to create role
var xmlaCreateRole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><Roles><xs:schema xmlns:xs=""http://www.w3.org/2001/XMLSchema""
xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""Name"" type=""xs:string"" sql:field=""Name""
minOccurs=""0""/><xs:element name=""Description"" type=""xs:string""
sql:field=""Description"" minOccurs=""0""/><xs:element name=""ModelPermission""
type=""xs:long"" sql:field=""ModelPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xmlanalysis:rowset""><Name>" + roleName + @"</Name><ModelPermission>2</ModelPermission></row></Ro
les></Create></Batch>";
// Get Role ID Where Rolename == @PRoleName: XMLA??
var objServer = new Microsoft.AnalysisServices.Tabular.Server();
objServer.Connect(ConnectionString);
objServer.Execute(xmlaCreateRole);
var reader = objServer.ExecuteReader(GetRolesIDXMLA, out XmlaResultCollection
resultsOut, null, true);
// Add all roles to Cached List
while (reader.Read())
{
RoleIDNamePair.Add(int.Parse(reader[0].ToString()), reader[2].ToString());
}
reader.Close();
reader.Dispose();
var RoleIDByRoleName = "";//= RoleIDNamePair.Single(s => s.Value == roleName).Key.ToString();
foreach (var pair in RoleIDNamePair)
{
if (pair.Value == roleName)
{
RoleIDByRoleName = pair.Key.ToString();
break;
}
}
var AddFilterTorole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><TablePermissions><xs:schema
xmlns:xs=""http://www.w3.org/2001/XMLSchema"" xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""RoleID"" type=""xs:unsignedLong""
sql:field=""RoleID"" minOccurs=""0""/><xs:element name=""RoleID.Role"" type=""xs:string""
sql:field=""RoleID.Role"" minOccurs=""0""/><xs:element name=""TableID""
type=""xs:unsignedLong"" sql:field=""TableID"" minOccurs=""0""/><xs:element
name=""TableID.Table"" type=""xs:string"" sql:field=""TableID.Table""
minOccurs=""0""/><xs:element name=""FilterExpression"" type=""xs:string""
sql:field=""FilterExpression"" minOccurs=""0""/><xs:element name=""MetadataPermission""
type=""xs:long"" sql:field=""MetadataPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xml-analysis:rowset""><RoleID>" + RoleIDByRoleName +
@"</RoleID><TableID>364</TableID><FilterExpression>" + filterExpression + @"</FilterExpression
></row></TablePermissions></Create><SequencePoint
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" +
databaseId + @"</DatabaseID></SequencePoint></Batch>";
var applyRoleQuery = AddFilterTorole;
objServer.Execute(applyRoleQuery);
}
