Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

Hi Matthijs,

You might also want to check you are using a supported Oracle version, and a supported JDBC driver.

Best regards,

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

Hi Matthijs,

you should also compare the size of your dataset with the number of rows in the table of your Oracle DB.

The question is: you wrote that there's a n:n relationshiop with the calendar table. Is this relationship resovled on the dataset and the Oracle table as well or dou you have a star schema in the database?

Hi Matthijs,

You might also want to check you are using a supported Oracle version, and a supported JDBC driver.

Best regards,

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

Good morning,

The oracle database only contains tables: no star schema is created there.

In other words:

Dataset solution: fact and dimension tables are created as individual tables in datasets. -> The required datasets are logically connected in a datamodule. ->A dashboard is built on the data module

Database solution: fact and dimension tables are created as individual tables in an oracle database. -> A connection between cognos and the database is made -> The required tables are loaded into a datamodule -> The required tables are logically connected in said datamodule. ->A dashboard is built on the data module

Each dataset and their database-table-counterpart have the exact same number of rows and columns.

------------------------------
Matthijs Oosterloo

Original Message:
Sent: Mon December 08, 2025 04:22 AM
From: Robert Dostal
Subject: Dashboard performs worse on an Oracle database than on Cognos Datasets

Hi Matthijs,

you should also compare the size of your dataset with the number of rows in the table of your Oracle DB.

The question is: you wrote that there's a n:n relationshiop with the calendar table. Is this relationship resovled on the dataset and the Oracle table as well or dou you have a star schema in the database?

------------------------------
Robert Dostal
Principal Expert BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Kupferzell

Original Message:
Sent: Mon December 08, 2025 04:07 AM
From: Patrick Neveu
Subject: Dashboard performs worse on an Oracle database than on Cognos Datasets

Hi Matthijs,

You might also want to check you are using a supported Oracle version, and a supported JDBC driver.

Best regards,

------------------------------
Patrick Neveu
BSL Consulting
IBM Champion

Original Message:
Sent: Fri December 05, 2025 05:01 AM
From: Matthijs Oosterloo
Subject: Dashboard performs worse on an Oracle database than on Cognos Datasets

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

------------------------------
Matthijs Oosterloo
------------------------------

Good morning,

The oracle database only contains tables: no star schema is created there.

In other words:

Dataset solution: fact and dimension tables are created as individual tables in datasets. -> The required datasets are logically connected in a datamodule. ->A dashboard is built on the data module

Database solution: fact and dimension tables are created as individual tables in an oracle database. -> A connection between cognos and the database is made -> The required tables are loaded into a datamodule -> The required tables are logically connected in said datamodule. ->A dashboard is built on the data module

Each dataset and their database-table-counterpart have the exact same number of rows and columns.

Hi Matthijs,

you should also compare the size of your dataset with the number of rows in the table of your Oracle DB.

The question is: you wrote that there's a n:n relationshiop with the calendar table. Is this relationship resovled on the dataset and the Oracle table as well or dou you have a star schema in the database?

Hi Matthijs,

You might also want to check you are using a supported Oracle version, and a supported JDBC driver.

Best regards,

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

There's some more information here.  I've included what I think is particularly relevant below.  
 Why do you think you need a N:N relationship?  Also could you answer Robert's question please.

https://www.cognoise.com/index.php/topic,38462.msg126163.html

The START_DATE and END_DATE indeed show the duration of a trajectory. Both columns are used for different use-cases. In this case I want to report on the number of active trajectories at a given time, using a N:N connection between the two dates and a year-date calendar, s.t.:

FACT1.START_DATE >= CALENDAR.DATE
FACT1.EIND_DATE <= CALENDAR.DATE

Good morning,

The oracle database only contains tables: no star schema is created there.

In other words:

Dataset solution: fact and dimension tables are created as individual tables in datasets. -> The required datasets are logically connected in a datamodule. ->A dashboard is built on the data module

Database solution: fact and dimension tables are created as individual tables in an oracle database. -> A connection between cognos and the database is made -> The required tables are loaded into a datamodule -> The required tables are logically connected in said datamodule. ->A dashboard is built on the data module

Each dataset and their database-table-counterpart have the exact same number of rows and columns.

Hi Matthijs,

you should also compare the size of your dataset with the number of rows in the table of your Oracle DB.

The question is: you wrote that there's a n:n relationshiop with the calendar table. Is this relationship resovled on the dataset and the Oracle table as well or dou you have a star schema in the database?

Hi Matthijs,

You might also want to check you are using a supported Oracle version, and a supported JDBC driver.

Best regards,

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

I would also (more or less) presume that the n:n relationship which sounds like a cross join between fact and calendar table is one of the root causes here.
You wrote that 170M records is the result when you combine fact and calendar table. If this is the end result after querying both tables on the database, then your dataset should also contain 170M records. Is that true?

I've never experienced a huge discrepancy between a dataset and the database when we talk about smaller tables. Yes, Cognos has to take more steps to get data from a db vendor than from a dataset but that's due to the concept that in a database you usually have multiple tables that have to be joined on-the-fly. The response should be visible in nearly the same amount of time. 

That said, if your dataset is only 130k rows in size as this is the end result of the summarized query of fact and calendar table but  "on-the-fly" amount of records that must be processed by the database is 170m - then I don't  wonder about bad performance.

For me it sounds like the issue we once had with our budget data. The data was stored at month level but users wanted to see it on a daily basis to compare iot with YTD actual data. I then joined the budget table with a few millions of records with the calendar table to get the end result on a day level and then summarize that back to a year level. The temporarily amount of data were roughly 260m records and the queries took long time to run - despite the fact that the result was just a frew thousand lines.

There's some more information here.  I've included what I think is particularly relevant below.  
 Why do you think you need a N:N relationship?  Also could you answer Robert's question please.

https://www.cognoise.com/index.php/topic,38462.msg126163.html

The START_DATE and END_DATE indeed show the duration of a trajectory. Both columns are used for different use-cases. In this case I want to report on the number of active trajectories at a given time, using a N:N connection between the two dates and a year-date calendar, s.t.:

FACT1.START_DATE >= CALENDAR.DATE
FACT1.EIND_DATE <= CALENDAR.DATE

Good morning,

The oracle database only contains tables: no star schema is created there.

In other words:

Dataset solution: fact and dimension tables are created as individual tables in datasets. -> The required datasets are logically connected in a datamodule. ->A dashboard is built on the data module

Database solution: fact and dimension tables are created as individual tables in an oracle database. -> A connection between cognos and the database is made -> The required tables are loaded into a datamodule -> The required tables are logically connected in said datamodule. ->A dashboard is built on the data module

Each dataset and their database-table-counterpart have the exact same number of rows and columns.

Hi Matthijs,

you should also compare the size of your dataset with the number of rows in the table of your Oracle DB.

The question is: you wrote that there's a n:n relationshiop with the calendar table. Is this relationship resovled on the dataset and the Oracle table as well or dou you have a star schema in the database?

Hi Matthijs,

You might also want to check you are using a supported Oracle version, and a supported JDBC driver.

Best regards,

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

There's some more information here.  I've included what I think is particularly relevant below.  
 Why do you think you need a N:N relationship?  Also could you answer Robert's question please.

https://www.cognoise.com/index.php/topic,38462.msg126163.html

The START_DATE and END_DATE indeed show the duration of a trajectory. Both columns are used for different use-cases. In this case I want to report on the number of active trajectories at a given time, using a N:N connection between the two dates and a year-date calendar, s.t.:

FACT1.START_DATE >= CALENDAR.DATE
FACT1.EIND_DATE <= CALENDAR.DATE

Good morning,

The oracle database only contains tables: no star schema is created there.

In other words:

Dataset solution: fact and dimension tables are created as individual tables in datasets. -> The required datasets are logically connected in a datamodule. ->A dashboard is built on the data module

Database solution: fact and dimension tables are created as individual tables in an oracle database. -> A connection between cognos and the database is made -> The required tables are loaded into a datamodule -> The required tables are logically connected in said datamodule. ->A dashboard is built on the data module

Each dataset and their database-table-counterpart have the exact same number of rows and columns.

Hi Matthijs,

you should also compare the size of your dataset with the number of rows in the table of your Oracle DB.

The question is: you wrote that there's a n:n relationshiop with the calendar table. Is this relationship resovled on the dataset and the Oracle table as well or dou you have a star schema in the database?

Hi Matthijs,

You might also want to check you are using a supported Oracle version, and a supported JDBC driver.

Best regards,

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

Hi, I recently attended an excellent webinar titled Cognos Analytics Tips & Tricks and AMA.  If you did not attend I would recommend taking a look.  There was a great deal of valuable material presented about how to evaluate the SQL being generated in CA which I think would be extremely helpful in diagnosing your issue with the Oracle implementation of your data sets.  I have included a link to the replay.  Thanks. https://ibm.webcasts.com/starthere.jsp?ei=1741568&tp_key=8a951355c7

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

Good morning!

As the title says: I have made a number of datasets in Cognos, and connected them logically in a data module. On this module I built a dashboard that performs perfectly.

To save the local cognos server, I asked our local ETL specialist to recreated the datasets with ETL, and to write them off in an Oracle database. These tables I have then connected again in a data module, in the exact same way as I did with the datasets. On this module I built an identical dashboard as well. The performance of this dashboard is absolutely horrable, and most visualisations time-out on loading.

The fact table in question is a table with about 130.000 records, using several foreign keys for which dimensions were built. Most of these dimensions are 1:N to the fact table, but I also built a date dimension that is N:N, covering the time between START_DATE and END_DATE. When using this dimension, the number of records increases to 170 million.

Am I doing something wrong, or is the discrepancy in performance between the Oracle-database and the Datasets to be expected?

Thanks for your insights!

------------------------------
Matthijs Oosterloo
------------------------------