I'm new to mongodb and trying to use it for reporting taking some data from MySQL (e.g. fetch-and-modify as a purchase is made). There are several filters to the report such as product category id, gender, country, affiliate, etc. I'm thinking of two ways. One is { _id : today, sale_amount : 100.00, gender : , country : , ...... } for each purchase (without pre-aggregation). Another one is { _id : { date : today, gender : , country : , (more filters) }, sale_amount : 100.00, sale_count : 1 }
for each combination of filters (with pre-aggregation). An example of what we need to see in the reporting is "Sales of product A per day made by male in some country in the last 90 days." If somebody can give me advice on how pre-aggregation should be done in this example, I'd highly appreciate it. Thank you in advance.
Could you give us a little more information? When you say, "Sales of product A per day made by male in some country in the last 90 days" - do you need the total sales of product A in dollars, a count of the sales of product A, or both?
On Friday, October 5, 2012 12:54:28 PM UTC-4, Fumiko wrote:
> Hi,
> I'm new to mongodb and trying to use it for reporting taking some data > from MySQL (e.g. fetch-and-modify as a purchase is made). There are > several filters to the report such as product category id, gender, country, > affiliate, etc. I'm thinking of two ways. One is > { _id : today, > sale_amount : 100.00, > gender : , > country : , > ...... } > for each purchase (without pre-aggregation). Another one is > { _id : { date : today, gender : , country : , (more filters) }, > sale_amount : 100.00, > sale_count : 1 }
> for each combination of filters (with pre-aggregation). An example of what > we need to see in the reporting is "Sales of product A per day made by male > in some country in the last 90 days." > If somebody can give me advice on how pre-aggregation should be done in > this example, I'd highly appreciate it. > Thank you in advance.
I truly appreciate your help. Yes, both dollar amount and count are needed. Your suggestion is pre-aggregation. I'd read those links prior to this posting and watched some recordings, and I may be having difficulty understanding it right. But, using those several filters as compound ids, and if, say to report "Sales in dollar amount per day made by male in the past 90 days" (i.e. one filter, gender, instead of multiple filters), then we are pre-aggregating wtih $inc followed by aggregating ($match: { _id.gender: "male" }) as well as by day? There may be 0 or more filters needed depending on report (sometimes all filters are needed and other times none - just sales in dollar amount, for example). I'd like to know if filters (includes date) as compound id is how it should be done or not. If not, could you please suggest what else can be possible? I'm not at all familiar with map-reduce. I'm hoping pre-aggregation or just plain aggregation itself is the solution. Thank you.
On Wednesday, October 10, 2012 10:36:41 AM UTC-7, Jenna deBoisblanc wrote:
> Hello Fumiko,
> Could you give us a little more information? When you say, "Sales of > product A per day made by male in some country in the last 90 days" - do > you need the total sales of product A in dollars, a count of the sales of > product A, or both?
> On Friday, October 5, 2012 12:54:28 PM UTC-4, Fumiko wrote:
>> Hi,
>> I'm new to mongodb and trying to use it for reporting taking some data >> from MySQL (e.g. fetch-and-modify as a purchase is made). There are >> several filters to the report such as product category id, gender, country, >> affiliate, etc. I'm thinking of two ways. One is >> { _id : today, >> sale_amount : 100.00, >> gender : , >> country : , >> ...... } >> for each purchase (without pre-aggregation). Another one is >> { _id : { date : today, gender : , country : , (more filters) }, >> sale_amount : 100.00, >> sale_count : 1 }
>> for each combination of filters (with pre-aggregation). An example of >> what we need to see in the reporting is "Sales of product A per day made by >> male in some country in the last 90 days." >> If somebody can give me advice on how pre-aggregation should be done in >> this example, I'd highly appreciate it. >> Thank you in advance.
{ _id : 1, product: "umbrella", sale_amount : 186.00, sale_count: 2, gender : female, country : USA,
}
Alternatively, you can insert a new document every time a purchase is made, and perform aggregation at a later point. The advantage of this procedure is that you maintain a record of every purchase (useful for tracking), but the disadvantage is that your data size will grow, and in addition, the aggregation command may be more expensive than a simple update with $inc. Here is an example of aggregation using the aggregation framework: { _id : 1, product: "umbrella", sale_amount : 100.00, gender : "female", country : "USA",
Map Reduce involves first emitting, and subsequently grouping, documents. The process is similar to the aggregation command above. The advantage of MR is that you can output the results to a new collection; however, since MR uses Javascript (and MongoDB currently uses a single-threaded Javascript engine), the performance may not be as good as the aggregation framework. Nevertheless, if you would like sample MR functions, please let me know.
On Thursday, October 11, 2012 7:44:57 PM UTC-4, Fumiko wrote:
> Hello Jenna,
> I truly appreciate your help. Yes, both dollar amount and count are > needed. Your suggestion is pre-aggregation. I'd read those links prior to > this posting and watched some recordings, and I may be having difficulty > understanding it right. But, using those several filters as compound ids, > and if, say to report "Sales in dollar amount per day made by male in the > past 90 days" (i.e. one filter, gender, instead of multiple filters), then > we are pre-aggregating wtih $inc followed by aggregating ($match: { > _id.gender: "male" }) as well as by day? There may be 0 or more filters > needed depending on report (sometimes all filters are needed and other > times none - just sales in dollar amount, for example). I'd like to know > if filters (includes date) as compound id is how it should be done or not. > If not, could you please suggest what else can be possible? I'm not at > all familiar with map-reduce. I'm hoping pre-aggregation or just plain > aggregation itself is the solution. > Thank you.
> Fumiko
> On Wednesday, October 10, 2012 10:36:41 AM UTC-7, Jenna deBoisblanc wrote:
>> Hello Fumiko,
>> Could you give us a little more information? When you say, "Sales of >> product A per day made by male in some country in the last 90 days" - do >> you need the total sales of product A in dollars, a count of the sales of >> product A, or both?
>> On Friday, October 5, 2012 12:54:28 PM UTC-4, Fumiko wrote:
>>> Hi,
>>> I'm new to mongodb and trying to use it for reporting taking some data >>> from MySQL (e.g. fetch-and-modify as a purchase is made). There are >>> several filters to the report such as product category id, gender, country, >>> affiliate, etc. I'm thinking of two ways. One is >>> { _id : today, >>> sale_amount : 100.00, >>> gender : , >>> country : , >>> ...... } >>> for each purchase (without pre-aggregation). Another one is >>> { _id : { date : today, gender : , country : , (more filters) }, >>> sale_amount : 100.00, >>> sale_count : 1 }
>>> for each combination of filters (with pre-aggregation). An example of >>> what we need to see in the reporting is "Sales of product A per day made by >>> male in some country in the last 90 days." >>> If somebody can give me advice on how pre-aggregation should be done in >>> this example, I'd highly appreciate it. >>> Thank you in advance.
