protected void Button1_Click(object sender, EventArgs e)
{
AdomdConnection con = new AdomdConnection("Data Source = PC; Initial Catalog = ADM_HW");
con.Open();
AdomdCommand com = new AdomdCommand();
com.Connection = con;
string s = @"select flattened predicthistogram(disease) from [HeartBMM]
natural prediction join
(select '" + TextBox1.Text + @"' AS [Age],
'" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type],
'" + TextBox2.Text + @"' AS [Rest Blood Pressure],
'" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar],
'" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina],
'" + TextBox3.Text + @"' AS [Max Heart Rate],
'" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t";
com.CommandText = s;
AdomdDataReader dr = com.ExecuteReader();
while (dr.Read())
{
if (dr[0] != null)
{
Label1.Text += dr[0].ToString() + " " + dr[2].ToString();
}
}
dr.Close();
con.Close();
}
/// <summary>
/// Returns a DataTable from an AdomdCommand reader (MDX Query in an OLAP Cube)
/// </summary>
static public DataTable FromAdomdCommand(AdomdCommand command)
{
AdomdDataReader dr = command.ExecuteReader();
DataTable result = new DataTable("Data");
// output the rows in the DataReader
DataTable dtSchema = dr.GetSchemaTable();
foreach (DataRow schemarow in dtSchema.Rows)
{
var columnName = schemarow.ItemArray[0].ToString().Replace("[", "").Replace("]", "").Replace(" ", "");
result.Columns.Add(columnName, Type.GetType(schemarow.ItemArray[5].ToString()));
}
while (dr.Read())
{
object[] ColArray = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr[i] != null)
{
ColArray[i] = dr[i];
}
}
result.LoadDataRow(ColArray, true);
}
dr.Close();
return(result);
}
private void getBoughtToghtherMovies(String input, List <string> output)
{
String newInput = input.Trim();
AdomdConnection CON = new AdomdConnection(
"Data Source=.;Catalog=ADMF18");
CON.Open();
AdomdCommand COM = CON.CreateCommand();
string s = "SELECT Flattened PREDICT([Movies],5" + ") FROM [CustomersMM] NATURAL PREDICTION JOIN (SELECT (" + "Select '" + newInput + "' as [movie]" + ") AS [Movies]) As T";
COM.CommandText = s;
AdomdDataReader DR = COM.ExecuteReader();
while (DR.Read())
{
if (DR[0] != null)
{
output.Add(DR[0].ToString());
}
}
DR.Close();
CON.Close();
}
public HttpResponseMessage List(string name = "cipla")
{
using (AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
// AdomdConnection conn = new AdomdConnection(
// "Data Source=localhost;Catalog=YourDatabase");
//conn.Open();
string commandText = @"SELECT FLATTENED
PredictAssociation()
From
[Mining Structure Name]
NATURAL PREDICTION JOIN
(SELECT (SELECT 1 AS [UserId]) AS [Vm]) AS t ";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(Convert.ToString(dr[0]));
}
dr.Close();
conn.Close();
return(null);
}
}
public List <YearCategory> Getyearcategory()
{
List <YearCategory> Year = new List <YearCategory>();
AdomdConnection conn = new AdomdConnection(
"Data Source=LAPTOP-ED7T3RSE\\ETELLERANDET;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Dim Product].[Hierarchy].[Main Category]} ON COLUMNS, NONEMPTY({[Dim Date].[Hierarchy].[Year]}) ON ROWS FROM [Charlie BI F Club] WHERE [Measures].[Fact Sale Count]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
YearCategory TempYear = new YearCategory();
TempYear.year = Convert.ToString(item[0]);
TempYear.food = Convert.ToString(item[1]);
TempYear.nonFood = Convert.ToString(item[2]);
TempYear.unknown = Convert.ToString(item[3]);
Year.Add(TempYear);
}
dr.Close();
conn.Close();
return(Year);
}
public List <Day> Getdays()
{
List <Day> Day = new List <Day>();
AdomdConnection conn = new AdomdConnection(
"Data Source=.;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Measures].[Fact Sale Count] } ON COLUMNS, NonEmpty({[Dim Date].[Hierarchy].[Day Of Month]}) ON ROWS FROM[Charlie BI F Club]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
Day Tempday = new Day();
Tempday.month = Convert.ToString(item[1]);
Tempday.day = Convert.ToString(item[2]);
Tempday.amount = Convert.ToString(item[3]);
Day.Add(Tempday);
}
dr.Close();
conn.Close();
return(Day);
}
public List <Members> Getmembers()
{
List <Members> members = new List <Members>();
AdomdConnection conn = new AdomdConnection(
"Data Source=LAPTOP-ED7T3RSE\\ETELLERANDET;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Measures].[Fact Sale Count]} ON COLUMNS, NONEMPTY({[Dim Member].[Member ID].[Member ID]}) ON ROWS FROM [Charlie BI F Club]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
Members member = new Members();
member.member = Convert.ToString(item[0]);
member.Amount = Convert.ToString(item[1]);
members.Add(member);
}
dr.Close();
conn.Close();
return(members);
}
public HttpResponseMessage GetItemsByDimension(string dim, string order)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
{0}.CHILDREN,
{0}.CURRENTMEMBER.MEMBER_NAME, " + order +
@")
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
[OrderDimension]
ON ROWS
";
string CUBO_NAME = "[DHW Northwind]";
WITH = string.Format(WITH, dim);
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
dynamic result = new
{
datosDimension = dimension
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dim);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public List <Year> GetYear()
{
List <Year> Year = new List <Year>();
AdomdConnection conn = new AdomdConnection(
"Data Source=.;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Measures].[Fact Sale Count] } ON COLUMNS, NONEMPTY({[Dim Date].[Hierarchy].[Year]} ) ON ROWS FROM[Charlie BI F Club]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
Year Tempyear = new Year();
Tempyear.year = Convert.ToString(item[0]);
Tempyear.amount = Convert.ToString(item[1]);
Year.Add(Tempyear);
}
dr.Close();
conn.Close();
return(Year);
}
public SemesterSales[] GetSalesPrSemester()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY([Dim Member].[Semester].[Semester]*[Dim Product].[Sub Sub Category].[Sub Sub Category]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SemesterSales> semesters = new List <SemesterSales>();
while (dr.Read())
{
SemesterSales semester = new SemesterSales
{
semester = dr[0].ToString(),
SubSubCategory = dr[1].ToString(),
saleCount = int.Parse(dr[2].ToString())
};
semesters.Add(semester);
}
dr.Close();
SemesterSales[] semesterSales = semesters.FindAll(FindBeer).ToArray();
conn.Close();
return(semesterSales);
}
}
protected void Button2_Click(object sender, EventArgs e)
{
AdomdConnection con = new AdomdConnection("Data Source=PC;Initial Catalog=ADM_HW");
con.Open();
AdomdCommand com = new AdomdCommand();
com.Connection = con;
string s = @"SELECT disease From [HeartID3MM]
NATURAL PREDICTION JOIN
(SELECT '" + TextBox1.Text + @"' AS [Age],
'" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type],
'" + TextBox2.Text + @"' AS [Rest Blood Pressure],
'" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar],
'" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina],
'" + TextBox3.Text + @"' AS [Max Heart Rate],
'" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t";
com.CommandText = s;
AdomdDataReader dr = com.ExecuteReader();
if (dr.Read())
{
if (dr[0] != null)
{
Label2.Text = dr[0].ToString();
}
}
dr.Close();
con.Close();
}
public ProductCat[] GetProductsByCategory()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT
[Measures].[Fact Sale Count] ON COLUMNS,
{[Dim Product].[Product Id].[Product Id]*[Dim Product].[Name].[Name]*[Dim Product].[Category].[Category]*[Dim Product].[Sub Category].[Sub Category]*[Dim Product].[Sub Sub Category].[Sub Sub Category]} ON ROWS
From [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <ProductCat> productCats = new List <ProductCat>();
while (dr.Read())
{
ProductCat productCat = new ProductCat
{
productID = int.Parse(dr[0].ToString()),
name = dr[1].ToString(),
cat = dr[2].ToString(),
subCat = dr[3].ToString(),
subSubCat = dr[4].ToString(),
};
productCats.Add(productCat);
}
dr.Close();
ProductCat[] productSubCats = productCats.ToArray();
conn.Close();
return(productSubCats);
}
}
public SCCatD[] GetProductSalesPrDay()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY( [Dim Product].[Category].[Category]*[Dim Date].[Day].[Day]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SCCatD> days = new List <SCCatD>();
while (dr.Read())
{
SCCatD day = new SCCatD
{
cat = dr[0].ToString(),
day = int.Parse(dr[1].ToString()),
saleCount = int.Parse(dr[2].ToString())
};
days.Add(day);
}
dr.Close();
SCCatD[] daySales = days.ToArray();
conn.Close();
return(daySales);
}
}
private Dictionary <string, int> RequestSales(string MDXQuery)
{
Dictionary <string, int> responseMessage = new Dictionary <string, int>();
try
{
OpenConnection();
AdomdCommand command = new AdomdCommand(MDXQuery, connection);
AdomdDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
responseMessage.Add(Convert.ToString(dataReader[0]), Convert.ToInt32(dataReader[1]));
}
dataReader.Close();
connection.Close();
return(responseMessage);
}
catch (Exception e)
{
Console.WriteLine("Exception has occured while retriving data:\n" +
"Error message: " + e.Message);
return(null);
}
}
public HttpResponseMessage comparativa(DatosComparar comp)
{
string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string WHERE = "";
string dimension = @"";
string coma = "";
foreach (var dato in comp.Nombres)
{
switch (comp.dimension)
{
case 1: dimension += coma + @"[Dim Cliente].[Dim Cliente Nombre].&[" + dato + "]"; break;
case 2: dimension += coma + @"[Dim Producto].[Dim Producto Nombre].&[" + dato + "]"; break;
case 3: dimension += coma + @"[Dim Producto].[Dim Producto Categoria].&[" + dato + "]"; break;
case 4: dimension += coma + @"[Dim Empleado].[Dim Empleado Nombre].&[" + dato + "]"; break;
}
coma = ", ";
}
string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } * { " + dimension + " } ON COLUMNS,";
string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE;
List <Comparativa> ventasUsuario = new List <Comparativa>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
int fi = dr.FieldCount;
while (dr.Read())
{
Comparativa v = new Comparativa();
List <decimal> listaUsu = new List <decimal>();
v.fecha = dr.GetString(1) + " " + dr.GetString(0);
for (int i = 2; i < fi; i++)
{
decimal valor = dr.IsDBNull(i) ? 0 : dr.GetDecimal(i);
listaUsu.Add(valor);
}
v.Datos = listaUsu;
ventasUsuario.Add(v);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
private void ExecuteOlapQuery()
{
OlapManager objOlapManager = new OlapManager();
CellSet objCellSet = objOlapManager.GetQueryResult(TextBoxQuery.Text);
AdomdDataAdapter objDataAdaptor = new AdomdDataAdapter(objOlapManager.ObjCommand);
AdomdDataReader objDataReader = objOlapManager.ObjCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataTable objTable = new DataTable();
DataColumn myColumn = new DataColumn();
DataRow myRow = null;
DataTable objSchemaTable = objDataReader.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 < objDataReader.FieldCount; i++)
{
myColumn = new DataColumn(lMeta[i]);
objTable.Columns.Add(myColumn);
}
// output the rows in the DataReader
while (objDataReader.Read())
{
// new row
myRow = objTable.NewRow();
// set the row values
for (int i = 0; i < objDataReader.FieldCount; i++)
{
myRow[i] = objDataReader[i];
}
// add row to the table
objTable.Rows.Add(myRow);
}
// close reader
objDataReader.Close();
GridViewData.DataSource = objTable;
GridViewData.DataBind();
// export TEST
//ExportDataTableToExcel(objTable);
objOlapManager.CloseConnection();
// load the main table data
this.ObjMainTable = objTable;
Session.Add("queryData", objMainTable);
}
public HttpResponseMessage GetDataPieByDimension(string dimension, string order, string[] values)
{
string elementsToSearch = "";
foreach (var item in values)
{
elementsToSearch += ($@"{dimension}.&[{item}],");
}
elementsToSearch = elementsToSearch.Remove(elementsToSearch.Length - 1);
string MDX_QUERY = $@"
SELECT
({{ {elementsToSearch} }})
ON ROWS,{{
[Measures].[VENTAS]
}}ON COLUMNS FROM [PUBS DW]
";
List <string> dim = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dim,
datosVenta = ventas,
datosTabla = lstTabla
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
//cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dim.Add(dr.GetString(0));
ventas.Add(dr.GetDecimal(1));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = dr.GetDecimal(1)
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage Top(Reporte info)
{
string nombreDimension = @"";
switch (info.dimension)
{
case 1: //Clientes
default:
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } ";
break;
case 2: //Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } ";
break;
case 3: //Categorias
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } ";
break;
case 4: //Empleadoss
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } ";
break;
}
string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) ";
string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS, ";
string ROWS = @"NON EMPTY { HEAD([TopVentas], " + info.top + ") } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string MDXQuery = WITH + "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME;
List <Venta> ventasUsuario = new List <Venta>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension));
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
Venta v = new Venta();
v.mes = dr.GetString(0);
v.ventas = dr.GetDecimal(1);
ventasUsuario.Add(v);
}
dr.Close();
}
}
};
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public HttpResponseMessage GetMesDimension()
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
[Dim Tiempo].[Mes Espaniol].CHILDREN,
[Dim Tiempo].[Mes Espaniol].CURRENTMEMBER.MEMBER_NAME
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Hec Ventas Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
[OrderDimension]
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
List <string> dimension = new List <string>();
dynamic result = new
{
datosMeses = dimension,
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage ventas(Filtro info)
{
//string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) ";
string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,";
string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string WHERE = "";
if (info.Item != 0 && info.Nombre != "")
{
string dimension = @"";
switch (info.Item)
{
case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].&[" + info.Nombre + "]"; break;
case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].&[" + info.Nombre + "]"; break;
case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].&[" + info.Nombre + "]"; break;
case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].&[" + info.Nombre + "]"; break;
}
WHERE = " WHERE " + dimension;
}
string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE;
List <Venta> ventasUsuario = new List <Venta>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
Venta v = new Venta();
//v.ano = ;
v.mes = dr.GetString(1) + " " + dr.GetInt16(0);
v.ventas = dr.GetDecimal(2);
ventasUsuario.Add(v);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public string Command(string analysisSever, string depenedentValue)
{
StringBuilder stringBuilder = new StringBuilder();
//Analysis Server Connection String
string analysisServer = "Provider=MSOLAP;Data Source=TJ-PC;Integrated Security=SSPI;Initial Catalog=HDPurchasingPredictiveAnalysis;Location=TJ-PC";
using (AdomdConnection serverConnection = new AdomdConnection(analysisServer))
{
//Initialise a new command
AdomdCommand command = new AdomdCommand();
//Connect to the Analysis Server
command.Connection = serverConnection;
//Run this singleton query
command.CommandText = "SELECT [DrugPricingCostLR].[Drug Cost_] From[DrugPricingCostLR] NATURAL PREDICTION JOIN (SELECT 234 AS[ASP 2016]) AS t";
//This is the parameter we are using to predict
AdomdParameter parameter = null;
parameter = command.CreateParameter();
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = "ASP 2016";
parameter.Value = depenedentValue;
command.Parameters.Add(parameter);
//Open the connection to the Analysis Server
serverConnection.Open();
//Execute the prediction query and return the results
AdomdDataReader reader = null;
reader = command.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append(reader.GetName(i) + "\t");
}
stringBuilder.Append("\r\n--------------------------------------------\r\n");
//Important before accessing any data, call the .Read method
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
stringBuilder.Append("\r\n");
}
reader.Close();
}
return(stringBuilder.ToString());
}
public HttpResponseMessage comparativausu(Reporte num)
{
string COLUMNS = @" NON EMPTY [Measures].[Fact Ventas Netas] ON COLUMNS,";
string CUBO_NAME = @"[DWH Northwind] ";
string WHERE = "";
string dimension = @"";
switch (num.dimension)
{
case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].CHILDREN"; break;
case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].CHILDREN"; break;
case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].CHILDREN"; break;
case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].CHILDREN"; break;
}
string ROWS = @" NON EMPTY " + dimension + " ON ROWS";
//WHERE = " WHERE " + dimension;
string MDXQuery = "SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
List <usuario> ventasUsuario = new List <usuario>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
usuario u = new usuario();
//v.ano = ;
u.nombre = dr.GetString(0);
ventasUsuario.Add(u);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public List <string> predict(List <string> input)
{
List <string> output = new List <string>();
//Connection
AdomdConnection CON = new AdomdConnection(
"Data Source=.;Catalog=AnalysisDB");
CON.Open();
// command
AdomdCommand COM = CON.CreateCommand();
// Query Command to Retrive Result
string s = @"SELECT Flattened PREDICT([Orders Details],3)
FROM [OrdersMM]
NATURAL PREDICTION JOIN
(SELECT ( ";
foreach (string x in input)
{
if (input.IndexOf(x) > 0)
{
s += " Union ";
}
s += "Select '" + x + "' as [Product Name]";
}
s += " ) AS [Orders Details]) As T";
COM.CommandText = s;
// Read result
AdomdDataReader DR = COM.ExecuteReader();
while (DR.Read())
{
if (DR[0] != null)
{
output.Add(DR[0].ToString());
}
}
DR.Close();
CON.Close();
return(output);
}
public List <Category> Category()
{
List <Category> Categories = new List <Category>();
AdomdConnection conn = new AdomdConnection(
"Data Source=LAPTOP-ED7T3RSE\\ETELLERANDET;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Dim Product].[Main Category].[Main Category]} ON COLUMNS, [Dim Product].[Product ID].[Product ID] ON ROWS FROM [Charlie BI F CLUB]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
}
dr.Close();
conn.Close();
return(Categories);
}
static void GetData(string connectionString)
{
AdomdConnection connection = new AdomdConnection(connectionString + ";Catalog=Analysis Services Tutorial");
connection.Open();
//string queryCommand = @" SELECT NON EMPTY { [Measures].[Internet Sales Count] } ON COLUMNS FROM [Analysis Services Tutorial] ";
string queryCommand = @" SELECT NON EMPTY { [Measures].[Internet Sales Count] } ON COLUMNS, NON EMPTY { ([Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS ) } ON ROWS FROM [Analysis Services Tutorial] ";
AdomdCommand command = new AdomdCommand(queryCommand, connection);
AdomdDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine(dataReader[0].ToString() + " | " + dataReader[1].ToString());
}
dataReader.Close();
connection.Close();
}
void RunMDX(сRequest pRequest, cParameter pParameter)
{
AdomdConnection conn = new AdomdConnection("Data Source=localhost;Catalog=YourDatabase");
conn.Open();
string commandText = @"SELECT FLATTENED
PredictAssociation()
From
[Mining Structure Name]
NATURAL PREDICTION JOIN
(SELECT (SELECT 1 AS [UserId]) AS [Vm]) AS t ";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(Convert.ToString(dr[0]));
}
dr.Close();
conn.Close();
}
public HttpResponseMessage GetDataBar(string[] values)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Ventas], DESC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
[OrderDimension]
}
ON ROWS
";
string CUBO_NAME = "[DHW Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dimension,
datosVenta = ventas,
datosTabla = lstTabla
};
string valoresDimension = string.Empty;
foreach (var item in values)
{
if (item == "1996" || item == "1997" || item == "1998")
{
valoresDimension += "[Dim Tiempo].[Dim Tiempo Año].[" + item + "],";
}
else if (item == "Enero" || item == "Febrero" || item == "Marzo" || item == "Abril" || item == "Mayo" || item == "Junio" || item == "Julio" ||
item == "Agosto" || item == "Septiembre" || item == "Octubre" || item == "Noviembre" || item == "Diciembre")
{
valoresDimension += "[Dim Tiempo].[Dim Tiempo Mes].[" + item + "],";
}
else
{
valoresDimension += "[Dim Cliente].[Dim Cliente Nombre].[" + item + "],";
}
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension);
valoresDimension = @"{" + valoresDimension + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
ventas.Add(Math.Round(dr.GetDecimal(1)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = Math.Round(dr.GetDecimal(1))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage Histograma(string dim, string order = "DESC")
{
string dimension;
List <string> clients = new List <string>();
List <string> employees = new List <string>();
List <string> products = new List <string>();
List <int> years = new List <int>();
switch (dim)
{
case "Cliente":
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
break;
case "Producto":
dimension = "[Dim Producto].[Dim Producto Nombre].CHILDREN";
break;
case "Empleado":
dimension = "[Dim Empleado].[Dim Empleado Nombre].CHILDREN";
break;
case "Año":
dimension = "[Dim Tiempo].[Dim Tiempo Año].CHILDREN";
break;
default:
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
break;
}
string WITH = @"
WITH
SET [TopVentas] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Ventas], " + order +
@")
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
HEAD([TopVentas], 10)
}
ON ROWS
";
string CUBO_NAME = "[DHW Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result2;
switch (dim)
{
case "Cliente":
result2 = new{
datosDimension = clients,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
case "Producto":
result2 = new
{
datosDimension = products,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
case "Empleado":
result2 = new
{
datosDimension = employees,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
case "Año":
result2 = new
{
datosDimension = years,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
default:
result2 = new
{
datosDimension = clients,
datosVenta = ventas,
datosTabla = lstTabla
};
break;
}
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
switch (dim)
{
case "Cliente":
clients.Add(dr.GetString(0));
break;
case "Producto":
products.Add(dr.GetString(0));
break;
case "Empleado":
employees.Add(dr.GetString(0));
break;
case "Año":
years.Add(dr.GetInt32(0));
break;
default:
clients.Add(dr.GetString(0));
break;
}
ventas.Add(Math.Round(dr.GetDecimal(1)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = Math.Round(dr.GetDecimal(1))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result2));
}
public HttpResponseMessage Top5(string dimension, int order, int top)
{
string orderString = "";
switch (order)
{
case 0:
orderString = "ASC";
break;
case 1:
orderString = "DESC";
break;
default:
orderString = "ASC";
break;
}
string topString = "";
if (top > 0)
{
topString = top.ToString();
}
else
{
topString = dimension;
}
var mdxQuery = $@"
WITH SET [TopVentas] AS
NONEMPTY(
ORDER(
{{ {dimension}.CHILDREN}},
[Measures].[VENTAS],{orderString}))
SELECT NON EMPTY
{{
([Measures].[VENTAS])
}} ON COLUMNS , NON EMPTY
{{
HEAD(TopVentas, {topString} )
}} ON ROWS
FROM [PUBS DW]";
List <string> dim = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dim,
datosVenta = ventas,
datosTabla = lstTabla
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn))
{
//cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dim.Add(dr.GetString(0));
ventas.Add(dr.GetDecimal(1));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = dr.GetDecimal(1)
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage GetDataByDimension(string dim, [FromBody] dynamic values)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Ventas], DESC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
([OrderDimension], STRTOSET(@Years), STRTOSET(@Months))
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
List <string> years = new List <string>();
List <string> months = new List <string>();
List <decimal> sales = new List <decimal>();
List <dynamic> tableList = new List <dynamic>();
dynamic result = new
{
dimensionData = dimension,
yearsData = years,
monthsData = months,
salesData = sales,
tableData = tableList
};
string dimensionValues = string.Empty;
Console.WriteLine(values);
foreach (var item in values.clients)
{
dimensionValues += "{0}.[" + item + "],";
}
dimensionValues = dimensionValues.TrimEnd(',');
dimensionValues = string.Format(dimensionValues, dim);
dimensionValues = @"{" + dimensionValues + "}";
string yearsValues = string.Empty;
foreach (var item in values.years)
{
yearsValues += "[Dim Tiempo].[Año].[" + item + "],";
}
yearsValues = yearsValues.TrimEnd(',');
yearsValues = @"{" + yearsValues + "}";
string monthsValues = string.Empty;
foreach (var item in values.months)
{
monthsValues += "[Dim Tiempo].[Mes].[" + item + "],";
}
monthsValues = monthsValues.TrimEnd(',');
monthsValues = @"{" + monthsValues + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dimensionValues);
cmd.Parameters.Add("Years", yearsValues);
cmd.Parameters.Add("Months", monthsValues);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
years.Add(dr.GetString(1));
months.Add(dr.GetString(2));
sales.Add(Math.Round(dr.GetDecimal(3)));
dynamic objTable = new
{
description = dr.GetString(0),
years = dr.GetString(1),
months = dr.GetString(2),
valor = Math.Round(dr.GetDecimal(3))
};
tableList.Add(objTable);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
