Use Data Modeler

Data Modeler enables you to model the data that is needed to produce reports.

Topics:

Open Data Modeler

Your administrator gives you access to Data Modeler.

  1. Sign in to Oracle Analytics Cloud.
  2. Click the Page menu on the Home page, and select Open Data Modeler.
  3. At the Models page, open an existing model or create a new model.

Create a Data Model

Create a new data model from scratch in Data Modeler.

  1. Open Data Modeler.
  2. Click Create model.
  3. Enter a name and description for your data model.
    The subject area associated with this model gets the same name.
  4. Connect the model to a Database.
    If the database you want isn't listed, ask your administrator to set up the connection for you.

Use the Left Pane in Data Modeler

Various data modeling menus are available from the left pane in Data Modeler.

  • Database — Lists source objects such database tables and views

  • Data Model — Lists data model objects such as fact tables, dimension tables, hierarchies, fact columns, and dimension columns

  • Variables — Lists variables for use in data security filters and in column expressions

  • Roles — Lists roles that you can use when defining object permissions and data security filters

Filter a list to find exactly what you want.

  1. In Data Modeler, in the left pane, open the Database, Data Model, Variables, or Roles menu.
  2. Click the Filter icon to the right of the selected menu.
  3. In the Filter area, enter a string value for filtering the display.
  4. Delete the text or click the Filter icon again to remove the filter.

Use the Right Pane in Data Modeler

The right pane in Data Modeler is a contextual pane that changes depending on what task you’re performing. After you have started modeling data, the default or home view shows the fact tables, dimension tables, and joins that you’ve defined so far.

  • In the fact tables and dimension tables area you can see the number of joins for each fact and dimension table, as well as the number of measures in each fact table.
  • Joins are listed below the fact and dimension tables. Click the up or down arrow in each column header to sort.
  • When you click an object to open its editor, the editor appears in the right pane. For example, clicking a dimension table name from the Data Model menu in the left pane opens the dimension table editor in the right pane.
  • Open the Permission tab to control who has access to the model and who is allowed to build reports from its associated subject area.
  • Open the Properties tab to rename the model or connect the model to a different database.

Use Action Menus

Data Modeler provides action menus for most objects. When you select an object, you'll see a gear icon, which displays the menu (Action menu icon).

A global Model Actions menu in the upper right corner enables you to clear, close, refresh, or unlock the model.

You can also use action menus to delete individual data model objects that you have locked.

  • You can delete source views but you can’t delete source tables. Use SQL Workshop to drop tables in the source database.

  • You can’t delete model objects that other objects depend on.

Lock a Data Model

You lock a data model before making any changes. Click Lock to Edit to lock the data model.

Tips:

  • Publish changes regularly (browsers timeout after 20 minutes of inactivity).
  • Publish changes before closing your browser to ensure that the lock is released.
  • Lock your model before changing views.
  • If you have administrative privileges, you can override locks set by other users.

Validate a Data Model

You can use the global Validate checkmark icon Validate icon in the upper-left corner to check whether a data model is valid.

The data model is also validated automatically when you publish changes. Validation errors are shown at the bottom of the right pane.

Use the Message Actions menu to customize the types of messages displayed (Errors, Warnings, and Information).

Some tasks are validated when they’re performed. For example, you can’t save a source view unless its SQL query is valid. Expressions for calculated measures and derived columns must be valid before they can be saved. Validation messages that are displayed as you’re performing tasks provide more information about any validation errors.

Refresh and Synchronize Source Objects and Data Model Objects

Data Modeler provides three ways to refresh data to ensure you’re looking at the most up-to-date information. You can refresh source objects, refresh the data model, or synchronize the data model with source object definitions in the database.

Refresh Source Objects

You can refresh the Database pane to ensure that the source objects list reflects the latest objects in the database. For example, you can refresh the source objects list to include any new database tables that were added. The source objects list is not refreshed automatically after new objects are loaded in to the database.

To refresh source objects, select Refresh from the Database Actions menu in the left pane.

Refresh the Data Model

In some cases, other Data Modeler users might have locked the model and made changes. You can refresh the data model to ensure that Data Modeler is displaying the latest version of the model.

To refresh the data model, select Refresh from the Data Model Actions menu in the left pane.

Alternatively, select Refresh Model from the Model Actions gear menu Actions icon next to the Lock to Edit button.

Synchronize with the Database

You can synchronize the data model with source objects in the database. Synchronization identifies objects in the model that have been deleted in the database, as well as tables and columns that are new. It also identifies other discrepancies like column data type mismatches.

To synchronize all model objects and source objects with the database, select Synchronize with Database from the global Model Actions menu in the upper right corner.

To synchronize individual fact tables or dimension tables, select Synchronize with Database from the Actions menu for the given fact table or dimension table in the Data Model objects list in the left pane. Then, click OK.

You must lock the data model to synchronize with the database.

Synchronization discrepancies are displayed in a message box at the bottom of the right pane. Use the Message Actions menu to customize the types of messages displayed (Errors, Warnings, and Information), select or deselect all messages, and perform sync-up actions on selected messages. For example, you can select all data type mismatch warnings and then select Sync-up selected from the Actions menu to make the relevant synchronization changes.

Publish Changes to Your Data Model

As you update a data model, you make changes that you can save or discard. You publish a model to save the changes permanently and make the data available for use in reports. The published data model displays as a subject area.

Tip:

Although changes to the data model are saved as you work, they are saved in the browser session only. The changes aren’t truly saved until you publish the model.

When you publish a data model, it is validated automatically. Any validation errors appear in the bottom of the right pane. If you see validation errors, fix them and then try to publish the data model again.

After making changes to your data model, you can perform these actions using the menus in the upper-right corner:

  • Publish and Unlock — Verifies that the model is valid, saves the changes, and publishes the model for use with reports. The model is unlocked for other users.
  • Publish and Keep Lock — Verifies that the model is valid, saves the changes, and publishes the model for use with reports. The lock is retained for further edits.
  • Unlock — Removes the lock on the model so that other users can update it. Your unpublished changes to the model are discarded.
  • Revert — Returns the model to its previously published state. Your unpublished changes to the model are discarded, but the model remains locked.
  • Clear—Permanently deletes all objects in the model and removes them from any reports that are based on the model’s subject area.

You can also click Undo and Redo in the upper right corner to revert or reapply individual changes.

Tip:

You don’t need to publish the model to save database changes. Changes made to database views and other source database objects are saved to the database when you complete the action, not to the data model. For database changes, Undo and Redo aren't available.

After publishing your model it takes up to two minutes for changes to the data model to reflect in reports and dashboards. To see changes immediately, open the report, click Refresh, and then Reload Server Metadata.

Oracle Analytics Cloud takes a snapshot when you or someone else publishes changes to the data model. If you’re having some problems with the latest data model, you can ask your administrator to restore an earlier version.

Clear Cached Data

Oracle Analytics Cloud caches data to maximize performance. This means data updates may not immediately reflect in reports and Data Modeler.

After loading new data in your tables, you might want to clear the cache to see the very latest data.
  • To see new data in Data Modeler, select the Refresh Model menu.
  • To see new data in reports, manually clear the cache from the Data Model menu in the left pane
    • To clear cached data for a particular fact or dimension table, right-click the table and select Clear Cached Data.
    • To clear all cached data, click Data Model Actions, then select Clear All Cached Data to remove all data from the cache.

You can also select Clear All Cached Data from the global Model Actions menu in the upper-right corner.

Tip:

Always clear the cache after loading new data to ensure that the most recent data is displayed in reports.

Rename a Data Model

To rename a data model, lock it, select the Properties tab, and change the name.

This action also renames the corresponding subject area for reports.

Connect a Model to a Different Database

When you start a new data model you’re asked to select the database where your data is stored. All the tables and views in this database display in Data Modeler so you can add them to your model. Sometimes, data is moved or the source database changes. If this happens, change your model’s database connection.

If you change the database, reports based on the model’s subject area won't work unless all the required source objects are available in the new database.

  1. In Data Modeler, lock your model for editing.
  2. Click the Properties tab.
  3. Select the Database.
    If the database you want isn't listed, ask your administrator to set up the connection for you.
  4. Synchronize your data model with the new database. Select Synchronize with Database from the Model Actions menu.

Export a Data Model

Individual data models can be exported to a JSON file and the information imported on another service. If you want to make minor changes to the model, you can edit the JSON before importing it. For example, you might want to change the name of the model (modelDisplayName) or the database connection (connectionName).

  1. Open Data Modeler.
  2. In the Models page, click the Model Actions icon for the model you want to export, and select Export.
  3. Save the JSON file. The default name is model.json.

Import a Data Model

Individual data models can be exported to a JSON file and the information imported on another service. If you want to make minor changes to the model, you can edit the JSON before importing it. For example, you might want to change the name of the model (modelDisplayName) or the database connection (connectionName).

For any data model to work properly it must have access to the associated database tables. Before importing the data model, check whether Data Modeler can connect to the required database. If not, ask your administrator to set up the connection.

  1. Open Data Modeler.
  2. Click Import Model.
  3. Browse to the JSON file that contains the data model you want to import.
  4. Click OK.
  5. Optional: Select a database connection for the model.
    You’re asked to select a database connection if Data Modeler doesn't recognize the connection name in the JSON file. If the connection you want isn't listed, ask your administrator to set up the connection and try again.
  6. Optional: Choose whether to replace a data model with the same name. Click Yes to overwrite the model or No to cancel.
    This happens when the model named in the JSON file clashes with another model in Data Modeler. If you don't want to replace the existing model, change the modelDisplayName attribute in the JSON file and try again.

Delete a Data Model

You can delete all objects from your data model if you want to clear your model and start over. Or you can delete an entire model along with its subject area.

  • Clearing model content—Lock the model and select Clear Model from the global Model Actions menu in the upper right corner.

    This permanently removes all the objects in the data model and also removes them from any reports that are based on the model’s subject area.

  • Deleting a model—Click Data Modeler, click the Model Actions menu for the model you don't want anymore, and select Delete.

    This permanently removes the data model and its subject area.

Before clearing or deleting a model, we recommend that you or your administrator take a snapshot of the model as a backup