The first step in building the data flow is adding a data source. You can add one data source if you want to connect to a single source, or add multiple data sources to copy tables from different sources and create a data mash up.
Pyramid supports a variety of datasources, including local files, relational databases, unstructured databases, web sources, and scripted sources.
Generally, once you connect to the data source you'll select which tables to add to the data flow; those tables will be copied from the source to the new Pyramid data model. Depending on your data source, you may be able to directly query the given database without copying any data into Pyramid or creating a new model output.
- Click here for a list of supported data sources.
Select a Data Source
Drag the relevant data source onto the canvas, and in the Properties panel:
- Select the server and database you want to query (blue highlight) - you can select the server from the drop down, or click the Add Server button to add a server.
- Click the Refresh icon to refresh the server selection
- Once the server has been selected, choose the database - click the Refresh icon to refresh the database selection
- Alternatively, select Direct Query Data Source
Select the tables you want to include in the data model - use the filter window to find tables quickly (green highlight)
- Refresh the table selection - if tables have been added or removed from the datasource, click the refresh button (red highlight), so that the changes will be reflected in the table selection
- Add the designated tables as Table nodes, or as a multi-select Tables node (orange highlight below).
- Table Nodes: a select node will be added for each selected table. Each table may then be previewed, and columns can be removed from given tables.
- Tables Nodes: one multi-select node will be added, containing all the selected tables
- A description may be added below the Table Selection (yellow highlight)
When Table nodes are added, click on a node to open its Preview panel.
Click on a select column node to open its Properties panel, where you can:
- Rename the table, under Resulting Table Name
- Replace the selected table node with a different table that wasn't previously selected (blue highlight)
- Select the columns you want to include, or change the column types, from the Column Selection drop down menu (green highlight)
- Refresh the columns in the selected table to update changes in the data source (indicated below in red)
- Add a description (orange highlight)
To remove a table from the data flow:
Select Node: select the relevant node and click Delete. For more information, see Data Sources.
Multi-Select Node: click the node and under Select Multiple in the Properties panel, deselect the relevant nodes.
If you add a server, and the data source added is based on Windows Authentication, you may need to supply a set of credentials.
This often occurs when the authentication method for the data source is set to "End-user" Windows Authentication. Under this model, the user running the queries is used to connect (via Kerberos token) to the data source. However, when running a batch task (offline), the user's token is not available - as such a set of their credentials is required for connectivity by the Pyramid engine.
Note: This may not be required if the website authentication is via Basic or Forms.