public void GroupByDateTime_MonthAndDate() { OlapDataSource dataSource = CreateTestSource(); OlapCube cube = new OlapCube() .GroupBy<DateTime>(v => v[2]) .KeyGroupAs(rd => rd.Year * 12 + rd.Month) .NameGroupAs(rd => rd.ToString("MMM yyyy")) .ThenBy<DateTime>(v => v[2]) .KeyGroupAs(rd => rd.Date) .NameGroupAs(rd => rd.ToString("dd/MM/yyyy")) .Sum<int>(v => v[3]); PivotTable table = cube.PreparePivotTable(dataSource); table.Value<int>(0).Should().Be(18); table.Groups.Should().HaveCount(2); var monthGroup = table.Groups.First(); monthGroup.Key.Should().Be(2013 * 12 + 8); monthGroup.Name.Should().Be("Aug 2013"); monthGroup.Value<int>(0).Should().Be(6); monthGroup.Groups.Should().HaveCount(1); var dateGroup = monthGroup.Groups.First(); dateGroup.Key.Should().Be(new DateTime(2013, 8, 1)); dateGroup.Name.Should().Be("01/08/2013"); dateGroup.Value<int>(0).Should().Be(6); monthGroup = table.Groups.Last(); monthGroup.Key.Should().Be(2013 * 12 + 9); monthGroup.Name.Should().Be("Sep 2013"); monthGroup.Value<int>(0).Should().Be(12); monthGroup.Groups.Should().HaveCount(2); dateGroup = monthGroup.Groups.First(); dateGroup.Key.Should().Be(new DateTime(2013, 9, 4)); dateGroup.Name.Should().Be("04/09/2013"); dateGroup.Value<int>(0).Should().Be(6); }
public void GroupByDateTime() { OlapDataSource dataSource = CreateTestSource(); OlapCube cube = new OlapCube() .GroupBy<DateTime>(v => v[2]) .Sum<int>(v => v[3]); PivotTable table = cube.PreparePivotTable(dataSource); table.Value<int>(0).Should().Be(18); }
public void GroupByDateTime() { OlapDataSource dataSource = CreateTestSource(); OlapCube cube = new OlapCube() .GroupBy <DateTime>(v => v[2]) .Sum <int>(v => v[3]); PivotTable table = cube.PreparePivotTable(dataSource); table.Value <int>(0).Should().Be(18); }
/// <summary> /// Creates a new instance of the OlapCell class. /// </summary> /// <param name="cube">The cube to which the cell belongs.</param> /// <param name="firstElement">The first element name.</param> /// <param name="secondElement">The second element name.</param> /// <param name="elements">A collection of elements that reference the cell.</param> public OlapCell(OlapCube cube, string firstElement, string secondElement, params string[] elements) { _cachedValue = null; _cube = cube; _elements = new System.Collections.Specialized.StringCollection(); _elements.Add(firstElement); _elements.Add(secondElement); if (elements != null) { for (int i = 0; i < elements.Length; i++) { _elements.Add(elements[i]); } } }
public void GroupWithPredicate() { OlapDataSource dataSource = CreateTestSource(); OlapCube cube = new OlapCube() .GroupBy <UserType>(v => v[0]) .ThenBy <string>(v => v[1]) .Sum <int>(v => v[3], v => string.Equals(v.Get <string>(1), "bpb", StringComparison.InvariantCultureIgnoreCase)) .Avg <double>(v => v[3], v => string.Equals(v.Get <string>(1), "bpb", StringComparison.InvariantCultureIgnoreCase)) .Count <int>(v => string.Equals(v.Get <string>(1), "bpb", StringComparison.InvariantCultureIgnoreCase)); PivotTable table = cube.PreparePivotTable(dataSource); table.Value <int>(0).Should().Be(9); //sum table.Value <double>(1).Should().BeInRange(3 - 0.01, 3 + 0.01); //avg table.Value <int>(2).Should().Be(3); // count table.Groups.Should().HaveCount(2); }
public void GroupByBankWithNull() { var dataSource = new OlapDataSource(); dataSource.Add(UserType.Private, "bpb", new DateTime(2013, 8, 1), 1); dataSource.Add(UserType.Private, null, new DateTime(2013, 9, 2), 2); var cube = new OlapCube() .GroupBy<string>(v => v[1]) .NameGroupAs(b => (b ?? "").ToUpper()) .Sum<int>(v => v[3]); var table = cube.PreparePivotTable(dataSource); table.Value<int>(0).Should().Be(3); table.Groups.Should().HaveCount(2); var group = table.Groups.First(); group.Key.Should().BeNull(); group.Name.Should().Be(""); group = table.Groups.Last(); group.Key.Should().Be("bpb"); group.Name.Should().Be("BPB"); }
public void GroupByBankWithNull() { var dataSource = new OlapDataSource(); dataSource.Add(UserType.Private, "bpb", new DateTime(2013, 8, 1), 1); dataSource.Add(UserType.Private, null, new DateTime(2013, 9, 2), 2); var cube = new OlapCube() .GroupBy <string>(v => v[1]) .NameGroupAs(b => (b ?? "").ToUpper()) .Sum <int>(v => v[3]); var table = cube.PreparePivotTable(dataSource); table.Value <int>(0).Should().Be(3); table.Groups.Should().HaveCount(2); var group = table.Groups.First(); group.Key.Should().BeNull(); group.Name.Should().Be(""); group = table.Groups.Last(); group.Key.Should().Be("bpb"); group.Name.Should().Be("BPB"); }
public void GroupByDateTime_MonthAndDate() { OlapDataSource dataSource = CreateTestSource(); OlapCube cube = new OlapCube() .GroupBy <DateTime>(v => v[2]) .KeyGroupAs(rd => rd.Year * 12 + rd.Month) .NameGroupAs(rd => rd.ToString("MMM yyyy")) .ThenBy <DateTime>(v => v[2]) .KeyGroupAs(rd => rd.Date) .NameGroupAs(rd => rd.ToString("dd/MM/yyyy")) .Sum <int>(v => v[3]); PivotTable table = cube.PreparePivotTable(dataSource); table.Value <int>(0).Should().Be(18); table.Groups.Should().HaveCount(2); var monthGroup = table.Groups.First(); monthGroup.Key.Should().Be(2013 * 12 + 8); monthGroup.Name.Should().Be("Aug 2013"); monthGroup.Value <int>(0).Should().Be(6); monthGroup.Groups.Should().HaveCount(1); var dateGroup = monthGroup.Groups.First(); dateGroup.Key.Should().Be(new DateTime(2013, 8, 1)); dateGroup.Name.Should().Be("01/08/2013"); dateGroup.Value <int>(0).Should().Be(6); monthGroup = table.Groups.Last(); monthGroup.Key.Should().Be(2013 * 12 + 9); monthGroup.Name.Should().Be("Sep 2013"); monthGroup.Value <int>(0).Should().Be(12); monthGroup.Groups.Should().HaveCount(2); dateGroup = monthGroup.Groups.First(); dateGroup.Key.Should().Be(new DateTime(2013, 9, 4)); dateGroup.Name.Should().Be("04/09/2013"); dateGroup.Value <int>(0).Should().Be(6); }
/// <summary> /// Creates an OLAP XML Request for splashing a value to a calculated cell in a cube. /// </summary> /// <param name="requestID">The id of the request.</param> /// <param name="cube">The cube to splash to.</param> /// <param name="value">The value to splash.</param> /// <param name="mode">The splash mode.</param> /// <param name="rounding">True, if values should be rounded, false if not.</param> /// <param name="decimals">The number of decimals to round, if rounding is true.</param> /// <param name="notDeleteOnZero">If true, sending the value 0 will not delete the leaf cells, but it will write the value 0. Otherwise sending the value 0 will delete the leaf cells.</param> /// <param name="first">The element for the first dimension.</param> /// <param name="second">The element for the second dimension.</param> /// <param name="elementNames">The variable elements for all other dimensions.</param> /// <returns>A string containing the XML request document ready to send.</returns> internal static string CreateRequest(string requestID, OlapCube cube, double value, string mode, bool rounding, int decimals, bool notDeleteOnZero, string first, string second, string[] elementNames) { if (cube.Dimensions.Count != elementNames.Length + 2) { throw new OlapException("Splash Value: The number of elements must match the number and order of dimensions in the cube " + cube.Name); } /* Sample Request * <Alea:Document xmlns:Alea="http://www.misag.com"> * <Alea:Request RequestID="1" Class="Cube" Method="Splashing"> * <Alea:Splashing AllocationMode="Equal" Rounding="true" DecimalPlaces="2" ErrorCorrection="true" Undo="true" DoNotDeleteOnZero="true"> * <Alea:CellCoordinates Purpose="Target" Cube="TOTSALES"> * <Alea:Element Dimension="YEARS" Name="2004"/> * <Alea:Element Dimension="ACTVSBUD" Name="Actual"/> * <Alea:Element Dimension="REGION" Name="Usa"/> * <Alea:Element Dimension="PRODUCT" Name="Total"/> * <Alea:Element Dimension="MONTHS" Name="May"/> * <Alea:Element Dimension="MEASURES" Name="Sales"/> * </Alea:CellCoordinates> * <Alea:Value>2.560000</Alea:Value> * </Alea:Splashing> * </Alea:Request> * </Alea:Document> */ XElement document = RequestBase.CreateDocumentBase(); XElement request = RequestBase.CreateRequestDocument(requestID, "Cube", "Splashing"); XElement splashingSection = new XElement(RequestBase.OlapNamespace + "Splashing"); request.Add(splashingSection); document.Add(request); splashingSection.SetAttributeValue("AllocationMode", mode); splashingSection.SetAttributeValue("Rounding", rounding ? "true" : "false"); splashingSection.SetAttributeValue("DecimalPlaces", decimals.ToString()); splashingSection.SetAttributeValue("ErrorCorrection", "true"); splashingSection.SetAttributeValue("Undo", "false"); splashingSection.SetAttributeValue("DoNotDeleteOnZero", notDeleteOnZero ? "true" : "false"); XElement cellCoordinateSection = new XElement(RequestBase.OlapNamespace + "CellCoordinates"); splashingSection.Add(cellCoordinateSection); cellCoordinateSection.SetAttributeValue("Purpose", "Target"); cellCoordinateSection.SetAttributeValue("Cube", cube.Name); XElement element = new XElement(RequestBase.OlapNamespace + "Element"); element.SetAttributeValue("Dimension", cube.Dimensions[0].Name); element.SetAttributeValue("Name", first); cellCoordinateSection.Add(element); element = new XElement(RequestBase.OlapNamespace + "Element"); element.SetAttributeValue("Dimension", cube.Dimensions[1].Name); element.SetAttributeValue("Name", second); cellCoordinateSection.Add(element); for (int i = 0; i < cube.Dimensions.Count - 2; i++) { element = new XElement(RequestBase.OlapNamespace + "Element"); element.SetAttributeValue("Dimension", cube.Dimensions[i + 2].Name); element.SetAttributeValue("Name", elementNames[i]); cellCoordinateSection.Add(element); } XElement valueSection = new XElement(RequestBase.OlapNamespace + "Value"); splashingSection.Add(valueSection); valueSection.SetValue(value.ToString("G", CultureInfo.InvariantCulture)); StringWriter requestString = new StringWriter(); document.Save(requestString); return(requestString.ToString()); }
public void GroupByUserTypeAndBank() { OlapDataSource dataSource = CreateTestSource(); dataSource.DataCount.Should().Be(6); OlapCube cube = new OlapCube() .GroupBy <UserType>(v => v[0]) .NameGroupAs(ut => ut == UserType.Private ? "Private users" : "Corporate users") .ThenBy <string>(v => v[1]) .KeyGroupAs(b => (b ?? "").ToLower()) .NameGroupAs(b => (b ?? "").ToUpper()) .Sum <int>(v => v[3]) .Min <int>(v => v[3]) .Max <int>(v => v[3]) .Avg <double>(v => v[3]) .Count <int>(); PivotTable table = cube.PreparePivotTable(dataSource); table.Value <int>(0).Should().Be(18); //sum table.Value <int>(1).Should().Be(1); //min table.Value <int>(2).Should().Be(6); //max table.Value <double>(3).Should().BeInRange(3 - 0.01, 3 + 0.01); //avg table.Value <int>(4).Should().Be(6); // count table.Groups.Should().HaveCount(2); var privateUsersGroup = table.Groups.First(); privateUsersGroup.Key.Should().Be(UserType.Private); privateUsersGroup.Name.Should().Be("Private users"); privateUsersGroup.Value <int>(0).Should().Be(7); privateUsersGroup.Value <int>(1).Should().Be(1); privateUsersGroup.Value <int>(2).Should().Be(4); privateUsersGroup.Value <double>(3).Should().BeInRange(7.0 / 3 - 0.01, 7.0 / 3 + 0.01); privateUsersGroup.Value <int>(4).Should().Be(3); privateUsersGroup.Groups.Should().HaveCount(2); privateUsersGroup.Groups.First().Key.Should().Be("bpb"); privateUsersGroup.Groups.First().Name.Should().Be("BPB"); privateUsersGroup.Groups.First().Value <int>(0).Should().Be(3); privateUsersGroup.Groups.First().Value <int>(1).Should().Be(1); privateUsersGroup.Groups.First().Value <int>(2).Should().Be(2); privateUsersGroup.Groups.First().Value <double>(3).Should().BeInRange(3.0 / 2 - 0.01, 3.0 / 2 + 0.01); privateUsersGroup.Groups.First().Value <int>(4).Should().Be(2); privateUsersGroup.Groups.Last().Key.Should().Be("bpu"); privateUsersGroup.Groups.Last().Name.Should().Be("BPU"); privateUsersGroup.Groups.Last().Value <int>(0).Should().Be(4); privateUsersGroup.Groups.Last().Value <int>(1).Should().Be(4); privateUsersGroup.Groups.Last().Value <int>(2).Should().Be(4); privateUsersGroup.Groups.Last().Value <double>(3).Should().BeInRange(4 - 0.01, 4 + 0.01); privateUsersGroup.Groups.Last().Value <int>(4).Should().Be(1); var corporateUsersGroup = table.Groups.Last(); corporateUsersGroup.Key.Should().Be(UserType.Corporate); corporateUsersGroup.Name.Should().Be("Corporate users"); corporateUsersGroup.Value <int>(0).Should().Be(11); corporateUsersGroup.Value <int>(1).Should().Be(2); corporateUsersGroup.Value <int>(2).Should().Be(6); corporateUsersGroup.Value <double>(3).Should().BeInRange(11.0 / 3 - 0.01, 11.0 / 3 + 0.01); corporateUsersGroup.Value <int>(4).Should().Be(3); corporateUsersGroup.Groups.Should().HaveCount(3); corporateUsersGroup.Groups.First().Key.Should().Be("bnp"); corporateUsersGroup.Groups.First().Name.Should().Be("BNP"); corporateUsersGroup.Groups.First().Value <int>(0).Should().Be(3); corporateUsersGroup.Groups.First().Value <int>(1).Should().Be(3); corporateUsersGroup.Groups.First().Value <int>(2).Should().Be(3); corporateUsersGroup.Groups.First().Value <double>(3).Should().BeInRange(3 - 0.01, 3 + 0.01); corporateUsersGroup.Groups.First().Value <int>(4).Should().Be(1); corporateUsersGroup.Groups.Last().Key.Should().Be("bpu"); corporateUsersGroup.Groups.Last().Name.Should().Be("BPU"); corporateUsersGroup.Groups.Last().Value <int>(0).Should().Be(2); corporateUsersGroup.Groups.Last().Value <int>(1).Should().Be(2); corporateUsersGroup.Groups.Last().Value <int>(2).Should().Be(2); corporateUsersGroup.Groups.Last().Value <double>(3).Should().BeInRange(2 - 0.01, 2 + 0.01); corporateUsersGroup.Groups.Last().Value <int>(4).Should().Be(1); }
/// <summary> /// Creates a new instance of the OlapCell class. /// </summary> /// <param name="cube">The cube to which the cell belongs.</param> /// <param name="elements">A collection of elements that reference the cell.</param> public OlapCell(OlapCube cube, System.Collections.Specialized.StringCollection elements) { _cube = cube; _elements = elements; _cachedValue = null; }
public void GroupByUserTypeAndBank() { OlapDataSource dataSource = CreateTestSource(); dataSource.DataCount.Should().Be(6); OlapCube cube = new OlapCube() .GroupBy<UserType>(v => v[0]) .NameGroupAs(ut => ut == UserType.Private ? "Private users" : "Corporate users") .ThenBy<string>(v => v[1]) .KeyGroupAs(b => (b ?? "").ToLower()) .NameGroupAs(b => (b ?? "").ToUpper()) .Sum<int>(v => v[3]) .Min<int>(v => v[3]) .Max<int>(v => v[3]) .Avg<double>(v => v[3]) .Count<int>(); PivotTable table = cube.PreparePivotTable(dataSource); table.Value<int>(0).Should().Be(18); //sum table.Value<int>(1).Should().Be(1); //min table.Value<int>(2).Should().Be(6); //max table.Value<double>(3).Should().BeInRange(3 - 0.01, 3 + 0.01); //avg table.Value<int>(4).Should().Be(6); // count table.Groups.Should().HaveCount(2); var privateUsersGroup = table.Groups.First(); privateUsersGroup.Key.Should().Be(UserType.Private); privateUsersGroup.Name.Should().Be("Private users"); privateUsersGroup.Value<int>(0).Should().Be(7); privateUsersGroup.Value<int>(1).Should().Be(1); privateUsersGroup.Value<int>(2).Should().Be(4); privateUsersGroup.Value<double>(3).Should().BeInRange(7.0 / 3 - 0.01, 7.0 / 3 + 0.01); privateUsersGroup.Value<int>(4).Should().Be(3); privateUsersGroup.Groups.Should().HaveCount(2); privateUsersGroup.Groups.First().Key.Should().Be("bpb"); privateUsersGroup.Groups.First().Name.Should().Be("BPB"); privateUsersGroup.Groups.First().Value<int>(0).Should().Be(3); privateUsersGroup.Groups.First().Value<int>(1).Should().Be(1); privateUsersGroup.Groups.First().Value<int>(2).Should().Be(2); privateUsersGroup.Groups.First().Value<double>(3).Should().BeInRange(3.0 / 2 - 0.01, 3.0 / 2 + 0.01); privateUsersGroup.Groups.First().Value<int>(4).Should().Be(2); privateUsersGroup.Groups.Last().Key.Should().Be("bpu"); privateUsersGroup.Groups.Last().Name.Should().Be("BPU"); privateUsersGroup.Groups.Last().Value<int>(0).Should().Be(4); privateUsersGroup.Groups.Last().Value<int>(1).Should().Be(4); privateUsersGroup.Groups.Last().Value<int>(2).Should().Be(4); privateUsersGroup.Groups.Last().Value<double>(3).Should().BeInRange(4 - 0.01, 4 + 0.01); privateUsersGroup.Groups.Last().Value<int>(4).Should().Be(1); var corporateUsersGroup = table.Groups.Last(); corporateUsersGroup.Key.Should().Be(UserType.Corporate); corporateUsersGroup.Name.Should().Be("Corporate users"); corporateUsersGroup.Value<int>(0).Should().Be(11); corporateUsersGroup.Value<int>(1).Should().Be(2); corporateUsersGroup.Value<int>(2).Should().Be(6); corporateUsersGroup.Value<double>(3).Should().BeInRange(11.0 / 3 - 0.01, 11.0 / 3 + 0.01); corporateUsersGroup.Value<int>(4).Should().Be(3); corporateUsersGroup.Groups.Should().HaveCount(3); corporateUsersGroup.Groups.First().Key.Should().Be("bnp"); corporateUsersGroup.Groups.First().Name.Should().Be("BNP"); corporateUsersGroup.Groups.First().Value<int>(0).Should().Be(3); corporateUsersGroup.Groups.First().Value<int>(1).Should().Be(3); corporateUsersGroup.Groups.First().Value<int>(2).Should().Be(3); corporateUsersGroup.Groups.First().Value<double>(3).Should().BeInRange(3 - 0.01, 3 + 0.01); corporateUsersGroup.Groups.First().Value<int>(4).Should().Be(1); corporateUsersGroup.Groups.Last().Key.Should().Be("bpu"); corporateUsersGroup.Groups.Last().Name.Should().Be("BPU"); corporateUsersGroup.Groups.Last().Value<int>(0).Should().Be(2); corporateUsersGroup.Groups.Last().Value<int>(1).Should().Be(2); corporateUsersGroup.Groups.Last().Value<int>(2).Should().Be(2); corporateUsersGroup.Groups.Last().Value<double>(3).Should().BeInRange(2 - 0.01, 2 + 0.01); corporateUsersGroup.Groups.Last().Value<int>(4).Should().Be(1); }
public void GroupWithPredicate() { OlapDataSource dataSource = CreateTestSource(); OlapCube cube = new OlapCube() .GroupBy<UserType>(v => v[0]) .ThenBy<string>(v => v[1]) .Sum<int>(v => v[3], v => string.Equals(v.Get<string>(1), "bpb", StringComparison.InvariantCultureIgnoreCase)) .Avg<double>(v => v[3], v => string.Equals(v.Get<string>(1), "bpb", StringComparison.InvariantCultureIgnoreCase)) .Count<int>(v => string.Equals(v.Get<string>(1), "bpb", StringComparison.InvariantCultureIgnoreCase)); PivotTable table = cube.PreparePivotTable(dataSource); table.Value<int>(0).Should().Be(9); //sum table.Value<double>(1).Should().BeInRange(3 - 0.01, 3 + 0.01); //avg table.Value<int>(2).Should().Be(3); // count table.Groups.Should().HaveCount(2); }