private static string GetXmlString(IDataRecord dr, int column)
{
// Use the original AdomdDataReader (we don't have to use the proxy here!)
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader mdXmlField = dr.GetValue(column) as Microsoft.AnalysisServices.AdomdClient.AdomdDataReader;
if (mdXmlField == null)
{
return(null);
}
XElement piXml = new XElement("PARAMETERINFO");
while (mdXmlField.Read())
{
XElement datanode = new XElement("Parameter");
for (int col = 0; col < mdXmlField.FieldCount; col++)
{
string fieldName = mdXmlField.GetName(col);
if (fieldName != "")
{
var fieldContent = mdXmlField[col];
if (fieldContent != null)
{
datanode.Add(new XElement(mdXmlField.GetName(col), fieldContent.ToString()));
}
}
}
piXml.Add(datanode);
}
string s = piXml.ToString();
return(s);
}
/*
* Init checkbox fields to exclude the key attribute
*/
void InitAttributeAndMeasures(string sQuery, GridView objGrid)
{
MiningManager objMiningManager = new MiningManager();
// display results
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objMiningData = objMiningManager.GetQueryResult(sQuery);
List <string> sAtributes = new List <string>();
try
{
while (objMiningData.Read())
{
for (int i = 0; i < objMiningData.FieldCount; i++)
{
object value = objMiningData.GetValue(i);
string strValue = (value == null) ? string.Empty : value.ToString();
sAtributes.Add(strValue);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
objGrid.DataSource = sAtributes;
objGrid.DataBind();
}
/*
* Init all cubes
*/
private void InitCubes()
{
MiningManager objMiningManager = new MiningManager();
string sQuery = "SELECT CUBE_NAME FROM $system.mdschema_cubes WHERE left(CUBE_NAME, 1) <> '$' AND len(BASE_CUBE_NAME) = 0";
// display results
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objMiningData = objMiningManager.GetQueryResult(sQuery);
List <string> sCubes = new List <string>();
try
{
while (objMiningData.Read())
{
for (int i = 0; i < objMiningData.FieldCount; i++)
{
object value = objMiningData.GetValue(i);
string strValue = (value == null) ? string.Empty : value.ToString();
sCubes.Add(strValue);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
DropDownListCubes.DataSource = sCubes;
DropDownListCubes.DataBind();
}
/*
* Init all dimension names into a listbox
*/
private void InitDimensionNames()
{
MiningManager objMiningManager = new MiningManager();
string sQuery = "SELECT DIMENSION_NAME FROM $system.mdschema_dimensions WHERE CUBE_NAME = '" +
DropDownListCubes.SelectedItem.Text + "'";
// display results
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objMiningData = objMiningManager.GetQueryResult(sQuery);
List <string> sCubes = new List <string>();
try
{
while (objMiningData.Read())
{
for (int i = 0; i < objMiningData.FieldCount; i++)
{
object value = objMiningData.GetValue(i);
string strValue = (value == null) ? string.Empty : value.ToString();
sCubes.Add(strValue);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
DropDownListDimensions.DataSource = sCubes;
DropDownListDimensions.DataBind();
}
/*
* Execute custom query
*/
private void InitDataTable(string sQuery)
{
SQLMiningManager objMiningManager = new SQLMiningManager();
// clear node table
GridViewDistribution.DataSource = null;
GridViewDistribution.DataBind();
// display results
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objMiningData = objMiningManager.GetMiningResults(sQuery);
if (objMiningData == null)
{
return;
}
DataTable objTable = new DataTable();
DataColumn myColumn = new DataColumn();
DataRow myRow = null;
DataTable objSchemaTable = objMiningData.GetSchemaTable();
List <string> lMeta = new List <string>();
// init meta values
for (int i = 0; i < objSchemaTable.Rows.Count; i++)
{
lMeta.Add(objSchemaTable.Rows[i][0].ToString());
}
// add columns and column captions
for (int i = 0; i < objMiningData.FieldCount; i++)
{
myColumn = new DataColumn(lMeta[i]);
objTable.Columns.Add(myColumn);
}
// output the rows in the DataReader
while (objMiningData.Read())
{
// new row
myRow = objTable.NewRow();
// set the row values
for (int i = 0; i < objMiningData.FieldCount; i++)
{
myRow[i] = objMiningData[i];
}
// add row to the table
objTable.Rows.Add(myRow);
}
// close reader
objMiningData.Close();
GridViewResults.DataSource = objTable;
GridViewResults.DataBind();
// load the main table data
Session.Add("queryMining", objTable);
}
/*
* 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);
}
/*
* Init all attributes
*/
private void InitAttributes()
{
// clear current query
DropDownListKey.DataSource = null;
DropDownListKey.DataBind();
MiningManager objMiningManager = new MiningManager();
string sQuery = "SELECT HIERARCHY_NAME FROM $system.mdschema_hierarchies WHERE CUBE_NAME = '" +
DropDownListCubes.SelectedItem.Text + "' AND [DIMENSION_UNIQUE_NAME] = '[" +
DropDownListDimensions.SelectedItem.Text + "]'";
// display results
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objMiningData = objMiningManager.GetQueryResult(sQuery);
List <string> sCubes = new List <string>();
try
{
while (objMiningData.Read())
{
for (int i = 0; i < objMiningData.FieldCount; i++)
{
object value = objMiningData.GetValue(i);
string strValue = (value == null) ? string.Empty : value.ToString();
sCubes.Add(strValue);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
DropDownListKey.DataSource = sCubes;
DropDownListKey.DataBind();
// init input columns
sQuery = "SELECT HIERARCHY_NAME FROM $system.mdschema_hierarchies WHERE CUBE_NAME = '" +
DropDownListCubes.SelectedItem.Text + "' AND [DIMENSION_UNIQUE_NAME] = '[" +
DropDownListDimensions.SelectedItem.Text + "]' AND HIERARCHY_NAME <> '" + DropDownListKey.SelectedItem.Text + "'";
InitAttributeAndMeasures(sQuery, GridViewAttributes);
// init measures
sQuery = "SELECT MEASURE_NAME FROM $system.mdschema_measures WHERE CUBE_NAME = '" +
DropDownListCubes.SelectedItem.Text + "'";
InitAttributeAndMeasures(sQuery, GridViewMeasures);
}
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 AdomdDataReader(Microsoft.AnalysisServices.AdomdClient.AdomdDataReader obj)
{
_obj = obj;
_type = AdomdType.AnalysisServices;
}
/*
* Show distribution node for selected row
*/
private void DisplayDistributionNode(string sNodeName)
{
SQLMiningManager objMiningManager = new SQLMiningManager();
string sQuery = "select NODE_DISTRIBUTION from [" + DropDownListStructures.SelectedItem.ToString() + "].CONTENT where NODE_NAME ='" + sNodeName + "'";
// display results
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objMiningData = objMiningManager.GetMiningResults(sQuery);
// return for invalid data
if (objMiningData == null)
{
return;
}
Microsoft.AnalysisServices.AdomdClient.AdomdDataReader objNode = null;
// output the rows in the DataReader
while (objMiningData.Read())
{
for (int j = 0; j < objMiningData.FieldCount; j++)
{
objNode = (Microsoft.AnalysisServices.AdomdClient.AdomdDataReader)objMiningData[j];
// table defines
DataTable objTable = new DataTable();
DataColumn myColumn = new DataColumn();
DataRow myRow = null;
// Get the node meta
DataTable objSchemaTable = objNode.GetSchemaTable();
List <string> lMeta = new List <string>();
// init meta values
for (int i = 0; i < objSchemaTable.Rows.Count; i++)
{
lMeta.Add(objSchemaTable.Rows[i][0].ToString());
}
// add columns and column captions
for (int i = 0; i < objNode.FieldCount; i++)
{
myColumn = new DataColumn(lMeta[i]);
objTable.Columns.Add(myColumn);
}
// read the node
while (objNode.Read())
{
// new row
myRow = objTable.NewRow();
// set the row values
for (int i = 0; i < objNode.FieldCount; i++)
{
myRow[i] = objNode[i];
}
// add row to the table
objTable.Rows.Add(myRow);
}
// close reader
objNode.Close();
GridViewDistribution.DataSource = objTable;
GridViewDistribution.DataBind();
// hide viewer panel and show grid table
GridViewDistribution.Visible = true;
PanelViewer.Visible = false;
// load the main table data
Session.Add("queryNode", objTable);
}
}
// close reader
objMiningData.Close();
}
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();
}
public AdomdDataReader(Microsoft.AnalysisServices.AdomdClient.AdomdDataReader obj)
{
_obj = obj;
}
public AdomdDataReader(Microsoft.AnalysisServices.AdomdClient.AdomdDataReader dataReader)
{
_obj = dataReader;
}
