I have created a Data Set of  about 6 million rows out of a Data Module on a relational database of more than 100 million rows.

Now I need to create a new Data Module to be able to create reports on this Data Set.

But I want to have separate entities/dimensions in the Data Module, as the user expects it, instead of showing all columns in just one table.

The Data Set has aggregated data and the keys and foreign keys columns are not included.

How can I set the relationships across the different tables that are part of the Data Module for reporting? I'm using the typical Star Schema.

------------------------------
Alex Santamarta
------------------------------

#CognosAnalyticswithWatson

If I correctly understand the problem, you have a data set which has the dimensions and aggregated data of module. You have not included the keys of the dimensions in the data set.

You want to take the objects of the data set and present them into separate objects for each dimension and your measures so that what is presented to your users is something like a set of query subjects.

You don't mention it but I believe you have attempted to create views from the data set and have discovered that they are not like model query subjects in FM, which can store references to objects and don't need relationships between themselves and other objects.

Unfortunately, right now there isn't anything analogue to that functionality in CA modelling.

You can create folders in the data set in the module and have each folder contain the objects of a dimension and have a folder for your measures.  That might be sufficient for your users, possibly.

Here's a very simple example.

------------------------------
IAN HENDERSON
------------------------------

Original Message:
Sent: Fri August 27, 2021 09:46 AM
From: Alex Santamarta
Subject: Data Modules on Data Sets: how to create linked tables

I have created a Data Set of  about 6 million rows out of a Data Module on a relational database of more than 100 million rows.

Now I need to create a new Data Module to be able to create reports on this Data Set.

But I want to have separate entities/dimensions in the Data Module, as the user expects it, instead of showing all columns in just one table.

The Data Set has aggregated data and the keys and foreign keys columns are not included.

How can I set the relationships across the different tables that are part of the Data Module for reporting? I'm using the typical Star Schema.

------------------------------
Alex Santamarta
------------------------------

#CognosAnalyticswithWatson

YES!!! Thanks!

------------------------------
Alex Santamarta
------------------------------

Original Message:
Sent: Sun August 29, 2021 05:08 PM
From: IAN HENDERSON
Subject: Data Modules on Data Sets: how to create linked tables

If I correctly understand the problem, you have a data set which has the dimensions and aggregated data of module. You have not included the keys of the dimensions in the data set.

You want to take the objects of the data set and present them into separate objects for each dimension and your measures so that what is presented to your users is something like a set of query subjects.

You don't mention it but I believe you have attempted to create views from the data set and have discovered that they are not like model query subjects in FM, which can store references to objects and don't need relationships between themselves and other objects.

Unfortunately, right now there isn't anything analogue to that functionality in CA modelling.

You can create folders in the data set in the module and have each folder contain the objects of a dimension and have a folder for your measures.  That might be sufficient for your users, possibly.

Here's a very simple example.

------------------------------
IAN HENDERSON

Original Message:
Sent: Fri August 27, 2021 09:46 AM
From: Alex Santamarta
Subject: Data Modules on Data Sets: how to create linked tables

I have created a Data Set of  about 6 million rows out of a Data Module on a relational database of more than 100 million rows.

Now I need to create a new Data Module to be able to create reports on this Data Set.

But I want to have separate entities/dimensions in the Data Module, as the user expects it, instead of showing all columns in just one table.

The Data Set has aggregated data and the keys and foreign keys columns are not included.

How can I set the relationships across the different tables that are part of the Data Module for reporting? I'm using the typical Star Schema.

------------------------------
Alex Santamarta
------------------------------

#CognosAnalyticswithWatson