none
Custom fields in olap cubes RRS feed

  • Question

  • Dear all,

    In my company we also use our work (hour) registration program to see how many hours are actually spent on a task. I have defined a custom field in which these hours can be entered. This field is called Ipower, has a entity of task and a type of duration. When i run the cube and load this data file in excel I can read the task type text files, but not the duration fields. I have found that this is the case, and i was trying to make an other field which converts this duration into a text, which i can get into the olap cube, but so far no succes. can anyone help me?

    Many thanks in advance,

    Bert

    Monday, August 9, 2010 2:40 PM

Answers

  • Hello Barbara,

     

    I will take your advice to heart and use the non-timephased cubes. they will do.

     

    We've been working with Project for some time now, but just recently started using Project server and sharepoint. We are making good progress but still a long way to go.

     

    Could you recommend some reading about how to make/understand macro?

    Again many thanks for your time and hard work.

     


    Kind regards, Bert
    • Marked as answer by Bert Wolff Tuesday, August 10, 2010 5:19 PM
    Tuesday, August 10, 2010 5:19 PM

All replies

  • Hello Bert,

    Did you add the custom field to you cube build?  Did you do a rebuild after adding?

    Cheers!

    Michael Wharton, MBA, PMP, MCT


    Sincerely, Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA www.WhartonComputer.com
    Monday, August 9, 2010 3:31 PM
    Moderator
  • Hello Michael,

     

    Thanks for your reply! Yes, I added the file to the cube build, but I cannot reproduce it when I make a data connection in Excel. It is not in the list anywhere and I checked in this and other forums and I thought custom fields with a text and duration characteristic cannot be reproduced in the Olap cube. I was looking for a work around, but since fields with formulas also are not included in OLAP cubes I got stuck.

     

    Hope you or anyone can help.

     

    Bert

    Tuesday, August 10, 2010 5:31 AM
  • Hi Bert,

    have you thought about using a macro to move the values of your duration field to a text field? It could be an event driven macro e.g. 'on save'.

    Regards
    Barbara

    Tuesday, August 10, 2010 6:07 AM
    Moderator
  • Hello Barbara,

    Yes i have thought/dreamt about it :-) but since I'm not capable of writing macro's I hoped to work around this. My next question on this forum would be, what would this macro look like? 

    Best regards,

    Bert

    Tuesday, August 10, 2010 6:23 AM
  • Hi Bert,

    do you fill IPower only on task level or is it defined as "Roll down, unless manually specified"?

    Regards
    Barbara

    Tuesday, August 10, 2010 6:31 AM
    Moderator
  • Hello Barbara,

     

    The Ipower field looks like this:

    Entity: Task

    Type: Duration

    Custom attributes: none

    Calc for sum rows: rollup (sum)

    Calc for ____ row: non

    values to disp : data

    required: no

    Regards,

    Bert

    Tuesday, August 10, 2010 6:38 AM
  • Hi Bert,

    this should work for you (IPowerText is an enterprise text field, please use a field name without spaces!):

    Private Sub Project_BeforeSave(ByVal pj As Project)
    Dim T As Task
    Dim A As Assignment

    For Each T In ActiveProject.Tasks
        If Not T Is Nothing Then
            T.Ipowertext = T.IPower / 480 'Duration is calculated in minutes, so you need to use 480 to get days
        End If 'nothing
    Next T
    End Sub

    As a first step, I suggest to test it only on one workstation:
    - Alt+F11 will open VB Editor
    - Verify that code window shows 'Global (+ non-cached Enterprise) - ThisProject (Code)'. If not go there using objkect browser: VBAProject (Global (+non-cached Enterprise))
    - Paste the piece of code in there.

    If you save a project using this client, the macro should run and fill IPowerText for you.

    After some time without issues, you can move this macro to your ETP.
    - Make an administrative back up of your Enterprise Global
    - Open Enterprise Global
    - Alt+F11 to open VB Editor
    - In object browser navigate to VBAProject (Checked-out Enterprise Global) - Microsoft Project Objects - ThisProject (Checked-out Enterprise Global)
    - Paste the code in there
    - Save Enterprise Global
    - Close and check-in Enterprise Global
    - Restart Project

    Hope that helps!
    Regards
    Barbara

    Tuesday, August 10, 2010 7:12 AM
    Moderator
  • Dear Barbara,

    I copied the following in the VB Editor, but it doesn't seem to work. nothing happens and when I copy it into the ThisProject (Global.mpt) I get a compile error: syntac error and the following line lights up: If Not T Is Nothing Then.

    Hope you can help.

     

    Thanks,

    Bert


    Kind regards, Bert
    Tuesday, August 10, 2010 8:06 AM
  • Hi Bert,

    I would like to help. But currently I can't see which step I missed in my description. So let's verify first if it is copied to the good place. It should look like this - as long you are testing on your client:

     Image and video hosting by TinyPic

    You can't start the macro manually. It starts if you save your project. Have you created your custom enterprise text field and replaced my "IPowertext" with your field name?

    Regards
    Barbara

     

    Tuesday, August 10, 2010 8:26 AM
    Moderator
  • Hi Barbara,

     

    I understand. I cannot paste screenshots in this window for some reason, but I have exactly the same. The only difference is that " End if ' nothing " is red in my view and green in yours.

    I've made a enterprise field in server settings --> custom fields and lookup tables and I named it Ipowertext. It has as an entity task, type text and the rest is none, dat and no.

     

    I opened a project, opened the VB editor, went to the above field, pasted the code, pressed the save button and than hit close and return to project.

     

    Hope this helps.

     


    Kind regards, Bert
    Tuesday, August 10, 2010 8:39 AM
  • Hi Bert,

    seems that something happened to line feeds. I have now deleted comments to fit it into this screen:

    Private Sub Project_BeforeSave(ByVal pj As Project)
    Dim T As Task
    Dim A As Assignment
    For Each T In ActiveProject.Tasks
        If Not T Is Nothing Then
            T.Ipowertext = T.IPower / 480
        End If
    Next T
    End Sub

    Does that work (in ThisProject (Global (+non-cached ...?
    Regards
    Barbara

    Tuesday, August 10, 2010 8:53 AM
    Moderator
  • Ok, let's try once more ;-) with typing:

    - Delete the first macro
    - Go to Code window for ThisProject (Global (+non-cached ...
    - Select Project in the left drop down box
    - Select BeforeClose in the right drop down
    - Delete Private Sub Project_Open(ByVal pj As Project) to the first End Sub
    - Paste the main part (see below) between Private Sub Project_BeforeSave and its End Sub:

    Dim T As Task
    Dim A As Assignment
    For Each T In ActiveProject.Tasks
        If Not T Is Nothing Then
            T.Ipowertext = T.IPower / 480
        End If
    Next T

    ???

    Regards
    Barbara

    Tuesday, August 10, 2010 8:57 AM
    Moderator
  • Hi Bert,

    I need a screenshot.... Can you please send to ...?

    Regards
    Barbara

    Tuesday, August 10, 2010 9:16 AM
    Moderator
  • Hello Barbara!

     

    SUCCES!! The Ipowertext fills up nice!! The only thing is now... how to get it in the OLAP cube? I cannot select this field in the configuration for the Cube. But I think i'm missing something...

     


    Kind regards, Bert
    Tuesday, August 10, 2010 10:03 AM
  • Ahhhhhhhhhh, I ignored your initial question - sorry!!!! I was so focused on your "...but since fields with formulas also are not included in OLAP cubes I got stuck....". You have a text field without look up table - you can't put into a cube. You will need to have a number or duration field for that.

    So I think you can delete the macro: IPower is a duration field without any formula, isn't it? Put it as a measure to your cube. No need for any additional fields ... If it is a formula field, create a second duration field, replace IPowertext with new field name, delete "/480" and use this field as measure.

    Hope that is what you want it to be?
    Regards
    Barbara

    Tuesday, August 10, 2010 10:17 AM
    Moderator
  • Hi Barbara,

     

    No worries, I learned a lot. sorry it took up so much of your time. 

    I have tried to add the Ipower field to the cube as a measure maybe a thousand times. I can select it in the configure part, but in Excel in the pivot table I cannot select it, i can't find it anywhere......!! :-(

     

    Question nr 2. Why can't i use the ipower field in a msp_portfolio_analyzer in a formula? that would also solve a lot.

     

    many thanks again


    Kind regards, Bert
    Tuesday, August 10, 2010 10:32 AM
  • Hi Bert,

    it worries myself - wasting also your time because of not reading carefully.

    Are you working with 2007 or 2010? I think it is 2010, since you want to use in Excel? Please let me know!
    Regards
    Barbara

    Tuesday, August 10, 2010 10:36 AM
    Moderator
  • Hi Barbara,

     

    I'm working in 2007 both Project as Excel. Excel is easy to use for people a little higher up the food chain :-) so if you make a data connection to your cube olap file and they only need to refresh the data to see capacity, work and hopefully Ipower (realised hours).

     


    Kind regards, Bert
    Tuesday, August 10, 2010 11:20 AM
  • Hi Bert,

    before having a deeper look at duration fields in cube....
    Why are you not using Actual Work, if you want to see realised hours?

    Regards
    Barbara

    Tuesday, August 10, 2010 11:44 AM
    Moderator
  • Hi Barbara,

     

    cause this is a calculated field based on work and % work complete. standard project fields. we also use this information to show how far our people think they are, but if you want to have real information you also have to see the hours which people really work on a task. our people write hour notes in on which they indicate how much time was spent on a task. That's why.

     

     


    Kind regards, Bert
    Tuesday, August 10, 2010 11:49 AM
  • Hi Bert,

    You want to see a measure which is not timephased (Custom Enterprise fields can’t be timephased) in a cube displaying timephased data (PortfolioANalyzer). After some testing I don’t see any chance to get that work. Are there any important timephased information you need to see together with IPower? If not, you can use a non-timephased cube and will see IPower in there without any additional steps.

    I don’t know how long you are already working with project server and the impact of changing tracking method. You seem to have always only one resource assigned to a task, so you could use Physical % Complete. Have you ever thought about changing your tracking method? To get what you want, I suggest to let people enter their real effort setting “Hours of work done per period” to see the realised hours. In addition add “Remaining work” or “Physical % Complete” to My Tasks page. Let them update this and you should get how far our people think they are.

    I don’t know if you have restrictions not allowing this approach?
    Regards
    Barbara

    Tuesday, August 10, 2010 12:48 PM
    Moderator
  • Hello Barbara,

     

    I will take your advice to heart and use the non-timephased cubes. they will do.

     

    We've been working with Project for some time now, but just recently started using Project server and sharepoint. We are making good progress but still a long way to go.

     

    Could you recommend some reading about how to make/understand macro?

    Again many thanks for your time and hard work.

     


    Kind regards, Bert
    • Marked as answer by Bert Wolff Tuesday, August 10, 2010 5:19 PM
    Tuesday, August 10, 2010 5:19 PM
  • Hi Bert,

    this book will be helpful to understand macros: http://www.projectvbabook.com/.

    Success!
    Barbara

    Wednesday, August 11, 2010 6:52 AM
    Moderator