New Mission from Gattaca
|
|
New Mission from Gattaca
|
Select \Mission\New… from the main menu
or the button in the Gattaca window to create a new mission.
First you should select: would you use an
already
created model template to create new mission?
If a predefined data model template will be used for the mission, find and select it.
Else, the program will require a data source to define a data model for a mission. Data source is necessary to be specified.
You will then
fill in the properties of the new mission. The created mission's link appears in
Gattaca and you will be able to raise the mission window and the
mission from
Gattaca
anytime.
A dialog box with empty properties of the new mission will be
displayed after selecting a
mission model template
or a data source.
First thing you should
select the mission type. Missions can be of two types:
detailed and
summary.
Detailed mission looks like the Windows Explorer. The left
pane displays the tree of selected
dimensions. The tree will have a hierarchy
according to the order you specified dimensions. When you click a tree item, the
program displays
details for this item on the right.
Summary mission is a query with or without aggregates. Only summary missions (plans) can be used as other missions' subqueries and in reports. But you can create submissions of any type from any type of parent missions. Practically, if you need to use an item of a detailed mission in a report, create a summary submission for this item.
Then specify a mission name. It will be formed from the filter condition if you do not.
Defining a mission starts from creating a data model. In the top-left tree the elements are grouped in several folders, to which you should add:
| Tables | The tables to retrieve data from |
| Missions | Subquery missions for retrieving data. Can be defined right here or copied from saved mission plans |
| Relations | Compulsory relations between all tables and missions from previous folders (direct or indirect) |
| Events | It is possible to create event handlers |
To add, edit, remove or change the order of (some) data model elements use the buttons above the tree of the model. A more detailed description of data models can be found in the part, devoted to data model templates. Do study it!
If you plan to frequently use a data model for several missions, it makes sense to create a data model template. When creating any mission, subquery or cube update, Dataodyssey Express suggests choosing a template to create a data model from. The template should not necessarily totally reflect the data model you need. Gather in it the most general (equal) data model elements for a group of missions. Straight when crating a mission you can change absolutely all the data model elements, copied from the template, and add new elements.
Simple queries from a particular table or several tables
If you need to perform a simple data retrieval from a certain table, you should essentially define a data model right here. When creating the mission, select "Empty" base and specify a data source. Select Summary as the mission type. Then add the table you want to the Tables folder of the model tree by clicking Add. Select the fields you need as dimensions and specify no aggregates. Form a retrieval condition (filter). After the mission executes, you will get a table fragment without row grouping. To group the rows, specify any aggregation (a dummy will also do).
|
Creating a mission from a data model template |
After a model has been created, it is required to:
create dimensions;
select details for a detailed mission;
create aggregates for a summary mission (if necessary);
create an initial filter condition (if necessary).
The list of dimensions should be created for a mission of any type.
Simple dimensions consist of certain table fields and subquery missions.
Expand the structure of a table in a data model and add the field you want to the dimension list. For example, you are going to create a detailed mission that groups data by client code and invoice month within. The dimension list will look like:
An additional dialog box will open when you select numeric, date and date-time fields as dimensions. You can specify a rounding rule for the values of numeric fields.
You can use partial values for date-time fields:
You can use as a dimension not only a reference to a field, but an expression that contains fields of data model's one or several tables and subquery missions. Practically, when you select a data-time field and specify to use its part, like year and month, the program creates from this field a compound dimension with the expression:
YEAR(A.DATE)*100+MONTH(A.DATE) AS DATE_YM_A
It is required to specify the compound dimension's column name using the AS keyword.
Compound expressions are created and edited by means of the expression builder, which can be opened by clicking the Edit button above the dimension list. After creating the expression the program requires specifying a column name for an AS clause.
Names and behaviour of properties or related objects in dimensions
The most common circumstance for creating a compound dimension is when you need to see not just a parameter code, like client code at sales analysis, but also the parameter's name and properties in a dimension itself. Thus, for a visual display of clients with the names you can specify as a dimension not just a client code from the CUSTOMERS table, but the expression for the names to be displayed:
Str(C.CODE)+' '+C.NAME AS CODE_NAME_C
The detailed mission tree will then get more visual then:
Syntax peculiarities of different data sources
Might be situation when your expression to show as node in tree is too complex. As result - slow details selection. In this case you can leave dimension expression simple (A.CODE as CODE_A) . Click "Node" button and create expression which will be used only to show nodes in tree. But! When program build the tree it will add additional column into SELECT clause with this expression and will include this column into GROUP BY clause. So, a speed of tree selection will decrease. But details will be selected faster because of simple key will be used. You should try both methods for each situation to determine which of them is faster.
It is obvious that different databases use different syntax for expressions and proprietary function sets. Dataodyssey Express will as far as possible try to convert the unified syntax with an allowance for database requirements. The EasySQL4Fox package takes charge of the process. But the area of responsibility for syntax conversion is strictly limited, and that is described here. In a great number of cases you should consider the syntax peculiarities for yourself. For example, you try to realize for ORACLE the described above sample of including the name interpretations into dimensions. The dimension expression will be:
Str(C.CODE)||' '||C.NAME AS CODE_NAME_C
because of the + operator is replaced with || for string concatenation in ORACLE.
If a mission retrieves data from a MySQL database, the expression sample should be:
Concat(Str(C.CODE),' ',C.NAME) AS CODE_NAME_C
, because it is also impossible to use the + operator for string concatenation in MySQL. Apply the Concat() function instead.
When you refer to fields with names which include not only alphanumeric symbols then remember that each database use different symbols as brackets to take complex identifiers:
 |
[COMPLEX IDENTIFIER] | in MS SQL Server and MS Access |
|
"COMPLEX IDENTIFIER" | in ODACLE. More. You have to take low-case identifiers in "Ident " too |
|
`COMPLEX IDENTIFIER` | in MySQL |
Case-sensitivity for identifiers and table names depends on type of database too. For MS SQL Server case sensitivity defined during installation as collation settings for whole server. But collation can be individual for any database or table. ORACLE is always case-sensitive. Table names case-sensitivity in MySQL depends on OS case-sensitivity, and identifiers are not case-sensitive. But all cubes, internal cursors and variables in Visual FoxPro are not case-sensitive always.
And! If the syntax you applied causes the expression builder's concern, but you are sure the syntax is correct, you should confirm it (the "Syntax is valid" option) and correctly specify the type and length of dimension.
You can manually code a subquery for a dimension. If the expression builder detects that the dimension expression begins with (SELECT, the syntax of the expression won't be checked, but you should keep it then. For example, you group mission by clients and want to display sales amount for every client, but there is no need to join the client list with accounts. Then you can write a subquery like this:
(SELECT Sum(i.QUANTITY*i.PRICE) From ["[`invoice_items.DSP_SQL]`]" i Join ["[`invoice_headers.DSP_SQL]`]" h On i.NUMBER=h.NUMBER Where h.CUSTOMER=c.CODE) as C_SUM
, where the C alias corresponds to the CUSTOMERS table in the mission (main query).
Take notice of the syntax of the reference to tables outside the data model! It generally looks like ["[`table_name.source_name]`]" .
Using the expression builder for dimensions
As it was already mentioned, an expression of dimensions can be edited by the expression builder only. Select the dimension you created and click Edit above the list. The expression, having no AS clause, will load into the expression builder.
You can type the expression manually, but it is better to build it by choosing elements from the list of fields of data model's tables and subquery missions and from four types of functions. Type manually only the +, -, *, /, etc. operators. You can choose from functions, which are valid for all database types or which can be converted into data source-specific functions by Dataodyssey Express. But when creating a mission to analyze data, which surely is stored in a database of the type you know, you can manually write the syntax, valid for the database.
The expression builder will in any case check the syntax after you click Ok. If the syntax contains errors or you haven't applied a universal one, an error message is displayed. If there are no errors, the following window with the name and type of dimension will have the type already defined and choice limited.
Specify no column name (or remove it) if you want the program to generate it automatically. But if you have trouble with column uniqueness in a query result or with a mismatch of names, dimensionality and column types in UNION merges or in one cube's several updates, you can specify correct information here.
If the expression builder does not accept your specific syntax, which you know is valid for the database, select the "Syntax is valid" checkbox before clicking the Ok button. The expression builder will all the same warn about an unknown syntax, but it will then request for confirmation to accept the syntax. If you confirm that the syntax is correct it will then be required to specify the syntax type in the window with the name, type and length of the dimension, because there is no opportunity to detect it.
The order of dimensions can always be changed. Click the button with up and down arrows near the dimension you need and drag it to another place on the list.
Some
data source
types in combination with certain access types (any based on HTTP) do not allow
to save large MEMO (TEXT) fields by one command. Such fields will be split into
a few parts and handed over to the data source in successive steps. Doing this
will require a table key. It usually occurs when the program needs to
transfer a subquery mission result to the main mission's data source from an
incompatible data source. If meantime the subquery selects MEMO (TEXT) fields,
specify key fields for it, clicking the
button above the list of dimensions. The circumstances are rare for missions. To
a greater extent specifying keys will be required for a
cube
update.
If you create a detailed mission, it is required to make the list of details. These are the fields, which appear on the right side of the explorer (of a detailed mission), when you you click a dimensions tree branch on the left.
You can create aggregates for a summary mission. Aggregates are used for the calculation of intermediate values for groups in a query result.
To calculate the intermediate values the aggregate functions Sum(), Count(), Avg(), Min(), Max() are used.
Choose any field
(except logical) in any
table
or submission of a model and click the
button to
add a new aggregate using this field. If the field is numeric, all the aggregate
functions will be available:
Only the Count, Min и Max aggregate functions can be used for non-numeric fields. To create a simple aggregate clicking Ok is enough.
You can write an expression from a few fields with aggregate functions or as an aggregate function's argument. The buttons to the left of the aggregate list allow to add fields with the multiplication, division, addition and subtraction operators of without an operator to the expression of an existing aggregate.
In the sample below you want to sum shipping by warehouses and clients. The properties of the new mission will be:
If you specified no aggregate at all, the mission will be equal to a simple query.
Using the expression builder for aggregates
The expression builder is applied for a more profound editing of an aggregate. It can be opened by clicking the Edit button above the aggregate list. The methods of using the expression builder are totally the same with those of the expression builder for dimensions. The exception is the availability of the additional list of elements – aggregate functions.
The order of aggregates can always be changed. Click the button with up and down arrows near the aggregate you need and drag it to another place.
***
When concluding the mission building you can create an initial filter condition for the data of the new mission.
Besides, you can use unions (UNION clause) for summary missions.
Any active
mission can be changed. Click the
button
in the mission management window to edit the mission properties.
The properties will be opened in the same window where you created the
new mission.
The mission data will be retrieved anew and shown on the same page of the
mission management window.