/// <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); }