Evolve Your Logical Data Model

In a typical workflow, you evolve your logical data model (LDM) over time. For example, when you add a new table to your database and you want to use the data from this table in your visualizations, you have to update the LDM accordingly.

To demonstrate how to evolve the LDM, this article is going to use the LDM that is derived from the pre-installed PostgreSQL database with the sample data prepared in the GoodData.CN Community Edition image.

Let’s imagine that there is a new requirement from business - start tracking plans. A new table plans is introduced containing planned revenue per month, campaign and product. Business users want to track, if planned revenue was met in each month and alternatively drill down to campaigns and products.

In this example, a business has introduced a new requirement to start tracking planned revenue. To meet this requirement, a new table named plans is introduced. The table contains planned revenue per month, campaign, and product. The business wants to track if planned revenue was met in each month and alternatively drill down to campaigns and products.

To evolve the LDM, do the following:

  1. Alter the relational model of the database.

  2. Update the PDM and the LDM using one of following methods:

  3. Publish the LDM.

  4. Evaluate the updated LDM in Analytical Designer.

Alter the Relational Model of the Database

You are going to alter the relational model of the pre-installed PostgreSQL database.

Use your favorite tool for working with databases, for example, DBeaver. If you prefer the command line, you can use psql from inside the container:

# Change "some-gooddata" to the name of the container that you chose during the startup of GoodData.CN
docker exec -it some-gooddata bash
psql -U demouser -d demo

Steps:

  1. Connect to the database using the following credentials:

    • The username is demouser.
    • The password is demopass.

  2. Execute the following SQL script to create new table plans. For our example, the table is populated with random data. Referential integrity is optional. If you created referential integrity and did not make any custom changes in the the LDM, you can generate the LDM automatically.

    set search_path to demo;

create table "plans" (
  "plan_id" integer not null,
  "date" date not null,
  "campaign_id" integer,
  "product_id" integer not null,
  "planned_revenue" decimal(15,2) not null
);

-- Populate some data to demonstrate the use case.
-- Generate unique values into column plan_id serving primary key.
insert into "plans"
select
  row_number() over () as plan_id, *
from (
  select
    date_trunc('month', "date"), "campaign_id", "product_id",
    -- use random to simulate if plan was / was not met
    sum("price" * "quantity" * (0.85 + random() / 5)) as planned_revenue
    --, sum("price" * "quantity") as revenue
  from "order_lines"
  group by date_trunc('month', "date"), "campaign_id", "product_id"
) x;

-- Optionally add referential integrity to help scan model to detect primary keys and references between datasets
alter table "plans" add constraint pk_plans primary key ("plan_id");
alter table "plans" add constraint fk_plans_campaign foreign key ("campaign_id") references "campaigns" ("campaign_id");
alter table "plans" add constraint fk_plans_product foreign key ("product_id") references "products" ("product_id");

Update the PDM and the LDM

Update the PDM and the LDM Manually in the LDM Modeler

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 should see the demo-ds data source in the left panel.

  4. Create a dataset named Plans. For information about how to create datasets, see Create a Logical Data Model Manually.

  5. Add the attribute Plan Id and set it as primary key.

  6. Add the fact Planned Revenue.

  7. Create relationships between the datasets:

    • From Products to Plans
    • From Campaigns to Plans
    • From Date (date dataset) to Plans

    Extended Demo Model - Manual Step 1

  8. Generate the PDM. For more information, see Generate a Physical Data Model in the LDM Modeler.

    Be sure to de-select the Generate datasets checkbox when configuring the scan dialog.

  9. Set up mapping for the Plans dataset. For information about how to map the LDM to the PDM, see Create a Logical Data Model Manually.

    Map the dataset to the plans table and all attributes, facts, and foreign keys to the corresponding columns from that table.

    Extended Demo Model - Manual Step 2

Automatically Generate the Updated PDM and LDM in the LDM Modeler

Because the primary keys and foreign keys are set up in the database, you can automatically generate the updated PDM and LDM in the LDM Modeler. To do so, follow the instructions from this article.

Be sure to choose the Replace the existing model scan mode when configuring the scan dialog. This mode will replace the current PDM and prevent invalid mapping between LDM and PDM.

In this case, you end up with the same model, which you created in the previous chapter Update the PDM and the LDM Manually in the LDM Modeler.

Automatically Generate the Updated PDM and LDM Using the API

To automatically generate the updated PDM and LDM using the API, follow the instructions from this article.

Evaluate the Updated LDM in Analytical Designer

In Analytical Designer, create a new visualization to validate the updated model.

  1. Pick Line chart visualization type.
  2. Drag & drop Revenue stored metric (in Ungrouped folder) and the new Revenue Planned (in Plans folder) fact into Metrics
  3. Drag & drop Date into Trend by. Change Group by of Date to Month.
  4. Store the new visualization as Revenue Plan vs. Reality

You should end up with the following visualization:

Visualization with Extended Model

Monitor Model Changes