OLAP Adapters

You can use the DB_EXPR function to generate a query-scope calculated measure as an MDX expression. The result of that expression must be type value. In the calculated measure expression, you can use any MDX function.

You are responsible for how the expression will work in the context of the whole generated MDX query. For example, note that if the expression references the [Calendar] hierarchy, the result will depend on which (if any) level of that hierarchy is referenced in a BY clause of the TABLE request. If the level is not taken into account, the query may produce unexpected results or errors.

DEFINE FILE ADVENTURE_WORKS_MIXED_CASE
ISA_YTD/D20.2 WITH Internet_Sales_Amount =   
DB_EXPR(Sum(Ytd( [Date].[Calendar].CURRENTMEMBER ),   
[Measures].[Internet Sales Amount]));   
END 
TABLE FILE ADVENTURE_WORKS_MIXED_CASE   
SUM Internet_Sales_Amount 
ISA_YTD   
BY Fiscal_Year NOPRINT 
BY Fiscal_Quarter   
ON TABLE SET PAGE NOPAGE 
END 

WITH 
MEMBER [Measures].[ISA_YTD] as ' 
Sum(Ytd( [Date].[Calendar].CURRENTMEMBER ),                              
[Measures].[Internet Sales Amount])' 
SET H24 as ' 
[Date].[Calendar].[Calendar Quarter].ALLMEMBERS' 
SET H33 as ' 
[Date].[Fiscal].[Fiscal Year].ALLMEMBERS' 
SELECT { 
[Measures].[Internet Sales Amount], 
[Measures].[ISA_YTD]} 
ON AXIS(0), 
NON EMPTY CROSSJOIN(H24, 
H33) 
DIMENSION PROPERTIES 
MEMBER_CAPTION 
,[Date].[Calendar].[Calendar Quarter].[Fiscal Quarter] ON AXIS(1) 
FROM [Adventure Works] 

DEFINE FILE ADVENTURE_WORKS_MIXED_CASE
I_S_A_4_MONTHS_ROLLING_AVERAGE/D20.2M 
WITH Internet_Sales_Amount = 
DB_EXPR(Avg({ [Date].[Calendar].CURRENTMEMBER.Lag(3) 
: [Date].[Calendar].CURRENTMEMBER }, 
[Measures].[Internet Sales Amount]));
END 
TABLE FILE ADVENTURE_WORKS_MIXED_CASE   
SUM Internet_Sales_Amount 
I_S_A_4_MONTHS_ROLLING_AVERAGE AS 'Rolling Average'
BY Fiscal_Year NOPRINT 
BY Fiscal_Quarter   
ON TABLE SET PAGE NOPAGE 
END 

WITH 
MEMBER [Measures].[I_S_A_4_MONTHS_ROLLING_AVERAGE] as ' 
Avg({ [Date].[Calendar].CURRENTMEMBER.Lag(3)                             
: [Date].[Calendar].CURRENTMEMBER },                                             
[Measures].[Internet Sales Amount])' 
SET H24 as ' 
[Date].[Calendar].[Calendar Quarter].ALLMEMBERS' 
SET H33 as ' 
[Date].[Fiscal].[Fiscal Year].ALLMEMBERS' 
SELECT { 
[Measures].[Internet Sales Amount], 
[Measures].[I_S_A_4_MONTHS_ROLLING_AVERAGE]} 
ON AXIS(0), 
NON EMPTY CROSSJOIN(H24, 
H33) 
DIMENSION PROPERTIES 
MEMBER_CAPTION 
,[Date].[Calendar].[Calendar Quarter].[Fiscal Quarter] ON AXIS(1) 
FROM [Adventure Works] 

The following display options are now supported and are used when generating synonyms for the sample data sources provided with SQL Server Analysis Services.

• M, which places a floating currency symbol to the left of a measure.
• p, which converts a measure to a percent by multiplying it by 100, and then displays it with a percent symbol on the right.
• %, which does no conversion, but displays the existing measure with a percent symbol on the right.

TABLE FILE ADVENTURE_WORKS_UPPER_CASE
PRINT
INTERNET_SALES_AMOUNT/P20.2 AS 'P20.2'
INTERNET_SALES_AMOUNT/P20.2M AS 'P20.2M'
GROSS_PROFIT_MARGIN/F10.4 AS 'F10.4'
GROSS_PROFIT_MARGIN/F10.2p AS 'F10.2p'
GROSS_PROFIT_MARGIN/F10.4% AS 'F10.4%'
BY COUNTRY
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

A sample DAX query can be executed using the existing MDX Passthru mechanism. The recommended way is to place a DAX query into a file with extension .sql and create a synonym for it. The synonym can be further edited for attributes such as proper formatting. Reporting is then done against that synonym.

To configure Cognos Access Manager (CAM) authentication, you must add the CAMNamespace parameter to the connection string.

You can configure this parameter on the Web Console. To configure the Adapter for TM1, use the Add Connection page for the Adapter for SQL Server Analysis Services, as shown in the following image.

For TM1, the catalog to be accessed must be part of the connection string. Enter the catalog name in the Default Database text box in order to add it to the connection string.

Enter the CAMNamespace parameter in the Additional connection string keywords text box. It will be added to the connection string as the ProviderString parameter when you press Configure. For example, if you enter CAMNamespace=NTLM_NAMESPACE, a connection string similar to the following is generated in the edasprof.prf server profile:

ENGINE SSAS SET CONNECTION_ATTRIBUTES CON1   
SQL2012x64-02/user1,pwd1;SData:CAMNamespace=NTLM_NAMESPACE 

The ENGINE SSAS SET PROVIDER TM1OLAP command must be in effect when using the Adapter for TM1. This command can be issued in edasprof.prf or in a FOCEXEC.

Integrated Login enables you to use Microsoft Windows network authentication to control access to IBM Cognos TM1 data.

In this security model, user and group Microsoft Windows login information has to be moved into the Cognos TM1 database. Integrated Login matches the domain-qualified name you use to sign in to Microsoft Windows with a name stored in the internal database.

Integrated Login is supported on Microsoft Windows only.

You can configure Integrated Login in the Web Console, using the Add Connection page of the Adapter for SQL Server Analysis Services. When you configure the Adapter for TM1, select a trusted connection, as shown in the following image.

The connection string must specify the name of the TM1 catalog to be accessed. Enter the catalog name in the Default Database text box. A connection string similar to the following will be generated in the edasprof.prf server profile.

ENGINE SSAS SET CONNECTION_ATTRIBUTES CON1   
SQL2012x64-02/,;SData 

Since the connection is configured as trusted, no user ID or password is included in the connection string. The Integrated SSPI login causes the credentials from the Windows login to be used to access TM1.

The ENGINE SSAS SET PROVIDER TM1OLAP command must be in effect when using the Adapter for TM1. This command can be issued in the Server Profile or in a FOCUS procedure.