Time dimension as a snapshot

[CyberFan]

We use Cognos cubes for a number of applications. One of them has proven quite difficult to implement because we need the time dimension to treat data differently than the Cognos provided implementation.

This model doesn't care about "daily events" like sales, revenue, expenses. It does care about "number of items in a particular state". The problem lies in the automatic rollup done when drilling from day granularity to month, month to quarter, quarter to year, etc.

We're an insurance company. The executives need to see reports that show the number of policies in effect at any point in time and compare with with other points. First/Last of the month is a popular choice.

Our star schema records the data this way. A simple SQL query can show the change in the number of policies between any two dates. Translating this data into cubes is tough. PowerPlay insists that monthly counts are really the sum of the daily counts. In fact, monthly counts are simply snapshots of one day in the month.

To work around this, we've defined days, months, quarters, and years as separate (and mutually exclusive) dimensions. This is a bit confusing to the user base, but it does get us past the automatic rollup issue.

Is there a better way? I'd certainly like the look and feel of the cube to be consistent with other cubes.


Thanks
CyberFan

 

[Draoued]

why don't you defined your measures to be First or Last for the Period ,and get the sum for the none time dimensions.

Open the Measure Propertie , Roll-Up Tab , then in Time State rollup select First Period or Last Period.

 

[yksvaan]

Exactly, then (in case of Last month in Transformer) at month level you see the value of the last day of that month and at quarter level you see the value of the last month in that quarter.

But, you can't use this feature if you have multiple time dimensions, you must only have one, otherwise cube build won't start ! But that is ok in your case.

Draoued, what did you mean by "and get the sum for the none time dimensions" ?

Cheers

 

[Draoued]

Let say , you have a Sales Manager dimension.
First Level = Country
second level = Sales Manager name

The cube will still aggregate the measure in this Dimension.
So when looking at Country level for a specific day, you have the sum of contracts for all sales manager in this country for this day

If you are looking at month level, you get the sum of contracts for the last period of the month in this country.

Country Sales Man Day Contract
UK Mr X 20.1.04 10
UK Mr X 31.1.04 10
UK Mr Y 31.1.04 10


Then in the cube at UK level for January you have 20 contracts.


Is that Clear ??

 

[yksvaan]

Ok Draoued, sorry, I just didn't get it at first that you ment with the phrase "sum for the none time dimensions" actually sum for the none time dimensions , alias regular dimensions

One very important thing. If you have level day in the time hierarcy under month level, Time state rollup Last period will rollup the last day of that calendar month, not the last day you bring data for. Meaning you must always have data for the last calendar day of the month.
Or, at least, all the "last" data has to be on the same date, and, on that level definition's Time tab "Generate all categories in the period" must be unchecked. For the month and quarter level, this usually should be unchecked, if Last period measures are used. If not you won't see the current year's numbers.

Cheers

 

[CyberFan]

>>why don't you defined your measures to be First or Last for the Period, and get the sum for the none time dimensions.


The measure isn't the first day or last day of the month. In our environment, critical time periods begin/end with the onset of hurricane season, a hurricane being name, making landfall, etc. I need daily counts.

Which, circularly, gets me back to the problem. Time State rollups still roll up.

Kent

 

[yksvaan]

Should know more about the situation. Can you give us an example?

Simplified scenario: Let's say you have only one time dimension and only day level in the whole time dimension so there's no rollups involved. Now user can see measure value at which ever days user wants. What's next?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[CyberFan]

I have several years worth of data in my Star Schema. The data is daily snapshots.

What I'm hoping to do, is deliver a cube to my user base that allows them to use the COGNOS provided Time dimension (or a similar feel) to generate their reports.

User A wants a report that shows the business measures on the dates when major hurricanes struck Florida. (No problem here.)

User B wants a report that shows 2004 trending data by showing the same measures on the first of the month.

User C wants a report that shows 3 years trending data by showing the same measures on the last day of each quarter.

Without using Year, Quarter/Yr, Month/Yr, and Day/Yr as separate (mutually exclusive) dimensions, I'm stumped.

 

[yksvaan]

I don't understand what's that trending data. But anyway..

Let's say you bring in a datasource Number of policies on each day, so a single fact. One thing, you must not have a datasource where you have precalculated month/quarter/month-wise numbers.

In thinking about cases B and C, you have conceptually two different measurement of a single fact: No of policies in the First period(or in the beginning of period), and No of policies in the Last period, Time State Rollups accordingly. So you must have two measures in the cube, for the day level, measures will have same values.

In B, looking at the year 2004's months, first measure handles that and in C user can look at quarters on those 3 years using the second measure.

Might be a case D, where user wants to view number of policies in the beginning of month and in the end of month, so would have to have two measures to accomplish that. And maybe a third measure, which is calculated, that has the change from beginning to the end of a period (this is a different thing from having change calculated in special time categories).

If a user wants to calculate differences between any points in time, which can't be defined in time dimension with Relative Time definitions, then the user must add calculations browsing the cube.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]