public HttpResponseMessage List(string name = "cipla")
{
using (AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
// AdomdConnection conn = new AdomdConnection(
// "Data Source=localhost;Catalog=YourDatabase");
//conn.Open();
string commandText = @"SELECT FLATTENED
PredictAssociation()
From
[Mining Structure Name]
NATURAL PREDICTION JOIN
(SELECT (SELECT 1 AS [UserId]) AS [Vm]) AS t ";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(Convert.ToString(dr[0]));
}
dr.Close();
conn.Close();
return(null);
}
}
protected void Button2_Click(object sender, EventArgs e)
{
AdomdConnection con = new AdomdConnection("Data Source=PC;Initial Catalog=ADM_HW");
con.Open();
AdomdCommand com = new AdomdCommand();
com.Connection = con;
string s = @"SELECT disease From [HeartID3MM]
NATURAL PREDICTION JOIN
(SELECT '" + TextBox1.Text + @"' AS [Age],
'" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type],
'" + TextBox2.Text + @"' AS [Rest Blood Pressure],
'" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar],
'" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina],
'" + TextBox3.Text + @"' AS [Max Heart Rate],
'" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t";
com.CommandText = s;
AdomdDataReader dr = com.ExecuteReader();
if (dr.Read())
{
if (dr[0] != null)
{
Label2.Text = dr[0].ToString();
}
}
dr.Close();
con.Close();
}
public HttpResponseMessage GetItemsByDimension(string dim, string order)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
{0}.CHILDREN,
{0}.CURRENTMEMBER.MEMBER_NAME, " + order +
@")
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
[OrderDimension]
ON ROWS
";
string CUBO_NAME = "[DHW Northwind]";
WITH = string.Format(WITH, dim);
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
dynamic result = new
{
datosDimension = dimension
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dim);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
private void getBoughtToghtherMovies(String input, List <string> output)
{
String newInput = input.Trim();
AdomdConnection CON = new AdomdConnection(
"Data Source=.;Catalog=ADMF18");
CON.Open();
AdomdCommand COM = CON.CreateCommand();
string s = "SELECT Flattened PREDICT([Movies],5" + ") FROM [CustomersMM] NATURAL PREDICTION JOIN (SELECT (" + "Select '" + newInput + "' as [movie]" + ") AS [Movies]) As T";
COM.CommandText = s;
AdomdDataReader DR = COM.ExecuteReader();
while (DR.Read())
{
if (DR[0] != null)
{
output.Add(DR[0].ToString());
}
}
DR.Close();
CON.Close();
}
/// <summary>
/// Prints query result.
/// </summary>
private static int ProcessQueryResult(AdomdDataReader reader)
{
int count = 0;
// print field names in the query result
for (int j = 0; j < reader.FieldCount; ++j)
{
Console.Write(reader.GetName(j) + (j == reader.FieldCount - 1 ? "" : ", "));
}
Console.WriteLine();
// print field values in the query result;
while (reader.Read())
{
++count;
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader[i] + (i == reader.FieldCount - 1 ? "" : ", "));
}
Console.WriteLine();
}
return(count);
}
private Dictionary <string, int> RequestSales(string MDXQuery)
{
Dictionary <string, int> responseMessage = new Dictionary <string, int>();
try
{
OpenConnection();
AdomdCommand command = new AdomdCommand(MDXQuery, connection);
AdomdDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
responseMessage.Add(Convert.ToString(dataReader[0]), Convert.ToInt32(dataReader[1]));
}
dataReader.Close();
connection.Close();
return(responseMessage);
}
catch (Exception e)
{
Console.WriteLine("Exception has occured while retriving data:\n" +
"Error message: " + e.Message);
return(null);
}
}
/// <summary>
/// Returns a DataTable from an AdomdCommand reader (MDX Query in an OLAP Cube)
/// </summary>
static public DataTable FromAdomdCommand(AdomdCommand command)
{
AdomdDataReader dr = command.ExecuteReader();
DataTable result = new DataTable("Data");
// output the rows in the DataReader
DataTable dtSchema = dr.GetSchemaTable();
foreach (DataRow schemarow in dtSchema.Rows)
{
var columnName = schemarow.ItemArray[0].ToString().Replace("[", "").Replace("]", "").Replace(" ", "");
result.Columns.Add(columnName, Type.GetType(schemarow.ItemArray[5].ToString()));
}
while (dr.Read())
{
object[] ColArray = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr[i] != null)
{
ColArray[i] = dr[i];
}
}
result.LoadDataRow(ColArray, true);
}
dr.Close();
return(result);
}
static void ConvertReaderToCsv(AdomdDataReader reader, bool openinExcel = true)
{
string csv = string.Empty;
for (int col = 0; col < reader.FieldCount; col++)
{
csv += reader.GetName(col);
csv += (col < (reader.FieldCount - 1)) ? "," : "\n";
}
// Create a loop for every row in the resultset
while (reader.Read())
{
// Create a loop for every column in the current row
for (int i = 0; i < reader.FieldCount; i++)
{
csv += reader.GetValue(i);
csv += (i < (reader.FieldCount - 1)) ? "," : "\n";
}
}
string filePath = System.IO.Directory.GetCurrentDirectory() + @"\QueryResuts.csv";
StreamWriter writer = File.CreateText(filePath);
writer.Write(csv);
writer.Flush();
writer.Dispose();
if (openinExcel)
{
OpenInExcel(filePath);
}
}
public SemesterSales[] GetSalesPrSemester()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY([Dim Member].[Semester].[Semester]*[Dim Product].[Sub Sub Category].[Sub Sub Category]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SemesterSales> semesters = new List <SemesterSales>();
while (dr.Read())
{
SemesterSales semester = new SemesterSales
{
semester = dr[0].ToString(),
SubSubCategory = dr[1].ToString(),
saleCount = int.Parse(dr[2].ToString())
};
semesters.Add(semester);
}
dr.Close();
SemesterSales[] semesterSales = semesters.FindAll(FindBeer).ToArray();
conn.Close();
return(semesterSales);
}
}
public ProductCat[] GetProductsByCategory()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT
[Measures].[Fact Sale Count] ON COLUMNS,
{[Dim Product].[Product Id].[Product Id]*[Dim Product].[Name].[Name]*[Dim Product].[Category].[Category]*[Dim Product].[Sub Category].[Sub Category]*[Dim Product].[Sub Sub Category].[Sub Sub Category]} ON ROWS
From [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <ProductCat> productCats = new List <ProductCat>();
while (dr.Read())
{
ProductCat productCat = new ProductCat
{
productID = int.Parse(dr[0].ToString()),
name = dr[1].ToString(),
cat = dr[2].ToString(),
subCat = dr[3].ToString(),
subSubCat = dr[4].ToString(),
};
productCats.Add(productCat);
}
dr.Close();
ProductCat[] productSubCats = productCats.ToArray();
conn.Close();
return(productSubCats);
}
}
public SCCatD[] GetProductSalesPrDay()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY( [Dim Product].[Category].[Category]*[Dim Date].[Day].[Day]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SCCatD> days = new List <SCCatD>();
while (dr.Read())
{
SCCatD day = new SCCatD
{
cat = dr[0].ToString(),
day = int.Parse(dr[1].ToString()),
saleCount = int.Parse(dr[2].ToString())
};
days.Add(day);
}
dr.Close();
SCCatD[] daySales = days.ToArray();
conn.Close();
return(daySales);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//الاتصال مع قاعدة البيانات
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB";
conn.Open();
// Command
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
//DMX استعلام
cmd.CommandText = String.Format(@"select Predict ([Product Name]) from [Customer_ProductsMM]
natural prediction join
(select '{0}' as [Age],
'{1}' as [Cars],
'{2}' as [Children],
'{3}' as [Commute Distance],
'{4}' as [Education],
'{5}' as [Gender],
'{6}' as [Home Owner],
'{7}' as [Income],
'{8}' as [Marital Status],
'{9}' as [Occupation],
'{10}' as [Region]) as t",
txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem,
RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem);
//إظهار النتيجة
AdomdDataReader adr = cmd.ExecuteReader();
while (adr.Read())
{
txtresult.Text = adr.GetString(0);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
AdomdConnection con = new AdomdConnection("Data Source = PC; Initial Catalog = ADM_HW");
con.Open();
AdomdCommand com = new AdomdCommand();
com.Connection = con;
string s = @"select flattened predicthistogram(disease) from [HeartBMM]
natural prediction join
(select '" + TextBox1.Text + @"' AS [Age],
'" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type],
'" + TextBox2.Text + @"' AS [Rest Blood Pressure],
'" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar],
'" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina],
'" + TextBox3.Text + @"' AS [Max Heart Rate],
'" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t";
com.CommandText = s;
AdomdDataReader dr = com.ExecuteReader();
while (dr.Read())
{
if (dr[0] != null)
{
Label1.Text += dr[0].ToString() + " " + dr[2].ToString();
}
}
dr.Close();
con.Close();
}
public static IEnumerable <T> ReturnFromReader <T>(this AdomdDataReader source) where T : new()
{
var type = typeof(T);
var schemaTable = source.GetSchemaTable();
var columnNames = schemaTable.Rows
.Cast <DataRow>()
.Select(x => x[0].ToString())
.ToArray();
var props = type.GetProperties()
.Where(x => System.Attribute.IsDefined(x, typeof(MapToAttribute)))
.Select(x => new
{
Attribute = x.GetCustomAttribute <MapToAttribute>(),
PropertyInfo = x
})
.Where(x => columnNames.Contains(x.Attribute.MdxColumn))
.OrderBy(x => x.Attribute.MdxColumn);
var bindingList = new Dictionary <ParameterExpression, MemberAssignment>();
var converterList = new TypeConverter[props.Count()];
var stringArrayParam = Expression.Parameter(typeof(string[]), "values");
var converterArrayParam = Expression.Parameter(typeof(TypeConverter[]), "converters");
var converter = typeof(TypeConverter).GetMethod("ConvertFromString", new [] { typeof(string) });
props.For((v, i) =>
{
var prop = v.PropertyInfo;
var paramExp = Expression.Parameter(prop.PropertyType, prop.Name);
var arrayAssignment = Expression.ArrayIndex(stringArrayParam, Expression.Constant(i));
var typeConverter = TypeDescriptor.GetConverter(prop.PropertyType);
var converterArrayAssignment = Expression.ArrayIndex(converterArrayParam, Expression.Constant(i));
var defaultValue = prop.PropertyType.GetDefault();
var defaultConstant = Expression.Constant(defaultValue == null ? null : defaultValue.ToString(), typeof(string));
var methodExp = Expression.Call(converterArrayAssignment, converter, Expression.Coalesce(arrayAssignment, defaultConstant));
Expression.Bind(prop,
Expression.Convert(methodExp, prop.PropertyType))
.Finally(bind => bindingList.Add(paramExp, bind));
converterList[i] = typeConverter;
});
var newExp = Expression.New(typeof(T));
var memberInit = Expression.MemberInit(newExp, bindingList.Values.ToArray());
var lambda = Expression.Lambda <Func <TypeConverter[], string[], T> >(memberInit, new[] { converterArrayParam, stringArrayParam });
var creatorMethod = lambda.Compile();
while (source.Read())
{
var rawResults = new string[columnNames.Length];
columnNames
.OrderBy(x => x)
.For((v, i) => rawResults[i] = source[v].ToString());
yield return(creatorMethod(converterList, rawResults));
}
}
private void ExecuteOlapQuery()
{
OlapManager objOlapManager = new OlapManager();
CellSet objCellSet = objOlapManager.GetQueryResult(TextBoxQuery.Text);
AdomdDataAdapter objDataAdaptor = new AdomdDataAdapter(objOlapManager.ObjCommand);
AdomdDataReader objDataReader = objOlapManager.ObjCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataTable objTable = new DataTable();
DataColumn myColumn = new DataColumn();
DataRow myRow = null;
DataTable objSchemaTable = objDataReader.GetSchemaTable();
List <string> lMeta = new List <string>();
// init meta values
for (int i = 0; i < objSchemaTable.Rows.Count; i++)
{
lMeta.Add(objSchemaTable.Rows[i][0].ToString());
}
// add columns and column captions
for (int i = 0; i < objDataReader.FieldCount; i++)
{
myColumn = new DataColumn(lMeta[i]);
objTable.Columns.Add(myColumn);
}
// output the rows in the DataReader
while (objDataReader.Read())
{
// new row
myRow = objTable.NewRow();
// set the row values
for (int i = 0; i < objDataReader.FieldCount; i++)
{
myRow[i] = objDataReader[i];
}
// add row to the table
objTable.Rows.Add(myRow);
}
// close reader
objDataReader.Close();
GridViewData.DataSource = objTable;
GridViewData.DataBind();
// export TEST
//ExportDataTableToExcel(objTable);
objOlapManager.CloseConnection();
// load the main table data
this.ObjMainTable = objTable;
Session.Add("queryData", objMainTable);
}
public HttpResponseMessage comparativa(DatosComparar comp)
{
string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string WHERE = "";
string dimension = @"";
string coma = "";
foreach (var dato in comp.Nombres)
{
switch (comp.dimension)
{
case 1: dimension += coma + @"[Dim Cliente].[Dim Cliente Nombre].&[" + dato + "]"; break;
case 2: dimension += coma + @"[Dim Producto].[Dim Producto Nombre].&[" + dato + "]"; break;
case 3: dimension += coma + @"[Dim Producto].[Dim Producto Categoria].&[" + dato + "]"; break;
case 4: dimension += coma + @"[Dim Empleado].[Dim Empleado Nombre].&[" + dato + "]"; break;
}
coma = ", ";
}
string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } * { " + dimension + " } ON COLUMNS,";
string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE;
List <Comparativa> ventasUsuario = new List <Comparativa>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
int fi = dr.FieldCount;
while (dr.Read())
{
Comparativa v = new Comparativa();
List <decimal> listaUsu = new List <decimal>();
v.fecha = dr.GetString(1) + " " + dr.GetString(0);
for (int i = 2; i < fi; i++)
{
decimal valor = dr.IsDBNull(i) ? 0 : dr.GetDecimal(i);
listaUsu.Add(valor);
}
v.Datos = listaUsu;
ventasUsuario.Add(v);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public HttpResponseMessage GetDataPieByDimension(string dimension, string order, string[] values)
{
string elementsToSearch = "";
foreach (var item in values)
{
elementsToSearch += ($@"{dimension}.&[{item}],");
}
elementsToSearch = elementsToSearch.Remove(elementsToSearch.Length - 1);
string MDX_QUERY = $@"
SELECT
({{ {elementsToSearch} }})
ON ROWS,{{
[Measures].[VENTAS]
}}ON COLUMNS FROM [PUBS DW]
";
List <string> dim = new List <string>();
List <decimal> ventas = new List <decimal>();
List <dynamic> lstTabla = new List <dynamic>();
dynamic result = new
{
datosDimension = dim,
datosVenta = ventas,
datosTabla = lstTabla
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
//cmd.Parameters.Add("Dimension", valoresDimension);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dim.Add(dr.GetString(0));
ventas.Add(dr.GetDecimal(1));
dynamic objTabla = new
{
descripcion = dr.GetString(0),
valor = dr.GetDecimal(1)
};
lstTabla.Add(objTabla);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
protected void Button1_Click(object sender, EventArgs e)
{
//الاتصال مع قاعدة البيانات
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB";
conn.Open();
//تعريف الاستعلام
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
// DMX استعلام
cmd.CommandText = String.Format(@"select flattened predicthistogram([Buy]) from [CustomersBayesMM]
natural prediction join
(select '{0}' as [Age],
'{1}' as [Cars],
'{2}' as [Children],
'{3}' as [Commute Distance],
'{4}' as [Education],
'{5}' as [Gender],
'{6}' as [Home Owner],
'{7}' as [Income],
'{8}' as [Marital Status],
'{9}' as [Occupation],
'{10}' as [Region]) as t",
txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem,
RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem);
AdomdDataReader adr = cmd.ExecuteReader();
//إظهار النتيجة
if (adr.Read())
{
txtresult.Text = adr.GetString(0) + " With " + Convert.ToString(Math.Round(Convert.ToDecimal(adr.GetString(2)) * 100, 2)) + " %";
}
if (adr.Read())
{
txtresult0.Text = adr.GetString(0) + " With " + Convert.ToString(Math.Round(Convert.ToDecimal(adr.GetString(2)) * 100, 2)) + " %";
}
}
public HttpResponseMessage Top(Reporte info)
{
string nombreDimension = @"";
switch (info.dimension)
{
case 1: //Clientes
default:
nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } ";
break;
case 2: //Productos
nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } ";
break;
case 3: //Categorias
nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } ";
break;
case 4: //Empleadoss
nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } ";
break;
}
string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) ";
string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS, ";
string ROWS = @"NON EMPTY { HEAD([TopVentas], " + info.top + ") } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string MDXQuery = WITH + "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME;
List <Venta> ventasUsuario = new List <Venta>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension));
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
Venta v = new Venta();
v.mes = dr.GetString(0);
v.ventas = dr.GetDecimal(1);
ventasUsuario.Add(v);
}
dr.Close();
}
}
};
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public HttpResponseMessage GetMesDimension()
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
[Dim Tiempo].[Mes Espaniol].CHILDREN,
[Dim Tiempo].[Mes Espaniol].CURRENTMEMBER.MEMBER_NAME
)
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Hec Ventas Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
[OrderDimension]
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
List <string> dimension = new List <string>();
dynamic result = new
{
datosMeses = dimension,
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public static List <AATest> GetDataByCon2(string strMdx)
{
List <AATest> list = new List <AATest>();
try
{
if (_connection != null)
{
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
Stopwatch sw = new Stopwatch();
//开始计时
sw.Start();
AdomdCommand cmd = _connection.CreateCommand();
cmd.CommandText = strMdx;
AdomdDataReader reader = cmd.ExecuteReader(); //Execute query
while (reader.Read()) // read
{
AATest dt = new AATest(); // custom class
dt.A = reader[0] == null ? "" : reader[0].ToString();
dt.B = reader[1] == null ? "" : reader[1].ToString();
dt.C = reader[2] == null ? "" : reader[2].ToString();
dt.D = reader[3] == null?"":reader[3].ToString();
dt.E = reader[4] == null ? "":reader[4].ToString();
dt.F = reader[5] == null ? "" : reader[5].ToString();
dt.G = reader[6] == null ? "" : reader[6].ToString();
//dt.H = reader[7].ToString();
//dt.I = reader[8].ToString();
//dt.J = reader[9].ToString();
//dt.K = reader[10].ToString();
list.Add(dt);
}
sw.Stop();
//获取运行时间[毫秒]
long times = sw.ElapsedMilliseconds;
Console.WriteLine($"耗时2——{times}");
_connection.Close();
}
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
static void AddSalesRegionMeasures()
{
// DAX query to be submitted totabuar database engine
String query = "EVALUATE( VALUES(Customers[Sales Region]) )";
AdomdConnection adomdConnection = new AdomdConnection(connectString);
adomdConnection.Open();
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
AdomdDataReader reader = adomdCommand.ExecuteReader();
// open connection use TOM to create new measures
TOM.Server server = new TOM.Server();
server.Connect(connectString);
TOM.Model model = server.Databases[0].Model;
TOM.Table salesTable = model.Tables["Sales"];
String measureDescription = "Auto Measures";
// delete any previously created "Auto" measures
foreach (TOM.Measure m in salesTable.Measures)
{
if (m.Description == measureDescription)
{
salesTable.Measures.Remove(m);
model.SaveChanges();
}
}
// Create the new measures
while (reader.Read())
{
String SalesRegion = reader.GetValue(0).ToString();
String measureName = $"{SalesRegion} Sales";
TOM.Measure measure = new TOM.Measure()
{
Name = measureName,
Description = measureDescription,
DisplayFolder = "Auto Measures",
FormatString = "$#,##0",
Expression = $@"CALCULATE( SUM(Sales[SalesAmount]), Customers[Sales Region] = ""{SalesRegion}"" )"
};
salesTable.Measures.Add(measure);
}
model.SaveChanges();
reader.Dispose();
adomdConnection.Close();
}
public HttpResponseMessage ventas(Filtro info)
{
//string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) ";
string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,";
string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS ";
string CUBO_NAME = @"[DWH Northwind] ";
string WHERE = "";
if (info.Item != 0 && info.Nombre != "")
{
string dimension = @"";
switch (info.Item)
{
case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].&[" + info.Nombre + "]"; break;
case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].&[" + info.Nombre + "]"; break;
case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].&[" + info.Nombre + "]"; break;
case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].&[" + info.Nombre + "]"; break;
}
WHERE = " WHERE " + dimension;
}
string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE;
List <Venta> ventasUsuario = new List <Venta>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
Venta v = new Venta();
//v.ano = ;
v.mes = dr.GetString(1) + " " + dr.GetInt16(0);
v.ventas = dr.GetDecimal(2);
ventasUsuario.Add(v);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
public void Visit(MetadataInfo.DaxMetadata daxMetadata)
{
if (daxMetadata == null)
{
throw new ArgumentNullException(nameof(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 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);
}
}
public string Command(string analysisSever, string depenedentValue)
{
StringBuilder stringBuilder = new StringBuilder();
//Analysis Server Connection String
string analysisServer = "Provider=MSOLAP;Data Source=TJ-PC;Integrated Security=SSPI;Initial Catalog=HDPurchasingPredictiveAnalysis;Location=TJ-PC";
using (AdomdConnection serverConnection = new AdomdConnection(analysisServer))
{
//Initialise a new command
AdomdCommand command = new AdomdCommand();
//Connect to the Analysis Server
command.Connection = serverConnection;
//Run this singleton query
command.CommandText = "SELECT [DrugPricingCostLR].[Drug Cost_] From[DrugPricingCostLR] NATURAL PREDICTION JOIN (SELECT 234 AS[ASP 2016]) AS t";
//This is the parameter we are using to predict
AdomdParameter parameter = null;
parameter = command.CreateParameter();
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = "ASP 2016";
parameter.Value = depenedentValue;
command.Parameters.Add(parameter);
//Open the connection to the Analysis Server
serverConnection.Open();
//Execute the prediction query and return the results
AdomdDataReader reader = null;
reader = command.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append(reader.GetName(i) + "\t");
}
stringBuilder.Append("\r\n--------------------------------------------\r\n");
//Important before accessing any data, call the .Read method
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
stringBuilder.Append("\r\n");
}
reader.Close();
}
return(stringBuilder.ToString());
}
public List <string> predict(List <string> input)
{
List <string> output = new List <string>();
//Connection
AdomdConnection CON = new AdomdConnection(
"Data Source=.;Catalog=AnalysisDB");
CON.Open();
// command
AdomdCommand COM = CON.CreateCommand();
// Query Command to Retrive Result
string s = @"SELECT Flattened PREDICT([Orders Details],3)
FROM [OrdersMM]
NATURAL PREDICTION JOIN
(SELECT ( ";
foreach (string x in input)
{
if (input.IndexOf(x) > 0)
{
s += " Union ";
}
s += "Select '" + x + "' as [Product Name]";
}
s += " ) AS [Orders Details]) As T";
COM.CommandText = s;
// Read result
AdomdDataReader DR = COM.ExecuteReader();
while (DR.Read())
{
if (DR[0] != null)
{
output.Add(DR[0].ToString());
}
}
DR.Close();
CON.Close();
return(output);
}
public HttpResponseMessage comparativausu(Reporte num)
{
string COLUMNS = @" NON EMPTY [Measures].[Fact Ventas Netas] ON COLUMNS,";
string CUBO_NAME = @"[DWH Northwind] ";
string WHERE = "";
string dimension = @"";
switch (num.dimension)
{
case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].CHILDREN"; break;
case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].CHILDREN"; break;
case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].CHILDREN"; break;
case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].CHILDREN"; break;
}
string ROWS = @" NON EMPTY " + dimension + " ON ROWS";
//WHERE = " WHERE " + dimension;
string MDXQuery = "SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
List <usuario> ventasUsuario = new List <usuario>();
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn))
{
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
usuario u = new usuario();
//v.ano = ;
u.nombre = dr.GetString(0);
ventasUsuario.Add(u);
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario));
}
/// <summary>
/// Returns a dictionary of row label to value retrieved from a query (command) applied to a cube (CubeInfo).
/// </summary>
/// <param name="cubeInfo"></param>
/// <param name="command"></param>
/// <returns></returns>
public Dictionary <string, double> GetSlicedMeasureData(CubeInfo cubeInfo, string command)
{
Dictionary <string, double> datalist = new Dictionary <string, double>();
string connection = cubeInfo.getStandardConnectionString();
try
{
using (AdomdConnection conn = new AdomdConnection(connection))
{
conn.Open();
using (AdomdCommand cmd = conn.CreateCommand())
{
cmd.CommandText = command;
using (AdomdDataReader result = cmd.ExecuteReader())
{
while (result.Read())
{
string label = "";
if (!result.IsDBNull(0))
{
label = result[0].ToString();
}
double value = 0;
if (!result.IsDBNull(2))
{
value = result.GetDouble(2);
}
datalist.Add(label, value);
}
}
}
conn.Close();
}
}
catch (AdomdErrorResponseException aere)
{
Utility.WriteToConsole(string.Format(Constants.invalid, aere.Message), 100);
}
return(datalist);
}
//</snippetReturnCommandUsingCellSet>
//<snippetOutputCommandWithDataReader>
void OutputCommandWithDataReader()
{
//Connect to the local server
AdomdConnection conn = new AdomdConnection("Data Source=localhost");
conn.Open();
//Create a command to send to the server.
AdomdCommand cmd = new AdomdCommand(@"WITH MEMBER [Measures].[FreightCostPerOrder] AS
[Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity],
FORMAT_STRING = 'Currency'
SELECT [Geography].[Geography].[Country].&[United States].Children ON ROWS,
[Date].[Calendar].[Calendar Year] ON COLUMNS
FROM [Adventure Works]
WHERE [Measures].[FreightCostPerOrder]", conn);
//Execute the command, returning an AdomdDataReader
AdomdDataReader reader = cmd.ExecuteReader();
//Retrieve the schema information about the AdomdDataReader
System.Data.DataTable schema = reader.GetSchemaTable();
//Cycle through the schema, printing out the
//ColumnName information as column headers.
for (int n = 0; n < schema.Rows.Count; n++)
{
Console.Write(schema.Rows[n]["ColumnName"] + "\t");
}
Console.WriteLine();
//Loop through the reader, outputting each cell's value.
while (reader.Read())
{
for (int n = 0; n < reader.FieldCount; n++)
{
Console.Write(reader.GetValue(n) + "\t");
}
Console.WriteLine();
}
//Close the connection and await user input.
conn.Close();
Console.ReadLine();
}
public void Run <TKey, TValue>(string mdxScript, IDictionary <TKey, TValue> result, Func <AdomdDataReader, String[], TKey> mapperKey, Func <AdomdDataReader, TValue> mapperValue, String[] args)
{
try
{
AdomdCommand cmd = new AdomdCommand(mdxScript, conn);
using (AdomdDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
result.Add(mapperKey(dr, args), mapperValue(dr));
}
}
}
catch (AdomdErrorResponseException e)
{
Console.WriteLine(e.Message);
}
}
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++;
}
}
