Microsoft SQL Server Data Source View (DSV) - Import
Availability-note
AWS
AWS-only features are available at any of the following URLs: us.cloud.talend.com, eu.cloud.talend.com, ap.cloud.talend.com
Bridge Requirements
This bridge:is only supported on Microsoft Windows.
Bridge Specifications
| Vendor | Microsoft |
| Tool Name | SQL Server Data Source View (DSV) |
| Tool Version | 9.0 (2005) to 10.5 (2008 r2) |
| Tool Web Site | http://msdn.microsoft.com/en-us/library/ms174778.aspx |
| Supported Methodology | [Data Modeling] Data Store (Physical Data Model), BI Design (RDBMS Source, Dimensional Target, Transformation Lineage, Expression Parsing) via DSV XML File |
| Data Profiling |  |
| Incremental Harvesting | |
| Multi-Model Harvesting | |
| Remote Repository Browsing for Model Selection | |
SPECIFICATIONS
Tool: Microsoft / SQL Server Data Source View (DSV) version 9.0 (2005) to 10.5 (2008 r2) via DSV XML File
See http://msdn.microsoft.com/en-us/library/ms174778.aspx
Metadata: [Data Modeling] Data Store (Physical Data Model), BI Design (RDBMS Source, Dimensional Target, Transformation Lineage, Expression Parsing)
Component: MicrosoftSqlServerDsv version 11.2.0
OVERVIEW
This bridge imports an XML file with DSV or XMLA extension.
REQUIREMENTS
n/a
FREQUENTLY ASKED QUESTIONS
n/a
LIMITATIONS
Refer to the current general known limitations at MIMB Known Limitations or bundled in Documentation/ReadMe/MIMBKnownLimitations.html
SUPPORT
Provide a troubleshooting package with debug log. Debug log can be set in the UI or in conf/conf.properties with MIR_LOG_LEVEL=6
Bridge Parameters
| Parameter Name | Description | Type | Values | Default | Scope | ||
| File | This bridge imports an XML file with DSV or XMLA extension. The file represents a data source view definition for Microsoft SQL Server 2005 Analysis Services. Two options are available to retrieve such a file: A) From SQL Server Business Intelligence Development Studio: Each project contains one or more files with dsv extension. Such file can be selected and exported with the command: "File->Save .dsv As ..." to generate the DSV file. Or the file can be found in the project directory. Each project contains one or more files with dsv extension. Such file can be selected and exported with the command: "File->Save .dsv As ..." to generate the DSV file. Or the file can be found in the project directory. B) From SQL Server Management Studio: SQL Server Management Studio can connect to an existing Analysis Services server, which contains a list of data source views. Choose a data source view to be saved and use the command:"Script database as->CREATE To -> File..." to generate a file with XMLA extension. |
FILE |
|
Mandatory | |||
| Create dimensional layer | Select whether to create a dimensional layer or a purely physical layer. 'True' A dimensional layer will be created. 'FALSE' No dimensional layer will be created. |
BOOLEAN |
|
True |
Bridge Mapping
| Meta Integration Repository (MIR) Metamodel (based on the OMG CWM standard) |
"Microsoft SQL Server Data Source View (DSV)" Metamodel MicrosoftSqlServerDsv |
Mapping Comments |
| AssociationRole | Element Content | |
| Multiplicity | Based on Source information and KeyType attribute | |
| Source | Based on Foreign Key information | |
| AssociationRoleNameMap | SourceField, TargetField | |
| Attribute | Column | |
| Description | msprop:Description | Mapped only if comment is empty. |
| Name | msprop:FriendlyName | Attribute Name. Computed if not set |
| Optional | minOccurs | |
| PhysicalName | msprop:DbColumnName | Attribute Physical Name.Computed from 'name' if not set |
| BaseType | SourceField, TargetField | BaseTypes are created to serve as parents for the simpleTypes defined in the file. |
| CandidateKey | xs:unique | |
| PhysicalName | name | Class Physical Name.Computed from the 'name' if not set |
| UniqueKey | parent key attribute information | |
| Class | Table | where msprop:TableType="Table" |
| CppClassType | Set to ENTITY | |
| CppPersistent | Set to True | |
| Description | msprop:Description | |
| Name | msprop:FriendlyName | Class Name. Computed if not set |
| PhysicalName | msprop:DbTableName | Class Physical Name.Computed from the 'name' if not set |
| DatabaseSchema | Database schema | Inferred from Table definition |
| DerivedType | SimpleType | In case of an Union, only the first item is used |
| Length | length, totalDigits, maxLength, minLength | |
| LowerBound | maxExclusive, maxInclusive | |
| Name | name | either explicitely in the file, or computed based on its usage |
| Scale | fractionDigits | |
| UpperBound | maxExclusive, maxInclusive | |
| UserDefined | True for simpleTypes defined in the file, False for those defined in the Schema specification | |
| DesignPackage | No equivalent in XML. A main package "Logical View" is created to contain all elements. | |
| Dimension | DataTable, Named Query | Represents the definition of logical table views, including calculated columns |
| Name | Name | |
| DimensionAttribute | Column, Calculated Column | |
| Name | Name | |
| Position | position | |
| ForeignKey | SourceField, TargetField | |
| Index | TargetIndex | |
| Name | Name | |
| IndexMember | TargetIndexField | |
| Position | position in XML file | |
| Join | Relationship | |
| Name | name | |
| JoinRole | Relationship | 2 roles are created for each join Relationship |
| OlapSchema | Data Source View | An OLAPSchema is created to represent the collection of objects available in this DSV file available to Cube builders |
| SQLViewAttribute | View Column | |
| Description | msprop:Description | Mapped only if comment is empty. |
| DesignLevel | No equivalent for Logical Only objects. | |
| Name | msprop:FriendlyName | Attribute Name. Computed if not set |
| PhysicalName | msprop:DbColumnName | Attribute Physical Name.Computed from 'name' if not set |
| Position | The attributes are ordered by position in the XML file | |
| SQLViewEntity | View | Tables withTableType=View |
| Description | msprop:Description | |
| DesignLevel | No equivalent for Logical Only objects. | |
| Name | msprop:FriendlyName | Class Name. Computed if not set |
| PhysicalName | msprop:DbTableName | Class Physical Name.Computed from the 'name' if not set |
| StoreConnection | Database | Inferred from Table definition |
| StoreModel | Schema | The model is built using the elements contained in the file. |
| Name | Computed from the file name. |