Generating a Multi-Dimensional Model

Purpose

This tutorial shows you how to generate a multi-dimensional model with Oracle SQL Developer Data Modeler.

Time to Complete

Approximately 15 minutes

Introduction

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.

Prerequisites

Before starting this tutorial, you should:

• Install Oracle SQL Developer Data Modeler from OTN here.
• Install the Oracle Database 10g and later.
• Download and unzip the datamodel3genmulti.zip file that contains all the files you need to perform this tutorial.

Note: This tutorial was created in a Linux environment. If you are using a Windows environment, the screenshots may be slightly different.

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. 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 directory where you unzipped the files from the Prerequisites and click Open.

   

5. Click OK.

   

6. Select Oracle Database 12c 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.  Navigate to view menu and select Navigator. In the Navigator window, you can move the red box around to adjust the view of your model.

   

9. To view the entire diagram, click the Fit Screen icon.

   

10. 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 she_cre_all and select Properties.

   

2. Change the Name to SH_Schema and click OK.

   

3. Expand SH_Schema > Physical Models > Oracle Database 12c > 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. Right click on the model, select Autoroute. 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. 

3. 

4. 

5. 

In Oracle SQL Developer Data Modeler 4.0, a custom report template that shows dimensions with details for hierarchies and levels is available. This custom report is integrated in the Search functionality.
Perform the following steps:

1. Click on the Search icon on the toolbar and enter customer. Since the SH_Multidimensional model is active, the search will be for this model.

   

2. In the Results window, select the following:

   • Model: SH_Multidimensional
   • Object: Dimensions SH_Multidimensional

   

   Click Report.

3. In the Reports Dialog, enter Customer Dimension for the title.

   

4. Click the Output Format drop down and select HTML. The various output formats available are HTML, PDF, XLS and XLSX.
   

   

5. In Templates section, click the drop down and select Dimensions_2_levels.

   

6. Click Manage.

   

7. You can view the Report Layout and properties. The template can be edited by clicking on the edit icon.

   

8. Click on Notes column and click on the up arrow to change the column order.

   

9. Click Save and then Close.

   

10. Select Dimensions template(Dimensions_2_levels) in the drop down again and click Generate Report to generate the report.

    

11. Click OK to open the report.

    

12. The report is displayed.

    

In this tutorial, you learned how to:

• Import DDL for the SH Schema to create a relational diagram
• Re-engineer to the logical diagram
• Create and generate a multidimensional model from existing dimensions and levels
• Use the Dimensions Custom Report Template

Resources

• To learn more about Oracle SQL Developer Data Modeler, refer to additional OBEs in the Oracle Learning Library .

Credits

• Lead Curriculum Developer: Swarnapriya Shridhar
• Other Contributors: Ashley Chen