Problem Month Granularity

Hello,

I suppose that my problem is something basic but it's the first time i'm confronted with this.

I want to link a measure group to a time dimension on a Month granularity.

My time dimension is a server time dimension, the inventory data comes on a monthly base, but with a normal datetime field (always the last day of the month). Linking this on a date granularity is no problem, but has no sense. When i set the granularity to Month i'm getting this error.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: ASW_Monthly_Stock, Column: ASW_STOCK_DATE, Value: 31/08/2006. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Month of Dimension: Calendar Time from Database: SdwCubes, Cube: c_Sales, Measure Group: ASW Monthly Stock, Partition: ASW Monthly Stock, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'ASW Monthly Stock' partition of the 'ASW Monthly Stock' measure group for the 'c_Sales' cube from the SdwCubes database.

I've seen that when setting the granularity in the Define Relationship window i'm getting a warning 'If you Select a non-key ....', but when i go into the 'Advanced...' button i can link more attributes.

But what function do i have to put on my ASW_STOCK_DATE to link it to the Month attribute of the time dimension ? The T-Sql function Month only returns the Month-number, and not the year ...

So ... if anyone could get me a bit furter on this ... greatly appreciated.

Please check that releationship between measure group and dimension columns is correct, most likely you still have days as a measure group column. To do this go to the Dimension Usage Tab, click on ... near the time dimension name, you'll get window "define relationship".|||

Hi,

Does your table AS_Monthly_Stock stock, store data for the last day of the month.

It seems like the server time dimension has the "month key" as the first day of the month. This can be the cause of your issue.

The easiet option is to change your fact table to record data on the first day of the month.

CAST(CAST(year(getdate())asvarchar)+'-'+CAST(month(getdate())asvarchar)+'-'+'01'asdatetime)

Punita