Friday, May 27, 2005

Database Feature Desire

It would be nice if SQL Server (or any other database) would divide everything into applications. So you would create an "application" which would then contain all of your stored procedures and views and packages and whatever else.

Wednesday, May 25, 2005

Real-Time Backup

I started prototyping an application for real-time backup. You fire it up and point it at directories that you would like to track. The app takes a snapshot the directory contents and then begins tracking file changes. When it detects a change, it makes a note, and then stores the difference between the snapshot and new file. I have not written the actually differencing part yet for good reason.

Why do I need real-time backup? The first reason is trivial. My desktop frequently becomes cluttered with documents I no longer need, but which I need to retain just in case management needs something in the future. I would like the ability to simply delete every file and be secure in the knowledge that I have lost no data. The second is more important. At my job I frequently must work with large excel files. Sometimes I spend tens of hours working with a single spreadsheet (cleaning data, performing analysis, coding, etc). Every once in a while I take the time to make a backup copy of my work. But that’s not enough. Several times I have lost a whole day of work because excel has lame undo capabilities. The moment you save a file, your undo history is gone. I have this habit of pressing ctrl+s every few seconds. Hence, I never have more than a couple seconds of undo. So I would like an application that will take every single save I make and store the complete history, transparently. There is no reason a user should have to actively store content changes, ever. None. Nada. Zilch. Except...

Real-time backup of office documents is nearly impossible. I wrote my application all the way up to the point where it makes note that a file has changed. I pointed the app at a sample excel file and.... changing a single character results and 20 or 30 file changes. And none of the changes occur in the file you are working in. It looks something like this:

1) Your original file is untouched
2) Make a change and save
3) Excel makes the change in any number of temporary files
4) Excel builds a new file with the change
5) Excel deletes your original file
6) Excel renames the file from step 4 to your original file name

Basically, in order to solve the real-time backup problem, I would have to build a bunch of code to identify the pattern and store the real change. Word pretty much works the same way, which raises issues not present with excel. There are multiple editors for .doc files. What if the user edits the file in WordPad instead of word? What if WordPad has a different pattern for saving the file than word? Well then maybe I could start tracking what application has which file handles open... maybe... but as you can tell, it all becomes a complicated mess.

Monday, May 23, 2005

I need a project! & Comments on Microsoft CRM

I should be working on an essay. School is winding down this week and and my last final is coming up. At work, there is nothing particularly interesting to work on. I am leading the way in developing an entirely custom interface for Microsoft CRM. Nothing particularly challenging. If you know of any hard programming problems or interesting ideas for software, LET ME KNOW!

The interface MSCRM ships is useless. The productivity of salesreps at my company has dropped at least 40% since we deployed MSCRM. I think what happened is Microsoft just plain decided to very rapidly develop MSCRM and ship it out. When you look at their original plans, this makes sense. MSCRM was supposed to be merely a platform, with third parties developing CRM applications on top of the platform. Apparently at some point they decided to build the application themselves and so the whole thing just feels rushed.

Frankly, if anyone out there is considering a CRM solution, stay away from MSCRM. It hardly provides any value for the investment. A half-decent web-developer could write a superior system in short order. What is the difference? You don't get a web-services API. You don't get role-based security. You don't get corporate support. You don't get exchange/outlook integration. You will probably get better search functionality. You will probably have an easier time integrating with your other systems. You will have a highly customizable UI (critical).

The web services get in the way more than anything else. The problem is that CRM never ever stands alone. It has to integrate with accounting and distribution management (at the very least). Want to search for customers based on how/what widgets they order? Good luck. You will have to ignore the webservices and query the tables directly (which in unsupported and loses the role based security benefit). THE MOST IMPORTANT point about MSCRM webservices though is that THEY AREN'T WEB SERVICES AT ALL. Want to call a webserice function? You need the Microsoft CRM dlls. You cant just hookup with the webserver the way you would with something like the google API or any other webservice. This is an absurd attempt at platform lock-in and holds zero value for the customer. Also, check out the paragaph below about integration for more reasons why web-services, as they are implemented in MSCRM, are Bad.

The role-based security is ok, though muddled. Take one part enterprise style security (company, divisions, vice presidents, managers, users) and one part small business application. Next, mix in a dash of very broad categories of security. For example, take your customer records. A given user can edit the record if the record is within their role (does the record belong to the user? the division? the whole company?). The problem is that merely "editing" a record is extremely broad. At my company, we would like to show the account's "Account #", a property of our distribution system. However, in MSCRM, if you show a field, that field will be editable. What if a user accidental changes the #? That will result in bad things like not being able to find the record and duplicate records (because at my company, new accounts in our distribution system are created in MSCRM). You may make a field non-editable through unsupported means, but then the whole reason you went with something MSCRM was to get support, right?

The support for MSCRM is silly. The only time you will ever call them is because you desperately need a feature that is not on their product roadmap. Good luck.

Exchange integration is also silly. Frankly, the way Microsoft recommends implementing it (that is, define a whole new subdomain exclusively for CRM) negates the whole benefit. You will need another exchange server (hardware $$$ and software $$$). Why not just deploy some free server for email? And for anyone thinking of getting MSCRM because of the Outlook integration, forget about it. They are integrated if your definition of integrated is "they are accessed through the same application through completely separate UI and almost no entities map correctly between the two applications and even when they do the mapping can be broken since one has significantly less functionality than the other."

The search functionality in MSCRM is horrendous. This is one area I could go into a lot of detail about. However, suffice it to say that whenever the question of improved search comes up (like in the Microsoft newsgroups), the inevitable answer is check out c360. More $$$. At my company, I have invested a very substantial amount of time implementing our own searching method.

Integrating with external systems is implemented in a useless way. Say you open up an account. The information for the account is broken up into tabs. Integration is accomplished by adding new tabs that point to web pages you develop. What if you want to show the customer's year-to-date sales on the main account screen? Good luck. The general answer to this is go check out scribe insight. Oh, and remember those wonderful webservices? Well, while you may not do any damage searching the and reading data directly from the database, it would be EXTREMELY unwise to directly write data. That's because the security system is implemented in a really strange way. Why does this matter? Because the maximum speed at which you can create/update records through web-services in MSCRM is about 10-15 records per second. You read that correctly. If you don't believe me, call up Scribe Software and ask them how fast insight is able to work with MSCRM. They will give you the same answer. Also, the 10-15 records per second is achieved only by using multiple-threads. If you know anything about programming, you know multi-threaded programming is much more difficult then single-threaded. <20 is the absolute maximum speed, regardless of hardware. This is caused by some bug/design in how MSCRM deals with it's own security. Last I read about this issue, there is no planned fix.

And then the UI. *sigh*.

First Post!