GENERATE DATE / TIME DIMENSION IN INFORMATICA-白红宇

GENERATE DATE / TIME DIMENSION IN INFORMATICA

阅读量：4209 次

发布时间：2019-05-26

本文共 3050 字，大约阅读时间需要 10 分钟。

有人能解释一下下面的SQL么? 查过文献没找到答案.

SELECT LEVEL

FROM dual

CONNECT BY LEVEL < 10

结果是:

level 是个伪列，类似于rownum

connect by 构造了一个循环

好. 如下也有同样结果:

SELECT ROWNUM

FROM dual

CONNECT BY ROWNUM < 10

另外

SELECT LEVEL

FROM dual

CONNECT BY 1=1

会得到无穷数列.

终于有点理解的感觉了. 谢.

We use database procedures to generate the date dimension for data warehouse applications. Here i am going to show you how to generate the date dimension in informatica.

Let see how to generate list out all the days between two given dates using oracle sql query.

SELECT  to_date('01-JAN-2000','DD-MON-YYYY') + level-1 calendar_dateFROM    dualconnect by level <=         (            to_date('31-DEC-2000','DD-MON-YYYY') -            to_date('01-JAN-2000','DD-MON-YYYY') + 1        );Output:CALENDAR_DATE-------------1/1/20001/2/20001/3/2000...12/31/2000

Now we can apply date functions on the Calendar date field and can derive the rest of the columns required in a date dimension.

We will see how to get the list of days between two given dates in informatica. Follow the below steps for creating the mapping in informatica.

Create a source with two ports ( Start_Date and End_Date) in the source analyzer.

Create a new mapping in the mapping designer Drag the source definition into the mapping.

Create the java transformation in active mode.

Drag the ports of source qualifier transformation in to the java transformation.

Now edit the java transformation by double clicking on the title bar and go to the "Java Code" tab. Here you will again find sub tabs. Go to the "Import Package" tab and enter the below java code:

import java.text.DateFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;

Not all these packages are required. However i included just in case if you want to apply any formatting on dates. Go to the "On Input Row" tab and enter the following java code:

int num_days = (int) ((End_Date - Start_Date) / (1000 * 60 * 60 * 24));long Start_Seconds = Start_Date;for (int i=1; i <= num_days ; i++){ if (i == 1) { generateRow(); } else { Start_Date = Start_Date + (1000 * 60 * 60 * 24); generateRow(); }}Start_Date = Start_Date + (1000 * 60 * 60 * 24);generateRow();

Compile the java code by clicking on the compile. This will generate the java class files.

Connect only the Start_Date output port from java transformation to expression transformation.

Connect the Start_Date port from expression transformation to target and save the mapping.

Now create a workflow and session. Enter the following oracle sql query in the Source SQL Query option:

SELECT to_date('01-JAN-2000','DD-MON-YYYY') Start_Date,       to_date('31-DEC-2000','DD-MON-YYYY') End_DateFROM   DUAL;

Save the workflow and run. Now in the target you can see the list of dates loaded between the two given dates.

Note1

: I have used relational table as my source. You can use a flat file instead.

Note2

: In the expression transformation, create the additional output ports and apply date functions on the Start_Date to derive the data required for date dimension.

转载地址：http://rzrli.baihongyu.com/

你可能感兴趣的文章