With the right mix of skill and ingenuity, your IT Systems can
make you proud. See for yourself.
Enable end users to select an unlimited number of recipients using an unlimited number of criteria in an unlimited number of combinations. Users must also be able to save search results to be reused in the future.
This type of a problem would be fairly easy to solve with LDAP filters, but in MS SQL it’s not so simple. There were dozens of tables involved and grouping could be recursive so without some sort of a reduction mechanism underlying tables would be queried several times over making queries too slow to be useful.
with a little help from my friends (Stanley Goldman to be precise) an F# logic engine was created to reduce the complexity of user requests to achieve an acceptable level of SQL server performance. It was a very complicated mathematical problem but we managed to solve it.
“Monster” queries were turned into docile, performance conscious sheep. Business need was addressed without breaking the system. Everyone was happy.
Management needs to have an ability to analyse CRM data in real time or as close to that as can get (but no more than a 15min delay)
It was obvious that the online data entry (OLTP) system had to be linked with the data analysis (OLAP) system. However, the ETL process was going to be prohibitively slow, and without ETL row data was not suitable for analysis.
SQL server replication with stored procedures was chosen to address the problem. ETL was logically broken down to the table level and each table got its own transformation procedure. This reduced the lag time between the systems to seconds. However, a secondary problem arose – stability and change management. Now that management got used to having reports in real time, replication became mission critical. To address the problem replication scripts were created that could fix and re-sync any part or all of the subscriptions. admin training was conducted so that in-house mainenance could be easily conducted.
Management got real time reports, IT department got tools and training to maintain the new system. Everyone was happy.