SSAS Heterogenous Datasources : SQL Server ,Oracle ,Teradata Combinations

1.   How to use heterogeneous data sources in SSAS


1.1 Introduction

             In our Microsoft BI projects we are creating many SQL Server analysis services CUBEs. In General all the Facts and Dimensions are present in one single SQL server instance which is most common design that everybody will fallow .In some cases we need to get the data from two different instances of SQL servers in this case one instance acts as primary data source and other instance acts as secondary data sources in Data Source view (DSV).  In our Telecom data warehouse we have different applications in different relational databases. Most of the mobile traffic data and Transactional data is available in Oracle and remaining Customer, CDR and Historical data in Teradata databases.
We have to create near-real time CUBEs which we need to present /Alert data within 5-10 minutes of actual event occur for example if number of calls reduced from any particular host for particular 5 minutes interval we need to send Represent that KPI in monitoring dashboard and send alert within next 5-10 minutes to rescue team .For near-real time cubes instead of loading fact data from Oracle/Teradata into sql server we can process cube directly from Oracle/Teradata.
Recently we got a requirement where fact data is available in Oracle and dimensional data available in Teradata. So when we tried to create cube using Oracle as primary data source and Teradata as secondary data source then we got an 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 “
While I am investigating why this error occurs and I performed some different tests using various combinations of data sources in SQL server analysis services CUBEs. What is possible and what is not possible regarding using heterogeneous data sources in SSAS will be shown in this paper.

2.   Primary and Secondary data sources in SSAS


When defining a data source view that contains tables, views, or columns from multiple data sources, the first data source from which you add objects to the data source view is designated as the primary data source (you cannot change the primary data source after it is defined). After defining a data source view based on objects from a single data source, you can then add objects from other data sources.
If an OLAP processing or a data mining query requires data from multiple data sources in a single query, the primary data source must support remote queries using OpenRowset. Typically, this will be a SQL Server data source. For example, if you design an OLAP dimension that contains attributes that are bound to columns from multiple data sources, then Analysis Services will construct an OpenRowset query to populate this dimension during processing. However, if an OLAP object can be populated or a data mining query resolved from a single data source, then an OpenRowset query will not be constructed. In certain situations, you may be able to define attribute relationships between attributes to eliminate the need for an OpenRowset query. To add tables and columns from a second data source, you double-click the DSV in Solution Explorer to open it in Data Source View Designer, and then use Add/Remove Tables dialog box to include objects from other data sources that are defined in your project.

3.   Different types of data sources in SSAS


You can retrieve data from the data sources in the following table. When you install SQL Server Data Tools (SSDT), setup does not install the providers that are listed for each data source. Some providers might already be installed with other applications on your computer; in other cases you will need to download and install the provider.
Note
Third party providers, such as the Oracle OLE DB Provider, may also be used to connect to third party databases, with support provided by those third parties.
Source
Versions
File type
Providers 1
Access databases
Microsoft Access 2007, 2010, 2013.
.accdb or .mdb
Microsoft Jet 4.0 OLE DB provider
SQL Server relational databases 5
Microsoft SQL Server 2005, 2008, 2008 R2, 2012, 2014 Azure SQL Database 2, SQL Server Parallel Data Warehouse (PDW) 3
(not applicable)
OLE DB Provider for SQL Server
SQL Server Native Client OLE DB Provider
SQL Server Native 11.0 Client OLE DB Provider
.NET Framework Data Provider for SQL Client
Oracle relational databases
Oracle 9i, 10g, 11g.
(not applicable)
Oracle OLE DB Provider
.NET Framework Data Provider for Oracle Client
.NET Framework Data Provider for SQL Server
MSDAORA OLE DB provider 4
OraOLEDB
MSDASQL
Teradata relational databases
Teradata V2R6, V12
(not applicable)
TDOLEDB OLE DB provider
.Net Data Provider for Teradata
Informix relational databases
V11.10
(not applicable)
Informix OLE DB provider
IBM DB2 relational databases
8.1
(not applicable)
DB2OLEDB
Sybase relational databases
V15.0.2
(not applicable)
Sybase OLE DB provider
Other relational databases
(not applicable)
(not applicable)
OLE DB provider or ODBC driver


4.   Tests performed with different combinations of relational data sources


We planned to perform tests using different combinations of relational data sources using in our data warehouse environment to know how to use heterogeneous data sources with combinations and to know what is possible and what is not possible with data sources of given providers.
We are using    1. SQL Server    (Provider: Native OLE DB\SQL Server Native Client 10.0)
                           2. Oracle   (Provider: Native OLE DB\Oracle Provider for OLE DB)
                           3. Teradata (Provider: .Net Providers\.NET Data Provider for Teradata):
Test 1: Single Data source    ie SQL server
Scenario: Both Facts and Dimensions are in single SQL server instance. In this case the single data source acts as Primary.
Test 1 Result: It is possible to create cube using on SQL server as primary  .
Test 2: Single Oracle instance data source
Scenario: Both Facts and Dimensions are in single Oracle instance. In this case the single data source acts as Primary.
Test 2 Result: It is possible to create cube using on SQL server as primary.

Test 3:  Combination of SQL server and Oracle.
Scenario: Fact tables are in Oracle and Dimension is in SQL server.
                   Oracle as Primary Data source and SQL server as secondary Data source 
Test 3 Result: It is possible to create Oracle as primary and SQL server as secondary.
Test 4:  Combination of Oracle and SQL server.
Scenario: Fact tables are in Oracle and Dimension is in SQL server.
                   SQL Server as Primary Data source and Oracle as secondary Data source
“ 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 “

Test 4 Result: It is not possible to create Oracle as secondary and SQL server as primary.

Test 5: Single Data source Teradata
Scenario: Fact tables and Dimension tables are in Teradata
                 Teradata as Primary Data Source
Test 5 Result: It is possible to create Teradata as  primary (single ) data source .
Test 6: Combination of Teradata and SQL server
 Scenario: Fact tables in Teradata and Dimension tables in SQL server.
   Teradata as primary data source and SQL server as secondary data source
Test 6 Result: It is possible to create Teradata as primary data source and SQL server as secondary data source .
Test 7: Combination of SQL Server and Teradata
 Scenario: Fact tables in SQL server and Dimension tables in Teradata.
 SQL server as Primary data source and Teradata as secondary data source
“ 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 “
Test 7 Result: It is not possible to create SQL server  as  primary data source and Teradata as secondary data source .
Test 8: Combination Oracle and Teradata
Scenario: Fact tables in Teradata and Dimensional tables in Oracle
Teradata as Primary data source and Oracle as Secondary data source
“ 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 “

Test 8 Result: It is not possible to create Teradata  as  primary data source and oracle as secondary data source .
Test 9: Combination of Teradata and Oracle
Scenario: Fact tables in Oracle and Dimensional tables in Teradata
Oracle as Primary data source and Teradata as Secondary data source
“ 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 “

Test 9 Result: It is not possible to create Oracle  as  primary data source and Teradata as secondary data source .

Conclusion:  what we can conclude from the tests we did using 3 heterogeneous relational data sources i.e. SQL server, Oracle and Teradata in analysis services CUBES in different combinations we come to know that Oracle and Teradata cannot be used as secondary data source with given providers only SQL server can be used as secondary data source. Oracle or Teradata (with the given providers) can only be used as Primary data source.


Comments

Post a Comment

Popular posts from this blog

SSAS : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). + Solution

Problem : have installed Analysis service on my server machine through remote login from my service account. I am able to open Analysis services from there. When I open SSMS from the same service account on my local machine, I am not able to connect to that Analysis service. It is giving me error by saying : "A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond 10.245.192.111:2382(System)." Solution : Based on my research, the issue was caused by the Browser Service account was changed to the domain account. In your scenario, you can try to connect to the Analysis Service using <Servername>:2383 instead of instance name. Or you can change the Browser Service account to "Local System".
Read more

SSIS Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces

Image
Problem : When we have Multiple Namespaces in the Xml Source which we need to load the Data from   as we shown below Then we will face a issue in generating XSD for that XML Error : Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio) Solution : First we need to avoid multiple namespaces to transform your source file to a format that doesn’t refer to the namespaces. SSIS has an XML task that can do the transformation. 1) Add the XML task to an SSIS   Control Flow and edit it. 2) Change the OperationType property value to XSLT, the SourceType to File connection and the Source to your source file that has the problem. 3) Set the SaveOperationResult property to True and expand the OperationResult branch. Set DestinationType to File Connection and the Destination to a new XML file ( ModifiedInput.XML). 4) Add the below script to a new file and save it with an xslt file extension (Input.XSLT) <
Read more