adding a new dimension

J

Jesse Vaught

Jarek's post and David's response on 9/5 got me started, but I need some
help adding a dimension to the OLAP cube.

The analyzer view I want would have Actual Work shown on a timescale by
project then by task. I can get all of this except the by-task info. So, I
think I need to add the TASK_NAME field from MSP_WEB_ASSIGNMENTS table to
the pivot table list.
I've been able to add the dimension in SQL directly so that it shows up in
the list and I can drag it into my pivot table, but I've not been able to
figure out how to link it correctly so the data is there (all actual work
shows zero, but if I remove the dimension, actual work shows up).

I think where I've gone wrong is that I used the MSP_WEB_ASSIGNMENTS table
directly instead of getting the field into an MSP_CUBE table.
This is where the article on extending the cube
http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/pdr/PDR_Overview_3338.asp
should help me, but I cannot figure out what I need to do.

The article talks about adding brand new fields, but does anyone know how to
adapt these instructions for adding an existing field?
I also looked at the code for MSOLAPBREAKOUT but it seems to be written
specifically for the example in the article and I do not know what needs to
be edited for my needs.
Please help!
Jesse

If what I am asking requires a complex answer, please refer me to the right
consultation service.
 
J

Jesse Vaught

A simpler question might be :):
Has anyone written (or know where I can get) the custom vb code necessary to
add task-level information to the cube?
Thanks,
Jesse
 
L

Leo

I used this to group each tasks detail with it's task summary.
Example: All Project Management tasks detail should grouped into Project
Management task summary, all Design tasks details should grouped into Design
task summary, etc..
Try this code, copy-paste into enterprise global template at Visual Basic
Editor.
I missed the URL for this macro at techrepublic.com , but you can try this
proven steps :) .

The steps are:
- login into ms project server from ms project pro 2002
- open enterprise global (Tools | Enterprise Options | Open Enterprise
Global
- go to Tools | Macro | Visual Basic Editor (Alt + F11)
- copy paste the code below
- rename field Enterprise Task Outline Code1 and Enterprise Resource Outline
code become new one, i.e: "Task Summary"
- create your new lookup table that you want
- share Enterprise Resource Outline Code lookup table with Enterprise Task
Outline Code1 lookup table
- save and close your ms project pro completely
- open your enterprise project
- choose view : Taks Usage
- insert new new column (enterprise resource outline code1)
- link each resource with this new lookup table
- update your OLAP Cube again,
- after OLAP cube updated, there should 2 additional dimension in the field
list: Assignment Task Summary and Resource Task Summary
- pls let me know this status after you already tried my steps above.
hope this help you.

Sub Transfer_Task_Codes()
Dim tskT As Task

On Error GoTo ErrorHandler

If Application.Projects.Count > 0 Then
If ActiveProject.Tasks.Count > 0 Then
For Each tskT In ActiveProject.Tasks
If Not (tskT Is Nothing) Then
nbItems = tskT.Assignments.Count
If nbItems > 0 Then
For i = 1 To nbItems
tskT.Assignments.Item(i).EnterpriseResourceOutlineCode1 =
tskT.EnterpriseOutlineCode1
Next i
End If
End If
Next tskT
End If
End If

Exit Sub
ErrorHandler:
MsgBox prompt:=Err.Description & Chr(13) & "In Task: " & tskT.ID &
Chr(13) & "In Assignment: " & tskT.Assignments.Item(i).UniqueID,
Buttons:=vbCritical, Title:="Code Transfer Error"
Resume Next
End Sub


Private Sub Project_BeforeClose(ByVal pj As Project)
Transfer_Task_Codes
End Sub
 
J

Jesse Vaught

Leo,
Thank you so much for the response. This may help me - I've played with it
a little bit and have a few questions first.
1) What does this code do (please forgive my vb ignorance)?
2) When should it be run? I ran it just after pasting it into the global. I
am wondering if it should be run in the project after I've given all the
resources a value in the task usage view.
3) What I'm really looking for is work data by task name. It seems like
this method will give me work data by task type (whatever types I put into
the EROC1 lookup table). Also, it seems like I could get this without the
vb code, so again I wonder what does the code do?

Thank you for your help.

If anyone else is monitoring this, I am still looking for code or guidance
on adding a non-outline code OLAP dimension (specifically, task name).

Jesse
 
L

Leo

1). this code only to transfer the task outline code into resource outline
code
2). it will run after you assigned each task/ resource and re-publish it.
3) the OLAP dimension did not designed to show the task information, but you
can use a trick by copy it from resource outline code to task level..

Let me know again the status..
I really glad if you can achieve your objective...
 
J

Jesse Vaught

Wouldn't it be faster to copy from the task level to the resource level?
Can your code be rewritten (tskT.EnterpriseOutlineCode1 =
tskT.Assignments.Item(i).EnterpriseResourceOutlineCode1) to reverse this?

By the way, I still don't see task names the pivot table, just the
categories, Design, Project Management, etc.

Thanks for your help,
Jesse
 
L

Leo

This solution only to show up your summary tasks, if you want to show up the
tasks in the pivot table: you have to define more detail in your lookup
table where the lookup table values should have the same as your task name.
I think if you want to like that, there will be performance issue.
 
Top