public void replaceMdxBlock(string tag, string mdx_new)
{
Cube = Database.Cubes.FindByName("KPI");
Command cmd;
//Command cmd = findCommandByPattern("KPI", "<KCALC.XPR>");
MdxScript script = Cube.MdxScripts[0];
cmd = script.Commands[0];
string mdx = cmd.Text;
string tag_start = String.Format("//<{0}>", tag);
string tag_end = String.Format("//</{0}>", tag);
int kpi_from = mdx.IndexOf(tag_start) + tag_start.Length;
int kpi_to = mdx.IndexOf(tag_end) - 1;
mdx = mdx.Remove(kpi_from, kpi_to - kpi_from + 1);
mdx = mdx.Insert(kpi_from, mdx_new);
cmd.Text = mdx;
/*
* script.Commands.Remove(cmd); // At(i);
* //script.Update();
* cmd = new Command();
* cmd.Text = mdx;
* script.Commands.Add(cmd);
*/
script.Update();
Cube.Update();
//Cube.Update(UpdateOptions.ExpandFull);
}
public void DemoMethod05()
{
Database d = srv.Databases.FindByName("AWDW");
Cube c = d.Cubes.FindByName("Adventure Works");
MdxScript mx = c.MdxScripts[0];
foreach (CalculationProperty cp in mx.CalculationProperties)
{
Console.WriteLine($"name: {cp.CalculationReference}, folder: {cp.DisplayFolder}, nonempty: {cp.NonEmptyBehavior}, visible: {cp.Visible}");
}
}
/// <summary>
/// Creating the Cube, MeasureGroup, Measure, and Partition Objects.
/// </summary>
/// <param name="objDatabase">DB instance.</param>
/// <param name="objDataSourceView">DataSourceView instance.</param>
/// <param name="objDataSource">DataSource instance.</param>
/// <param name="objDimensions">Dimensions array instance.</param>
/// <param name="strFactTableName">FactTable Name.</param>
/// <param name="strTableNamesAndKeys">Array of Table Names and Keys.</param>
/// <param name="intDimensionTableCount">DimensionTable Count.</param>
private static void CreateCube(Database objDatabase, DataSourceView objDataSourceView, RelationalDataSource objDataSource, Dimension[] objDimensions, string strFactTableName, string[,] strTableNamesAndKeys, int intDimensionTableCount)
{
try
{
Console.WriteLine("Creating the Cube, MeasureGroup, Measure, and Partition Objects ...");
Cube objCube = new Cube();
Measure objSales = new Measure();
Measure objQuantity = new Measure();
MdxScript objTotal = new MdxScript();
String strScript;
Partition objPartition = new Partition();
Command objCommand = new Command();
//Add Cube to the Database and set Cube source to the Data Source View
objCube = objDatabase.Cubes.Add("SampleCube");
objCube.Source = new DataSourceViewBinding(objDataSourceView.ID);
//Add Measure Group to the Cube
//MeasureGroup objMeasureGroup = objCube.MeasureGroups.Add("FactSales");
MeasureGroup objMeasureGroup = objCube.MeasureGroups.Add(strFactTableName);
//Add Measure to the Measure Group and set Measure source
objSales = objMeasureGroup.Measures.Add("Amount");
objSales.Source = new DataItem(strFactTableName, "SalesAmount", OleDbType.Currency);
objQuantity = objMeasureGroup.Measures.Add("Quantity");
objQuantity.Source = new DataItem(strFactTableName, "OrderQuantity", OleDbType.Integer);
////Calculated Member Definition
//strScript = "Calculated; Create Member CurrentCube.[Measures].[Total] As [Measures].[Quantity] * [Measures].[Amount]";
////Add Calculated Member
//objTotal.Name = "Total Sales";
//objCommand.Text = strScript;
//objTotal.Commands.Add(objCommand);
//objCube.MdxScripts.Add(objTotal);
for (int i = 0; i < intDimensionTableCount; i++)
{
GenerateCube(objCube, objDimensions[i], objMeasureGroup, strFactTableName, strTableNamesAndKeys[i, 3]);
}
objPartition = objMeasureGroup.Partitions.Add(strFactTableName);
objPartition.Source = new TableBinding(objDataSource.ID, "dbo", strFactTableName);
objPartition.ProcessingMode = ProcessingMode.Regular;
objPartition.StorageMode = StorageMode.Molap;
//Save Cube and all major objects to the Analysis Services
objCube.Update(UpdateOptions.ExpandFull);
}
catch (Exception ex)
{
Console.WriteLine("Error in Creating the Cube, MeasureGroup, Measure, and Partition Objects. Error Message -> " + ex.Message);
}
}
public void DemoMethod03()
{
Database d = srv.Databases.FindByName("ContosoDCDemo");
Cube c = d.Cubes.FindByName("OperationA");
MdxScript mx = c.MdxScripts[0];
string cs = mx.Commands[0].Text;
Console.WriteLine($"scripts in cube: {c.MdxScripts.Count}");
Console.WriteLine($"commands in script: {c.MdxScripts[0].Commands.Count}");
Console.WriteLine($"{new String('-', 100)}\n");
Console.WriteLine(cs);
}
/// <summary>
/// Update Cube Command
/// </summary>
/// <param name="cube_server"></param>
/// <param name="cube_db"></param>
/// <param name="cube"></param>
/// <param name="mdx_code"></param>
public void UPDATE_CUBE_COMMAND(DB_SQLHELPER_BASE sqlHelper, Server cube_server, Database cube_db, Cube cube, String mdx_code)
{
try
{
MdxScript mdx = new MdxScript();
if (cube.DefaultMdxScript == null)
{
sqlHelper.ADD_MESSAGE_LOG(
"DefaultMdxScript is none, creating a new one",
MESSAGE_TYPE.MDX,
MESSAGE_RESULT_TYPE.Normal);
mdx.ID = cube.MdxScripts.GetNewID();
mdx.Name = "MDXHelper";
}
else
{
mdx = cube.DefaultMdxScript;
}
Command cmd = new Command();
if (cube.DefaultMdxScript == null || cube.DefaultMdxScript.Commands == null)
{
sqlHelper.ADD_MESSAGE_LOG(
"DefaultMdxScript.Commands is none, creating a new one",
MESSAGE_TYPE.MDX,
MESSAGE_RESULT_TYPE.Normal);
}
else
{
cmd = cube.DefaultMdxScript.Commands[0];
}
cmd.Text = mdx_code;
mdx.Commands.Remove(cmd);
mdx.Commands.Add(cmd);
cube.MdxScripts.Remove(mdx);
cube.MdxScripts.Add(mdx);
sqlHelper.ADD_MESSAGE_LOG(
"Refreshed cube mdx calculations.",
MESSAGE_TYPE.MDX,
MESSAGE_RESULT_TYPE.Normal);
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG(
"Failed to refreshed cube mdx calculations:" + ex.Message.ToString(),
MESSAGE_TYPE.MDX,
MESSAGE_RESULT_TYPE.Error);
throw ex;
}
}
public static SortedDictionary <int, MdxScriptCommand> GetCommandsFromScript(MdxScript script)
{
SortedDictionary <int, MdxScriptCommand> ret = new SortedDictionary <int, MdxScriptCommand>();
SortedDictionary <int, MdxScriptCommand> temp = new SortedDictionary <int, MdxScriptCommand>();
foreach (Command cmd in script.Commands)
{
temp = MdxScriptHelper.GetCommandsFromText(cmd.Text.Replace("\n", Environment.NewLine), ret.Count);
foreach (MdxScriptCommand cmd1 in temp.Values)
{
ret.Add(cmd1.CommandNumber, cmd1);
}
}
return(ret);
}
private void bdCalculatedMembers_CheckedChanged(object sender, EventArgs e)
{
if (rbCalculatedMembers.Checked == true && ASDatabase.SelectedIndex > -1 && Cube.SelectedIndex > -1)
{
Dimension.Visible = true;
MessageBox.Show("In method");
Dimension.DataSource = null;
using (Server S = new Server())
{
S.Connect(connectionString);
Cube cube = S.Databases.GetByName(ASDatabase.SelectedItem.ToString()).Cubes.GetByName(Cube.SelectedItem.ToString());
MdxScript M = cube.MdxScripts[0];
foreach (CalculationProperty CP in M.CalculationProperties)
{
MessageBox.Show(CP.CalculationType.ToString());
if (CP.CalculationType == CalculationType.Member)
{
MessageBox.Show(CP.CalculationReference);
}
}
}
}
}
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 MdxScript ToMdxScript(int compatibilityLevel)
{
var script = new MdxScript();
script.ID = "MdxScript";
script.Name = "MdxScript";
var firstCommand = new Command();
firstCommand.Text = compatibilityLevel < 1103 ?
ServerCommandProducer.CommonCommandText1100 :
ServerCommandProducer.CommonCommandText1103;
script.Commands.Add(firstCommand);
if (compatibilityLevel < 1103)
{
//Add all measures full text in one command
var command = new Command();
command.Text = ServerCommandProducer.DoNotModify1100 + string.Concat(
Measures.Select(i => i.FullText + ";" + Environment.NewLine)
);
script.Commands.Add(command);
}
else
{
foreach (var measure in Measures)
{
var command = new Command();
command.Text = ServerCommandProducer.DoNotModify1103;
if (!string.IsNullOrWhiteSpace(measure.Scope))
{
command.Text = command.Text.TrimEnd('\r', '\n', ' ');
command.Text += $@"
-- MDX SCRIPT --
{measure.Scope}
-- MDX SCRIPT --
";
}
command.Text += measure.FullText + ";" + Environment.NewLine;
if (measure.CalcProperty?.KPI != null)
{
var goalName = "_" + measure.Name + " Goal";
var goalMeasure = GetSupportMeasure(goalName);
if (goalMeasure != null)
{
command.Text += goalMeasure.FullText + "; " + Environment.NewLine;
}
var statusName = "_" + measure.Name + " Status";
var statusMeasure = GetSupportMeasure(statusName);
if (statusMeasure != null)
{
command.Text += statusMeasure.FullText + Environment.NewLine + "; " + Environment.NewLine;
}
var trendName = "_" + measure.Name + " Trend";
var trendMeasure = GetSupportMeasure(trendName);
if (trendMeasure != null)
{
command.Text += trendMeasure.FullText + Environment.NewLine + "; " + Environment.NewLine;
}
//ASSOCIATED_MEASURE_GROUP = 'DimCurrency'
command.Text += "CREATE KPI CURRENTCUBE." + measure.NameInBrackets +
" AS Measures." + measure.NameInBrackets;
command.Text += ", ASSOCIATED_MEASURE_GROUP = '" + measure.TableName + "'";
if (goalMeasure != null)
{
command.Text += ", GOAL = Measures." + goalMeasure.NameInBrackets;
}
if (statusMeasure != null)
{
command.Text += ", STATUS = Measures." + statusMeasure.NameInBrackets;
}
if (!string.IsNullOrWhiteSpace(measure.CalcProperty.KPI.StatusGraphic))
{
command.Text += ", STATUS_GRAPHIC = " + measure.CalcProperty.KPI.StatusGraphic;
}
if (trendMeasure != null)
{
command.Text += ", TREND = Measures." + trendMeasure.NameInBrackets;
}
if (!string.IsNullOrWhiteSpace(measure.CalcProperty.KPI.TrendGraphic))
{
command.Text += ", TREND_GRAPHIC = " + measure.CalcProperty.KPI.TrendGraphic;
}
command.Text += ";" + Environment.NewLine;
}
command.Annotations.Insert(0, "FullName", measure.Name);
command.Annotations.Insert(1, "Table", measure.TableName);
script.Commands.Add(command);
}
}
foreach (var measure in Measures)
{
var property = measure.CalcProperty != null?
measure.CalcProperty.ToXmlCalculationProperty(measure.NameInBrackets) :
DaxCalcProperty.CreateDefaultCalculationProperty().ToXmlCalculationProperty(measure.NameInBrackets);
script.CalculationProperties.Add(property);
if (measure.CalcProperty?.KPI != null)
{
var kpiProperties = measure.CalcProperty.GetKpiProperties(measure.Name);
foreach (var kpiProperty in kpiProperties)
{
script.CalculationProperties.Add(kpiProperty);
}
}
}
var lastProperty = new CalculationProperty();
lastProperty.CalculationReference = compatibilityLevel < 1103 ?
"Measures.[__No measures defined]" :
"[__XL_Count of Models]";
lastProperty.CalculationType = CalculationType.Member;
lastProperty.Visible = false;
script.CalculationProperties.Add(lastProperty);
return(script);
}
