GENERATE DATE / TIME DIMENSION IN INFORMATICA

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

SELECT LEVEL
FROM dual
CONNECT BY LEVEL < 10

结果是:
1                                   
2                                   
3                                   
4                                   
5                                   
6                                   
7                                   
8                                   
9

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_date
FROM    dual
connect by level <= 
        ( 
           to_date('31-DEC-2000','DD-MON-YYYY') - 
           to_date('01-JAN-2000','DD-MON-YYYY') + 1
        );

Output:

CALENDAR_DATE
-------------
1/1/2000
1/2/2000
1/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_Date
FROM   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.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值