 |
|
|||||||
 |
   
|
|
||||||
|
|
|||||||
|
|
|
||||||
| Vendor | Microsoft |
| Tool Name | Office Excel |
| Tool Version | 97 to 2007 |
| Tool Web Site | http://office.microsoft.com/excel/ |
| Supported Methodology | [Metadata Management] ETL (Source and Target Data Stores) via OLE API |
Import tool: Microsoft Office Excel 97 to 2007 (http://office.microsoft.com/excel/)
Import interface: [Metadata Management] ETL (Source and Target Data Stores) via OLE API from Import bridge: 'MicrosoftExcel' 6.0.2 - Aug 20 2008 19:35:58
Microsoft Excel is a very commonly used tool in the world of information system design as it provides a very convenient interface to enter, store, and manage metadata; especially in cases where this same information is not being defined and managed by other data modeling, data integration, or business intelligence design tools. These Excel spreadsheets are used to document (e.g., with business names, descriptions, etc.) data models, data dictionaries, or even the data mapping of data movements (e.g., ETL) defined in SQL scripts, COBOL code, etc.
Using this bridge allows one to bring these Excel spreadsheets "into the fold" of a metadata management process, where they may contain anything from simple data models to complex mappings (where various Excel sheets are used to define the source data models, target data model, and the mapping between them).
This bridge reads an Excel spreadsheet in XLS or XML format and creates a model from the textual information in the spreadsheet. The "File" parameter should contain the name of this first spreadsheet, which is generally referred to as the "metadata source spreadsheet."
In addition, the bridge uses a "Metadata mapping spreadsheet", or MetaMap, which is an Excel spreadsheet in XLS or XML format, used by the bridge as a control file to determine both the structure of the metadata import spreadsheet specified in the "File" parameter, as well as how this metadata is mapped from that structure into the Meta Integration Repository Metamodel. The "Metadata mapping spreadsheet" parameter should contain the name of this second spreadsheet.
Some bridge parameters are also embedded in the MetaMap. As such, you may also edit the MetaMap file 'Overview' tab and set the parameter values there. This way, you may always know that the default parameter value is defined in your own MetaMap file, rather than requiring bridge users to specify the correct bridge parameter values.
Some bridge parameters are ONLY embedded in the MetaMap. As such, you MUST also edit the MetaMap file 'Overview' tab and set the parameter values there. For these values, there are no bridge parameter equivalents in the UI.
Some bridge parameters are ONLY provided as bridge parameters in the UI. As such, you MUST set the parameter values here.
In most cases it is entirely unnecessary to create your own MetaMap spreadsheet. Instead, you may simply refer to the Standard Data Model or Standard Data Dictionary MetaMap spreadsheets that are provided as part of the installation. Please find these MetaMap spreadsheets at $MetaIntegration\conf\MIRModelBridgeTemplate\MIRMicrosoftExcel\, as well as samples of associated metadata import spreadsheets. Using one of these files as a guide, you may create a metadata import spreadsheet with your own model information.
Finally, always use the "compiled" version of the MetaMaps (i.e., the .XML versions). When running the bridge on a machine with Excel 2007 installed, the bridge will not work with the .XLS version of the MetaMap. Instead, the .XML version of the MetaMap is REQUIRED. You may "compile" either of these spreadsheets before hand by saving them in XML format. Doing so will improve the speed of import to some extent. However, once a spreadsheet has been saved as XML format, you should never edit the XML file again. Instead, use the original .XLS file for editing. In this way, it is also useful to "lock" a MetaMap spreadsheet, so that users to not "break" this file which is reused by a large number of users.
To save a MetaMap file as XML please follow following steps:
1) Open the mapping XLS file.
2) Click menu Edit->Links and than "Open Source" button. It should open the sample metadata source file that was used to create
this mapping.
3) In the MetaMap spreadsheet select "Overview" sheet and press "Save as XML" button. Your file will be automatically converted to the XML spreadsheet format and saved. At this point all links to the external metadata source file are removed and resulted XML spreadsheet file is completely self-contained.
FREQUENTLY ASKED QUESTIONS
Q: How does one define User Defined Property (UDP) Data Types?
A: The MetaMap contains sections for assigning UDPs to models, schemas, packages,classes, attributes and data types. Each UDP may be of a data type (e.g., STRING,DATE, etc.) and this is defined in the metamap. Thus, one must customize thetype for UDPs that are of a type other than STRING, even for the standarddelivered DataModel-MetaMap and DataMapping-MetaMap.
Q: How does one use the bridge on Windows Vista?
A: When running the bridge on a PC with Microsoft Windows Vista installed, you must use the .XML version of the MetaMap files
Q: How does one edit MetaMap files with Microsoft Excel 2007?
A: When editing MetaMap Excel files in the Office 2007 version of Excel, you must enable macros. Here are the steps:
1. Click on the big circular Office button on the top left corner, then click on the 'Excel Options' button located next to the 'Exit' button in the lower left corner.
2. In the 'Excel Options' window, go to the first tab called 'Popular', then go to the first group called 'Top options for working with Excel', and check the third box 'Show Developer tab in the Ribbon'.
3. In the 'Developer' tab of the ribbon, go to the first block, and click 'Macro Security'.
4. In the 'Trust Center' window, go to the firth tab called 'Macro Settings', and check the fourth radio button to 'Enable all macros'.
Also, be sure to open the linked blank or metadata source file by following these steps:
1. Click on the 'Data' ribbon
2. Click on the 'Edit Links' option in the 'Connections' section of this ribbon
3. Click on the 'Open Source' button
4. If necessary, click on the 'Change Source' button to specify an new source metadata file.
Finally, be sure to save the file as an 'Excel 97-2003 Workbook (*.xls)' and then save it again as an 'XML Spreadsheet 2003 (*.xml)' format.
| Parameter Name | Description | Type | Values | Default | Mandatory |
| File | This bridge reads an Excel spreadsheet in XLS or XML format and creates a model from the textual information in the spreadsheet. The "File" parameter should contain the name of this first spreadsheet, generally referred to as the "metadata import spreadsheet." This metadata import spreadsheet MUST be formatted to match the structure specified by the "Metadata mapping spreadsheet" spreadsheet parameter. Please find samples of these metadata import spreadsheets in $MetaIntegration\conf\MIRModelBridgeTemplate\MIRMicrosoftExcel\BlankFiles\. Using these files as guides, you may simply create a metadata import spreadsheet with your own model information that matches these examples. This bridge parameter is NOT in the MetaMap file. |
FILE | Yes | ||
| With metadata mapping spreadsheet | This bridge also uses a "metadata mapping spreadsheet", or MetaMap, which is an Excel spreadsheet in XLS or XML format, used by the bridge as a control file to determine both the structure of the metadata import spreadsheet specified in the "File" parameter, as well as how this metadata is mapped from that structure into the Meta Integration Repository Metamodel. This parameter should contain the name of this second spreadsheet. In most cases it is entirely unnecessary to create your own MetaMap spreadsheet. Instead, you may simply use the Standard Data Model or Standard Data Dictionary metadata mapping spreadsheets that are provided as part of the installation. Please find these metadata mapping spreadsheets at $MetaIntegration\conf\MIRModelBridgeTemplate\MIRMicrosoftExcel\MetadataMappingFiles\. Please note, when creating and maintaining the MetaMap spreadsheet, you should: 1) Always have a sample metadata import file open in Excel, and maintain that sample in the same directory location. This way, when you need to edit the MetaMap spreadsheet, all references to the sample file structure will be simple for Excel to resolve. 2) Turn off the Excel feature for automatically updating automatic external links by selecting Edit->Links and than "Startup prompt..." button in Excel and selecting "Don't display alert and don't update automatic links" option. 3) "Compile" your MetaMap spreadsheet into an XML spreadsheet format, and maintain this compiled version for importing and exporting metadata. When running the bridge on a machine with Excel 2007 installed, the bridge will not work with the .XLS version of the MetaMap. Instead, the .XML version of the MetaMap is REQUIRED. This bridge parameter is NOT in the MetaMap file. |
FILE | Yes | ||
| Compare names ignoring case | Generally, objects within the metadata import spreadsheet will be referred to many times on many worksheets (or tabs). The bridge associates these objects by name. This bridge parameter informs the bridge how to compare names when matching case (upper vs. lower). "True": object names will be compared ignoring case. "False": object names will be compared in a case-sensitive manner. "Use metadata mapping spreadsheet value": to use the value for this bridge parameter specified in the MetaMap file specified. |
ENUMERATED | Use metadata mapping spreadsheet value True False |
Use metadata mapping spreadsheet value | |
| Merge objects with same names | Set this option to true if you expect same objects to appear multiple times in the same table. For example if document defines maps between source and destination features, same source feature may appear multiple times as a data source for different destination features. In this case if this option is set to true, bridge will import only one source feature. Specify 'Use metadata mapping spreadsheet value' to use the value for this bridge parameter specified in the MetaMap file specified. |
ENUMERATED | Use metadata mapping spreadsheet value True False |
Use metadata mapping spreadsheet value | |
| Print warnings if rows are skipped in the middle of the table | The bridge generally determines the last valid row in a worksheet by counting the number of empty rows. Possible values for this parameter are: "True": If there are a number of rows partway into a worksheet that are determined to be empty, but not enough to signal the end of the spreadsheet, the bridge will display a warning to that effect in the log. "False": No warnings are displayed in the log concerning empty rows. "Use metadata mapping spreadsheet value": to use the value for this bridge parameter specified in the MetaMap file specified. Set this parameter to "True" if the metadata import spreadsheet contains varying numbers of empty rows and you wish to be sure that the bridge has parsed all of the non-empty rows. |
ENUMERATED | Use metadata mapping spreadsheet value True False |
Use metadata mapping spreadsheet value | |
| ETL data stream merging strategy | Defines import bridge behavior when imported data integration transformations are merging tables without an explicit join or union. Supported actions are: Add join - generate a join transformation to join data pipelines. Allows exporting to ETL tools. Add union - interpret data pipelines merging as a data union. Allows exporting to ETL tools. Do not change - do not change model content by introducing changes necessary for exporting to ETL tools. Use Metadata mapping spreadsheet value - use the value for this bridge parameter specified in the MetaMap file. |
ENUMERATED | Use metadata mapping spreadsheet value Add join Add union Do not change |
Use metadata mapping spreadsheet value | |
| Environment variable file | The MetaMap spreadsheet may allow for substitution parameters. These parameters are then determined at the time of execution of the bridge based upon the setting of OS environment variables with the same names as the substitution parameters. Please find examples of an environment variable file at $MetaIntegration\conf\MIRModelBridgeTemplate\MIRMicrosoftExcel. This bridge parameter is NOT in the MetaMap file. |
FILE |
| Meta Integration Repository (MIR) Metamodel (based on the OMG CWM standard) |
"Microsoft Office Excel" Metamodel |
Mapping Comments |
| Attribute | Attributes, SourceAttributes or TargetAttributes | |
| Comment | Attribute Comment | |
| Description | Attribute Description | |
| InitialValue | Attribute Initial Value | |
| Name | Attribute Name | |
| Optional | Is Attribute Optional | Is Attribute Required |
| PhysicalName | Attribute Physical name | |
| Position | Attribute Position | |
| Class | Classes, SourceClasses or TargetClasses | |
| Comment | Class Comment | |
| Description | Class Description | |
| Name | Class Name | |
| PhysicalName | Class Physical name | |
| ClassifierMap | Computed based on feature maps | |
| DataAttribute | Computed based on feature maps | |
| DataSet | Computed based on Transformation | |
| DatabaseCatalog | DataPackages, SourceDataPackages or TargetDataPackages | Created based on the "System Type" |
| Comment | Package Comment | |
| Description | Package Description | |
| Name | Package Name | |
| PhysicalName | Package Physical name | |
| SystemType | System Type | |
| DatabaseSchema | Schemas, SourceSchemas or TargetSchemas | Created based on the "System Type" |
| Comment | Schema Comment | |
| Description | Schema Description | |
| Name | Schema Name | |
| PhysicalName | Schema Physical name | |
| DerivedType | Create using "Data Type Inherited From" or calculated using template data. | |
| Comment | Data Type Comment | |
| Description | Data Type Description | |
| InitialValue | Data Type Initial Value | |
| Length | Data Type Length | |
| LowerBound | Data Type Min Value | |
| Name | Data Type Name | |
| PhysicalName | Data Type Physical Name | |
| Scale | Data Type Scale | |
| UpperBound | Data Type Max Value | |
| UserDefined | Calculated based on the datatype usage | |
| DesignPackage | DataPackages, SourceDataPackages or TargetDataPackages | |
| Comment | Package Comment | |
| Description | Package Description | |
| Name | Package Name | |
| PhysicalName | Package Physical name | |
| FeatureMap | FeatureMaps | Computed based on source and target attribute references. |
| Description | Feature Map Description | |
| Name | Feature Map Name | |
| Operation | Feature Map Operation | |
| OperationDescription | Feature Map Operation Description | |
| Model | Models | |
| Author | Model Creation Time | |
| Comment | Model Comment | |
| CreationTime | Model Creation Time | |
| Description | Model Description | |
| ModificationTime | Model Creation Time | |
| Modifier | Model Creation Time | |
| Name | Model Name | |
| PhysicalName | Model Physical name | |
| PropertyType | Defined in the template | |
| DataType | Defined in the template | |
| InitialValue | Defined in the template | |
| Name | Defined in the template | |
| Name | Defined in the template | |
| Value | Defined in the template | |
| RecordFileSchema | Schemas, SourceSchemas or TargetSchemas | Created based on the "System Type" |
| Comment | Schema Comment | |
| Description | Schema Description | |
| Name | Schema Name | |
| PhysicalName | Schema Physical name | |
| Transformation | Transformations | |
| Description | Transformation Description | |
| Name | Transformation Name | |
| Operation | Transformation Operation | |
| OperationDescription | Transformation Operation Description | |
| TransformationTask | TransformationTasks | |
| Description | Transformation Task Description | |
| Name | Transformation Task Name | |
| TypeValue | DataTypes, SourceDataTypes or TargetDataTypes | |
| Description | Data Type Enumerated Value Description | |
| Name | Data Type Enumerated Value |
Last updated on Mon, 25 Aug 2008 19:35:40
