Create a Logical Data Model Manually In the LDM Modeler

Once you have generated the physical data model (PDM) either using the API or in the LDM Modeler, create the logical data model (LDM) manually in the LDM Modeler.

If you cannot or do not want to create the LDM manually, you can generate the LDM automatically using the API or in the LDM Modeler.

Creating the LDM manually is suitable for the following situations:

• Your database contains the complex analytical scenarios.
• Your database stores only a part of the metadata (for example, relations are handled by application logic and not by foreign keys in the database).
• You want to first create the LDM and then define your analytic use cases based on it.
• You want to build your analytic experience iteratively, customize, and evolve your LDM. In this case, consider the following:
  • If you make custom changes to the LDM and then automatically generate the LDM later, the custom changes will be overwritten.
  • If you regularly update the relational model of your database, maintaining the LDM manually can be very costly and even not possible in some cases.

To create the LDM manually, do the following:

1. Create datasets.
2. Map the LDM to the PDM.
3. Set the primary key in datasets.
4. Create relationships between datasets.
5. Add Date datasets.
6. Save the LDM.

Create Datasets

Create Datasets From Your Data Source

Even if you do not generate the LDM automatically, you can still use the automatically pre-generated datasets in combination with any datasets you may create from scratch. This is the recommended way of creating datasets in your LDM.

Steps:

1. To add a dataset, drag a dataset from the left panel and drop it in the blank canvas area.

   

   This action creates datasets from your database. It guesses the field types (fact/attribute) based on the database types and it creates mapping between the database table and the LDM.

   If you add a dataset to the LDM this way, any other datasets referenced by the dataset will be added as well. You can always delete the datasets you don’t want in your LDM.

2. Hover over the dataset and click View details.

   

3. Review the Fields and Data mapping tabs and ensure the data is mapped correctly according to your needs. Change the field types or reconfigure the data mapping if necessary (see Map the LDM to the PDM).

Create Datasets From Scratch

Follow these steps to create a dataset manually.

Steps:

1. Open your workspace.

2. Click the Data tab.

   The LDM Modeler opens in view mode.

3. Click Edit.

   The LDM Modeler is switched to edit mode. You can see the registered data sources in the left panel.

   

   If you do not have any data source, you cannot generate the LDM. Create a data source first.

4. To add a dataset, drag Empty dataset from the left panel and drop it in the blank canvas area.

   

   Don’t forget to give it an appropriate name.

5. Select the newly added dataset, click More… -> View details.

   

6. Add fields (facts, attributes, and attribute labels) to the dataset.

   

7. Click Save changes.

   

8. Repeat these steps to create as many datasets as you need.

   Once you have created one or more datasets in the LDM, the next step will be to map the datasets to the PDM.

Map the LDM to the PDM

Set up mapping for all the datasets in your LDM. Mapping your LDM to the PDM allows you to use data from your database in GoodData.

Steps:

1. Select a dataset, click More… -> View details.

2. Click the Data mapping tab.

3. Map the dataset to a particular table in your database.

   

4. Map each field in the dataset to a specific column in this table.

   

5. Click Save changes.

6. Repeat these steps for every dataset in your LDM.

Set the Primary Key in Datasets

Set the primary key (grain) for datasets in your LDM.

Steps:

1. Select a dataset, and click More… -> Set primary key.

2. Select the attribute that should become the primary key, and click Set key.

   

   The dialog closes, and the primary key is set.

3. Repeat these steps for every dataset in your LDM.

Create Relationships between Datasets

A relationship between two datasets allows you to use information from one dataset to slice the data in the other dataset. Creating relationships allows you to discover new analytical scenarios when creating visualizations.

Steps:

1. Locate the datasets that you want to create a relationship between.

   

   In this example you want to be able to use the information from the Products (originating) dataset in the Order lines (target) dataset.

2. Select the originating dataset. Click the blue dot on the right border of the originating dataset and drag the arrow to connect it to the target dataset.

   

   The following dialog opens:

   

3. Customize the relationship as required.

   In the target dataset, the primary key defines the unique identifier for a row of data from the originating dataset. If the originating dataset does not contain a primary key, select an attribute from the Select Primary Key drop down list to set it as the primary key.

   We recommend you use the default 1:N relationship type.

   In the Select Reference Field drop down list you can choose from one of the following options:

   • Connect as new field: Add the primary key of the originating dataset as a new attribute (foreign key) to the target dataset. The foreign key serves as a reference to the originating dataset.

   • Existing dataset fields: Reuse an existing attribute from the target dataset, and make it a reference (foreign key) to the originating dataset.

4. Click Connect.

   Notice that the target dataset has been extended by the foreign key from the originating dataset.

   If you reused an existing attribute from the target dataset, your datasets are now associated through a relationship. Continue to step 7.

   

   If you created a new attribute in the target dataset, continue to the next step.

   

5. Select the target dataset. Click More… -> View details, then click the Data mapping tab.

   

6. Locate the newly added foreign key, and map it to a specific column in the table.

   

   Your datasets are now associated through a relationship.

   

7. Repeat these steps for all the datasets in your LDM that you want to connect.

Add Date Datasets

A Date dataset is a dataset that represents DATE / TIMESTAMP columns in your database. The Date dataset helps you manage time-based data and enables aggregation at the day, week, month, quarter, and year level.

Steps:

1. Drag Date from the left panel and drop it in the blank canvas area.

   

2. Create a relationship between the new Date dataset and a dataset that contains a date/timestamp column.

   In the following image, the dataset with a date/timestamp column is the Order lines dataset.

   

   Notice that the Order lines dataset has been extended by the Date foreign key.

3. Select the dataset where the foreign key was added to, click More… -> View details, then click the Data mapping tab.

4. Locate the newly added Date foreign key, and map it to a column in the table that is mapped to the dataset (the foreign key column).

   

5. (Optional) Configure the Date dataset.

   1. Select the dataset, and click Details.

      

   2. In the configuration dialog, configure the dataset as needed:

      

      • Add a description to the dataset.

      • Configure how the name of the included date/time granularity levels will be displayed.

        The Title pattern field defines the general format for the titles of all included granularity levels. Use the %titleBase and %granularityTitle placeholders to define the order in which the value from the Title base field and the default granularity title will be used in the title. If Title base is not specified, the default name of the Date dataset (Date) will be used (for example, Date - Year, Date - Hour, and so on).

      • Select the date/time granularity levels that you want to include in the Date dataset.

        Some date granularity levels are selected by default and cannot be excluded from the Date dataset.

6. Create as many Date datasets as you need.

Save the LDM

Save the LDM to confirm your changes.

Once you have saved the LDM, you can start building visualizations and dashboards.

Change the model layout

By default in your logical data model, all dimension datasets are on the left side and the fact datasets are on right side.

To change the layout any time:

1. Open the logical data model for editing.
2. Drag any dataset to a new place.
3. Save the model.

To switch between your custom layout and the default Analytical layout, click the layout icon at the bottom and select the layout to display.