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 void Disconnect()
{
if (Server != null & Server.Connected == true)
{
Server.Disconnect();
}
}
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);
}
}
private void DisConnectSSASServer()
{
if (ssasServer != null)
{
if (ssasServer.GetConnectionState(true) == System.Data.ConnectionState.Open)
{
ssasServer.Disconnect(true);
}
ssasServer.Dispose();
}
ssasServer = null;
}
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();
}
}
public DataTable ListFunctions()
{
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(Context.CurrentServerID);
try
{
return(getFunctionList(svr.Assemblies));
}
finally
{
svr.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();
}
}
}
// 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 VerifyServer()
{
try
{
if (Options == null)
{
return(false);
}
if (!srv.Connected || srv.ConnectionInfo.Server != txtServer.Text.Trim())
{
if (srv.Connected)
{
srv.Disconnect();
}
srv.Connect("Integrated Security=SSPI;Persist Security Info=False;Data Source=" + txtServer.Text.Trim());
}
return(true);
}
catch (Exception exc)
{
throw exc;
}
}
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();
}
}
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 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();
}
}
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 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);
}
}
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 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;
}
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);
}
}
public ActionResponse Execute()
{
var ids = new SSASIdentifiers(_input, _output);
using (_server) {
Database database;
_server.Connect($"Data Source={_output.Connection.Server};Catalog=;");
if (_server.Databases.Contains(ids.DatabaseId))
{
database = _server.Databases.Find(ids.DatabaseId);
_output.Info($"Updating existing OLAP database: {ids.DatabaseId}");
}
else
{
database = new Database()
{
Name = ids.DatabaseId,
ID = ids.DatabaseId,
DataSourceImpersonationInfo = new ImpersonationInfo {
ImpersonationMode = ImpersonationMode.Default
}
};
_server.Databases.Add(database);
_output.Info($"Creating new OLAP database: {ids.DatabaseId}");
if (!SSAS.Save(_server, _output, database))
{
return(new ActionResponse(500, $"Could not save OLAP database: {ids.DatabaseId}"));
}
}
DataSource dataSource;
if (database.DataSources.Contains(ids.DataSourceId))
{
dataSource = database.DataSources.Find(ids.DataSourceId);
_output.Info($"Updating existing data source: {ids.DataSourceId}");
}
else
{
dataSource = database.DataSources.AddNew(ids.DataSourceId);
_output.Info($"Creating new data source: {ids.DataSourceId}");
}
dataSource.ConnectionString = $"Provider=SQLOLEDB.1;Data Source={_input.Connection.Server};Integrated Security=SSPI;Initial Catalog={_input.Connection.Database}";
dataSource.ImpersonationInfo = new ImpersonationInfo {
ImpersonationMode = ImpersonationMode.ImpersonateServiceAccount
};
dataSource.Timeout = System.TimeSpan.FromSeconds(_input.Connection.RequestTimeout);
if (!SSAS.Save(_server, _output, dataSource))
{
return(new ActionResponse(500, $"Could not save data source: {ids.DataSourceId}"));
}
// view
var entity = _output.Entity;
var fields = SSAS.GetFields(_output);
DataSourceView dataSourceView;
if (database.DataSourceViews.Contains(ids.DataSourceViewId))
{
dataSourceView = database.DataSourceViews.Find(ids.DataSourceViewId);
if (dataSourceView.Schema.Tables.Contains(entity.Alias))
{
dataSourceView.Schema.Tables.Remove(entity.Alias);
}
dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields));
_output.Info($"Updating existing data source view: {ids.DataSourceViewId}");
}
else
{
_output.Info($"Creating new data source view: {ids.DataSourceViewId}");
dataSourceView = database.DataSourceViews.AddNew(ids.DataSourceViewId);
dataSourceView.DataSourceID = ids.DataSourceId;
dataSourceView.Schema = new DataSet("Schema");
dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields));
if (!SSAS.Save(_server, _output, dataSourceView))
{
return(new ActionResponse(500, $"Could not save data source view: {ids.DataSourceViewId}"));
}
}
Dimension dimension;
if (database.Dimensions.Contains(entity.Alias))
{
dimension = database.Dimensions.Find(entity.Alias);
_output.Info($"Updating existing dimension: {entity.Alias}");
}
else
{
dimension = database.Dimensions.AddNew(entity.Alias, entity.Alias);
dimension.WriteEnabled = false;
dimension.StorageMode = DimensionStorageMode.Molap;
_output.Info($"Creating new dimension: {entity.Alias}");
}
dimension.Source = new DataSourceViewBinding(ids.DataSourceViewId);
foreach (var field in fields)
{
DimensionAttribute dimensionAttribute;
if (dimension.Attributes.Contains(field.Alias))
{
dimensionAttribute = dimension.Attributes.Find(field.Alias);
}
else
{
dimensionAttribute = dimension.Attributes.Add(field.Label, field.Alias);
dimensionAttribute.OrderBy = OrderBy.Key; // for now
}
var optimize = field.PrimaryKey || field.Dimension == "true" || field.Dimension == "default" && !field.IsNumeric();
dimensionAttribute.AttributeHierarchyEnabled = optimize;
dimensionAttribute.IsAggregatable = optimize;
dimensionAttribute.Usage = field.PrimaryKey ? AttributeUsage.Key : AttributeUsage.Regular;
dimensionAttribute.KeyColumns.Clear();
DataItem keyColumn;
if (field.Type == "string")
{
var length = field.Length == "max" ? int.MaxValue : Convert.ToInt32(field.Length);
keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field), length)
{
Trimming = Trimming.None
};
}
else
{
keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field));
}
dimensionAttribute.KeyColumns.Add(keyColumn);
}
if (!SSAS.Save(_server, _output, dimension))
{
return(new ActionResponse(500, $"Could not save dimension: {entity.Alias}"));
}
Cube cube;
if (database.Cubes.Contains(ids.CubeId))
{
cube = database.Cubes.Find(ids.CubeId);
_output.Info($"Updating existing cube: {ids.CubeId}");
}
else
{
cube = database.Cubes.AddNew(ids.CubeId, ids.CubeId);
_output.Info($"Creating new cube: {ids.CubeId}");
}
cube.Source = new DataSourceViewBinding(ids.DataSourceViewId);
CubeDimension cubeDimension;
if (cube.Dimensions.Contains(entity.Alias))
{
cubeDimension = cube.Dimensions.Find(entity.Alias);
}
else
{
cubeDimension = cube.Dimensions.Add(entity.Alias);
}
cubeDimension.Attributes.Clear();
foreach (DimensionAttribute attribute in database.Dimensions.Find(entity.Alias).Attributes)
{
cubeDimension.Attributes.Add(attribute.ID);
}
var normalMeasureGroup = GetMeasureGroup(cube, entity, ids.NormalMeasureGroupId, ids.DataSourceViewId);
// standard count measure
Measure countMeasure;
if (normalMeasureGroup.Measures.Contains("Count"))
{
countMeasure = normalMeasureGroup.Measures.Find("Count");
}
else
{
countMeasure = normalMeasureGroup.Measures.Add("Count", "Count");
}
countMeasure.AggregateFunction = AggregationFunction.Count;
countMeasure.Source = new DataItem(new RowBinding(entity.Alias), OleDbType.Integer);
// version measures
var versionField = entity.GetVersionField();
if (versionField != null)
{
Measure versionMeasure;
if (normalMeasureGroup.Measures.Contains(ids.VersionId))
{
versionMeasure = normalMeasureGroup.Measures.Find(ids.VersionId);
}
else
{
versionMeasure = normalMeasureGroup.Measures.Add(ids.VersionId);
}
versionMeasure.AggregateFunction = AggregationFunction.Max;
versionMeasure.Source = new DataItem(entity.Alias, versionField.Alias, SSAS.GetOleDbType(versionField));
versionMeasure.Visible = false;
}
// defined measures
var measureFields = entity.GetAllOutputFields().Where(f => f.Measure && f.Type != "byte[]");
// normal measures
foreach (var field in measureFields.Where(f => f.AggregateFunction != "distinctcount"))
{
Measure measure;
var function = (AggregationFunction)Enum.Parse(typeof(AggregationFunction), field.AggregateFunction, true);
if (normalMeasureGroup.Measures.Contains(field.Alias))
{
measure = normalMeasureGroup.Measures.Find(field.Alias);
}
else
{
measure = normalMeasureGroup.Measures.Add(field.Label, field.Alias);
}
measure.AggregateFunction = function;
measure.FormatString = field.Format;
measure.Source = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field));
}
// distinct measures
foreach (var field in measureFields.Where(f => f.AggregateFunction == "distinctcount"))
{
var type = SSAS.GetOleDbType(field);
if (SSAS.CanDistinctCount(type))
{
Measure measure;
var distinctMeasureGroup = GetMeasureGroup(cube, entity, ids.DistinctMeasureGroupId, ids.DataSourceViewId);
if (distinctMeasureGroup.Measures.Contains(field.Alias))
{
measure = distinctMeasureGroup.Measures.Find(field.Alias);
}
else
{
measure = distinctMeasureGroup.Measures.Add(field.Label, field.Alias);
}
measure.AggregateFunction = AggregationFunction.DistinctCount;
measure.FormatString = field.Format;
measure.Source = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field));
}
else
{
_output.Warn($"Can not distinct count {field.Label} using {field.Type} type.");
}
}
if (!SSAS.Save(_server, _output, cube))
{
return(new ActionResponse(500, $"Could not save cube: {ids.CubeId}"));
}
_server.Disconnect();
}
return(new ActionResponse());
}
static void CreateAllPartitions()
{
//https://msdn.microsoft.com/en-us/library/ms345091.aspx
string srv_name =
@"SCRBMSBDK000660\PREPRODQUERYSRV1";
//@"SCRBSQLDEFRM637"; //this one is not enough for Intermodal, always fails during the processing
string db_name = "Intermodal";
//"Intermodal_TEST";
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;
//-------- USD - Equipment Level
mg = cube.MeasureGroups.FindByName("USD - Equipment Level");
/*
* select top 100 percent * from
* (
* ) x
* order by Shipment_CODE_SSL, Equipment_Key
*
*/
query = @"
select * from DTST12_SIT_DEV.IM_PnLFACTUSDAggrShpCnt
where AccountMonth_CODE_SSL in (
select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
where MonthYYYYMM_No = {0}
)
" ;
slice = "[Time Accounting period].[Unique Month].&[{0}]";
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);
CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 120000000);
//-------- FFE
/*
* select top 100 percent * from
* (
* ) x
* order by Shipment_CODE_SSL, Equipment_CODE_SSL
*
*/
mg = cube.MeasureGroups.FindByName("FFE");
query = @"
select * from DTST12_SIT_DEV.IM_PnL_FACT_FFEDisc_D
where AccountMonth_CODE_SSL in (
select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
where MonthYYYYMM_No = {0}
)
union all
select * from DTST12_SIT_DEV.IM_PnL_FACT_FFEDisc_L
where AccountMonth_CODE_SSL in (
select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
where MonthYYYYMM_No = {0}
)
" ;
slice = "[Time Accounting period].[Unique Month].&[{0}]";
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, 2000000);
CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 2000000);
//-------- Equipment Moves
/*
* select top 100 percent * from
* (
* ) x
* order by Shipment_Key
*
*/
mg = cube.MeasureGroups.FindByName("Equipment Moves");
query = @"
select * from DTST12_SIT_DEV.IM_tF_PnLContMovAggr
where AccountingMonth_Key in (
select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
where MonthYYYYMM_No = {0}
)
" ;
slice = "[Time Accounting period].[Unique Month].&[{0}]";
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, 10000000);
CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 10000000);
//-------- USD - Shipment Level
/*
* select top 100 percent* from
* (
* ) x
* order by Shipment_CODE_SSL
*
*/
mg = cube.MeasureGroups.FindByName("USD - Shipment Level");
query = @"
select * from DTST12_SIT_DEV.IM_tF_PnLFACTUSDAggr_Limited
where AccountMonth_CODE_SSL in (
select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
where MonthYYYYMM_No = {0}
)
" ;
slice = "[Time Accounting period].[Unique Month].&[{0}]";
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, 250000);
CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 250000);
cube.Update();
}
finally
{
if (srv.Connected == true)
{
srv.Disconnect();
}
}
}
static void PartitionInfo()
{
string srv_name = @"SCRBMSBDK000660";
string db_name = "FBR_FYPnL_DPRD";
string cube_name = "FYPnL Cube";
string mg_name = "USD";
//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);
Cube cube = db.Cubes.FindByName(cube_name);
CubeDimension cubedim = cube.Dimensions[0];
Dimension dbdim = cubedim.Dimension;
DimensionAttribute dbattr = dbdim.Attributes[0];
//var Source = dbattr.Source;
System.Data.DataSet ds = dbdim.DataSourceView.Schema;
string dsid = db.DataSourceViews[0].DataSourceID;
DataTable dt = db.DataSourceViews[0].Schema.Tables[0];
//db.DataSources[0].
//DataTable dt = ds.Tables["SHARED_DimBrand"];
//ep = dt.ExtendedProperties.
MeasureGroup mg = cube.MeasureGroups.FindByName(mg_name);
MeasureGroupDimension mgd = mg.Dimensions[0];
List <MeasureGroupAttribute> alist = new List <MeasureGroupAttribute>();
if (mgd is RegularMeasureGroupDimension)
{
RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)mgd;
foreach (MeasureGroupAttribute mgattr in rmgd.Attributes)
{
if (mgattr.Type == MeasureGroupAttributeType.Granularity)
{
alist.Add(mgattr);
}
}
//MeasureGroupAttribute mgattr = rmgd.Attributes.f["Key"];
}
Type t = alist[0].KeyColumns[0].Source.GetType();
Measure msr = mg.Measures[0];
foreach (Partition part in mg.Partitions)
{
string src;
TabularBinding tb = part.Source;
if (tb is QueryBinding)
{
src = String.Format("QUERY: {0}", ((QueryBinding)tb).QueryDefinition);
}
else if (tb is TableBinding)
{
src = String.Format("TABLE: {0}.{1}", ((TableBinding)tb).DbSchemaName, ((TableBinding)tb).DbTableName);
}
else if (tb is DsvTableBinding)
{
src = String.Format("DSV: {0}.{1}", ((DsvTableBinding)tb).DataSourceViewID, ((DsvTableBinding)tb).TableID);
}
else
{
src = String.Empty;
}
logMessageFmt("Partition [{0}]: {1}", part.Name, src /*part.EstimatedRows*/);
//part.Process()
}
//Partition part = mg.Partitions[0]; //.FindByName(part_name);
logMessage("Done.");
Console.ReadKey();
}
finally
{
if (srv.Connected == true)
{
srv.Disconnect();
}
}
}
/// <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);
}
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 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 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);
}