Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
Mixed OLAP cube

Question Mixed OLAP cube

  • Monday, June 25, 2012 12:24 PM
     
     
    Sign In to Vote
    0

    Dear colleagues,

    Please help to newbie with building mixed OLAP cube.

    I have Project Server 2010, Analysis Service, Reporting Service.

    I have standard cube named "Task Non Timephased" with measures which are custom fields (Forecasted Cost, Forecasted Duration).

    How can i build mixed cube with measures from "Task Non Timephased" and "Assignment Non Timephased"?

    Also one more question - when i build reports via Dashboard designer where i can find EAC ?

     

All Replies

  • Monday, June 25, 2012 2:00 PM
    Moderator
     
     
    Sign In to Vote
    0
    You may have to check the virtual cubes - maybe Portfolio Analyzer?
     
    For the EV data, you need to go into the cube configuration and specifically
    select to include EV fields.  Note however that the OLAP cubes do not contain
    timephased EV data - only the latest value for the project.  You may be better
    off pushing EV snapshots into a custom table for reporting.
     
     
    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
     
  • Tuesday, June 26, 2012 3:26 AM
     
     
    Sign In to Vote
    0

    Andrew, there are no custom fields in Portfolio Analyzer.

    Also i have no available EV fields in cube configuration. Should be EV fields  specifically configured for OLAP cubes?

    Should i use MDX expression to build mixed cube?

     
  • Tuesday, June 26, 2012 8:16 AM
     
     
    Sign In to Vote
    1

    Hi Denis,

    EAC field is not OOTB in OLAP cubes.

    "mixed cubes" you can build via BI development studio where you choose some cube (cube 1) and than add measures to this cube as new linked object from another cube (cube 2) in this scenario eg to Task Non Timephased from Assignment Non Timephased measures. You can also use MDX to query measures from another cube, but from view of performance it should not be optimal.

    Easiest way is to create your own custom database and in this database create customization as you need. in this article you can read some information how you can extend Project OLAP http://msdn.microsoft.com/en-us/library/aa188799(v=office.10).aspx

    Tomas

     
  • Tuesday, June 26, 2012 8:26 AM
     
     
    Sign In to Vote
    0
    Tomas, could you please provide some example of right MDX Expression?
     
  • Tuesday, June 26, 2012 9:00 AM
     
     
    Sign In to Vote
    0

    Here you can find MDX expression for lookupcube function http://msdn.microsoft.com/en-us/library/ms144720.aspx

    could you describe me what kind of report or structured data you want to express? without knowledge what you want to add to report is difficult write MDX statement.

    but as you can read in msdn article "Any calculation using the LookupCube function is likely to suffer from poor performance. Instead of using this function, consider redesigning your solution so that all of the data you need is present in one cube."

     If you reach the point where you just absolutely have to use the LOOKUPCUBE function in a calculated measure, it's time to start thinking about redesigning your cube to include the missing data.

    tomas

     
  • Tuesday, June 26, 2012 9:03 AM
     
     
    Sign In to Vote
    0
    Tomas, as i wrote before, i have cube named "Task Non Timephased" with measures which are custom fields (Forecasted Cost, Forecasted Duration). And i need to include these fields into cube named "Assignment Non Timephased"
     
  • Tuesday, June 26, 2012 9:10 AM
     
     
    Sign In to Vote
    0
    i understand but i need to now report structure, rows and columns.
     
  • Tuesday, June 26, 2012 11:45 AM
     
     
    Sign In to Vote
    0

    Denis,
    Here you can find example of MDX statement to query across cube

    WITH MEMBER [Measures].[LookupDate] AS
    LOOKUPCUBE("Task Non Timephased",
    "([Measures].[Task Count], [Task List].[Task List].&[" + [Task List].[Task List].CurrentMember.Name + "])")

    of course to Olap configuration you should add this MDX withou statement before LOOKUPCUBE. This statemet query from Task Non Timephased to selected cube, base on task list.

    hope this helps you to lookup measures between cubes.

    tomas

     
  • Tuesday, June 26, 2012 2:56 PM
    Moderator
     
     
    Sign In to Vote
    0
    Scroll down in the same screen and you see the option to opt in/out baseline and EVM data.

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky