RavenDB complex index

Question

While I'm really excited about idea of using RavenDB as OLTP-application storage I'm bit in trouble with Linq/Map-Reduce index implementations.

Here is what I'm trying to do - I have documents -

Event {
   UserId:  "1",
   Location : "X",
   EventDate : "1/1/2010",
   EventType : "type A"
}
...
Event {
   UserId:  "2",
   Location : "Y",
   EventDate : "1/1/2011",
   EventType : "type B"
}

The action query/index supposed to perform is

"Bring me count of different events for specific user in specific date range grouped by location,event"

The result example:

Location | Count(EventA) | Count(EventB) 
---------------------------------------
X        | 10            |     2
Y        |  4            |    22

I believe this should be straight forward. I'm probably just missing something.

Thank you for any help!

Answer 1

What you are asking is a typical case for reporting. And that's one thing RavenDB isn't really suited for (http://ravendb.net/docs/server/bundles/index-replication). Your question resembles the structure of a cube in SQL Server Analysis Services.

The problem in this case is the date range. If the ranges are fixed, say I want to know it for each month, you could do this in an index, but if the ranges are ad hoc, then I believe this will not be possible in Raven by using an index and presumably not even a query because you would have to do the grouping client side and thus would have to retrieve a large amount of documents (far more than Raven's default 128).

But in case someone is searching for a multiple group by example in an index where we omit the date range then following index implementation where results are grouped by userid, location and event type could be a solution:

public class Index : AbstractIndexCreationTask<Index.Result>
{
    public class Result
    {
        public string UserId { get; set; }
        public string Location { get; set; }
        public string EventType { get; set; }
        public int Count { get; set; }
    }

    public Index()
    {
        Map = events => from e in events
                        select new Result
                        {
                            UserId = e.UserId,
                            Location = e.Location,
                            EventType = e.EventType,
                            Count = 1
                        };

        Reduce = results => from result in results
                            group result by new { result.UserId, result.Location, result.EventType }
                                into g
                                select new Result
                                {
                                    UserId = g.Key.UserId,
                                    Location = g.Key.Location,
                                    EventType = g.Key.EventType,
                                    Count = g.Sum(x => x.Count)
                                };
    }
}

This will give you this result

UserId   | Location | EventType     | Count
-------------------------------------------
1        | X        | A             |     2
1        | X        | B             |     4
1        | Y        | A             |    22
1        | Y        | B             |     6
2        | X        | A             |     7
2        | X        | B             |     3
2        | Y        | A             |     9
2        | Y        | B             |    16

You can then query that index and do additional grouping on the results of the query.