• Skip to main content

IBM Blueview

Cognos Analytics and all things IBM

  • The Blog
  • Cognos Glossary
  • Cognos Resources
  • About Me
  • Categories
    • Cognos
      • Data Modules
      • Administration
      • Framework Manager
      • Dashboards
    • Opinion
    • Community Spotlight
  • PMsquare
  • Subscribe

Relative Time in Framework Manager

December 31, 2019 by Ryan Dolley 6 Comments

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!

The enrich package screen of Cognos
The Enrich Package interface

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

Adding a package as a source to data modules
A package has been added as a data source to this data module
  1. Click the ‘new’ icon and select data module
  2. Navigate to your package in the folder structure, click on it and click ‘OK’
  3. The data module screen will open with the package visible in the data tray

Step 2: Create a custom time table

Building a custom table in Cognos
Building a custom time table from a package
  1. Click the ‘Custom tables’ tab and click ‘Create custom table’
  2. Click ‘select tables’ and click the package source. All the tables in the package are displayed on the left.
  3. Click ‘create a view of tables’ and click ‘Next’
  4. Don’t forget to give your custom table a new name!
  5. Click ‘invert’ then select only the table with which you want to use relative time
  6. Click finish. Your custom time table will appear in the data tray

Step 3: Add relative time functionality

Adding relative time to the custom table in Cognos
Relative time can be added to your custom time table
  1. Click the ‘Add sources and tables’ button and select ‘Add more sources’
  2. Navigate to the ‘Calendars’ folder in the samples and select the ‘Fiscal calendar’ data module
  3. Click ‘OK’. The FiscalCalendar table will appear in the data tray, hidden by default
  4. 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.
  5. In the properties window, select ‘FiscalCalendar’ in the ‘Lookup reference’ drop down menu.
  6. You now have relative time functionality in your data module!
  7. Rinse and repeat for any additional time or measure fields that require this functionality

Step 4: Join the custom time table to the package

Joining a custom table to a framework manager package
The custom time table can now be joined to the package
  1. Click your custom time table and choose ‘New… Relationship’ in the pop up menu
  2. Select the appropriate table to relate the custom time table to the rest of the package. Oftentimes this is a fact table.
  3. Select the appropriate field(s), cardinality and join type for the join.
  4. 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!


Read on to level up your Cognos skills!

  • Cognos Union Queries in Reports
  • Cognos Relative Dates in 11.2
  • The 2021 Gartner BI Magic Quadrant is Broken for Cognos Analytics
  • Data Modeling for Success: BACon 2020
  • Cognos Analytics 11.1.6 What’s New

Filed Under: Cognos, Data Modules, Framework Manager

Reader Interactions

Comments

  1. Gaston says

    January 9, 2020 at 12:11 am

    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.

    Reply
    • Ryan Dolley says

      January 16, 2020 at 9:33 am

      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!

      Reply
      • Thomas says

        January 22, 2020 at 9:03 am

        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.

        Reply
        • Ryan Dolley says

          January 22, 2020 at 9:06 am

          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.

          Reply
  2. Kamil says

    January 16, 2020 at 8:23 am

    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

    Reply
    • Ryan Dolley says

      January 16, 2020 at 9:25 am

      You are welcome. I’m glad my content helps you in your job – your comment made my day!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Copyright © 2023 · Atmosphere Pro on Genesis Framework · WordPress · Log in