5

We are on Sitecore 8.2 rev. 170614 (8.2 Update-4) using MongoDB On-Premise mode with custom SSL Setup.

Need to submit a custom report to business based on various logins (and related data) categorized by GeoIP Location.

Since in xDB the IPs are stored in Mongo only not in SQL Reporting Database, how we can consolidate both data and create a report.

Though we are working on creating MongoDB script for the same but in the Phase-2 we need to fetch data from Client's Legacy Database which is again on SQL.

  • Are you looking for IPs or Geo like country, state, etc... – Chris Auer Dec 1 '17 at 1:54
  • Just an idea, but did you check the experience extractor? github.com/Sitecore/experience-extractor – Gatogordo Dec 1 '17 at 8:59
  • Yes @ChrisAuer. Login data need to be categorized on Countries, States and Cities. Also I think in Mongo the IPs are stored in Base64 format hence a conversion to Actual IPs (may be Hex) will also be needed. We are able to achieve it using Mongo Scripts though as a temporary solution. – Amitabh Vyas Dec 1 '17 at 16:11
  • @Gatogordo, let me explore the tool, will update you accordingly. – Amitabh Vyas Dec 1 '17 at 16:12
  • I was hoping on pulling the data our of Sitecore_Analytics_Index since it has the geo IP information. But it does not have event/goal/outcome data. You will need to go to Mongo for that. – Chris Auer Dec 2 '17 at 0:50
3

Sitecore itself stores the raw analytics data in Mongo, then continually processes the data into a SQL database to make it easier to report on. You can do the same thing, depending on how fresh you need the data to be.

If it needs to be up to date all the time, consider adding a processor to your xDB Aggregation system. This piggy-backs on the built-in Sitecore process to handle data incrementally and store it in a new SQL table in a shape considerate of your reporting needs.

If it only needs to be updated periodically, say daily, it is worth creating a separate batch process, again run on xDB, that can handle the data and prepare it for reporting without slowing down the normal processing system.

| improve this answer | |
  • Its a Monthly Report Richard. You mean creating a connector between Mongo and SQL, which pulls the data from Mongo convert it to the Relational DB format and save it to SQL. Is that correct ? – Amitabh Vyas Dec 1 '17 at 16:43
  • Yes, that's right. Be sure to add indexes to MongoDB to ensure queries are as efficient as possible. Try to structure your query using Map/Reduce or the aggregation framework, rather than relying on C# to do the rollup. You will find Mongo is very good at processing data this way. – Richard Hauer Dec 1 '17 at 17:10
  • Will give it a shot with the Map/reduce, but my understanding is correct right that Siteore OOB doesn't store the GeoIP data in Reporting DB at all? – Amitabh Vyas Dec 1 '17 at 19:49
  • The raw data is in Mongo. Note that Mongo is not a relational dB, so you can't "join" to the GeoIP collection – Richard Hauer Dec 1 '17 at 23:11
  • Agreed, Mongo is not relational rather it stores on BSON format. Hence we are writing Mongo Scripts (Using MongoChef as GUI) to query the Interaction collection for the required data. – Amitabh Vyas Dec 3 '17 at 5:11
2

Extracting the inputs from Chris Auer and Richard Hauer, consolidating below the best fit solution in our scenario.

As a temporary solution we have built MongoDB scripts on Interaction collection for all the data required from Mongo. Since using MongoChef as GUI tool we are also able to export the data in CSV format. Imported the CSV in a SQL table and playing around for the additional joins on Legacy DB tables for required reporting.

As a permanent solution, since it is a Monthly report for us hence rather tweaking the OOB aggregation framework, we are planning to introduce a custom batch process to pull the required documents from Interaction collection and populate a custom table in our SQL DB.

Thanks Chris Auer and Richard Hauer for the guidance.

| improve this answer | |
1

A custom aggregator processor can be defined and it will pull data from the xDB( MongoDB) and add these data to custom SQL tables to the reporting database.

Sample code for the reference.

 public class PageEventProcessor : AggregationProcessor
  {
    protected override void OnProcess(AggregationPipelineArgs args)
    {
        Assert.ArgumentNotNull(args, "args");
        try
        {
            var factDetails = args.GetFact<PageEvent>();
            foreach (var pageItem in args.Context.Visit.Pages)
            {
                foreach (var fEvent in pageItem.PageEvents)
                {
                   // Custom logic
                }
            }
        }
    }
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.