Every data exploration mission or cube update is based on a data model. When creating a mission or a cube update you can use a predefined model template or define it from scratch in the properties of the mission or the cube update.

Data model templates

    To create or change a data model template select \Space\Models… on the system menu. Once created, a model template removes the necessity of defining it anew for typical missions and dramatically diminishes the actual time and error probability.

Data models in missions and cube updates

    When creating a new mission or a cube update you will be offered to choose one of already created data model templates. You can use a template that suits you most, and change its copy directly in the mission or the cube update. If there is not an apt template to use, cancel choosing and you will be offered to specify a data source for a mission, it is necessary. The technologies of creating and changing data models in templates are similar.

    A data model consists of the following possible components:

• List of tables to retrieve data from;

• List of mission plans to retrieve data from;

• List of subquery missions to retrieve data from (can be defined only in a mission or a cube update, unavailable in templates);

• List of relations between tables, mission templates and subqueries;

• List of events, for which handlers can be written.

    To create a new model template click New model... Then click Model properties... to edit the model properties. In a mission or a cube update select the first tree branch and click Edit above the tree. In any case a model has the following properties to be filled:

	Name	A unique model name.
	Data source	Click Data source... to select a data source in the dialog window. The data source should be defined beforehand.
	Additional JOIN clause	Interactive tools allow to define simple relations and query filters. If you are creating a more complex model, but cannot create a filter or join condition using standard tools, you can type the conditions here. You should refer to the tables of the model using only their aliases (pseudonyms), which are generated when adding tables to the model. And you should strictly adhere to the Structured Query Language syntax. See another article of this manual about this property.
	Additional WHERE clause	Like JOIN clause mentioned above.

Select from...

    Then the end of the Select from... statement can be composed by using lists of tables, mission plans and subquery missions. It corresponds to the FROM clause in SQL.

FROM clause tables

    Select a directory with tables and click Add table...  The program will display the list of all tables of the model's data source. For example, the data source DSP contains the tables: Customers; Goods; Invoice headers; Invoice items. Add all these tables to the model one by one.

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

INVOICE_HEADERS	A
INVOICE_ITEMS	B
CUSTOMERS	C
GOODS	D

Retrieving from mission plans

    Besides retrieving from tables, Dataodyssey Express allows to retrieve data from saved mission plans. In terms of SQL conventions references to mission plans are FROM statement predefined subqueries. Select "Missions in plans" from the data model structure and click "Add mission plan..." in the template window or "Add" in the mission designer. There will appear a standard mission plan request form. If you have few mission plans, click Select. When the number of mission plans is large it is recommended that you build a filter in the same window. The resultant list will comprise summary missions only.

    Select the mission plan you need from the list and click "Ok". After the compatibility verification a reference to the mission plan will be added to the data model with a unique pseudonym (alias). In fact a data retrieval model of one data source can be supplemented with a data retrieval from other data sources (incompatible), but mind the specifics that affect mission performance. It is really possible to retrieve data, for example, from a Microsoft SQL Server, Oracle or Microsoft Access database in one mission (query).

    If you define a data model template, only a saved mission plan reference will be added to it. But! There is one important thing! When you create a data model for exactly a mission, the mission plan will be copied whole into the data model. Using then the data model template to create a mission will make all the mission plans that have references from the template be initially copied into the new mission's data model as unique subquery missions, and you can edit these subquery missions. If you add a reference to the saved mission plan directly into the plan of another mission, the program will immediately copy the data model from this plan into the unique model of another subquery mission.

Retrieving from a subquery mission

    There is no necessity (as in the previous version) to create and save a mission plan to make an analog of subquery. If the subquery structure is nonrecurring, it can be defined directly in the data model when creating a mission or a cube update. This method is unavailable for a data model template. That is why if you want such a model to be replicated, you will have to define the whole mission (as simple as possible) and save its plan. Then you can create any number of missions based on the plan and edit their structures.

    Select "Missions" from the data model structure and click "Add". The whole new mission defining dialog will appear. The mission can be summary only and it should have one or a few dimensions listed.

    A subquery mission can retrieve data from data sources incompatible with that of the main mission, but when creating such a subquery you will be warned about a longer query run time. Compatibility issues are described below. But it is really possible to retrieve data, for example, from a Microsoft SQL Server, Oracle or Microsoft Access database in one mission (query).

Relations

    Every table, mission plan or subquery mission should have relations with all tables and missions of a data model directly or by the other tables of this data model! If you do not link all tables and missions of one data model directly or indirectly, you will waste time, losing drive space and nothing else. You can create numerous data models for the same tables of the same data source. And you will be able to combine in reports the results of such missions and the missions based on data from other data sources.

    Select a directory with the data model relations and click "Add relation...". The dialog box will look like:

    This tool is for creating simple relations. The upper list contains all the tables and missions of the data model. They all should be related in turn.

    The relations are divided by the join type: inner join, left outer join, right outer join. In the first case the mission result will contain the table rows that have met the join condition on both sides. It is the second case when you want, for example, to interpret the property names (clients or goods) in query result, but excluding no operational data (about sales) if there is not such an interpretation found on the list of properties. Then the left or right outer join can be applied depending on the side you define tables in the condition.

    Relations can also be simple and compound.

    If a relation between two tables can be defined by a join condition, it is a simple relation. Even if there are field expressions in the condition. It is not required for a simple relation to write additional statements or see to syntax.

    If there is a need to combine some conditions by operators (AND, OR, NOT) and brackets, you will have to ensure the syntax of the compound relation is correct. List all the relation's conditions close one by one for clearness and specify the brackets and logical operators (AND, OR, NOT) you want in the fields on the left or right of the join in order to make of individual conditions one syntactically correct compound condition.

    To create an individual condition select tables and their fields in the expressions of both relation parts. You can specify field + field in an expression. In the sample the invoice headers have the NUMBER key field and the invoice items are related with the headers by this field.

    In the sample the following relations should be defined:

    You can manually define more complex relations using additional JOIN and WHERE clauses in the model properties.

Data source compatibility

    It is obvious that the different data source types that Dataodyssey Express can deal with are incompatible. This means that none of the databases allows to retrieve data from several such data sources in one query. It is obvious that neither Microsoft SQL Server nor ORACLE, etc. allows to join in one query the data from its native databases and those like Microsoft Access, Visual FoxPro DBF and any other. Moreover, Microsoft SQL Server, for example, will not be able to retrieve data from different servers on a distributed network by one query (but not in the case of setting special relations between the servers using a special protocol, that being not always possible).

    If you defined several heterogeneous data sources in Dataodyssey Express, they will be incompatible with each other. It is easier to describe situations when data sources are compatible:

• Microsoft SQL Server, ORACLE and MySQL compatible data sources are the data sources that have references to the same database or to different databases on the same server (the same host). Names of tables from different databases can coincide;

• Microsoft Access, Visual FoxPro DBF and DBC compatible data sources are any that match by type, despite file location. Table names can coincide.

• The compatibility rules for the data sources that have access configured via Dataodyssey Express are governed by the server according to the two principles, mentioned above. Naturally, the Dataodyssey Express server is the same.

    But! Incompatibility does not imply that Dataodyssey Express is unable to retrieve data from such data sources. Dataodyssey Express can combine in one mission the data from any data source absolutely. Incompatibility means only that:

• mission (data retrieval) performance will dramatically fall;

• you should take care of mission speed optimization for yourself.

    You can combine data from different data sources in one mission because for a mission, retrieving data from one data source you can define subquery missions, retrieving data from other data sources. When the mission's data source is incompatible with that of the subquery missions, the following things happen:

• subquery missions are processed regardless of any relations with the parent mission and all data is preliminary retrieved on the client side;

• client side data retrieved by the subquery will be sent to the data source of the main mission;

• you should analyze subquery filter condition because in most cases you get an opportunity to increase the subquery speed by hundreds of times using the superposition of filter properties.

    For example, if by a subquery you retrieve sales data, summarized and stored on a remote server incompatible with others and you specify only a short interval of goods codes, you should make this interval, defined for the main mission get into the filter condition of the subquery mission. Otherwise you will transmit the whole table from one server to another just to learn that you needed merely a few rows. !!!

    If the data sources of the main and subquery mission are compatible, Dataodyssey Express generates an SQL nested query and communicates it to the server that enables robust optimization tools and cuts down the retrieval speed by probably hundreds or thousands of times (though not in every case).

www.dataodyssey.com         Contents