Editing datasources
Opening the datasource panel
Before you can create new or edit existing datasources, you must open the datasource panel in the editor. Click onto the "datasource" tab to open the panel.
The datasource panel is divided into two main areas: The datasource tree on the left side and the ERM panel on the right side. Both elements contain a small toolbar on their right border.
Nearly all elements in the tree and in the ERM diagram can be selected by clicking onto them. After an element was selected, all of its properties will be displayed in the Property editor.
Some element (e.g. links and table-definitions) also contain some sub-elements. They will be displayed in a small table below the ERM diagram. In the previous screenshot, the tables shows the column-pairs for a selected link.
The Datasource tree
The datasource tree shows all defined datasources of the configuration. When opening a datasource in the tree, you can also see all additionally defined elements for a database:
- Manually defined tables
- Aliases
- Links
- Expressions
This elements are just simple grouping nodes and have no properties. Than cannot be edited or deleted at all. All elements, their function, and how to edit them, are described later in this chapter.
The toolbar
The tree also contains a small toolbar which is primary used to create new datasources and to delete elements:
This button adds a new SQL datasource to your configuration. Before the datasource is added to the tree, a datasource wizard will open and ask you for the most important connection parameter.
The button "Collapse all" collapses the whole tree and all of its elements.
Use this button to delete any datasource or element from the datasource tree (except the grouping nodes). No element will be deleted unless you confirm the following dialog.
Datasource elements
In the tree, each datasource contains a number of nested elements. The following section describes the additional elements for databases:
Manually defined tables (and columns)
Usually instantOLAP will gather all table names and their columns after you created a datasource connection. The tables and their columns are visible in the database explorer left to the configuration editor.
Some databases or JDBC drivers are not able to list their columns. In this case you'll have to define the tables and columns manually, otherwise you cannot use them later in the configuration. The defined names must exactly match the real names.
However, this is very seldom and nearly every JDBC driver is able to list the names of the tables.
If you need to add a manual table definition, use the context menu on the item "Manually defined tables" nested under the database node. Use the menu item "Add table definition" here - then the system will ask for name of the table and create a new table node.
After you created the table you'll have to add its column names. Click onto the table node and use the table below the ERM diagram to add or delete column definitions to the table.
Aliases
Like in "pure" SQL, you must use aliases if you want to add a table more than once to a SQL statement. Especially when joining or filtering tables more than once, the usage of aliases is mandatory.
Consider the following example, where a fact table is linked to two different dimension table and both dimension table are linked to the same lookup table (which e.g. contains translations).
This configuration would return wrong and too few results, because the "Lookup table" is used and joined twice in the generated statement. The generator would create a statement like:
SELECT ... FROM FACT, DIM1, DIM2, LOOKUP WHERE FACT.ID1 = DIM1.ID AND FACT.ID2 = DIM2.ID AND DIM1.LID = LOOKUP.ID AND DIM2.LID = LOOKUP.ID
So, the lookup table will be limited at the same time by dimension 1 and dimension 2 and the result will be empty or miss of the most result rows.
To correct this, you must create an alias on the lookup table use this for the second link:
Now, the lookup table is doubles and each instance (the original lookup table and the alias) are both limited only once. The statement would look like:
SELECT ... FROM FACT, DIM1, DIM2, LOOKUP LOOKUP1, LOOKUP LOOKUP2 WHERE FACT.ID1 = DIM1.ID AND FACT.ID2 = DIM2.ID AND DIM1.LID = LOOKUP1.ID AND DIM2.LID = LOOKUP2.ID
In instantOLAP, aliases only define their name, their original table and a optional WHERE condition. You can create as many aliases for each table as you need.
Links
Links describe to relationship between tables or aliases and are used by the SQL generator to add the JOINs to the SQL statements.
Whenever you use more than one table (e.g. in a cube), the generator will add them to the generated statement and will then try to connect them using the configured links of the datasource. If the generator is not able to link every table of the statement, it will raise an error message and a query will be interrupted.
Each link has a couple of properties:
- Links have a logical name, which is only used to display and edit it in the configuration editor.
- Links have a source table, a target table and a direction. The default direction is "LEFT" (from source to target), but it can also be "RIGHT" or "BOTH". The direction is very important for the generator, because it can limit the usage of link and can avoid the combination of tables which should never used together in a single statement (see below).
- Links can be INNER- or OUTER-joins. The type of the join influences the result of the SQL statements, because an INNER join only produces result-rows for primary / foreign key pair that are contain in the source and target table.
- Links contain an arbitrary number of column pairs, always on column from the source and one from the target table.
- Links have a WHERE condition, which is automatically appended to the generated statement whenever a link is used.
When creating and editing links it is very important to know the rules instantOLAP uses when assembling SQL statements:
- instantOLAP collects all tables needed to load keys or to gather data from a cube.
- The generator tries to find at least one combination of links in which all of the used tables are joined with at least one other table. There must be one central table (usually the fact table or the basic dimension table), from which all other tables are reachable considering the direction of the links. Also, there must be no cluster of tables which is not linked to the rest of the tables.
- If the used tables cannot be linked, instantOLAP will add additional tables to the statement as "bridges" if there are links defined between the used tables and the new tables. It will use as few additional tables as possible.
Because the direction of the links is very important, it is suggested to
This link configuration would create wrong result, because the "dimension 3" is only reachable from the "fact table 1" through "dimension 2" and "fact table 2" (which would automatically added to the statement), and this permutates all rows from fact table 1 and 2 when using "fact table 1" with "dimension 3".
This happens because the link directions of all links is "BOTH", which allows the generator to use all links every time, even if this causes the include of other fact tables.
This is a better and error-free link configuration: All links are directed from the fact tables away and all needed dimension tables are linked to all fact tables directly. Now, there is no need and possibility for the SQL generator to add fact tables to the statement.
Table expressions
A table expression is a "virtual" column which can be added to a table and basically is a SQL expression - whenever the virtual column it is used in a statement, the generator will insert its SQL expression instead.
Each table-expression has some basic properties:
- Each expression belongs to a single table. The name of the table is stored in the "table" property of the expression.
- Every expression contains a name. It will appear under its bound table as normal column with this name.
- The SQL expression defines the SQL which will be inserted in the generated statement whenever the expression is used.
- It has a "type" which describes the return type of the SQL expression.
- It contains a optional WHERE condition, which is automatically appended to generated SQL statement whenever the table-expression is used.
You can use table expression to simplify you configuration, because complex SQL expression can be reused by defining them as table expression.
But they are also very useful to define a return type for SQL expressions, because whenever you use database dependent SQL code in your configuration, instantOLAP can have problems identifying the return type and this can cause problems. Then, create an table-expression, set the proper return type and use it in your dimensions or cubes.
Creating new datasources
Use the "Add datasource" button from the toolbar to add a new datasource definition to the configuration. This will open the datasource wizard:
On the first page you must provide the logical name of the datasource. This is only the name under which the datasource will be referenced inside the configuration. The name does not need to match the real database name.
After you've chosen the datasource name, use the "Next >>" button to open the next page of the wizard.
This page asks you for type of the database connection you want to use. There are two different ways to connect a database:
- Connecting a database using a JDBC URL: With this connection type, you'll have to provide all information like the used JDBC driver, the server URL, the username or password manually and all of this information are stored within the configuration.
- Using a predefined JNDI datasource from the application server: The administrator of the application server instantOLAP is installed on can provide predefined connections (datasources). In this case, you only have to tell instantOLAP the name of this connection (and sometimes the username and password, if they are not configured in the datasource).
The advantage of the predefined sources is that a configuration becomes less dependent on the database server, because its address is not hard-coded in the configuration. Especially when using development- and production-servers, the configuration can be copied from on to the other without changes.
In both cases, when using a predefined datasource or when establishing a manual configured connection, the JDBC driver which belongs to the database must be installed on the instantOLAP server.
If you chose to use a manual JDBC connection, the next page in the wizard will ask you for the basic connection properties:
- Driver: Each JDBC driver has its unique Driver-path, which must be entered here. You find the path of each driver in the drivers documentation. For the most common drivers (Oracle, DB/2, Informix, Sybase, MySql, MS-SQL etc) the driver paths a pre-configured and you can chose on of them by opening the drop down box.
- URL: This field expects a JDBC Connection-URL, which mainly contains the name of the used driver and the server URL. Usually the URL is "jdbc:<driver>:<url>", but the actual syntax is always dependent on the used JDBC driver. Like for the driver-paths, the URL syntax for the most common databases is also available as drop down list.
- User: The connection to the database will be established with this username.
- Password: The password which belongs to the user defined in the field before.
After you finished your entries, use the "Next >>" button to test the connection. If the server was able to connect the server without problems, the next page of the wizards will be displayed. If any error occurred, the message will be displayed and the wizard still shows this page.
If you want to use a predefined JNDI datasource and used the second option in the last wizard page, a new page asking you for the JNDI path of the datasource will open.
Here must enter the complete JNDI path. Usually, the path starts with "java:comp/env/..." followed by the name of the datasource. Your administrator can tell you the complete path under which is located the source.
This wizards tries to load the list of available datasources from the server. But most application server will not list a datasource unless it is used at least once, so you might have to input the path manually.
If both cases, when using a manual connection or a datasource, you can limit the connection to a single schema or catalog. The last page of the wizard lets you input or chose a schema- or catalog-name.
Not every database know the concept of schemas or catalogs. If the database knows them, instantOLAP will query the database for all available schemas or catalogs and display them in the drop down boxes. Sometimes, a database has schemas or catalogs, but the driver is not able to list them - in this case you'll have to input the schema name or catalog manually.
The last field allows to input a pattern for table names if you want to limit the listed tables for databases. You can list a number of comma separated table names here and you can also use wildcards as "*" and "?" in the names.
Finally you can use the "Finish" button to create the datasource and to add it to the configuration.
All settings you make in the wizard, like the connection properties, schema names, catalog names, table filters etc. can always be edited after the datasource was created by clicking onto the database element and changing its properties.
Adding a link to a datasource
There are two ways to add a link definition to a datasource: Adding it manually to the link node in the datasource-tree or creating a link with mouse in the ERM diagram (see below).
If you want to add a link manually, open the context menu of the "Links" node nested in the datasource and use the item "Add link...". After you clicked on the item, a dialog - the link wizard - will open.
The first page of the wizards ask you for the name of the new link. Each link has a unique and logical name which is only used for the visualization and navigation in the datasource tree.
After you press "Next >>" you'll able to define the source and target table and the direction of the link. The source and target table can be any table of the current database. You can select both by using the dropdown boxes, they contain all tables of the database.
The direction of the link is initially a "LEFT INNER JOIN", which means the link points from the left table to the right and is a inner join. You can change the link to "LEFT", "RIGHT" and "FULL" in combination with "INNER" or "OUTER JOIN":
- An inner join links tables in that way that the result of the generated SQL statements will only contain records for primary / foreign keys that are contained in both tables. The direction for an inner join only determines if the SQL generator can use it when trying to find a link path from one table to another.
- An outer join creates also results for missing records in the source- or target-table. All selected columns of the missing record are filled with NULL then. For outer joins, the direction is important: In an "LEFT OUTER JOIN", the target record may miss, for a "RIGHT OUTER JOIN" the source record may miss and for an "FULL OUTER JOIN" both source or target records may miss.
Note that outer joins can generate much more CPU on the database server. Also, some database system do not know outer joins or have a limited number of outer joins that can be used in one statement.
Editing links
After you created a link, you can edit it at any time. To edit a link, click onto the link in the database tree or in the ERM diagram.
Like for all other elements, the Property editor will show all properties of the link and you can edit them freely. But because the links also contain some sub-elements (the definition of the joined columns or expression from the source and target table), this elements will appear in a small table underneath the ERM diagram.
In this table, you can add new column pair to the link or edit the existing pairs. Each pair consists of three different columns:
- The source expression, usually a simple column of the source table, defines the foreign key of the source table. You can use any expression here, but it must use the source table only.
- The operator defines the operator both expressions are compared with. The common operator is "=", but you can also use other operators like "<", "<=", ">", ">=", "<>", "IN" or "LIKE".
- The target expression, usually a simple column of the target table, defines the primary key of the target table. You can use any expression here, but it must use the target table only.
Use the "Add link" or "Delete link" buttons to add or remove pairs from the table. You can also arrange the order of the links using the "Move up" and "Move down" buttons. When deleting or moving a link, you must select it first by clicking on the icon it the first column of the table.
Adding an alias to a datasource
The only way to create a new alias is to use the context menu from the "Alias" node of the datasource. After using the menu item "Add alias", the alias wizard will open:
This is a very wizard with only one page and three entries:
- Name: This is the name of the new alias. The alias will appear as a new table with this name in the database explorer after you created the alias.
- Table: This is the table the new alias is based on. This field is a dropdown box where you can choose the table from.
- Where: You can add an optional WHERE condition here, which always will be appended to the created SQL statement whenever the alias is used.
After you pressed "Finish", the alias will be created and added to the list of aliases.
Adding an expression to a datasource
In the datasource editor, three different context menus offer the creation of a new alias: The context menu of the "Alias" node under the datasource node, the menu of a table in the database explorer and the menu of a column in the database explorer.
Each item will immediately add an expression to the list of expressions, without any wizard. Depending on the menu you used, some of the expression properties are already pre-configured, the other are empty.
Click onto the alias under the "Alias" node to display the alias and to edit the missing properties.
Using the ERM editor
The ERM editor is a graphical representation of the tables of one or more databases. It is mainly used to visualize the tables, their columns and - most important - the relationship between the tables. Also, the diagram offers the most comfortable way to create links between the tables with drag&drop.
The diagram is initially empty and does not show any of the database tables. You must add the tables, or their aliases, to diagram manually. The added tables and their position in the diagram is stored together with the configuration file and restores automatically when loading the configuration at a later point of time.
The diagram will cluster all tables of the same database in a frame with the database-name below. It it possible (and necessary) to share the same ERM diagram for all databases.
Adding tables to the ERM
The only way to add a table or alias to the ERM diagram is to drag it out of the database explorer into the diagram panel. If the table did not exist in the diagram before, it will appear at the position you dropped it.
The tables are represented as boxes with the table title at the top and a maximum height. If the table has too many column, a black scrollbar appears at the right border. You can scroll the columns and make other columns visible with the mouse wheel while the mouse is located over the columns.
Instead of tables you also add aliases to the diagram. Therefore a table can exist multiple times in the diagram, every time with a different alias (or as original table). It is very important to use aliases when defining the relationships between tables, otherwise you may create circular relations and the SQL generator would generate invalid statements.
Moving tables in the ERM
To move a table within the diagram, you can grab its title and drag the table freely within the ERM. The links will rearrange themself while dragging the table.
Removing tables from the ERM
If you want to remove a table from the diagram, you must open its context menu (use the right mouse button anywhere on the table) and click onto the item "Remove from ERM".
Removing the table from diagram will neither delete the table from the database or the connected links from the datasource. It only removes the visualization, the table can be added to diagram to any time again.
Moving the viewport
To move the viewport and to show other parts of the diagram you can use the scrollbars at right and bottom border or you can click anywhere in the diagram and drag the visible viewport.
The toolbar
Use the "Refresh" button from the ERM toolbar to repaint all tables and all links.
The zoom buttons in the toolbar allow to increase or decrease the zoom if the diagram. If the zoom becomes to low, the text from the tables will disappear and only the frames of the tables and the links stay visible.
Instead of these buttons you can also use the mouse wheel to adjust the zoom while holding the mouse anywhere over the diagram except on a table.
Creating links in the ERM
Creating and managing links is the main purpose for the diagram. To create a link between two tables, locate the mouse on the columns of the source table which represents the foreign key (e.g. a column "ProductID" in a fact table).
Then hold the mouse button pressed and drag the mouse to the primary key of the target table (e.g. a column "ID" from a product table). While dragging, a line will indicate you are currently creating a link.
A target column will be highlighted while you drag the mouse over it and if it would be a valid target. It wouldn't be valid, if it is from the same table than the source column or if both tables are already linked.
If the target column is valid and you release the mouse button, the same link wizard describe before will appear, but this time prefilled with all necessary information to create the link: A generated name, the source- and target-table, the link direction and the columns.
Only if your link should contain more than one pair of columns you must click through the wizard pages to the last one and add other column pairs to your new link.
