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)); }