Today I was asked to analyze a series of reports to improve performance. All of the queries were built on a very simple Power Cube. Three dimensions and two measures. Dimensions are: Time, Plants, KPIs. Measures: Actual, Target

One of the things that struck me was the rampant use of string functions on attributes. The attributes of each KPI were used, and since it’s a Power Cube they couldn’t add any more. An example Long Name: 001 – Items Sold. In some reports they needed to show the entire Long Name, some they needed to show only the number, and some only the name itself.

A simple crosstab might have KPIs in the rows, plants in the column, actual as the measure and a specific month in the slicer. The MDX would appear as:

SELECT [Plant]..[Plant 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(0), [KPI]..[KPI 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(1), {[MEASURES]..[Actual Value]} DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(2) FROM [Global_Operations_KPIs] WHERE ([Date].[Current Month].[@MEMBER].[Current Month])

Simple and to the point. The problem happens when they want to truncate the Long Name of the KPI to show only the first 3 characters. They use the expression: substring([Cube].[KPI].[KPI].[KPI1].[KPI – Long Name],1,3) and the resulting MDX is:

SELECT [Plant]..[Plant1].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(0), [KPI]..[KPI1].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, [KPI].[KPI1].[substring(Cube_KPI_KPI_KPI1_KPI – Long Name from 1 for 3)0] ON AXIS(1), {[MEASURES]..[Actual Value]} DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(2) FROM [F:/Program Files/cognos/c8./bin../tempdmbTemp_2712_0002_12910169730.dmc] WHERE ([Date]..[Current Month (Root)].[Current Month])

Power Cubes don’t support string operations, so Cognos is dumping the resultset into a temp file. After it’s done dumping it into the file it runs Cognos SQL on top of it:

with
Query1_0_tab_oqpTabStream4 as
(select
Query1_0_tab_oqpTabStream.”Current Monthkey”  as  Current_Monthkey,
Query1_0_tab_oqpTabStream.”Current Month0″  as  Current_Month0,
Query1_0_tab_oqpTabStream.Plant0key  as  Plant0key,
Query1_0_tab_oqpTabStream.Plant1  as  Plant1,
Query1_0_tab_oqpTabStream.Plant1key  as  Plant1key,
Query1_0_tab_oqpTabStream.Plant10  as  Plant10,
Query1_0_tab_oqpTabStream.KPI0key  as  KPI0key,
Query1_0_tab_oqpTabStream.KPI1  as  KPI1,
Query1_0_tab_oqpTabStream.KPI1key  as  KPI1key,
Query1_0_tab_oqpTabStream.KPI10  as  KPI10,
Query1_0_tab_oqpTabStream.”substring(Cube_KPI_KPI_KPI1_KPI – Long Name from 1 for 3)01″  as  c11,
Query1_0_tab_oqpTabStream.”Actual Value”  as  Actual_Value
from
TABLE(_ROWSET(“Query1.0_tab_oqpTabStream”)) Query1_0_tab_oqpTabStream
)
select
Query1_0_tab_oqpTabStream4.Current_Monthkey  as  Current_Monthkey,
Query1_0_tab_oqpTabStream4.Current_Month0  as  Current_Month0,
Query1_0_tab_oqpTabStream4.Plant0key  as  Plant0key,
Query1_0_tab_oqpTabStream4.Plant1  as  Plant1,
Query1_0_tab_oqpTabStream4.Plant1key  as  Plant1key,
Query1_0_tab_oqpTabStream4.Plant10  as  Plant10,
Query1_0_tab_oqpTabStream4.KPI0key  as  KPI0key,
Query1_0_tab_oqpTabStream4.KPI1  as  KPI1,
Query1_0_tab_oqpTabStream4.KPI1key  as  KPI1key,
Query1_0_tab_oqpTabStream4.KPI10  as  KPI10,
substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3)  as  c11,
XSUM(Query1_0_tab_oqpTabStream4.Actual_Value  for Query1_0_tab_oqpTabStream4.Current_Monthkey,Query1_0_tab_oqpTabStream4.Current_Month0,Query1_0_tab_oqpTabStream4.Plant0key,Query1_0_tab_oqpTabStream4.Plant1,Query1_0_tab_oqpTabStream4.Plant1key,Query1_0_tab_oqpTabStream4.Plant10,Query1_0_tab_oqpTabStream4.KPI0key,Query1_0_tab_oqpTabStream4.KPI1,Query1_0_tab_oqpTabStream4.KPI1key,Query1_0_tab_oqpTabStream4.KPI10,substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3) )  as  Actual_Value
from
Query1_0_tab_oqpTabStream4
group by
Query1_0_tab_oqpTabStream4.Current_Monthkey,
Query1_0_tab_oqpTabStream4.Current_Month0,
Query1_0_tab_oqpTabStream4.Plant0key,
Query1_0_tab_oqpTabStream4.Plant1,
Query1_0_tab_oqpTabStream4.Plant1key,
Query1_0_tab_oqpTabStream4.Plant10,
Query1_0_tab_oqpTabStream4.KPI0key,
Query1_0_tab_oqpTabStream4.KPI1,
Query1_0_tab_oqpTabStream4.KPI1key,
Query1_0_tab_oqpTabStream4.KPI10,
substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3)

That’s a lot of effort for a simple substring.

Instead they could have used a Report Expression. On the crosstab node change the Source Type from Data Item Value to Report Expression. Use the expression: substring([Query1].[KPI – Long Name],1,3) remembering to add Long Name to the properties of that row.

When Cognos executes the report the MDX is untouched. Cognos will only execute the substring function when it draws the results on the crosstab. This allows for much more efficient reports, while still providing you with the results you expect.