Dataodyssey Express and Common Technologies
|
|
Dataodyssey Express and Common Technologies
|
Dataodyssey Express was not implied to refer a user to the known technologies like SQL and OLAP. On the contrary, it was desired the approach to data mining problems be quite novel, abstracting from the methods invented long before and bare terminology. Nevertheless on a fundamental level Dataodyssey Express brought nothing new in the data mining industry. And you can always see it has much to do with basic technologies.
SQL (Structured Query Language) Conventions
Actually the methods of Dataodyssey mission creation allow to form SQL query clauses.
SELECT clause is formed from the two lists of: dimensions and aggregates (for a summary mission). You can use subqueries.
FROM clause is made of a data model's table list, subqueries formed from saved mission plans and subqueries formed from missions directly defined in the data model. Subquery text is included into the main query only if the subquery is executed from a data source compatible with that of the main query. If the sources are incompatible, all the subquery data will first be retrieved on the client side and then sent to the main query source as a temporary table. The main query will include only the temporary table's link.
All the tables and queries will be joined by a JOIN clause (LEFT or RIGHT OUTER possible). ON expressions will be formed from the relations between tables and subqueries.
If the means of data model definition are insufficient to create a FROM clause, the clause can be written manually in the data model properties. A reference to the tables that are not included in the data model is set using the syntax: <#<table.DataSourceName>#>
If there is no opportunity to specify all relations between the tables in FROM and JOIN clauses, it is possible to manually compose join conditions in addition to the WHERE clause in the data model properties. For example, when you need to create an Exists query instead of joining. It is possible to make reference to the tables not included in the data model, using the syntax <#<table.DataSourceName>#>.
Filter condition for the WHERE clause will be composed from the filter condition of the mission. It can also be added manually in the data model properties.
The tools for filter condition creation allow to use the Exists(Select... construction.
The expression builder for editing dimensions and aggregates skips the analysis of an expression, if it begins with (SELECT ...). It is possible to make reference to the tables not included in the data model, using the syntax <#<table.DataSourceName>#>.
As it was said before, you can manually write subqueries for a data model's mission plans and subquery missions in the data model properties, if the data is retrieved from data sources compatible with the source of the main query. It is possible to use the syntax <#<table.DataSourceName>#> to make reference to the tables not included in the data model.
The standard tool for specifying filter condition for a mission allows to make subqueries with the Exists(SELECT ... expression. Subqueries can be written manually in data model properties. You can use the syntax <#<table.DataSourceName>#> to make reference to the tables not included in the data model.
For summary missions it is possible to create several sections, combined using UNION.
One of the principal goals, pursued while developing Dataodyssey Express, was the opportunity to work with data from heterogeneous data sources. For this Dataodyssey Express uses the level of abstraction from data types. This level was created on the basis of the EasySQL4Fox technology. It allows to convert the generalized SQL syntax into a database-specific one ( http://easysql4fox.vallmind.com/EasySQL4Fox.html ).
For OLAP solutions even the terminology was not altered. We as well suggest using cubes and tried to ensure the OLAP core elements implementation. Though we define cubes as redundant tables, you can arrange star schemes, combining the cubes in new missions taking no data from other tables. From technical point of view Dataodyssey Express does not attempt to press the redundancy on system level and the cubes are just the same tables.
After the execution of missions you can
view the codes of SQL queries.
In the mission service window you can see the
button with the
More actions prompt. Clicking it will open the menu with the "Show last
queries..." item and also the "Show details of last queries" item for a
detailed
mission. Selecting these menu items will open the window with the text of the
main query and the texts of subqueries, which are executed separately to
retrieve the data from
data sources
incompatible with the source of the main query.
In the process of Dataodyssey Express installation the DataOdyssey.DCOM class will be registered. This class functions as a remote server using the RPC and RPC+IIS access technologies. Besides, objects of this class can be created and used in other applications.
Batch updating of cubes and replications from other applications
The code sequence for batch updating of cubes and replications will be the following:
For Visual Basic:
Set Dto = CreateObject("DataOdyssey.DCom")
Dto.User = "sa"
Dto.Password = "..."
Set Result = Dto.Update_Cubes()
For Visual FoxPro:
m.Dto = CreateObject('DataOdyssey.DCom')
Dto.User = 'sa'
Dto.Password = '...'
m.Result = Dto.Update_Cubes()
As well as in the command line case you can specify a destination, group or criterion for a replication. For example: = Dto.Update_Cubes(1,1,0).
The Result variable will be True in case of success. The result of updating or errors can be viewed in the log (the dtoe_logrec table from the DATAMAP source).
Dataodyssey Express inside (in different from Dataodyssey (.NET technologies)) is Visual FoxPro 9.0 . According with this fact there are a lot of limitations which can theoretically bring problems:
Numeric values can't overflow 20 digits (38 in MS SQL Server and ORACLE, ...);
Index names length can't be longer than 10 symbols;
Size of selected cursor can't be larger than 2Gb.