利用Cognos Transformer工具创建模型文件的时候,首先需要加载数据源,并且OLAP的时候,也需要大量时间从数据仓库中读取数据,因此,建模的时候务必要对数据源做好充分的准备。
Cognos Transformer工具支持多种数据源,这里我们仅以其常用的IQD文件做说明。
以下是一个简单的IQD文件的源文
COGNOS QUERY
STRUCTURE,1,1
DATABASE,NEDW
TITLE,[NEDW].[F_SEM_Response]
BEGIN SQL
{
SELECT
F_SEM_Response.ReportDate AS ReportDate,
F_SEM_Response.CampaignID AS CampaignID,
F_SEM_Response.Responses AS HBX_Response,
D_SEM_Campaign_List.CampaignName AS CampaignName,
D_SEM_Campaign_List.Type_1 AS Type_1,
D_SEM_Campaign_List.Type_2 AS Type_2
FROM NEDW.dbo.F_SEM_Response F_SEM_Response
LEFT OUTER JOIN NEDW.dbo.D_SEM_Campaign_List D_SEM_Campaign_List on F_SEM_Response.CampaignID = D_SEM_Campaign_List.CampaignID
WHERE F_SEM_Response.ReportDate='2007-05-01'
}
END SQL
COLUMN,0,ReportDate
COLUMN,1,CampaignID
COLUMN,2,HBX_Response
COLUMN,3,CampaignName
COLUMN,4,Type_1
COLUMN,5,Type_2
IQD文件可以使用Cognos提供的Impromptu或FrameWork Manager工具来制作,但与上文的IQD文件相比,用Impromptu或FrameWork Manager工具生成的IQD文件相对烦琐,达不到最优化的目的,影响数据读取速度,因此,不建议用Impromptu或FrameWork Manager工具生成IQD文件,而可以直接在{}内书写标的SQL语句,Cognos不会对{}中的sql语句进行解晰或校验,而是直接传递给连接的数据库引擎,数据库本身执行解晰、优化及查询计划。
为了能够清晰的了解IQD文件的结构,现对其说明如下:
COGNOS QUERY
COGNOS是固定关键字,QUERY是IQD文件的类型,Cognos在解晰的时候将会严格解晰这两个关键字,因此,只要是连接数据库,这两个关键字是固定的。
STRUCTURE,1,1
STRUCTURE是固定关键字,不可改变,后面的数字不做严格要求。
DATABASE,NEDW
DATABASE是固定关键字,不可改变,NEDW是OLAP Server需要连接的数据库,此数据库的连接串必须包含在Cognos.ini文件中.
TITLE,[NEDW].[F_SEM_Response]
TITLE是固定关键字,不可改变,后面的字符串不做严格要求。
BEGIN SQL
{标准SQL脚步本}
END SQL
COLUMN,0,ReportDate
输出栏位信息。值得注意的是输出栏位的索引值是从0开始,其输出的顺序与字段名称没有关系,而是由SQL语句中字段排列的顺序。
如果SQL语句中输出的栏位多于IQD文件中的输出数量,也不会输出多余的字段,反之亦然。
例如:
(1)
COGNOS QUERY
STRUCTURE,1,1
DATABASE,NEDW
TITLE,[NEDW].[F_SEM_Response]
BEGIN SQL
{
SELECT
F_SEM_Response.ReportDate AS ReportDate,
F_SEM_Response.CampaignID AS CampaignID,
F_SEM_Response.Responses AS HBX_Response,
D_SEM_Campaign_List.CampaignName AS CampaignName,
D_SEM_Campaign_List.Type_1 AS Type_1,
D_SEM_Campaign_List.Type_2 AS Type_2
FROM NEDW.dbo.F_SEM_Response F_SEM_Response
LEFT OUTER JOIN NEDW.dbo.D_SEM_Campaign_List D_SEM_Campaign_List on F_SEM_Response.CampaignID = D_SEM_Campaign_List.CampaignID
WHERE F_SEM_Response.ReportDate='2007-05-01'
}
END SQL
COLUMN,0,A
COLUMN,1,B
COLUMN,2,C
COLUMN,3,D
COLUMN,4,E
COLUMN,5,F
Cognos Transformer加载上述IQD文件后,在Transformer的Datasource View的视窗中看到的栏位名称是A、B、C、D、E、F。实际的映身关系是:
A->ReportDate
B->CampaignID
C->HBX_Response
D->CampaignName
E->Type_1
F->Type_2
(2)
COGNOS QUERY
STRUCTURE,1,1
DATABASE,NEDW
TITLE,[NEDW].[F_SEM_Response]
BEGIN SQL
{
SELECT
F_SEM_Response.ReportDate AS ReportDate,
F_SEM_Response.CampaignID AS CampaignID,
F_SEM_Response.Responses AS HBX_Response,
2.5 AS UnitPrice,
D_SEM_Campaign_List.CampaignName AS CampaignName,
D_SEM_Campaign_List.Type_1 AS Type_1,
D_SEM_Campaign_List.Type_2 AS Type_2
FROM NEDW.dbo.F_SEM_Response F_SEM_Response
LEFT OUTER JOIN NEDW.dbo.D_SEM_Campaign_List D_SEM_Campaign_List on F_SEM_Response.CampaignID = D_SEM_Campaign_List.CampaignID
WHERE F_SEM_Response.ReportDate='2007-05-01'
}
END SQL
COLUMN,0,ReportDate
COLUMN,1,CampaignID
COLUMN,2,HBX_Response
COLUMN,3,CampaignName
COLUMN,4,Type_1
COLUMN,5,Type_2
Cognos Transformer加载上述IQD文件后,在Transformer的Datasource View的视窗中看到的栏位名称是ReportDate、CampaignID、HBX_Response、CampaignName、Type_1、Type_2。实际的映身关系是:
ReportDate->ReportDate
CampaignID->CampaignID
HBX_Response->HBX_Response
CampaignName->UnitPrice(2.5)
Type_1->CampaignName
Type_2->Type_1
源数据只含有模型所需的信息,将会加快Transformer 读取数据的时间。如果在数据源中包含了一些没有用的列,那么即使在模型中没有用到这些列,Transformer 也会花费额外的时间对它们进行处理。因此,满足需求的情况下Transformer读取的记录数量越少,PowerCube生成的时间也就越短。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7477027/viewspace-582232/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7477027/viewspace-582232/