ROLAP, DSV and disparate data sources

hi all,

we have come across peculiar issue using rolap has devastating effects on our performance.

we sharding data across multiple sql instances (all same schema).  for example, have 3 servers , 3 partitions in our main fact measure group pointing 3 separate data sources.

in our data source view, specify first server data source can correctly add tables etc.  in xmla data source, has "<datasourceid>server1</datasourceid>"

when running mdx query hit these rolap partitions, actual sql query generated different.  without being specific, this:

@param1 = '1'
select sum([agg_1].[amount])
[agg_1], openrowset( ... ) [product]
[agg_1].[productid] = [product].[productid]
, [product].[categoryid] = @param1

select sum([agg_1].[amount])
[agg_1], openrowset( ... ) [product]
[agg_1].[productid] = [product].[productid]
group [product].[categoryid]

the latter query being run on 2 other servers (the ones not referenced in data source view).  if change data source view point 1 of other servers, former query run.

clearly, performance difference massive.  in former query, can filter products in category "1" whereas in latter, categories , ssas filters there on.

for reason, ssas believes should able generate sql proper clauses server referenced primary data source in data source view, when in actual fact on three.

this causing significant trouble performance awful poor queries.  we using sql server 2012.

does know why happening , avenue take fix it?

unfortunately, expected behaviour when using multiple data sources - there has primary data source sql server, , queries other data sources directed through using openrowset. you're going need change architecture think... why want use multiple data sources, , why use rolap (which performance nightmare in itself)?

regards,

chris

---

check out my ms bi blog ssas, powerpivot, mdx , dax consultancy , run public sql server , bi training courses in uk

SQL Server  >  SQL Server Analysis Services