Create a Logical Data Model Manually
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, follow these steps:
- Create datasets.
- Set the primary key in datasets.
- Create relationships between datasets.
- Add Date datasets.
- Save the LDM.
You can create datasets in two ways:
Create Datasets From Pre-generated Datasets
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.
To add a dataset, drag a table from the left panel and drop it in the blank canvas area.
This action creates a dataset from your table. It guesses the field types (fact/attribute) based on the database types and it creates mapping between the database table and the LDM.
Repeat the previous step for all the tables you want to use.When you add a dataset to the LDM this way, relationships will be automatically created to other datasets that are referenced by the newly added dataset, if there are any.
Hover over the dataset and click View details.
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.
Create Datasets From Scratch
Follow these steps to create a dataset manually.
Open your workspace.
Click the Data tab.
The LDM Modeler opens in view mode.
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.
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.
Select the newly added dataset, click More… -> View details.
Add fields (facts, attributes, and attribute labels) to the dataset.
Click Save changes.
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 database.
Map the LDM to the Database
Set up mapping for all the datasets in your LDM. Mapping your LDM to the database allows you to use data from your database in GoodData.
The LDM focuses on your analytic needs and may not match the relational model of your database. Before mapping the LDM to the database, you may need to alter the relational model and introduce data transformation operations.
Select a dataset, click More… -> View details.
Click the Data mapping tab.
Map the dataset to a particular table in your database.
Map each field in the dataset to a specific column in this table.
Click Save changes.
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.
Select a dataset, and click More… -> Set primary key.
Select one or more attributes that should become the primary key, and click Set key.
The dialog closes, and the primary key is set.
Creating a Composite Primary Key
When selecting more than one attribute, a composite primary key is generated. The resulting composite key is a concatenation of the attribute values that make up the composite key. This results in a more distinctive identifier, facilitating the connection of datasets that might not share unique ID values otherwise.
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.
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.
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:
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.
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.
Select the target dataset. Click More… -> View details, then click the Data mapping tab.
Locate the newly added foreign key, and map it to a specific column in the table.
Your datasets are now associated through a relationship.
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
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.
Drag Date from the left panel and drop it in the blank canvas area.
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
Notice that the
Order linesdataset has been extended by the
Select the dataset where the foreign key was added to, click More… -> View details, then click the Data mapping tab.
Locate the newly added
Dateforeign key, and map it to a column in the table that is mapped to the dataset (the foreign key column).
(Optional) Configure the Date dataset.
Select the dataset, and click Details.
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.
Title patternfield defines the general format for the titles of all included granularity levels. Use the
%granularityTitleplaceholders to define the order in which the value from the
Title basefield and the default granularity title will be used in the title. If
Title baseis 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.
Create as many Date datasets as you need.