Kamil asks an excellent question about relative time in framework manager in response to my Framework Manager vs Data Modules article:
Great article. I have one question, is it possible to use relative dates with packages from framework manager?
– Kamil
Like all questions in Cognos, the short answer is ‘no’ and the long answer is ‘yes!’ Let’s take a quick dive into relative time in Framework Manaager.
Relative time in FM? No!
There is no ‘easy button’ for using the new relative time functionality with Framework Manager, unfortunately. I was briefly hopeful that this is possible using the enrich package functionality but it’s not there.
Enrich package is an important piece of the Cognos pie so it’s worth talking about briefly. Enriching a package provides needed context that allows Dashboards, Explore and the AI Assistant to do their thing. Most notably, enriching a package will allow Cognos to properly display time and geographic data types and will collect the sample data that allows the AI assistant and Explore to function properly. Enriching a package taxes your system so consider restricting the query subject or running it off hours.
If easy relative time does come to FM this is where I’d expect it to go. It’s worth reiterating that FM itself will receive no changes going forward so it’s time to start making the change to data modules. It’s easier than you think!
Relative time in FM? Yes!
Here’s where things get a little trickier and using relative time with your FM model becomes possible. To make this work we’re going to need to use https://ibmblueview.com/what-are-cognos-data-modules/Data Modules, custom tables and the lookup reference feature.
Step 1: Add a package source to a data module
- Click the ‘new’ icon and select data module
- Navigate to your package in the folder structure, click on it and click ‘OK’
- The data module screen will open with the package visible in the data tray
Step 2: Create a custom time table
- Click the ‘Custom tables’ tab and click ‘Create custom table’
- Click ‘select tables’ and click the package source. All the tables in the package are displayed on the left.
- Click ‘create a view of tables’ and click ‘Next’
- Don’t forget to give your custom table a new name!
- Click ‘invert’ then select only the table with which you want to use relative time
- Click finish. Your custom time table will appear in the data tray
Step 3: Add relative time functionality
- Click the ‘Add sources and tables’ button and select ‘Add more sources’
- Navigate to the ‘Calendars’ folder in the samples and select the ‘Fiscal calendar’ data module
- Click ‘OK’. The FiscalCalendar table will appear in the data tray, hidden by default
- Expand your custom time table, click the date you wish to use for relative time and click the ‘properties’ button in the upper right. The properties window will open.
- In the properties window, select ‘FiscalCalendar’ in the ‘Lookup reference’ drop down menu.
- You now have relative time functionality in your data module!
- Rinse and repeat for any additional time or measure fields that require this functionality
Step 4: Join the custom time table to the package
- Click your custom time table and choose ‘New… Relationship’ in the pop up menu
- Select the appropriate table to relate the custom time table to the rest of the package. Oftentimes this is a fact table.
- Select the appropriate field(s), cardinality and join type for the join.
- Click ‘OK’
There you have it! Relative time in Framework Manager (sort of)
At this point you can save and use your data module, which is made of your pre-existing package plus one or more custom tables. This doesn’t solve the obvious problem that your existing content references the package and not the new data module, but new content can be built off this module. The module will even inherit changes made to the package. And there you have it – relative time in Framework Manager… sort of!
