QuoteThe fact table in question is a table with about 130.000 records, ... When using this dimension, the number of records increases to 170 million.

After rereading this, I think this is the symptom to focus on.

By any chance, does your date dimension have about 1300 rows?  About 3.6 years?  Of course, working with non-rounded numbers to begin with would help.

I think what you are meaning by N:N is not even that the date table and the fact have a many-to-many relationship.  I think you're saying they have no relationship -- a cross join or cartesian join.  So the result is a dataset with the number of rows being the number or rows from fact times the number of rows from the date dimension.

Creating a proper relationship between the tables will help.  The result should be the number of rows on the fact.  Once you have that, you can start trying to use the dataset to answer questions.

I don't know what you mean by 'trajectories' and I think I don't need to know, although knowing would be nice.

You want the count of trajectories where start date >= {some date} and end date <= {some other date} is that a correct understanding?  That is easily done, without mucking about with many to many relationships.  Why didn't you try that?

Quote from: bus_pass_man on 05 Dec 2025 06:08:47 PMYes I think you're doing something wrong.  You don't mention a bridge table for your N:N relationship, which you probably would want to have for any legitimate bridge table scenario but I really don't think this is one of them. 
  Can you clarify what you mean by this.  Is this a duration?  What are you trying to do here?

Also is the N:N in the time dimension or the relationship between it and the fact table?  You are unclear.


I can not comment about possible other modelling problems because I have not seen your model and all the information I have is what you have chosen to reveal.



One very big problem with data sets is that you can't define data security on them, unlike tables in a loaded schema.

Thanks for your response. 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


A trajectory typically spans several months. I have considered using a year-month calendar instead, but in the dataset-solution the response time was very acceptable when using a year-date calendar. Furthermore, a year-date calendar would leave more of the original data, since in different use-cases reporting on date level is required. Adding several calendars to describe the same time(span) would make working with the data too complex for end users.

Quote from: dougp on 05 Dec 2025 12:22:43 PMThat is expected.  The dashboard that gets data from the database must perform these tasks for every visualization every time the user touches something.
write SQL
connect to the database server
run a query that involves joins between multiple tables
get a response back
update the viz

Put another way, when you are connecting to the database:
Cognos must write a more complex query.
Cognos spends time communicating with the db server.
the database server must perform lookups and filters across joins.
Cognos spends more time communicating with the db server.
Cognos waits for the data to be downloaded across the network.

In contrast, using a Cognos dataset means
write SQL
get data from a single table
update the viz

Choosing to use a Cognos dataset involves considering the tradeoffs between size and speed.  For most cases, a dataset will perform much faster than a direct database connection.


This is the same as using Power BI and comparing Import vs. Direct Query.

Thanks for your reply. So this logic can explain why the dataset-solution would show a visualisation after 10 seconds, but the oracle-solution times out after a few minutes?

I have a date table that includes holidays.  So Event Studio would work.  It doesn't seem it would be much effort for someone to create a package for Event Studio to use.

Alternatively, you could use an external event (https://www.ibm.com/docs/en/cognos-analytics/12.0.x?topic=scheduling-set-up-trigger-occurrence-server) based on a script that computes 2nd business day.  On Windows I might do this by calling a PowerShell script from a Windows Scheduled Task.  But you can also call the script from an ETL process to be sure it's not just on a schedule, but also that it runs following successful completion of your data mart load (instead of after a failure or while the data is still being loaded).

Hi all,
I need a report to run on the 2nd business day of each month excluding Canadian (and company) holidays.  The report is built on a data module so I can't use the Event agent.  I have identified the following dates that the report should run for 2026 (2026-01-05,2026-02-03,2026-03-03,2026-04-02,2026-05-02,2026-06-02,2026-07-03,2026-08-05,2026-09-02,2026-10-02,2026-11-03,2026-12-02).
I can manually schedule the report 12 times but was wondering if there was an alternate way to do that?
thx!

Hiya,

Thanks for your reply. That is Active directory. Sorry for the confusion.

Yes I think you're doing something wrong.  You don't mention a bridge table for your N:N relationship, which you probably would want to have for any legitimate bridge table scenario but I really don't think this is one of them. 

Quotecovering the time between START_DATE and END_DATE.

Can you clarify what you mean by this.  Is this a duration?  What are you trying to do here?

Also is the N:N in the time dimension or the relationship between it and the fact table?  You are unclear.


I can not comment about possible other modelling problems because I have not seen your model and all the information I have is what you have chosen to reveal.



One very big problem with data sets is that you can't define data security on them, unlike tables in a loaded schema.

I use Active Directory on prem, even though we have Azure Active Directory.

I'm sorry, but I'm not up on my acronyms.  What specifically is "ADS"?  Unfortunately, when I search the web for ADS is get ads (advertisements).  Not something I typically need to search for deliberately.  I get plenty already.

That is expected.  The dashboard that gets data from the database must perform these tasks for every visualization every time the user touches something.
write SQL
connect to the database server
run a query that involves joins between multiple tables
get a response back
update the viz

Put another way, when you are connecting to the database:
Cognos must write a more complex query.
Cognos spends time communicating with the db server.
the database server must perform lookups and filters across joins.
Cognos spends more time communicating with the db server.
Cognos waits for the data to be downloaded across the network.

In contrast, using a Cognos dataset means
write SQL
get data from a single table
update the viz

Choosing to use a Cognos dataset involves considering the tradeoffs between size and speed.  For most cases, a dataset will perform much faster than a direct database connection.


This is the same as using Power BI and comparing Import vs. Direct Query.