In your project Which Case you will go for star schema designing and which Case you will go for snow flake schema designing?

Purba

• Jul 4th, 2016

Snowflake schema is used for dimension Analysis. Eg: How many online accounts does an advertiser have.

Star schema is used for metric analysis. Eg: What is the revenue of a customer.

Ram

• Jun 30th, 2016

Star schema --> Fact table is connect with different Dimension tables.
Snowflake schema --> Fact table is connect with different dimension tables. And dimension table/tables refer to another dimensional table.

Thanks !

Answer Question  Select Best Answer

can anyone pls solve in datastage.........
source column is..
A
A
B
B
B
C
C
D
........want output columns(2 cols) as.........
A 1
A 2
B 1
B 2
B 3
C 1
C 2
D 1

THANX IN ADVANCE.

Amulya Kumar Panda

• Jul 11th, 2016

Job flow sequential_file-> eggregate->sort->tranformer-> sequential

fast aggregate stage use count rows and add extra column seq_no
next stage sort -key source column sort order ascending.
tranformer stage use loop while ex-@ITERATION

Kumar

• Jul 8th, 2016

Seq file>transformer>Seq file
1.Mode of execution in transformer must be sequential and data must be sorted on col1
2. Transformer logic:
Stv1=Input column
Stv2=if Stv1=Stv3 Then Stv2+1 Else 1
Stv3=Stv1
3.Output of transformer
Col1,Col2
Input column,Stv2

Answer Question  Select Best Answer

How will you separate the records with special characters in a column. For example
If suppose the column contains
Name*
&Name
Na*me
$Name
Na$me

I need to separate these records alone.

Shrushti

• Jul 16th, 2016

Source - transformer - target.
In transformer,
Namesp --- If alphanum(Name) then 1 else 0
Constraint --- Namesp=0

Answer Question  Select Best Answer

We are using Bulk Collect methodology in Database to load the bunch of records at a time.How we will implement the same operation in Datastage. If my source having millions of records, I want to load the bunch of records in to target at a time. How?

Sri

• Jul 20th, 2016

Increase the array size in Target Table stage. Also use Parallel Hints while selecting the records.

Pavan Kumar

• Jul 8th, 2016

You can load the bunch of files at a same time using sequential file stage. In options just select the file location the DB and change the option in the seq file stage to "File Pattern".

Answer Question  Select Best Answer

In Data warehouse which table will load first and why? Fact or Dimension?

Sri

• Jul 20th, 2016

Dimension table will load first and then fact tables.

Ram

• Jun 30th, 2016

Hi Aloka,
Fact table is connect with different dimension tables with foreign key relationship. Fact table having only Ids & Measures. Dimension tables having detailed descriptions of the Ids.

Thank you !

Answer Question  Select Best Answer

How to generate the occurrence no in out put file for the respective no of records in input file
I need a count in the target file how many times the same record is available in input file.

INPUT
-----

ID NAME LOC

20 B Y
10 A X
20 B Y
30 C Z
10 A X
10 A X
20 B Y
20 B ...

Aloka

 Profile Answers by Aloka Questions by Aloka

• Jul 22nd, 2016

Thanks Sri for your reply. After using 3 stage variable in xfm we can generate the count of the respective record.But after that i need no remove the duplicates to get the unique respective record in...

Sri

• Jul 20th, 2016

First sort the incoming rows by ID, NAME and LOC.
Then take three stage variables in the transformer

StageVar=inputcol
StageVar1=Stagevar
StageVar2=if StageVar=StageVar1 then StageVar2+1 else 1
map the StageVar2 to the output link column

Answer Question  Select Best Answer

Can we change the configuration file during run time? If then how?

hema

• Aug 3rd, 2016

First we need to add the environment variable(apt_config_file) to the job parameters

Answer Question  Select Best Answer

I have a source file having 4 columns. How can I store first 2 columns values into one target and the next 2 columns values into another target? Can anyone suggest me here how can I achieve this using Copy stage and without using Copy Stage?

Ram

• Aug 4th, 2016

Hi

you can try with 1) copy stage & 2) transformer stage

Thanks!

Answer Question  Select Best Answer

I have an input file in below format
Name DOB
------ ------
A 10-05-1990
B 07-12-2000

Q1 -> How we will load the above file data into a target data base table in a simplest method without using Transformer ?

Q2 -> How will load the Date column into 3 splitted columns(DD|MM|YYYY). Like below
Name DOB
------ -------

amulya panda

• Aug 3rd, 2016

Use in transformer stage and use Ereplace(SOURCE_COLUMN_NAME,"-","|")

Gagandeep Singh

• Jul 29th, 2016

Define delimiter as - in Sequential file stage

Answer Question  Select Best Answer

We are using below command in Unix to run a Datastage Job:
dsjob run mode project_name job_name
When we are executing the above command in Unix what exactly it returns?

Aloka

 Profile Answers by Aloka Questions by Aloka

• Aug 8th, 2016

Hi,
May be a question for reviewers who wanted to know more on this.You can leave it.
Thanks !

Ram

• Aug 4th, 2016

Hi,
is it question for reviewers or your doubt ?
you got answer if you run the command.

Thanks !

Answer Question  Select Best Answer

I have 5 source files. In target I need to write it into a single file. But in the output file I need the corresponding input file name i.e which records are coming from which source file. How to achieve this?

sreeni

• Aug 8th, 2016

Hi Aloka we can use seq file properties -> Options -> File Name Column = FileNameCol
you will get source file name to each column. You can identify easily...

Ram

• Aug 4th, 2016

Hi
Use file name column option in seq file stage & do funnel then you will get required output.

Thanks !

Answer Question  Select Best Answer

Link Partitioner - Used for partitioning the data.Link Collector - Used for collecting the partitioned data.

Read Best Answer

Editorial / Best Answer

nikhilanshuman  

 Profile Answers by nikhilanshuman Questions by nikhilanshuman

• Member Since Nov-2009 | Dec 17th, 2010

---

Link Partitioner and collecter are basically used to introduce data parallellism in server jobs.link partitioner,splits the data on many links.Once the data is processed,link collector collects the data and passes it to a single link.These are used in server jobs.In datastage parallel jobs,these things are inbuilt and automatically taken care of.

karthik

• Aug 23rd, 2016

Link Partition and Link collector are used in Server jobs for to split data.
Partition technique can be done in server jobs by using these two stages.

nikhilanshuman

 Profile Answers by nikhilanshuman Questions by nikhilanshuman

• Dec 17th, 2010

Link Partitioner and collecter are basically used to introduce data parallellism in server jobs.link partitioner,splits the data on many links.Once the data is processed,link collector collects the da...

Improve Answer

How to handle rejects in Transformer & How many rejects a xfm support?

vibgyss

• Aug 24th, 2016

Transformer
The transformer only rejects the data of a column which has nulls and that column is used in the expression then that type data is rejected by transformer stage

No other data is rejected by Transformer Stage
Only one reject link supported by transformer

Answer Question  Select Best Answer

What is the minimum number of input and output required for Lookup ?

vibgyss

• Aug 24th, 2016

Min of 2 inputs is required by lookup stage and 1 output link is mandatory for any type of lookup i.e. we have 4 types of lookups from 8.0 version those Normal lookup, sparse lookup (only supports 2 inputs one is primary and second is reference), range lookup, caseless lookup

Answer Question  Select Best Answer

I have a file having columns C1,C2,C3,C4,C5 with comma delimited.In target I want store the first two columns value into one column.

Like below

Input Output I need
-------------- ----------------------
C1 C2 C3 C4 C5 C1(C1 & C2 value) C3 C4 C5

That means my input is 5 columns & Output will be 4 columns.
Can...

Chikka

• Aug 27th, 2016

Create a new stage variable and using concatenation operator combine first two columns data and map it to a column.

subbu

• Aug 26th, 2016

You can try using copy & tx stages

Answer Question  Select Best Answer

Without using Sequencer how to design a Datastage job which normally receives input file to run. If tomorrow we will not receive any input file Job should not fail. It will run successfully without any warnings.

Ram

• Sep 6th, 2016

Hi Aloka,

you can achieve this by using unix shell scripting and schedule the job/script in any third party scheduling tool.

Thanks !

suresh

• Sep 2nd, 2016

Using JCL (Job Control Language) we can achieve this.

Answer Question  Select Best Answer

Suppose I have designed a Datastage job using sequential file stage,It will run in sequential mode.How can I run it it Parallel mode.

Suresh

• Sep 13th, 2016

Aloka, There are two ways in your example. In sequential file stage, number of readers per node and multiple nodes. These options will run an sequential stage in parallel.

Sandhyaram

• Jul 24th, 2016

In sequential file stage, we have two options No. of readers per node and reading from multiple nodes. We can run job in parallel mode.

Answer Question  Select Best Answer

I have 10 files, in 5 files having same data(3 columns), 5 columns having same data(4 columns). How can I perform funnel and what are stages required from me?

Srinivas

• Oct 7th, 2016

Use column generator to add columns to make the column count is same and also the column names should be same in each files. Then you can perform funnel.

Answer Question  Select Best Answer

Sequencers are job control programs that execute other jobs with preset Job parameters.    

Read Best Answer

Editorial / Best Answer

jagannimmagadda  

 Profile Answers by jagannimmagadda Questions by jagannimmagadda

• Member Since Jan-2006 | Jan 10th, 2006

---

A sequencer allows you to synchronize the control flow of multiple activities in a job sequence. It can have multiple input triggers as well as multiple output triggers.The sequencer operates in two modes:ALL mode. In this mode all of the inputs to the sequencer must be TRUE for any of the sequencer outputs to fire.ANY mode. In this mode, output triggers can be fired if any of the sequencer inputs are TRUE

Tariq Awan

• Oct 25th, 2016

As previous posts already pointed out, there are only two modes: All or Any. All means all incoming triggers must be true and Any means at least one needs to be true. However, in case, if you want to execute next job even though all the incoming triggers are False, then Seqencer is NOT the solution.

Priyanka

• Mar 10th, 2016

@Rohit: Yes you can run the 2nd job even if the first one fails. For that you need to set the trigger option to unconditional. However, the control comes to the 2nd job only after the 1st job has run (pass or fail).

Improve Answer

Why we are using orchadmin to remove dataset. why we are not using rm commond in remove dataset..and why you need dataset remove waht is the reason?

Reddy

• Dec 19th, 2016

Dataset is not like a normal file & it is not a single file...based on number og node in config file, those many data files and one descriptor file..are created for each dataset. Hence removing using rm command will corrupt the dataset and will not be removed properly.

Answer Question  Select Best Answer