Leverage “Quick Find” for “Quick Data Entry”

As the name of the feature suggests, users of Dynamics CRM can use “Quick Find” to quickly and easily search for the records in the entity. You can configure the Quick Find view for a specific entity by defining “view” columns and “find” columns. CRM searches for the text you enter in the search box (including any wild cards) in the “find” columns defined for the Quick Find view.

In addition to this functionality, you can also use this feature for quick data entry. The following example illustrates this.

In this example, the company uses CRM to provide services to publicly traded companies. Companies are stored in the Account entity. Each company has been populated with its unique Ticker Symbol. Account Quick Find view has been configured with Ticker Symbol as one of the “Find” columns. The following screenshot shows the sample company data.

With the Ticker Symbol defined as one of the “Find” columns for the Quick Find view, you can now enter the unique Ticker Symbol anywhere in CRM where you need to enter the Company name. This includes Advanced Find and forms where the Account (lookup) needs to be entered.

For example, when you enter “MSFT” in the Account (lookup) field in the Opportunity form and tab out, CRM populates “Microsoft Corporation” in the field.

 

Because Ticker Symbols are unique across the companies, CRM finds exactly one record based on the Ticker Symbol you enter. This technique can also be used for the other unique fields such as Account Number, Short Name or Alias.

Sending Direct Email and Automated Email to records in a custom entity in Dynamics CRM

Prior to Dynamics CRM 2013, you could not send direct email to records in a custom entity as you could for the standard entities such as Account, Contact, Lead and User.

With the introduction of the following option on the entity configuration when you create a custom entity, you can now enable the entity for sending Direct Email.

Selecting this option (as in the above screenshot), adds the Send Direct Email button to the main ribbon tab for the entity.

For example, I have a custom entity named Workorder with the “Sending email” option checked. When I select the records in the Workorders view, I see the “SEND DIRECT EMAIL” button on the ribbon which enables me to send direct email for the selected records. Using this feature, a user can send direct email to the email address on the selected records.

If you do not have an existing email address field in the entity, CRM creates one as seen in the screenshot below.

Note that after you enable this option, it cannot be disabled.

Another not so obvious and undocumented feature that becomes available when you select this option is an ability to send email to the email address on the record using a Workflow.

Prior to CRM 2013, in order to send email to the Email address on the custom entity records, you had to select the “Allow messages with unresolved email recipients to be sent” option in System Settings (shown below). You could then populate the “To” field in the email with the Email address on the custom entity record in a custom workflow activity which requires custom development.

With the “Sending email” option, you can do this without enabling the above system setting. This can now be accomplished using a standard Workflow as illustrated in the screenshot below.

Upgrade PowerPivot Data Model to SSAS Tabular

In the previous articles, I discussed the steps for building PowerPivot data models for Dynamics GP Sales and Financial (GL) data using PowerPivot for Excel. This is a great way for power users to build powerful business intelligence solutions without expensive hardware and difficult to use development tools. It is also a great way for professional BI developers to prototype a complete solution quickly.

However, there will be situations where PowerPivot based solutions may not meet your business needs. In this case, there are two upgrade paths available to migrate your PowerPivot data model. These are PowerPivot for SharePoint and SSAS Tabular. In this article, I will discuss SSAS Tabular.

Here are some of the reasons why you may want to upgrade from PowerPivot to SSAS Tabular.

Security

Tabular model databases can use row-level security, using role-based permissions in Analysis Services. For example, you can configure security to enable Sales Manager to view all sales data while Salesperson to view only his or her sales data.

Power Pivot workbooks are secured at the file level. A user who has access to the file can view all the information contained within the workbook.

Scalability

PowerPivot for Excel has an artificial file size limit of 2 gigabytes, which is imposed so that workbooks created in Power Pivot for Excel can be uploaded to SharePoint, which sets maximum limits on file upload size. One of the main reasons for migrating a Power Pivot workbook to a tabular solution on a standalone Analysis Services instance is to get around the file size limitation.

Partitioning

Partitions, in tabular models, divide a table into logical partition objects. Each partition can then be processed independent of other partitions. For example, a table may include certain row sets that contain data that rarely changes (historical sales data, for example), but other row sets have data that changes often (current year’s sales data).

Query and Reporting

Excel and PowerView are your only options if you use PowerPivot. With SSAS Tabular, you can use many other reporting tools including SQL Server Reporting Services (SSRS).

You will find detailed comparison of SSAS Tabular and PowerPivot here.

Before you upgrade your PowerPivot data model to SSAS Tabular, you will need the following:

1. SQL Server Analysis Services instance running in Tabular mode (I used SQL Server 2014)
2. SQL Server Data Tools (SSDT) (This is required if you use Visual Studio to import your data model to SSAS Tabular. Please see below for details)

There are two ways you can import your PowerPivot data model to SSAS Tabular.

Option 1:

SQL Server Management Studio option: Import directly to SQL Server using Restore from PowerPivot option as shown in the following screenshot

Option 2:

Visual Studio (SSDT) option: Import using Visual Studio (SSDT) as shown in the following screenshot

Once completed, you will be able to connect to the SSAS Tabular data model on the server and create a report in Excel and other reporting tools such as SSRS.

Here’s the PivotTable created using Excel. You will notice that the presentation of PivotTable fields is slightly different from what you see when you create a PivotTable from PowerPivot data model. For example, KPIs have their own folder. However, the end result is the same.

Because the data is no longer stored within the Workbook but is stored on the server, the file size is very small as shown in the following screenshot.

After you have migrated the PowerPivot data model to SSAS Tabular, you do not need the original Excel workbook any more. You will need to use Visual Studio to make changes to the Tabular data model and implement new features such as security and partitioning.

If you would like to learn more about SSAS Tabular, you will find step by step tutorials here.