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!
From my experience, the implementation in framework packages is far easier than Data Modules. You just need to have a good “Time/Date” dimension and a “Relative Time” dimension including all the periods you need to compare (Current FT, Previous FY, Rolling 12 months, Rolling 12 months previous year, etc.). These two dimensions then are included in all the framework packages.
The relative time solution offered for data modules is ok for a quick and dirty approach, but the best course of action is to create a conformed dimension with relative periods.
Creating conformed relative time dimensions in your underlying data warehouse is a great solution but takes a lot more work. What is the relative time feature in data modules missing that you’d like to see? I can provide feedback directly to IBM!
The relative time feature in data modules misses creating relative time measure values relative to a date column. It now creates for example Prior Month [Amount] for the [Amount] measure, but this Prior Month is relative to the current date.
It would be awesome if it would be possible to relate this to a date column in a dimension (or fact). Then it would be very simple to create reports with these two measures next to eachother. Instead of creating a report with more complex DMR functions.
100% this. Is there a feature request for this yet? If not lets make one and I will pass it directly to offering management. This is the big problem with the relative time feature. You can customize the ‘as of date’ as a global param however that still requires manual intervention and is quite clunky compared to what you suggest here.
At the end of the day I still just want a modernized version of Transformer.
Thank you very mutch. I am very grateful for what you do on your blog, it helps me a lot in my work. It is not easy to find a well presented knowledge of practical use of Cognos, but thanks to you it is changing.
Btw I hope that in the new realases of Cognos the functionality of relative time will be developed even further.
Regards
You are welcome. I’m glad my content helps you in your job – your comment made my day!