The Data Layer component provides the data to be rendered to the visualizations. It is responsible for the interaction with the connectors (that retrieve the data from each particular data source) and the post-process.
Every data source provides different capabilities in terms of data management. The below are some examples:
Data from CSV or Excel files cannot be filtered by the data source itself. The data is not strongly typed, and, in the case of Excel, it might not be structured at all.
Data from other sources can sometimes be filtered and sorted in the data source. This is the case, for example, with relational databases. The data is structured, since you always get the data from a table, and strongly typed, since for each column you know exactly what’s the datatype and you know the database will enforce it.
The Data Layer component handles all of these cases. For CSV and Excel files, it provides data type detection. In the case of Excel files, it also provides detection for data tables inside a single sheet. For query based providers (SQL based or MDX based in the case of MS Analysis Services), it generates the query based on the requirements in the dashboard trying to leverage all of the capabilities of the given data source.
Some of these tasks are resolved by the Engine component, and some other by the specific connector for the data source. The Engine component caches data in order to improve performance and minimize the access to data sources. It also resolves some other post-processing tasks like pivoting the data, resolving calculated fields and data management operations (filters and sorting) not supported by the specific data source being used.
Indeed, the engine is a dual implementation running completely in memory for small amounts of data to get the best performance and using an embedded database when the amount of data cannot be handled in memory. All of the thresholds involved when deciding which implementation to use (in-memory vs embedded database) are completely configurable by administrators.
One of the more powerful features in the product allows you to join (or merge) data from different data sources. For instance, you can display Sales figures loaded from an Analysis Services cube or Salesforce along with Budget numbers loaded from an Excel Spreadsheet stored in your SharePoint serve, Google Drive, or others, in the same widget.
This data merging is processed completely by the data layer in a transparent way, using the embedded engine and then enabling data management operations like calculated fields on top of the merged data.