none
Earned value chart containing BCWS, BCWP and ACWP, with time in x-axis

    Question

  • I just want to create an earned value chart containing BCWS, BCWP and ACWP, with time in x-axis. Can you please tell me how to create a SQL Server cube with which I can generate a this chart in Share point. The first task which I guess here is to create a cube with time, can you please guide me how to do that.

    Thanks in advance

    -Georgey

    Friday, October 21, 2011 7:06 PM

Answers

  • Hi Georgey,

    You are on right track. To add a time dimension, it's a simple process. Please follow below steps -

    1. Create a dimension in your database. You can see Adventure Works sample database (AdventureWorksDW2008 -> DimDate). You can also refer link - http://www.bp-msbi.com/2011/09/building-a-date-table/ which has some good information for your reference.

    2. Now, to analyze your Project management metrics you need to have link between your fact table and the date dimension. So you should have a FK date key in your fact table which referes Date dimension.

    3. Once dimension is added and a relationship is established (it's not mandatory though) you can add this in your BIDS. First, add date dimension to your DSV. Make the relationship between fact and dimension table. Right click Dimension folder in Solution explorer and create a new dimension & follow the wizard. Once dimension is added in your BIDS you need to add it into your cube. So for that, double click your cube in cube folder of your solution explorer, on the left side in Dimensions pane right click and click "Add Cube Dimension". Once this is done, you need to click Dimension Usage tab make the relationship between your newly added dimension and measure group as regular. Save your solution and process it & you are done!

    Also, there are default dimensions created, if you don't want you can remove them, please read the steps of wizard carefully. I'd suggest first create all the dimension you want & then create cube on top of it and select only those dimensions which are required for your bussiness and you have created them.

    HTH.

    Regards,
    Santosh

     


    It feels good if you give us some points for helpful post. :)
    Thursday, October 27, 2011 3:27 AM

All replies

  • Are you referring to Project Management data - if so, what does the source data schema look like, and is there a date table in place?
    - Deepak
    Saturday, October 22, 2011 3:33 AM
  • Hi Georgey,

    Deepak has asked a valid question - what is the source of your data. I am assuming here it's in RDBMS, not in MPP.

    If that is the case then -

    You'd need a fact table containing all the project management data - actual and budgeted. You then create dimensions to analyze the data e.g. time dimension is one but you might want to analyze your data based on resources so that could be another one similary others as per your business need. 

    Once your dimensional modelling is complete you then can create cube on top of your database (data mart in BI realm). Initally cube might contain raw data i.e. which may come from time sheets etc. You then would need to compute project metrics like EV, CPI, SPI etc. etc. for that you can create calculated measures in your cube which would be computed based on the values like actual cost, budgeted cost etc. etc.

    Once cube is created you then create SSRS reports and then show them in Sharepoint or may be other ways.

    HTH.

    Regards,
    Santosh

     


    It feels good if you give us some points for helpful post. :)
    Saturday, October 22, 2011 2:56 PM
  • Hi Deepak

    Thanks fro the email. I am new to SQL server, MS Sharepoint and Dev Studio. I looked at the Database through MS SQL server Management Studio, and I see that there is a date table, other supporting tables as well.  I also see that there are cubes created. I see that in Assighnment Non-Time Phased cube, I get all the values what I want, but there is no time component in that, when I look at the Dimensions.

    Can I create a cube based on the 'Assighnment Non-Time Phased' cube, and add Time dimension to it?

    Thanks in advance.

    Warm Regards

    -Georgey

     

    Monday, October 24, 2011 10:38 PM
  • Hi Santosh

    Thanks for the reply. I have my data in SQL server stored in tables. I also see that there is a cube created 'Assighnment Non-Time Phased', which contains what I need (BCWS, BCWP and ACWP). Unfortunately Earned value analysis has an x-axis which is 'time', without which BCWS, BCWP and ACWP graph might not make any sense. How can I get the source code of the existing cubes? Is it possible to take that code and create a new one so that I can combine other tables? Then add dimensions to it. 

    Thanks in advance

    Monday, October 24, 2011 10:51 PM
  • Hi Georgey,

    Yes, that is very much possible.

    If you have the solution of existing cube you can copy it and rename your existing cube to new one and amend it accordingly in BIDS and then deploy it your AS server. You can also connect to the Analysis server from BIDS and can make changes to the exisitng cube, but beware it'd do changes online i.e. directly to the server.

    So better would be to get the exisiting solution and amend it for your scenario.

    Regards,
    Santosh

     


    It feels good if you give us some points for helpful post. :)
    Tuesday, October 25, 2011 2:44 AM
  • Hi Santosh

    Thanks for the message...

    I have an existing cube which is created by someone long back, I can see the structure of it too, but, when I am trying to introduce a time dimension, it throws an error.

    Below are the steps

    Step 1:

    I created a data source view, with the table where BCWS, BCWP and ACWP are stored. I selected the related tables also and saved the data source.

    Step 2:

    Started creating a cube from the above Data Source.  In the cube wizard, I selected my data-source and selected my table where I have BCWS, BCWP and ACWP. In measures I chose only Assignment ACWP, Assignment ACWP and Assignment BCWS. In dimension I chose all the dimensions. when i click on finish, It throws an error that there in no time component and when I click on OK, it closes the cub wizard and exits without creating the cube.

    I am new to SQL server and MS technologies, trying to catch up with them. I dont find any document in the web world which tells me how to create an Earned Value over time cube in a step by step manner. Can you please guide me to get that? It would be a great document for many. Every place I search, I see this question on Earned value over time, but no one is answering when it comes to how to generate a cube.

    Thanks a lot for helping me...

     

     

     

    Tuesday, October 25, 2011 6:14 PM
  • Hi Georgey,

    First of all - it's not that difficult & Microsoft has good documentation & nice support, so you'll get it done soon :).

    For SSAS, EV is just a calculation, so it won't have some template etc. to create it. You'd need to design cube to achieve that.

    From your explanations looks like you are creating your cube from scratch, by seeing the existing solution, not reusing it completely, but that's OK!

    Step 1 looks perfrectly OK. Does it contain your time dimension?

    In Step 2, I am not sure what options you are choosing. You probably can follow links which describes how to generate a cube, dimension etc - http://technet.microsoft.com/en-us/library/ms169712.aspx. Probably you can tell exact steps and then we can tell where is the issue. FYI - Time dimension is not mandate to create a cube. Also, before creating cubes you can establish relationship between fact tables and dimension tables explicitly in DSV, it'd help cube wizard internally to create your cube.

    I am not project metric expert, but what I can suggest you is, get all the data elements which are part of EV formula in cube and then try to create calculated measure to evaluate EV - trust me it won't be difficult.

    Regards,
    Santosh

     

     


    It feels good if you give us some points for helpful post. :)
    Wednesday, October 26, 2011 2:39 AM
  • Hi Santosh

    Thanks for the reply. I kept on doing my experiments and I decided to create a view of my own so that I can create a cube (PMO_GV). So, I went ahead and created a View with the following columns.

    TimeByDay
    AssignmentUID
    ProjectUID
    AssignmentACWP
    AssignmentBCWP
    AssignmentBCWS

    I queried the view and it was giving me values for each day.In the next step I created a Data Source View with this view alone using Microsoft Visual Studio. Then I created a Cube based on the view and clicked the 'Next' until 'Finish' and a new cube was created. I clicked on 'Process' and it succeeded. Bu default I saw that there were 2 dimensions created,
     PMO GV and PMO GV 11.  I saved everything and moved to sharepoint where I was able to see the cube.

    I am able to plot ACWP, BCWP and BCWS with day in X-Axis. The issue here is I am not get the other dimensions like, Week, Month, Quarter and Year. How can I add those dimensions. It will be a great help for me if you can tell me how to do that.

    Thanks

    -Georgey

    Wednesday, October 26, 2011 11:31 PM
  • Hi Santosh

    To make it simple on what I have asked in my previous question. My view has date column , but when I display the report, I want to give it the option of drill up and down based on date column.Year, Quarter, Month, Week and day are what I am looking for.

    Thanks in advance.

    Wednesday, October 26, 2011 11:50 PM
  • Hi Georgey,

    You are on right track. To add a time dimension, it's a simple process. Please follow below steps -

    1. Create a dimension in your database. You can see Adventure Works sample database (AdventureWorksDW2008 -> DimDate). You can also refer link - http://www.bp-msbi.com/2011/09/building-a-date-table/ which has some good information for your reference.

    2. Now, to analyze your Project management metrics you need to have link between your fact table and the date dimension. So you should have a FK date key in your fact table which referes Date dimension.

    3. Once dimension is added and a relationship is established (it's not mandatory though) you can add this in your BIDS. First, add date dimension to your DSV. Make the relationship between fact and dimension table. Right click Dimension folder in Solution explorer and create a new dimension & follow the wizard. Once dimension is added in your BIDS you need to add it into your cube. So for that, double click your cube in cube folder of your solution explorer, on the left side in Dimensions pane right click and click "Add Cube Dimension". Once this is done, you need to click Dimension Usage tab make the relationship between your newly added dimension and measure group as regular. Save your solution and process it & you are done!

    Also, there are default dimensions created, if you don't want you can remove them, please read the steps of wizard carefully. I'd suggest first create all the dimension you want & then create cube on top of it and select only those dimensions which are required for your bussiness and you have created them.

    HTH.

    Regards,
    Santosh

     


    It feels good if you give us some points for helpful post. :)
    Thursday, October 27, 2011 3:27 AM
  • Hi Santosh

    I still have issues. The 'Time' (Fiscal Time is another dimension which I see there)  dimension is already defined and I can see it in the Dimension list. My issue is when I use it. I referred to the links which you gave, but failed to understand how to link the existing Time Dimension to the newly build cube. When I created the cube, by default it created a dimension with the same same as that of my cube and it contains only one attribute 'Time By Day'. I am stuck at this place. I can plot reports based on Time By Day.

    When I try to add time into the dimension cube, it throws the following error.

    --------Lets take this as break point-----

    *************************************************************************************************

    "The data source view for the cube does not contain some table, named calcuations, primary keys, or relationships used by the dimensions of the cube, Do you want to add these items to the data source view?"

    YES or NO

    *************************************************************************************************

    Scenario 1.

    I clicked No. Clicked on Process, it succeeds. Save all. Now when I expand the time attribute, I see Day, Month, Quarter, week, year etc as the attributes.  Clicked on the browser tab so that I can make a report. I added one of my measure to the data area, and added 'Time' to the column area............

    Every column has the same value, expanded year, all the quarters had the same number as that of years, expanded quarters the quarter value, year value and month value are all the same as the total value. In summary, from the very first value to the last, everything is one value. What am I doing wrong here? Can you please guide me?

     

    Below message has reference above.

    *************************************************************************************************

    "The data source view for the cube does not contain some table, named calcuations, primary keys, or relationships used by the dimensions of the cube, Do you want to add these items to the data source view?"

    YES or NO

    *************************************************************************************************

    Secnario 2

    I clicked Yes. It added one more table to my data source view.  I went to the Browser tab and in column I put time. I got the same result as Scenario 1.The cumilative value is displayed for all the values, whether is day/week/month/quarter/year. It displays properly when I put Time By Day in the column.

     

    How can I get around this problem. Thanks in advance. Please guide me.

     

     

     

    Thursday, October 27, 2011 6:36 PM
  • Hi Santosh

    I figure it out. I had not added the referencing table in my datasource view and that was why I was not able to get it. I am able to draw the report now using Sharepoint and works fine on browser.

    Thursday, October 27, 2011 11:13 PM