When creating a query in SQL or using the Query Builder, it is important to note that there are no restrictions on how much data and which data you decide to pull from the database. Every time a dashboard is opened or updated by any user, its query runs to pull data from the database that is then displayed on the dashboard. Depending on the required dashboard, this may contain a lot of data or complex data comparisons that can cause optimization issues and time delays in entire system.
Minimize Data Selection
When selecting which fields from which tables you would like to use within your dashboard, never select 'All'. This will pull all the data from all the columns on the selected data table, using much more resources as a result.
In the above example, we wish to create a dashboard displaying Documents per Customer. Therefor we only need the 'Account' table's 'Name' field and the 'Document' table's 'DocumentNo' field. The 'DocumentDate' field is selected for use on a Date based filter.
Create a Query Filter
Continuing from the above example, Lets say we wish to only see Sales Order's per customer rather than every Document per customer. It would be a waste of resources to pull through the data of all Document Types and then filter it down to Sales Orders, instead we apply the filter to the query to tell it to only pull the data we want. This way we also avoid having to pull in another data field, namely 'DocumentType', which would be necessary to create the required filter on the front end.
Here we are adding a filter on the query that generates the dashboard data rather than on the dashboard itself.
You can do this by either clicking the 'Filter' button at the bottom of the Query Builder, or by navigating to the 'Data Source' tab and then clicking the 'Filter' button.
In order to create the same filter from the front end, an additional field, either 'DocumentTypeid' or 'Name' from the 'DocumentType' table is needed.
Select the Pivot Table, navigate to the 'Data' tab and click 'Edit Filter'. The 'DocumentType_Name' field will have to be added to the Pivot Chart element as a 'Dimension' or 'Hidden Data Item'. You can now call the DocumentType_Name on the front-end filter.
The above examples end up with the same resultant dashboard, but the latter method requires more resources.
Avoid Historic Data When Possible
In most cases, it is not necessary to view and analyze records from more than a year ago or from the previous financial year. We can avoid pulling historic data onto the dashboard by the use of a date filter. A date filter is a must in almost all reports in order to trim down the resources used up when processing dashboards and reports.
Add a date filter element with the appropriate data field attached. In this case, we have used 'DocumentDate'. Right-Click on the element and select 'Edit Periods'.
The 'Edit Periods' window will open. Here you can select date periods from the left and add them to the filter selection by clicking the arrow button. 
Make sure to select a default period so that the filter takes effect immediately, preventing the loading of the full data set. Custome periods can be added here as well.
From the date filter element, the user can then switch between date periods and can still view all the records without having to display all the records at the same time.
