private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// dispose managed state (managed objects).
if (_command != null)
{
_command.Dispose();
_command = null;
}
else
{
void f()
{
_objExcel.Dispose();
_objExcel = null;
}
f();
}
}
disposedValue = true;
}
}
/*
* Return mining results from query
*/
public Microsoft.AnalysisServices.AdomdClient.AdomdDataReader GetMiningResults(string sQuery)
{
try
{
string sConnString = "Data Source=" + sServer + "; Initial Catalog=" + sCatalog;
Microsoft.AnalysisServices.AdomdClient.AdomdConnection objConn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(sConnString);
objConn.Open();
Microsoft.AnalysisServices.AdomdClient.AdomdCommand objCmd = objConn.CreateCommand();
objCmd.CommandText = sQuery;
/*
* "SELECT FLATTENED PredictHistogram(Generation) " +
* "FROM [Generation Trees] " +
* "NATURAL PREDICTION JOIN " +
* "( SELECT " +
* " (SELECT ’Cinemax’ AS Channel UNION " +
* " SELECT ’Showtime’ AS Channel) AS PayChannels " +
* ") AS T ";*/
//Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objReader = objCmd.ExecuteReader();
//Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter objDataAdaptor = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(objCmd);
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objDataReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection);
/*
* try
* {
* for (int i = 0; i < objDataReader.FieldCount; i++)
* {
* Console.Write(objDataReader.GetName(i) + "\t");
* }
* Console.WriteLine();
* while (objDataReader.Read())
* {
* for (int i = 0; i < objDataReader.FieldCount; i++)
* {
* object value = objDataReader.GetValue(i);
* string strValue = (value == null) ?
* string.Empty : value.ToString();
* Console.Write(strValue + "\t");
* }
* Console.WriteLine();
* }
* }
* finally
* {
* objDataReader.Close();
* }
*/
return(objDataReader);
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
return(null);
}
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 void SimplePredictionQuery()
{
Microsoft.AnalysisServices.AdomdClient.AdomdConnection connection = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection();
connection.ConnectionString =
"Data Source=localhost; Initial Catalog=Chapter 16";
connection.Open();
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = connection.CreateCommand();
cmd.CommandText =
"SELECT Predict(Generation) FROM [Generation Trees] " +
"NATURAL PREDICTION JOIN " +
"( SELECT " +
" (SELECT ’Cinemax’ AS Channel UNION " +
" SELECT ’Showtime’ AS Channel) AS PayChannels " +
") AS T ";
// execute the command and display the prediction result
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string predictedGeneration = reader.GetValue(0).ToString();
Console.WriteLine(predictedGeneration);
}
reader.Close();
connection.Close();
}
public AdomdCommand(Microsoft.AnalysisServices.AdomdClient.AdomdCommand command)
{
_command = command;
}
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 AdomdCommand(Microsoft.AnalysisServices.AdomdClient.AdomdCommand obj)
{
_obj = obj;
}
public AdomdCommand(Microsoft.AnalysisServices.AdomdClient.AdomdCommand obj)
{
_obj = obj;
}
public void MultipleRowQuery(Microsoft.AnalysisServices.AdomdClient.AdomdConnection objConn)
{
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = objConn.CreateCommand();
cmd.CommandText =
"SELECT FLATTENED PredictHistogram(Generation) " +
"FROM [Generation Trees] " +
"NATURAL PREDICTION JOIN " +
"( SELECT " +
" (SELECT ’Cinemax’ AS Channel UNION " +
" SELECT ’Showtime’ AS Channel) AS PayChannels " +
") AS T ";
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader reader = cmd.ExecuteReader();
try
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetName(i) + "\t");
}
Console.WriteLine();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
object value = reader.GetValue(i);
string strValue = (value == null) ?
string.Empty : value.ToString();
Console.Write(strValue + "\t");
}
Console.WriteLine();
}
}
finally
{
reader.Close();
}
// Demo code
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
// Check for nested table columns
if (reader.GetFieldType(i) == typeof(Microsoft.AnalysisServices.AdomdClient.AdomdDataReader))
{
// fetch the nested data reader
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader nestedReader = reader.GetDataReader(i);
while (nestedReader.Read())
{
for (int j = 0; j < nestedReader.FieldCount; j++)
{
object value = nestedReader.GetValue(j);
string strValue = (value == null) ?
string.Empty : value.ToString();
Console.Write(strValue);
}
Console.WriteLine();
}
// close the nested reader
nestedReader.Close();
}
}
}
cmd.CommandText =
"SELECT Predict(Generation) FROM [Generation Trees] " +
"NATURAL PREDICTION JOIN " +
"( SELECT " +
" (SELECT @Channel1 AS Channel UNION " +
" SELECT @Channel2 AS Channel) AS PayChannels " +
") AS T ";
Microsoft.AnalysisServices.AdomdClient.AdomdParameter p1 = new Microsoft.AnalysisServices.AdomdClient.AdomdParameter();
p1.ParameterName = "Channel1";
p1.Value = "Cinemax";
cmd.Parameters.Add(p1);
Microsoft.AnalysisServices.AdomdClient.AdomdParameter p2 = new Microsoft.AnalysisServices.AdomdClient.AdomdParameter();
p2.ParameterName = "Channel2";
p2.Value = "Showtime";
cmd.Parameters.Add(p2);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd2 = objConn.CreateCommand();
cmd2.CommandText =
"SELECT Predict(Generation) FROM [Generation Trees] " +
"NATURAL PREDICTION JOIN " +
"SHAPE { @CaseTable } " +
" APPEND( { @NestedTable } RELATE CustID TO CustID) " +
" AS PayChannels " +
"AS T ";
DataTable caseTable = new DataTable();
caseTable.Columns.Add("CustID", typeof(int));
caseTable.Rows.Add(0);
DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("CustID", typeof(int));
nestedTable.Columns.Add("Channel", typeof(string));
nestedTable.Rows.Add(0, "Cinemax");
nestedTable.Rows.Add(0, "Showtime");
Microsoft.AnalysisServices.AdomdClient.AdomdParameter p3 = new Microsoft.AnalysisServices.AdomdClient.AdomdParameter();
p3.ParameterName = "CaseTable";
p3.Value = caseTable;
cmd.Parameters.Add(p3);
Microsoft.AnalysisServices.AdomdClient.AdomdParameter p4 = new Microsoft.AnalysisServices.AdomdClient.AdomdParameter();
p4.ParameterName = "NestedTable";
p4.Value = nestedTable;
cmd.Parameters.Add(p4);
// execute the command and display the prediction result
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader reader2 = cmd.ExecuteReader();
if (reader2.Read())
{
string predictedGeneration = reader2.GetValue(0).ToString();
Console.WriteLine(predictedGeneration);
}
reader2.Close();
}
private void btnTestMDX_Click(object sender, EventArgs e)
{
try
{
SaveAction();
Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
cmd.CommandTimeout = 0;
cmd.Connection = conn;
Dictionary<string, string> dictQueries = new Dictionary<string, string>();
if (_currentAction.CaptionIsMdx)
{
dictQueries.Add("Caption MDX", "WITH MEMBER [Measures].[Caption MDX] as " + _currentAction.Caption + "\r\nSELECT [Measures].[Caption MDX] on COLUMNS");
}
if (!string.IsNullOrWhiteSpace(_currentAction.Condition))
{
dictQueries.Add("Condition MDX", "WITH MEMBER [Measures].[Condition MDX] as " + _currentAction.Condition + "\r\nSELECT [Measures].[Condition MDX] on COLUMNS");
}
if (txtExpression.Visible && !string.IsNullOrWhiteSpace(txtExpression.Text))
{
dictQueries.Add("Expression MDX", "WITH MEMBER [Measures].[Expression MDX] as " + txtExpression.Text + "\r\nSELECT [Measures].[Expression MDX] on COLUMNS");
}
if (_currentAction is ReportAction)
{
ReportAction reportAction = (ReportAction)_currentAction;
for (int i = 0; i < reportAction.ReportParameters.Count; i++)
{
ReportParameter rp = reportAction.ReportParameters[i];
dictQueries.Add("Report Parameter " + i + " MDX (" + rp.Name + ")", "WITH MEMBER [Measures].[Report Parameter " + i + " MDX] as " + rp.Value + "\r\nSELECT [Measures].[Report Parameter " + i + " MDX] on COLUMNS");
}
}
StringBuilder sbResult = new StringBuilder();
sbResult.AppendLine("Each MDX expression in this action is tested to make sure it works. Ensure each expression evaluates to a string except for the Condition which should evaluate to a boolean.\r\n");
foreach (string sKey in dictQueries.Keys)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(dictQueries[sKey]);
sb.AppendLine("FROM [" + cube.Name + "]");
if (_currentAction.TargetType == ActionTargetType.AttributeMembers || _currentAction.TargetType == ActionTargetType.HierarchyMembers || _currentAction.TargetType == ActionTargetType.Hierarchy)
{
sb.AppendLine("WHERE (" + _currentAction.Target + ".[All].FirstChild)");
}
else if (_currentAction.TargetType == ActionTargetType.Level || _currentAction.TargetType == ActionTargetType.LevelMembers)
{
sb.AppendLine("WHERE (Head(" + _currentAction.Target + ",1))");
}
cmd.CommandText = sb.ToString();
try
{
Microsoft.AnalysisServices.AdomdClient.CellSet cs = cmd.ExecuteCellSet();
sbResult.AppendLine(sKey + ": " + Convert.ToString(cs.Cells[0].Value));
}
catch (Exception exMDX)
{
sbResult.AppendLine(sKey + ": ERROR! " + exMDX.Message);
}
sbResult.AppendLine();
}
if (dictQueries.Count == 0)
sbResult.AppendLine("No MDX expressions used in this action.");
MessageBox.Show(sbResult.ToString(), "BIDS Helper Tabular Actions Editor - Test MDX Expressions");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
