CozyRoc, CRM's key to the outside world

For CRM development, Microsoft has set a few ground rules of how things should be done. One of them is about the CRM database. Developers cannot access the database directly or else the almighty Microsoft will punish you for all eternity (ok, not as bad as that, but you do lose the microsoft CRM support). This particular rule feels like a handcuff on me everytime I work on an ETL project where CRM is the destination. Now, by setting up this rule, then Microsoft does give you a chance of doing things their way, and that is through their data objects. Users can create the data object classes with their crmsvcutil application after they have downloaded the CRM SDK. After that, it is plain and simple EF4 and LINQ. So with that in mind, our way to do the projects (still with the handcuffs on) are to make a class that abstracts this by having a bunch of InsertXXX(List<XXX> items), UpdateXXX(List<XXX> items), DeleteXXX(List<XXX> items) methods. Ok, it is not the end of the world, except there still are quite a few ways to do this on, code maintenance after project completion is a challenge, integrating a bunch of C# code in the SSIS package doesn't exactly sound like a best practice either. So as I am getting fed up with that, I thought to myself that there must be someone else who are equally frustrated as I am regarding this CRM data integration. Afterall, as I have mentioned in my earlier post, BI and CRM fits very well together, so it is only reasonable that they have some sort of tool somewhere in the market that will make life easier for us. After some research I found a couple of candidate:

• Kingswaysoft
• CozyRoc

I had to run a presentation to my client regarding the pros and cons of these tool, plus the pros and cons for not using any of them, I am sharing my findings with you here. Both Kingswaysoft and CozyRoc offered SSIS tools for CRM data integration. There is a new Data Flow component called CRM Source and CRM Destination (As the classical OLE Db Source/Destination). Like the classical OLE Db, in order to utilize it you will also need to set up a connection manager. So we will have to set up a new CRM connection manager. However, we decided to go with CozyRoc for this project because it also include other SSIS tools that would be useful at the project as well. Otherwise, I consider them rather equal. (Kingswaysoft charges a tiny bit more, but the $100 a year per deployment is consider minimal in most companies) The CozyRoc CRM connection manager set up looks something like this: You can set up the server, type of CRM Deployment, credentials, these are quite similar to other connection managers and was quite easy to set up. Once you have that set up you can use the Dynamics CRM Source and Destination at will: The finishing product includes no more messy C# code and is plain boxes with arrows. My client is happy and it saved me a couple of days of implementation time. Win win situations. To read more about them: CozyRoc: www.CozyRoc.com Kingswaysoft: www.kingswaysoft.com  

BI + CRM , 1 + 1 > 2

As you all have read from my mini bio on the right panel of this blog, I am in love with Business Intelligence. However, for the last few days in Malmö, I am having an affair with CRM. It all started with the current project with my client who wants to implement a CRM solution for their clients. Initially hired to handle the migration of data, I volunteer to handle some of the CRM development tasks to expand my technological arsenal. CRM didn't catch my attention until its most recent version, Dynamics CRM 2011, when the demand exploded during 2011, as a side effect the demand for ETL developer exploded as well. As Jukka Niiranen has pointed out in his blog, there are many exciting features that attracts corporates and companies of all sizes. So why would a BI consultant like me wants to learn CRM? From what I have learned from the CRM course, CRM development itself is not difficult for developers of some programming background, the customization is even easier. The key is not about the technical, but rather the understanding of the user habits and the nature of the business. This is very similar to BI. So if you are a good BI developer with good understanding of the business, you path to be a successful CRM developer is halfed. Besides the ETL for data migration to CRM system, SSAS analysis and SSRS reports will create endless of combinations that makes CRM much more powerful for all business areas. Let's say you have a decent CRM solution for a bike importer (reminds you of Advanture works huh?). It logs all the customer and potential sales leads. Some of the standard chart even tells the CEO the sales breakdown by region. Let's say a CRM developer with BI knowledge comes in, and run a data mining on the CRM data to find out what products sells best with product A, B, C etc. and presents the data in a custom SSRS report chart? All of a sudden you have a CRM solution that can do cross selling. How about a CRM system for stock brokers who can monitor and analyze the portfolio of its clients? VaR, Exposures, even some basic analysis analysis of the companies in the portfolio can be done. All of these BI tricks can be done and it is only a matter of hours to include them into the CRM, but it adds a lot more values to the solution. A BI consultant learning CRM is definitely a 1 + 1 > 2, one gets to utilize his business knowledge in another dimension to create more exciting solutions for clients which only imagination (and budget) is the limit. For those who have read my post about the choice of technology, CRM is a technology on the rise, require little overhead to get going and with those extra bonuses of knowing BI. It adds up to be time well spent for me in Malmö, the sunset by the pier was very beautiful and I am enjoying my affair.

*Un*expected traps in an ETL Project

I am writing this post as a supplement to this post by Bjørn Eilertsen. Once we get comfortable with SSIS, we tend to assume we can handle any ETL project with ease. Especially when we have already developed quite a few of the reusable modules that would save us significant amount of time. Thus we would go lower on our estimates in order to win the contract and impress the client. Though it is true that with good experience with SSIS it would lower the development time of an ETL project, but there are some factors that has nothing to do with SSIS but are the main reason to delays and development hours shot off the chart. Besides listing the issues I would also point out a few ways to minimize if not avoid the delay.   Data/Environment access: As a consultant when we comes in to our client and work on a project, their data (or sometimes their client's data) can sometimes be sensitive and may not be available to us until we have completed a long list of task in order to gain access to it. Now, there is no guarantee of how long this may take so start early! This is the first of the many traps in an ETL project so the earlier this is taken care of, the more time there is to deal with the other potential issues.   Data Schema: When the sample data is not immediately available the client may try to appease us by giving us the data schema and claiming that the sample data will strictly follow the schema. It would be nice if it does, but it definitely would avoid a lot of time wasting if there is a plan B should the sample data deviates from the received schema.   Data Quality: In a perfect world, all data is clean. Just like in a perfect society everyone is living happily and there will be no crime. We all know that is impossible. Be prepared to spend some quality time to assure the quality of data. If the SSIS packages have implemented data control and spits out the data rows that contains unexpected data, you are half way done. It may take from a few communication mails with the person responsible of the source data to a month of sitting down to figure out how the data entities are connected. Connection between data entities are tricky especially when they come from different sources and they should not be taken lightly.   Data Model: The destination data model may seem perfectly under control of the SSIS developer and is definitely possible to define prior to the start of the project. However, should there be any changes to this, then there is a risk of facing all the challenges listed above again. The risk of this is high as the data model is usually used to serve as the base of data analysis/mining or an application that utilizes the data. So a glitch in the specification process will likely cause a change in the data model.   Most of these potential traps are difficult to avoid, but it is no point to feel discouraged. I believe it is because of these traps that make the existence of an SSIS developer worthwhile. With careful planning, accurate estimate and a good plan B, the traps would be able overcome with relatively little or no pain.

Data warehouse design, are you really that special

Whenever I came across a project where a data warehouse design is required, I often hear the customer stressing the point that their business is unique to everyone else and that a custom designed data warehouse is required. Well, according to the Lens Silverston, the author to the book series "The Data Model Resource Book", there exists a generic data model for all business. Most customization would merely be additional fields in the entities, or in most cases I can imagine, the customization work will simply be determining which subset of the generic data model to include. [gallery] Neither him nor me are suggesting that everyone can suddenly become a data warehouse architect and implement data warehouses on the fly by just treating the books as the holy bible, but with that as a starting point plus some accurate understanding of the business requirements to the customer. One can save a lot of time on the actual datawarehouse design process. After all, the understanding of the business aspect is the key to a successful datawarehouse. We are trying out his idea in my current project as we are implementing the data warehouse based on the data model from the financial sector suggested in volume 2. Over the course of the project we will track how much it really satisfy the customer's need and how much customizations along the way that would deviate from the data model (our goal is none). With that in mind, let's take it one step further, for consultants who work in a specific field of business, how about coding the data model into a database project in Visual Studio as a template and make the customizations from it? Imagine how much time it would have saved?

Different Views to an IT Project - Illustration

When I first came across this picture I couldn't stop laughing, mostly because of the truth in it. Especially the documentation part. How many times did we come across an old solution that doesn't come with any documentation at all?

As time goes on I also realize that there is a difference between what the customer wants and how the customer explained it. Thus the importance of a prototype and frequent communication.

SSIS - Good Practice

This post talks about a good practice of implementing SSIS projects, to make a template that can be reuse for every SSIS project. We have frameworks for application design, such as MVC, Spring, that include the basic elements which tend to repeat on every single environment. We have that for SSIS as well. However, we are not as lucky to be able to generate the basic elements in a package by going through a wizard, so we will have to include our own. Some of the good elements to include in the SSIS project framework would be:

• A master package
• Logging
• Configuration file

A master package is responsible for calling each sub package and execution order can easily be managed in the master package. Once deployed, one can just schedule the execution of the master package to save deployment effort. Logging is responsible for logging the execution time and outcome for each batch (which is simply the execution of master package), package, and task. Corresponding database tables and stored procedures need to be implemented in order to support the logging system. I started a database project in visual studio and simply do a schema compare to install the needed tables and stored procedures, it took me seconds now after having spent hours doing the initial work. Configuration file allows for the package to be configured, all the logging connection strings and the location of the sub packages are best stored in the configuration so that it can adjust to different project and environment. It took me 3 days to set that up from scratch, so if these elements are stored in a SSIS template and one just pulls it out for the next project, that is 3 days saved. In addition, the following elements are not always required but could be useful in the template, one can just disable those element should be deemed not required for the project:

• A foreach loop container that loops through all the files in a folder (or all the tables in a database)
• A sequence container

Since most ETL projects involves handling a bunch of source data, one can almost not avoid the first element. The universal data dumper mention in earlier post fits very nicely into this foreach loop. The sub package that is responsible for data transform could be inserted into the sequence container that is used to manage the execution sequence and organizes a rollback should one or more of the element fails. With these elements setup in my template, I could now get started on my ETL projects right away without having to spend days doing repetitive work.

SSRS - Good Practice

This post talks about one of the good practice for an SSRS developer, the importance of generating mock report prior to report implementation. A couple of years ago I was at a bank making SSRS reports as part of a multi-phase, large scale project. The project leader who is on his last project before his retirement. He is extremely experienced and is also known to have a bottomless pocket because even though he keep claiming the budget is tight, but for some reason he is always able to squeeze out the extra budget required to handle any unexpected scenarios. During phase 1 there was about 10 reports made, I completed them according to the specifications which was solely based on the data that needs to be shown and the parameters required. Most of them are to replace today's excel reports so I was given samples to use as design guideline. The report were completed and deployed prior to deadline so I was quite proud of myself. However, that's when the fun begins, I start getting numerous change requests regarding the layout of the report, either the number format, the width of the column, the order of columns, and even the color choice of the header background. Though they are change requests so technically it isn't my fault, but these excessive communication pushed the release date of reports back. When the first phase was completed I had a meeting with my project leader to discuss my performance, and he challenged me to minimize the change requests by hitting on the spot of what the client wants on first try. He then suggested a mock report that simulates the layout and submit it to the client for approval prior to the report implementation. He is aware that it will prolong the implementation time, but the time saved afterward is well worth it. I took his advice and start making excel mock reports for phase 2, and I am able to cut down the change request by 90%, most report were accepted right away while a few of them had a couple of small changes that was not picked up with the mock report. Since I am pretty good with excel, it only took me about an hour for each report but they saved me days of follow-up work. Phase 2 and 3 went smoothly with his advice and I have since then included this into my best practice list.

SSRS - Permission Denied

So you are new to SSRS 2008 R2, you checked all the boxes at installation, started the services and started the Reporting Services Configuration Manager. So far so good, but just when you try to get on to the reporting manager (http://localhost/Reports) to check out the interface. You got the following error:

You started to wonder: "How could this be?", I am the administrator of the machine, I am the system admin of the SQL Server, so there is no way I can't access the report server. Well, you are right, except for one minor detail...

The default setup account that has access to the report server is only BUILTIN\Administrator, so everyone else cannot access the page. You can do one of the following:

Log in as BUILTIN\Administrator

Run internet explorer as BUILTIN\Administrator and then add yourself the permission.

So a few minutes later I am off making reports and deploying them to the site which I now have access.

NDC 2012

The 2012 Norwegian Developer Conference takes place in Oslo 6th - 8th June in Oslo Spectrum. Amende has a stand in the venue and along with it comes a few passes which us employees can share. I signed up to go on Thursday because there was a couple of talks that really interests me. Other obvious reason for picking Thursday may include the party of music and alcohol follows the day, but I was honestly too tired to join as my brain was going through indigestion from the amount of information and ideas absorbed during the day. This is actually my first NDC or any .NET developer conference for that matter since I was a java developer in my early days, Java Zone was a more natural destination for me, I was there in 2008. Talks in the NDC varies, some may be presentation of a new idea, others maybe a cool project that they work on, there are even some workshops to present some cool features in a common technology. The talks I went to are

• Clean Architecture by Robert Martin
• Embrace the Uncertainty by Dan North
• Git and GitHub for Developers on Windows by Phil Haack
• You are in production, now what? by Tatham Oddie

  Besides going to the presentations I am also responsible to be by the Amende stand in between sessions or even during a couple of sessions. Mainly giving out souveniers to guests and encourage them to take our Amende survey for a chance to win the macbook air. It was nice to chat with different people who drops by to learn more about what they do and what they think about us. I tried to sell the idea of EasyQuest to glasspaper, but apparently one of my colleagues beat me to it. Our USB memory stick was extremely popular, that was because (as I later found out) that our memory stick has to most capacity. Comparing to 4 GB and 2 GB that was offered at other stands, ours were superior, props to us :) One thought I had regarding the giveaways of the stands, I wish they can be more original. If I ended up winning all the drawings I would indeed end up with 3 iPads! Overall I think this event is definitely worth going if one has the means and time. Not only did I learn about new technologies or get a peek inside the mind of the experts in the field, but the socializing and networking is also an important reason to show your face at such a venue. So hopefully I will get to go again next year and I hope to see you there next year.

IT Consulting, the choice of technology

Once upon a time I referred to the technology I mastered as "weapons", that I would use to kill bugs.  That was the time when I called myself a "bugslayer" and my satisfaction was to eliminate bugs.  That was before I took a step back and see the whole picture.

Fast forward a couple of years and I have met my mentor/friend who recommended a book to me, "The Passionate Programmer" by Chad Fowler.  In the book (Part 1) he compared technology to "stocks" and compared spending time to master a technology to spending money to buy stock.  With stock investing as a hobby, this idea sat really well with me and have since then influenced my choice of technology to master.

In traditional stock market, there are blue chip stocks that represent the big, established companies. They won't go bankrupt tomorrow, but they won't double in price tomorrow either.  People buy these stocks for their stable return. In the IT world, blue chip technology would be like Java and C#, by mastering these languages, you have the key to a big part of the IT job market, there are thousands of jobs available, but at the same time there are also thousands of professional with the same background.  So the salary won't be great, but one should have no problem making a living.

Then there are the small companies (The penny stocks), they represent the small companies which the price may shoot up to the sky the next day or may as well disappear from the market the next day.  Investment in these stocks consist of high risk but at the same time the return can be phenomenal.  In the IT world, we have technologies such as small talk, rubies on rail, or other less popular programming languages or technologies that not many people know, but at the same time there are also few jobs asking for these skills.  However, when a company is desperate for such a skill, and you happen to be the only one knocking on their door, you will be in a very good bargaining position.  On the other side, if the demand of the technology dies down, then all the time you have spent on aquiring the knowledge would be wasted.

Stocks in the IPO could sometimes be a good investment, and so is a new technology.  The first ones to master the new technology is usually well on their way to enjoy the payoff while others are still learning.  However, while we cheer for the andriod developers, we must not forget the poor symbian developers.

So what technologies should we spend our time on? Like the good old investment advice: "Don't put all your eggs in one basket." Everyone should have a "portfolio" consisting of serveral technologies that belong to each categories.  Do the homework, follow the trend closely and watch for signs of a market change.  Don't be afraid of make a switch when the market has changed.  As for myself, I started off as a Java developer, but then I realized that C# is more popular in the Norwegian market than Java so I made the switch.  As I have gotten comfortable with C#, I have added Microsoft BI into my portfolio and now I am able to enjoy the boom in ETL projects while I can always go back to traditional programming should it become less popular in the future.

So whenever I meet a student and they ask me for advice as for what technology they should focus on, I tell them this story above, and recommend them to read the book as well.

Difference between a developer and a consultant

I recall a few years back while I was still working towards my degree of Computer Science and Engineering, without a superior GPA and without a clear path of a career, I seeked advice from an older friend who had been in the working world for a few years. His answer was quite straight forward, "Go be a consultant." he said. Without too much knowledge in the consulting world, and with my mindset of being a software developer, I quickly brushed off the advice.

Recently I recalled this conversation out of the blue and by that time I was an IT Consultant having worked in the field for 5 years already, and I am enjoying it.

So what is a difference between a developer and a consultant?

In short, developers solve a technological challenge, while an IT consultant solves a business challenge using technlogy.

During my career I have worked with developers and consultants, sometimes even worked with consultants who really should have been a developer or vice versa. I think the biggest difference lies in the following points.

Understanding of the subject knowledge:

Most technologies works independent of industry, one can make a website with .NET MVC for an online store or for a stock exchange broker. So as a developer working for Microsoft to come out with a technology (in this case MVC) which helps web developers to make a website, not much subject knowledge is required.

However, as a consultant who sits with an online store owner trying to figure out what features should be included in the online store, subject knowledge would be necessary. A basic understanding of the business, customers, would be needed in order to design a website that suits the need of the client. The subject knowledge is not always required, but if a picky client is paying for a consultant to get things done, they are looking for exactly this.

Communication skills requirement

A developer usually works in-house and is in charge of code. Most often they report to the lead developer or the software architect. No matter if it is a lead developer or software architect, their understanding of technology subject is usually higher than the developer himself and thus a developer rarely have to worry about the word choice, the technology specific terms will be understood.

Consultants usually works at the client site and often needs to communicate with the client regarding needs and wants. Most often the client is a businessman who doesn't have a clue how technology works. So it would be a bad idea to start bringing in terms like SQL, .NET, Dependency Injection etc. because to a businessman these terms are as familiary as greek. (No offence to Greece, but that's what I was told in my early years.)

Ability to bargain and be political

Developers gets a task list and the list needs to be completed by a certain deadline and usually that's the end of the story. It is not because there are no politics, but simply those are handled by people higher up in the heiarchy. A developer simply have to strive on making the best software, one that is robust, fast, and is flexible to additional features in the future.

Consultants often faces challenges outside of technology. For example, a budget constraint or a strategic decision which doesn't allow the consultant to implement the solution with the desired technology or method. Furthermore, the classical argument of what is a change request vs a bug also require a lot of energy/effort.

Summary:

The line between consultant and developer is getting blurry and thus it is more difficult to split between them. I am writing this to point out a few key difference so that students or job hunters can self-analyze thier strength and weaknesses in order to choose the job that fits them better.