locked
Multiple data provider calculations RRS feed

  • Question

  • I have two SSAS cubes, one which contains dollar values for utility usage, and the other for the actual usage measures (kilowatts, tons, gallons, etc). I have brought them both in to PowerPivot and I would like to create a calculation to come up with a Dollar per Usage metric to compare similar properties.

    I build the calculation, but it is returning the same value for every row of data, what am I missing?

    The following calc is on the "Usage" tab:

    =SUM([MeasuresELECTRIC])/SUM(USD[MeasuresELECTRIC USD])

    Monday, October 8, 2012 7:15 PM

Answers

  • Just query a relational source for a date dimension.  Or create one on the fly against a relational source with a script like the one Olaf Helper posted in the Script Center here.  Or create a linked table with the dates you need. 

    Just make sure your OLAP queries are returning a date you can create the relationship on.  Then you don't have to use a cube to get your date dimension. 


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Wednesday, October 10, 2012 6:11 PM
    Answerer

All replies

  • Would like to help, but need a bit more detail on your model.  Can you post a diagram or at least details on how the tables are related in your model?  Thanks.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Tuesday, October 9, 2012 4:47 AM
    Answerer
  • It's two SSAS Cubes. I created a common key between the two based on the month/year and property id (concatenated the two fields and joined the two tabs)
    Tuesday, October 9, 2012 12:17 PM
  • I think you need a common dimension table that joins with both in order for the relationships to work. I would create a date table and import that as well and then join the two fact tables to that one dimension table. I think that should work.
    Tuesday, October 9, 2012 11:31 PM
  • I'm unable to do that because it is an OLAP source... you can't query a cube without having something defined as a measure.

    Any other thoughts?

    Wednesday, October 10, 2012 1:03 PM
  • Just query a relational source for a date dimension.  Or create one on the fly against a relational source with a script like the one Olaf Helper posted in the Script Center here.  Or create a linked table with the dates you need. 

    Just make sure your OLAP queries are returning a date you can create the relationship on.  Then you don't have to use a cube to get your date dimension. 


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Wednesday, October 10, 2012 6:11 PM
    Answerer