Friday, August 17, 2012

Report based on 2 cubes with 1 overlapping parameter

The solution is to used linked dimensions:

Define Linked Dimensions

t been rated 
Rate this topic
A linked dimension is one that exists in one Analysis Services database, but reused in another Analysis Services database of the same version and compatibility level. Linked dimensions are read-only. If you want to modify the dimension or create new relationships, you must use the source dimension.
All related measure groups and dimensions must come from the same source database. You cannot create new relationships between local measure groups and the linked dimensions you add to your cube. After linked dimensions and measure groups have been added to the current cube, the relationships between them must be maintained in their source database.

The source database that provides the dimension and the current database that uses it must be at the same version and compatibility level. For more information, see Set the Compatibility Level of a Multidimensional Database (Analysis Services).
The source database must be deployed and online. Servers that publish or consume linked objects must be configured to allow the operation (see below).
The dimension you want to use cannot itself be a linked dimension.

  1. In SQL Server Management Studio, connect to an Analysis Services server. In Object Explorer, right-click the server name and select Facets.
  2. Set LinkedObjectsLinksFromOtherInstancesEnabled to True to allow the server to issue requests for linked objects that reside in databases running on other instances.
  3. Set LinkedObjectsLinksToOtherInstances to True to allow the server to request data for linked on databases running on other instances.

  1. Start the wizard. In SQL Server Data Tools (SSDT), right-click the Dimensions folder in an Analysis Services database or project, and then click New Linked Dimension.
  2. Connect to the Analysis Services database that provides the dimension. On the Select a Data Source page of the Linked Object Wizard, choose the Analysis Services data source or create a new one.
  3. On the Select Objects page of the wizard, choose the dimensions you want to link to in the remote database.
  4. On the Completing the Wizard page, you can preview the linked objects. If you link a dimension that has the same name as one that already exists, an ordinal number (starting with '1' for the first duplicated name) is appended to the name. When you complete the wizard, the dimension is added to theDimensions folder.

Use the New Data Source wizard to add to your project connection information about the Analysis Services database that provides the dimension. You can start the wizard by clicking New Data Source in the Select a Data Source page of the Linked Objects wizard.
  1. In the Data Source Wizard, on the Select how to define the connection page, click New.
  2. In Connection Manager, verify that the provider is set to Native OLE DB\Microsoft OLE DB Provider for Analysis Services 11.0.
  3. Enter the name of the server (use servername\instancename for a named instance)1 or type localhost to connect to an Analysis Services server that is running on the same computer.
  4. Use Windows authentication for the connection.
  5. In Initial catalog, click the down arrow to select a database on this server.
  6. On the Data Source Wizard, click Next to continue.
  7. On the Impersonation Information page, click Use the service account. Click Next, and then finish the wizard. The connection you just defined will be selected in the Linked Objects Wizard.

You cannot change the structure of a linked dimension, so you cannot view it with the Dimension Structure tab of Dimension Designer. After processing the linked dimension, you can view it with the Browser tab. You can also change its name and create a translation for the name.