locked
Problems with ProcessAdd and secondary DataSource RRS feed

  • Question

  • We have a SSAS 2005 cube in place and have been using ProcessAdd XMLA for some time with success.  The current environment has a single datasource called 'Sales' and a single DSV called 'Sales'.  The sales datasource is a DB2 database.  Everything works great.

     

    In preperation for adding some new data sources, we have built a version in our test environment that is configured with two data sources and a single DSV.  'Master' is the primary DS and it points to an empty DB on SQL Server.  'Sales' is a secondary DS and it points to the original DB2 database.  There is a single DSV that is called 'Master'.  Everything works well through SSMS, but I can't seem to build the correct XMLA to get ProcessAdd working again.

     

    If I build it with the 'Master' datasource and DSV it gives me a bunch of errors that arent specific enough for me to take action.  Things like

    Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DIM IBU HIER VER', Name of 'IBU' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile=""

     

    If I build the xmla for ProcessAdd with the 'Sales' datasource and 'Master' DSV and then rename 'Master' to 'Sales' it appears to work and executes successfuly..  However, when you watch the activity on the profiler, it is not using the query from the out-of-line DSV query but instead it goes back to SSAS DB and uses the query stored there.

     

    All of the ProcessAdd examples I have seen seem to deal with a single DSV and DS.

     

    Can there be multiple datasources in the ProcessAdd xmla?

     

    Is it possible to make this scenario work?

     

    Thanks,

    Marty

     

    Wednesday, September 5, 2007 8:50 PM

Answers

  • Darren, Thanks for the reply.  I still couldnt get it to work. Something about the two data sources seemed to throw it off.  What I have decided to do instead is use a combination of ALTER DSV and a very simple ProcessAdd XMLA that will tell SSAS to processAdd using the in-line query.  In our environment this actually provides a simpler and easier to maintain solution.

    Thanks again,
    Marty

    Friday, September 7, 2007 6:14 PM

All replies

  • Have you seen Greg's post on ProcessAdd at http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx ?

     

    Apparently the out-of-line dsv needs to have the same ID as the datasource that was used to orginally build the object and the data sources need to be included in the xmla Process element (this is a bug) - see the notes in his process dimension example.

     

    Thursday, September 6, 2007 6:52 AM
  • Darren, Thanks for the reply.  I still couldnt get it to work. Something about the two data sources seemed to throw it off.  What I have decided to do instead is use a combination of ALTER DSV and a very simple ProcessAdd XMLA that will tell SSAS to processAdd using the in-line query.  In our environment this actually provides a simpler and easier to maintain solution.

    Thanks again,
    Marty

    Friday, September 7, 2007 6:14 PM