protected override OlapRow BuildRow(AdomdDataReader rdr)
{
var row = new OlapRow();
row.Caption = rdr.GetString(0).Substring(1, rdr.GetString(0).Length - 2);
row.DisplayFolder = rdr.GetString(1);
return row;
}
public static ColumnRow Load(AdomdDataReader dataReader)
{
// Traverse the response and
// read column 1, "TABLE_SCHEMA"
// read column 2, "TABLE_NAME"
// read column 2, "COLUMN_NAME"
// Get the column value
string perspectiveName = dataReader.GetString(1);
string tableName = dataReader.GetString(2);
string columnName = dataReader.GetString(3);
if (tableName.StartsWith("$") && columnName != "RowNumber")
{
// Get the column value
var row = new ColumnRow();
row.Name = columnName;
return(row);
}
else
{
return(null);
}
}
protected virtual OlapRow BuildRow(AdomdDataReader rdr)
{
var row = new OlapRow();
row.Caption = rdr.GetString(0);
row.DisplayFolder = rdr.GetString(1);
return row;
}
protected virtual OlapRow BuildRow(AdomdDataReader rdr)
{
var row = new OlapRow();
row.Caption = rdr.GetString(0);
row.DisplayFolder = rdr.GetString(1);
return(row);
}
protected override OlapRow BuildRow(AdomdDataReader rdr)
{
var row = new OlapRow();
row.Caption = rdr.GetString(0).Substring(1, rdr.GetString(0).Length - 2);
row.DisplayFolder = rdr.GetString(1);
return(row);
}
protected override OlapRow BuildRow(AdomdDataReader rdr)
{
var row = new DimensionRow();
row.Caption = rdr.GetString(0);
row.DisplayFolder = rdr.GetString(1);
row.DimensionType = rdr.GetInt16(2);
return row;
}
protected override OlapRow BuildRow(AdomdDataReader rdr)
{
var row = new DimensionRow();
row.Caption = rdr.GetString(0);
row.DisplayFolder = rdr.GetString(1);
row.DimensionType = rdr.GetInt16(2);
return(row);
}
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));
}
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));
}
protected void Button1_Click(object sender, EventArgs e)
{
//الاتصال مع قاعدة البيانات
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB";
conn.Open();
// Command
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
//DMX استعلام
cmd.CommandText = String.Format(@"select Predict ([Product Name]) from [Customer_ProductsMM]
natural prediction join
(select '{0}' as [Age],
'{1}' as [Cars],
'{2}' as [Children],
'{3}' as [Commute Distance],
'{4}' as [Education],
'{5}' as [Gender],
'{6}' as [Home Owner],
'{7}' as [Income],
'{8}' as [Marital Status],
'{9}' as [Occupation],
'{10}' as [Region]) as t",
txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem,
RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem);
//إظهار النتيجة
AdomdDataReader adr = cmd.ExecuteReader();
while (adr.Read())
{
txtresult.Text = adr.GetString(0);
}
}
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));
}
protected void Button1_Click(object sender, EventArgs e)
{
//الاتصال مع قاعدة البيانات
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB";
conn.Open();
//تعريف الاستعلام
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
// DMX استعلام
cmd.CommandText = String.Format(@"select flattened predicthistogram([Buy]) from [CustomersBayesMM]
natural prediction join
(select '{0}' as [Age],
'{1}' as [Cars],
'{2}' as [Children],
'{3}' as [Commute Distance],
'{4}' as [Education],
'{5}' as [Gender],
'{6}' as [Home Owner],
'{7}' as [Income],
'{8}' as [Marital Status],
'{9}' as [Occupation],
'{10}' as [Region]) as t",
txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem,
RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem);
AdomdDataReader adr = cmd.ExecuteReader();
//إظهار النتيجة
if (adr.Read())
{
txtresult.Text = adr.GetString(0) + " With " + Convert.ToString(Math.Round(Convert.ToDecimal(adr.GetString(2)) * 100, 2)) + " %";
}
if (adr.Read())
{
txtresult0.Text = adr.GetString(0) + " With " + Convert.ToString(Math.Round(Convert.ToDecimal(adr.GetString(2)) * 100, 2)) + " %";
}
}
public static TableRow Load(AdomdDataReader dataReader)
{
// Traverse the response and
// read column 1, "TABLE_SCHEMA"
// read column 2, "TABLE_NAME"
// Get the column value
string perspectiveName = dataReader.GetString(1);
string tableName = dataReader.GetString(2);
if (tableName.StartsWith("$"))
{
// Get the column value
var row = new TableRow();
row.Name = tableName.Substring(1);
return row;
}
else
return null;
}
public static DimensionRow LoadLinkedTo(AdomdDataReader dataReader)
{
// read column 2, "CUBE_NAME"
// read column 5, "DIMENSION_UNIQUE_NAME"
// read column 6, "DIMENSION_CAPTION"
// read column 7, "DIMENSION_IS_VISIBLE"
var perspectiveName = (string)dataReader.GetValue(2);
if ((bool)dataReader.GetValue(7) && !perspectiveName.StartsWith("$"))
{
// Get the columns value
var row = new DimensionRow();
row.PerspectiveName = perspectiveName;
row.UniqueName = dataReader.GetString(5);
row.Caption = dataReader.GetString(5).Replace("[", "").Replace("]", "");
return row;
}
else
return null;
}
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 static ColumnRow Load(AdomdDataReader dataReader)
{
// Traverse the response and
// read column 1, "TABLE_SCHEMA"
// read column 2, "TABLE_NAME"
// read column 2, "COLUMN_NAME"
// Get the column value
string perspectiveName = dataReader.GetString(1);
string tableName = dataReader.GetString(2);
string columnName = dataReader.GetString(3);
if (tableName.StartsWith("$") && columnName!="RowNumber")
{
// Get the column value
var row = new ColumnRow();
row.Name = columnName;
return row;
}
else
return null;
}
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 static DimensionRow LoadLinkedTo(AdomdDataReader dataReader)
{
// read column 2, "CUBE_NAME"
// read column 5, "DIMENSION_UNIQUE_NAME"
// read column 6, "DIMENSION_CAPTION"
// read column 7, "DIMENSION_IS_VISIBLE"
var perspectiveName = (string)dataReader.GetValue(2);
if ((bool)dataReader.GetValue(7) && !perspectiveName.StartsWith("$"))
{
// Get the columns value
var row = new DimensionRow();
row.PerspectiveName = perspectiveName;
row.UniqueName = dataReader.GetString(5);
row.Caption = dataReader.GetString(5).Replace("[", "").Replace("]", "");
return(row);
}
else
{
return(null);
}
}
public static TableRow Load(AdomdDataReader dataReader)
{
// Traverse the response and
// read column 1, "TABLE_SCHEMA"
// read column 2, "TABLE_NAME"
// Get the column value
string perspectiveName = dataReader.GetString(1);
string tableName = dataReader.GetString(2);
if (tableName.StartsWith("$"))
{
// Get the column value
var row = new TableRow();
row.Name = tableName.Substring(1);
return(row);
}
else
{
return(null);
}
}
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 HttpResponseMessage GetItemByDimension(string dim)
{
string dimension = string.Empty;
string dim2 = string.Empty;
switch (dim)
{
case "Cliente":
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
dim2 = "[Dim Cliente].[Dim Cliente Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
case "Producto":
dimension = "[Dim Producto].[Dim Producto Nombre].CHILDREN";
dim2 = "[Dim Producto].[Dim Producto Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
case "Empleado":
dimension = "[Dim Empleado].[Dim Empleado Nombre].CHILDREN";
dim2 = "[Dim Empleado].[Dim Empleado Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
case "Tiempo":
dimension = "[Dim Tiempo].[Anio].CHILDREN";
dim2 = "[Dim Tiempo].[Anio].CURRENTMEMBER.MEMBER_NAME";
break;
default:
dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN";
dim2 = "[Dim Cliente].[Dim Cliente Nombre].CURRENTMEMBER.MEMBER_NAME";
break;
}
string WITH = @"
WITH
SET [OrderDImension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
" + dim2 + @", ASC
)
)
";
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> clients = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = clients,
datosVenta = ventas,
datosTabla = lstTabla
};
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())
{
clients.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 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));
}
public HttpResponseMessage GetDataGeneric(string dim, string[] anios, string[] meses, string[] values)
{
string dimension = string.Empty;
switch (dim)
{
case "Cliente":
dimension = "[Dim Cliente].[Dim Cliente Nombre]";
break;
case "Producto":
dimension = "[Dim Producto].[Dim Producto Nombre]";
break;
case "Empleado":
dimension = "[Dim Empleado].[Dim Empleado Nombre]";
break;
default:
dimension = "[Dim Cliente].[Dim Cliente Nombre]";
break;
}
string WITH = @"
WITH
SET [Info] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Hec Ventas Ventas], DESC
)
)
";
string COLUMNS = @"
{
STRTOSET(@Anio)
}
ON COLUMNS,
";
string ROWS = @"
{
[Info]
}*
{
STRTOSET(@Mes)
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
List <string> dimen = new List <string>();
List <decimal> ventas = new List <decimal>();
List <string> mes = new List <string>();
List <string> anio = new List <string>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dimen,
datosVenta = ventas,
datosMes = mes,
datosAnio = anio,
datosTabla = lstTabla
};
string valoresDimension = string.Empty;
foreach (var item in values)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dimension);
valoresDimension = @"{" + valoresDimension + "}";
string valoresMes = string.Empty;
foreach (var item in meses)
{
valoresMes += "[Dim Tiempo].[Mes Espaniol].[" + item + "],";
}
valoresMes = valoresMes.TrimEnd(',');
//valoresMes = @"{" + valoresMes + "}";
string valoresAnio = string.Empty;
foreach (var item in anios)
{
valoresAnio += "[Dim Tiempo].[Anio].[" + item + "],";
}
valoresAnio = valoresAnio.TrimEnd(',');
//valoresAnio = @"{" + valoresAnio + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
cmd.Parameters.Add("Anio", valoresAnio);
cmd.Parameters.Add("Mes", valoresMes);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimen.Add(dr.GetString(0));
ventas.Add(Math.Round(dr.GetDecimal(1)));
mes.Add(dr.GetString(2));
anio.Add(dr.GetString(3));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
data = Math.Round(dr.GetDecimal(1)),
label = dr.GetString(3)
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage Top5(string dimension)
{
string nombreDimension = @"";
switch (dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } ";
break;
default: //Dimension por Default
nombreDimension = @"{ [Dim Cliente].[Dim Cliente 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],5)
}
ON ROWS";
string CUBO_NAME = @"[DWH Northwind]";
string MDXQuery = WITH +
@"SELECT " +
COLUMNS +
ROWS +
" FROM " + CUBO_NAME;
Dictionary <string, decimal> structure = new Dictionary <string, decimal>();
List <string> name = new List <string>();
List <decimal> value = new List <decimal>();
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())
{
//structure.Add(dr.GetString(0), dr.GetDecimal(1));
name.Add(dr.GetString(0));
value.Add(dr.GetDecimal(1));
//Debug.Write(structure);
}
dr.Close();
}
}
}
var data = (name, value);
return(Request.CreateResponse(HttpStatusCode.OK, data));
}
public HttpResponseMessage SerieHistoricaPost(Filtro objFiltro)
{
//Debug.Write(objFiltro);
//return Request.CreateResponse(HttpStatusCode.OK, objFiltro);
//Se puso en filtro 0 para no tomarlo en cuenta y traer todas las de la categoría, sin embargo en el MDX no funciona, se trae todas las ventas
string nombreDimension = @"";
if (objFiltro.Item == "0")
{
switch (objFiltro.Dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[QUICK-Stop], [Dim Cliente].[Dim Cliente Nombre].&[Ernst Handel], [Dim Cliente].[Dim Cliente Nombre].&[Save-a-lot Markets], [Dim Cliente].[Dim Cliente Nombre].&[Rattlesnake Canyon Grocery], [Dim Cliente].[Dim Cliente Nombre].&[Hungry Owl All-Night Grocers] } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[Côte de Blaye], [Dim Producto].[Dim Producto Nombre].&[Thüringer Rostbratwurst], [Dim Producto].[Dim Producto Nombre].&[Raclette Courdavault], [Dim Producto].[Dim Producto Nombre].&[Tarte au sucre], [Dim Producto].[Dim Producto Nombre].&[Camembert Pierrot] } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[Beverages], [Dim Producto].[Dim Producto Categoria].&[Dairy Products], [Dim Producto].[Dim Producto Categoria].&[Confections], [Dim Producto].[Dim Producto Categoria].&[Meat/Poultry], [Dim Producto].[Dim Producto Categoria].&[Seafood] } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[Margaret Peacock], [Dim Empleado].[Dim Empleado Nombre].&[Janet Leverling], [Dim Empleado].[Dim Empleado Nombre].&[Nancy Davolio], [Dim Empleado].[Dim Empleado Nombre].&[Andrew Fuller], [Dim Empleado].[Dim Empleado Nombre].&[Laura Callahan] } ";
break;
default: //Dimension por Default
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[QUICK-Stop], [Dim Cliente].[Dim Cliente Nombre].&[Ernst Handel], [Dim Cliente].[Dim Cliente Nombre].&[Save-a-lot Markets], [Dim Cliente].[Dim Cliente Nombre].&[Rattlesnake Canyon Grocery], [Dim Cliente].[Dim Cliente Nombre].&[Hungry Owl All-Night Grocers] } ";
break;
}
}
else
{
if (objFiltro.Item == "Meat")
{
objFiltro.Item = "Meat/Poultry";
}
switch (objFiltro.Dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[" + objFiltro.Item + "] } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[" + objFiltro.Item + "] } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[" + objFiltro.Item + "] } ";
break;
default: //Dimension por Default
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } ";
break;
}
}
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
}
*
{
STRTOSET(@Dimension)
}
ON ROWS";
string CUBO_NAME = @"[DWH Northwind]";
string MDXQuery = @"SELECT " +
COLUMNS +
ROWS +
" FROM " + CUBO_NAME;
Dictionary <string, decimal> structure = new Dictionary <string, decimal>();
List <string> year = new List <string>();
List <string> month = new List <string>();
List <decimal> value = new List <decimal>();
List <string> filter = new List <string>();
dynamic data;
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())
{
year.Add(dr.GetString(0));
month.Add(dr.GetString(1));
filter.Add(dr.GetString(2));
value.Add(Math.Round(dr.GetDecimal(3)));
}
dr.Close();
}
}
data = new { years = year, months = month, filters = filter, values = value };
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)data));
}
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 getClients()
{
string[] dimensions = new string[] { "[Dim Cliente].[Dim Cliente Nombre]", "[Dim Tiempo].[Dim Tiempo Año]", "[Dim Tiempo].[Dim Tiempo Mes]" };
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
{0}.CHILDREN,
{0}.CURRENTMEMBER.MEMBER_NAME,
ASC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Fact Ventas Netas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
[OrderDimension]
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
//Debug.Write(MDX_QUERY);
List <string> clients = new List <string>();
List <string> months = new List <string>();
List <string> years = new List <string>();
dynamic result = new
{
clientes = clients,
meses = months,
anios = years
};
for (int i = 0; i < dimensions.Length; i++)
{
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
string NEW_WITH = string.Format(WITH, dimensions[i]);
string MDX_QUERY = NEW_WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
//cmd.Parameters.Add("Dimension", dimensions[i]);
Console.WriteLine(MDX_QUERY);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
switch (i)
{
case 0:
clients.Add(dr.GetString(0));
break;
case 1:
years.Add(dr.GetString(0));
break;
case 2:
months.Add(dr.GetString(0));
break;
}
}
dr.Close();
}
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage Top5DimensionsPie(string dim, string año = "", string mes = "", string order = "DESC")
{
string dimension = string.Empty;
string DimensionMes = "{(";
string DimensionAño = "";
dimension = dim + ".CHILDREN";
DimensionMes = "{([Dim Tiempo].[Mes Espaniol].[" + mes + "],";
DimensionAño = "[Dim Tiempo].[Anio].[" + año + "],";
//Consulta MDX concatenando El mes + el año y la dimension que se esta seleccionando.
string WITH = @"
WITH
SET [TopVentas] AS
NONEMPTY(
ORDER(
" + DimensionMes + @" " + DimensionAño + @"STRTOSET(@Dimension))},
[Measures].[Hec Ventas Ventas], " + order + @"))";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Hec Ventas Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
HEAD([TopVentas], 5)
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> clientes = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = clientes,
datosVenta = ventas,
datosTabla = lstTabla
};
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())
{
int conteo = dr.FieldCount;
clientes.Add(dr.GetString(conteo - 2));
ventas.Add(decimal.Parse(dr.GetString(conteo - 1)));
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public Task OutputResultsAsync(IQueryRunner runner)
{
var dlg = new Microsoft.Win32.SaveFileDialog
{
DefaultExt = ".txt",
Filter = "Tab separated text file|*.txt|Comma separated text file - UTF8|*.csv|Comma separated text file - Unicode|*.csv"
};
string fileName = "";
// Show save file dialog box
var result = dlg.ShowDialog();
// Process save file dialog box results
if (result == true)
{
// Save document
fileName = dlg.FileName;
return(Task.Run(() =>
{
try
{
runner.OutputMessage("Query Started");
var sw = Stopwatch.StartNew();
string sep = "\t";
string decimalSep = System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator;
string isoDateFormat = string.Format("yyyy-MM-dd HH:mm:ss{0}000", decimalSep);
Encoding enc = Encoding.UTF8;
switch (dlg.FilterIndex)
{
case 1: // tab separated
sep = "\t";
break;
case 2: // utf-8 csv
sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
break;
case 3: //unicode csv
enc = Encoding.Unicode;
sep = System.Globalization.CultureInfo.CurrentUICulture.TextInfo.ListSeparator;
break;
}
var dq = runner.QueryText;
//var res = runner.ExecuteDataTableQuery(dq);
AdomdDataReader res = runner.ExecuteDataReaderQuery(dq);
if (res != null)
{
sw.Stop();
var durationMs = sw.ElapsedMilliseconds;
//runner.ResultsTable = res;
runner.OutputMessage("Command Complete, writing output file");
var sbLine = new StringBuilder();
bool moreResults = true;
int iFileCnt = 1;
while (moreResults)
{
int iMaxCol = res.FieldCount - 1;
int iRowCnt = 0;
if (iFileCnt > 1)
{
fileName = AddFileCntSuffix(fileName, iFileCnt);
}
using (var writer = new StreamWriter(File.Open(fileName, FileMode.Create), enc))
{
// write out clean column names
writer.WriteLine(string.Join(sep, res.CleanColumnNames()));
// write out data
while (res.Read())
{
iRowCnt++;
for (int iCol = 0; iCol < res.FieldCount; iCol++)
{
switch (res.GetDataTypeName(iCol))
{
case "Decimal":
case "Int64":
if (!res.IsDBNull(iCol))
{
sbLine.Append(res.GetString(iCol));
}
break;
case "DateTime":
if (res.IsDBNull(iCol))
{
sbLine.Append("\"\"");
}
else
{
sbLine.Append(res.GetDateTime(iCol).ToString(isoDateFormat));
} // ISO date format
break;
default:
sbLine.Append("\"");
if (!res.IsDBNull(iCol))
{
sbLine.Append(res.GetString(iCol).Replace("\"", "\"\"").Replace("\n", " "));
}
sbLine.Append("\"");
break;
}
if (iCol < iMaxCol)
{
sbLine.Append(sep);
}
}
writer.WriteLine(sbLine);
sbLine.Clear();
if (iRowCnt % 1000 == 0)
{
runner.NewStatusBarMessage(string.Format("Written {0:n0} rows to the file output", iRowCnt));
}
}
}
runner.OutputMessage(
string.Format("Query Completed ({0:N0} row{1} returned)"
, iRowCnt
, iRowCnt == 1 ? "" : "s"), durationMs);
runner.RowCount = iRowCnt;
moreResults = res.NextResult();
iFileCnt++;
}
runner.SetResultsMessage("Query results written to file", OutputTargets.Grid);
//runner.QueryCompleted();
runner.ActivateOutput();
}
res.Close();
}
catch (Exception ex)
{
runner.ActivateOutput();
runner.OutputError(ex.Message);
#if DEBUG
runner.OutputError(ex.StackTrace);
#endif
}
finally
{
runner.QueryCompleted();
}
}));
}
// else dialog was cancelled so return an empty task.
return(Task.Run(() => { }));
}
public HttpResponseMessage TablaComparativa(Filtro objFiltro)
{
string nombreDimension = @"";
switch (objFiltro.Dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[" + objFiltro.Item + "] } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[" + objFiltro.Item + "] } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[" + objFiltro.Item + "] } ";
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
{
(
[Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN,
[Dim Tiempo].[Dim Tiempo Año].CHILDREN
)
}
*
{
HEAD([TopVentas],1000)
}
ON ROWS";
string CUBO_NAME = @"[DWH Northwind]";
string MDXQuery = WITH +
@"SELECT " +
COLUMNS +
ROWS +
" FROM " + CUBO_NAME;
List <string> fecha = new List <string>();
List <decimal> valor = new List <decimal>();
dynamic data;
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())
{
fecha.Add(dr.GetString(0) + "-" + dr.GetString(1));
valor.Add(Math.Round(dr.GetDecimal(3)));
}
dr.Close();
}
}
data = new { fecha = fecha, valor = valor };
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)data));
}
public HttpResponseMessage GetDataPieByDimension(string dim, string order, [FromBody] dynamic values)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
STRTOSET(@Dimension),
[Measures].[Fact Ventas Netas], DESC
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Fact Ventas Netas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
([OrderDimension], STRTOSET(@Anios), STRTOSET(@Meses))
}
ON ROWS
";
string CUBO_NAME = "[DWH 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>();
List <string> anios = new List <string>();
List <string> meses = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dimension,
datosAnios = anios,
datosMeses = meses,
datosVenta = ventas,
datosTabla = lstTabla
};
string valoresDimension = string.Empty;
Console.WriteLine(values);
foreach (var item in values.clients)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dim);
valoresDimension = @"{" + valoresDimension + "}";
string valoresAnios = string.Empty;
foreach (var item in values.years)
{
valoresAnios += "[Dim Tiempo].[Dim Tiempo Año].[" + item + "],";
}
valoresAnios = valoresAnios.TrimEnd(',');
valoresAnios = @"{" + valoresAnios + "}";
string valoresMeses = string.Empty;
foreach (var item in values.months)
{
valoresMeses += "[Dim Tiempo].[Dim Tiempo Mes].[" + item + "],";
}
valoresMeses = valoresMeses.TrimEnd(',');
valoresMeses = @"{" + valoresMeses + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
cmd.Parameters.Add("Anios", valoresAnios);
cmd.Parameters.Add("Meses", valoresMeses);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
anios.Add(dr.GetString(1));
meses.Add(dr.GetString(2));
ventas.Add(Math.Round(dr.GetDecimal(3)));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
años = dr.GetString(1),
meses = dr.GetString(2),
valor = Math.Round(dr.GetDecimal(3))
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public HttpResponseMessage Combos(Filtro objFiltro)
{
string nombreDimension = @"";
switch (objFiltro.Dimension)
{
case "1": // Clientes
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } ";
break;
case "2": // Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } ";
break;
case "3": // Categorías
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } ";
break;
case "4": // Empleados
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } ";
break;
}
string COLUMNS = @"NON EMPTY
{
[Measures].[Fact Ventas Netas]
}
ON COLUMNS,";
string ROWS = @"NON EMPTY
{
STRTOSET(@Dimension)
}
ON ROWS";
string CUBO_NAME = @"[DWH Northwind]";
string MDXQuery = @"SELECT " +
COLUMNS +
ROWS +
" FROM " + CUBO_NAME;
Dictionary <int, string> structure = new Dictionary <int, string>();
List <int> consecutivo = new List <int>();
List <string> valor = new List <string>();
var contador = 1;
dynamic data;
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())
{
structure.Add(contador, dr.GetString(0));
consecutivo.Add(contador);
valor.Add(dr.GetString(0));
contador += 1;
}
dr.Close();
}
}
//data = new { id = consecutivo, itemName = valor };
}
//return Request.CreateResponse(HttpStatusCode.OK, (object)data);
return(Request.CreateResponse(HttpStatusCode.OK, structure));
}
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 GetDataMonthByDimension(string dim, string year, string month, string order, ConsultaMes data)
{
string WITH = @"
WITH
SET [Items] AS
{
STRTOSET(@Dimension)
}
";
string COLUMNS = @"
NON EMPTY
{
STRTOSET(@Year)
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
ORDER(
[Items],
[Measures].[Hec Ventas Ventas], " + order +
@")
}
*
{
STRTOSET(@Month)
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> anio = new List <string>();
List <dynamic> lstTabla = new List <dynamic>();
string valoresDimension = string.Empty;
string valoresYear = string.Empty;
string valoresMonth = string.Empty;
foreach (var item in data.Dimension)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dim);
valoresDimension = @"{" + valoresDimension + "}";
foreach (var item in data.Year)
{
valoresYear += "{0}.[" + item + "],";
}
valoresYear = valoresYear.TrimEnd(',');
valoresYear = string.Format(valoresYear, year);
valoresYear = @"{" + valoresYear + "}";
foreach (var item in data.Month)
{
valoresMonth += "{0}.[" + item + "],";
}
valoresMonth = valoresMonth.TrimEnd(',');
valoresMonth = string.Format(valoresMonth, month);
valoresMonth = @"{" + valoresMonth + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
cmd.Parameters.Add("Year", valoresYear);
cmd.Parameters.Add("Month", valoresMonth);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
switch (dr.FieldCount)
{
case 2:
dynamic objTabla = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)) },
total = getDecimalHelper(dr.GetValue(2)),
};
lstTabla.Add(objTabla);
break;
case 3:
dynamic objTabla2 = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)), getDecimalHelper(dr.GetValue(3)) },
total = getDecimalHelper(dr.GetValue(2)) + getDecimalHelper(dr.GetValue(3)),
};
lstTabla.Add(objTabla2);
break;
case 4:
dynamic objTabla3 = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)), getDecimalHelper(dr.GetValue(3)), getDecimalHelper(dr.GetValue(4)) },
total = getDecimalHelper(dr.GetValue(2)) + getDecimalHelper(dr.GetValue(3)) + getDecimalHelper(dr.GetValue(4))
};
lstTabla.Add(objTabla3);
break;
}
}
switch (dr.FieldCount)
{
case 4:
anio.Add(dr.GetName(1).Substring(22, 4));
anio.Add(dr.GetName(2).Substring(22, 4));
anio.Add(dr.GetName(3).Substring(22, 4));
break;
case 3:
anio.Add(dr.GetName(1).Substring(22, 4));
anio.Add(dr.GetName(2).Substring(22, 4));
break;
case 2:
anio.Add(dr.GetName(1).Substring(22, 4));
break;
}
dr.Close();
}
}
}
dynamic result = new
{
anio,
sales = lstTabla
};
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}