Print PreviewThis tutorial contains the following sections:
Click icon to show all screenshots
Note: Alternatively, you can click an individual icon (or image) associated with each step to view (or hide) the screenshot associated with that step.
This tutorial shows you how to generate a multi-dimensional model with Oracle SQL Developer Data Modeler.
Approximately 15 minutes
Oracle SQL Developer Data Modeler offers a full spectrum of data and database modeling tools and utilities, including Entity Relationship modeling, Relational (Schema), Data Types or Object Types modeling, and Multidimensional modeling and DDL generation. It includes importing from and exporting to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of Design Rules.
Oracle SQL Developer Data Modeler will be released as an extension to SQL Developer and as a standalone product, for those developers who only want to work with visual data modeling.
In this tutorial, you import the DDL for the SH schema, examine the relational and physical model, reverse engineer to create a logical model, create a new multidimensional model, engineer from the physical model to create the multidimensional diagram and examine the results.
In this section, you import the DDL from the SH schema to create a relational model. Perform the following steps:
|
1 . |
Open Oracle SQL Developer Data Modeler from the icon on your desktop. If you performed the previous tutorial, and did not exit Oracle SQL Developer Data Modeling, select File > Close to close the model you previously worked on.
|
|---|---|
|
2 . |
Select File > Import > DDL File.
|
|
3 . |
You can add multiple DDL files to be imported at the same time. Click the '+' icon to add a DDL file.
|
|
4 . |
Select sh_cre_all.sql from the sqldev directory and click Open.
|
|
5 . |
Click OK.
|
| 6 . |
Select Oracle Database 11g and click OK.
|
| 7 . |
The Compare Model window appears. You can view the changes that will occur when the DDL file is imported. Expand Tables under sh_cre_all. You see that the list of tables that will be created. Click Merge.
|
| 8 . |
The relational diagram is displayed. To view the entire diagram, click the Fit Screen icon.
|
|
9 . |
You can now see more of the diagram. In the next section, you review the relational and physical model objects in the navigator.
|
In this section, you review the relational and physical model in the navigator. Perform the following steps:
|
1 . |
In the left navigator, expand Relational Model. Right-click Relational_1 and select Properties.
|
|---|---|
|
2 . |
Change the Name to SH_Schema and click OK.
|
|
3 . |
Expand SH_Schema > Physical Models > Oracle Database 11g > Dimensions > times_dim > Levels. Notice that there are 5 Dimensions in this model. Each dimension contains various levels and hierarchies. Double-click the day level for the times dimension.
|
| 4 . |
Click the Level Attributes tab to view the columns defined for this level.
|
| 5 . |
The columns are displayed. Click OK.
|
| 6 . |
Expand Hierarchies. Double-click cal_rollup.
|
|
7 . |
Review the list of levels included in this hierarchy. Click OK. In the next section, you engineer the relational model to a logical model so that you can then create the multidimensional model.
|
In this section, you engineer the relational model to a logical model and then generate the multidimensional model. Perform the following steps:
|
1 . |
Click the Engineer to Logical Model icon.
|
|---|---|
|
2 . |
Expand Tables to see the objects that will be added to the Logical Model. Click Engineer.
|
|
3 . |
The Logical Model is displayed.
|
|
4 . |
Now you can create a multidimensional model. In the left navigator, right-click Multidimensional Models and select New Multidimensional Model.
|
|
5 . |
Once your multidimensional model is created, you can change the name. Expand Multidimensional Models and right-click Multidimensional_1, then select Properties.
|
|
6 . |
Change the Name from Multidimensional_1 to SH_Multidimensional and click OK.
|
|
7 . |
Now you can create the multidimensional model. Right-click SH_Multidimensional and select Engineer From Oracle Model.
|
|
8 . |
Click OK.
|
|
9 . |
The multidimensional model was created successfully. In the next section, you review the times_dim object in the multidimensional model.
|
In this section, you review the times_dim object in the multidimensional model. Perform the following steps:
|
1 . |
Double-click the times_dim object in the diagram.
|
|---|---|
|
2 . |
In the left navigator, click Levels.
|
|
3 . |
The levels are displayed. You can drill down into the levels. Double-click the day level.
|
|
4 . |
In the left navigator, click Descriptive Attributes.
|
|
5 . |
Review the attributes shown. Click OK twice to return to the diagram.
|
In this tutorial, you have learned how to:
|
About
Oracle |Oracle and Sun |  | Careers | Contact
Us | Site Maps | Legal
Notices | Terms of Use | Your Privacy Rights
|
