public static DateTime GetCubeLastSchemaUpdateDate()
{
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName = GetCurrentCubeName();
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try {
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
dtTemp = cube.LastSchemaUpdate;
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start();
while (!td.Join(1000))
{
Context.CheckCancelled();
}
if (exDelegate != null) throw exDelegate;
return dtTemp;
}
public static Tuple <Version, string, string> GetServerInfo()
{
Version version;
string edition;
string config;
using (var server = new Microsoft.AnalysisServices.Server())
{
server.Connect("*");
version = Version.Parse(server.Version);
edition = server.Edition.ToString();
}
Func <string> read = () =>
{
var path = Environment.GetCommandLineArgs()
.SkipWhile((a) => !"-s".Equals(a, StringComparison.OrdinalIgnoreCase))
.Skip(1)
.Take(1)
.FirstOrDefault();
if (path == null)
{
throw new FileNotFoundException("Unable to retrieve path from CommandLine", "msmdsrv.ini");
}
return(File.ReadAllText(Path.Combine(path, "msmdsrv.ini")));
};
using (var task = Task.Factory.StartNew(read))
config = task.Result;
return(Tuple.Create(version, edition, config));
}
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);
}
public SSASInitializer(InputContext input, OutputContext output, IConnectionFactory connectionFactory)
{
_input = input;
_output = output;
_server = new Microsoft.AnalysisServices.Server();
_connectionFactory = connectionFactory;
}
private static DataTable GetClusterCharacteristics(string strModel, string strClusterUniqueID, double dThreshold)
{
//if we don't know the path to the system data mining sprocs assembly, get it
if (_cachedSystemDataMiningSprocsPath.Length == 0)
{
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(Context.CurrentServerID);
ClrAssembly ass = (ClrAssembly)svr.Assemblies.GetByName("System");
if (ass == null)
{
throw new Exception("System (data mining sprocs) assembly not found");
}
foreach (ClrAssemblyFile file in ass.Files)
{
if (file.Type == ClrAssemblyFileType.Main)
{
lock (_cachedSystemDataMiningSprocsPath) _cachedSystemDataMiningSprocsPath = file.Name;
break;
}
}
svr.Disconnect();
}
//get the DataMining sprocs assembly and call the GetClusterCharacteristics function
System.Reflection.Assembly asAss = System.Reflection.Assembly.LoadFile(_cachedSystemDataMiningSprocsPath);
Type t = asAss.GetType("Microsoft.AnalysisServices.System.DataMining.Clustering");
object oClustering = t.GetConstructor(new Type[] { }).Invoke(new object[] { });
return((DataTable)t.InvokeMember("GetClusterCharacteristics", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod, null, oClustering, new object[] { strModel, strClusterUniqueID, dThreshold }));
}
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);
}
}
public void Open()
{
if (!IsConnectionOpen)
{
Server = new AS.Server();
Server.Connect(ConnectionString.GetConnectionWithoutCatalog().Value);
}
}
public DataTable ForEachMeasureGroupInternal(string command, bool forEachPartition)
{
DataTable result = new DataTable();
Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
server.Connect("*");
Database db = server.Databases.GetByName(Context.CurrentDatabaseName);
foreach (Microsoft.AnalysisServices.Cube c in db.Cubes)
{
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + server.Name + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Cube=" + c.Name);
foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
{
if (forEachPartition)
{
foreach (Microsoft.AnalysisServices.Partition p in mg.Partitions)
{
//parameters don't appear to work with some DMV queries, so use string substitution
string sNewCommand = command;
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_NAME", p.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_ID", p.ID);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
cmd.CommandTimeout = 0;
Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, result);
}
}
else
{
//parameters don't appear to work with some DMV queries, so use string substitution
string sNewCommand = command;
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
cmd.CommandTimeout = 0;
Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, result);
}
}
conn.Close();
}
return(result);
}
private static Microsoft.AnalysisServices.Database GetAmoDatabase(ADOTabularConnection cnn)
{
var serverName = cnn.ServerName;
var server = new Microsoft.AnalysisServices.Server();
server.Connect(serverName);
var db = server.Databases.GetByName(cnn.Database.Name);
return(db);
}
private Server ConnectAnalysisServices(string strDBServerName, string strProviderName)
{
try
{
AppendLogLine("");
AppendLogLine(string.Format("Connecting to the Analysis Services: {0}...", strDBServerName));
Server objServer = new Server();
string strConnection = string.Empty;
if (CbIntegratedSecurity)
{
//Direct Connection
strConnection = string.Format("Data Source={0};Integrated Security=SSPI;Provider={1}", strDBServerName, strProviderName);
}
else
{
//HTTP connection
strConnection = strConnection +
string.Format(@"Data Source=http://{0}/;Provider={1};User Id={2};Password={3}",
strDBServerName, strProviderName, CbUserId, CbPassword);
}
AppendLogLine(string.Format("Analysis services connection string: {0}", strConnection));
//Disconnect from current connection if it's currently connected.
if (objServer.Connected)
{
objServer.Disconnect();
}
else
{
objServer.Connect(strConnection);
}
AppendLogLine(string.Format(" Product name : {0}", objServer.ProductName));
AppendLogLine(string.Format(" Product edition : {0}", objServer.Edition));
AppendLogLine(string.Format(" Version : {0}", objServer.Version));
foreach (Database aAnalysisDatabase in objServer.Databases)
{
AppendLogLine(string.Format(" Database : [{0}] with cubes:", aAnalysisDatabase.Name));
foreach (Cube aCube in aAnalysisDatabase.Cubes)
{
AppendLogLine(string.Format(" Cube : [{0}]", aCube.Name));
}
}
AppendLogLine("");
return(objServer);
}
catch (Exception ex)
{
AppendLogLine("Error in Connecting to the Analysis Services. Error Message -> " + ex.Message);
return(null);
}
}
/// <summary>
/// Create SSAS Cube Server Connection
/// </summary>
/// <param name="cubeServerName"></param>
/// <returns></returns>
public static Microsoft.AnalysisServices.Server CREATE_OLAP_CONNECTION(String cubeServerName, String cubeName = null)
{
String ConnecteString = String.Format("Data Source ={0}; Provider=msolap", cubeServerName);
if (cubeName != null)
{
ConnecteString = String.Format("Data Source ={0};;Initial Catalog={1}; Connect Timeout=600;Provider=msolap", cubeServerName, cubeName);
}
Microsoft.AnalysisServices.Server asServer = new Microsoft.AnalysisServices.Server();
asServer.Connect(ConnecteString);
return(asServer);
}
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);
}
}
private void DisConnectSSASServer()
{
if (ssasServer != null)
{
if (ssasServer.GetConnectionState(true) == System.Data.ConnectionState.Open)
{
ssasServer.Disconnect(true);
}
ssasServer.Dispose();
}
ssasServer = null;
}
public DataTable ListFunctions()
{
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(Context.CurrentServerID);
try
{
return(getFunctionList(svr.Assemblies));
}
finally
{
svr.Disconnect();
}
}
public void TableCount1103()
{
using (Amo.Server server = new Amo.Server())
{
server.Connect("localhost\\tb");
Amo.Database db = server.Databases.FindByName("Test1103_Target");
Assert.IsNotNull(db);
Assert.AreEqual(3, db.Cubes[0].Dimensions.Count);
server.Disconnect();
}
}
/// <summary>
/// Performs the action of this task.
/// </summary>
protected override void InternalExecute()
{
this.server = new AMO.Server();
StringBuilder con = new StringBuilder(string.Format(CultureInfo.CurrentCulture, "Data Source={0};", this.MachineName));
if (string.IsNullOrEmpty(this.UserName))
{
this.LogTaskMessage(MessageImportance.Low, "Using a Trusted Connection");
con.Append("Integrated Security=SSPI;");
}
else
{
con.AppendFormat("UserName={0}", this.UserName);
}
if (!string.IsNullOrEmpty(this.UserPassword))
{
con.AppendFormat("Password={0}", this.UserPassword);
}
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Connect to server: {0}", this.MachineName));
this.server.Connect(con.ToString());
switch (this.TaskAction)
{
case ScriptCreateTaskAction:
this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptCreate(objects, xmlWriter, dependantObjects));
break;
case ScriptAlterTaskAction:
this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptAlter(objects, xmlWriter, dependantObjects));
break;
case ScriptDeleteTaskAction:
this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptDelete(objects, xmlWriter, dependantObjects));
break;
case ProcessTaskAction:
this.Process();
break;
case ExecuteTaskAction:
this.ExecuteScript();
break;
default:
this.Log.LogError(string.Format(CultureInfo.CurrentCulture, "Invalid TaskAction passed: {0}", this.TaskAction));
return;
}
this.server.Disconnect();
}
static void ProcessDimensions()
{
string srv_name = @"SCRBSQLDEFRM637";
string db_name = "Intermodal_TEST";
//TextWriter tw = new StreamWriter("date.txt", true);
Microsoft.AnalysisServices.Server srv;
Microsoft.AnalysisServices.Database db;
srv = new Microsoft.AnalysisServices.Server();
try
{
srv.Connect(srv_name);
logMessageFmt("Databases on [{0}]: {1}", srv_name, srv.Databases.Count);
db = srv.Databases.FindByName(db_name);
foreach (Dimension d in db.Dimensions)
{
logMessageFmt("Processing {0}(ID=[{1}])...", d.Name, d.ID);
try
{
if (d.State != AnalysisState.Processed)
{
d.Process(ProcessType.ProcessFull);
logMessage("---> Processed successfully.");
}
else
{
logMessage("---> Already processed.");
}
}
catch (Exception e)
{
logMessageFmt("---> ERROR: {0}", e.Message);
}
}
logMessage("Done.");
Console.ReadKey();
}
finally
{
if (srv.Connected == true)
{
srv.Disconnect();
}
}
}
public bool ConnectToAnalysisServer()
{
_CbServer = new Server();
_CbDatabase = new Database();
_CbDataSource = new RelationalDataSource();
_CbDataSourceView = new DataSourceView();
_CbDataSet = new DataSet();
//Connecting to the Analysis Services.
_CbServer = (Server)ConnectAnalysisServices(CbServerName, CbProviderName);
if (_CbServer != null)
{
LoadFromDsDwMap();
}
return(_CbServer != null);
}
/*---------------------------------
* Description: Update data for Analysis DB and reprocess it
* ----------------------------------- */
public static void UpdateTrainDB(Microsoft.AnalysisServices.Server svr, string sStockCode, bool bAll, DateTime dtFrom, DateTime dtTo, bool bMulti)
{
Database db = svr.Databases.GetByName("Stock");
CreateDataAccessObjects(db, sStockCode, bAll, dtFrom, dtTo, bMulti, false);
Microsoft.AnalysisServices.MiningModel mm = db.MiningStructures.GetByName(sStockCode).MiningModels.GetByName(sStockCode);
// Max, Min Time Series
mm.AlgorithmParameters.Remove("MAXIMUM_SERIES_VALUE");
mm.AlgorithmParameters.Remove("MINIMUM_SERIES_VALUE");
mm.AlgorithmParameters.Add("MAXIMUM_SERIES_VALUE", MAXIMUM_SERIES_VALUE);
mm.AlgorithmParameters.Add("MINIMUM_SERIES_VALUE", MINIMUM_SERIES_VALUE);
mm.Update();
//db.MiningStructures.GetByName(sStockCode).Process(ProcessType.ProcessFull);
//mm.Process(ProcessType.ProcessFull);
db.Process(ProcessType.ProcessFull);
}
/// <summary>
/// Performs the action of this task.
/// </summary>
protected override void InternalExecute()
{
this.server = new AMO.Server();
StringBuilder con = new StringBuilder(string.Format(CultureInfo.CurrentCulture, "Data Source={0};", this.MachineName));
if (string.IsNullOrEmpty(this.UserName))
{
this.LogTaskMessage(MessageImportance.Low, "Using a Trusted Connection");
con.Append("Integrated Security=SSPI;");
}
else
{
con.AppendFormat("UserName={0}", this.UserName);
}
if (!string.IsNullOrEmpty(this.UserPassword))
{
con.AppendFormat("Password={0}", this.UserPassword);
}
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Connect to server: {0}", this.MachineName));
this.server.Connect(con.ToString());
switch (this.TaskAction)
{
case ScriptCreateTaskAction:
this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptCreate(objects, xmlWriter, dependantObjects));
break;
case ScriptAlterTaskAction:
this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptAlter(objects, xmlWriter, dependantObjects));
break;
case ScriptDeleteTaskAction:
this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptDelete(objects, xmlWriter, dependantObjects));
break;
case ProcessTaskAction:
this.Process();
break;
case ExecuteTaskAction:
this.ExecuteScript();
break;
default:
this.Log.LogError(string.Format(CultureInfo.CurrentCulture, "Invalid TaskAction passed: {0}", this.TaskAction));
return;
}
this.server.Disconnect();
}
public static DateTime GetPartitionLastProcessedDate(string measureGroupName, string partitionName)
{
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
if (string.IsNullOrEmpty(measureGroupName) || string.IsNullOrEmpty(partitionName))
return dtTemp;
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName = GetCurrentCubeName();
string sMeasureGroupName = measureGroupName;
string sPartitionName = partitionName;
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try
{
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
MeasureGroup measuregroup = cube.MeasureGroups.FindByName(sMeasureGroupName);
Partition partition = measuregroup.Partitions.FindByName(sPartitionName);
dtTemp = partition.LastProcessed;
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start();
while (!td.Join(1000))
{
Context.CheckCancelled();
}
if (exDelegate != null) throw exDelegate;
return dtTemp;
}
private static void OpenAMOServerConnection(object o)
{
ConnectAMOServerInfo info = null;
try
{
info = (ConnectAMOServerInfo)o;
Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
s.Connect(info.ConnectionString);
info.Server = s;
}
catch (Exception ex)
{
info.ex = ex;
}
finally
{
info.autoEvent.Set();
}
}
// 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 DataTable ListFunctions(string databaseName)
{
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(Context.CurrentServerID);
Microsoft.AnalysisServices.Database db = svr.Databases.GetByName(databaseName);
if (db == null)
{
throw new Exception(string.Format("Unable to find a database called '{0}'", databaseName));
}
else
{
try
{
return(getFunctionList(db.Assemblies));
}
finally
{
svr.Disconnect();
}
}
}
private bool ConnectSSASServer(string connectionString)
{
try
{
if (ssasServer == null)
{
ssasServer = new AMO.Server();
}
if (ssasServer.GetConnectionState(true) != System.Data.ConnectionState.Open)
{
ssasServer.Connect(connectionString);
}
return(true);
}
catch
{
return(false);
}
}
public static void Main1()
{
serverAnalysis = new Microsoft.AnalysisServices.Server();
try
{
serverAnalysis.Connect("Data Source = " + strServerName);
olap = new Olap();
analysis.Database db = serverAnalysis.Databases.FindByName(strDataBaseName);
if (db != null)
{
db.Drop();
}
else
{
db = serverAnalysis.Databases.Add(strDataBaseName);
db.Update();
olap.CreateDataSource(db, strName, strConnectionString);
olap.CreateDataSourceView(db, strName, strName);
olap.CreateGeographyDimension(db, strName);
olap.CreateCustomerDimension(db, strName);
olap.CreateCube(db, strName);
}
}
catch (analysis.AmoException ex)
{
Console.WriteLine(ex.Message);
}
}
// 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 DateTime GetCubeLastProcessedDate()
{
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName = AMOHelpers.GetCurrentCubeName();
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try
{
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
dtTemp = cube.LastProcessed;
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start(); //run the delegate code
while (!td.Join(1000)) //wait for up to a second for the delegate to finish
{
Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
}
if (exDelegate != null)
{
throw exDelegate;
}
return(dtTemp);
//return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
}
public static void Main1()
{
serverAnalysis = new Microsoft.AnalysisServices.Server();
try
{
serverAnalysis.Connect("Data Source = " + strServerName);
olap = new Olap();
analysis.Database db = serverAnalysis.Databases.FindByName(strDataBaseName);
if (db != null)
{
db.Drop();
}
else
{
db = serverAnalysis.Databases.Add(strDataBaseName);
db.Update();
olap.CreateDataSource(db, strName, strConnectionString);
olap.CreateDataSourceView(db, strName, strName);
olap.CreateGeographyDimension(db, strName);
olap.CreateCustomerDimension(db, strName);
olap.CreateCube(db, strName);
}
}
catch (analysis.AmoException ex)
{
Console.WriteLine(ex.Message);
}
}
public static void AssignValue(string subCube, MDXValue valueToAssign)
{
Microsoft.AnalysisServices.Server mServer;
Database mDB;
Cube mCube;
MdxScript mMdxScript;
mServer = new Microsoft.AnalysisServices.Server();
try
{
mServer.Connect("*");
mDB = mServer.Databases.GetByName(Context.CurrentDatabaseName);
mCube = mDB.Cubes.GetByName(Context.CurrentCube.Name);
mMdxScript = mCube.DefaultMdxScript;
mMdxScript.Commands.Add(new Command(scriptComment() + subCube + " = " + valueToAssign.ToString() + ";" + System.Environment.NewLine));
mMdxScript.Update();
}
finally
{
mServer.Disconnect();
}
}
public static DateTime GetCubeLastProcessedDate()
{
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName = AMOHelpers.GetCurrentCubeName();
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try
{
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
dtTemp = cube.LastProcessed;
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start(); //run the delegate code
while (!td.Join(1000)) //wait for up to a second for the delegate to finish
{
Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
}
if (exDelegate != null) throw exDelegate;
return dtTemp;
//return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
}
public static void AssignMDXExpression(string subCube, string expressionToAssign)
{
Microsoft.AnalysisServices.Server mServer;
Database mDB;
Cube mCube;
MdxScript mMdxScript;
mServer = new Microsoft.AnalysisServices.Server();
try
{
mServer.Connect("*");
mDB = mServer.Databases.GetByName(Context.CurrentDatabaseName);
mCube = mDB.Cubes.GetByName(Context.CurrentCube.Name);
mMdxScript = mCube.DefaultMdxScript;
mMdxScript.Commands.Add(new Command(scriptComment() + subCube + " = " + expressionToAssign.ToString() + ";" + System.Environment.NewLine));
mMdxScript.Update();
}
finally
{
mServer.Disconnect();
}
}
private static void ReadPartitionInfo()
{
string srv_name = @"SCRBMSBDK000660";
string db_name = "BICC_Intermodal";
string cube_name = "Intermodal Cube";
//TextWriter tw = new StreamWriter("date.txt", true);
Microsoft.AnalysisServices.Server srv;
Microsoft.AnalysisServices.Database db;
Microsoft.AnalysisServices.Cube cube;
Microsoft.AnalysisServices.MeasureGroup mg;
Microsoft.AnalysisServices.Partition part;
srv = new Microsoft.AnalysisServices.Server();
try
{
srv.Connect(srv_name);
logMessageFmt("Connected to {0}", srv_name);
db = srv.Databases.FindByName(db_name);
cube = db.Cubes.FindByName(cube_name);
string query, slice;
mg = cube.MeasureGroups.FindByName("USD - Equipment Level");
logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name);
//DropMeasureGroupPartitions(mg);
//CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 120000000);
}
finally
{
if (srv.Connected == true)
{
srv.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 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);
}
}
private static DataTable GetClusterCharacteristics(string strModel, string strClusterUniqueID, double dThreshold)
{
//if we don't know the path to the system data mining sprocs assembly, get it
if (_cachedSystemDataMiningSprocsPath.Length == 0)
{
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(Context.CurrentServerID);
ClrAssembly ass = (ClrAssembly)svr.Assemblies.GetByName("System");
if (ass == null) throw new Exception("System (data mining sprocs) assembly not found");
foreach (ClrAssemblyFile file in ass.Files)
{
if (file.Type == ClrAssemblyFileType.Main)
{
lock (_cachedSystemDataMiningSprocsPath) _cachedSystemDataMiningSprocsPath = file.Name;
break;
}
}
svr.Disconnect();
}
//get the DataMining sprocs assembly and call the GetClusterCharacteristics function
System.Reflection.Assembly asAss = System.Reflection.Assembly.LoadFile(_cachedSystemDataMiningSprocsPath);
Type t = asAss.GetType("Microsoft.AnalysisServices.System.DataMining.Clustering");
object oClustering = t.GetConstructor(new Type[] { }).Invoke(new object[] { });
return (DataTable)t.InvokeMember("GetClusterCharacteristics", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod, null, oClustering, new object[] { strModel, strClusterUniqueID, dThreshold });
}
public DataTable DiscoverPartitionSlices(string cubeName, string measureGroupName)
{
DataTable dt;
DataTable dtTemp;
string props;
XmlaDiscover xd = new XmlaDiscover();
int dimCount;
int i;
DataRow[] sameDimRows;
string overlapText;
bool notFirstDim = false;
Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
server.Connect("*");
Database db = server.Databases.GetByName(Context.CurrentDatabaseName);
Cube cube = db.Cubes.GetByName(cubeName);
MeasureGroup mg = cube.MeasureGroups.GetByName(measureGroupName);
props = "<DATABASE_NAME>" + Context.CurrentDatabaseName + "</DATABASE_NAME>";
props += "<CUBE_NAME>" + cubeName + "</CUBE_NAME><MEASURE_GROUP_NAME>" + measureGroupName + "</MEASURE_GROUP_NAME>";
//get info for the first partition in the measure group
dt = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[0].Name + "</PARTITION_NAME>");
dt.Columns.Add("Overlap", System.Type.GetType("System.String"));
dt.AcceptChanges();
dimCount = dt.Rows.Count;
//get info for other partitions, if they exist
if (mg.Partitions.Count > 1)
{
for (i = 1; i < mg.Partitions.Count; i++)
{
Context.CheckCancelled();
dtTemp = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[i].Name + "</PARTITION_NAME>");
dtTemp.Columns.Add("Overlap", System.Type.GetType("System.String"));
dtTemp.AcceptChanges();
dt.Merge(dtTemp);
}
//work out if partitions overlap
foreach(DataRow currentRow in dt.Rows)
{
Context.CheckCancelled();
if (currentRow["ATTRIBUTE_INDEXED"].ToString() == "true")
{
overlapText = "";
sameDimRows = dt.Select("DIMENSION_NAME='" + currentRow["DIMENSION_NAME"] + "' AND ATTRIBUTE_NAME='" + currentRow["ATTRIBUTE_NAME"] + "' AND PARTITION_NAME<>'" + currentRow["PARTITION_NAME"] + "' AND ATTRIBUTE_INDEXED='true'");
notFirstDim = false;
foreach (DataRow dr in sameDimRows)
{
Context.CheckCancelled();
if (
(
(Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()))
&&
(Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
)
||
(
(Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()))
&&
(Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
)
||
(
(Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) >= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
)
)
{
if (notFirstDim)
overlapText+=", ";
overlapText+=dr["PARTITION_NAME"];
notFirstDim = true;
}
}
currentRow["Overlap"] = overlapText;
}
dt.AcceptChanges();
}
}
server.Disconnect();
return dt;
}
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 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);
}
}
public static DateTime GetLastProcessedDateOverPartitions(string CubeName, string MeasureGroupName, string PartitionName, bool IncludeMeasureGroupLastProcessed)
{
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName;
if (string.IsNullOrEmpty(CubeName))
{
sCubeName = AMOHelpers.GetCurrentCubeName();
}
else
{
sCubeName = CubeName;
}
if (!string.IsNullOrEmpty(PartitionName) && string.IsNullOrEmpty(MeasureGroupName))
{
throw new Exception("Measure group may not be empty if partition is explicitly specified");
}
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try
{
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
//If measure group is specified - get it. Otherwise iterate over all measure groups
if (!string.IsNullOrEmpty(MeasureGroupName))
{
dtTemp = FindMaxLastProcessedDateInMeasureGroup(cube.MeasureGroups.GetByName(MeasureGroupName), PartitionName, IncludeMeasureGroupLastProcessed);
}
else
{
foreach (MeasureGroup curMeasureGroup in cube.MeasureGroups)
{
DateTime curLastProcessedDate = FindMaxLastProcessedDateInMeasureGroup(curMeasureGroup, PartitionName, IncludeMeasureGroupLastProcessed);
if (dtTemp < curLastProcessedDate)
{
dtTemp = curLastProcessedDate;
}
}
}
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start(); //run the delegate code
while (!td.Join(1000)) //wait for up to a second for the delegate to finish
{
Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
}
if (exDelegate != null)
{
throw exDelegate;
}
return(dtTemp);
}
public DataTable ForEachMeasureGroupInternal(string command, bool forEachPartition)
{
DataTable result = new DataTable();
Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
server.Connect("*");
Database db = server.Databases.GetByName(Context.CurrentDatabaseName);
foreach (Microsoft.AnalysisServices.Cube c in db.Cubes)
{
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + server.Name + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Cube=" + c.Name);
foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
{
if (forEachPartition)
{
foreach (Microsoft.AnalysisServices.Partition p in mg.Partitions)
{
//parameters don't appear to work with some DMV queries, so use string substitution
string sNewCommand = command;
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_NAME", p.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_ID", p.ID);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
cmd.CommandTimeout = 0;
Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, result);
}
}
else
{
//parameters don't appear to work with some DMV queries, so use string substitution
string sNewCommand = command;
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
cmd.CommandTimeout = 0;
Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
TimeoutUtility.FillAdomdDataAdapter(adp, result);
}
}
conn.Close();
}
return result;
}
public static DateTime GetLastProcessedDateOverPartitions(string CubeName, string MeasureGroupName, string PartitionName, bool IncludeMeasureGroupLastProcessed)
{
string sServerName = Context.CurrentServerID;
string sDatabaseName = Context.CurrentDatabaseName;
string sCubeName;
if (string.IsNullOrEmpty(CubeName))
{
sCubeName = AMOHelpers.GetCurrentCubeName();
}
else
{
sCubeName = CubeName;
}
if (!string.IsNullOrEmpty(PartitionName) && string.IsNullOrEmpty(MeasureGroupName))
{
throw new Exception("Measure group may not be empty if partition is explicitly specified");
}
DateTime dtTemp = DateTime.MinValue;
Exception exDelegate = null;
System.Threading.Thread td = new System.Threading.Thread(delegate()
{
try
{
Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
oServer.Connect("Data Source=" + sServerName);
Database db = oServer.Databases.GetByName(sDatabaseName);
Cube cube = db.Cubes.FindByName(sCubeName);
//If measure group is specified - get it. Otherwise iterate over all measure groups
if (!string.IsNullOrEmpty(MeasureGroupName))
{
dtTemp = FindMaxLastProcessedDateInMeasureGroup(cube.MeasureGroups.GetByName(MeasureGroupName), PartitionName, IncludeMeasureGroupLastProcessed);
}
else
{
foreach (MeasureGroup curMeasureGroup in cube.MeasureGroups)
{
DateTime curLastProcessedDate = FindMaxLastProcessedDateInMeasureGroup(curMeasureGroup, PartitionName, IncludeMeasureGroupLastProcessed);
if (dtTemp < curLastProcessedDate)
{
dtTemp = curLastProcessedDate;
}
}
}
}
catch (Exception ex)
{
exDelegate = ex;
}
}
);
td.Start(); //run the delegate code
while (!td.Join(1000)) //wait for up to a second for the delegate to finish
{
Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
}
if (exDelegate != null) throw exDelegate;
return dtTemp;
}
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 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);
}
}
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);
}
}