Creating a Data Set

With M-Files Reporting Data Services, you can export data from M-Files to external databases (SQL Server). You can select which data is exported, set scheduled data exports, and create reports of the exported data set. M-Files Reporting Data Services is installed when you install M-Files Server.

Before you create a data set, we recommend that you create a new target database to which the new data set is exported.

To create a data set:

  1. Open M-Files Admin.
  2. In the left-side tree view, expand a connection to M-Files server.
  3. Expand Document Vaults.
  4. Expand a vault.
  5. Select Reporting and Data Export and then select New Data Set on the task area.
    Result:The Data Set Properties dialog opens.
  6. In the Name field, enter a name for the new data set.
  7. In the Data to export section, select Add Objects... to select the objects to be exported.
    Result:The Add Objects to Data Set dialog is opened.
  8. Use the Objects to export drop-down menu to select the objects to be exported on the basis of the object type.
    You can select Filter Objects... to refine the selected objects by specifying property conditions that the objects must meet to be exported. For more information on filtering objects by properties, see Property-Based Conditions.
  9. Optional: Select the object that you just added to the Data to export section and select Add Property... to add for the selected object type a property that you want to be exported.
    1. Use the Property to export drop-down menu to select the property to be exported.
    2. Go to the History and Old Values tab.
      On the History and Old Values tab you can define whether you want to export the change history and previous values of the selected property.
      Result:The History and Old Values tab is opened.
    3. Check the Export the change history of this property check box if you want to export the change history of the selected property.
      The change history adds an entry of each change in the property value to the data set. This makes it possible to answer questions such as "What was the total value of deals closed in August?" by exporting the change history of the property value Closed and pinpointing the objects for which the value of said property changed from No to Yes during August.
    4. Check the Export the old values of this property, using sampling check box if you want to export previous values of the selected property and use the Sample at drop-down menu to define the frequency of the sampling (daily, weekly, monthly, or yearly).
    5. Select either Full history, Fixed-length period, or Starting from date option to define the history length for the change history and old values of the selected property.
      If the frequency of sampling is high (for instance daily), it is recommended to restrict the length of the history period to avoid expanding the database unnecessarily and to increase the speed of the export function.
    6. Select OK to close the Property to Export dialog.
  10. In the OLE DB connection string field, enter the connection string for connecting to the target database or select Define... to define the connection string.
    Note: We recommend that you use a separate target database for every new data set.

    If you use Microsoft SQL Server as the target database, it is advised to use Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) as the data provider, and to use either Simple or Bulk-logged as the Recovery model setting in the target database. This will significantly improve the speed and performance of exporting data sets.

    For more information on database connection strings, see Connections to External Databases for Object Types.

  11. Go to the Advanced tab.
    Result:The Advanced tab is opened.
  12. In the Use the identity of the following user when reading data drop-down menu, select the user whose identity you want to use for transferring data from M-Files to the reporting services.
    The most suitable user is a "regular" user without any extended rights. If you use, for example, your own user identity that has a system administrator role, data that you do not want to expose to all users could end up in the reports displayed by the client software.
    If the metadata structure of the vault is translated into multiple languages, the metadata language of the exported data set depends on the vault language settings of the user whose identity is used for reading data.
    If you have the Electronic Signatures and Advanced Logging module in use, you must select (M-Files Server) as the user.
  13. Optional: Check the Export data on a scheduled basis option check box and select Schedule... if you want to export this data set on a scheduled basis.
    It is recommended to schedule the data export to be performed once a day/week/month. The reports are updated at the same time. If you do not select scheduling, the data will not be updated after export. You can also create a separate update link in the reporting services so that the user can update the report in the client software whenever desired. For more information, contact our customer support in M-Files Support Portal or your M-Files reseller.
  14. Optional: On the Advanced tab, define an alias for the data set.
    Use semicolons (;) to separate many aliases.
    When automatic aliases are in use and you enter a name on the General tab, the Aliases field on the Advanced tab is automatically filled in. The alias has the format <predefined prefix>.<name>. Configure automatic aliases for your vault in Advanced Vault Settings.
  15. Optional: Select Export Now to export the data set right away.
    You can select Status of Exporting... to view the status of the exporting process when the exporting is in progress.
    Note: When you start an export for the first time, the system creates and populates tables with data. New exports populate new tables while the old tables stay available for reporting. Once completed, old tables are replaced with new ones. The system exports all data by default, but you can use metadata filters to control the export set. For more detailed information, see Reporting and Data Export.
  16. Select OK to finish creating the new data set.
The data set that you have just defined is added to the Reporting and Data Export list. The data set is exported either automatically on a scheduled basis or manually, depending on the settings that you have provided.
Now that you have created a data set, you can use it to create a report (see Creation of Reports and the Required Software) and then create a report object in M-Files to read the report (see Creating a Report Object for a Report).