This type of DataReader protocol imports data from an ODBC or OLE DB data source, including OLE DB for OLAP (ODBO).
To create an ODBC or OLE DB DataReader protocol, go to the SQL tab of the DataReader click the action icon and select New Protocol.
The following protocol configuration window opens
Type the protocol name in the Title field.
Select the entities and InfoCubes to feed: the list window located on the right displays all entities and InfoCubes of the current Board database. To select an entity or InfoCube, tick the check-box or use the mouse to drag and drop the desired item to the Main window. Note that entities have two fields, the code part and the description part. When you select an InfoCube, make sure to also select all its dimensions. For example, if you select the InfoCube Sales Amount, dimensioned by Customer-Product-Month, you must also include the following fields: Month, Product code and Customer code.
Select the data source connection from the Connection drop-down list. Note that this list box shows the OLE DB and ODBC data source connections already used at least once therefore the first time you configure a protocol this list box will initially be empty. For creating new connections, refer to Defining OLEDB connections.
Upon selecting the connection from the list-box, Board will connect to the data source (generally a relational database system) and list the tables and views of the database.
Select the tables and views you wish to imports data from by ticking the check-box, the selected tables are added to the main area.
Then desired field, then, keeping the mouse button pressed, drag and drop the cursor in the cell next to the entity or InfoCube you want to feed, as illustrated.
Drag and drop the table fields in the cell next to the entity or InfoCube you want to feed, as illustrated.
Note:
You may double click on the table title bar to view the fields data types and a sample record. Refer to ODBC DataReader options for details on how to configure the automatic data type conversion of the table fields to Board's internal data types.
Optionally, you can then edit a field and type an expression. You may, for example, wish to divide or multiply a field. Double-click on the field you want to edit, then, using the commands and syntax supported by the ODBC or OLE DB driver used, and type in the expression.
Note:
If you need to define complex transformation formulas you may use the ETL module.
If you have selected more than one table set the join conditions by dragging and dropping the fields to be joined. The Join window shows the defined conditions. The join statement generated is an equal join (natural join). If you need to define a different type of join, for example an outer join or a left join, you may edit the SQL statement (refer to Editing and Testing an SQL statement for details).
Note that by changing the Join option, you can alter the syntax used for writing the Join statement.
To define a Where condition click the Where tab and type the desired condition. Note that you may drag&drop a field from the selected table onto the text area to avoid retyping.
For each field (except standard Board Time entities), set the Append/Replace option as desired. Click on the yellow cell to set the option as desired. Refer to the paragraph Append and Replace options for details.
Press OK to save the protocol.
Defining a DataReader protocol generates a SQL statement. When the protocol is launched, Board sends the SQL statement to the ODBC or OLE DB provider and waits for the results. Note that the SQL statement is entirely executed by the ODBC or OLE DB provider therefore the commands and syntax which can be used in the SQL statement entirely depend on the ODBC or OLE DB provider used.
It is recommended to test the protocol SQL statement using the Browse function: click the Browse button as shown is the illustration. The query will be executed on the first 200 rows and the resulting data is displayed in spreadsheet.
Note that Board is not meant to be a sophisticated SQL query generator, if complex queries are needed it is recommended to use an ad-hoc query tool or that to create a view in the relational database which performs the desired query, then use this view in the Board protocol.
Defining an ODBC Data Reader protocol generates an SQL statement. When the protocol is launched, Board sends the SQL statement to the OLE DB provider and waits for the results.. Note that the SQL statement is run on the OLE DB provider therefore the commands and syntax which are supported depend on the OLE DB provider you connect to.
It is recommended you test the SQL statement using the Browse function: from the protocol definition window, click the Edit button, then click the Browse button. The query is executed on the first 200 rows and the result data is displayed in spreadsheet format.
Note:
The maximum length of an SQL statement is 3000 characters.