public static HierarchyRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 3, "DIMENSION_UNIQUE_NAME" // read column 21, "HIERARCHY_IS_VISIBLE" // read column 5, "HIERARCHY_UNIQUE_NAME" // read column 4, "HIERARCHY_NAME" //Don't use the hierarchy caption (prefer the hierarchy name) because for role-playing dimensions the copation wille the NameOfRolePlayingDimension.NameOfHieiarchy // read column 23, "HIERARCHY_DISPLAY_FOLDER" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$") && (bool)dataReader.GetValue(21)) { if (true) //Needed to avoid dimension [Measure] previously filtered { // Get the columns value var row = new HierarchyRow(); row.PerspectiveName = perspectiveName; row.DimensionUniqueName = (string)dataReader.GetValue(3); row.UniqueName = (string)dataReader.GetValue(5); row.Caption = (string)dataReader.GetValue(4); row.DisplayFolder = (string)dataReader.GetValue(23); return row; } } else return null; }
public MeasureByWeek(AdomdDataReader reader, string measure) { var member = reader["[Time].[Week].[Week].[MEMBER_CAPTION]"] as string; var weekStr = member.Substring(_weekPrefix); Week = DateTime.Parse(weekStr); Values = reader.IsDBNull(reader.GetOrdinal(measure)) ? (double?)null : double.Parse(reader[measure].ToString()); }
protected virtual OlapRow BuildRow(AdomdDataReader rdr) { var row = new OlapRow(); row.Caption = rdr.GetString(0); row.DisplayFolder = rdr.GetString(1); return row; }
public static LevelRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 3, "DIMENSION_UNIQUE_NAME" // read column 4, "HIERARCHY_UNIQUE_NAME" // read column 6, "LEVEL_UNIQUE_NAME" // read column 8, "LEVEL_CAPTION" // read column 9, "LEVEL_NUMBER" // read column 15, "LEVEL_IS_VISIBLE" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$") && (bool)dataReader.GetValue(15)) { string dimensionUniqueName = (string)dataReader.GetValue(3); if (true) //Needed to avoid dimension [Measure] previously filtered //Metadata.Perspectives[perspectiveName].Dimensions.ContainsKey(dimensionUniqueName) { // Get the columns value var row = new LevelRow(); row.PerspectiveName = perspectiveName; row.DimensionUniqueName = dimensionUniqueName; row.HierarchyUniqueName = (string)dataReader.GetValue(4); row.UniqueName = (string)dataReader.GetValue(6); row.Caption = (string)dataReader.GetValue(8); row.Number = Convert.ToInt32((uint)dataReader.GetValue(9)); return row; } } else return null; }
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 MeasureRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 4, "MEASURE_UNIQUE_NAME" // read column 5, "MEASURE_CAPTION" // read column 18, "MEASUREGROUP_NAME" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$")) { // Get the column value var row = new MeasureRow(); row.PerspectiveName = perspectiveName; row.MeasureGroupName = (string)dataReader.GetValue(18); row.UniqueName = (string)dataReader.GetValue(4); row.Caption = (string)dataReader.GetValue(5); row.DisplayFolder = (string)dataReader.GetValue(19); return row; } else return null; }
public override void PreExecute() { base.PreExecute(); adoMdConn = new AdomdConnection("Data Source=WHQWSSASQRY1;Catalog=Tyson Analytics"); adoMdConn.Open(); AdomdCommand cmd = new AdomdCommand(Variables.qryCube, adoMdConn); dataReader = cmd.ExecuteReader(); }
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 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 static PerspectiveRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 4, "MEASURE_UNIQUE_NAME" // read column 5, "MEASURE_CAPTION" // read column 18, "MEASUREGROUP_NAME" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$")) { // Get the column value var row = new PerspectiveRow(); row.Name = perspectiveName; return row; } else return null; }
public static MeasureGroupRow Load(AdomdDataReader dataReader) { // Traverse the response and // Traverse the response and // read column 2, "CUBE_NAME" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$")) { var row = new MeasureGroupRow(); if (dataReader.FieldCount == 11) { // read column 18, "MEASUREGROUP_NAME" // read column 5, "DIMENSION_UNIQUE_NAME" // read column 7, "DIMENSION_IS_VISIBLE" // Get the column value row.PerspectiveName = perspectiveName; row.Name = (string)dataReader.GetValue(3); //Check if the linked dimension is visible if ((bool)dataReader.GetValue(7)) row.LinkedDimensionUniqueName = (string)dataReader.GetValue(5); return row; } else { // read column 18, "MEASUREGROUP_NAME" // Get the column value row.PerspectiveName = perspectiveName; row.Name = (string)dataReader.GetValue(18); } return row; } else return null; }
public static DimensionRow Load(AdomdDataReader dataReader) { // read column 2, "CUBE_NAME" // read column 4, "DIMENSION_UNIQUE_NAME" // read column 6, "DIMENSION_CAPTION" // read column 8, "DIMENSION_TYPE" // read column 10, "DEFAULT HIERARCHY" var perspectiveName = (string)dataReader.GetValue(2); if ((short)dataReader.GetValue(8) != 2 && !perspectiveName.StartsWith("$")) { // Get the columns value var row = new DimensionRow(); row.PerspectiveName = perspectiveName; row.UniqueName = (string)dataReader.GetValue(4); row.Caption = (string)dataReader.GetValue(6); row.DefaultHierarchy = (string)dataReader.GetValue(10); return row; } else return null; }
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 AdomdDataReader ExecuteReader(string query, int maxRows) { _runningCommand = _adomdConn.CreateCommand(); _runningCommand.CommandType = CommandType.Text; _runningCommand.CommandText = query; //var dt = new DataTable("DAXResult"); if (_adomdConn.State != ConnectionState.Open) { _adomdConn.Open(); } AdomdDataReader rdr = _runningCommand.ExecuteReader(); rdr.Connection = this; rdr.CommandText = query; return(rdr); //int iRow = 0; //dt.BeginLoadData(); //if (maxRows <= 0) //{ // dt.Load(rdr); //} //else //{ // while (rdr.Read()) // { // DataRow dr = dt.NewRow(); // rdr.GetValues(dr.ItemArray); // dt.ImportRow(dr); // //dt.LoadDataRow(rdr[iRow], LoadOption.OverwriteChanges); // iRow++; // } // dt.EndLoadData(); //} ////while (rdr) //FixColumnNaming(dt); //_runningCommand = null; //return dt; }
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); } }
protected void btnCalculate_Click(object sender, EventArgs e) { // get the values from the form string creditScore = tbCreditScore.Text; string loanToValue = tbLTV.Text; string monthlyIncome = tbIncome.Text; string monthlyDebt = tbDebt.Text; string loanAmount = tbAmount.Text; // calculate dti int dti = (int)((Convert.ToDouble(monthlyDebt) / Convert.ToDouble(monthlyIncome)) * 100); // construct the query string string dmx = @"SELECT PREDICT([Interest Rate]) FROM [InterestRate LR] NATURAL PREDICTION JOIN (SELECT " + creditScore + @" as [Credit Score] , " + loanToValue + @"as [Original CTLV] , " + dti.ToString() + @" as [Original DTI Ratio] , " + loanAmount + @" as [Original UPB] ) as t"; // connect to ssas AdomdConnection conn = new AdomdConnection(@"Data Source=localhost\MD;Catalog=Freddie SSAS"); conn.Open(); AdomdCommand cmd = new AdomdCommand(dmx, conn); AdomdDataReader reader = cmd.ExecuteReader(); reader.Read(); double rate = (double)reader[0]; conn.Close(); lbInterestRate.Text = Math.Round(rate, 2).ToString(); }
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"; //s += "Select '"+input+"' as [movie]"; //s += ") AS [Movies]) As T"; COM.CommandText = s; //Label2.Text = input + "Test"; AdomdDataReader DR = COM.ExecuteReader(); while (DR.Read()) { if (DR[0] != null) { output.Add(DR[0].ToString()); } } DR.Close(); CON.Close(); }
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 [Category Name] from [Customer_CategoryMM] 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()) { txtcatname.Text = adr.GetString(0); } }
public static PropertyRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 3, "DIMENSION_UNIQUE_NAME" // read column 4, "HIERARCHY_UNIQUE_NAME" // read column 5, "LEVEL_UNIQUE_NAME" // read column 7, "PROPERTY_TYPE" (Must be 1) // read column 8, "PROPERTY_NAME" // read column 9, "PROPERTY_CAPTION" // read column 10, "DATA_TYPE" (int value) // read column 23, "PROPERTY_IS_VISIBLE" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$") && (bool)dataReader.GetValue(23) && ((short)dataReader.GetValue(7)) == 1) { // Get the columns value string dimensionUniqueName = (string)dataReader.GetValue(3); if (dimensionUniqueName != "[Measure]") //Needed to avoid dimension previously filtered { var row = new PropertyRow(); row.PerspectiveName = perspectiveName; row.DimensionUniqueName = dimensionUniqueName; row.HierarchyUniqueName = (string)dataReader.GetValue(4); row.LevelUniqueName = (string)dataReader.GetValue(5); row.Name = (string)dataReader.GetValue(8); row.UniqueName = string.Format("{0}.[{1}]", row.LevelUniqueName, row.Name); row.Caption = (string)dataReader.GetValue(9); return row; } } return null; }
public static PropertyRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 3, "DIMENSION_UNIQUE_NAME" // read column 4, "HIERARCHY_UNIQUE_NAME" // read column 5, "LEVEL_UNIQUE_NAME" // read column 7, "PROPERTY_TYPE" (Must be 1) // read column 8, "PROPERTY_NAME" // read column 9, "PROPERTY_CAPTION" // read column 10, "DATA_TYPE" (int value) // read column 23, "PROPERTY_IS_VISIBLE" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$") && (bool)dataReader.GetValue(23) && ((short)dataReader.GetValue(7)) == 1) { // Get the columns value string dimensionUniqueName = (string)dataReader.GetValue(3); if (dimensionUniqueName != "[Measure]") //Needed to avoid dimension previously filtered { var row = new PropertyRow(); row.PerspectiveName = perspectiveName; row.DimensionUniqueName = dimensionUniqueName; row.HierarchyUniqueName = (string)dataReader.GetValue(4); row.LevelUniqueName = (string)dataReader.GetValue(5); row.Name = (string)dataReader.GetValue(8); row.UniqueName = string.Format("{0}.[{1}]", row.LevelUniqueName, row.Name); row.Caption = (string)dataReader.GetValue(9); return(row); } } return(null); }
public static PerspectiveRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 4, "MEASURE_UNIQUE_NAME" // read column 5, "MEASURE_CAPTION" // read column 18, "MEASUREGROUP_NAME" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$")) { // Get the column value var row = new PerspectiveRow(); row.Name = perspectiveName; return(row); } else { return(null); } }
public AdomdDataReader ExecuteReader(string query) { if (_runningCommand != null) { _runningCommand.Dispose(); _runningCommand = null; } _runningCommand = _adomdConn.CreateCommand(); _runningCommand.CommandType = CommandType.Text; _runningCommand.CommandText = query; // TOOO - add parameters to connection if (_adomdConn.State != ConnectionState.Open) { _adomdConn.Open(); } AdomdDataReader rdr = _runningCommand.ExecuteReader(); rdr.Connection = this; rdr.CommandText = query; return(rdr); }
public static LevelRow Load(AdomdDataReader dataReader) { // Traverse the response and // read column 2, "CUBE_NAME" // read column 3, "DIMENSION_UNIQUE_NAME" // read column 4, "HIERARCHY_UNIQUE_NAME" // read column 6, "LEVEL_UNIQUE_NAME" // read column 8, "LEVEL_CAPTION" // read column 9, "LEVEL_NUMBER" // read column 15, "LEVEL_IS_VISIBLE" // Get the column value string perspectiveName = (string)dataReader.GetValue(2); if (!perspectiveName.StartsWith("$") && (bool)dataReader.GetValue(15)) { string dimensionUniqueName = (string)dataReader.GetValue(3); if (true) //Needed to avoid dimension [Measure] previously filtered //Metadata.Perspectives[perspectiveName].Dimensions.ContainsKey(dimensionUniqueName) { // Get the columns value var row = new LevelRow(); row.PerspectiveName = perspectiveName; row.DimensionUniqueName = dimensionUniqueName; row.HierarchyUniqueName = (string)dataReader.GetValue(4); row.UniqueName = (string)dataReader.GetValue(6); row.Caption = (string)dataReader.GetValue(8); row.Number = Convert.ToInt32((uint)dataReader.GetValue(9)); return(row); } } else { return(null); } }
private void EnsureSQLTableExists(SqlConnection conn, string sqlTableName, AdomdDataReader reader) { var strColumns = new StringBuilder(); var schemaTable = reader.GetSchemaTable(); foreach (System.Data.DataRow row in schemaTable.Rows) { var colName = row.Field <string>("ColumnName"); var regEx = System.Text.RegularExpressions.Regex.Match(colName, @".+\[(.+)\]"); if (regEx.Success) { colName = regEx.Groups[1].Value; } colName.Replace('|', '_'); var sqlType = ConvertDotNetToSQLType(row); strColumns.AppendLine($",[{colName}] {sqlType} NULL"); } var cmdText = @" declare @sqlCmd nvarchar(max) IF object_id(@tableName, 'U') is not null BEGIN raiserror('Droping Table ""%s""', 1, 1, @tableName) set @sqlCmd = 'drop table ' + @tableName + char(13) exec sp_executesql @sqlCmd END IF object_id(@tableName, 'U') is null BEGIN declare @schemaName varchar(20) set @sqlCmd = '' set @schemaName = parsename(@tableName, 2) IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = @schemaName) BEGIN set @sqlCmd = 'CREATE SCHEMA ' + @schemaName + char(13) END set @sqlCmd = @sqlCmd + 'CREATE TABLE ' + @tableName + '(' + @columns + ');' raiserror('Creating Table ""%s""', 1, 1, @tableName) exec sp_executesql @sqlCmd END ELSE BEGIN raiserror('Table ""%s"" already exists', 1, 1, @tableName) END "; using (var cmd = new SqlCommand(cmdText, conn)) { cmd.Parameters.AddWithValue("@tableName", sqlTableName); cmd.Parameters.AddWithValue("@columns", strColumns.ToString().TrimStart(',')); cmd.ExecuteNonQuery(); } }
public MeasureByMonth(AdomdDataReader reader, string measure) { var member = reader["[Time].[Month].[Month].[MEMBER_CAPTION]"] as string; Month = DateTime.ParseExact(member, "MMMM yyyy", CultureInfo.InvariantCulture); Values = reader.IsDBNull(reader.GetOrdinal(measure)) ? (double?)null : double.Parse(reader[measure].ToString()); }
internal Mapperlator(AdomdDataReader reader) { this._rator = new Enumerlator(reader); }
protected override Data.Pipeline.Services.ValidationResult Compare(string SourceTable, string TargetTabel, Dictionary <string, string> Params) { Dictionary <string, double> oltpTotals = new Dictionary <string, double>(); Dictionary <string, double> mdxTotals = new Dictionary <string, double>(); string dayCode = Convert.ToDateTime(Params["Date"]).ToString("yyyyMMdd"); #region Getting measuers from oltp using (SqlConnection sqlCon = new SqlConnection(AppSettings.GetConnectionString(this, "OltpDB"))) { sqlCon.Open(); SqlCommand sqlCommand = new SqlCommand( "SELECT SUM(cost),sum(imps),sum(clicks) from " + SourceTable + " where account_id = @Account_ID and Day_Code = @Daycode and Channel_ID = @Channel_ID" ); SqlParameter accountIdParam = new SqlParameter("@Account_ID", System.Data.SqlDbType.Int); SqlParameter daycodeParam = new SqlParameter("@Daycode", System.Data.SqlDbType.Int); SqlParameter channelIdParam = new SqlParameter("@Channel_ID", System.Data.SqlDbType.Int); accountIdParam.Value = Params["AccountID"]; daycodeParam.Value = dayCode; channelIdParam.Value = Params["ChannelID"]; sqlCommand.Parameters.Add(accountIdParam); sqlCommand.Parameters.Add(daycodeParam); sqlCommand.Parameters.Add(channelIdParam); sqlCommand.Connection = sqlCon; using (var _reader = sqlCommand.ExecuteReader()) { progress += 0.5 * (1 - progress); this.ReportProgress(progress); if (!_reader.IsClosed) { while (_reader.Read()) { if (!_reader[0].Equals(DBNull.Value)) { oltpTotals.Add("Cost", Convert.ToDouble(_reader[0])); oltpTotals.Add("Imps", Convert.ToDouble(_reader[1])); oltpTotals.Add("Clicks", Convert.ToDouble(_reader[2])); } } } } } #endregion #region Getting measures from Analysis server (MDX) string admobConnection = this.Instance.Configuration.Options["AdmobConnection"]; AdomdConnection conn = new AdomdConnection(admobConnection); try { conn.Open(); //TO DO : Get Cube Name from DB string CubeName = GetCubeName(Convert.ToInt32(Params["AccountID"])); string mdxCommandText = string.Format(@"Select {{ [Measures].[Impressions],[Measures].[Clicks],[Measures].[Cost]}} On Columns , ( [Accounts Dim].[Accounts].[Account].&[{0}] )On Rows From [{1}] WHERE ([Channels Dim].[Channels].[Channel].&[{2}] ,[Time Dim].[Time Dim].[Day].&[{3}] ) ", Params["AccountID"], CubeName, Params["ChannelID"], Convert.ToDateTime(Params["Date"]).ToString("yyyyMMdd")); AdomdCommand mdxCmd = new AdomdCommand(mdxCommandText, conn); AdomdDataReader mdxReader = mdxCmd.ExecuteReader(CommandBehavior.CloseConnection); while (mdxReader.Read()) { mdxTotals.Add("Imps", Convert.ToDouble(mdxReader[2])); mdxTotals.Add("Clicks", Convert.ToDouble(mdxReader[3])); mdxTotals.Add("Cost", Convert.ToDouble(mdxReader[4])); } mdxReader.Close(); #endregion return(IsEqual(Params, oltpTotals, mdxTotals, "Oltp", "Mdx")); } catch (Exception e) { return(new ValidationResult() { ResultType = ValidationResultType.Error, AccountID = Convert.ToInt32(Params["AccountID"]), Message = e.Message, TargetPeriodStart = Convert.ToDateTime(Params["Date"]), TargetPeriodEnd = Convert.ToDateTime(Params["Date"]), ChannelID = Convert.ToInt32(Params["ChannelID"]), CheckType = this.Instance.Configuration.Name }); } }
internal DaxDataReader(AdomdDataReader reader) { this._reader = reader; }
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 Enumerlator(AdomdDataReader reader) { this._reader = reader; this.init(); }
//string drugDatabase = "Data Source=TJ-PC;Initial Catalog=HDPurch;Integrated Security=True"; private void buttonPredict_Click(object sender, EventArgs e) { if (radioButtonANN.Checked.Equals(true) && !(string.IsNullOrEmpty(dependentVariable.Text))) { //SingletonQuery string dmxQuery = @"SELECT [DrugPricingCostNN].[Drug Cost_] From [DrugPricingCostNN] NATURAL PREDICTION JOIN (SELECT '" + dependentVariable.Text + @"'AS [ASP 2016]) AS t"; //AllResultQuery string AnnResults = "SELECT[DrugPricingCostNN].[Drug Cost_] From [DrugPricingCostNN] PREDICTION JOIN OPENQUERY([HD Purch], " + "'SELECT [Drug_Cost_$], [ASP_2012], [ASP_2013], [ASP_2014], [ASP_2015], [ASP_2016] " + "FROM [dbo].[DrugPricingCost] ') AS t ON " + "[DrugPricingCostNN].[Drug Cost_] = t.[Drug_Cost_$] AND " + "[DrugPricingCostNN].[ASP 2012] = t.[ASP_2012] AND " + "[DrugPricingCostNN].[ASP 2013] = t.[ASP_2013] AND " + "[DrugPricingCostNN].[ASP 2014] = t.[ASP_2014] AND " + "[DrugPricingCostNN].[ASP 2015] = t.[ASP_2015] AND " + "[DrugPricingCostNN].[ASP 2016] = t.[ASP_2016]"; try { //Open a connection to the analysis services server AdomdConnection connection = new AdomdConnection(analysisServer); connection.Open(); //SingletonQuery AdomdCommand command = new AdomdCommand(dmxQuery, connection); AdomdDataReader reader = command.ExecuteReader(); reader.Read(); //Init stringBuilder StringBuilder stringBuilder = new StringBuilder(); //Init a data table //DataGrid grid = new DataGrid(); for (int i = 0; i < reader.FieldCount; i++) { stringBuilder.Append("Predicted " + reader.GetName(i) + "\t"); stringBuilder.Append(reader.GetValue(i).ToString().Trim() + "\t"); //allResultGridView.DataSource = reader; } //Important before accessing any data, call the .Read method while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { //stringBuilder.Append(reader.GetName(i) + "\t"); stringBuilder.Append(reader.GetValue(i).ToString() + "\t"); } stringBuilder.Append("\r\n"); } //Return the predicted value and display it in the text box predictedValue.Text = stringBuilder.ToString(); Console.Write(stringBuilder.ToString()); reader.Close(); connection.Close(); } catch (Exception) { throw; } try { AdomdConnection allANNConnect = new AdomdConnection(analysisServer); allANNConnect.Open(); AdomdCommand allClusterQuery = new AdomdCommand(AnnResults, allANNConnect); AdomdDataReader annReader = allClusterQuery.ExecuteReader(); annReader.Read(); System.Data.DataTable dataTable = new System.Data.DataTable(); dataTable.Load(annReader); algorithmGridView.DataSource = dataTable; allANNConnect.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); throw; } } //Run the Logistic Regression Algorthim else if (radioButtonLR.Checked.Equals(true) && !(string.IsNullOrEmpty(dependentVariable.Text))) { string dmxQuery = @"SELECT [DrugPricingCostLogReg].[Drug Cost_] From [DrugPricingCostLogReg] NATURAL PREDICTION JOIN (SELECT '" + dependentVariable.Text + @"'AS [ASP 2016]) AS t"; //AllResultQuery string LogRegResults = "SELECT[DrugPricingCostLogReg].[Drug Cost_] From [DrugPricingCostLogReg] PREDICTION JOIN OPENQUERY([HD Purch], " + "'SELECT [Drug_Cost_$], [ASP_2012], [ASP_2013], [ASP_2014], [ASP_2015], [ASP_2016] " + "FROM [dbo].[DrugPricingCost] ') AS t ON " + "[DrugPricingCostLogReg].[Drug Cost_] = t.[Drug_Cost_$] AND " + "[DrugPricingCostLogReg].[ASP 2012] = t.[ASP_2012] AND " + "[DrugPricingCostLogReg].[ASP 2013] = t.[ASP_2013] AND " + "[DrugPricingCostLogReg].[ASP 2014] = t.[ASP_2014] AND " + "[DrugPricingCostLogReg].[ASP 2015] = t.[ASP_2015] AND " + "[DrugPricingCostLogReg].[ASP 2016] = t.[ASP_2016]"; try { AdomdConnection connection = new AdomdConnection(analysisServer); connection.Open(); AdomdCommand command = new AdomdCommand(dmxQuery, connection); AdomdDataReader reader = command.ExecuteReader(); reader.Read(); StringBuilder stringBuilder = new StringBuilder(); for (int i = 0; i < reader.FieldCount; i++) { stringBuilder.Append("Predicted " + reader.GetName(i) + "\t"); stringBuilder.Append(reader.GetValue(i).ToString() + "\t"); } //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"); } predictedValue.Text = stringBuilder.ToString(); Console.Write(stringBuilder.ToString()); reader.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); throw; } //Display the results in the grid view tab try { //string drugData = "Select Drug_ID From Drug"; //SqlConnection sqlConnection = new SqlConnection(drugDatabase); AdomdConnection allLogRegConnect = new AdomdConnection(analysisServer); allLogRegConnect.Open(); AdomdCommand allLogRegQuery = new AdomdCommand(LogRegResults, allLogRegConnect); //sqlConnection.Open(); //SqlCommand sqlCommand = new SqlCommand(drugData, sqlConnection); //SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); //sqlDataReader.Read(); AdomdDataReader logRegReader = allLogRegQuery.ExecuteReader(); logRegReader.Read(); System.Data.DataTable dataTable = new System.Data.DataTable(); //dataTable.Columns.Add("Drug_ID"); //dataTable.Columns.Add("Brand_Name"); //dataTable.Columns.Add("Generic_Name"); //dataTable.Columns.Add("Drug_Cost_$"); //System.Data.DataRow row = dataTable.NewRow(); //while (sqlDataReader.Read()) //{ // row["Drug_ID"] = sqlDataReader[0]; // row["Brand_Name"] = sqlDataReader[1]; // row["Generic_Name"] = sqlDataReader[2]; // row["Drug_Cost_$"] = sqlDataReader[3]; // dataTable.Rows.Add(row); //} //dataTable.Load(sqlDataReader); dataTable.Load(logRegReader); algorithmGridView.DataSource = dataTable; allLogRegConnect.Close(); //sqlConnection.Close(); } catch (Exception exp) { MessageBox.Show(exp.ToString()); throw; } } //Run the clustering algorithm else if (radioButtonClustering.Checked.Equals(true) && !(string.IsNullOrEmpty(dependentVariable.Text))) { string dmxQuery = @"SELECT [DrugPricingClustering].[Drug Cost_] From [DrugPricingClustering] NATURAL PREDICTION JOIN (SELECT '" + dependentVariable.Text + @"'AS [ASP 2016]) AS t"; //AllResultQuery string ClusterResults = "SELECT [DrugPricingClustering].[Drug Cost_] From [DrugPricingClustering] PREDICTION JOIN OPENQUERY([HD Purch], " + "'SELECT [Drug_Cost_$], [ASP_2012], [ASP_2013],[ASP_2014],[ASP_2015], [ASP_2016] FROM [dbo].[DrugPricingCost] ') AS t " + "ON " + "[DrugPricingClustering].[Drug Cost_] = t.[Drug_Cost_$] AND " + "[DrugPricingClustering].[ASP 2012] = t.[ASP_2012] AND " + "[DrugPricingClustering].[ASP 2013] = t.[ASP_2013] AND " + "[DrugPricingClustering].[ASP 2014] = t.[ASP_2014] AND " + "[DrugPricingClustering].[ASP 2015] = t.[ASP_2015] AND " + "[DrugPricingClustering].[ASP 2016] = t.[ASP_2016]"; try { AdomdConnection connection = new AdomdConnection(analysisServer); connection.Open(); AdomdCommand command = new AdomdCommand(dmxQuery, connection); AdomdDataReader reader = command.ExecuteReader(); reader.Read(); StringBuilder stringBuilder = new StringBuilder(); for (int i = 0; i < reader.FieldCount; i++) { stringBuilder.Append("Predicted " + reader.GetName(i) + "\t"); stringBuilder.Append(reader.GetValue(i).ToString() + "\t"); } //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"); } predictedValue.Text = stringBuilder.ToString(); //Console.Write(stringBuilder.ToString()); reader.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); throw; } try { AdomdConnection allClusterConnect = new AdomdConnection(analysisServer); allClusterConnect.Open(); AdomdCommand allClusterQuery = new AdomdCommand(ClusterResults, allClusterConnect); AdomdDataReader clusterReader = allClusterQuery.ExecuteReader(); clusterReader.Read(); System.Data.DataTable dataTable = new System.Data.DataTable(); dataTable.Load(clusterReader); algorithmGridView.DataSource = dataTable; allClusterConnect.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); throw; } } else { //Display an error message implying that the user has to select a specific algorithm button MessageBox.Show("Please select an algorithm to commence the prediction & enter a prediction value into the text box"); } }
public void LoadData(AdomdDataReader reader) { ClearGrid(); int iColCount = reader.FieldCount; this.ColumnsCount = iColCount; this.RowsCount = 1; // set the column headers for (int i = 0; i < iColCount; i++) { string sColumn = reader.GetName(i); SetHeaderValue(0, i, sColumn); } int iRow = 1; this.RowsCount = 1; while (reader.Read()) { this.RowsCount++; for(int iCol=0; iCol < iColCount; iCol++) { if (!reader.IsDBNull(iCol)) { string sValue = reader.GetValue(iCol).ToString(); SetCellValue(iRow, iCol, sValue); } else { SetCellValue(iRow, iCol, ""); } } iRow++; } }
public HttpResponseMessage GetOrPost([FromUri] string server, [FromUri] string db, [FromUri(Name = "query")] string queryFromUri = null, [FromUri] int localeIdentifier = 1029, [FromUri] string applicationName = "SSASQueryProxy", [FromUri] int timeout = 120) { // https://weblog.west-wind.com/posts/2013/dec/13/accepting-raw-request-body-content-with-aspnet-web-api // get MDX query from URI or from entire body content string query = queryFromUri ?? Request.Content.ReadAsStringAsync().Result; Trace.TraceInformation($"{Request.GetCorrelationId()} Got query request from IP {HttpContext.Current?.Request.UserHostAddress} for server {server}, " + $"database {db}, locale identifier {localeIdentifier}, application name {applicationName}, timeout {timeout} seconds, " + $"query (first {LIMIT_QUERY_CHARS} characters) \"{query?.Substring(0, Math.Min(LIMIT_QUERY_CHARS, query?.Length ?? 0)) ?? "<empty>"}\""); // check identity presence (should be handled by SaveCredsBasicAuthentication filter) var identity = Request.GetRequestContext().Principal?.Identity as SaveCredsBasicAuthenticationIdentity; if (identity == null) { return(TraceErrorAndPassthrough(Request.CreateErrorResponse(HttpStatusCode.Unauthorized, "Basic authentication credentials not found in request"))); } Trace.TraceInformation($"{Request.GetCorrelationId()} Using username {identity.Name} provided through basic authentication"); // check if server is allowed in configuration if (!_allowedServers.Contains(server.ToUpper())) { return(TraceErrorAndPassthrough(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Parameter server contains forbidden value (not in allowed servers list)"))); } // check parameters for special characters; not exhausting list of possible characters, but should be sufficient if (!Regex.IsMatch(db, @"^[a-zA-Z0-9_-]+$")) { return(TraceErrorAndPassthrough(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Parameter db contains forbidden characters"))); } if (!Regex.IsMatch(applicationName, @"^[a-zA-Z0-9_\-!@#$%^&*()+.:]+$")) { return(TraceErrorAndPassthrough(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Parameter applicationName contains forbidden characters"))); } if (!Regex.IsMatch(identity.Name, @"^[a-zA-Z0-9_\-!#$%^&*()+.]+$")) { return(TraceErrorAndPassthrough(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "User's username contains forbidden characters"))); } if (!Regex.IsMatch(identity.Password, @"^[a-zA-Z0-9_\-!@#$%^&*()+.:]+$")) { return(TraceErrorAndPassthrough(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "User's password contains forbidden characters"))); } Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); // require Pkt Privacy (encrypted connection) // set reasonable default for various SSAS parameters using (AdomdConnection conn = new AdomdConnection($"Data Source='{server}'; Catalog='{db}'; User ID='{identity.Name}'; Password='******'; Protection Level=Pkt Privacy; " + $"Locale Identifier={localeIdentifier}; Application Name='{applicationName}'; " + $"Connect Timeout={timeout}; Timeout={timeout}; " + $"MDX Compatibility=1; MDX Missing Member Mode=Ignore; VisualMode=0;")) { var ds = new DataSet(); ds.EnforceConstraints = false; ds.Tables.Add(); conn.Open(); string commandText = query; using (AdomdCommand cmd = new AdomdCommand(commandText, conn)) { cmd.CommandTimeout = timeout; using (AdomdDataReader dr = cmd.ExecuteReader()) { ds.Tables[0].Load(dr); dr.Close(); } } conn.Close(); stopwatch.Stop(); Trace.TraceInformation($"{Request.GetCorrelationId()} Connection to SSAS and query processing took {stopwatch.Elapsed.TotalMilliseconds} ms, got {ds.Tables[0].Rows.Count} rows"); // will return data in simple structured format, default is JSON return(Request.CreateResponse(HttpStatusCode.OK, ds.Tables[0])); } }
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 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)); }
private void tabPage1_Enter(object sender, EventArgs e) { if (comboBox1.SelectedIndex != 0 && !string.IsNullOrEmpty(comboBox1.Text)) { Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString_Analysis"].ConnectionString); Microsoft.AnalysisServices.AdomdClient.AdomdCommand comand = new AdomdCommand(); conn.Open(); comand.Connection = conn; comand.CommandText = "SELECT FLATTENED PredictTimeSeries(" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "." + campos[1].Trim() + "," + numericUpDown1.Value + ") " + "From " + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13); prev = comand.ExecuteReader(); // Expression expression = new Expression(); // expression = (Expression) x[0]; string val = ""; int j = 1; //CARREGA GRID e valores grafico dataGridView2.Rows.Clear(); foreach (var item in prev) { val += item[1].ToString() + ","; dataGridView2.Rows.Add(j, prev[0].ToString(), prev[1].ToString()); j++; } val = val.Substring(0, val.Length - 1); try { //chartHist.Clear(); chartPrev.Charts[0].SeriesCollection.Delete(0); chartPrev.Charts.Delete(0); chartPrev.Refresh(); } catch { } chartPrev.ResetText(); chartPrev.Charts.Add(0); chartPrev.Charts[0].SeriesCollection.Add(0); chartPrev.Charts[0].Type = OWC11.ChartChartTypeEnum.chChartTypeLine; chartPrev.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimSeriesNames, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), "Quantidade"); chartPrev.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), val); chartPrev.Refresh(); } }
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 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)); }
protected void btn_cluster_Click(object sender, EventArgs e) { AdomdConnection con = new AdomdConnection( "Data Source=.; Catalog=MovieDM"); con.Open(); AdomdCommand com = new AdomdCommand(); com.Connection = con; string str = drop_Education.SelectedValue; str = str.ToString().Replace("'", "''"); string str1 = drop_tv_signal.SelectedValue; str1 = str1.ToString().Replace("'", "''"); string s = string.Format(@"select Predict ([Movie]) from CustomersMoviesMM natural prediction join (select {0} as [Age], '{1}' as [Education Level], '{2}' as [Gender], '{3}' as [Home Ownership], '{4}' as [Internet Connection], '{5}' as [Marital Status], '{6}' as [Movie Selector], {7} as [Num Bathrooms], {8} as [Num Bedrooms], {9} as [Num Cars], {10} as [Num Children], {11} as [Num T Vs], '{12}' as [PPV Freq], '{13}' as [Buying Freq], '{14}' as [Format], '{15}' as [Renting Freq], '{16}' as [Viewing Freq], '{16}' as [Theater Freq], '{17}' as [TV Movie Freq], '{18}' as [TV Signal] ) as t ", txt_age.Text, str,//drop_Education.SelectedValue, drop_gender.SelectedValue, drop_home.SelectedValue, drop_internet.SelectedValue, drop_marital.SelectedValue, drop_movie_s.SelectedValue, txt_bathrooms.Text, txt_bedrooms.Text, txt_cars.Text, txt_Children.Text, txt_tvs.Text, drop_ppv.SelectedValue, drop_buying.SelectedValue, drop_format.SelectedValue, drop_rent_f.SelectedValue, drop_viewing_f.SelectedValue, drop_theater.SelectedValue, drop_tv_movie.SelectedValue, str1//drop_tv_signal.SelectedValue ); com.CommandText = s; AdomdDataReader dr = com.ExecuteReader(); if (dr.Read()) { if (dr[0] != null) { lbl_result.Text = dr[0].ToString(); } } dr.Close(); con.Close(); }
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 void Visit(MetadataInfo.DaxMetadata daxMetadata) { string ssasVersion = GetSsasVersion(); Product productInfo = GetProduct(ssasVersion); daxMetadata.Version = new MetadataInfo.SsasVersion { SSAS_VERSION = ssasVersion, CAPTURE_DATE = DateTime.Now, PRODUCT_TYPE = productInfo.Type, PRODUCT_NAME = productInfo.Name }; AdomdDataReader result = _conn.ExecuteReader("SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS"); while (result.Read()) { // Filters only DAX functions int?origin = GetInt(result, result.GetOrdinal("ORIGIN")); if (origin == null) { continue; } if (origin != 3 && origin != 4) { continue; } var SSAS_VERSION = ssasVersion; var FUNCTION_NAME = GetString(result, result.GetOrdinal("FUNCTION_NAME")); var DESCRIPTION = GetString(result, result.GetOrdinal("DESCRIPTION")); var PARAMETER_LIST = GetString(result, result.GetOrdinal("PARAMETER_LIST")); var RETURN_TYPE = GetInt(result, result.GetOrdinal("RETURN_TYPE")); var ORIGIN = origin; var INTERFACE_NAME = GetString(result, result.GetOrdinal("INTERFACE_NAME")); var LIBRARY_NAME = GetString(result, result.GetOrdinal("LIBRARY_NAME")); var DLL_NAME = GetString(result, result.GetOrdinal("DLL_NAME")); var HELP_FILE = GetString(result, result.GetOrdinal("HELP_FILE")); var HELP_CONTEXT = GetInt(result, result.GetOrdinal("HELP_CONTEXT")); var OBJECT = GetString(result, result.GetOrdinal("OBJECT")); var CAPTION = GetString(result, result.GetOrdinal("CAPTION")); var PARAMETERINFO = GetXmlString(result, result.GetOrdinal("PARAMETERINFO")); var DIRECTQUERY_PUSHABLE = (result.FieldCount >= 14 ? GetInt(result, result.GetOrdinal("DIRECTQUERY_PUSHABLE")) : null); var function = new MetadataInfo.DaxFunction { SSAS_VERSION = ssasVersion, FUNCTION_NAME = GetString(result, result.GetOrdinal("FUNCTION_NAME")), DESCRIPTION = GetString(result, result.GetOrdinal("DESCRIPTION")), PARAMETER_LIST = GetString(result, result.GetOrdinal("PARAMETER_LIST")), RETURN_TYPE = GetInt(result, result.GetOrdinal("RETURN_TYPE")), ORIGIN = origin, INTERFACE_NAME = GetString(result, result.GetOrdinal("INTERFACE_NAME")), LIBRARY_NAME = GetString(result, result.GetOrdinal("LIBRARY_NAME")), DLL_NAME = GetString(result, result.GetOrdinal("DLL_NAME")), HELP_FILE = GetString(result, result.GetOrdinal("HELP_FILE")), HELP_CONTEXT = GetInt(result, result.GetOrdinal("HELP_CONTEXT")), OBJECT = GetString(result, result.GetOrdinal("OBJECT")), CAPTION = GetString(result, result.GetOrdinal("CAPTION")), PARAMETERINFO = GetXmlString(result, result.GetOrdinal("PARAMETERINFO")), DIRECTQUERY_PUSHABLE = (result.FieldCount >= 14 ? GetInt(result, result.GetOrdinal("DIRECTQUERY_PUSHABLE")) : null) }; daxMetadata.DaxFunctions.Add(function); } }
// GET: Actors/Details/5 public ActionResult Details(int?id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } Actors actors = db.Actors.Find(id); if (actors == null) { return(HttpNotFound()); } //Start section DataMining :Who are actor’s friends try { AdomdConnection CON = new AdomdConnection("Data Source=ABOREDA; Initial catalog=MoviesDB"); CON.Open(); AdomdCommand COM = CON.CreateCommand(); string s = @"SELECT Flattened PREDICT([Actors], 5) FROM CustomersMMAssociationActors NATURAL PREDICTION JOIN (select (select '" + actors.Name.ToString() + "' as [Actor]) AS[Actors]) As T"; COM.CommandText = s; List <string> listofNameActorsRelated = new List <string>(); AdomdDataReader DR = COM.ExecuteReader(); while (DR.Read()) { if (DR[0] != null) { listofNameActorsRelated.Add(DR[0].ToString()); } } DR.Close(); CON.Close(); var Listactors = new List <Actors>(); foreach (var item in db.Actors) { for (int i = 0; i < listofNameActorsRelated.Count; i++) { if (item.Name.Equals(listofNameActorsRelated[i])) { Listactors.Add(item); } } } ViewBag.ActorRelated = Listactors; ViewBag.listofNameActorsRelated = listofNameActorsRelated; ViewBag.ConnectionDataBase = true; } catch (Exception) { ViewBag.ConnectionDataBase = false; // throw; } //End section DataMining :Who are actor’s friends return(View(actors)); }
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)); }
// GET: Movies/Details/5 public ActionResult Details(int?id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } Movie movie = db.Movies.Find(id); if (movie == null) { return(HttpNotFound()); } ViewBag.MovieID = id; // الافلام مشترك فيها الممثل var Results = from b in db.Actors select new { b.Id, b.Name, Checked = (( from ab in db.MoviesToActors where (ab.MoviesID == id) & (ab.ActorsID == b.Id) select ab).Count() > 0) }; var MyViewModel = new MoviesViewModel(); MyViewModel.ID = id.Value; MyViewModel.Name = movie.Name; MyViewModel.Image = movie.Image; MyViewModel.LinkDownload = movie.LinkDownload; MyViewModel.Sight = movie.Sight; MyViewModel.Price = movie.Price; MyViewModel.CategoryId = movie.CategoryId; MyViewModel.DirectorId = movie.DirectorId; var MyCheckBoxList = new List <CheckBoxViewModel>(); foreach (var item in Results) { MyCheckBoxList.Add(new CheckBoxViewModel { ID = item.Id, Name = item.Name, Checked = item.Checked }); } MyViewModel.Actors = MyCheckBoxList; ViewBag.MovieID = id.Value; var comments = db.CommentsMovies.Where(d => d.MovieID.Equals(id.Value)).ToList(); ViewBag.Comments = comments; var CategoryName = db.Categories.Where(d => d.Id.Equals(movie.CategoryId)).ToList();; ViewBag.CategoryName = CategoryName; var DirectorName = db.Directors.Where(d => d.Id.Equals(movie.DirectorId)).ToList();; ViewBag.DirectorName = DirectorName; var ratings = db.CommentsMovies.Where(d => d.MovieID.Equals(id.Value)).ToList(); if (ratings.Count() > 0) { var ratingSum = ratings.Sum(d => d.Rating); ViewBag.RatingSum = ratingSum; var ratingCount = ratings.Count(); ViewBag.RatingCount = ratingCount; } else { ViewBag.RatingSum = 0; ViewBag.RatingCount = 0; } //Start section DataMining :Customers Who Bought This Item Also Bought try { AdomdConnection CON = new AdomdConnection("Data Source=ABOREDA; Initial catalog=MoviesDB"); CON.Open(); AdomdCommand COM = CON.CreateCommand(); string s = @"SELECT Flattened PREDICT([Movies], 25) FROM CustomersMMAssociation NATURAL PREDICTION JOIN (select (select '" + movie.Name.ToString() + "' as [Movie]) AS[Movies]) As T"; COM.CommandText = s; List <string> listofNameMoviesRelated = new List <string>(); AdomdDataReader DR = COM.ExecuteReader(); while (DR.Read()) { if (DR[0] != null) { listofNameMoviesRelated.Add(DR[0].ToString()); } } DR.Close(); CON.Close(); var movies = new List <Movie>(); foreach (var item in db.Movies) { for (int i = 0; i < listofNameMoviesRelated.Count; i++) { if (item.Name.Equals(listofNameMoviesRelated[i])) { movies.Add(item); } } } ViewBag.MovieRelated = movies; ViewBag.ConnectionDataBase = true; } catch (Exception) { ViewBag.ConnectionDataBase = false; // throw; } //End section DataMining :Customers Who Bought This Item Also Bought return(View(MyViewModel)); }
public static string returnJson(string clientid, string secret, string query, string workspace, string dataset) { if (String.IsNullOrEmpty(clientid) || String.IsNullOrEmpty(secret) || String.IsNullOrEmpty(query) || String.IsNullOrEmpty(workspace) || String.IsNullOrEmpty(dataset)) { return("Not all parameters have been specified! clientid, secret, query, workspace, dataset"); } string tenantid = "your_tenant_id"; string connectionString = "Datasource=" + workspace + ";initial catalog=" + dataset + ";User ID=app:" + clientid + "@" + tenantid + ";Password="******"EVALUATE (Sales)"; AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection); /******************************************************* * Run the Query * - Open the connection * - Issue the query * - Iterate through each row of the reader * - Iterate through each column of the current row * - Close the connection *******************************************************/ adomdConnection.Open(); AdomdDataReader reader = adomdCommand.ExecuteReader(); //Create a loop for every row in the resultset List <string> curColumn = new List <string> (); StringBuilder sb = new StringBuilder(); StringWriter sw = new StringWriter(sb); string columnName = string.Empty; string fieldVal = string.Empty; string prevFieldVal = string.Empty; if (reader != null) { using (JsonWriter myJson = new JsonTextWriter(sw)) { myJson.WriteStartArray(); while (reader.Read()) { myJson.WriteStartObject(); int fields = reader.FieldCount; for (int i = 0; i < fields; i++) { if (reader[i] != null) { fieldVal = reader[i].ToString(); if (i != 0 && reader[i - 1] != null) { prevFieldVal = reader[i - 1].ToString(); } else { prevFieldVal = "First"; } if ((fieldVal == null || fieldVal.ToLower().Trim() == "undefined" || fieldVal.ToLower().Trim() == "unknown") && (prevFieldVal == null || prevFieldVal.ToLower().Trim() == "undefined" || prevFieldVal.ToLower().Trim() == "unknown")) { continue; } else { columnName = reader.GetName(i).Replace(".[MEMBER_CAPTION]", "").Trim(); curColumn = columnName.Split(new string[] { "." }, StringSplitOptions.None).ToList(); columnName = curColumn[curColumn.Count - 1].Replace("[", "").Replace("]", ""); if (Convert.ToString(columnName.Trim()).ToLower() == "latitude") { columnName = "lat"; } if (Convert.ToString(columnName.Trim()).ToLower() == "longitude") { columnName = "lon"; } myJson.WritePropertyName(columnName); myJson.WriteValue(reader[i]); } } } myJson.WriteEndObject(); } myJson.WriteEndArray(); } } else { return("No Records to display"); } //Console.WriteLine(curColumn); adomdConnection.Close(); return(sw.ToString()); }
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)); }