Navigation: Smarten-Administrator-Manual > Create Cube > Using Database profile >

Real-Time cube using graphical query generation

7.1.3Real-Time cube using graphical query generation

The cube creation process through graphical query generation is divided into the following easy steps:

Table Selection

Cube Column Selection

Where Criteria

Group By

Having Criteria

Order By

Cube Options

Dimension Column Selection

Reference: Concept Manual > Designing the Data Model > Smarten Cubes > Cube Generation Process > Extraction from Database

To create Real-Time cube using graphical query generation:

STEP 1

Procedure

1.In the Navigation Panel, click Cube Management.

2.In the Cube Management menu, click Create Cube.

The system displays the Create Cube page.

Create real-time Cube: Cube Options

3.In the Cube name field, enter a name.

4.In the Data source type field, select the Database radio button, and select an option from the Data source profile drop-down list.

5.In the Cube type filed, select the Real-Time radio button.

6.In the Query design option field, select the Graphical query generation radio button.

7.From the Financial year starts from drop-down list, select a month.

8.Click Next.

9.The system displays the Create cube - select tables page.

STEP 2

Table Selection

The table selection option is used to select table(s) for creating the cube.

Note:

Query Schema is not available in some databases, for example, MS - Access Database.

Procedure

1.Follow the Procedure of STEP 1.

2.From the Available tables list, you can drag and drop the tables to the Selected tables list or deselect the selected tables by moving the tables back from the Selected tables.

3.Click BACK to go back to Create Cube dialog box.

4.Click CANCEL to go back to Create Cube dialog box without saving any change.

5.Click Next.

The system displays the Create cube - cube column selection page.

Create real-time Cube: Graphical Query Generation: Select Table

STEP 3

Cube Column Selection

In the Cube Column Selection option, you can select columns that are used for creating cube, changing display value name of cube columns, and creating custom cube columns using formula.

Procedure

1.Follow the Procedure of STEP 2.

2.From the Select table drop-down list, select table name.

3.From the Available columns list, you can drag and drop the columns to the Selected columns list or deselect the selected columns by moving the columns back from the Selected columns.

4.From the table list, you can select columns from multiple tables by selecting table.

5.Select the checkbox Show table name to see table name in the Selected columns list.

6.You can move the columns up and down by drag and drop within Selected columns list to set the order of cube columns.

7.Click EDIT DISPLAY NAME.

The system displays the Create cube - edit display name dialog box.

Create Cube: Graphical Query Generation: Cube Column Selection

Create real-time Cube: Graphical Query Generation: Display Name Setting

8.In the Create cube - edit display name dialog box, edit the display name for the selected columns.

9.Click OK.

10.The system displays the Create cube - where criteria page.

STEP 4

Where Criteria

This option is used to specify Where criteria for the database query.

From the Create Cube - where Criteria page, you can set various conditions using the following:

Comparison of Values

Range Specification

Pattern Matching

Relation

Procedure

1.Follow the Procedure of STEP 3

2.From the topmost drop-down list, select a condition (Comparison, Range, Pattern, and Range).

The system displays the fields according to the selected condition.

3.Select the values for the displayed fields.

4.Click ADD.

5.Under the Extraction condition section, group two or more conditions by enclosing them together using the Enclose Parenthesis icon.

6.Select the Remove parenthesis icon to remove parenthesis.

7.Click the Delete icon to delete a condition.

The system displays a confirmation message to delete the selected condition.

8.Click OK to delete the profile or click Cancel.

9.Click Back to go back to Create Cube - cube column selection page.

10.Click OK.

The system displays the Create cube - dimension/measure selection page.

Create real-time Cube: Graphical Query Generation: WHERE Criteria

Create real-time Cube: Graphical Query Generation: WHERE Criteria: Comparison

Create real-time Cube: Graphical Query Generation: WHERE Criteria: Range specification

Create real-time Cube: Graphical Query Generation: WHERE Criteria: Pattern matching

Create real-time Cube: Graphical Query Generation: WHERE Criteria: Relation

STEP 5

Dimensions/ measures selection

This option is used to bifurcate the selected columns from the created cube in dimensions and measures.

Reference: Concept Manual > Designing the Data Model > Cube Meta Data > Dimension

Reference: Concept Manual > Designing the Data Model > Cube Meta Data > Measure

Procedure

1.Follow the Procedure of STEP 4.

2.From the Available cube columns list, you can drag and drop the columns to the Dimension columns list and Measure columns list. You can deselect the selected columns by moving the columns back from the Dimension columns and Measure columns.

3.Click on SELECT DATA OPERATIONS to select pre-defined data operations for selected measures

Create real-time Cube: Graphical Query Generation: Dimension Column Selection

To Select Data operations:

Procedure

4.Click on the SELECT DATA OPERATIONS button below the Measures columns section. The system displays the Data operations dialog box.

Select data operations

5.Click the checkboxes to select pre-defined data operations against each measure.

6.Click OK to confirm or click Cancel.

Create real-time Cube: Graphical Query Generation: fetching meta data from server

Create real-time Cube: Graphical Query Generation: Cube Created