Datawarehouse and Data-marts

Data marts are 2 type

• 1) Dependent Data-mart:  Data marts build on Data warehouse. Top down approach. First Data warehouse got built and to ease of use later on Data marts built which are dependent on existing Data Warehouse.
• 2) Independent Data-mart: This is bottom up approach. Data marts have been built based on company or organization requirements and later on created DW by integrating all these data-marts.

Data Warehouse is:

• Subject Oriented
• Integrated
• Non Volatile
• Time Varient

Difference between Star schema and Snow-flake schema:

Comparison chart

Snowflake Schema versus Star Schema comparison chart

	Snowflake Schema	Star Schema
Ease of maintenance / change	No redundancy, so snowflake schemas are easier to maintain and change.	Has redundant data and hence less easy to maintain/change
Ease of Use	More complex queries and hence less easy to understand	Lower query complexity and easy to understand
Query Performance	More foreign keys and hence longer query execution time (slower)	Less number of foreign keys and hence shorter query execution time (faster)
Type of Datawarehouse	Good to use for datawarehouse core to simplify complex relationships (many:many)	Good for datamarts with simple relationships (1:1 or 1:many)
Joins	Higher number of Joins	Fewer Joins
Dimension table	A snowflake schema may have more than one dimension table for each dimension.	A star schema contains only single dimension table for each dimension.
When to use	When dimension table is relatively big in size, snowflaking is better as it reduces space.	When dimension table contains less number of rows, we can choose Star schema.
Normalization/ De-Normalization	Dimension Tables are in Normalized form but Fact Table is in De-Normalized form	Both Dimension and Fact Tables are in De-Normalized form
Data model	Bottom up approach	Top down approach

Informatica repository Backup

Informatica command line reference

pmcmd Command Reference

Using pmcmd

pmcmd is a program you use to communicate with the Integration Service. With pmcmd, you can perform some of the tasks that you can also perform in the Workflow Manager, such as starting and stopping workflows and sessions.

Use pmcmd in the following modes:

Command line mode. You invoke and exit pmcmd each time you issue a command. You can write scripts to schedule workflows with the command line syntax. Each command you write in command line mode must include connection information to the Integration Service.

Interactive mode. You establish and maintain an active connection to the Integration Service. This lets you issue a series of commands. You can use environment variables for user names and passwords with pmcmd. You can also use environment variables to customize the way pmcmd displays the date and time on the machine running the Integration Service process. Before you use pmcmd, configure these variables on the machine running the Integration Service process.

Running Commands in Command Line Mode

Command line mode invokes and exits pmcmd each time you issue a command. Command line mode is useful if you want to run pmcmd commands through batch files, scripts, or other programs. Use pmcmd commands with operating system scheduling tools like cron, or you can embed pmcmd commands into shell or Perl scripts.

When you run pmcmd in command line mode, you enter connection information such as domain name, Integration Service name, user name and password in each command. For example, to start the workflow “wf_SalesAvg” in folder “SalesEast,” use the following syntax:

pmcmd startworkflow -sv MyIntService -d MyDomain -u seller3 -p jackson -f SalesEast wf_SalesAvg

The user, seller3, with the password “jackson” sends the request to start the workflow.

If you omit or incorrectly enter one of the required options, the command fails, and pmcmd returns a non-zero return code.

To run pmcmd commands in command line mode:

1.At the command prompt, switch to the directory where the pmcmd executable is located.

By default, the PowerCenter installer installs pmcmd in the \server\bin directory.

2.Enter pmcmd followed by the command name and its required options and arguments:

pmcmd command_name [-option1] argument_1 [-option2] argument_2...

Return Codes

In command line mode, pmcmd indicates the success or failure of a command with a return code. Return code “0” indicates that the command succeeded. Any other return code indicates that the command failed.

Use the DOS or UNIX echo command immediately after running a pmcmd command to see the return code for the command:

In a DOS shell: echo %ERRORLEVEL%

1

In a UNIX Bourne or Korn shell: echo $?

In a UNIX C shell: echo $status

Running Commands in Interactive Mode

Use pmcmd in interactive mode to start and stop workflows and sessions without writing a script. When you use the interactive mode, you enter connection information such as domain name, Integration Service name, user name, and password. You can run subsequent commands without entering the connection information for each command.

For example, the following commands invoke the interactive mode, establish a connection to Integration Service “MyIntService,” and start workflows “wf_SalesAvg” and “wf_SalesTotal” in folder “SalesEast”:

pmcmd

pmcmd> connect -sv MyIntService -d MyDomain -u seller3 -p jackson

pmcmd> setfolder SalesEast

pmcmd> startworkflow wf_SalesAvg

pmcmd> startworkflow wf_SalesTotal

To run pmcmd commands in interactive mode:

1.At the command prompt, switch to the directory where the pmcmd executable is located.

By default, the PowerCenter installer installs pmcmd in the \server\bin directory.

2.At the command prompt, type pmcmd.

This starts pmcmd in interactive mode and displays the pmcmd> prompt. You do not have to type pmcmd before each command in interactive mode.

3.Enter connection information for the domain and Integration Service. For example:

connect -sv MyIntService -d MyDomain -u seller3 -p Jackson

4.Type a command and its options and arguments in the following format:

command_name [-option1] argument_1 [-option2] argument_2...

pmcmd runs the command and displays the prompt again.

5.Type exit to end an interactive session.

Setting Defaults

After you connect to an Integration Service using pmcmd, you can designate default folders or conditions to use each time the Integration Service executes a command. For example, if you want to issue a series of commands or tasks in the same folder, specify the name of the folder with the setfolder command. All subsequent commands use that folder as the default.

You can use the showsettings command to display the default settings.

Running in Wait Mode

You can run pmcmd in wait or nowait mode. In wait mode, pmcmd returns to the shell or command prompt after the command completes. You cannot run subsequent commands until the previous command completes.

For example, if you enter the following command, pmcmd starts the workflow “wf_SalesAvg” and does not return to the prompt until the workflow completes:

pmcmd startworkflow -sv MyIntService -d MyDomain -u seller3 -p jackson -f SalesEast -wait wf_SalesAvg

2

In nowait mode, pmcmd returns to the shell or command prompt immediately. You do not have to wait for one command to complete before running the next command.

For example, if you enter the following commands, pmcmd starts workflow “wf_SalesTotal” even if workflow “wf_SalesAvg” is still running:

pmcmd startworkflow -sv MyIntService -d MyDomain -u seller3 -p jackson -f SalesEast wf_SalesAvg

pmcmd startworkflow -sv MyIntService -d MyDomain -u seller3 -p jackson -f SalesEast wf_SalesTotal

By default, pmcmd executes commands in nowait mode.

You can configure the wait mode when you run in command line or interactive mode. In command line mode, use the -wait option to run a command in wait mode. In interactive mode, use the setwait or setnowait command before entering subsequent commands.

Scripting pmcmd Commands

When you use pmcmd, you might use some commands with specific options and arguments on a regular basis. For example, you might use pmcmd to check the status of the Integration Service. In this case, you can create a script or batch file to call one or more pmcmd commands including its options and arguments.

You can run scripts in command line mode. You cannot run pmcmd scripts in interactive mode.

For example, the following UNIX shell script checks the status of Integration Service “testService,” and if it is running, gets details for session “s_testSessionTask”:

#!/usr/bin/bash

# Sample pmcmd script

# Check if the service is alivepmcmd pingservice -sv testService -d testDomain

if [ "$?" != 0 ]; then # handle error echo "Could not ping service" exitfi

# Get service propertiespmcmd getserviceproperties -sv testService -d testDomain

if [ "$?" != 0 ]; then # handle error echo "Could not get service properties" exitfi

# Get task details for session task "s_testSessionTask" of workflow# "wf_test_workflow" in folder "testFolder"pmcmd gettaskdetails -sv testService -d testDomain -u Administrator -p adminPass -folder testFolder -workflow wf_test_workflow s_testSessionTask

if [ "$?" != 0 ]; then # handle error echo "Could not get details for task s_testSessionTask" exitfi

Entering Command Options

pmcmd provides multiple ways to enter some of the command options and arguments. For example, to enter a password, use the following syntax:

<<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>

To enter a password, precede the password with the -password or -p option:

-password The Password

or

-p The Password

If you use a password environment variable, precede the variable name with the -pv or -passwordvar

3

option:

-passwordvar PASSWORD

or

-pv PASSWORD

If a command option contains spaces, use single or double quotation marks to enclose the option. For example, use single quotes in the following syntax to enclose the folder name:

abortworkflow -sv MyIntService -d MyDomain -u seller3 -p jackson -f 'quarterly sales' -wait wf_MyWorkflow

To denote an empty string, use two single quotes ('') or two double quotes ("").

4

aborttask

Aborts a task. Issue this command only if the Integration Service fails to stop the task when you issue the stoptask command.

The aborttask command uses the following syntax in the command line mode:

pmcmd aborttask<<-service|-sv> service [<-domain|-d> domain] [<-timeout|-t> timeout]><<-user|-u> username|<-uservar|-uv> userEnvVar><<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>[<<-usersecuritydomain|-usd> usersecuritydomain|<-usersecuritydomainvar|-usdv> userSecuritydomainEnvVar>][<-folder|-f> folder]<-workflow|-w> workflow[<-runinsname|-rin> runInsName][-wfrunid workflowRunId][-wait|-nowait]taskInstancePath

The aborttask command uses the following syntax in the interactive mode:

aborttask[<-folder|-f> folder]<-workflow|-w> workflow[<-runinsname|-rin> runInsName][-wfrunid workflowRunId][-wait|-nowait]taskInstancePath

5

abortworkflow

Aborts a workflow. Issue this command only if the Integration Service fails to stop the workflow when you issue the stopworkflow command.

The abortworkflow command uses the following syntax in the command line mode:

pmcmd abortworkflow<<-service|-sv> service [<-domain|-d> domain] [<-timeout|-t> timeout]><<-user|-u> username|<-uservar|-uv> userEnvVar><<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>[<<-usersecuritydomain|-usd> usersecuritydomain|<-usersecuritydomainvar|-usdv> userSecuritydomainEnvVar>][<-folder|-f> folder][-wait|-nowait][<-runinsname|-rin> runInsName][-wfrunid workflowRunId] workflow

The abortworkflow command uses the following syntax in the interactive mode:

abortworkflow[<-folder|-f> folder][-wait|-nowait][<-runinsname|-rin> runInsName][-wfrunid workflowRunId] workflow

6

connect

Connects the pmcmd program to the Integration Service in the interactive mode. If you omit connection information, pmcmd prompts you to enter the correct information. Once pmcmd successfully connects, you can issue commands without reentering the connection information.

connect<<-service|-sv> service [<-domain|-d> domain] [<-timeout|-t> timeout]><<-user|-u> username|<-uservar|-uv> userEnvVar><<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>[<<-usersecuritydomain|-usd> usersecuritydomain|<-usersecuritydomainvar|-usdv> userSecuritydomainEnvVar>]

Note: Use this command in the pmcmd interactive mode only.

7

disconnect

Disconnects pmcmd from the Integration Service. It does not close the pmcmd program. Use this command when you want to disconnect from an Integration Service and connect to another in the interactive mode.

The disconnect command uses the following syntax in the interactive mode: