Problems with ProcessAdd and secondary DataSource

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

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.

|||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

Problems with ProcessAdd and secondary DataSource

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

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.

|||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

Problems with dual role of time dimension

I'm fairly new to SSAS, so forgive me for asking a question that’s probably quite easy to answer, if you know your way around cubes and dimensions.

The situation is this: I'm creating a cube for resource planning (dimensions include resource/team, project/customer and time, fact table include number of bookings and value of bookings/projects). The time dimension plays two roles:

1) The booking date. Meaning that ressource1 from Team A is booked for project X on given dates in the future.

2) Reporting date. The complete plan is loaded into a Data warehouse once a day. The reporting date should allow us to compare the current situation with a previous point in time (compare booking ratio, value of future projects etc). In effect the reporting date is the versioning of the plan.

I'm currently facing two problems with the dual role of the time dimension:

1) How can you make the Reporting date semi-additive without also affecting the booking date (booking count and value should be summed across booking dates for a given reporting date, but not across reporting dates)?

2) How can you set default values for reporting dates? - Default I would always want to see data for the latest reporting date (corresponding to the latest version of the plan). But this should not affect the booking date - for the latest version of the plan, I want to see all future bookings.

I guess these are common problems when building a planning cube with time versioning, so I hope somebody has some valuable input.

Thanks in advance.

Thomas N. S?rensen

Hi Thomas,

Here are links to past threads in this forum that could help you:

For problem 1) relating to semi-additive measures when there are multiple time dimensions:

lastnonempty & role playing date dimension

>>

Hello,

I'm just curious if in case of a role-playing date dimention it's possible to somehow tell SSAS to use only one role for LastNonEmpty aggregate?

Like we have a fact table with a few date related members - such as TransactionDate, DateOpened, DateClosed etc.

All measures in this fact table are set to aggregate as LastNonEmpty & everything works just fine as long as only TransactionDate is linked to dimDate.

If any other dates are linked then LastNonEmpty doesn't work properly anymore & we get unpredictable results.

So if it possible to set ONLY TransactionDate to be used as LastNonEmpty & for all other dates just aggregate as sum?

Thanks!

...

It is always only ONE role-playing time dimension. The problem is, you cannot control which one it is. Sorry, but there is no way to tell directly to SSAS which one. You can keep reordering the cube dimensions in AMO until SSAS picks the right one. After that, if the order doesn't change - it will always use the same one (it is stable algorithm w.r.t. order of dimensions).

---

Mosha - http://www.mosha.com/msolap
...

>>

For problem 2) relating to different default members for each role of a dimension:

Different Default Members for Role Playing Dimension

>>

...

You could try updating the default member for each role with cube MDX script statements instead, like:

Code Snippet

ALTER CUBE CurrentCube UPDATE DIMENSION [Base UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Base UOM].[UOM Abbrev].&[Abbrev1];

ALTER CUBE CurrentCube UPDATE DIMENSION [Reporting UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Reporting UOM].[UOM Abbrev].&[Abbrev2];

>>