Connecting to Database with Transformer

[vsudaya]

Hi,

How to connect to Database with Transformer(Suppose yu dont have access to Impromptu) and create a cube.?

Thanks in advance
vsudaya

[cognostechie]

You need to me more specific. Which database are you talking about? There are some databases Transformer would connect to but not all. Also those databases that Transformer connects to by itself have to be either locally or thru a mapped drive to your server.

[vsudaya]

Hai,

I am able to connect to databases like foxpro,access etc in Transformer, if yu want to connect to Oracle or Sql Server what is the procedure?

Thanks in Advance
vsudaya

[cognostechie]

If you don't have access to Impromptu/Reportnet, you will have to write a SQL similar to the one written by Impromptu and use that SQL to connect to the database thru ODBC.

[sir_jeroen]

With ReportNet:
- Build your IQD's in Framework Manager;
- Publish Package and externalize;
- Goto ReportNet and copy your database connection string
- Past this string in Cognos.ini

Now you can use the IQD's build with CRN in Transformer.


Good luck

[Darek]

Or use NOTEPAD ...

[sir_jeroen]

Or use NOTEPAD ...

Can you explain how you do that... I think that's the best method... Could you create an example please ???

[Darek]

Welcome back, Addict. How was Turkey ... ?

Just export any Query Subject as IQD and check the content. It's fairly easy. If one does not have FM, than there is another post somewhere here, which shows an example.

[dwood09]

Here's an example template that you can use ... just replace the sql between the start and end tags:

COGNOS QUERY
STRUCTURE,1,1
DATABASE,CPM-One Datamart
DATASOURCENAME,T:/BI/RC_Finance/DataSources/CurrYear/RC PandL MTD Facts IG Elim Bal.imr
TITLE,RC PandL MTD Facts IG Elim Bal.imr
BEGIN SQL

   SELECT       --core month data
      cast('Month' as varchar(20)),
      T1.Forecastcentre,
      T1.ForecastElement,
      T3.calendar_period ,
      sum(T1.actual),
      sum(T1.budget),
      sum(T1.CurrentForecast),
      sum(T1.PreviousForecast)
   
   FROM
      F_ProfitAndLossAtForecastlevelSchema T1,
      D_Accounting_Period T3

   
   where
      T3.accounting_year  = (SELECT DISTINCT Reporting_year FROM D_Accounting_Period)
      AND T1.accounting_period = T3.accounting_period
      AND T1.ForecastElement NOT IN ('FTE', 'HEADCOUNT')

   GROUP BY
      T1.Forecastcentre,
      T1.ForecastElement,
      T3.calendar_period
      
END SQL
COLUMN,0,TimeAspect
COLUMN,2,Forecast Centre
COLUMN,4,Forecast Element
COLUMN,5,Cal Period
COLUMN,6,Actual Dollars
COLUMN,7,Budget Dollars
COLUMN,8,Current Forecast Dollars
COLUMN,9,Previous Forecast Dollars


There are some differences between this SQL and normal SQL, here are a few:

* Rather than using "+" to concatenate strings, Cognos SQL uses ||.  eg 'FirstName || ' ' || LastName
* Union statements require all columns to be cast the same
* Others that I just can't remember at the moment!!!

cheers,
DW

[Darek]

... but if you want to use DB specific SQL just enclose the whole SELECT statement in curly brackets.

[Sridark]

Hi Team,

COGNOS QUERY
STRUCTURE,1,1
DATABASE, ORACLE
TITLE, TEST_IQD

BEGIN SQL

SELECTÃ,  Ã, T1.CUST_FIRST_NAME,
   T1.CUST_GENDER,
   T1.CUST_CITY,
   T2.COUNTRY_NAME,
   COUNT(T2.COUNTRY_ID)

FROM    CUSTOMERSÃ,  T1,COUNTRIES T2

WHERE T1.COUNTRIES.COUNTRY_ID = T2.CUSTOMERS.COUNTRY_ID

GROUP BY   Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  T1.CUST_FIRST_NAME ,
      T1.CUST_GENDER ,
      T1.CUST_CITY,
      T2.COUNTRY_NAME

END SQL

COLUMN,0,CUSTOMER_NAME
COLUMN,1,CUSTOMER_GENDER
COLUMN,2,CUST_CITY
COLUMN,3,COUNTRY_NAME
COULMN,4,COUNT

I tried this for the first time and gettin error stating

Dms-e-general , A general exception has occurred during operation 'prepare request'
and Table or view does not found in dictionay.
kindly suggest ..

[CoginAustin]

Just as Darek said, enclose in curly brackets. If you use any kind of "custom sql" you must enclose the sql statement

COGNOS QUERY
STRUCTURE,1,1
DATABASE, ORACLE
TITLE, TEST_IQD

BEGIN SQL
{
SELECT   T1.CUST_FIRST_NAME,
   T1.CUST_GENDER,
   T1.CUST_CITY,
   T2.COUNTRY_NAME,
   COUNT(T2.COUNTRY_ID)

FROM    CUSTOMERS  T1,COUNTRIES T2

WHERE T1.COUNTRIES.COUNTRY_ID = T2.CUSTOMERS.COUNTRY_ID

GROUP BY                   T1.CUST_FIRST_NAME ,
      T1.CUST_GENDER ,
      T1.CUST_CITY,
      T2.COUNTRY_NAME
}
END SQL

COLUMN,0,CUSTOMER_NAME
COLUMN,1,CUSTOMER_GENDER
COLUMN,2,CUST_CITY
COLUMN,3,COUNTRY_NAME
COULMN,4,COUNT

[sir_jeroen]

Darek,
I was just kidding. Would like to have seen how you would create a IQD from scratch by using Notepad... No editting or copying... From scratch :D

[Darek]

Should I create a tutorial like you do? I'm so jealous.

[sir_jeroen]

That would be very nice and handy  :P

Btw... my tutorials ain't that difficult to create

[dwood09]

.... "curly brackets" ... you mean I have been adhering to some strange Cognos SQL for years now for no good reason!!! ... substring('xxx' from 1 for 2) .... and I could have just enclosed in curlies!!   


I'm glad that I've found out now though!!!

thanks Darek

cheers,
DW

[Blue]

You can use many types of data sources to bring data into your cube model besides Impromptu IQD files.  Here is a list from the Transformer Help system:

-- Architect Model   
A package contained in an Architect Model.

-- Impromptu Query Definition(valid on client or server)   
The source table is described in an Impromptu Query Definition (.iqd file).  Input values are retrieved from a supported Impromptu database by executing an SQL query (defined by Impromptu) that is stored in the .iqd file.  The contents of the .iqd file are stored in the model and the embedded .iqd contents are refreshed when the data source is opened (that is, when you generate categories or create cubes), or when you click OK on the Data Source property sheet.  For PowerPlay users to drill through to detail values, include an associated Impromptu report (.imr) file.
Note: Transformer does not support Impromptu HotFiles as input.

-- Delimited-field Text with Column Titles(valid on client or server)   
Input values are found in a text file with one record per line. The values in the first line represent column names. The fields (column values) are delimited by the character specified in the Field Delimiter box.

-- Delimited-Field Text(valid on client or server)   
Input values are found in a text file with one record per line. The fields are delimited by the character specified in the Field Delimiter box.

-- dBase Table(valid on client only)   
Input values are found in a dBase file (version III, IV, and 5.0).

-- Access Table(valid on client only)   
Input values are found in an Access file (version 2.0, 95, and 97). Secured (password-protected) files are not supported, however.

-- Access Query(valid on client only)   
The data source table is described in an Access Query (.mdb file). Input values are retrieved from an ODBC-supported database by executing an SQL query that is stored in the .mdb file. Secured (password-protected) files are not supported, however.

-- Paradox Table(valid on client only)   
Input values are found in a Paradox file (version 3.x, 4.x, and 5.x).

-- Lotus 1-2-3 Crosstab(valid on client only)   
Input values are found in a Lotus 1-2-3 crosstab file (version 1A, 2.x, Symphony, 3, and R4).

-- Excel Crosstab(valid on client only)   
Input values are found in an Excel crosstab file (version 3.0, 4.0, 5.0, 7.0, Office 97 and Office 2000).

-- Lotus 1-2-3 Database(valid on client only)   
Input values are found in a Lotus 1-2-3 spreadsheet database file (version 1A, 2.x, Symphony, 3, and R4).

-- Excel Database(valid on client only)   Input values are found in an Excel spreadsheet database file (version 3.0, 4.0, 5.0, 7.0, Office 97 and Office 2000).

-- PowerHouse Portable Subfile(valid on client or server)   
Input values are found in a PowerHouse portable subfile (.ps).

-- FoxPro Table(valid on client only)   I
nput values are found in a FoxPro file (version 2.0, 2.5, 2.6, and 3.0).

-- Clipper Table(valid on client only)   
Input values are found in a Clipper file (version 5.0 and 5.2).

-- Fixed-Field Text(valid on client or server)   
Input values are found in a text file with one record per line. Each field starts at the byte immediately following the preceding field; the width of each field occupies a specified number of bytes. Each row ends with a text line delimiter.

-- Fixed Field and Record without CR LF(valid on client or server)   
Input values are found in a text file. Each field starts at the byte immediately following the preceding field; the width of each field occupies a specific number of bytes. The record end is not marked by a text line delimiter.

Note there does noy appear to be an ODBC or SQL-OLE or SQL*Net type of input option.  All except the IQD and Architect types are not dynamic - that is you must capture the data first into an intermediary data file.

Given that Architect comes with PowerPlay/Transformer and you don't have Impromptu I suggest you examine this as a possible solution.

[Darek]

"... Note there does noy appear to be an ODBC or SQL-OLE or SQL*Net type of input option.  All except the IQD and Architect types are not dynamic - that is you must capture the data first into an intermediary data file. ..."

... or route through an Access Query. My choice would be IQD as I'm very allergic to Architect.

[Draoued]

Have a look to this cognos Document Creating IQD

This document describes how you can
ââ,¬Â¢ Create an Impromptu Query Definition (.iqd) file without using Impromptu
ââ,¬Â¢ Build a model based on a manually created .iqd file