Framework Manager - Filtering huge relational data

[mvkvivek]

Hello,

I am very very new to Cognos. For the past 10 days I am working on this. I should say learning. I was asked to migrate existing cognos reports (based on SQL) to framework.

In my project, I imported a table (has 22M rows), created Query Subject with required columns (just 4 columns). Tried to add a filter to bring only small set of data. The filter is based on date and I am look for only today. The problem is if I hard code the filter value like this

[relational_db].[QUERY_SUBJECT_NAME].[MY_KEY]  > '20201028'

It works. It only picks only required rows.

To make it dynamic I used like this

[relational_db].[QUERY_SUBJECT_NAME].[MY_KEY]  > year(current_date) || month(current_date) || day(current_date)

When I test this it is long time to execute finally I am getting a timeout issue.

I tried similar filter in other table which has only 2k records and it worked fine.

How can I make this filter dynamically changing based on the current date?

Thanks

[BIengineer]

This is honestly something I would implement in Cognos instead - utilizing a parameter prompt for running the report (either a from-to date filter, or select a single day to filter for).

Restricting the data from FM too much makes the point of a re-useable package a little bit lost. You might need to filter for today only for this one report, but then comes a time when you want another report with the same data, except for showing historical data as well - then you might as well use the same model, filtered for different dates on Cognos level. Even though your table is 22M rows large, that does not mean it queries the entire thing in Cognos: depending on the top-level filters you define in Cognos, you can query out a subset of your table.

My suggestion for a solution in Cognos:

* Create a prompt page, add a Date filter and name the date parameter for the user to input.
* When setting up your report queries, you can add a detail filter using the date column from your FM-package, and filter it according to the date prompt parameter, like:

.[DateColumn] = ? Input_date ? or for a date range filter (requiring two date filters, thus two input parameters): .[DateColumn] >= ? Input_date_1 ? AND .[DateColumn] <= ? Input_date_2 ? --- If you absolutely need it done in FM, then you need to clarify a bit. [relational_db].[QUERY_SUBJECT_NAME].[MY_KEY]  > '20201028' would yield fields starting from the date '20201029', and the filter: [relational_db].[QUERY_SUBJECT_NAME].[MY_KEY]  > year(current_date) || month(current_date) || day(current_date) searches for rows having a day "larger" than today. If you need rows exactly for today, you either need to use the inclusive operator >= or simply = In your test table with 2k rows, what date rows did it select for you, and which date did you filter for?

[adam_mc]

I would do this by creating a Parameter Map that is based on a Query Subject that returns values of a number of significant dates eg. Today, Yesterday, Tomorrow, Last Year Today, etc...

Then, I would use the value of the appropriate date I need retrieved as a macro as the filter in my Query Subject.

So a query subject with following SQL would be basis for the Parameter Map (shows Todays Date as a number only):

SELECT
                'TODAY_TY' as PARM,
                to_number(to_char(a.CAL_DT, 'yyyymmdd')) as VALUE
FROM
                [EDW].CAL_DT a
WHERE
                a.CAL_DT = current_date


Then, once you've completed your Parameter setup, you would add the filter [relational_db].[QUERY_SUBJECT_NAME].[MY_KEY]  >= #$[Your Parameter Name]{'TODAY_TY'}# to your query subject.

This should not time-out as it passes the value of the date rather than the calculation of the date which will be performed against every row you are comparing against.

Hope this helps,
Adam