Create a Logical Data Model Manually

This is an alternative approach to Auto generate LDM. It is recommended to go this way in the following scenarios:

You want to create LDM first to define your reporting use cases
- Later you map LDM to PDM.
You want to customize and evolve your LDM
- Once you apply custom changes into LDM, the auto-generation would rewrite them
- You could regularly update the relational model, so auto-generation still works, but can be very costly and even not possible in some cases

Info

LDM is focused on your reporting needs and may not match relational model of your database. Before mapping LDM to PDM, you may have to alter the relational model (introduce an ETL process).

Create Datasets

To start creating your logical data model, simply drag and drop a new empty Dataset or Date from the left panel onto the canvas. Click on More … in the bottom-right corner of the dataset and pick View details. You can add attributes, additional labels, and facts into the dataset.

Here is the example of a manually created dataset “Order lines”:

New dataset

Map Your Logical Data Model to the Physical Model

Mapping your logical data model to the physical model is not required from the point of your ability to publish the model - you can publish a model without any mapping. However, mapping is mandatory from the point of your ability to utilize the model in an analytical application like Analytical Designer.

There is a second tab in the dataset detail - Data Mapping.

Data Mapping

As you can see, no table can be mapped to the dataset yet.

Use the following procedure:

Click on Scan button in the top-left corner, uncheck the checkbox “Generate datasets”
Return back to dataset details and select a table to be mapped with the dataset.
Map labels / facts to the physical columns.
Map all entities that you want to utilize in analytics applications.
Save changes.

Go to Physical Data Model to learn more about how to manage it.

Set the Primary Key

Before you can publish the model, you have to define the primary key (grain) of each dataset. You can find the Set primary key option in the bottom right corner (More…) of each dataset.

Set primary key

References (Relations) between Datasets

Creating a new dataset. In the following example it is the “Customers” dataset.
Set the primary key on the second dataset.
Click the dataset, locate the blue bullet on the right edge of the dataset object and drag it and drop it to target “Order lines” dataset to create a new reference.

References - reference

You can see that “Order lines” dataset has just been extended by “Customer id” foreign key.

Enter the detail of “Order lines” dataset, go to the Data Mapping tab and find the new foreign key entity. You can map it to physical column (foreign key).

References - reference mapping

The reference opens up new analytics scenarios in Analytical Designer, for example, you can slice the price from Order lines by the state from Customers.

Date Datasets

Drag and drop Date from the left panel:
Create a new reference between the new date dataset and a dataset that contains a date / timestamp column.
In our case it is “Order lines” dataset.
Drag the blue bullet on the right edge of the date dataset object and drop it to to the target “Order lines” dataset.
Map the new date “foreign key” with a physical column (just like in the case of a standard reference).
Configure date dataset details (optional).
- Press “Details” button within the date dataset box.
- In the configuration dialog you can:
  - Add description to date dataset.
  - Configure how the name of included date/time granularity will be formatted.
  - Select date/time granularity.
The Title pattern will define the overall format of the title for all included granularity. Using placeholders %titleBase and %granularityTitle you can position where the Title base and the default granularity title will be placed. If the Title base is not specified, the default name of date dataset will be used.