June 5, 2009 at 12:08 pm
Hi All,
I have recently joined a company who have had a CRM system designed and implemented for them. The CRM system is is fed data via a web service from a operational data store containing customer account information and personal details.
There is a also a link back from the CRM system to update the data store which, although I haven't had the chance to look into this in-depth seems very complicated and doesn't work particularly well (this is where I come in!). They are also using tables which are written to when certain events are fired in the CRM system so they are able to target the update rows for entities they are interested in.
I have no experience working with CRM systems as my main experience is around implementing kimball designed MS data warehouses.
Currently there are a list of initial reports which need to be developed which are integrated into sharepoint. All of these reports seem to require data which is all contained within the CRM database. These have been started to be built from SSAS by the report writers but the cubes which have been designed before I joined the company are very poor and don't contain all the information required.
My first thoughts are to generate a snapshot of the CRM database, and use the views shipped out of the box to generate custom reports on. From what I can understand this is generally best practice??
As the data store feeds a lot of information into the CRM system already I don't neccessarily see a need to bring the data back into the repository as it should all be stored in the CRM tables themselves. I know this will obviously depend on what the business wants but on the face of is they want to analyse campaign data and the outcomes.
In addition to this they want to harness the data mining capabilities of SSAS in the future.
I am sure some of you guys must have implemented solutions similar to this and maybe you can inform me of some best practices or solutions? Or point me in a direction where I can find some help. I am really struggling to find many examples or information out there. I have got the sample reports which ship with CRM 4.0 which I can use as a basis to start working.
Should I be thinking about pulling this information into a data store/data warehouse or should I go with the snapshot option?
Thanks for your help!
June 5, 2009 at 12:25 pm
If your CMS is not very busy you could use either SSIS package to update your Reporting database or use Replication but you could get latency and other issues. If your CMS is Microsoft here are more reports samples you can download.
You need a reporting database which you already know. If you want to fix the CMS code then you need to know both layers if Microsoft then .NET and SQL Server, if Java then both Java and SQL Server.
Kind regards,
Gift Peddie
June 7, 2009 at 6:47 am
Thanks for your reply.
I have been thinking about this for a while now and what seems to have been built for the company is an Operational Data Store (ODS). But one which maintains historical data over time. The ODS creates a single customer view using fuzzy matching, it then updates the CRM system and specific modified data is targeted and extracted from the CRM system back into the ODS several times throughout the day. It's structured in the 3rd NF and in my mind does not meet the needs of a DW due to it's volitile operational requirements.
I believe the best implementation of what I discussed in my 1st post is as follows;
1. Stabilise the ODS system and ensure every thing is working as expected.
2. Create the operational BI reporting directly from the ODS relational database. This satisfies the immediate needs of the business in terms of reporting.
3. Use the ODS as a single source of data to feed a Kimball dimensional designed DW. The DW will serve the strategic aims of the business. Create an SSAS db on top of the DW for data minining purposes.
I believe this represents the best solution to the business as it delivers their immediate goals more quickly, provides them with trust that the ODS is accurate and allows them to move forward strategically using the DW.
I welcome any comments you have on this design guys? I would be interested in anyone who has implemented a DW in a similar sceanrio or others who believe there could be a better solution?
Thanks
June 22, 2009 at 2:55 pm
Hi,
We have implemented a similar solution. We have a warehouse being the source of our CRM system and just talking now about a feed back the other way. Our solution is rather complicated but what you talk about it similar in its theme.
We don't have our warehouse updated daily, in fact we only update once a month currently. When data is added in CRM it remains until its fed back to our warehouse.
- Your ODS system has to be stable, this is your source and foundation.
- For reporting:
I would build a cube from the ODS and then standard reports from the CRM system, this negates the need to send updates back to the ODS throughout the day. You could also look to move data to another server for reporting depending on how big it was.
Do you enter new data directly into CRM? If so you will need to think about Dedupe rules in CRM. We currently do a lookup back to the warehouse and have processes in place for users to search before creating a new record. How have you implemented deduping? I would have thought that the dedupe process would run into a new database so you have the day to day transactions in one warehouse and then single customer view in another.
Do you have to worry about TPS and Postcode cleansing?
June 30, 2009 at 6:54 am
Hi Steve, thanks for your reply - sorry about the delayed response.
I agree with your solution and certainly building reports directly from the CRM system would be a lot easier. Here it is a little different as the business want non technical report writers to be able to drag and drop fields as they do not have the skills to write SQL or MDX.
New customers are added directly into the CRM system but these are called prospects and kept seperate from the customers in the ODS (until a match can be found). The de-dupe process is fairly complex and is a weak point or the system at the moment. There has been a lot of development work going into to using the Fuzzy Matching Lookup components in SSIS to match the records from our sources.
Steve, do you use MS CRM? For your warehouse are you capturing historical events? If so how are you doing this? We have plug ins which log to a table new or updated records. However this only provides the PK in order for the ETL to target the rows affected. We need to extend this to capture each and every event (in order) which occurs on the front end. For example different sales stages a entity goes through.
Thanks.
June 30, 2009 at 1:04 pm
Thats OK.
You can buy tools to sit over the front? We use Proclaity over the cubes but deliver SQL reporting via SSRS albeit you can buy tools for end users as well.
We use the term Prospects but they are certainly not customers?? Prospects are well, prospects or leads..... We hold customer records in a source system and then pass it through to CRM. If they convert the sale, it is converted in CRM but then entered into the source system to feed back through.
Fuzzy dedupe logic is complex and there is no getting away from that.
We do use MS CRM yes. We will pass updates back to the warehouse but things will be overwritten as its a warehouse! I am not sure the advantage of passing back transactions states to your warehouse? The sales process is the sales process so do they really care where it has been outside of CRM, other than where it is now?
June 30, 2009 at 2:47 pm
Unfortunately as the company is pretty well midsized they won't purchase things like proclarity unless they truely need to.
The business haven't had much of a view of what the reports can tell them at the moment, apart from using advanced find. The cubes were poorly designed and this is the legacy I am dealing with now - working out source to target mappings and then extending the existing cubes to suit the requested reports from the business. As ppl are just now getting access to basic reporting there is no push for proclarity.
Indeed prospects are leads and we treat them as such. The users cannot create new customers in the front end. If a prospect is promoted to a customer then it will be entered into the source system and picked up as part of the ETL.
The purpose of a warehouse is to track history of the items the business wants to track surely?! Implemented as Type 2 SCDs. Certainly any attributes the business weren't bothered about the system would treat them as Type 1's. As the moment everything in our system is treated as Type 1!
The business have asked to track the sales process over time by users to see how efficient they are. They plan to link this to bonuses etc. I know, I know...it's a CUSTOMER relationship managerment system but this is where they want to start.
Steve, I would be very interested to hear more about the general metrics your business users request, how they use them, if you use type 2 SCDs and more about your general set up? It may help me envision a better way to position this system within the business.
Thanks
September 18, 2009 at 12:57 pm
Steve? Are you still there??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply