locked
compare cube value to relational dbase value RRS feed

  • Question

  • I want to compare a value in the cube with a value in a relational database.

     

    I want this comparison to automatically run daily and email me if the 2 figures are different.

    How do i do this?

    I guess i want to compare a mdx result against a select sum(x) from ... result, how do i do this? I dont know how to combine the 2 different types of query.

    Friday, January 13, 2012 4:10 PM

Answers

All replies

  •  

    Please refer to the following thread. And post back if you have further questions.

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/82b8e01e-b177-4b25-b633-e88501572a47


    please remember to mark as answered if the post helped resolve the issue.
    • Proposed as answer by Challen Fu Tuesday, January 17, 2012 7:33 AM
    • Marked as answer by Challen Fu Tuesday, January 24, 2012 5:33 AM
    Friday, January 13, 2012 4:27 PM
  • I will, thanks for your help.

    Is it possible to use mdx in conjunction with the execute sql task?


    Friday, January 13, 2012 4:34 PM
  • You can use OPENQUERY for this. See these links for guidance

    http://geekswithblogs.net/darrengosbell/archive/2006/04/26/76418.aspx

    http://www.triballabs.net/2011/11/overcoming-openquery-mdx-challenges/

     

     


    -Remember to mark as helpful/the answer if you agree with the post.
    • Proposed as answer by Challen Fu Tuesday, January 17, 2012 7:33 AM
    • Marked as answer by Challen Fu Tuesday, January 24, 2012 5:33 AM
    Friday, January 13, 2012 4:50 PM
  • Yes you can. In execute sql task , you can use  MDX,DMX and sql.
    please remember to mark as answered if the post helped resolve the issue.
    • Edited by rok1 Friday, January 13, 2012 5:33 PM
    Friday, January 13, 2012 5:32 PM
  • I can store the output from the stored proc in object variable but how do i put the variable content into a database table? 
    Monday, January 16, 2012 12:12 PM
  • Alternatively use a dataflow task with MDX query and load the cube data into a table into the database. Now you can compare these two tables and send out the email using script task.

    To use MDX query as source in OLE DB data flow task , create a OLE DB connection manager  and choose the provider for Analysis services.

    • Proposed as answer by Challen Fu Tuesday, January 17, 2012 7:33 AM
    • Marked as answer by Challen Fu Tuesday, January 24, 2012 5:33 AM
    Monday, January 16, 2012 12:40 PM
  • Alternatively use a dataflow task with MDX query and load the cube data into a table into the database. Now you can compare these two tables and send out the email using script task.

    To use MDX query as source in OLE DB data flow task , create a OLE DB connection manager  and choose the provider for Analysis services.

    Thanks, I managed to get this working :)
    Monday, January 16, 2012 2:38 PM