06 December
Extend the OLAP cube for week in Time dimension
By default, the OLAP cube of project server does not have week in time dimension. However, in real world, week is important. To add week into time dimension, we need to extend the standard OLAP cube building process.
In fact, the MSP_CUBE_TIME_BY_DAY table has already had week number in it. We can use it to create a new level in time dimension. However, to have more friendly interface, you may create additional column in MSP_CUBE_TIME_BY_DAY table as the display of the week level.
To extend the week, you need to do the following:
1. For all physical cube, delete time dimension if used and remember all the cube name.
2. For all virtual cube, delete time dimension if used and remember all the cube name.
3. Delete the time dimension
4. Create a new time dimension and create six levels of it.
5. Add the time dimension into the physical cube of step 1.
6. Add the time dimension into the virtual cube of step 2.
There is one thing you may need to know. When you remove or add a dimension from a physical cube, you need to reconstruct the join clause.
Here is the source code in VB6 for SQLServer 2000 database. If your database is SQLServer 2005, it may be a little different.
' Modify the time dimension to insert week of month.
Public Function ModifyTimeDimension(dsoDatabase As DSO.MDStore)
Dim dsoDimension As DSO.Dimension
Dim dsoLevelAll As DSO.DbLevel
Dim dsoLevel As DSO.Level
Dim strDimensionName As String
Dim strDimensionTableName As String
Dim dsoCube As DSO.MDStore
Dim DicCubes As Dictionary
Dim fSuccess As Boolean
Dim jcJoin As JoinClauseManager
Dim Condition As JoinClause
Dim nPos As Integer
fSuccess = False
On Error GoTo LblErrorHandler
strDimensionName = "Time"
strDimensionTableName = "MSP_CUBE_TIME_BY_DAY"
Set DicCubes = New Dictionary
Set jcJoin = New JoinClauseManager
If dsoDatabase.Dimensions.Find(strDimensionName) Then
For nPos = 1 To dsoDatabase.MDStores.Count
Set dsoCube = dsoDatabase.MDStores(nPos)
If dsoCube.Dimensions.Find(strDimensionName) And dsoCube.SubClassType = sbclsRegular Then
Call DicCubes.Add(dsoCube.Name, dsoCube.Name)
dsoCube.Dimensions.Remove (strDimensionName)
Call jcJoin.LoadJoinClause(dsoCube.JoinClause)
Call jcJoin.RemoveJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_ID", strDimensionTableName, "TIME_ID")
Call jcJoin.RemoveJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_DATE", strDimensionTableName, "TIME_DATE")
dsoCube.JoinClause = jcJoin.GetJoinClause
dsoCube.Update
dsoCube.Process processFull
End If
Next nPos
For nPos = 1 To dsoDatabase.MDStores.Count
Set dsoCube = dsoDatabase.MDStores(nPos)
If dsoCube.Dimensions.Find(strDimensionName) And dsoCube.SubClassType = sbclsVirtual Then
Call DicCubes.Add(dsoCube.Name, dsoCube.Name)
dsoCube.Dimensions.Remove (strDimensionName)
dsoCube.Update
dsoCube.Process processFull
End If
Next nPos
dsoDatabase.Dimensions.Remove (strDimensionName)
End If
Set dsoDimension = dsoDatabase.Dimensions.AddNew(strDimensionName, sbclsRegular)
Set dsoDimension.DataSource = dsoDatabase.DataSources(1)
dsoDimension.FromClause = """" & strDimensionTableName & """"
Set dsoLevelAll = dsoDimension.Parent.Parent.CreateObject(clsDatabaseLevel)
dsoLevelAll.Name = "All"
dsoLevelAll.IsAll = True
If dsoDimension.Levels.Count > 0 Then
Call dsoDimension.Levels.Add(dsoLevelAll, , 1)
Else
Call dsoDimension.Levels.Add(dsoLevelAll)
End If
dsoLevelAll.MemberKeyColumn = "All Years"
dsoLevelAll.LevelType = levAll
Set dsoLevel = dsoDimension.Levels.AddNew("Year", sbclsRegular)
dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_YEAR"""
dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_YEAR"""
dsoLevel.LevelType = levTimeYears
dsoLevel.columnType = adSmallInt
Set dsoLevel = dsoDimension.Levels.AddNew("Quarter", sbclsRegular)
dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_QTR"""
dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_QTR"""
dsoLevel.LevelType = levTimeQuarters
dsoLevel.columnType = adWChar
Set dsoLevel = dsoDimension.Levels.AddNew("Month", sbclsRegular)
dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_MONTH_OF_YEAR"""
dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_MONTH"""
dsoLevel.LevelType = levTimeMonths
dsoLevel.columnType = adSmallInt
dsoLevel.Ordering = orderKey
Set dsoLevel = dsoDimension.Levels.AddNew("Week", sbclsRegular)
dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_WEEK_OF_YEAR"""
dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_WEEK"""
dsoLevel.LevelType = levTimeWeeks
dsoLevel.columnType = adSmallInt
dsoLevel.Ordering = orderKey
Set dsoLevel = dsoDimension.Levels.AddNew("Days", sbclsRegular)
dsoLevel.MemberKeyColumn = """" & strDimensionTableName & """.""" & "TIME_DAY_OF_WEEK"""
dsoLevel.MemberNameColumn = """" & strDimensionTableName & """.""" & "TIME_DAY_OF_MONTH"""
dsoLevel.LevelType = levTimeDays
dsoLevel.columnType = adSmallInt
dsoLevel.Ordering = orderKey
'Update and process the dimension.
dsoDimension.Update
dsoDimension.Process (processFull)
For Each dsoCube In dsoDatabase.MDStores
If DicCubes.Exists(dsoCube.Name) And dsoCube.SubClassType = sbclsRegular Then
dsoCube.Dimensions.AddNew (strDimensionName)
Call jcJoin.LoadJoinClause(dsoCube.JoinClause)
Call jcJoin.AddJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_ID", strDimensionTableName, "TIME_ID")
Call jcJoin.AddJoinCondition(TrimQuate(dsoCube.SourceTable), "TIME_DATE", strDimensionTableName, "TIME_DATE")
dsoCube.JoinClause = jcJoin.GetJoinClause
dsoCube.Update
dsoCube.Process (processFull)
End If
Next dsoCube
For Each dsoCube In dsoDatabase.MDStores
If DicCubes.Exists(dsoCube.Name) And dsoCube.SubClassType = sbclsVirtual Then
dsoCube.Dimensions.AddNew (strDimensionName)
dsoCube.Update
dsoCube.Process (processFull)
End If
Next dsoCube
fSuccess = True
LblErrorHandler:
Set dsoDimension = Nothing
Set dsoLevelAll = Nothing
Set dsoLevel = Nothing
Set dsoCube = Nothing
If (Not fSuccess) Then
Call TraceAndRaise("Error in ModifyTimeDimension.")
End If
End Function
Tools 
Help
