To create a relational database connection:
Select File, then New, then Database Connection, then Relational.
Click
, and select Database Connection, then Relational.The Process bar displays the steps for creating relational database connections: Connection, Select Fact Table, and Define Cube. To successfully create a JDBC RDBMS connection, you must know the kind of relational data source you are accessing, the parameters needed to connect to it, and have a user name and password that supports connectivity.
Replace Database Connection String variables with values.
Depending on the driver type, there are variables for host name, database name, port, and DSN. You must replace the brackets and the variable.
If the connection fails, repeat steps 6 through 10 until you can establish successful relational database connectivity. Make sure you remove brackets from string variables.
After connecting, close the Test Connection message and click Next.
Step 2: Select Fact Table you must select a relational fact table from a list. Schema filters and table type filters are available to narrow long lists of tables.
Click Retrieve Table List to query the relational database for tables meeting filter requirements, and display the result set.
To select the fact table, you must understand the relational database. If necessary, ask the relational database administrator to identify the fact table. The fact table must contain at least one column of numeric data that can be used as a measures dimension.
Select a table to be used as the fact table from the table result set, and click Next.
Step 3 Relational Cube Editor diagrams the relational cube as a node tree in the same manner that the Dimension Browser presents OLAP cubes. Click to select a node. Double-click to expand and collapse nodes. Right-click to edit dimensions and measures.
Right-click Measures and select Edit Measures.
The Measure Editor dialog box enables you to define relational columns as measures by moving them from the Available Columns list to the Measures Members list. You can also rename the measures dimension, specify the default dimension, add measures to the cube, and specify aggregation methods for measures dimensions.
Select a column with a numeric data type from Available Columns, and click the right arrow (>) to move the column to Measure Members.
Note that the selected measure is designated as the default measure dimensions.
Optional: When Measure Members contains multiple measures, you can specify the measure used as the default measure by clicking an option in Default.
Optional: To change the measure name, double-click Member Name, and type a name. Member names must be unique to each other and the name of the measures dimension for SQL parsing to be successful.
Optional: To indicate another member aggregation method, click Aggregation Type and select from these aggregation methods. When no aggregation type is specified Sum is used by default:
When all fact table columns used as measures are specified, click OK.
The Relational Cube Editor is displayed. Note that the Measures node can be expanded to display the measures dimension.
Enter a dimension name to define, and click OK.
The Generation Editor dialog box is displayed. It enables you to create a generation for the dimension, to name the generation, to indicate the ID and alias tables used for return values, and to relate the dimension back to the fact table.
Enter a dimension generation name in Generation Name.
Note that Select Member ID and Description Columns displays the fact table by default.
To narrow the list of tables, select a filter from Table Type.
Indicate the Descriptions aliases column in the bottom Column list.
By identifying common table columns, you relate the defined dimension generation to the fact table. Typically, several sets of common columns are used to identify a circuit to the fact table.
Ask the relational database administrator for a description of relational tables and their columns to discern common table columns.
Start by identifying tables sharing columns with the dimension generation table. Find tables sharing columns with the fact table. Determine if tables that share columns with the dimension generation also share columns with the fact table. If they do, your relationship is mapped. If not, you must continue comparing columns until you can map a bridge between the dimension generation and the fact table.
Note that Define Fact Table Mappings displays the dimension and the fact table by default. Unless they share a common column, you must select a Table Type filter, a table, and a column that matches the dimension generation. If they do share a common column, you need only indicate that column in the Column drop-down list.
When a table and column are selected, a line is added to Define Fact Table Mappings below until the dimension generation and fact table relationship is mapped.
Keep these considerations in mind as you make your selections.
In Define Fact Table Mappings, select a table from Table that has a column that matchesColumn.
Repeat steps 25 and 26 until a relationship between the dimension generation and the fact table is mapped, and click OK.
The Relational Cube Editor is the current dialog box again. Note that the Dimension, generation and Measures nodes can be expanded, by clicking the plus sign (+), to display the relational cube structure.
Optional: To add generations to one dimension, right-click the dimension name and select Add Generation. The Generation Editor is displayed again. Repeat steps 20-27.
Optional: To reorder the generations in the dimension hierarchy, right-click a generation name and select Move Up or Move Down.
The generation node moves in the specified direction in the dimension hierarchy.
Optional: To add dimensions to one relational cube, right-click Dimensions and select Add Dimension. Repeat steps 18-29.
When all measures, dimensions, and generations are defined and ordered, click Finish.
The Save As dialog box is displayed. It prompts you to navigate to the repository location where the database connection is saved.
Navigate to the folder into which you want to save your database connection file:
Click Up to display the contents of the parent folder in the selection frame.
Click Home to jump to and display the contents of the current Home folder (set in preferences) in the selection frame.
In Location, type the path to the repository folder whose contents you want displayed, and press Enter.
In Location, click the drop-down arrow and select another location from the repository.
When you navigate to another folder, you can click Previous to return to the last folder.
Similarly, you can click Next to display the next folder in the location series.
As you navigate, the selection frame lists the files and folders indicated by Files of Type.
When you reach the location where you want to save the file, enter a name for the database connection in Filename, and click OK.
Your relational database connection file is saved to the specified repository location. You can use it to create documents, just as you would OLAP database connection.