BIEE使用体验 – 关于时间格式

  我们数据库有很多关于时间的字段，都是以DATE格式存储的。

这些DATE的数据中有些是到年月日，有些是具体到时分秒的. 当我们将表导入 BIEE物理层的时候,发现含有时分秒 的数据都会默认转变为 timestamp 类型.

为什么biee 要强制转换为 timestamp 类型,我还不得而知,但是在报表开发中的确带来了一些问题. 

问题之一:我们在Administrator中创建过一些变量,希望给这些变量赋值为 前一天 或前一周这样的动态的时间.以便应用在报表提示中固定查询之前一段时间的数据.

在变量的定义中使用了select trunc(sysdate)-1 from dual 这样的写法. 结果发现,转换成的sql语句是这样的 trade_day>TIMESTAMP ’2008-02-19 00:00:00′. BIEE将时间格式都变为TIMESTAMP型了. 

而我们这个表由于数据量非常大,按照date类型日期进行了分区.现在由于条件日期是TIMESTAMP型,这样一来,查询语句就不能使用现成的分区,查询速度变的很慢很糟糕. 

如何让查询中日期条件不要转换为TIMESTAMP格式呢,在多次试验后发现,使用biee提供的函数CURRENT_DATE,将变量 的写法改为CURRENT_DATE  – 1 ,则查询语句就成了 trade_day>TO_DATE ‘2007-01-01’. 几秒钟之内就查询出了数据. 

如果查询提示中 不事先设定变量,而是接收用户前端传过来的日期条件,则查询语句没有这个问题.

问题二:我们有不少查询 是要用明细数据表 将时间按天进行group by 来做汇总计算的, 我们在逻辑层中建立了一个‘天‘的实体,在表达式定义中使用trunc(trade_day)的写法希望截取到天, 写法却不被BIEE识别.

 我们试着将物理层中的 trade_day 这列改为date类形,但发现biee并没有根据这个设置做任何转换, group by 语句还是对含有时分秒的数据进行group by

反复尝试,碰了很多钉子后,发现在逻辑表中‘天‘这个 实体的表达式中 使用CAST(FACT_DETAIL.trade_day AS DATE ) 这种BIEE自带函数的写法,将日期截取到天,biee生成的查询语句就按照我们想象的 成为 group by trunc(trade_day)了.

这个问题还有其他的解决方法,比如 在物理层建立一个 sql类型的表,用将sql语句的结果映射成一个表的方式.这样 ,在sql中就可以使用oracle函数对字段进行转换, 例如trunc. 不过,这种方法在报表中生成的sql并不够优化 . 

 

或者另一种方法是通过物理层将维表和事实表的时间字段 建立关系的时候选择复杂关系。然后通过维表的天来聚合统计数据.