protected MdQueryResult CreateEmptyResult(MdQuery q)
        {
            var result = new MdQueryResult();

            foreach (var dim in q.Dimensions)
            {
                var dimModel = model.Dimensions.Single(p => p.Name == dim);
                result.Headers.Add(new HeaderCell(dim, dimModel.Caption, dimModel.Type, HeaderType.DimensionHeader));
            }
            foreach (var measure in q.Measures)
            {
                var measureModel = model.Measures.Single(p => p.Name == measure);
                result.Headers.Add(new HeaderCell(measure, measureModel.Caption, measureModel.Type, HeaderType.MeasureHeader));
            }


            return(result);
        }
        public virtual MdQueryResult Execute(MdQuery q)
        {
            var data = q.SourceData as IEnumerable;
            if (data != null)
            {
                var result = CreateEmptyResult(q);

                var groupSb = new StringBuilder();
                var measureSb = new StringBuilder();

                foreach (var dim in q.Dimensions)
                {
                    var dimModel = this.model.Dimensions.Single(p => p.Name == dim);
                    if (string.IsNullOrEmpty(dimModel.Expression))
                    {
                        groupSb.AppendFormat("{0} as {0},", dim);
                        measureSb.AppendFormat("Key.{0} as {0},", dim);
                    }
                    else
                    {
                        groupSb.AppendFormat("{0} as {1},", dimModel.Expression, dim);
                        measureSb.AppendFormat("Key.{0} as {0},", dim);
                    }
                }
                groupSb.Remove(groupSb.Length - 1, 1);
                var groupQueryString = string.Format("new ({0})", groupSb.ToString());
                var groupQuery = data.AsQueryable().GroupBy(groupQueryString, "it");

                foreach (var measure in q.Measures)
                {
                    var measureData = model.Measures.Single(p => p.Name == measure);
                    if (string.IsNullOrEmpty(measureData.Expression))
                    {
                        measureSb.AppendFormat("{0}({1}) as {1},",
                            AggregateFunc(measureData.Aggregation), measure);
                    }
                    else
                    {
                        measureSb.AppendFormat("{0}({1}) as {2},",
                            AggregateFunc(measureData.Aggregation), measureData.Expression, measure);
                    }
                }
                measureSb.Remove(measureSb.Length - 1, 1);
                var measureQueryString = string.Format("new ({0})", measureSb.ToString());
                var measureQuery = groupQuery.Select(measureQueryString);
                var sortBuffer = new StringBuilder();

                for (int i = 0; i < q.Sort.Count; i++)
                {
                    var sort = q.Sort[i];
                    sortBuffer.AppendFormat("{0} {1},", sort.Column, sort.Direction.ToString());
                }

                if (sortBuffer.Length > 0) {
                    sortBuffer.Remove(sortBuffer.Length-1, 1);
                    var sortExp = sortBuffer.ToString();
                    if (!string.IsNullOrEmpty(sortExp))
                        measureQuery = measureQuery.OrderBy(sortExp);
                }

                if (q.Skip > 0)
                    measureQuery = measureQuery.Skip(q.Skip);

                if (q.Take > 0)
                    measureQuery = measureQuery.Take(q.Take);

                foreach (var resultItem in measureQuery)
                {
                    var row = new List<DataCell>();
                    foreach (var dim in q.Dimensions)
                    {
                        var value = getDynamicValue(resultItem, dim);
                        var formatInfo = model.Dimensions.Single(p => p.Name == dim).DefaultFormat;
                        var cell = new DataCell(value, formatInfo == null ? null: formatInfo.FormatString);
                        row.Add(cell);
                    }

                    foreach (var measure in q.Measures)
                    {
                        var value = getDynamicValue(resultItem, measure);
                        var formatInfo = model.Measures.Single(p => p.Name == measure).DefaultFormat;
                        var cell = new DataCell(value, formatInfo == null ? null : formatInfo.FormatString);
                        row.Add(cell);
                    }
                    result.Items.Add(row);
                }

                return result;
            }
            else return null;
        }
        protected MdQueryResult CreateEmptyResult(MdQuery q)
        {
            var result = new MdQueryResult();
            foreach (var dim in q.Dimensions)
            {
                var dimModel = model.Dimensions.Single(p => p.Name == dim);
                result.Headers.Add(new HeaderCell(dim, dimModel.Caption, dimModel.Type, HeaderType.DimensionHeader));
            }
            foreach (var measure in q.Measures)
            {
                var measureModel = model.Measures.Single(p => p.Name == measure);
                result.Headers.Add(new HeaderCell(measure, measureModel.Caption, measureModel.Type, HeaderType.MeasureHeader));
            }

            return result;
        }
Example #4
0
        static void Main(string[] args)
        {
            var file = "sales.csv";
            var table = CsvEngine.CsvToDataTable(file, ';');

            var columnTypes = new Dictionary<string, Type>();
            columnTypes.Add("Year", typeof(string));
            columnTypes.Add("Month", typeof(string));
            columnTypes.Add("Quantity", typeof(int));
            columnTypes.Add("VAT", typeof(decimal));
            columnTypes.Add("Unit_Price", typeof(decimal));
            columnTypes.Add("Total_Price", typeof(decimal));

            var newTable = new DataTable();
            newTable.TableName = "Product Sales";
            foreach (DataColumn col in table.Columns)
            {
                var t = typeof(string);
                if (!columnTypes.TryGetValue(col.ColumnName, out t))
                    t= typeof(string);
                newTable.Columns.Add(col.ColumnName, t);
            }

            foreach (DataRow row in table.Rows)
            {
                newTable.Rows.Add(row.ItemArray);
            }

            var cubeModel = CubeModel.GenerateFromDataTable(newTable);
            cubeModel.Dimensions.RemoveAll(p => p.Name == "Year");
            cubeModel.Dimensions.RemoveAll(p => p.Name == "Month");

            cubeModel.Dimensions.Add(new DimensionModel("Year", DataType.Text));
            cubeModel.Dimensions.Add(new DimensionModel("Month", DataType.Text));

            cubeModel.Dimensions.Add(new DimensionModel("Quarter", DataType.Text) { Expression = "Year + \" \" + Month" });
            cubeModel.Measures.Add(new MeasureModel("CalcField", DataType.Integer) { Expression = "Total_Price * 100" });

            foreach (var item in cubeModel.Dimensions)
            {
                item.Caption = item.Name.Replace('_', ' ');
            }

            foreach (var item in cubeModel.Measures)
            {
                item.Caption = item.Name.Replace('_', ' ');
            }

            cubeModel.Measures.Single(p => p.Name == "Total_Price").Caption = "Total Price";
            cubeModel.Measures.Single(p => p.Name == "Unit_Price").Caption = "Unit Price";

            cubeModel.Measures.Single(p => p.Name == "Total_Price").DefaultFormat = new DataFormat() { FormatString = "C" };
            cubeModel.Measures.Single(p => p.Name == "Unit_Price").DefaultFormat = new DataFormat() { FormatString = "C" };

            var ds = new DataSet();
            ds.Tables.Add(newTable);
            ds.WriteXml(cubeModel.Name + "-DataSet.xml");

            DataEngine engine = new DataEngine(cubeModel);
            var query = new MdQuery();
            query.SourceData = DynamicObject.ListFromDataTable(newTable);
            query.Dimensions.Add("Year");
            query.Dimensions.Add("Month");
            query.Dimensions.Add("Quarter");
            query.Measures.Add("Unit_Price");
            query.Measures.Add("Total_Price");
            query.Measures.Add("CalcField");

            query.Sort.Add(new JDash.InMemoryAnalytics.Engine.Sort("Quarter", JDash.InMemoryAnalytics.Engine.SortDirection.Descending));
            query.Take = 5;
            query.Skip= 5;

            var result = engine.Execute(query);

            foreach (var header in result.Headers)
            {
                Console.Write(header.Caption + "\t");
            }
            Console.WriteLine();
            foreach (var row in result.Items)
            {
                foreach (var cell in row)
                {
                    Console.Write(cell.FormattedValue + "\t");
                }
                Console.WriteLine();
            }
            Console.ReadLine();
        }
        public virtual MdQueryResult Execute(MdQuery q)
        {
            var data = q.SourceData as IEnumerable;

            if (data != null)
            {
                var result = CreateEmptyResult(q);

                var groupSb   = new StringBuilder();
                var measureSb = new StringBuilder();

                foreach (var dim in q.Dimensions)
                {
                    var dimModel = this.model.Dimensions.Single(p => p.Name == dim);
                    if (string.IsNullOrEmpty(dimModel.Expression))
                    {
                        groupSb.AppendFormat("{0} as {0},", dim);
                        measureSb.AppendFormat("Key.{0} as {0},", dim);
                    }
                    else
                    {
                        groupSb.AppendFormat("{0} as {1},", dimModel.Expression, dim);
                        measureSb.AppendFormat("Key.{0} as {0},", dim);
                    }
                }
                groupSb.Remove(groupSb.Length - 1, 1);
                var groupQueryString = string.Format("new ({0})", groupSb.ToString());
                var groupQuery       = data.AsQueryable().GroupBy(groupQueryString, "it");


                foreach (var measure in q.Measures)
                {
                    var measureData = model.Measures.Single(p => p.Name == measure);
                    if (string.IsNullOrEmpty(measureData.Expression))
                    {
                        measureSb.AppendFormat("{0}({1}) as {1},",
                                               AggregateFunc(measureData.Aggregation), measure);
                    }
                    else
                    {
                        measureSb.AppendFormat("{0}({1}) as {2},",
                                               AggregateFunc(measureData.Aggregation), measureData.Expression, measure);
                    }
                }
                measureSb.Remove(measureSb.Length - 1, 1);
                var measureQueryString = string.Format("new ({0})", measureSb.ToString());
                var measureQuery       = groupQuery.Select(measureQueryString);
                var sortBuffer         = new StringBuilder();

                for (int i = 0; i < q.Sort.Count; i++)
                {
                    var sort = q.Sort[i];
                    sortBuffer.AppendFormat("{0} {1},", sort.Column, sort.Direction.ToString());
                }

                if (sortBuffer.Length > 0)
                {
                    sortBuffer.Remove(sortBuffer.Length - 1, 1);
                    var sortExp = sortBuffer.ToString();
                    if (!string.IsNullOrEmpty(sortExp))
                    {
                        measureQuery = measureQuery.OrderBy(sortExp);
                    }
                }

                if (q.Skip > 0)
                {
                    measureQuery = measureQuery.Skip(q.Skip);
                }

                if (q.Take > 0)
                {
                    measureQuery = measureQuery.Take(q.Take);
                }

                foreach (var resultItem in measureQuery)
                {
                    var row = new List <DataCell>();
                    foreach (var dim in q.Dimensions)
                    {
                        var value      = getDynamicValue(resultItem, dim);
                        var formatInfo = model.Dimensions.Single(p => p.Name == dim).DefaultFormat;
                        var cell       = new DataCell(value, formatInfo == null ? null: formatInfo.FormatString);
                        row.Add(cell);
                    }

                    foreach (var measure in q.Measures)
                    {
                        var value      = getDynamicValue(resultItem, measure);
                        var formatInfo = model.Measures.Single(p => p.Name == measure).DefaultFormat;
                        var cell       = new DataCell(value, formatInfo == null ? null : formatInfo.FormatString);
                        row.Add(cell);
                    }
                    result.Items.Add(row);
                }

                return(result);
            }
            else
            {
                return(null);
            }
        }