分享程序员开发的那些事...
更新时间：2023-01-29 11:36:33
Cognos BI使用动态查询模式；默认情况下（从10.2.1开始）。它基本上是Cognos的查询引擎。引擎计划执行，该执行可以包括在从数据源接收到数据之后本地处理数据。您可以在IBM的免费红皮书中阅读所有相关内容
http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf
因此，这意味着本地查询并不完整图片。从MS SQL接收数据后，Cognos可能会进行更多处理。话虽如此，如果您想在MS SQL中复制相同的查询，则需要自己完成缺失的逻辑。如果将Cognos中的多维模型按1：1映射到MS SQL关系数据库，那么这应该很容易（将丢失的部分从Cognos SQL自己转换为MS SQL）。很多时候，并不是很直接b / c Cognos内部的模型可能不是1：1映射到MS SQL表/列。在这种情况下，将Cognos查询转换为MS SQL查询b / c时，您将需要了解Cognos内部的模型，这是本机查询和Cognos查询之间发生的事情的一部分。
我可以对于您的模型部分没有任何帮助，但对于其余部分，您可以首先尝试更改查询（或数据源）的汇总处理，属性数据库。这应该有助于将扩展的聚合函数（XCOUNT等）转换为本机SQL。还要检查使用SQL参数属性并将其设置为文字并查看这是否会对本机SQL中的参数有所帮助。
rank（）函数的语法与MS SQL中的语法几乎相同，因此，一旦保重，您应该能够轻松地处理该问题。功能扩展。但是我先来看一下新的本机查询。
RCount（运行计数）也可以使用窗口函数在MS SQL中轻松处理（请参见下面的链接）。但是再次，首先生成新的本机查询，然后查看RCount是否消失。
I'm trying to convert IBM Cognos SQL which contains a filter to Microsoft SQL Server Query.
I tried to apply the filter in the Microsoft SQL Server Query, but it didn't work.
Here the IBM Cognos Query:
IBM Cognos SQL
select
rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id for client__iacode.ia_code ) desc nulls last) as Rank_IA,
client__iacode.ia_code as IA_Code,
client__iacode.ia_short_descr as IA_Short_Descr,
XCOUNT(client_document.client_document_id for client__iacode.ia_code,client__iacode.ia_short_descr ) as Doc_Count,
XCOUNT(client.client_code at client__iacode.ia_code,client__iacode.ia_short_descr,client.client_id for client__iacode.ia_code,client__iacode.ia_short_descr ) as Client_Count,
XSUM(XCOUNT(client_document.client_document_id for client__iacode.ia_code,client__iacode.ia_short_descr ) at client__iacode.ia_code,client__iacode.ia_short_descr ) as Total_Doc_Count_,
XSUM(XCOUNT(client.client_code at client__iacode.ia_code,client__iacode.ia_short_descr,client.client_id for client__iacode.ia_code,client__iacode.ia_short_descr ) at client__iacode.ia_code,client__iacode.ia_short_descr ) as Total_Client_Count_
from
(
dbo.client client
join
dbo.broker client__broker
on (client.broker_id = client__broker.broker_id)
join
dbo.ia_code client__iacode
on (client.ia_code_id = client__iacode.ia_code_id)
join
dbo.client_document client_document
on (client.client_id = client_document.client_id)
join
dbo.client_status client_status
on (client.client_status_id = client_status.client_status_id)
join
dbo.provider client__provider
on (client__provider.provider_id = client.provider_id)
)
left outer join
dbo.branch client__iacode__branch
on (client__iacode.branch_id = client__iacode__branch.branch_id)
where
(client_document.requested_date is not NULL) and
(client_document.received_date is NULL) and
(client__iacode__branch.branch_descr = CAST(:PQ1 AS varchar(255))) and
(client__broker.broker_code = 'CCC') and
(client_status.client_status_code = 'A') and
((client__provider.provider_code <> 'PRS-R') or (client__provider.provider_code is NULL))
group by
client__iacode.ia_code,
client__iacode.ia_short_descr
filter
(rank() over ( at client__iacode.ia_code order by XCOUNT(client.client_code at client__iacode.ia_code,client.client_id for client__iacode.ia_code ) desc nulls last) <= 25) and
(RCOUNT(rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id for client__iacode.ia_code ) desc nulls last) at client__iacode.ia_code order by rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id for client__iacode.ia_code ) desc nulls last) asc,client__iacode.ia_code asc,client__iacode.ia_short_descr asc ) <= 25)
order by
Rank_IA asc,
IA_Code asc,
IA_Short_Descr asc
Here the Native SQL Query from the IBM Cognos report studio:
select "client__iacode"."ia_code" AS "C0", "client_documen
t"."client_document_id" AS "C1", "client__iacode"."ia_short_descr" AS "C2", "client"."client_id" AS "C3", "client"."client_code" AS "C4"
from ((((("dbo"."client" "client" INNER JOIN "dbo"."broker" "client__broker" on "client"."broker_id" = "client__broker"."broker_id") INNER JOIN "dbo"."ia_code" "client__iacode" on "client"."ia_code_id" = "client__iacode"."ia_code_id") INNER JOIN "dbo"."client_document" "client_document" on "client"."client_id" = "client_document"."client_id") INNER JOIN "dbo"."client_status" "client_status" on "client"."client_status_id" = "client_status"."client_status_id") INNER JOIN "dbo"."provider" "client__provider" on "client__provider"."provider_id" = "client"."provider_id") LEFT OUTER JOIN "dbo"."branch" "client__iacode__branch" on "client__iacode"."branch_id" = "client__iacode__branch"."branch_id"
where NOT "client_document"."requested_date" is null and "client_document"."received_date" is null and "client__iacode__branch"."branch_descr" = CAST( @BRANCH AS VARCHAR( 255 ) ) and "client__broker"."broker_code" = 'CCC' and "client_status"."client_status_code" = 'A' and ("client__provider"."provider_code" <> 'PRS-R' or "client__provider"."provider_code" is null)
Here the Microsoft SQL Server Query that I have created so far but it didn't shows me the expected result:
select L1.*
from
(
select L.Rank_IA,
L.IA_Code,
L.IA_Short_Descr,
L.Doc_Count,
L.Client_Count,
L.[Rank],
(count (L.Condition) over( order by L.Condition asc, L.IA_Code asc, IA_Short_Descr asc )) as Condition
from
(
select
rank() over ( order by COUNT(client_document.client_document_id ) desc ) as Rank_IA,
client__iacode.ia_code as IA_Code,
client__iacode.ia_short_descr as IA_Short_Descr,
COUNT( client_document.client_document_id ) as Doc_Count,
COUNT(client.client_code ) as Client_Count,
(rank() over ( order by COUNT( client.client_id ) desc ) ) as [Rank],
rank() over ( order by COUNT( client_document.client_document_id ) desc ) as Condition
--XSUM(XCOUNT(client_document.client_document_id for client__iacode.ia_code,client__iacode.ia_short_descr ) at client__iacode.ia_code,client__iacode.ia_short_descr ) as Total_Doc_Count_,
--XSUM(XCOUNT(client.client_code at client__iacode.ia_code,client__iacode.ia_short_descr,client.client_id for client__iacode.ia_code,client__iacode.ia_short_descr ) at client__iacode.ia_code,client__iacode.ia_short_descr ) as Total_Client_Count_
from
(
dbo.client client
join
dbo.broker client__broker
on (client.broker_id = client__broker.broker_id)
join
dbo.ia_code client__iacode
on (client.ia_code_id = client__iacode.ia_code_id)
join
dbo.client_document client_document
on (client.client_id = client_document.client_id)
join
dbo.client_status client_status
on (client.client_status_id = client_status.client_status_id)
join
dbo.provider client__provider
on (client__provider.provider_id = client.provider_id)
)
left outer join
dbo.branch client__iacode__branch
on (client__iacode.branch_id = client__iacode__branch.branch_id)
where
(client_document.requested_date is not NULL) and
(client_document.received_date is NULL) and
(client__iacode__branch.branch_descr = CAST(@BRANCH AS varchar(255))) and
(client__broker.broker_code = 'CCC') and
(client_status.client_status_code = 'A') and
((client__provider.provider_code <> 'PRS-R') or (client__provider.provider_code is NULL))
group by
client__iacode.ia_code,
client__iacode.ia_short_descr)L)L1
--filter
-- (rank() over ( at client__iacode.ia_code order by XCOUNT(client.client_code at client__iacode.ia_code,client.client_id for client__iacode.ia_code ) desc nulls last) <= 25) and
-- (RCOUNT(rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id for client__iacode.ia_code ) desc nulls last) at client__iacode.ia_code order by rank() over ( at client__iacode.ia_code order by XCOUNT(client_document.client_document_id for client__iacode.ia_code ) desc nulls last) asc,client__iacode.ia_code asc,client__iacode.ia_short_descr asc ) <= 25)
where L1.[Rank] <=25
and L1.Condition<=25
order by
Rank_IA asc,
IA_Code asc,
IA_Short_Descr asc
Can you please help me to apply that filter in the Microsft SQL Server code. Thanks in advance for the help.
Cognos BI uses "dynamic query mode" by default (starting with 10.2.1). It's basically Cognos' query engine. The engine plans an execution which may include processing data locally after it is received from the data source. You may read all about it in IBM's free redbook here
http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf
Here is a quick overview of the engine
So, that means the native query is not the whole picture. Cognos may do more processing after receiving the data from MS SQL. That being said, if you would like to replicate the same query in MS SQL, you will need to complete the missing logic yourself. If the multi-dimensional model in Cognos is mapped to MS SQL relational database on 1:1, then that should be easy (convert missing parts from Cognos SQL to MS SQL yourself). Lots of the time, it is not that straight forward b/c the model inside Cognos may not be 1:1 mapping to MS SQL tables/columns. In that case, you will need to understand the model inside Cognos when converting Cognos query to MS SQL query b/c that's part of what happens between native query and Cognos query.
I can't be of any help on your model part but for the rest, you can first try changing the query's (or data source's) "Rollup Processing" property to "Database". That should help converting extended aggregate functions (XCOUNT etc) to native SQL. Also check out "Use SQL parameters" property and set it to "Literal" and see if that will helps with the parameters in native SQL.
Screenshot of the properties window
The rank() functions syntax is pretty much the same syntax in MS SQL so you should be able to handle that easily once you took care of extended functions. But I would look at the new native query first.
RCount (running count) can also be easily handled in MS SQL using windowing functions (see link below). But again, first generate the new native query and see if RCount is gone or not.