Business Intelligence Overview

It is such a long time no posting anything. I feel a bit disappointed of myself when I couldn't spend more time for this blog. As it is a part of my job, I should maintain it more sufficiently thought.

Recently, I joined a long long project which is totally non-related to CRM, it's all about BUSINESS INTELLIGENCE ! Feel being distracted a bit when I kept dragging between CRM and BI...

Anw, I initially was an Information Systems student. My major is working with data, making them from boring numbers becoming interesting things. Today I would like to spend time to provide you guys (who interested in BI) what I have learnt about BI. The agenda for the post is:

1. What is Business Intelligence ?
2. How does BI actually work?

I hope someone out there will interested in this blog.

1. What is Business Intelligence ?

Okay, in general, BI could be defined as the delivery of accurate, useful information to the decision makers. Here I would like to emphasize the "Accurate & useful" information. It means, they are not only just raw numbers, their are "meaningful" and have ability to tell you something.

For example, for sure, from financial perspective, every company has maintained some kind of operation reports which include transaction records, bank records, etc. But finance professionals want to also move beyond standard reporting to analyse details, provide summary to optimise financial performance, such as: Total Sales, Revenue, Profit Margin, etc. To achieve these goals, you must have full insight into the bottom line, cash flow, investments, sales and many other factors. BI is the tool helping you to combine all of those information into one package.

Business Intelligence, to a great extend, is a stack of Data Discovery and Knowledge Management, and is a higher level of Operational Reporting Services. Looking at the diagram below to understand more levels of Data Discovery:

 

The lowest degree of intelligence is Statistics Information. They normally are raw numbers. People normally use standard reports or operational reports to present them. From there you will see what is the fact ? what is actually happening ? Those kind of information is provided daily, weekly, monthly and always be the same. They are good for people who are in operational level. Whereas, while look into the upper level of Intelligence, where BI located. After analyzing raw statistic, BI provide you the insight of information. It not only shows you what is happening anymore, it but also provide you why it is happening and predict the trends what would be happened in the future.

2. How does BI actually work?

I might make you guys feel boring a bit with the all above theory about BI. The next question will be, how does BI actually work ? How could it help us to understand the meaning behind numbers?

Well, pls take a look at the below diagram:

The concept of BI could be understood simply like this: you collect raw data from different sources. For example, in an organisation, you have different departments with different databases, storing different information from: Sales (bills, orders, invoices..), CRM (Accounts, Contacts, Cases..), Supply Chain (products, inventory movement…), etc. Then putting all of raw data into a “Data warehouse”, generating “Cubes” which contains all of those information. And finally, doing analytic queries and  presenting them by using BI Tools such as in graphs or reports…

 When talking about BI, definitely we need to understand the definitions of "Cube", "Dimension" and "Measure".

 

 

In order to provide meaningful information,  BI stores it data in Analysis Database (as shown in the above picture). Different from normal database, organize data in rows and columns, Analysis Database used cubes to present BI data. Cubes used for business intelligence is divided into two units :

–Measure: a measure (or fact) is a numeric quantity expressing some aspect of an organisation’s performance.

–Dimension: a dimension is a categorisation used to break down an aggregate measure to its constituent parts.
In the picture above, the measure here is Total Sales & the dimentions are Year & Product Type.
 

 

 





Promote to Campaign Response button is disabled in CRM Outlook - Tips & Tricks

Hi everyone,

I just wanna quick update a trick to overcome the problem when your "Promote to Campaign Response" button in CRM outlook is disabled.

The scenario here is: you send out an email to a customer via CRM Outlook. Then the customer replies and you want to convert the replied email to a campaign response (even originally the out-going email is not a part of any campaign).

Here is what will happen:

You can click "Track in CRM" button to track the replied email as below:

But when you click "View in CRM", on the email form, the "Promote to Response" is disabled
as below:

The reason for this is:  the email activity needs to be a campaign activity (belongs to a campaign).

So solve the problem, very simple. You go back and "Set Regarding" the replied email to any campaign activity you have in CRM as below:

So now the button is enabled for you to convert into campagin response ! Bingo !

Note: Sometimes, there will be another reasons that could make the "Promote to Response" button disabled also. For example, the person who want to promote an activity to a response need to be the person owns that activity at the first time. Or your CRM Settings lacks of some required features. To ensure everything is correct, you could read this article to learn 4 ways to use "Campaign Response" in CRM : http://rc.crm.dynamics.com/rc/2011/en-us/on-prem/5.0/campaignresponses.aspx 

If you have any problem with it, please email me at linhhk87@gmail.com. Im willing to discuss ;)

Kelly

CRM 2011 Online Authentication for Custom Web application

Hi everyone,

Long time no post.

Today I would like to show you guys how to authentication CRM 2011 Online in coding .Net C#.
The objective is: creating a custom web application capturing contact information, then updating the information into CRM Online.

For CRM On Premise, to do so, you guys could follow exactly the same to this blog: http://blogs.msdn.com/b/crminthefield/archive/2011/06/03/microsoft-dynamics-crm-2011-custom-contact-entry-website-using-early-bound-entity-classes.aspx

However, in order to connect to CRM 2011 Online using IOrganizationService Web Service, you need to change the code a bit to make the authentication through (Remember, CRM On Premise using AD authentication, but CRM Online using Windows Live ID to do that).

So please, following the above blog until the code inside and using the below code for CRM Online Authentication:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Crm;
using Microsoft.Crm.Tools;
using System.Net;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Services.Utility;
namespace CRM_Test2
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        private static ClientCredentials GetDeviceCredentials()
        {
            return Microsoft.Crm.Services.Utility.DeviceIdManager.LoadOrRegisterDevice();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            //Authenticate using credentials of the logged in user;
            string UserName = "abc@hotmail.sg";   //your Windows Live ID
            string Password = "crmadmin@123";    // your password
            ClientCredentials Credentials = new ClientCredentials();
            Credentials.UserName.UserName = UserName;
            Credentials.UserName.Password = Password;
     
            Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
            //This URL needs to be updated to match the servername and Organization for the environment.
            Uri OrganizationUri = new Uri("https://<CRM-Online-Server-Name>/XRMServices/2011/Organization.svc");           //this URL could copy from Setting --> Developer Source
         
           Uri HomeRealmUri = null;
            //OrganizationServiceProxy serviceProxy;  
            using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, GetDeviceCredentials()))
           
           {
                IOrganizationService service = (IOrganizationService)serviceProxy;
          
            //Instantiate the contact object and populate the attributes.                     
            Contact contact = new Contact();
            contact.FirstName = txtFirstName.Text.ToString();
            contact.LastName = txtLastName.Text.ToString();
            contact.EMailAddress1 = txtEmailAddress.Text.ToString();
            Guid newContactId = service.Create(contact);           
         
            //This code will clear the textboxes after the contact is created.
            txtFirstName.Text = "";
            txtLastName.Text = "";
            txtEmailAddress.Text = "";
            txtPhoneNumber.Text = "";

            }
        }
       
    }
}

Notice the red lines in above code. That's the difference between authenticate in CRM Online and On Premise. For Online version, you need to call the function named "GetDeviceCredentials()", which is defined as private function before that. In order to use this function, you MUST to add one more item which is from SDK --> SampleCode --> Cs --> deviceidmanager.cs. So add this new item into your solution.

After adding the deviceidmanager.cs item, open the cs file and add one more nesscessary reference for using the item, which is: System.Security.Cryptography.

Then now build the whole solution ! Everything is done !

Any question or unclear point. Please drop me an email at linhhk87@gmail.com. I will reply you very quick ;)

Have a nice weekend ;)

Unable to configure CRM 4.0 Outlook Client after installing CRM 2011 Outlook Client

Hi everyone,

Today, I spent the whole afternoon to figure out what was wrong with my CRM Outlook Client !!! My situation is I installed CRM 2011 Outlook Client already. However, for testing CRM 4.0 purpose, I need to install CRM 4.0 Outlook Client also. What I have done is:
- Download CRM 4.0 Outlook Client Setup
- Install it
- Open Configuration Winzard in Microsoft CRM 4.0 Outlook Client
- Run the Configuration Winzard (by putting my CRM 4.0 URL into it)
---> I met this error "Action Microsoft.Crm.Config.Client.InstallPstAction failed. LaunchOutlookInstallerProcess failed" and the installation was fail.
I guessed there is a conflict between CRM 2011 and CRM 4.0 Outlook Client. So I uninstall CRM 2011 Outlook Client and try to run the CRM 4.0 Configuration Winzard again. But the problem is not solved !!!!!  In the end, I figured the solution for that. After uninstall CRM 2011 Outlook Client, there is still one file which is not uninstalled and you need to delete it manually. So the steps are:

1. Navigate to C:\Program Files\Microsoft Dynamics CRM\Client\ConfigWizard directory or C:\Program Files (x86)\Microsoft Dynamics CRM\Client\ConfigWizard directory.
2. Delete the CRMForOutlookInstaller.exe file.
3. Do one of the following options:
          - Replace the CRMForOutlookInstaller.exe from the 4.0 client install bits.
          - Run a repair on the Dynamics CRM 4.0 Client for Microsoft Office Outlook installation.
          - Uninstall and reinstall the CRM 4.0 Client.
4. Run the Configuration Wizard.

So now the installation is good already ! I hope it helps. Thankssss ! :D

Linh

Microsoft Business Intelligence - Distinguish between SSAS, SSRS & SSIS

I originally decided to develop my career path in Business Intelligence. To be honest, I am not a great coder. But I am very interested in everything related to databases, information systems and analyzing. Therefore, though BI is absolutely a ideal place for me to start.

Last week, my boss asked me to distinguish SSRS, SSAS and SSIS which somehow are called similarly with each other and are all BI services developed by Microsoft. I searched on the Internet, and figured out that there are also a lot of people having the same question. So spending one day to get my answer.

Before understanding what are SSRS, SSAS and SSIS, you might need to know Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) which both are tools from Microsoft for managing database, doing tasks related to data processing. Whereas:

-  BIDS is a place that you could develop and deploy BI projects. BIDS run in Visual Studio environment. Therefore, when you open BIDS, you will see the interface is exactly the same with when you develop a new VS project. However, you do not need install VS in order to use BIDS. You could install and run BIDS seperately with VS also.

- SSMS is a database management system, which is the "head office" of Microsoft SQL Server.

1. SSAS: SQL Server Analysis Service

    - Is a part of Microsoft SQL Server, which is a database management system.

    - In SSAS, the database is organized in cubes (dimensions and measures).

    - Developers use BIDS to develop and deploy Analysis Services projects. After that they could use SSMS to manage the Analysis Services databases instantiated from these projects.

Using BIDS to create a new Analysis Service project

      The screen how to develop a Analysis Service project in BIDS

 

After deploy a Analysis Service project in BIDS, you could use SSMS to open and manage the Analysis Service database

2. SSRS: SQL Server Reporting Services

- Is as server-based report generation software system. It can be used to prepare and deliver a variety of interactive and printed reports.

- SSRS uses Microsoft SQL Server databases or Microsoft SQL Server Analysis Services databases as a data source for reports.

- Using BIDS to develop and deploy reports.





Using BIDS to create a new Report Server Project

3. SSIS: SQL Server Integration Services

- Is a tool that is used to perform ETL operations (extract, transfrom and load data). It means it mainly is used to transform data from a database to another database.

- SSIS packages is created, deployed and executed in BIDS. However, you could execute the SSIS packages in SSMS as well.

Using BIDS to create a new Integration Service Package

Developing an Integration Service package in BIDS

After creating an Integration Service package in BIDS, you could use SSMS to execute the package

So I hope this blog could help you guys to understand to whole BI tools provided by Microsoft. Thank you for reading !!!

Kelly

Creating a custom report in CRM 2011 by BIDS

Hi,

Long time no post on my blog. Actually, sometimes I wonder whether I should continue this blog anymore or not... No one really care to this one... :D

Anws, still on my way study everything about CRM: javascript, plug-in, silverlight, BI, reporting... A lot of things.. And till now I'm quite happy with what I studied. At least I began knowing what can do with CRM, and how to do that,etc. which are good when you meet the customers :D.

Today, regarding a demand from people on Microsoft CRM forum for knowing how to create a custom report in CRM. Then I decided to wirte this blog to describe step by step how to achieve this purpose.

Firstly, you need to have the following things in order to create and deployed a report successfully in CRM:
- Business Intelligence Development Studio (BIDS) which run on Visual Studio platform.
- Ensure that your CRM includes Microsoft Dynamics CRM Reporting Extensions.
- Your CRM reporting server URL (which you define when setting up CRM system)

Here is step by step:

- Step 1: Open your BIDS. Remember that BIDS run in VS environment, then you will see the VS screen openned up.

- Step 2: Creating a new Reporting project

- Step 3: In Solution Explorer --> Right Click Report --> Create new report --> you will see the Report Wizrad pop up

- Step 4: Creating a Data Source for your report. Data Source is like a link which connects your report to the database storing your data. In order to define a Data Source, you need to know your Database Server and Database Name:

-Step 5: After defining the Data Source, clicking Next --> Query Builder window will pop up. Here is the place that you build queries to retrieve your data.

-Step 6: Click Query Builder --> Click Add Table icon --> Choose all tables that you need (in this example, I chose Account Table )

 
- Step 7: After adding tables, selecting fields that you want to view in the report. You will see the equivalent query to your select below.

- Step 8: Click Next until the end of the dialog and put name of report inside. Now you have a report interface like that:

The left hand-side is all things included in your report. You could see "Name","Address".. fields in the report content.

- Step 9: Now, if you want to add some more parameters to filter your report result. You could go to "Parameter"  --> Add New

- Put available values for the parameter. Here I would like to put the "Account Name" field

- Step 10:  Now the report is nearly done. Then you "Build" the report.
                 After that, need to Deploy the report into your CRM Reporting Server. To do so, open Properties, add "TargetReportServer" by your Reporting Server URL:

- Step 11: Go to your CRM --> Workplace --> Report --> Add new report --> Add existing report --> put the "RDL" file in your report project into the CRM

- Step 12: Testing your report by opening it ! Here you will see the filter condition which is "Account Name" we created before and the result when running report.

Please feel free leave your comment if you dont understand any step. :D. And Please comment a bit if you find this blog is useful :P. Many thanksssss

Create,Update,Retrieve, RetrieveMultiple Records in CRM 2011 with Javascript REST endpoint

Hi,

For who are new with Microsoft CRM 2011, this is a first lession that you should know: How to create, update, retrieve and delete a record of an entity in CRM by using javascript. These basic operations help you a lot in developing your CRM by using the client-side language Javascript.

There are two ways to achieve the results:
1. Using directly the REST Endpoint method like in SDK sample in this link: http://msdn.microsoft.com/en-us/library/gg334427.aspx --> for me, this way is complicated, because you have to bring the whole REST Endpoint function to your code, which makes your code like a mess.

2. Download the REST Endpoint method from SDK  and use it like a library for your javascript code.

Now are steps how to do the second way:

- Step 1: Prepare the following required js:
       + JavascriptRESTDataOperations --> from SDK: SampleCode\JS\RESTEnpoint\JavascriptRESTDataOperations
       + jquery1.7.1.js --> from http://jquery.com/
       + json2.js --> from http://www.json.org/json2.js

- Step 2: Creating your javascript as following:

//Create record
function Create_CRMObject()
{ var CRMObject = new Object();
  CRMObject.Id = "";
  //createRecord function is in jsoperation.js
  createRecord(CRMObject,"your entity set name", createCRMObjectCompleted,errorCreate) 
  // entity set name, for example: AccountSet, ContactSet, etc
}
createCRMObjectCompleted = function (data, textStatus, XmlHttpRequest)
{ var newCRMObjectCreated = data["d"];  
}
errorCreate = function (XMLHttpRequest, textStatus, errorThrown) 
{ alert("Error");
}

//Update record
function Update_CRMObject()
{ 
    new CRMObject = new Object();
 var CRMObject_Id = "put the GUID here";
 var CRMObject."Attribute" = "put any attribute value here"
 //updateRecord function is in jsoperation.js
 updateRecord(CRMObject_Id,CRMObject,"you entity set name", updateCRMObjectCompleted,errorUpdate); // entity set name, for example: AccountSet, ContactSet, etc
}
updateCRMObjectCompleted = function (data, textStatus, XmlHttpRequest)
{ alert("your update is successful");  
}
errorUpdate = function (XMLHttpRequest, textStatus, errorThrown) 
{ alert("Error");
}

//Delete Record
function Delete_CRMObject()
{  var CRMObject_Id =" put the GUID here");
   //deleteRecord function is in jsoperation.js
   deleteRecord(CRMObject_Id,"your entity set name", deleteCRMObjectCompleted, errorDelete);   
}
deleteCRMObjectCompleted = function (data, textStatus, XmlHttpRequest)
{}
errorDelete = function (XMLHttpRequest, textStatus, errorThrown) 
{ alert("Error");
}

//Retrieve Record
function retrieve_CRMObject()
{ //retrieveRecord function is in jsoperation.js
   retrieveRecord("put your record GUID here","put your entity set name", retrieveCRMObjectCompleted, errorRetrieve);
// entity set name, for example: AccountSet, ContactSet, etc
}
retrieveCRMObjectCompleted = function (data, textStatus, XmlHttpRequest)
{ var newCRMObjectRetrieve = data["d"];
  //now you could retrieve any field belongs to your record
}
errorRetrieve = function (data, textStatus, XmlHttpRequest) 
{ alert("Error"); 
}

//Retrieve Multiple Records
function retrievemultiple_CRMObject() 
{  retrieveMultiple("your entity set name","", retrievemultipleCRMObjectCompleted, errorRetrieveMultiple);
// entity set name, for example: AccountSet, ContactSet, etc
}
retrievemultipleCRMObjectCompleted = function (data, textStatus, XmlHttpRequest)
{ var CRMObject = data;
  if (CRMObject.length !==0)
   { for (i=0, i<CRMObject.length, i++)
      { CRMObject[0].id = ... //retrieve any field you want
   }   
   }
  else
  { alert("no record");
  }
}
errorRetrieveMultiple = function (data, textStatus, XmlHttpRequest) 
{ alert("Error"); 
}

Save all of above code in CRMObject.js

- Step 3: Add all 4 above js files in your entity's form. Put the function name for onload or onsave event. Then you could do anything you want now !

I hope it helps. Please email to linhhk87@gmail.com if you have any further question to implement it !




    

       

Using window.showModalDialog in IE9 - CRM Customization

Hi everyone ( I hope someone takes a look at this blog really..),

Here is a very small tip for you guys to control how your window dialog appear in CRM.

The scenario here is you would like to create a new button in CRM ribbon. When you click the button, a window dialog will be pop up.

 The solution is writing a javascript function to show up the window dialog using window.showModalDialog method. All about this method could be found in this link: http://msdn.microsoft.com/en-us/library/ie/ms536759(v=vs.85).aspx

However, your window dialog appeareance depends on kind of browser you use (IE, Firefox, Chrome, etc..). Now i'm using IE9, then figure out one problem with it.

As you see the following javascript:

 window.showModalDialog(url, null, "dialogHeight:600px;dialogWidth:800px;center:yes; resizable:1;maximize:1;minimize:1;status:no;scroll:no" );

In IE9, all the features defined in the javascript above (underlined) could not be fired. Then you have to enbale the Compatibility View in IE9 in order to run it correctly.

To enable Compatibility View in IE9, the following steps:
- Alt + T to see the toolbars
- Go to Tools tab, you will see Compatibility View Setting

 - Then adding your website (here is your CRM) and select Display all website as shown in the below pic

So now, run your javascript again and see your window dialog appear correctly as your code !

I hope it'll be useful.

Creating embedded grid between parent & child entities in CRM form

Last time, I spent 2 days to figure out how to create an embedded-grid showing "child" entities which are related to a parent entity as the following picture:

As you see, in Campaign Activity entity (which is a parent entity), there are many Email entities (which are related entities). So now, I would like to create a navigation when clicked will show you related entities like that.

The key thing needed to understand is: actually, when you create a relationship bet when 2 entities (such as: Campaign Activity & Email), there is an exsiting "tabset" which stores all related entities created. The navigation link just let us to that tabset only. So the task now is: building url to the tabset.

Here is the javascript how to build the navigation link to get the embedded-grid:

function passURLtoNavigation()
{ //Gets navigation item
   var items = Xrm.Page.ui.navigation.items.get();
   for (var i in items)
    {
       var item = items[i];
      var label = item.getLabel();
      if (label == "Email Created")
          { var navId = item.getId();
            var navItem = document.getElementById(navId);
            if (navItem != null && navItem != undefined)
              { var serverUrl = location.protocol + '//' + location.host + '/' +     Xrm.Page.context.getOrgUniqueName();
          var oId = Xrm.Page.data.entity.getId();
          var oType = Xrm.Page.context.getQueryStringParameters().etc;
          var security = "852023";
          var tabSet = "//here is your relationship name";       

                   //building the URL for the navigation link
                   var navUrl = encodeURI(serverUrl + "/userdefined/areas.aspx?oId=" + oId + "&oType=" + oType + "&security=" + security + "&tabSet=" + tabSet);
                   navItem.onclick = function () { loadIsvArea(Mscrm.CrmUri.create(navUrl), false ); };
}
}
}
}

So now, you have the subgrid as shown in the first picture. I hope it will be helpful.

CRM 2011 Customization - Decrypt default Javascript files in CRM Server

 So it's this, the first blog !!!

Two weeks ago, during the time writing a Javascript to customize a CRM button, I found out the way how to decrypt the default Javascript files in CRM server which are might be helpful for you to refer and understand how CRM functions were built.

What I wanted is tracing what they did when building the Campaign Activity entity. Then I exported the default solution and looked what they did with the entity. Opening the customization file, you can see in the picture, there are 3 javascript files applied for it:

As you see, the "CamPaignActivity_main_system_library" was located somewhere in the server and defined in Javascript by "$webresource". It took me several hours to find where actually the file was. Then, the tip is very simple, CRM itself has a database which store all things in the system inside. And in the database, there is an view named "WebResource" where you could look for any webresource belongs to your CRM system. So what you need to do is: opening SQL Server --> CRM Database --> put the query to find WebResource View.

Look at the following pic to refer:

So now I find out the content of the javascript file ! But this content is encrypted. Actually, they just converted them into a different kind of string. Then you need to make some code to decrypt in order to take the real javascript content. The C# code below:

 protected void Button1_Click(object sender, EventArgs e)
        {       
            byte[] binaryData = System.Convert.FromBase64String("//here is your javascript encripted content//");

            string x = System.Text.Encoding.ASCII.GetString(binaryData);              

            Response.Write(x);      

        }

So now, you had the javascript file with decrypted content. I hope it's helpful ;)