Microsoft Dynamics CRM Online has a number of capabilities that allow customers to surface business data that helps them drive decisions and interact with their customers more effectively.

Capabilities that are available within CRM Online include:

• views
• charts
• dashboards
• Microsoft SQL Server Reporting Services reports
• Microsoft Office Excel integration that allows users to easily build self-service reports by using the Power BI features PowerView, PowerPivot, and PowerQuery

As the volume of data held within the CRM Online database continues to grow it becomes more important than ever to think about your BI strategy and determine the most effective mechanisms for reporting and visualizing large datasets.

In a CRM Online environment, the reporting infrastructure is shared and separate from the database. In this architecture, although customers share the resources required to run the report, each report runs against the customers’ individual database instance. Additionally, with CRM Online, users can run as many reports as they need whenever they want to run them to meet business goals. We do not place time restrictions on reports.

The reporting capabilities built in to CRM Online are designed to let users run reports on datasets that span shorter periods of time. Considering this, Microsoft Dynamics CRM has the following fixed settings:

• Reports and queries can execute for up to five minutes. When the maximum period is reached, the report will time-out and a message is returned to the user. Within the five-minute duration, reports and queries are allowed to span large datasets that are beyond 50,000 records, which provides significant flexibility to satisfy most operational reporting needs.
• To improve query response, we recommend that detailed reports minimize the display of large numbers of records. To do this, apply suitable filtering to reduce the number of records that are returned. When you create aggregated or summarized reports, queries should push the aggregation to the query rather than fetch detailed records to perform aggregation in the report. This can be done by using Fetch XML aggregation. More information: Use FetchXML aggregation
• For charts and grids displayed in dashboards, Microsoft Dynamics CRM allows users to run queries that have a dataset that is under 50,000 rows. Should a user run a dashboard query that span a dataset of 50,000 or more rows, Microsoft Dynamics CRM returns the message “The maximum record limit is exceeded. Reduce the number of records.” The dataset practical setting helps to ensure optimal performance of the CRM Online application.

For more on...

• Tips and solutions for reporting
• Third-Party Microsoft Dynamics CRM adapters for SSIS
• ETL tools

... please read on here

See also

• Dynamics CRM OData Feeds and Power Query: What’s the [Record]? - link
• Dynamics CRM 2013 and Power BI for Office 365 – Part 1 and Part 2

In a previous blog post "Visualize hierarchical data in CRM 2015" I showed you how to visualize the out-of-the-box hierarchy for Accounts.

To enable a hierarchy, you need to have at least one self referential 1:N relationship. After creating the relationship, you can select one 1:N relationship as hierarchy relationship. In this blog post I'll show how to do this for the Contact entity

Go to Settings > Customize and select Customize the System.

Fig. 1 - customize the system

x

Expand Contacts

Fig. 2 - Contacts entity

x

Click 1:N Relationships and then New 1-to-many relationship

Fig. 3 - relationships

x

Select Contact as “Related Entity”. Provide a name. Specify this relationship as "Hierachical" . Select “Yes” to “Hierarchical”. Provide a Display Name for the Lookup Field

Click Save and Close

Fig. 4 - define relationship

x

Navigate to Contact > Hierarchy_Settings and click New

Fig. 5 - hierarchical settings

x

Enter unique name and select any card (quick view form - you can customize those)

Click here in the 'Please click here to mark a relationship as hierarchical' string to open the dialog

Fig. 6 - mark as hierarchical

x

Select the newly created referential 1:n relationship and click Done

Fig. 7 - select relationship

x

Note the Hierarchical Releationship box now lists the relationship

Fig. 8 - verify selection

x

Navigate to Forms, and open any form - eg Contacts

Fig. 9 - edit form

x

Add the relationship lookup field ("Parent Contact") to the form, and then publish all customizations

Fig. 10 - add Lookup field

x

Now done with the configuration set the “Parent Contact” for several contact records and see Hierarchy view

Fig. 11 - define parent

x

Fig. 12 - contacts with hierarchy icon

x

Fig. 13 - visualization one branch

x

Fig. 14 - visualization another branch

x

Note: Dynamics CRM 2015 is in beta when writing this blog post. Things can change between now and the actual launch of the service

Model and visualize hierarchical data to gain valuable business insights

Previously, you were able to create hierarchies of related records using self-referential relationships, but you had to iteratively query for the related records.

In Dynamics CRM 2015 - expected to be generally available in the fourth quarter of 2014 - new capabilities will let you query and view the records as hierarchies. To query an entity as a hierarchy you must enable a One-to-Many (1:N) or Many-to-One (N:1) self-referential relationship as hierarchical.

You can gain valuable business insights by visualizing hierarchically related data. The new visualization feature gives you a hierarchical view into the data. You'll be able to enable visuals for specific system entities and custom entities after you have updated the hierarchy settings for the entity.

Users can choose between a tree view, which shows the entire hierarchy, and a tile view, which is a detailed view of a smaller portion of the hierarchy. You can explore a hierarchy by expanding and contracting a hierarchy tree. Also, you can compare the attributes between the records at multiple levels in a tree and perform actions on one or more records right from the tree view.

Once defined, the hierarchy settings enable visualization in the CRM Web application and in Microsoft Dynamics CRM for tablets, but, for the tablets, in a modified format suitable for the smaller form factor. Because these visualizations are solution components, they can be transported between organizations like any other customization. You can configure the attributes shown in the visualization by using the customization tools in the CRM Web application. There is no requirement to write code.

Example

The Account entity is hierarchical by default - I can designate parent and childs to a an account. In the below example I've created the top account "Microsoft Corp", with two childs "Microsoft Europe" and "Microsoft Middle East and Africa (MEA)". "Microsoft Europe" has two childs "Microsoft Denmark" and "Microsoft Sweden", and "Microsoft Middle East and Africa" has one child "Microsoft Turkey" (obviously there are more childs, but this is just an example)

Fig. 1 - Accounts with Hierarchical Icon

If I open Microsoft Europe I can check to see the parent as well as the childs

Fig. 2 - Defining an accounts Parent Account

Fig. 3 - Child Accounts

Before CRM 2015 the above is what we had - now we have a great visualization. Clicking any of the Hierarchical Icons (see Fig 1) will open the visualization.

You can

• navigate the hierarchy by clicking the tiles as well as using the tree view to the left
• nagivate to lower layer by clicking anchor arrow under a card
• open a record by clicking icon on right upper corner.

Fig. 4 - Top of Hierarchy

Fig. 5 - Note the Open Revenue on Microsoft Europe (rolled up from child accounts)

Fig. 6 - Child Accounts of Microsoft Europe

Fig. 7 - Open Revenue on Microsoft Denmark and Sweden - rolled up to Microsoft Europe

Fig. 8 - You can navigate the hierarchy using the left tree view control too

So now its easy for you to traverse and explore the hierarchy. For example, from the hierarchical view for accounts, you can:

• See how an account is doing in overall revenue
• Drill into tiles for sub-accounts to see where the deals are coming from
• Find out who is working an account and enlist help from others by sending email or sharing the account with other salespeople
• View important details about each account, such as credit limit and latest activity posts for the account

See also

• New Microsoft Dynamics CRM breaks down silos between marketing and sales - link
• CRM 2015 Release Preview Guide - link