Add Time Hierarchy in Project Server 2007 Cubes
Many organizations have their own definitions of time. Project Server standard time dimension may not fit their needs. Don't worry, we can extend the cube by adding more time hierarchies.
Another good thing in project server 2005 is that we can use event and managed code. We can write code on OnCubeProcessed event. This event occurs after project server processed OLAP cube. Since we are creating time hierarchy, we need to wait project server finishing creating time dimension.
Here, I am using Analysis Management Object API for Analysis Service 2005, which is similiar but a little different from old way. The following sample code is to create another time hierarchy that only have year, week and day. So that week will not split between months.
public override void OnCubeProcessed(PSContextInfo contextInfo, CubeAdminPostCubeProcessEventArgs e)
{
try
{
WriteMessage("Start Cube Extension");
// connect analysis service server
Server OLAPServer = new Server();
OLAPServer.Connect("Data Source=" + e.ServerName + ";Provider=msolap;");
WriteMessage("Analysis service server connected:" + e.ServerName);
// open EPM OLAP database
Database EPMOLAP = OLAPServer.Databases.FindByName(e.DbName);
if (EPMOLAP == null)
{
EventLog.WriteEntry(LogSource, "Database: " + e.DbName + " does not exist in Project Server 2007 cube.", EventLogEntryType.Error);
return;
}
WriteMessage("Start building new Time hirarchy");
// open Time dimension
Dimension dmnTime = EPMOLAP.Dimensions[TimeDimensionID];
if (dmnTime == null)
{
EventLog.WriteEntry(LogSource, "Time dimension does not exist.", EventLogEntryType.Error);
return;
}
// check if the custom hierarchy is already created
if (dmnTime.Hierarchies.FindByName(NewTimeDimensionName) != null)
{
EventLog.WriteEntry(LogSource, "New Time hierarchy has already been created by other process.", EventLogEntryType.Error);
return;
}
// create custom hierarchy in time dimension
Hierarchy NewTime = dmnTime.Hierarchies.Add(NewTimeDimensionName);
// find year, week, and day attribute in time dimension
DimensionAttribute atrYear = dmnTime.Attributes.FindByName(YearAttributeName);
if (atrYear == null)
{
EventLog.WriteEntry(LogSource, "Year attribute does not exist in Time dimension.", EventLogEntryType.Error);
return;
}
DimensionAttribute atrWeek = dmnTime.Attributes.FindByName(WeekAttributeName);
if (atrWeek == null)
{
EventLog.WriteEntry(LogSource, "Week attribute does not exist in Time dimension.", EventLogEntryType.Error);
return;
}
DimensionAttribute atrDay = dmnTime.Attributes.FindByName(DayAttributeName);
if (atrDay == null)
{
EventLog.WriteEntry(LogSource, "Day attribute does not exist in Time dimension.", EventLogEntryType.Error);
return;
}
// create level for year, week, and day in custom hierarchy
Level lvlYear = NewTime.Levels.Add(YearLevelName);
lvlYear.SourceAttributeID = atrYear.ID;
Level lvlWeek = NewTime.Levels.Add(WeekLevelName);
lvlWeek.SourceAttributeID = atrWeek.ID;
Level lvlDay = NewTime.Levels.Add(DayLevelName);
lvlDay.SourceAttributeID = atrDay.ID;
WriteMessage("Complete building hirarchy");
// save the changes to database.
dmnTime.Update();
WriteMessage("Complete Cube Extension");
}
catch (Exception ex)
{
EventLog.WriteEntry(LogSource, ex.ToString(), EventLogEntryType.Error);
}
}
