Build dashboards that aggregate data from NetSuite and Dynamics CRM using Qlik Sense

One of the biggest challenges to gaining actionable insight is getting your data into your BI tool. Accomplishing business intelligence with multiple data sources like Dynamics CRM, NetSuite, and Google Sheets often involves manual processes like rekeying data into Excel spreadsheets. Another challenge is keeping this data current: Your most valuable data may be constantly changing, and you don't have the time to be constantly rekeying.

Monitor Back Office and Front Office Systems in Real Time

CData drivers enable connectivity to business intelligence tools without rekeying or an ETL. Creating mashups of your CRM, ERP, and accounting systems in business intelligence tools like Tableau, TIBCO Spotfire, and Qlik can give you a 360 degree perspective on your organization. Manage your back office and front office systems from the same dashboard. Graph opportunity history from presales to service execution.

CData drivers are standard database drivers that take advantage of new technology to enable real-time analysis and easy connectivity. The drivers model your data source's API (application programming interface) as a database. APIs can be interacted with in real time; data access standards enable connectivity to the major business intelligence tools. The drivers bring these two capabilities together by translating communications between business intelligence tools and APIs.

Connectivity to APIs preserves the business logic of Dynamics CRM and NetSuite: The CData ODBC Driver for Dynamics CRM enables access to all of the entities in your Dynamics CRM system. The driver models Dynamics CRM entities as relational tables. When you connect, the driver retrieves the metadata for Dynamics CRM entities and dynamically generates the schemas, or table definitions. Reconnect to pick up changes like custom fields.

The CData ODBC Driver for NetSuite makes it easy for third-party tools to interact with NetSuite business logic. The driver surfaces all of the entities in your NetSuite account, including transactions, lists, and saved searches.

Secure and Streamline Data Access

CData drivers help to streamline the flow of your business critical data to the sales reps and marketers who need it. Connecting to external CRM and ERP data makes access control easier. CData drivers are an easy way to provide your sales reps with on-demand access to ERP while reducing the load on IT.

Follow a standard process to connect to disparate data sources like NetSuite and Dynamics CRM. ODBC has built-in support on Windows and the driver is managed with Windows tools. Access to ODBC data sources is controlled through a DSN (data source name): Simply provide the credentials to your CRM account, the address of your server, and your CRM version in the ODBC Administrator tool. CData drivers for Dynamics CRM support CRM Online Office 365, CRM 2011, CRM 4.0, CRM 2013, and CRM 2015. IFD deployments are also supported.

See the driver help documentation for a guide to create a DSN for Dynamics CRM.

The ODBC Driver for NetSuite makes it easy to connect to any NetSuite system: Simply set the User and Password properties, along with the Id of your company account. See the driver help for a guide to create a DSN to NetSuite.

You can use standards for protecting the confidentiality and authenticity of your data to connect to NetSuite and Dynamics CRM. The drivers support TLS (transport layer security) by default to secure the connection to cloud-based data sources like NetSuite and CRM Online. Connection properties are available to secure connections to Dynamics CRM on premises with TLS. The CData Drivers for NetSuite also have full support for NetSuite Web Services permissions: For more information, see the connection guide to NetSuite.

Connect to Dynamics CRM and NetSuite in Qlik Sense

Follow the steps below to add ODBC connections to Dynamics CRM and NetSuite DSNs in Qlik Sense:

1. In a new Qlik Sense app, open the Data Load Editor from the toolbar.
2. In the Data Connections pane, click Create New Connection -> ODBC.
3. Select your DSN.
4. Click Insert Connection String. A line like the following is added to your load script:

   LIB CONNECT TO 'MyDSN';

Mash Up CRM Accounts and ERP Invoices

Follow the steps below to start seeing associations in your data. You will create a simple sheet that draws from invoices in NetSuite and accounts in Dynamics CRM to show account details and the total estimated gross profit for each account. After adding the ODBC connection, load account information from Dynamics CRM:

1. In the selection for the DSN for Dynamics CRM, click Select Data and select the Account table.
2. Select the columns you want and click Insert Script. You can use any SELECT statement supported by Dynamics CRM. This article uses the query below to select Dynamics CRM account details:

   CRMAccount:
   SQL SELECT
   Name,
   Description
   FROM CData.Account;

3. In the selection for the DSN for NetSuite, click Select Data and select the Invoice table.
4. Click Insert Script after you have selected the columns you want.

   This article uses the query below to retrieve estimated gross profit from NetSuite invoices:

   ERPInvoice:
   SQL SELECT
   Entity_Name AS Name,
   EstGrossProfit
   FROM CData.Invoice;

Qlik associates columns that have identical names into a single logical table. With the previous data load script, the EstGrossProfit field from the Invoice table in NetSuite is concatenated onto the Name and Revenue fields from the Account table in Dynamics CRM.

Drag and drop dimensions and measures to create data visualizations:

As you make selections, the data visualizations display excluded, possible, and alternative values with color codes that make these associations intuitive:

You can enrich the associations in your data by using CData drivers to set up automatic syncs of your CRM, ERP, and accounting data.