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 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 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;
}
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 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;
}
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);
}
}
private static string stringify(AdomdDataReader row, int i)
{
if (row.IsDBNull(i))
{
return(null);
}
var type = row.GetFieldType(i);
if (type == typeof(byte[]) || type == typeof(char[]))
{
return(Convert.ToBase64String((byte[])row.GetValue(i)));
}
else if (type == typeof(IDataReader))
{
return("<IDataReader>");
}
return(row.GetValue(i).ToString());
}
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 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 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 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);
}
}
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 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 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 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());
}
//</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 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 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 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 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);
}
}
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 HttpResponseMessage GetDataMonthByDimension(string dim, string year, string month, string order, ConsultaMes data)
{
string WITH = @"
WITH
SET [Items] AS
{
STRTOSET(@Dimension)
}
";
string COLUMNS = @"
NON EMPTY
{
STRTOSET(@Year)
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
ORDER(
[Items],
[Measures].[Hec Ventas Ventas], " + order +
@")
}
*
{
STRTOSET(@Month)
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> anio = new List <string>();
List <dynamic> lstTabla = new List <dynamic>();
string valoresDimension = string.Empty;
string valoresYear = string.Empty;
string valoresMonth = string.Empty;
foreach (var item in data.Dimension)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dim);
valoresDimension = @"{" + valoresDimension + "}";
foreach (var item in data.Year)
{
valoresYear += "{0}.[" + item + "],";
}
valoresYear = valoresYear.TrimEnd(',');
valoresYear = string.Format(valoresYear, year);
valoresYear = @"{" + valoresYear + "}";
foreach (var item in data.Month)
{
valoresMonth += "{0}.[" + item + "],";
}
valoresMonth = valoresMonth.TrimEnd(',');
valoresMonth = string.Format(valoresMonth, month);
valoresMonth = @"{" + valoresMonth + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
cmd.Parameters.Add("Year", valoresYear);
cmd.Parameters.Add("Month", valoresMonth);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
switch (dr.FieldCount)
{
case 2:
dynamic objTabla = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)) },
total = getDecimalHelper(dr.GetValue(2)),
};
lstTabla.Add(objTabla);
break;
case 3:
dynamic objTabla2 = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)), getDecimalHelper(dr.GetValue(3)) },
total = getDecimalHelper(dr.GetValue(2)) + getDecimalHelper(dr.GetValue(3)),
};
lstTabla.Add(objTabla2);
break;
case 4:
dynamic objTabla3 = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)), getDecimalHelper(dr.GetValue(3)), getDecimalHelper(dr.GetValue(4)) },
total = getDecimalHelper(dr.GetValue(2)) + getDecimalHelper(dr.GetValue(3)) + getDecimalHelper(dr.GetValue(4))
};
lstTabla.Add(objTabla3);
break;
}
}
switch (dr.FieldCount)
{
case 4:
anio.Add(dr.GetName(1).Substring(22, 4));
anio.Add(dr.GetName(2).Substring(22, 4));
anio.Add(dr.GetName(3).Substring(22, 4));
break;
case 3:
anio.Add(dr.GetName(1).Substring(22, 4));
anio.Add(dr.GetName(2).Substring(22, 4));
break;
case 2:
anio.Add(dr.GetName(1).Substring(22, 4));
break;
}
dr.Close();
}
}
}
dynamic result = new
{
anio,
sales = lstTabla
};
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
static private void executeDAX(Model model)
{
Console.Clear();
showConsoleHeader(model);
myConsoleWriteLine($"Enter or Pick a Query");
string query = getData("Query.dat");
AdomdConnection adomdConnection = new AdomdConnection($"Data Source={model.Database.Parent.ConnectionString};Initial catalog={model.Database.Name}");
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
adomdConnection.Open();
String queryResultFileName = $"{appFolder}QueryResult-{Guid.NewGuid()}.tsv";
List <string> list = new List <string>();
bool hasHeader = false;
try {
AdomdDataReader reader = adomdCommand.ExecuteReader();
while (reader.Read())
{
String rowResults = "";
/*****************************************************************************
* Add Header (if needed)
****************************************************************************/
if (!hasHeader)
{
for (int columnNumber = 0; columnNumber < reader.FieldCount; columnNumber++)
{
if (columnNumber > 0)
{
rowResults += $"\t";
}
rowResults += $"{reader.GetName(columnNumber)}";
}
Console.WriteLine(rowResults);
list.Add(rowResults);
hasHeader = true;
}
/*****************************************************************************
* Add normal line
****************************************************************************/
rowResults = "";
// Create a loop for every column in the current row
for (int columnNumber = 0; columnNumber < reader.FieldCount; columnNumber++)
{
if (columnNumber > 0)
{
rowResults += $"\t";
}
rowResults += $"{reader.GetValue(columnNumber)}";
}
Console.WriteLine(rowResults);
list.Add(rowResults);
}
System.IO.File.WriteAllLines(queryResultFileName, list);
ProcessStartInfo startInfo = new ProcessStartInfo();
bool excelFound = false;
if (File.Exists("C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE"))
{
startInfo.FileName = "C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE";
excelFound = true;
}
else
{
if (File.Exists("C:\\Program Files (x86)\\Microsoft Office\\root\\Office16\\EXCEL.EXE"))
{
startInfo.FileName = "C:\\Program Files (x86)\\Microsoft Office\\root\\Office16\\EXCEL.EXE";
excelFound = true;
}
}
if (excelFound)
{
startInfo.Arguments = queryResultFileName;
Process.Start(startInfo);
}
}
catch (Exception ex) {
Console.WriteLine(ex.Message);
Console.ReadKey();
}
adomdConnection.Close();
}
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++;
}
}
//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");
}
}
