Expand the <//Solution : name / Data Sources> folder and select the Data Source to present the editor used to edit Data Sources.
The main goal here is to specify a connection to the source system.
The Procedure
Right click the Data Sources Folder node. Click New Data Source.
Define the fields presented then click Finish.
Define and configure the connection.
The resulting Data Source connection can be tested by clicking on the Test Connection button.
Data Source - Right Click Options
|
New Data Source Table |
Click to add a data source table definition to the solution. |
|
New Folder |
Click to add a folder. |
| New Script | Click to open the form that allows for definition of a script. |
|
Save As Template |
Data Source Tables can be saved to the Template library for use in future projects. Select a Dimension Table and click. The displayed dialog gives the user the opportunity to either Save the Table as New or to Overwrite an existing Table. |
|
Copy |
A Data Source Table can be copied to clipboard by clicking. |
|
Delete |
Click to delete the selected Data Source Table. An "Are you sure?" dialog requiring a confirmation is displayed before the delete is performed. |
Data Source - Command Buttons
|
Save |
Click to save the information defined in the Details and the Column Mapping panes to the Solution database. |
|
Reset |
Click to undo any edits and or deletes and redisplays the original configuration as of the most recent Save. |
|
Test Connection |
Click to test and validate that a connection can be made to the Data Source. NB! For File and Excel connections thebutton only tests that access to the specified folder is OK. |
| Create Default PowerShell Script | Click to create a default script meant to contain a library of functions to be used in source expressions or scripts either implicitly or via the {{EmbedDefaultScript}} macro. |
|
Generate Table Type Folders |
Click to generate folders grouping data source tables by Table type. (Only visible if data source has no existing folder structure) |
| Estimate Compression Savings | Click to run a script that can help you identify the best candidates for page compression. |
Main Settings
|
Name |
Name of the Data Source Table |
|
Main Filter |
Define a data source wide main filter on the rows in each Data Source Table, i.e. the Where Clause. For example, the Main Filter: Company = 'CompanyX' will ensure only the rows for CompanyX are extracted from the source data. Users can override the main filter setting by either unchecking the 'Apply Main Filter' check box or removing the filter from a source SQL Expression. |
|
Description |
Optional |
|
Comment |
Optional |
Source Connection Settings (Databases)
|
Connection |
Shows source connection type (i.e. Oracle) and method (i.e. SSIS). |
|
Server Name |
Select from the pull down list the Server instance where the source database resides. This field can also be edited directly by clicking in the field and typing. If the Linked Server Connection Method is used, a new linked server registration can be added by clicking the link button. An existing linked server registration can be deleted by clicking thelink button and edited by clicking thelink button. |
|
Database |
Enter or select from the pull down list the Source Database name. |
|
Schema / Owner |
Enter the database Schema name (dbo is the default for MSSQL). |
|
User name |
Enter the User name log in name (is the default). |
| Password | Enter the Password log in name (is the default). |
| Timeout | Enter the number of seconds to wait for a result from the source server when executing source queries (applies to import of metadata, previews, validations and transfers). Caution: Setting timeout to 0 is not recommended as it does not automatically free up resources when the source query does not return. |
| Row Limit | Enter the maximum number of rows returned in data previews. Caution: Setting row limit to 0 is not recommended as data previews might consume a lot of system memory resulting in poor performance or "System out of memory" error messages. |
Source Connection Settings (File (CSV, XML, JSON) and Excel (and MS Access) Source Types)
|
Connection |
Shows source connection type (i.e. Oracle) and method (i.e. SSIS). |
|
Folder |
Enter or browse and point to the catalog containing the source files. |
|
File type filter |
Enter a file type filter in the format of namefilter1.ext;namefilter2.ext. Example: *.csv;*.dat |
|
Default File (only for File Source Type) |
Useful for identifying row delimiter information. The invisible characters CR and or LF is added to the file to make for easier identification of correct row delimiter settings. |
| User name | Enter the User name log in name (is the default). If this is left blank for MSSQL, integrated security is used. If the Linked Server Connection Method is used, you can specify advanced security settings via the Edit link button. |
|
Password |
Enter the Password log in name (is the default). |
|
Column delimiter (only for File Source Type) |
Select from the pull down list the Column Delimiter used in the delimited flat files. |
|
Row Delimiter (only for File Source Type) |
Select from the pull down list the Row Delimiter used in the delimited flat files. |
| Code page (only for File Source Type) | Select from the pull down list the Code Page used in the delimited flat files. |
| Locale (only for File Source Type) | Select from the pull down list the Locale used in the delimited flat files. |
| Text qualifier (only for File Source Type) | Select from the pull down list the Text Qualifier used in the delimited flat files. |
| Header row | Indicates if the files / worksheets has header rows. |
| Header row delimiter (only for File Source Type) | Select from the pull down list the Header Row Delimiter used in the delimited flat files. |
| Header row count (only for File Source Type) | Enter the number of rows used for headers. Usually 1. |
| Timeout | Enter the number of seconds to wait for a result from the source server when executing source queries (applies to import of metadata, previews, validations and transfers). Caution: Setting timeout to 0 is not recommended as it does not automatically free up resources when the source query does not return. |
| Row Limit | Enter the maximum number of rows returned in data previews. Caution: Setting row limit to 0 is not recommended as data previews might consume a lot of system memory resulting in poor performance or "System out of memory" error messages. |
| Skip SSIS (only for Excel Source Type) |
Skip SSIS allows you to import Excel data without having to install Jet / ACE OLEDB Provider and/or Integration Services. Also 32-bit vs. 64-bit issues can be avoided by checking this option. Note! |
DW Connection Settings (Databases)
|
Use Separate DW Connection |
Check to define a separate target data warehouse database. |
|
Database |
Enter or select from the pull down list the Source Database name. |
|
Schema |
Enter the database Schema name (dbo is the default for MSSQL). |
|
User name |
Enter the User name log in name (is the default). |
| Password | Enter the Password log in name (is the default). |
| Timeout | Enter the number of seconds to wait for a result from the source server when executing source queries (applies to import of metadata, previews, validations and transfers). Caution: Setting timeout to 0 is not recommended as it does not automatically free up resources when the source query does not return. |
| Row Limit | Enter the maximum number of rows returned in data previews. Caution: Setting row limit to 0 is not recommended as data previews might consume a lot of system memory resulting in poor performance or "System out of memory" error messages. |
Status
The Status pane displays the activity and status of updates from the Data Source to the Data Warehouse.
For Name, Description, Comments editing click here.