Multiple Data Souce

Question

 

If I have multiple data source, can I make a relationship in one DSV ?

 

We have one product table in different source system but the product key is in fact which is different data source from product.

 

Once I set up 2 data source then can I make one DSV which contains these 2 data source tables?

After processing cube, I got the following error:

 

Errors in the high-level relational engine. The 'DS Name' secondary data source is not a relational data source, or does not use an OLE DB provider.

 

Also, in connection manager in DS, what's the difference between .NET provider\ Oracle Client Data Providers and OLE DB Provider for Oracle. Currently,  .NET provider\ Oracle Client Data Providers is onlyh working with oracle data source in my case.

 

Any kinds of advice would be greatly appreciated.

Thanks.

Answer 1

Hi,

 

You cannot have multiple datasources into a single DSV. SSAS does not support creation of single DSV from multiple datsources.

 

 

Not sure of the second questions. Never worked with oracle

 

This link might help:

http://books.google.co.in/books?id=bATOzjQmhIgC&pg=PA42&lpg=PA42&dq=List+of+data+provider+%2B+SSAS&source=web&ots=LX_fkLXoWT&sig=KJ1PEbq_OdCfP2Ff756T9IHjGo0&hl=en&sa=X&oi=book_result&resnum=5&ct=result#PPA42,M1

 

Hope this helps ...

 

Regards...

Girija Shankar

 

Answer 2

Thanks for your reply.

 

Even if the values are same except different data source, it won't allow to add different ds into a single DSV?

If so, the only option is to copy the table into the data source which fact table is located and use that table which will be same data souce as fact ?

 

FYI,

I found the article that indicates

 

"AS 2005 can use multiple various data sources to build out a DSV that can contain tables from all of them. You can even create named arbitrary sql queries, and use them as a data source to build your cubes."

ref : http://www.sqlservercentral.com/articles/Miscellaneous/areportfromthenydatabaseprofessionalscouncil/1676/

 

According to this article, I think I can set up the multiple DS into a single DSV.Does anybody give me a clear answer for this?

 

Please let me know.

Thanks.

Answer 3

Hi,

 

Sorry if I confused you. This is what I meant:

 

Basically you can use the objects from  different datasource, but your DSV will be built on a single datasource.

 

To create a data source view that is based on multiple data sources, you first define a data source view that is based on a single data source. This data source is then called the primary data source. You can then add tables and views from a secondary data source. When designing dimensions that contain attributes based on related tables in multiple data sources, you might have to define a Microsoft SQL Server data source as the primary data source to use its distributed query engine capabilities.

 

Please refer the following links (The above definition was there in this link):

http://msdn.microsoft.com/en-us/library/ms170402.aspx

 

Here are the steps:

 

Lets say that you have two datasources (DS1 and DS2):

 

1. First create the DSV using DS1 and select the required tables.

2. On the DSV tab(In BIDS) , select "Add/Remove Objects "

3. A pop up window opens (Add/Remove Tables) and you will have to select the other datasource(DS2) from the datasource dropdown.

4. Select the tables from the other datasource that you want to add.

5. You may have to add some relationships if you need.

 

Hope this helps...

 

Regards..

Girija Shankar

 

 

Answer 4

 

Hi Girija,

 

I do not know whether you are still available to answer my question but here is what I've experieced.

 

In some reason, I can only use .NET Provider to connect oracle data source in connection string.

If I create the DSV with multiple DS I can create the relationship but I can't process the cube with the following reason:

 

Errors in the high-level relational engine. The 'DS Name' secondary data source is not a relational data source, or does not use an OLE DB provider.

 

It seems this is because of provider but I can't resolve this issue since only .NET Provider works for my case.

 

What I did in DSV is that:

 

1. Create One primary DS ( let say PRIMARY_DB)

2. I do not create DS for SECONDARY_DB but joining this table as replacing table with new named query in PRIMARY_DB in DSV

3. Create DSV based on DS and replace table with new named query like: ( joining the table from different DB)

 

    Select * From PRIMARY_DB.table a, SECONDARY_DB.table @SECONDARY DB b

    where a.PROD_CD = b.PROD_CD

   

4. Good news is I can finally process cube as adding dimension from different data source(SECONDARY_DB)  with this query

5. My question is I'm not sure about cube process time and query performance, and it is a good idea or not.

( i'm thinking that the best way is just copy the table in secodary_db into the primary db but client does not want to any integration work at this point, so I want to try to think of this case only in SSAS 2005)

 

Please let me know what you think and any kinds of advice would be greatly appreciated.

Thanks.

 

Answer 5

I thihk what you have done is correct and only possible way out considering client requirements..

 

Hope this helps...

Regards..

Giirja  Shankar

Answer 6

Actually you can have multiple datasource into a single DSV from 2005 onwards.

Try

1) Create 2 datasource pointing to let say 2 different database i.e datasource_A, datasource_b. One of them will be primary.

2) Now create a DSV, with tables from primary data source. Once done

3) Now open the newly created DSV, in the designer right click --> add/remove tables ( or from top menu bar -- data source view --> add/remove tables) & then select the required tables from the datasource_b.

4) Now u have tables from 2 different datasources, you can now create releationship between them.

thanks
Harman

Answer 7

In fact Yes you can have multiple Datasource into a single DSV, however, if you have let's say dimensions split over in Primary and Secondary DS (for e.g. Shared Dimensions in Primary DataSource, and Private Dimensions in Secondary Datasource), and process them there may not be any errors reported, but doing a Dimension Health Check in yours BIDS on Secondary Datasource Dimensions, you will notice pages full of errors with table or view named object as not defined. This is something I recently found out.

 

Thanks,

Tasneem

Answer 8

Here is my answer of Invalid Object Name when referencing dimensions from the secondary datasource using Dimension Health Check.

1. In your Named Query select Primary Datasource for your secondary data object
2. In the query selection use Fully qualified name for your source table / view. i.e. dbname.schemaname.table/view name

The dimension should process OK, as well as the Dimension Health Check should display "No Problems Found". Other issues like key uniqueness, attribute relationships, duplicates etc. is something that you have to work on.

 

Thanks,

Tasneem