public void RestoreCube(string connectionString, string abfFilePath)
{
if (string.IsNullOrEmpty(abfFilePath))
{
FailedToRestore($"abfFilePath is null or empty.");
//throw new ArgumentNullException($"abfFilePath is null or empty.");
}
if (string.IsNullOrEmpty(connectionString))
{
FailedToRestore($"connectionString is null or empty.");
//throw new ArgumentNullException($"connectionString is null or empty.");
}
try
{
if (ConnectSSASServer(connectionString))
{
string SSASDBName = Path.GetFileNameWithoutExtension(abfFilePath);
ssasServer.Restore(abfFilePath, SSASDBName, true);
ssasServer.Restore(abfFilePath, SSASDBName, true);
ssasServer.Refresh();
AMO.Database ssasDataBase = ssasServer.Databases[SSASDBName];
ssasDataBase.Cubes[0].Update();
ssasDataBase.Cubes[0].Refresh();
RestoredSuccessfully();
}
else
{
FailedToRestore($"Failed to connect to the server.");
}
}
catch (NullReferenceException nex)
{
FailedToRestore(nex.Message);
//throw new Exception(nex.Message);
}
catch (Exception ex)
{
FailedToRestore(ex.Message);
//throw new Exception(ex.Message);
}
finally
{
DisConnectSSASServer();
}
}
/// <summary>
/// This method ensures the tabular model is online and populates the CompatibilityLevel property.
/// </summary>
/// <param name="closedBimFile">A Boolean specifying if the user cancelled the comparison. For the case where running in Visual Studio, the user has the option of cancelling if the project BIM file is open.</param>
public void InitializeCompatibilityLevel(bool closedBimFile = false)
{
if (UseBimFile)
{
TOM.Database tomDatabase = null;
bool exceptionLoadingFile = false;
try
{
tomDatabase = OpenDatabaseFromFile();
}
catch
{
exceptionLoadingFile = true;
}
if (exceptionLoadingFile || tomDatabase == null)
{
throw new ConnectionException($"Can't load file \"{_bimFile}\".");
}
_compatibilityLevel = tomDatabase.CompatibilityLevel;
_dataSourceVersion = tomDatabase.Model.DefaultPowerBIDataSourceVersion.ToString();
_directQuery = (tomDatabase.Model != null && tomDatabase.Model.DefaultMode == Microsoft.AnalysisServices.Tabular.ModeType.DirectQuery);
return;
}
if (UseProject)
{
//Initialize _projectDirectoryInfo
FileInfo projectFileInfo;
if (_project == null)
{
//Probably running in command-line mode
projectFileInfo = new FileInfo(_projectFile);
}
else
{
projectFileInfo = new FileInfo(_project.FullName);
}
_projectDirectoryInfo = new DirectoryInfo(projectFileInfo.Directory.FullName);
//Read settings file to get workspace server/db
ReadSettingsFile();
//Read project file to get deployment server/cube names, and bim file
ReadProjectFile();
//Overwrite the server if a workspace server provided
if (_workspaceServerProvided)
{
this.ServerName = _workspaceServer;
}
}
Microsoft.AnalysisServices.Server amoServer = new Microsoft.AnalysisServices.Server();
try
{
amoServer.Connect(BuildConnectionString());
}
catch (ConnectionException) when(UseProject)
{
//See if can find integrated workspace server
bool foundServer = false;
string tempDataDir = Path.GetTempPath() + @"Microsoft\Microsoft SQL Server\OLAP\LocalServer\Data";
if (Directory.Exists(tempDataDir))
{
var subDirs = Directory.GetDirectories(tempDataDir).OrderByDescending(d => new DirectoryInfo(d).CreationTime); //Need to order by descending in case old folders hanging around when VS was killed and SSDT didn't get a chance to clean up after itself
foreach (string subDir in subDirs)
{
string[] iniFilePath = Directory.GetFiles(subDir, "msmdsrv.ini");
if (iniFilePath.Length == 1 && File.ReadAllText(iniFilePath[0]).Contains("<DataDir>" + _projectDirectoryInfo.FullName + @"\bin\Data</DataDir>")) //Todo: proper xml lookup
{
//Assuming this must be the folder, so now get the port number
string[] portFilePath = Directory.GetFiles(subDir, "msmdsrv.port.txt");
if (portFilePath.Length == 1)
{
string port = File.ReadAllText(portFilePath[0]).Replace("\0", "");
this.ServerName = $"localhost:{Convert.ToString(port)}";
amoServer.Connect(BuildConnectionString());
foundServer = true;
break;
}
}
}
}
if (!foundServer)
{
throw;
}
}
////non-admins can't see any ServerProperties: social.msdn.microsoft.com/Forums/sqlserver/en-US/3d0bf49c-9034-4416-9c51-77dc32bf8b73/determine-current-user-permissionscapabilities-via-amo-or-xmla
//if (!(amoServer.ServerProperties.Count > 0)) //non-admins can't see any ServerProperties
//{
// throw new Microsoft.AnalysisServices.ConnectionException($"Current user {WindowsIdentity.GetCurrent().Name} is not an administrator on the Analysis Server " + this.ServerName);
//}
if (amoServer.ServerMode != ServerMode.Tabular && amoServer.ServerMode != ServerMode.SharePoint) //SharePoint is what Power BI Desktop runs as
{
throw new ConnectionException($"Analysis Server {this.ServerName} is not running in Tabular mode");
}
Microsoft.AnalysisServices.Database amoDatabase = null;
if (this.DatabaseName == "" && this.ServerName.ToUpper().StartsWith("localhost:".ToUpper()))
{
//PBI Desktop doesn't have db name yet
if (amoServer.Databases.Count > 0)
{
amoDatabase = amoServer.Databases[0];
this.DatabaseName = amoDatabase.Name;
}
}
else
{
amoDatabase = amoServer.Databases.FindByName(this.DatabaseName);
}
if (amoDatabase == null)
{
if (!this.UseProject)
{
throw new ConnectionException("Could not connect to database " + this.DatabaseName);
}
else
{
/* Check if folder exists using SystemGetSubdirs. If so attach. If not, do nothing - when execute BIM file below will create automatically.
* Using XMLA to run SystemGetSubdirs rather than ADOMD.net here don't want a reference to ADOMD.net Dll.
* Also, can't use Server.Execute method because it only takes XMLA execute commands (as opposed to XMLA discover commands), so need to submit the full soap envelope
*/
string dataDir = amoServer.ServerProperties["DataDir"].Value;
if (dataDir.EndsWith("\\"))
{
dataDir = dataDir.Substring(0, dataDir.Length - 1);
}
string commandStatement = String.Format("SystemGetSubdirs '{0}'", dataDir);
bool foundFault = false;
XmlNodeList rows = Core.Comparison.ExecuteXmlaCommand(amoServer, "", commandStatement, ref foundFault);
string dbDir = "";
foreach (XmlNode row in rows)
{
XmlNode dirNode = null;
XmlNode allowedNode = null;
foreach (XmlNode childNode in row.ChildNodes)
{
if (childNode.Name == "Dir")
{
dirNode = childNode;
}
else if (childNode.Name == "Allowed")
{
allowedNode = childNode;
}
}
if (dirNode != null && allowedNode != null && dirNode.InnerText.Length >= this.DatabaseName.Length && dirNode.InnerText.Substring(0, this.DatabaseName.Length) == this.DatabaseName && allowedNode.InnerText.Length > 0 && allowedNode.InnerText == "1")
{
dbDir = dataDir + "\\" + dirNode.InnerText;
break;
}
}
if (dbDir != "")
{
//attach
amoServer.Attach(dbDir);
amoServer.Refresh();
amoDatabase = amoServer.Databases.FindByName(this.DatabaseName);
}
}
}
if (this.UseProject)
{
//_bimFileFullName = GetBimFileFullName();
if (String.IsNullOrEmpty(_ssdtBimFile))
{
throw new ConnectionException("Could not load BIM file for Project " + this.ProjectName);
}
if (!closedBimFile) //If just closed BIM file, no need to execute it
{
//Execute BIM file contents as script on workspace database
//We don't know the compatibility level yet, so try parsing json, if fail, try xmla ...
try
{
//Replace "SemanticModel" with db name.
JObject jDocument = JObject.Parse(File.ReadAllText(_ssdtBimFile));
if (jDocument["name"] == null || jDocument["id"] == null)
{
throw new ConnectionException("Could not read JSON in BIM file " + _ssdtBimFile);
}
jDocument["name"] = DatabaseName;
jDocument["id"] = DatabaseName;
//Todo: see if Tabular helper classes for this once documentation available after CTP
string command =
$@"{{
""createOrReplace"": {{
""object"": {{
""database"": ""{DatabaseName}""
}},
""database"": {jDocument.ToString()}
}}
}}
";
amoServer.Execute(command);
}
catch (JsonReaderException)
{
//Replace "SemanticModel" with db name. Could do a global replace, but just in case it's not called SemanticModel, use dom instead
//string xmlaScript = File.ReadAllText(xmlaFileFullName);
XmlDocument document = new XmlDocument();
document.Load(_ssdtBimFile);
XmlNamespaceManager nsmgr = new XmlNamespaceManager(document.NameTable);
nsmgr.AddNamespace("myns1", "http://schemas.microsoft.com/analysisservices/2003/engine");
XmlNode objectDatabaseIdNode = document.SelectSingleNode("//myns1:Object/myns1:DatabaseID", nsmgr);
XmlNode objectDefinitionDatabaseIdNode = document.SelectSingleNode("//myns1:ObjectDefinition/myns1:Database/myns1:ID", nsmgr);
XmlNode objectDefinitionDatabaseNameNode = document.SelectSingleNode("//myns1:ObjectDefinition/myns1:Database/myns1:Name", nsmgr);
if (objectDatabaseIdNode == null || objectDefinitionDatabaseIdNode == null || objectDefinitionDatabaseNameNode == null)
{
throw new ConnectionException("Could not access XMLA in BIM file " + _ssdtBimFile);
}
objectDatabaseIdNode.InnerText = DatabaseName;
objectDefinitionDatabaseIdNode.InnerText = DatabaseName;
objectDefinitionDatabaseNameNode.InnerText = DatabaseName;
//1103, 1100 projects store the xmla as Alter (equivalent to createOrReplace), so just need to execute
amoServer.Execute(document.OuterXml);
}
}
//need next lines in case just created the db using the Execute method
//amoServer.Refresh(); //todo workaround for bug 9719887 on 3/10/17 need to disconnect and reconnect
amoServer.Disconnect();
amoServer.Connect(BuildConnectionString());
amoDatabase = amoServer.Databases.FindByName(this.DatabaseName);
}
if (amoDatabase == null)
{
throw new ConnectionException($"Can not load/find database {this.DatabaseName}.");
}
_compatibilityLevel = amoDatabase.CompatibilityLevel;
if (_compatibilityLevel >= 1400)
{
_dataSourceVersion = amoDatabase.Model.DefaultPowerBIDataSourceVersion.ToString();
}
_serverMode = amoServer.ServerMode;
_directQuery = ((amoDatabase.Model != null && amoDatabase.Model.DefaultMode == Microsoft.AnalysisServices.Tabular.ModeType.DirectQuery) ||
amoDatabase.DirectQueryMode == DirectQueryMode.DirectQuery || amoDatabase.DirectQueryMode == DirectQueryMode.InMemoryWithDirectQuery || amoDatabase.DirectQueryMode == DirectQueryMode.DirectQueryWithInMemory);
}