Data Source
Next step is to define data source (s) of this dimension.
1. To do this, click Data Source via the submenu 
of the dimension created earlier.
2. Click New to start the creation process of a data source for query: Enter:
| • | Name: give a name to this Data Source. |
| • | Description: give a description to this Data Source. |
| • | Connection: choose a connection previously created. |
| • | Update Method: type of updated used for this dimension. |
Default:
| • | if there is no equivalent record, add the record. |
| • | If there is any, update the record. |
Inclusion: only add records, declining records that already exist.
Upgrade: identical to the standard.
| • | Invalid Processing: it specifies how the processing is, if there are invalid records. |
| • | Invalid Reports: it specifies the report of invalid records. |
| • | Perform Queries: if you want to update added data of each query after each import (and not when the query is performed for the first time after import), select this option. It speeds up the first query execution. Added data are the data of each query, created to make the navigation process faster. |
Click Next to go to the next stage.
In this stage, according to the type of connection previously entered (TOTVS | DbAccess, Protheus® SX or Protheus® Direto), different parameters and some similar ones are requested:
Similar Fields:
Filter: enter an AdvPL expression that is run in each record read by data source, validating the record or not. Through it, records that are brought to the dimension can be validated. This expression ADVPL should return True or False.
Cleanup Condition: enter a SQL expression that is run before starting the import, containing cleanup conditions (for example, clean all data referring to the last month or all inactive customers). If no cleanup condition is entered, SIGADW deletes all dimension records and makes a new inclusion whenever it makes the import.
TOTVS | DbAccess:
SQL (data): click SQL (data) and enter SQL clause that is run to obtain data from data source.
For example: SELECT * FROM SA1990. Here you can enter any clause with SQL syntax compatible with the database that is being accessed and that returns a result to fill out the dimension.
SIGADW tries to relate names of attributes created for this dimension with names of fields returned by the query.
If any relation cannot be made (for example, there is no field of query called CODCLI to be related to the attribute that has this name), the relation must be made manually through script option.
SQL (structure): There is the option of entering a query to make SIGADW know the column structure that comes from this query, without worrying about data. For this, enter a query in SQL (structure) that brings a minimum number of records (the ideal is to bring only one record). If this query is not entered, SIGADW runs the query that was entered in SQL (data), what may cause a delay in this structure validation process.
If data source is based on a connection for an Oracle database, there is the option Optimize to make the execution faster if AdvPL functions are not used in data source.
Protheus SX:
Alias: alias of the file that exists in System data dictionary.
For example: SA1.
Embedded SQL Command: AdvPL standard SQL clause.
Protheus-Direto:
Enter Company/Branch, Path, and Filename with the extension added. For example: TESTE.DBF.
Note that in cases of Protheus-Direto connection, you cannot enter a SQL clause, what limits the extraction and transformation of data. |
After entering the parameters, click Next to define filters/events for data source.
Filters/Events:
For each data source of a dimension, you can define special events that are executed during data import process. These events are in the menu Filters/Events of each Data Source.
The available events are:
| • | Limit of Records: limit of records to be considered in import. |
| • | By Starting Event: Enter an AdvPL expression (a code part) that is run by starting the import. For example, the opening of a file .DBF in a new Alias, that keeps open during the whole import process. |
| • | By Finishing Event: Enter an AdvPL expression (a code part) that is run by finishing the import. For example, the closing of the alias open in By Starting Event. |
| • | By Validating Event: Enter an AdvPL expression (a code part) that is run in each record read by data source, validating the record or not. Through it, records that are brought to the dimension can be validated. |
These expressions in AdvPL must return a logical value, that is, True or False. |
After entering the parameters, click Next to define scripts for data source.
Scripts:
In data source, you must define the script for the transformation of each attribute defined.
In the script, you define the relation between each attribute created for dimension with the origin field of result query of SQL expression previously entered. That is, binding is defined from where values of each attribute are filled out.
In the picture above, you can see that attributes created in the dimension are characterized as fields. Origin field defines the table field used to fill out data of that attribute. As both names (attribute and origin field) are the same, no change is necessary. Otherwise, you need to choose an Origin Field by clicking the list of fields in the origin.
To define a transformation expression of this field, choose Origin Field and use the option of attribute script to define the transformation expression. On this screen, you must enter an AdvPL expression that is run in each record of database.
Example:
In the picture above, to make the content of the field A1_COD be always returned with the character * before it, the script expression is:
Return ‘*’ + DW_VALUE
where DW_VALUE represents the value of origin field.
After making the definition, you can import data to this dimension and use it in cubes.
Importing Dimension:
To make manual import, click the data source created for that dimension and import dimension data by clicking Import, according to the picture below.
Follow the steps above for the other dimensions to be created.