SSRS shared data source to SSAS and MDX query

Question

I am trying to deploy reports that connect to an SSAS cube as a shared data source. The problem I'm having centers around that fact that even if I change the Initial Catalog in the SSAS connection string, all the MDX queries in the rdl files still reference the old SSAS cube by name.

This is a particular problem because these cube names will change often (would be different for just about every client we install our software for). If the name of the cube a developer wrote the report with is hard-coded in the query, is the best way to do with this to do some search/replace in the rdl files before trying to deploy?

Here's an example of one of the MDX simpler queries:

WITH MEMBER [Measures].[ParameterCaption] 
AS [Time Dim].[Year].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] 
AS [Time Dim].[Year].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] 
AS [Time Dim].[Year].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} 
ON COLUMNS , [Time Dim].[Year].ALLMEMBERS ON ROWS FROM [Data Warehouse]

So say that "Data Warehouse" was the name of the database storing the data for the SSAS cube on the dev's machine. But in production it's called "Prod DW". Even though I change the data source to point to the new cube, the queries still look at the old one. The only way I've found to change it is to hand edit the rdl files or edit them in Visual Studio.

ZombieDev ZombieDev · Accepted Answer · 2010-02-17T17:49:21

So I'm not entirely certain about this answer and other people can confirm if this is true.

It seems like the names such as [Data Warehouse] or [Some Cube] that were in these queries are the names of the data sources, data source views, and cubes in our SSAS database.

The reason I was concerned is because our application needs to be multi-tenant with multiple tenants having almost identical cubes all hosted in the same SSAS server. I thought then that the Name of the Cube needed to be unique for each SSAS database but that's not the case, only the database names need to be unique.

So if in xmla file used to deploy the AS database, the database name and id change but the names and ids of the cubes, data sources, and data source views stay the same then reports can work on various different cubes depending on their shared data source.

SSRS shared data source to SSAS and MDX query

1 Answers