Microsoft SQL Server Analysis Services (SSAS) 7.0 to 2000 (via ODBO) - Unsupported EOL Bridge - Import - 7.1

Talend Data Catalog Bridges

author
Talend Documentation Team
EnrichVersion
7.1
EnrichProdName
Talend Big Data Platform
Talend Data Fabric
Talend Data Management Platform
Talend Data Services Platform
Talend MDM Platform
Talend Real-Time Big Data Platform
EnrichPlatform
Talend Data Catalog

Bridge Requirements

This bridge:
  • is only supported on Microsoft Windows.

  • requires the tool to be installed to access its SDK.

Bridge Specifications

Vendor Microsoft
Tool Name SQL Server Analysis Services (SSAS)
Tool Version 7.0 to 8.0 (2000)
Tool Web Site http://msdn.microsoft.com/en-us/library/ms714903.aspx
Supported Methodology [Business Intelligence] BI Design (OLAP Source) via ADOMD COM API
Incremental Harvesting
Multi-Model Harvesting
Remote Repository Browsing for Model Selection
Data Profiling

Import tool: Microsoft SQL Server Analysis Services (SSAS) 7.0 to 8.0 (2000) (http://msdn.microsoft.com/en-us/library/ms714903.aspx)
Import interface: [Business Intelligence] BI Design (OLAP Source) via ADOMD COM API from Microsoft SQL Server Analysis Services (SSAS) 7.0 to 2000 (via ODBO) - Unsupported EOL Bridge Import bridge: 'MicrosoftAdomd' 10.1.0

This bridge relies on the the ActiveX Data Objects Multi-Dimensional API (ADOMD) version 2.5 and OLE DB for OLAP (ODBO) to connect to an OLAP Database and import the metadata definition of OLAP cubes.
These APIs are part of the Microsoft Data Access Components (MDAC) architecture.

To enable access to the OLAP system via ADOMD, the OLAP database vendors provides an ODBO compliant data Provider that plugs into this architecture. Please make sure this data provider has been properly installed and configured.

FREQUENTLY ASKED QUESTIONS

How do I connect to my OLAP database?

Provide the OLEDB connection string in the appropriate bridge option. An OLEDB Connection String usually uses:

a Universal Data Link (UDL) file which contains all connection parameters or an OLEDB for OLAP data provider and its specific options. See the parameter description for examples of connection strings.


Bridge Parameters

Parameter Name Description Type Values Default Scope
Connection String NOTE: As you can see below, this parameter may contain password information.

This parameter is passed as clear text within this API based interface and within the bridge itself. Please be cognizant of any security concerns relating to passing passwords via clear text within your organization

Provide here the OLE DB connection string used to connect to the OLAP Server.
Please refer to your OLAP vendor documentation for details.

Importing from Microsoft SQLServer 2000 Analysis Services:

To connect to a cube running on a local computer, the connection string would look like:
Provider=msolap;Data Source=localhost;Initial Catalog=FoodMart 2000
Provider=msolap;Data Source=localhost;Initial Catalog=Bobs Video Store

To connect to a Cube running on a distant server, the connection string would look like:
Provider=msolap;Data Source=ServerName;Initial Catalog=Bobs Video Store

You can also directly connect to a cube file using the connection string format:
Provider=msolap;Location=C:\MSDASDK\samples\oledb\olap\data\bobsvid.cub

Importing from MicroStrategy MDX Adapter:

To connect to a cube using the MDX Adapter user interface, the connection string would look like:
Provider=MSTRODBO;

To connect to a cube directly, the connection string would look like:
Provider=MSTRODBO;Cache Authentication=False;Mask Password=False;Persist Encrypted=True;Persist Security Info=True;User ID=Administrator;Data Source=MicroStrategy BI Developer Kit (Direct);Initialization Mode=Read|Share Deny None;Extended Properties="PRMT=N/UID=Administrator/PWD=/PRJ=MicroStrategy Tutorial/CUBE=Tutorial/FILTER=/S/N"

Importing from SAS OLAP Server

To connect to a local SAS OLAP Server, the connection string would look like:
Provider=sas.OLAPProvider.9.1;Data Source=_LOCAL_

To connect to a remote SAS OLAP Server, the connection string would look like:
Provider=SAS OLAP Data Provider 9.1;User ID=sas; Password=sas;Data Source=;Mode=Read;SAS Cell Cache Size=10000;SAS Machine DNS Name=psassrv1;SAS Port=5451;SAS Protocol=2;SAS Server Type=2
Provider=sas.OLAPProvider.9.1;Data Source=lambchop.unx.sas.com;SAS Port=1359;SAS Protocol=2;User ID=fred;Password=banana

Importing from SAP Business Warehouse:

To connect to SAP BW, the connection string looks like:
Provider=MDrmSap;DataSource=JXX;UserID=MyName;Password=MyPassword;SFC_CLIENT=005;SFC_LANGUAGE=DE
STRING     Mandatory

 

Bridge Mapping

Meta Integration Repository (MIR)
Metamodel
(based on the OMG CWM standard)
"Microsoft SQL Server Analysis Services (SSAS) 7.0 to 2000 (via ODBO) - Unsupported EOL Bridge"
Metamodel
MicrosoftAdomd
Mapping Comments
     
Cube ADOMD:CubeDef  
Description Description  
Name Name  
CubeDimensionAssociation ADOMD::Cube defines which Dimensions are used in the Cube
DesignPackage   one default Package is created to store all the cubes and dimensions.
Name   set to Logical View
UserDefined   Set to FALSE
Dimension ADOMD::Dimension  
Description Description  
Name Name  
PhysicalName UniqueName  
Type   set to TIME or REGULAR if available via data provider
DimensionAttribute ADOMD::Level  
Name Name  
Hierarchy ADOMD::Hierarchy  
Description Description  
Name Name  
PhysicalName UniqueName  
HierarchyLevelAssociation ADOMD::Level defines which Levels are used in the Dimension Hierarchy
Position Depth  
Level ADOMD::Level  
Description Description  
Name Name  
PhysicalName UniqueName  
LevelAttribute ADOMD::Level  
Name Name  
Measure ADOMD::Member The measures are imported from the Cube members
Description Description  
Name Name  
PhysicalName UniqueName  
PropertyElementTypeScope ADO::Property  
Scope   set to DIMENSION, HIERARCHY, LEVEL or MEASURE
PropertyType ADO::Property Data provider specific properties for Dimensions, Hierarchies, Levels and Measures
Name Name  
Usage DataProvider Name  
PropertyValue ADO::Property  
Value Value value set on an object
StoreModel ADOMD::Catalog The model is built using the OLAP metadata accessible via the ADOMD data provider.
Name name