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

  1. Drag the relevant data source onto the canvas.
  2. Select the data source node on the canvas and, at the top of the Properties panel (blue highlight below), either:
    • Select the Server and Database you want to query:
      1. Select the Server from the drop-down, or click Add Server to add a server. Note: You can click Refresh to refresh the server selection.
      2. Once the Server has been selected, Choose the Database.
    • Select Direct Query Data Source.

Table Selection

  • 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 by selecting the tables you want to add and choosing one of the following options (orange highlight below):
    • Add Table: a select node will be added for each selected table. Each table may then be previewed, and columns can be removed from given tables.
    • Add as Multi-Select: 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, select a node to open its Preview panel.

Column Selection

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.

Data Source Authentication

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.