SSAS dimension from two DSV

Question

please, can I create a dimension from two different Data source views in one cube ? I had one AS DB in it one dsv and one cube now I want create another cube based on other dsv but with same dimension date used tables are wuth same structure joined on same date field to DimDate table I can't see tebles from other then original dsv

BIDS x MSSQL

Thanks a lot !

Answer 1

You can't define a single dimension that uses tables from multiple DSVs. It won't know how to join the two tables together to create a single dimension. However you can add a table to the first DSV that comes from a secondary data source. Just beware that it will try to have SQL Server open an ad-hoc linked server to connect to the secondary data source and that performance may suffer during processing and credentials will need to potentially double hop.

Instead, I would just recommend pulling both tables needed for the dimension into one database with SSIS before cube processing.

Answer 2

The answer is no because it is not the best practice. Note that within the BIDS Designer, you can create multiple "Data Sources", which are objects that utilize data providers such as a text file provider or any other supported data provider. This image shows some of the Data Source providers.

The "Data Source View (DSV) is a different design surface - it "inherits" allowed objects from the provider-linked objects in the Data Source (DS). This image shows two Data Sources and two DSV objects

You can have multiple Data Sources (DS), and any of the objects connected can be used in any DSV (Data Source View), and you can have multiple DSV objects too.

For example, if you connect a data source (DS) provider for SQL Server 2012 by using the "Add New Datasource" wizard in Visual Studio BIDS, and another data source (DS) for a text file, both of those data sources can be exposed in one or multiple Data Source Views (DSV) that you create.

You add items to any DSV by adding or creating objects exposed through the data source providers. The DSV is a container for all objects that have data, but the data source connectors do not live in the DSV, they are only exposed in the DSV through the data source providers.

The data source connectors, and their "connection strings", live within the design surfaces in the DS (data source).

Given that, the best practice is to create one cube containing dimensions from one DSV because that is the best practice. Any one dimension tied to a single cube (with multiple measure groups) should come from the same DSV because that is how they are exposed to the Cube Designer.

The objects within the DSV can be from any number of valid connected data sources exposed through the providers in the Data Source Node.