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");
}
}
internal DaxDataReader(AdomdDataReader reader)
{
this._reader = reader;
}
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));
}
