/// <summary>
/// Given a CubeInfo, will return a dictionary of measure group to list of measures in the cube.
/// </summary>
/// <param name="cubeInfo"></param>
/// <returns></returns>
private Dictionary <string, List <string> > GetMeasureGroups(CubeInfo cubeInfo)
{
Dictionary <string, List <string> > measureGroupsToMeasures = new Dictionary <string, List <string> >();
string connection = string.Format(Constants.dataSource, cubeInfo.Datasource, cubeInfo.Catalog);
AdomdConnection conn = new AdomdConnection(connection);
conn.Open();
CubeDef cubeDef = null;
// Find cube in question.
foreach (CubeDef cube in conn.Cubes)
{
if (cube.Name == cubeInfo.Cube)
{
cubeDef = cube;
}
}
foreach (Measure m in cubeDef.Measures)
{
Property p = m.Properties.Find(Constants.measureGroup);
if (!measureGroupsToMeasures.ContainsKey(p.Value.ToString()))
{
measureGroupsToMeasures.Add(p.Value.ToString(), new List <string>());
}
measureGroupsToMeasures[p.Value.ToString()].Add(m.UniqueName);
}
conn.Close();
return(measureGroupsToMeasures);
}
/// <summary>
///
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private CubeInfo generateCubeInfoFromRow(IXLRangeRow row)
{
CubeInfo cube = new CubeInfo(row.Cell(Constants.B).GetString(), row.Cell(Constants.C).GetString(), row.Cell(Constants.D).GetString());
if (!row.Cell(Constants.E).IsEmpty())
{
cube.setTimeRange(new CubeQueryTimeRange(row.Cell(Constants.E).GetDateTime(), row.Cell(Constants.F).GetDateTime()), Settings[Constants.dateDimension]);
}
return(cube);
}
/// <summary>
/// Builds a command for a single attribute report.
/// </summary>
/// <param name="cube"></param>
/// <param name="measures"></param>
/// <param name="sliceByAttribute"></param>
/// <param name="filters"></param>
/// <param name="includeLabels"></param>
/// <param name="timeRange"></param>
/// <param name="timeDimension"></param>
/// <returns></returns>
private string commandBuilder(CubeInfo cube, List <string> measures, string sliceByAttribute, List <string> filters, bool includeLabels, CubeQueryTimeRange timeRange, string timeDimension)
{
string cmd;
string subCube = string.Format("[{0}]", cube.Cube);
char[] comma = { ',' };
Dictionary <string, List <string> > attributeToFilters;
if (!includeLabels)
{
cmd = "SELECT { ";
}
else
{
cmd = string.Format(Constants.memberMessage, sliceByAttribute.Substring(0, sliceByAttribute.LastIndexOf('.')));
}
foreach (string s in measures)
{
cmd += s + ",";
}
cmd = cmd.TrimEnd(comma);
cmd += " } ON COLUMNS";
if (includeLabels)
{
cmd += string.Format(Constants.allocateMessage, sliceByAttribute);
}
cmd += " FROM";
if (timeRange != null)
{
subCube = string.Format(Constants.selectMessage,
timeDimension, timeRange.StartDate, timeDimension, timeRange.EndDate);;
}
attributeToFilters = seperateMembersIntoHierarchies(filters);
foreach (KeyValuePair <string, List <string> > kvp in attributeToFilters)
{
subCube = createSubCubeQuery(kvp.Value, subCube);
}
return(cmd + subCube);
}
/// <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);
}
/// <summary>
/// Validates a cube's connection parameters.
/// </summary>
/// <param name="cube"></param>
/// <returns>
/// 0: Success
/// 1: Failure
/// </returns>
private int validateCubeConnection(CubeInfo cube)
{
string connection = string.Format(Constants.dataSource, cube.Datasource, cube.Catalog);
int errorCode = 0;
bool cubeFound = false;
try
{
using (AdomdConnection conn = new AdomdConnection(connection))
{
conn.Open();
foreach (var c in conn.Cubes)
{
if (c.Name == cube.Cube)
{
cubeFound = true;
}
}
conn.Close();
}
if (!cubeFound)
{
Console.WriteLine();
Console.WriteLine(string.Format(Constants.cubeMessage, cube.Cube, cube.Datasource, cube.Catalog));
Console.WriteLine();
errorCode = 1;
}
}
catch (Exception ex)
{
Console.WriteLine();
Console.WriteLine(string.Format(Constants.connectionMessage, cube.Datasource, cube.Catalog, cube.Cube));
Console.WriteLine(string.Format(Constants.errorMessage, ex.Message));
Console.WriteLine();
errorCode = 1;
}
return(errorCode);
}
/// <summary>
/// Gets the MDX query from a Report and CubeInfo.
/// </summary>
/// <param name="rep"></param>
/// <param name="cube"></param>
/// <returns></returns>
private string getReportsCommand(Report rep, CubeInfo cube)
{
List <string> measures = new List <string>();
measures.Add(rep.MeasureUniqueName);
CubeQueryTimeRange cqtr;
if (rep.TimeRange == null)
{
cqtr = cube.TimeRange;
}
else
{
cqtr = rep.TimeRange;
}
return(commandBuilder(cube, measures, rep.SliceByAttribute, rep.FilterList, true, cqtr, getDefaultTimeDimension()));
}
/// <summary>
/// Constructs a CubeCompare object.
/// </summary>
/// <param name="cube1"></param>
/// <param name="cube2"></param>
/// <param name="reports"></param>
public CubeCompare(CubeInfo cube1, CubeInfo cube2, List <Report> reports)
{
this.Cube1 = cube1;
this.Cube2 = cube2;
this.Reports = reports;
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
public SettingsLoader(string filePath)
{
this.settingfilePath = filePath;
this.Settings = new Dictionary <string, string>();
CubeInfo cube1 = null;
CubeInfo cube2 = null;
List <Report> reportList = new List <Report>();
Dictionary <int, List <string> > filterMap = new Dictionary <int, List <string> >();
Dictionary <int, string> measureMap = new Dictionary <int, string>();
Dictionary <int, string> slicerMap = new Dictionary <int, string>();
Dictionary <int, ReportThreshold> thresholdMap = new Dictionary <int, ReportThreshold>();
List <string> blankMeasure = new List <string>();
List <string> blankSlicers = new List <string>();
List <string> blankFilters = new List <string>();
ReportThreshold blankThreshold = null;
// Get Workbook and worksheets from file.
XLWorkbook workbook = new XLWorkbook(settingfilePath);
IXLWorksheet connectionsWorksheet = workbook.Worksheet(Constants.connection);
IXLWorksheet settingsSheet = workbook.Worksheet(Constants.settings);
IXLWorksheet reports = workbook.Worksheet(Constants.measures);
IXLWorksheet slicers = workbook.Worksheet(Constants.rowLabel);
IXLWorksheet filters = workbook.Worksheet(Constants.filters);
IXLWorksheet thresholds = workbook.Worksheet(Constants.threshold);
// Get other settings
foreach (IXLRangeRow r in settingsSheet.Tables.First().DataRange.RowsUsed())
{
this.Settings.Add(r.Cell(Constants.A).GetString(), r.Cell(Constants.B).GetString());
}
// Get CubeInfo information.
foreach (IXLRangeRow r in connectionsWorksheet.Tables.First().DataRange.RowsUsed())
{
// Decide which cube will go first. The math is done something like cube2 - cube1 for difference.
if ((int)r.Cell(Constants.A).GetDouble() == 1)
{
cube1 = generateCubeInfoFromRow(r);
}
if ((int)r.Cell(Constants.A).GetDouble() == 2)
{
cube2 = generateCubeInfoFromRow(r);
}
}
// Get Report info
// First get all filters
foreach (IXLRangeRow r in filters.Tables.First().DataRange.RowsUsed())
{
if (r.Cell(Constants.A).IsEmpty())
{
blankFilters.Add(r.Cell(Constants.B).GetString());
}
else
{
List <int> reportNumbers = getNumbersFromString(r.Cell(Constants.A).GetString());
foreach (int num in reportNumbers)
{
if (!filterMap.ContainsKey(num))
{
filterMap.Add(num, new List <string>());
}
filterMap[num].Add(r.Cell(Constants.B).GetString());
}
}
}
// Get the measures
foreach (IXLRangeRow r in reports.Tables.First().DataRange.RowsUsed())
{
if (r.Cell(Constants.A).IsEmpty())
{
blankMeasure.Add(r.Cell(Constants.B).GetString());
}
else
{
List <int> reportNumbers = getNumbersFromString(r.Cell(Constants.A).GetString());
foreach (int num in reportNumbers)
{
measureMap.Add(num, r.Cell(Constants.A).GetString());
}
}
}
// Get the slicers
foreach (IXLRangeRow r in slicers.Tables.First().DataRange.RowsUsed())
{
if (r.Cell(Constants.A).IsEmpty())
{
blankSlicers.Add(r.Cell(Constants.B).GetString());
}
else
{
List <int> reportNumbers = getNumbersFromString(r.Cell(Constants.A).GetString());
foreach (int num in reportNumbers)
{
slicerMap.Add(num, r.Cell(Constants.B).GetString());
}
}
}
// Get all thresholds
foreach (IXLRangeRow r in thresholds.Tables.First().DataRange.RowsUsed())
{
if (r.Cell(Constants.A).IsEmpty())
{
blankThreshold = new ReportThreshold(r.Cell(Constants.B).GetDouble(), r.Cell(Constants.C).GetDouble());
}
else
{
List <int> reportNumbers = getNumbersFromString(r.Cell(Constants.A).GetString());
foreach (int num in reportNumbers)
{
thresholdMap.Add(num, new ReportThreshold(r.Cell(Constants.B).GetDouble(), r.Cell(Constants.C).GetDouble()));
}
}
}
// Add all numbered reports
foreach (KeyValuePair <int, string> kvp in measureMap)
{
Report report;
List <string> repFilt;
ReportThreshold repThres;
if (filterMap.ContainsKey(kvp.Key))
{
repFilt = filterMap[kvp.Key];
}
else
{
repFilt = new List <string>();
}
if (thresholdMap.ContainsKey(kvp.Key))
{
repThres = thresholdMap[kvp.Key];
}
else
{
repThres = null;
}
report = new Report(kvp.Key, kvp.Value, slicerMap[kvp.Key], repFilt, repThres);
reportList.Add(report);
}
// Add unnumbered reports.
int lastReportNumber = 0;
if (measureMap.Count != 0)
{
lastReportNumber = measureMap.Keys.Max();
}
addBlankReportsToReportList(reportList, blankMeasure, blankSlicers, blankFilters, blankThreshold, lastReportNumber + 1);
this.CubeComparison = new CubeCompare(cube1, cube2, reportList);
}