Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
 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);
 }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
 /// <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]);
         }
     }
 }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
 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");
 }
Ejemplo n.º 7
0
        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");
        }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 9
0
        /// <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());
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
 /// <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;
 }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
 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);
 }