Cognos Impromptu Catalog Folders and Time Dimension

[monsky]

Hi Guys,

Given a fact table that has several date keys and one period table to which I will relate these keys. The approach to be taken is to create views for the period table for each role creating virtual period tables.

First question, is it a good idea to represent this as alias tables in Impromptu Catalog instead of creating views in the RDBMS?

Second question, how would you organize your folders for each period table in the catalog?

Now this may be confusing so let me know if you need clarification.

 

[DoubleD]

I would suggest views in the RDBMS. You then have the option to do performance tweaking for each of the views if necessary. Also, the RDBMS views would be reusable in multiple catalogs.

I'd need to understand a little more detail around the table structure and what the date fields mean before I would hazard any suggestions regarding folder structure.

I am what I am based on the decisions I have made.

DoubleD

 

[monsky]

Hi DoubleD,

Here's an example of the Folder Organization:

-Period
- Date
- Year
- Month
- Fiscal Year
- Fiscal Month
-Agent
-Agency
-Agent Balances
-Agent Income
-Agent Balances
-Policies
- Issue Date
- Submit Date
- Paid Up Date
- Policy Number

Now, the Policies folder have several dates and I currently have only one instance of the Period folder. Assuming this is currently linked only with Issue date but later I want to include Submit Date and Paid Up Date should I now create another Period folder coming from a view from my RDBMS then name this Period table say Submit Period or Paid Up Period?

For example:

-Issue Period
-Submit Period
-Paid Up Period
-Agent
-Agency
-Agent Balances
-Agent Income
-Agent Balances
-Policies
- Issue Date
- Submit Date
- Paid Up Date
- Policy Number

I'd also like the dates in the Policies folder to be hidden so that user is not tempted to use them.

Was this clear?

 

[DoubleD]

I would suggest one of two structures:
In both structures I would suggest you move the Date fields into their coordinating folder.

Option 1:
-Policy Dates
-Issue Period
-Submit Period
-Paid Up Period
-Agent
-Agency
-Agent Balances
-Agent Income
-Agent Balances
-Policies
- Policy Number

Option 2:
Option 1:
-Agent
-Agency
-Agent Balances
-Agent Income
-Agent Balances
-Policies
-Policy Number
-Policy Dates
-Issue Period
-Submit Period
-Paid Up Period


I am what I am based on the decisions I have made.

DoubleD