Every mission of analyzing data is based on a mission model, which you are to construct before. Open the model list using \Space\Models… system menu item.

Every model consists of:

• a list of tables;

• can include some mission plans created before (subquerys technique);

• a list of relations;

Click “New model…” and fill in the following properties:

	Model name	Unique model name.
	Data source	Enter the name of an existing data source or press the “Data source…” button and select data source in the dialog box.
	Additional JOIN condition	Interactive means allow describing simple relations and query filters. If you are creating rather a complicated model and cannot create a join or filter condition, using standard means you can type them manually here. You must refer to the model tables according to the aliases only, the latter being generated, while adding tables to a model. And you should strictly stick to the syntax of SQL. See another article of this manual devoted to this property.
	Additional WHERE condition	Like JOIN condition mentioned above.

    Click “Save” and the new model with two subfolders for tables and relations will be added to the model list.

    Next select the folder with tables and click “Add a table...”. The program will display the list of all model tables of the data source. For example, the DSP data source contains the following tables: Customers; Goods; Invoice headers; Invoice items. Add all these tables to the model one by one.

    Also you can add mission plans saved before into model. Dataodyssey will use a technique of subquerys in this case. Only remote plans of summary missions saved into same data source can be added into model. Select the folder with mission plans in model and click button "Add mission plan...". A form of mission plans selection will appear. If there are not so many saved plans, just click the "Select" button. If there are many plans, you will have to form plan selection condition and then click "Select". But only summary mission plans in model's data source will be shown.

    The program will display the fields of every table and mission plan. It will also generate unique aliases for every table and mission plan of a model. In our example tables get the following aliases:

• CUSTOMERS                 A

• GOODS                         B

• INVOICE_HEADERS        C

• INVOICE_ITEMS            D

 
    Each table and mission plan in a model must be linked with the rest of the tables and mission plans of a model directly or via other tables or mission plans of the same model. If you don’t link all the tables and mission plans of one model directly or indirectly, you’ll waste time, losing drive space and nothing else! You can create numerous models for the same tables in the same data source. And you’ll be able to combine results of such missions and the missions for data from other sources in reports.

    Select the folder with model relations and click “Add a relation”. Dialog box will look like:

    This means is for creating simple relations only. Select tables and their fields of expressions of both relation parts. You can indicate field+field in an expression or describe more complex condition manually. In our example invoice headers have the NUMBER key field and invoice items are connected with the headers by this field. Also you can choose three types of the relation: INNER JOIN (each row of right table(mission plan) must match row of left table(mission plan) and each row of left table(mission plan) must match row of right table(mission plan)) (default) ; LEFT OUTER JOIN (all rows of left table might be selected into result independently on right table, but all rows of right table must match row in left table); RIGHT OUTER JOIN. For example, the LEFT OUTER JOIN link might be useful when a right table is a reference book (goods names) and the left table has got important data which must be selected independently on matching. But !!! We had got a problem using ORACLE. When we used LEFT OUTER JOIN relations in summary mission plan and after we tried using this plan in another model (subquery in FROM clause with OUTER JOIN) then we received an ORA-3113 and could not fix it !

    Here we must define the following relations:

• Invoice headers -> Invoice items      (C.NUMBER -> D.NUMBER)

• Invoice headers -> Customers         (C.CUSTOMER -> A.CODE)

• Invoice items -> goods                    (D.GOODS -> B.CODE)

    You can manually describe more complicated relations in additional JOIN and WHERE model properties.

www.dataodyssey.com         Table of contents