Using Data Modeler

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

Topics:

Opening Data Modeler

You need the BI Data Model Author role to use Data Modeler. Ask your administrator to give you access if you don't see this option.

  1. Sign in to Oracle Analytics Cloud.
  2. Click the Page menu on the Home page, and select Open Data Modeler.
  3. Click the name of a model to open it in Data Modeler.
  4. To start a new model, click Create model.

Creating 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. See Managing Database Connections for Data Models.

Using 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.

Using 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.

Using Action Menus

Data Modeler provides action menus for most objects. Action menus are represented by a gear icon (Action menu icon).

Action menus contain actions that are relevant for a particular object or context, and are visible when the object is selected. For example, select a source object in the Database menu in the left pane to see its action menu.

A global Model Actions menu is also provided in the upper right corner. You use the global Model Actions menu for tasks that apply to the entire data model, such as clearing, closing, refreshing, or unlocking the model.

Deleting Individual Data Model Objects in Data Modeler

You can use action menus to delete data model objects. Note the following about deleting objects:

  • You must lock the model to delete an object.

  • 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. For example, you can’t delete a dimension table that is joined to another table until the join is removed. Similarly, you can’t delete a column that’s used in an expression, or a source view that’s being used in another view.

  • Objects aren’t truly deleted until changes are published, with the exception of source views. Source views are deleted when you complete the action.

Locking a Data Model

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

Always publish changes you want to keep before leaving Data Modeler for an extended length of time. When your HTTP browser session times out (after 20 minutes of inactivity), the lock is released and any unpublished changes are discarded.

Similarly, closing a browser ends the HTTP session and discards any unpublished changes. However, closing the browser does not release the lock. In this case, you can start a new session in a new browser and sign in with the same user name. When you attempt to lock the model in the new session, Data Modeler asks whether you want to reacquire the lock.

Changing Database Views

You must also lock the model if you want to change database views from Data Modeler. Changes you make to database views are immediately saved to the database. This is different to data model changes which are only saved when you publish them.

Locking the model prevents other users from changing database views using Data Modeler. The lock does not stop someone from modifying database objects using other tools, such as APEX and SQL Developer.

Overriding Locks

If you have administrative privileges, you can override locks set by other users. To do this, select Override Lock from the global Model Actions menu in the upper right corner. Overriding a lock discards changes made by other users in their browser sessions. You must have the BIServiceAdministrator role to override a lock.

Validating 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.

Refreshing and Synchronizing 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.

Refreshing 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.

Refreshing 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.

Synchronizing 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.

Publishing 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. See Restoring from a Snapshot.

Clearing 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.

Renaming 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.

Connecting 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.

Note:

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. See Managing Database Connections for Data Models.
  4. Synchronize your data model with the new database. Select Synchronize with Database from the Model Actions menu.

Exporting 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.

Importing 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. See Connecting to Data in an Oracle Cloud Database.

  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.

Deleting 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. For instructions, see Taking Snapshots and Restoring.