• Success
    Manage your Success Plans and Engagements, gain key insights into your implementation journey, and collaborate with your CSMs
    Success
    Accelerate your Purchase to Value engaging with Informatica Architects for Customer Success
  • Communities
    A collaborative platform to connect and grow with like-minded Informaticans across the globe
    Communities
    Connect and collaborate with Informatica experts and champions
    Have a question? Start a Discussion and get immediate answers you are looking for
    Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
  • Knowledge Center
    Troubleshooting documents, product guides, how to videos, best practices, and more
    Knowledge Center
    One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
    Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
    Information library of the latest product documents
    Best practices and use cases from the Implementation team
  • Learn
    Rich resources to help you leverage full capabilities of our products
    Learn
    Role-based training programs for the best ROI
    Get certified on Informatica products. Free, Foundation, or Professional
    Free and unlimited modules based on your expertise level and journey
    Self-guided, intuitive experience platform for outcome-focused product capabilities and use cases
  • Resources
    Library of content to help you leverage the best of Informatica products
    Resources
    Most popular webinars on product architecture, best practices, and more
    Product Availability Matrix statements of Informatica products
    Monthly support newsletter
    Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
    End of Life statements of Informatica products
Last Updated Date May 26, 2021 |

Challenge

Using Informatica's suite of metadata tools effectively in the design of the end-user analysis application.

Description

The Informatica tool suite can capture extensive levels of metadata but the amount of metadata that is entered depends on the metadata strategy. Detailed  information or metadata comments can be entered for all repository objects (e.g. mapping, sources, targets, transformations, ports etc.). Also, all information about column size and scale, data types, and primary keys are stored in the repository. The decision on how much metadata to create is often driven by project timelines. While it may be beneficial for a developer to enter detailed descriptions of each column, expression, variable, etc, it will also require extra  amount of time and efforts to do so. But once that information is fed to the Informatica repository ,the same information can be retrieved using Metadata reporter any time. There are several out-of-box reports and customized reports can also be created to view that information. There are  several options available to export these reports (e.g. Excel spreadsheet, Adobe .pdf file etc.). Informatica offers two ways to access the repository metadata:

  • Metadata Reporter, which is a web-based application that allows you to run reports against the repository metadata. This is a very comprehensive tool that  is powered by the functionality of Informaticas BI reporting tool, Data Analyzer. It is included on the PowerCenter CD.
  • Because Informatica does not support or recommend direct reporting access to the repository, even for Select Only queries, the second way of repository metadata reporting is through the use of views written using Metadata Exchange (MX).

Metadata Reporter

The need for the Informatica Metadata Reporter arose from the number of clients requesting custom and complete metadata reports from their repositories. Metadata Reporter is based on the Data Analyzer and PowerCenter products. It provides Data Analyzer dashboards and metadata reports to help you administer your day-to-day PowerCenter operations, reports to access to every Informatica object stored in the repository, and even reports to access objects in the Data Analyzer repository. The architecture of the Metadata Reporter is web-based, with an Internet browser front end. Because Metadata Reporter runs on Data Analyzer, you must have Data Analyzer installed and running before you proceed with Metadata Reporter setup.

Metadata Reporter setup includes the following .XML files to be imported from the PowerCenter CD in the same sequence as they are listed below:

  • Schemas.xml
  • Schedule.xml
  • GlobalVariables_Oracle.xml  (This file is database specific, Informatica provides GlobalVariable files for DB2, SQLServer, Sybase and Teradata. You need to select the appropriate file based on your PowerCenter repository environment)
  • Reports.xml
  • Dashboards.xml

Note : If you have setup a new instance of Data Analyzer exclusively for Metadata reporter, you should have no problem importing these files. However, if you are using an existing instance of Data Analyzer which you currently use for some other reporting purpose, be careful while importing these files. Some of the file (e.g., Global variables, schedules, etc.) may already exist with the same name. You can rename the conflicting objects.

The following are the folders that are created in Data Analyzer when you import the above-listed files:

  • Data Analyzer Metadata Reporting - contains reports for Data Analyzer repository itself e.g. Todays Login ,Reports accessed by Users Today etc.
  • PowerCenter Metadata Reports - contains reports for PowerCenter repository. To better organize reports based on their functionality these reports are further grouped into        subfolders as following:
  • Configuration Management - contains a set of reports that provide detailed information on configuration management, including deployment and label details. This folder contains following subfolders:
    • Deployment
    • Label
    • Object Version
  • Operations - contains a set of reports that enable users to analyze operational statistics including server load, connection usage, run times, load times, number of runtime errors, etc. for workflows, worklets and sessions. This folder contains following subfolders:
    • Session Execution
    • Workflow  Execution
  • PowerCenter Objects - contains a set of reports that enable users to identify all types of PowerCenter objects, their properties, and interdependencies on other objects within the repository. This folder contains following subfolders:
    • Mappings
    • Mapplets
    • Metadata Extension
    • Server Grids
    • Sessions
    • Sources
    • Target
    • Transformations
    • Workflows
    • Worklets
  • Security - contains a set of reports that provide detailed information on the users, groups and their association within the repository.

Informatica recommends retaining this folder organization, adding new folders if necessary.

The Metadata Reporter provides 44 standard reports which can be customized with the use of parameters and wildcards. Metadata Reporter is accessible from any computer with a browser that has access to the web server where the Metadata Reporter is installed, even without the other Informatica client tools being installed on that computer. The Metadata Reporter connects to the PowerCenter repository using JDBC drivers. Be sure the proper JDBC drivers are installed for your database platform.

Note: You can also use the JDBC to ODBC bridge to connect to the repository (e.g., Syntax -  jdbc:odbc:<data_source_name>

  • Metadata Reporter is comprehensive. You can run reports on any repository. The reports provide information about all types of metadata objects.
  • Metadata Reporter is easily accessible. Because the Metadata Reporter is web-based, you can generate reports from any machine that has access to the web server. The reports in the Metadata Reporter are customizable. The Metadata Reporter allows you to set parameters for the metadata objects to include in the report.
  • The Metadata Reporter allows you to go easily from one report to another. The name of any metadata object that displays on a report links to an associated report. As you view a report, you can generate reports for objects on which you need more information.

The following table shows list of reports provided by the Metadata Reporter, along with their location and a brief description:

Reports For PowerCenter Repository

Sr No.

Name

Folder

Description

1

Deployment Group

Public Folders>PowerCenter Metadata Reports>Configuration Management>Deployment>Deployment Group

Displays deployment groups by repository

2

Deployment Group History

Public Folders>PowerCenter Metadata Reports>Configuration Management>Deployment>Deployment Group History

Displays, by group, deployment groups and the dates they were deployed. It also displays the source and target repository names of the deployment group for all deployment dates. This is a primary report in an analytic workflow.

3

Labels

Public Folders>PowerCenter Metadata Reports>Configuration Management>Labels>Labels

Displays labels created in the repository for any versioned object by repository.

4

All Object Version History

Public Folders>PowerCenter Metadata Reports>Configuration Management>Object Version>All Object Version History

Displays all versions of an object by the date the object is saved in the repository. This is a standalone report.

5

Server Load by Day of Week

Public Folders>PowerCenter Metadata Reports>Operations>Session Execution>Server Load by Day of Week

Displays the total number of sessions that ran, and the total session run duration for any day of week in any given month of the year by server by repository. For example, all Mondays in September are represented in one row if that month had 4 Mondays

6

Session Run Details

Public Folders>PowerCenter Metadata Reports>Operations>Session Execution>Session Run Details

Displays session run details for any start date by repository by folder. This is a primary report in an analytic workflow.

7

Target Table Load Analysis (Last Month)

Public Folders>PowerCenter Metadata Reports>Operations>Session Execution>Target Table Load Analysis (Last Month)

Displays the load statistics for each table for last month by repository by folder. This is a primary report in an analytic workflow.

8

Workflow Run Details

Public Folders>PowerCenter Metadata Reports>Operations>Workflow Execution>Workflow Run Details

Displays the run statistics of all workflows by repository by folder. This is a primary report in an analytic workflow.

9

Worklet Run Details

Public Folders>PowerCenter Metadata Reports>Operations>Workflow Execution>Worklet Run Details

Displays the run statistics of all worklets by repository by folder. This is a primary report in an analytic workflow.

10

Mapping List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mappings>Mapping List

Displays mappings by repository and folder. It also displays properties of the mapping such as the number of sources used in a mapping, the number of transformations, and the number of targets. This is a primary report in an analytic workflow.

11

Mapping Lookup Transformations

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mappings>Mapping Lookup Transformations

Displays Lookup transformations used in a mapping by repository and folder. This report is a standalone report and also the first node in the analytic workflow associated with the Mapping List primary report.

12

Mapping Shortcuts

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mappings>Mapping Shortcuts

Displays mappings defined as a shortcut by repository and folder.

13

Source to Target Dependency

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mappings>Source to Target Dependency

Displays the data flow from the source to the target by repository and folder. The report lists all the source and target ports, the mappings in which the ports are connected, and the transformation expression that shows how data for the target port is derived.

14

Mapplet List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mapplets>Mapplet List

Displays mapplets available by repository and folder. It displays properties of the mapplet such as the number of sources used in a mapplet, the number of transformations, or the number of targets. This is a primary report in an analytic workflow.

15

Mapplet Lookup Transformations

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mapplets>Mapplet Lookup Transformations

Displays all Lookup transformations used in a mapplet by folder and repository. This report is a standalone report and also the first node in the analytic workflow associated with the Mapplet List primary report.

16

Mapplet Shortcuts

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mapplets>Mapplet Shortcuts 

Displays mapplets defined as a shortcut by repository and folder.

17

Unused Mapplets in Mappings

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Mapplets>Unused Mapplets in Mappings

Displays mapplets defined in a folder but not used in any mapping in that folder.

18

Metadata Extensions Usage

 Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Metadata Extensions>Metadata Extensions Usage

 Displays, by repository by folder, reusable metadata extensions used by any object. Also displays the counts of all objects using that metadata extension.

19

Server Grid List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Server Grid>Server Grid List

Displays all server grids and servers associated with each grid. Information includes host name, port number, and internet protocol address of the servers.

20

Session List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Sessions>Session List

Displays all sessions and their properties by repository by folder. This is a primary report in an analytic workflow.

21

 

Source List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Sources>Source List

Displays relational and non-relational sources by repository and folder. It also shows the source properties. This report is a primary report in an analytic workflow.

22

Source Shortcuts

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Sources>Source Shortcuts

Displays sources that are defined as shortcuts by repository and folder

23

Target List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Targets>Target List

Displays relational and non-relational targets available by repository and folder. It also displays the target properties. This is a primary report in an analytic workflow.

24

Target Shortcuts

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Targets>Target Shortcuts

Displays targets that are defined as shortcuts by repository and folder.

25

Transformation List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Transformations>Transformation List

Displays transformations defined by repository and folder. This is a primary report in an analytic workflow.

26

Transformation Shortcuts

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Transformations>Transformation Shortcuts

Displays transformations that are defined as shortcuts by repository and folder.

27

Scheduler (Reusable) List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Workflows>Scheduler (Reusable) List

Displays all the reusable schedulers defined in the repository and their description and properties by repository by folder. This is a primary report in an analytic workflow.

28

Workflow List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Workflows>Workflow List

Displays workflows and workflow properties by repository by folder. This report is a primary report in an analytic workflow.

29

Worklet List

Public Folders>PowerCenter Metadata Reports>PowerCenter Objects>Worklets>Worklet List

Displays worklets and worklet properties by repository by folder. This is a primary report in an analytic workflow.

30

Users By Group

Public Folders>PowerCenter Metadata Reports>Security>Users By Group

Displays users by repository and group.

 

Reports For Data Analyzer Repository

Sr No

Name

Folder

Description

1

Bottom 10 Least Accessed Reports this Year

Public Folders>Data Analyzer Metadata Reporting>Bottom 10 Least Accessed Reports this Year

Displays the ten least accessed reports for the current year. It has an analytic workflow that provides access details such as user name and access time.

2

Report Activity Details

Public Folders>Data Analyzer Metadata Reporting>Report Activity Details

Part of the analytic workflows "Top 10 Most Accessed Reports This Year", "Bottom 10 Least Accessed Reports this Year" and "Usage by Login (Month To Date)".

3

Report Activity Details for Current Month

Public Folders>Data Analyzer Metadata Reporting>Report Activity Details for Current Month

Provides information about reports accessed in the current month until current date.

4

Report Refresh Schedule

Public Folders>Data Analyzer Metadata Reporting>Report Refresh Schedule

Provides information about the next scheduled update for scheduled reports. It can be used to decide schedule timing for various reports for optimum system performance.

5

Reports Accessed by Users Today

Public Folders>Data Analyzer Metadata Reporting>Reports Accessed by Users Today

Part of the analytic workflow for "Today's Logins". It provides detailed information on the reports accessed by users today. This can be used independently to get comprehensive information about today's report activity details.

6

Todays Logins

Public Folders>Data Analyzer Metadata Reporting>Todays Logins

Provides the login count and average login duration for users who logged in today.

7

Todays Report Usage by Hour

Public Folders>Data Analyzer Metadata Reporting>Todays Report Usage by Hour

Provides information about the number of reports accessed today for each hour. The analytic workflow attached to it provides more details on the reports accessed and users who accessed them during the selected hour.

8

Top 10 Most Accessed Reports this Year

Public Folders>Data Analyzer Metadata Reporting>Top 10 Most Accessed Reports this Year

Shows the ten most accessed reports for the current year. It has an analytic workflow that provides access details such as user name and access time.

9

Top 5 Logins (Month To Date)

Public Folders>Data Analyzer Metadata Reporting>Top 5 Logins (Month To Date)

Provides information about users and their corresponding login count for the current month to date. The analytic workflow attached to it provides more details about the reports accessed by a selected user.

10

Top 5 Longest Running On-Demand Reports (Month To Date)

Public Folders>Data Analyzer Metadata Reporting>Top 5 Longest Running On-Demand Reports (Month To Date)

Shows the five longest running on-demand reports for the current month to date. It displays the average total response time, average DB response time, and the average Data Analyzer response time (all in seconds) for each report shown.

11

Top 5 Longest Running Scheduled Reports (Month To Date)

Public Folders>Data Analyzer Metadata Reporting>Top 5 Longest Running Scheduled Reports (Month To Date)

Shows the five longest running scheduled reports for the current month to date. It displays the average response time (in seconds) for each report shown.

12

Total Schedule Errors for Today

Public Folders>Data Analyzer Metadata Reporting>Total Schedule Errors for Today

Provides the number of errors encountered during execution of reports attached to schedules. The analytic workflow "Scheduled Report Error Details for Today" is attached to it.

13

User Logins (Month To Date)

Public Folders>Data Analyzer Metadata Reporting>User Logins (Month To Date)

Provides information about users and their corresponding login count for the current month to date. The analytic workflow attached to it provides more details about the reports accessed by a selected user.

14

Users Who Have Never Logged On

Public Folders>Data Analyzer Metadata Reporting>Users Who Have Never Logged On

Provides information about users who exist in the repository but have never logged in. This information can be used to make administrative decisions about disabling accounts.

Security Awareness for Metadata Reporter

Metadata Reporter uses Data Analyzer for reporting out of the PowerCenter /Data Analyzer repository. Data Analyzer has a robust security mechanism that is inherited by Metadata Reporter. You can establish groups, roles, and/or privileges for users based on their profiles. Since the information in PowerCenter repository does not change often after it goes to production, the Administrator can create some reports and export them to files that can be distributed to the user community. If the numbers of users for Metadata Reporter are limited, you can  implement security using report filters or data restriction feature. For example, if a user in PowerCenter repository has access to certain folders, you can create a filter for those folders and apply it to the user's profile. For more information on the ways in which you can implement security in Data Analyzer, refer to the Data Analyzer documentation.

Metadata Exchange: the Second Generation (MX2)

The MX architecture was intended primarily for BI vendors who wanted to create a PowerCenter-based data warehouse and display the warehouse metadata through their own products. The result was a set of relational views that encapsulated the underlying repository tables while exposing the metadata in several categories that were more suitable for external parties. Today, Informatica and several key vendors, including Brio, Business Objects, Cognos, and MicroStrategy are effectively using the MX views to report and query the Informatica metadata.

Informatica currently supports the second generation of Metadata Exchange called MX2. Although the overall motivation for creating the second generation of MX remains consistent with the original intent, the requirements and objectives of MX2 supersede those of MX.

The primary requirements and features of MX2 are:

Incorporation of object technology in a COM-based API. Although SQL provides a powerful mechanism for accessing and manipulating records of data in a relational paradigm, it is not suitable for procedural programming tasks that can be achieved by C, C++, Java, or Visual Basic. Furthermore, the increasing popularity and use of object-oriented software tools require interfaces that can fully take advantage of the object technology. MX2 is implemented in C++ and offers an advanced object-based API for accessing and manipulating the PowerCenter Repository from various programming languages.

Self-contained Software Development Kit (SDK). One of the key advantages of MX views is that they are part of the repository database and thus can be used independent of any of the Informatica software products. The same requirement also holds for MX2, thus leading to the development of a self-contained API Software Development Kit that can be used independently of the client or server products.

Extensive metadata content, especially multidimensional models for OLAP. A number of BI tools and upstream data warehouse modeling tools require complex multidimensional metadata, such as hierarchies, levels, and various relationships. This type of metadata was specifically designed and implemented in the repository to accommodate the needs of the Informatica partners by means of the new MX2 interfaces.

Ability to write (push) metadata into the repository. Because of the limitations associated with relational views, MX could not be used for writing or updating metadata in the Informatica repository. As a result, such tasks could only be accomplished by directly manipulating the repository's relational tables. The MX2 interfaces provide metadata write capabilities along with the appropriate verification and validation features to ensure the integrity of the metadata in the repository.

Complete encapsulation of the underlying repository organization by means of an API. One of the main challenges with MX views and the interfaces that access the repository tables is that they are directly exposed to any schema changes of the underlying repository database. As a result, maintaining the MX views and direct interfaces requires a major effort with every major upgrade of the repository. MX2 alleviates this problem by offering a set of object-based APIs that are abstracted away from the details of the underlying relational tables, thus providing an easier mechanism for managing schema evolution.

Integration with third-party tools. MX2 offers the object-based interfaces needed to develop more sophisticated procedural programs that can tightly integrate the repository with the third-party data warehouse modeling and query/reporting tools.

Synchronization of metadata based on changes from up-stream and down-stream tools. Given that metadata is likely to reside in various databases and files in a distributed software environment, synchronizing changes and updates ensures the validity and integrity of the metadata. The object-based technology used in MX2 provides the infrastructure needed to implement automatic metadata synchronization and change propagation across different tools that access the PowerCenter Repository.

Interoperability with other COM-based programs and repository interfaces. MX2 interfaces comply with Microsoft's Component Object Model (COM) interoperability protocol. Therefore, any existing or future program that is COM-compliant can seamlessly interface with the PowerCenter Repository by means of MX2.

 

 

Table of Contents

Success

Link Copied to Clipboard