public static bool Save(Microsoft.AnalysisServices.Server server, OutputContext output, IMajorObject obj)
{
var builder = new StringBuilder();
using (var xmlWriter = XmlWriter.Create(builder, new XmlWriterSettings {
OmitXmlDeclaration = true
})) {
Scripter.WriteAlter(xmlWriter, obj, true, true);
xmlWriter.Flush();
}
var command = builder.ToString();
output.Debug(() => command);
var results = server.Execute(command);
if (results.Count > 0)
{
foreach (XmlaResult result in results)
{
if (result.Messages.Count > 0)
{
foreach (XmlaMessage message in result.Messages)
{
output.Error(message.Description);
}
return(false);
}
}
}
return(true);
}
private void ExecInternal(ProjectItem projItem, DataModelingSandbox sandbox)
{
#if DENALI || SQL2014
var db = sandbox.Database;
#else
var db = ((DataModelingSandboxAmo)sandbox.Impl).Database;
#endif
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
ApplicationObject.StatusBar.Progress(true, "Deploying Tabular Database", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying Tabular Database", 4, 5);
// execute the xmla
try
{
Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(
string.Format("A database called {0} could not be found on the {1} server",
deploySet.TargetDatabase, deploySet.TargetServer));
}
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
// TODO - do we need different code for JSON based models??
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { db }, xwrtr, true);
// update the MDX Script
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
{
// all OK - 1 result - no messages
}
else
{
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Tabular Database");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "BIDSHelper - Deploy Tabular Database - Exception");
package.Log.Exception("Deploy Tabular Database Failed", ex);
}
}
// Clears the cache for the current database
// This method uses Server.Execute() as I need to establish an AMO
// connection anyway in order to find the CubeID from the CubeName
// Other methods in this class use the XmlaClient objects to execute
// the Xmla requests.
public void ClearCache(string cubeName)
{
// only way to get a DatabaseID from a Database name appears to be to use AMO
string dbId = "";
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
using (svr)
{
svr.Connect(Context.CurrentServerID);
try
{
Database db = svr.Databases.FindByName(Context.CurrentDatabaseName);
dbId = db.ID;
if (cubeName.Length != 0)
{
Cube c = db.Cubes.FindByName(cubeName);
if (c != null)
{
cubeName = "<CubeID>" + c.ID + "</CubeID>";
}
else
{
throw new Exception("The cube '" + cubeName + "' does not exist in the " + Context.CurrentDatabaseName + " database.");
}
}
// execute clear cache based on the ID
string clearCmd = string.Format(CLEARCACHE_TEMPLATE, dbId, cubeName);
svr.Execute(clearCmd);
}
catch
{ // re-throw any exception
throw;
}
finally
{
// clean up
svr.Disconnect();
}
}
}
public static void DeployAggDesigns(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
bool bFoundAggDesign = false;
foreach (MeasureGroup mg in oCube.MeasureGroups)
{
if (mg.AggregationDesigns.Count > 0)
{
bFoundAggDesign = true;
break;
}
}
if (!bFoundAggDesign)
{
MessageBox.Show("There are no aggregation designs defined in this cube yet.");
return;
}
if (MessageBox.Show("This command deploys just the aggregation designs in this cube. It does not change which aggregation design is assigned to each partition.\r\n\r\nYou should run a ProcessIndex command from Management Studio on this cube after aggregation designs have been deployed.\r\n\r\nDo you wish to continue?", "BIDS Helper - Deploy Aggregation Designs", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 1, 5);
string sPartitionsFileName = projItem.get_FileNames(1);
sPartitionsFileName = sPartitionsFileName.Substring(0, sPartitionsFileName.Length - 5) + ".partitions";
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(sPartitionsFileName);
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
{
//TODO - prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployAggDesigns));
using ((xsltRdr))
{
// read content from .partitions file
xrdr = XmlReader.Create(sPartitionsFileName);
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 4, 5);
// execute the xmla
try
{
// Build up the Alter MdxScript command using XSLT against the .partitions file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
xslarg.AddParam("TargetDatabase", "", targetDB.ID);
xslarg.AddParam("TargetCubeID", "", oCube.ID);
xslta.Transform(xrdr, xslarg, xwrtr);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
// update the agg designs
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
if (!string.IsNullOrEmpty(xmlaRC[iRC].Messages[iMsg].Description))
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
}
if (sbErr.Length > 0)
{
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Aggregation Designs");
}
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the aggregation designs\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDSHelper - Deploy Aggregation Designs"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 5, 5);
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying Aggregation Designs", 5, 5);
}
}
public static void DeployAggDesigns(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
bool bFoundAggDesign = false;
foreach (MeasureGroup mg in oCube.MeasureGroups)
{
if (mg.AggregationDesigns.Count > 0)
{
bFoundAggDesign = true;
break;
}
}
if (!bFoundAggDesign)
{
MessageBox.Show("There are no aggregation designs defined in this cube yet.");
return;
}
if (MessageBox.Show("This command deploys just the aggregation designs in this cube. It does not change which aggregation design is assigned to each partition.\r\n\r\nYou should run a ProcessIndex command from Management Studio on this cube after aggregation designs have been deployed.\r\n\r\nDo you wish to continue?", "BIDS Helper - Deploy Aggregation Designs", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 1, 5);
string sPartitionsFileName = projItem.get_FileNames(1);
sPartitionsFileName = sPartitionsFileName.Substring(0, sPartitionsFileName.Length - 5) + ".partitions";
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(sPartitionsFileName);
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
{
//TODO - prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployAggDesigns));
using ((xsltRdr))
{
// read content from .partitions file
xrdr = XmlReader.Create(sPartitionsFileName);
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 4, 5);
// execute the xmla
try
{
// Build up the Alter MdxScript command using XSLT against the .partitions file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
xslarg.AddParam("TargetDatabase", "", targetDB.ID);
xslarg.AddParam("TargetCubeID", "", oCube.ID);
xslta.Transform(xrdr, xslarg, xwrtr);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
// update the agg designs
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
if (!string.IsNullOrEmpty(xmlaRC[iRC].Messages[iMsg].Description))
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
if (sbErr.Length > 0)
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Aggregation Designs");
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the aggregation designs\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDSHelper - Deploy Aggregation Designs"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 5, 5);
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying Aggregation Designs", 5, 5);
}
}
private void ExecInternal(ProjectItem projItem, DataModelingSandbox sandbox)
{
//sandbox.
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
ApplicationObject.StatusBar.Progress(true, "Deploying Tabular Database", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying Tabular Database", 4, 5);
// execute the xmla
try
{
Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(
string.Format("A database called {0} could not be found on the {1} server",
deploySet.TargetDatabase, deploySet.TargetServer));
}
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] {sandbox.Database}, xwrtr, true);
// update the MDX Script
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
{
// all OK - 1 result - no messages
}
else
{
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Tabular Database");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "BIDSHelper - Deploy Tabular Database - Exception");
}
}
public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
try
{
//validate the script because deploying an invalid script makes cube unusable
Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube);
}
catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex)
{
string throwaway = ex.Message;
MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script");
return;
}
if (oCube.MdxScripts.Count == 0)
{
MessageBox.Show("There is no MDX script defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5);
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly )
{
//TODO - can I check and maybe prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5);
// execute the xmla
try
{
Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();
// Build up the Alter MdxScript command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
// Extract the current script from the server and keep a temporary backup copy of it
StringBuilder sbBackup = new StringBuilder();
XmlWriterSettings xwSet = new XmlWriterSettings();
xwSet.ConformanceLevel = ConformanceLevel.Fragment;
xwSet.OmitXmlDeclaration = true;
xwSet.Indent = true;
XmlWriter xwScript = XmlWriter.Create(sbBackup,xwSet);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
else if (oServerCube.State == AnalysisState.Unprocessed)
{
throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer));
}
if (oServerCube.MdxScripts.Count == 0)
{
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true);
}
else
{
MdxScript mdxScr = oServerCube.MdxScripts[0];
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true);
}
xwScript.Close();
// update the MDX Script
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
{
// all OK - 1 result - no messages
}
else
{
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count;iRC ++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
MessageBox.Show(sbErr.ToString(),"BIDSHelper - Deploy MDX Script" );
}
// Test the MDX Script
AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase);
cn.Open();
AdomdCommand cmd = cn.CreateCommand();
string qry = "SELECT {} ON 0 FROM [" + oCube.Name +"];";
cmd.CommandText = qry;
try
{
// test that we can query the cube without errors
cmd.Execute();
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
// --
// This line is included in this try block so that it is only executed if we can
// successfully query the cube without errors.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
// undo the deployment if we caught an exception during the deployment
svr.Execute(sbBackup.ToString());
MessageBox.Show(ex.Message);
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
,"BIDSHelper - Deploy MDX Script"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
}
}
// Clears the cache for the current database
// This method uses Server.Execute() as I need to establish an AMO
// connection anyway in order to find the CubeID from the CubeName
// Other methods in this class use the XmlaClient objects to execute
// the Xmla requests.
public void ClearCache(string cubeName)
{
// only way to get a DatabaseID from a Database name appears to be to use AMO
string dbId = "";
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
using (svr)
{
svr.Connect(Context.CurrentServerID);
try
{
Database db = svr.Databases.FindByName(Context.CurrentDatabaseName);
dbId = db.ID;
if (cubeName.Length != 0)
{
Cube c = db.Cubes.FindByName(cubeName);
if (c != null)
{
cubeName = "<CubeID>" + c.ID + "</CubeID>";
}
else
{
throw new Exception("The cube '" + cubeName + "' does not exist in the " + Context.CurrentDatabaseName + " database.");
}
}
// execute clear cache based on the ID
string clearCmd = string.Format(CLEARCACHE_TEMPLATE, dbId, cubeName);
svr.Execute(clearCmd);
}
catch
{ // re-throw any exception
throw;
}
finally
{
// clean up
svr.Disconnect();
}
}
}
public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
try
{
//validate the script because deploying an invalid script makes cube unusable
Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube);
}
catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex)
{
string throwaway = ex.Message;
MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script");
return;
}
if (oCube.MdxScripts.Count == 0)
{
MessageBox.Show("There is no MDX script defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5);
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
{
//TODO - can I check and maybe prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5);
// execute the xmla
try
{
Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();
// Build up the Alter MdxScript command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
// Extract the current script from the server and keep a temporary backup copy of it
StringBuilder sbBackup = new StringBuilder();
XmlWriterSettings xwSet = new XmlWriterSettings();
xwSet.ConformanceLevel = ConformanceLevel.Fragment;
xwSet.OmitXmlDeclaration = true;
xwSet.Indent = true;
XmlWriter xwScript = XmlWriter.Create(sbBackup, xwSet);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
else if (oServerCube.State == AnalysisState.Unprocessed)
{
throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer));
}
if (oServerCube.MdxScripts.Count == 0)
{
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true);
}
else
{
MdxScript mdxScr = oServerCube.MdxScripts[0];
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true);
}
xwScript.Close();
// update the MDX Script
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
{
// all OK - 1 result - no messages
}
else
{
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy MDX Script");
}
// Test the MDX Script
AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase);
cn.Open();
AdomdCommand cmd = cn.CreateCommand();
string qry = "SELECT {} ON 0 FROM [" + oCube.Name + "];";
cmd.CommandText = qry;
try
{
// test that we can query the cube without errors
cmd.Execute();
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
// --
// This line is included in this try block so that it is only executed if we can
// successfully query the cube without errors.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
// undo the deployment if we caught an exception during the deployment
svr.Execute(sbBackup.ToString());
MessageBox.Show(ex.Message);
}
finally
{
cmd.Dispose();
cn.Close();
cn.Dispose();
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDSHelper - Deploy MDX Script"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
svr.Dispose();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
}
}
public static void DeployPerspectives(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
if (oCube.Perspectives.Count == 0)
{
MessageBox.Show("There are no perspectives defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 1, 5);
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
{
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployPerspectives));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 4, 5);
// execute the xmla
try
{
// Build up the Alter perspectives command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
//drop any perspectives which don't exist
svr.CaptureXml = true;
for (int i = 0; i < oServerCube.Perspectives.Count; i++)
{
Perspective p = oServerCube.Perspectives[i];
if (!oCube.Perspectives.Contains(p.ID))
{
p.Drop();
i--;
}
}
svr.CaptureXml = false;
try
{
if (svr.CaptureLog.Count > 0)
{
svr.ExecuteCaptureLog(true, false);
}
}
catch (System.Exception ex)
{
throw new System.Exception("Error dropping perspective that were deleted in the source code. " + ex.Message);
}
// update the perspectives
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
if (sbErr.Length > 0)
{
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Perspectives");
}
try
{
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the perspectives\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDS Helper - Deploy Perspectives"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying perspectives", 5, 5);
}
}
/// <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);
}
public static void DeployPerspectives(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
if (oCube.Perspectives.Count == 0)
{
MessageBox.Show("There are no perspectives defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 1, 5);
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly )
{
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployPerspectives));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 4, 5);
// execute the xmla
try
{
// Build up the Alter perspectives command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
//drop any perspectives which don't exist
svr.CaptureXml = true;
for (int i = 0; i < oServerCube.Perspectives.Count; i++)
{
Perspective p = oServerCube.Perspectives[i];
if (!oCube.Perspectives.Contains(p.ID))
{
p.Drop();
i--;
}
}
svr.CaptureXml = false;
try
{
if (svr.CaptureLog.Count > 0)
svr.ExecuteCaptureLog(true, false);
}
catch (System.Exception ex)
{
throw new System.Exception("Error dropping perspective that were deleted in the source code. " + ex.Message);
}
// update the perspectives
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
if (sbErr.Length > 0)
{
MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Perspectives");
}
try
{
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the perspectives\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
, "BIDS Helper - Deploy Perspectives"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying perspectives", 5, 5);
}
}