0

I'm in the middle of trying to talk our management into letting us do a portal that sits across the many, many systems that we use. I believe in using the best tool for the job so we have a good ERP, Dynamics CRM, Office 365 and A service desk system and umpteen other monitoring and maintenance systems not to mention a couple of legacy systems we hang onto for historical purposes.

My proposal is we start to join the dots and make it easier for people to access data by having a central portal that can look across all these systems and has keys for joining the data so a customer record could show the crm data, erp data, support and monitoring data.

My question is mostly around accessing the data and performance. My current prototype uses service stack ormlite and sits mostly on SQL views across tables. I then call out to individual API's for data as well. The performance on this is not the greatest so I was looking for a good approach to this problem.

One of my thoughts was go down the elastic search or lucence type route with classes mashed up and stored specifically for searching and reading data. I'm not sure if this is a viable approach and looking at the lucence libraries available for .net most are way out of date.

I'm in the process of looking into using caching with a framework such as redis but some thing doesn't feel right here either. The key side is pretty good but if i need to do complex searches against the data it's not really geared for that.

I'm also in the process of reading up on data warehousing and potentially using a centralized database just for reporting, search/sort/filtering of mashed up data. Considering SQL and a possible NoSql option for this.

Does anybody have any recommendations for a best effort approach?

|improve this question
  • What's the actual usage pattern? Does data change often? Can it be stale? Are you only doing readonly things with it? Does there need to be permissions/controls on it? Do people really want to text search it? – Telastyn Apr 29 '15 at 15:37
  • Some data changes often but a lot can be stale. I'm not sure what you mean by usage pattern? The key idea is for this to initially be a read only system that joins many other read write systems. The vast majority of our users simple query data. look up customer records find contact data, review usage stats, look at invoices etc. Searches may not be text but will definitely require criteria filtering, limiting of things like customers with a specific product etc. – Andy Allison Apr 29 '15 at 15:45
  • I don't understand why your prototype is so slow. Is it grabbing data from all those other applications in real-time? – Robert Harvey Apr 29 '15 at 15:47
  • Yes at the minute, we have some data coming in via views from a database then look ups via a web service. My main question is how do we get around doing this. Caching? ETL? Database warehousing? Thanks for the response. – Andy Allison Apr 29 '15 at 15:57
  • 1
    All of the above. Don't use NoSql for the centralized database unless you have extremely large, unstructured datasets. You'll need SQL capabilities to create the reports. – Robert Harvey Apr 29 '15 at 16:08
0

First Step

If you have a strong DBA, I would recommend having them inspect the query execution plan to determine if there are any indexes that will help the views be more performant. Indexes trade lookup speed for disk space, so be aware of that tradeoff.

I can't tell you the number of times that a DBA has helped us improve the performance of a multi-second query to sub-second either by adding the right index, or by telling us how to restructure our query.

Second Step

This is a new project, take a step back and architect it. That means:

  • Define acceptable performance (or you will never get out of the optimization phase)
  • Can you make use of asynchrnous updates?
  • Identity modifications to existing apps, or side-car services to pull data
  • Identify whether you want to have search (potentially new capabilities), simply cache, or a combination
  • If you choose to add search, identify how you will populate the index.
    • Elastic Search provides a site indexing function, will this be good enough?
    • How do you need your index to be formatted to handle the cross-application search needs?
    • Do you need faceting support?

Agile doesn't mean no planning, it means just enough planning to move forward.

Do It

Once you've done enough planning, execute the plan. Just prioritize the highest value items first.

A lot of these things take time to get right. As usual, I always recommend get something working and the build on it.

I would also recommend going with Elastic or Apache Solr over using the Lucene indexes directly. Index management is a hard problem, don't buy a problem when there is an existing solution that already does it better.

|improve this answer

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