Getting a specific month as a category in Transformer

[jaybuffet]

I have an effective date column in my data source that contains month only information, ie 1/1/200, 2/1/200..etc.

Well I want to be able to compare all the decembers in a view. 2001/Dec, 2002/Dec.. etc

Is this possible? This needs to be published to the web also.

Thanks
Jason

 

[CanadianTechie]

This is indeed not a problem -- however the Cognos Documentation (nor Education) do not cover in any detail the concepts of semi-additive dimensions or snapshot type data (e.g. Monthly Inventory Snapshot data, Daily Backorder Inforation, etc). In a nutshell, this type of dimensional data is not additive (thus the semi-additive moniker) across all dimensions (in this case specifically not across the time dimension). Take a look at Time State Rollups (in your case probably Last Period) and just make sure the lowest level in your time dimension is month and you should be OK...if you search the Transformer documentation for time status rollups, you will see an inventory example which should help you out. Let me know if you have trouble finding it.

When I get a chance, I'm going to put out an FAQ in time state rollups (maybe even external rollups)...but as a "newbie" to the world of Cognos, I've still got a lot to learn/master and won't get around to it for at least a month...

Good Luck,


Pedro Cardoso
A.K.A CanadianTechie

 

[griffindm]

Pedro,

We could certainly use a good PowerPlay FAQ. Anything you put together would be appreciated by the users here.

Thanks for all your contributions since joining the forum.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[jaybuffet]

I'm not sure if that helps.... Lets say we want to compare the Inventory from last month to that of Last December, or the December before that.. I need all the columns to display on the cube..

And the month is December. Don't ask me why.. the client uses that as a benchmark month or something.

The time state rollup is only for measures isn't it. I think this has to be either a dimension or a custom category created in the Diagram window

Thanks for any help

 

[CanadianTechie]

Jay,

You are correct -- time state rollup is for measures -- specifically, the measure in question right now is Inventory Quantity and what you want to do is make that measure a time state rollup SO THAT when you do analyze across the time dimension (and let's say your lowest level in the time dimension is MONTH, based on your comments) -- what you do NOT want powerplay to do, assuming you are capturing daily snapshots of inventory, is to SUM up (which is default behaviour for a measure) the inventories for all the days within that month, when you are analyzing it BY MONTH...enter the time state rollup.

If you are just capturing monthly snapshots anyway, and the lowest level in your time dimension is month, then time state rollup is a non-issue or requirement -- you should be able to perform the analysis you describe -- you may have to create some additional time dimensions to represent the period 12 months prior in order to get your now vs same time last year comparison....

Hope this makes sense....? Let me know...


Pedro Cardoso
A.K.A CanadianTechie

 

[goldsmt]

In transformer show the diagram for the Time dimension.

Create a new special category. Call it all Decembers

Manually drag all the decembers into this category

Use this category in the reports

Works for me unless I've not correctly understood the problem, quite possible on a friday afternoon

Regards

Tony Goldsmith
tony.goldsmith@frontline-consultancy.co.uk

 

[flex13]

If Tony's suggestion of special categories don't work for you, how about using relative time categories?

Scott

 

[jaybuffet]

Well dragging the months would work. But I was looking for a more dynamic method. So when the next year or the year following comes around it would automatically build the new category instead of having to add the december on the diagram

 

[CanadianTechie]

Jay,

If December is that important to you, and if you have the flexibility in the source/db (or do it in Impromptu), create a calculated column that just includes December sales and you can use that column as a specific measure or as a special dimension....



Pedro Cardoso
A.K.A CanadianTechie

 

[sparked]

If your looking for some cleaver dynamic method of creating an ad hoc grouping of months in a time dimension, your probably out of luck.

Most of the dynamic stuff tends to be based around the concept of a relative time period, i.e. something that is x periods different to the current month. (e.g. Current month -1, YTD, YTD -1 etc.)

Creating 'every december' as a manual category within the time dimension gets my vote. Not ideal, but you should only need to maintain it once a year.

 

[CanadianTechie]

Sparked,

The easy answer is to create a calculated column (as I indicated) in Impromptu which is 0 unless the month is December in which case it is the actual column value. It is true that (to my knowledge thus far) this cannot be achieved within the Transformer environment since relative time categories are just that...relative, and not absolute...but this method basically gives you the desired functionality (I believe, if I've interpreted it correctly) of this "dynamic" requirement....

Pedro Cardoso
A.K.A CanadianTechie

 

[sparked]

Canadian Techie,
What users invariably want in a cube is all measures avalable by all dimensions, otherwise they often get all confused.

Any solution that only picks out certain figures in certain periods, will only work if:

You create specific measures that are associated with them.

or you create a new dimension in the model, that allows you to pick out special subsets. i.e. an aditional time dimension of some sort.

In reality, this loses people who may actually use the application every once in a while as they can easily forget that this measure works in this way, and that one works in the other or that you need to look in two places every once in a while to pick out certain time periods.

Which is why to avoid all that hassle, training, repeated explanation, convaluted thinking, brain ache etc etc, Manually creating a time category offers the user the most flexible and least complicated solution.

And its a five minute job...... ;-)

 

[jaybuffet]

Ok.. I think manual creation gets my vote.. thanks all

 

[CanadianTechie]

Sparked,

I agree with most of your points sir -- I always like to limit any required manual maintenance (if at all possible). After some further deliberation and musing, allow me to modify then my previous suggestion by instead, in this specific example, assuming that December might not be the only month that needs to be considered in this fashion, creating a calculated column called MONTH, which would evaluate to the textual representation for the month (i.e. January, February, etc), and using that field as the basis for a MONTH dimension within the cube would be the way that I "personally" would likely recommend in most installs...that way no ongoing maintenance is required and months other than just December are available for reporting/analysis....

Just my .02....

Pedro Cardoso
A.K.A CanadianTechie

 

[sparked]

i.e. a second time dimension. Technically this is a good solution, however users (bless 'em) often struggle when you give them more than one time dimension.....

 

[CanadianTechie]

Sparked,

As a "newbie" to all things Cognos, I find myself at this point in time, commenting on many things that I haven't actually rolled out into production yet -- your point about users struggling with multiple time dimensions is likely quite valid, and learnt through experience...could we not also setup an alternate drill path in that time dimension, and put that MONTHS level in there...? Your thoughts are appreciated....

Regards,


Pedro Cardoso
A.K.A CanadianTechie

 

[BruceReed]

Maybe I missed it in the postings & if so please forgive me (I've not been around for a while...)

Use Impromptu to create a calculated MONTH (numeric or text, your choice) field. Only include month and not years. Then have this month field as a second date dimesion in PP to compare all Decembers, all Januarys etc.

I used this many times & I could compare both regular calendar dates, as well as month-to-month over the years.

Hope this helps
Bruce

 

[BruceReed]

Whopps an addendum to my previous thought

You can also make the calculated field in Transformer (6.5 or later), instead to Impromptu. It amounts the same thing, but one might be easier for you.

Thnaks
BR