Time Dimension and OLAp Cube Studio

[deleted_user]

Hello All,
I feel stumped with this issue. I have a Date variable called Calendar_date with the format datetime20. I am trying to create a Level for a Time Dimension using the OLAP Cube Studio 4.2. I can only see the Calendar_date in the drop down list, Even though I have another variable called sas_incident_date with format date8. If I choose Calendar_Date and complete building the Cube, and try to view the cube data, For the time dimension, I see **** instead of the actual level (Year/Month/Day). I have not been able to figure out what I am doing wrong can some one help. Here is the code for the time dimension

DIMENSION Incident_Date
CAPTION = 'Incident_Date'
TYPE = TIME
SORT_ORDER = ASCENDING
HIERARCHIES = (
Incident_Date
) /* HIERARCHIES */;

HIERARCHY Incident_Date
ALL_MEMBER = 'All Incident_Date'
CAPTION = 'Incident_Date'
LEVELS = (
Year Month Day
) /* LEVELS */
DEFAULT;

LEVEL Year
COLUMN = CALENDAR_DATE
FORMAT = YEAR4.
TYPE = YEAR
CAPTION = 'Year'
SORT_ORDER = ASCENDING;

LEVEL Month
COLUMN = CALENDAR_DATE
FORMAT = MONNAME9.
TYPE = MONTHS
CAPTION = 'Month'
SORT_ORDER = ASCENDING;

LEVEL Day
COLUMN = CALENDAR_DATE
FORMAT = DOWNAME9.
TYPE = DAYS
CAPTION = 'Day'
SORT_ORDER = ASCENDING;


Thanks for all your suggestions.

Shri

[AngelaHall]

Shri,
The reason this is occuring is the mix of format types. In the source data, you have a datetime value, but in the year/month/day values you reference date formats.

If you mix the two in base SAS you also get *** values.

You can either utilize a new date variable in your source table
-> which could be newdate=datepart(calendar_date);

or you could change the year/month/day values to use other datetime formats.
Such as dtmonyy. for Month and dtyear. for Year.

Reference Material on all the Date Formats Can be Found at:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a001263753.htm

~ Angela
http://sas-bi.blogspot.com

[deleted_user]

> Shri,
> The reason this is occuring is the mix of format
> types. In the source data, you have a datetime value,
> but in the year/month/day values you reference date
> formats.
>
> If you mix the two in base SAS you also get ***
> values.
>
> You can either utilize a new date variable in your
> source table
> -> which could be newdate=datepart(calendar_date);
>
> or you could change the year/month/day values to use
> other datetime formats.
> Such as dtmonyy. for Month and dtyear. for Year.
>
> Reference Material on all the Date Formats Can be
> Found at:
> http://support.sas.com/documentation/cdl/en/lrdict/626
> 18/HTML/default/a001263753.htm
>
> ~ Angela
> http://sas-bi.blogspot.com

Angela,

Thank you for your message. Here are a couple of things,
In the levels, I did not enter the format, those formats were listed by default and I was trying to go with the default and it did not work.

I did realize that it is a datetime variable and I did create another variable in my dataset just like you said with just the datepart, but I do not see that variable in the drop down while creating the levels. It looks like a bug to me.

I'll try and change the format like you said and will let you know if it works

Thanks
Shri

[AngelaHall]

Shri,
Just to verify ~ when you created the new variable with datepart() did you register that column in the metadata or update the metadata for the entire table?

In the Dimension Designer screen - there are formats in the dropdown list - but you can also manually type the format you would like to use for each level.

~ Angela Hall
http://sas-bi.blogspot.com

[deleted_user]

Angela,
You are a genius, I had not updated the meta data for the column after I updated the table, that's why it was not showing up. Once I updated the metadata the field is showing up.

Thanks
Shri

[deleted_user]

Angela,
I have one more question on the Time Dimension. When I use the level provided by SAS, the time is broken down to Year/Quarter/Month (this is the combination I used) depending on the calendar year (jan1 - Dec 31). Is there a way to customize the year(our year is Aug 25 to June 10 - School year) and our quarters are different too. I have built a cube on attendance and I know the first thing that will be asked is if they can see the data by month/quarters/years but in terms of the school calendar not the regular calendar. Can you suggest any way of doing this. I have built a data set which gives me the start date and end date for the quarter and the month but I don't know how to use that to define the criteria for the level.

Thanks
Shri

[AngelaHall]

Hi Shri,
As mentioned within the OLAP doc(http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/a002254427.htm) you can have two Time Hierarchies - one for 'Fiscal' and one for 'Calendar'. 'Fiscal' for business is the same terminology as 'School' for your scenario. I would suggest having two data elements each, a school year and calendar year, a school month & calendar month, a school quarter and calendar quarter.

You could set these 6 columns up in a separate table and join only at OLAP run time, or simply add them into the base source table for the OLAP Cube.

~ Angela Hall
http://sas-bi.blogspot.com