Issue With Year dimension

[HumayunDC]

Hi,

I am trying to create a dimension named as YEAR with year,quarter,period and weeks as levels in dynamic cubes.The issue i am facing with it is not displaying the levels correctly. It displays the first year value with all the levels and rest of the years levels are not displayed.I have attached the screenshot of the issue i am facing.

Please let me know if you need any more information on this.

Regards,
Humayun

[bus_pass_man]

My first guess based on what I'm seeing is that your level keys are not sufficient to uniquely identify the quarters.  That should be detected at cube start-up.

1. What are you using to define the keys for your levels?

2. Is the data defining each member unique?

Select the columns being used to define the keys and choose view data and attach the result.

3. What are you using to define the level unique key?

4.  Do you see any messages in the xqe log file with a time stamp from when the cube started up?

5. Do you happen to have a fairly old version of dynamic cubes?  The first release didn't do quite as good a job of detecting of non-unique levels during cube start-up as later releases.  For example, the retailer name level in the sample model's retailer dimension wasn't defined with a sufficiently unique key ( as retailer name is duplicated and you need retailer country's key to uniquely identify each retailer as a retailer (Falcon outfitters) exists in both Canada and the United States) but that didn't prevent the cube from starting up.

6. Regular dimension I assume and not a parent-child dimension.  Is that a correct understanding?

[HumayunDC]

Thanks a lot for your reply.Please find the below responses.
1. What are you using to define the keys for your levels?

We don't have seperate keys to be used for levels.I am using the same attribute as member caption and level unique key.

2. Is the data defining each member unique?

Select the columns being used to define the keys and choose view data and attach the result.

Attached the data.

3. What are you using to define the level unique key?

I am using the same attribute as member caption and level unique key.

4.  Do you see any messages in the xqe log file with a time stamp from when the cube started up?

I Dont see any xqe log files during the cube start up.

5. Do you happen to have a fairly old version of dynamic cubes?  The first release didn't do quite as good a job of detecting of non-unique levels during cube start-up as later releases.  For example, the retailer name level in the sample model's retailer dimension wasn't defined with a sufficiently unique key ( as retailer name is duplicated and you need retailer country's key to uniquely identify each retailer as a retailer (Falcon outfitters) exists in both Canada and the United States) but that didn't prevent the cube from starting up.
We are using cognos 10.2.2 FP1.
6. Regular dimension I assume and not a parent-child dimension.  Is that a correct understanding?
Yes,It is a regular Dimension

Regards,
Humayun

[MFGF]

Thanks a lot for your reply.Please find the below responses.
1. What are you using to define the keys for your levels?

We don't have seperate keys to be used for levels.I am using the same attribute as member caption and level unique key.

2. Is the data defining each member unique?

Select the columns being used to define the keys and choose view data and attach the result.

Attached the data.

3. What are you using to define the level unique key?

I am using the same attribute as member caption and level unique key.

4.  Do you see any messages in the xqe log file with a time stamp from when the cube started up?

I Dont see any xqe log files during the cube start up.

5. Do you happen to have a fairly old version of dynamic cubes?  The first release didn't do quite as good a job of detecting of non-unique levels during cube start-up as later releases.  For example, the retailer name level in the sample model's retailer dimension wasn't defined with a sufficiently unique key ( as retailer name is duplicated and you need retailer country's key to uniquely identify each retailer as a retailer (Falcon outfitters) exists in both Canada and the United States) but that didn't prevent the cube from starting up.
We are using cognos 10.2.2 FP1.
6. Regular dimension I assume and not a parent-child dimension.  Is that a correct understanding?
Yes,It is a regular Dimension

Regards,
Humayun

So from what I can see in your attachment, you don't have unique quarter IDs, unique month IDs or unique week IDs.

In your hierarchy each member must have a unique ID. If you assign an ID of 1 to the Q1 member belonging to 2013, you cannot also assign an ID of 1 to the Q1 members belonging to 2011, 2012 or 2014. To fix this you should concatenate the year and quarter to make each quarter ID unique eg YEAR_FISCAL || 'Q' || QTR_FISCAL would give you IDs that look like 2014Q1. You'd need to take a similar approach with month IDs and week IDs too.

Cheers!

MF.

[HumayunDC]

Hi MF,

Thanks a lot for your Help.This worked like a charm :).

Regards,
Humayun

[bus_pass_man]

1.  The null question.
It isn't necessarily a good idea to use a column with nulls as part of your key.  A null isn't a very useful thing to use to identify an entity.

In addition, it isn't necessarily a good idea to use a column with nulls in an expression.  This can cause the result set to exclude any records where there were nulls.

You might have lucked out but you ought not to count on luck.

Just as an observation, if that's the state of your time dimension table after your ETL how can you trust the ETL in more subtle cases?

2.  Composite keys

The expression-based composite key trick isn't necessary in cube designer.  You can use the level unique key editor in the level editor to bring in references to higher level keys into the level key.

3.  XQE log file.

The xqe log file is located in the ..logs\xqe directory of your portal.  It's easy to find; its big and its name starts with xqelog.   

You may find that during development having fairly verbose logging levels in the xqe.diagnosticlogging.xml is useful to see what happens 'cause things can happen and if you're not looking out for them you might miss them, such as members not being generated and silently (except for a message in the log file) omitted.

This document could be useful. 

https://www.ibm.com/developerworks/library/ba-pp-infrastructure-cognos_specific-page655/