This topic explains how to create a routine to extract and process data from the system databases through a SQL script. This data will be stored in templates of the Microsoft Analysis Services database so that it can be used to generate intelligence reports.
Access to the Analysis Services database must be previously configured in the Intelligence Solution section, located in the Settings tab of the Administration module. The database and the data to be extracted must be specified through a SQL script. This script will extract the data and reorganize it in an external database (configured in the web.config file in the installation folder for the system) that will be accessed by Microsoft Analysis Services, where the data to be used in reports of the Intelligence solution is stored. This script processes and simplifies complex data, allowing more intricate data to be included in intelligence reports, which would not be possible through direct access between Microsoft Analysis Services and the complex structures of the system databases.
Note that the SQL script can only query and extract data from these databases and cannot make any changes in them. If a database is not specified in the script, the queries will be directed to the external database, by default. In addition, the databases that will be accessed must be registered in the system installation file and the due permissions must be granted to ensure access. The history of the task indicates whether the task was successfully executed or if there were any errors.