Cubes from SQL Server/Oracle

[Steve]

Hello Cognos Gurus:

I have been making cubes from an RDBMS called PROGRESS (thru ODBC) and from flat files. I want to make cubes now from Oracle and SQL Server. Since Powerplay Transformer does not give any option to choose any Oracle/SQL as the source when you are building a model, I was wondering do we make cubes from Oracle/SQL Server by using an Impromptu IQD ?

Thanks in advance,
Steve

[Darek]

Yes, you should use an IQD, created by Impromptu, ReportNet, Notepad or UltraEdit. Here is an example of what it looks like:

COGNOS QUERY
STRUCTURE,1,1
DATABASE,gosales
TITLE,[gosales_goretailers].[Sales reps]
BEGIN SQL
{select "Sales_staff"."Staff_name" AS "Staff_name", case  when "Country"."COUNTRY_CODE1" = 11 then "Sales_staff"."LAST_NAME_MB"+' '+"Sales_staff"."FIRST_NAME_MB"+N'' when "Country"."COUNTRY_CODE1" = 12 then "Sales_staff"."LAST_NAME_MB"+' '+"Sales_staff"."FIRST_NAME_MB" when "Country"."COUNTRY_CODE1" = 13 then "Sales_staff"."LAST_NAME_MB"+' '+"Sales_staff"."FIRST_NAME_MB" when "Country"."COUNTRY_CODE1" = 14 then "Sales_staff"."LAST_NAME_MB"+' '+"Sales_staff"."FIRST_NAME_MB" else "Sales_staff"."FIRST_NAME_MB"+' '+"Sales_staff"."LAST_NAME_MB" end  AS "Staff_name__multiscript_", "Sales_staff"."FIRST_NAME" AS "First_name", "Sales_staff"."FIRST_NAME_MB" AS "First_name__multiscript_", "Sales_staff"."LAST_NAME" AS "Last_name", "Sales_staff"."LAST_NAME_MB" AS "Last_name__multiscript_", "Sales_staff"."Position1" AS "Position1", "Sales_staff"."WORK_PHONE" AS "Work_phone", "Sales_staff"."EXTENSION" AS "Extension", "Sales_staff"."FAX" AS "Fax", "Sales_staff"."EMAIL" AS "Email", "Sales_staff"."DATE_HIRED" AS "Date_hired"
from (
select "SALES_STAFF"."FIRST_NAME"+' '+"SALES_STAFF"."LAST_NAME" AS "Staff_name", "SALES_STAFF"."FIRST_NAME", "SALES_STAFF"."LAST_NAME", "SALES_STAFF"."FIRST_NAME_MB", "SALES_STAFF"."LAST_NAME_MB", "SALES_STAFF"."POSITION_EN" AS "Position1", "SALES_STAFF"."WORK_PHONE", "SALES_STAFF"."EXTENSION", "SALES_STAFF"."FAX", "SALES_STAFF"."EMAIL", "SALES_STAFF"."DATE_HIRED", "SALES_STAFF"."SALES_BRANCH_CODE"
from "SALES_STAFF" "SALES_STAFF") "Sales_staff", (
select "COUNTRY_MULTILINGUAL"."COUNTRY_CODE" AS "COUNTRY_CODE1"
from "COUNTRY" "COUNTRY", "COUNTRY_MULTILINGUAL" "COUNTRY_MULTILINGUAL"
where "COUNTRY"."COUNTRY_CODE" = "COUNTRY_MULTILINGUAL"."COUNTRY_CODE" and "COUNTRY_MULTILINGUAL"."LANGUAGE" = 'EN') "Country", "SALES_BRANCH" "Sales_branch"
where "Sales_branch"."SALES_BRANCH_CODE" = "Sales_staff"."SALES_BRANCH_CODE" and "Country"."COUNTRY_CODE1" = "Sales_branch"."COUNTRY_CODE"}
END SQL
COLUMN,0,Staff name
COLUMN,1,Staff name (multiscript)
COLUMN,2,First name
COLUMN,3,First name (multiscript)
COLUMN,4,Last name
COLUMN,5,Last name (multiscript)
COLUMN,6,Position
COLUMN,7,Work phone
COLUMN,8,Extension
COLUMN,9,Fax
COLUMN,10,Email
COLUMN,11,Date hired

That database has to be defined somewhere: IMPROMPTU.INI or AccessManager

[Steve]

Thanks Darek, and sorry for posting it in the wrong forum.

So, I wonder why Transformer doesn't interface directly with Oracle/SQL Server, since it does interface with even the old ones (DBase), and since the largest market share for COGNOS customers is probably using Oracle. Any particular reason why they didn't have this functionality.

[Darek]

Well, it does interface directly, meaning it will use ODBC, OLE DB or native drivers to get data from DB. It only needs a connection, or rather query definition. It would be too much and kind of redundant, to put query modelling facilities into Transformer.

Oh, I've almost forgot, there is a tool which allows you to do "query modelling". It is called Architect, comes with Transformer, but use it at your own risk. As every model and query definition is being stored in Access Manager LDAP directory (or LAE file), Architect has been known to corrupt security once in a while.

[Steve]

I may not have been too clear Darek. I am not concerned with Query modelling as I can always do that in Impromptu and save it as an IQD. What I meant was, since Transformer can read directly from Dbase, MS Access, Flat files, why not read Oracle the same way. Is it because it needs an SQL to pull the data out? Even thru ODBC, it's not direct as it will involve an IQD or some kind of SQL as you said and the ODBC itself is another component.

[Darek]

You hit it right on the head ...

Reading flat files, dBase or Acsess, is a no brainer. Reading from database server is a different story. There is networl involved, drivers, ports, cables ... you get my point.

I'd like to reiterate: it interfaces directly with Oracle or any other DB server. It just needs to be told how to get there.

[BIsrik]

darek, but the cognos people themselves says that the transformer can't read oracle, SQL server and similar RDBMS directly. They have to be fed through other means. They couldn't give a satisfactory answer. One of the support guys told that this is due to security reasons. Is this reason...

srik

[Darek]

I like a good story every now and than ...

Anyhow, can Transformer open an Oracle table directly in its interface so you can see all columns and data types? NO. Can it read such table directly once it is told what it looks like by Impromptu or Framework Manager or notepad-driven-IQD? YES!

Tha's all I was trying to say. That it does not need any extract file, dum file or anything of that kind. Just where the source is (even if it comes from multiple tables) and what it looks like.

Cheers.

[cognostechie]

Srik and Darek:

I have also been thinking about this so dug up this post. Yes, both of you have a point. The main reason is the database security and the fact that modelling cannot be done in Transformer and it would be redundant to do that as Impromptu and Reportnet can do it.

However, the network, cables, protocols is no longer an issue. Nowadays, any directory on UNIX can be shared thru NFS and can be mapped to any Windows box thru a drive letter. It acts the same as reading something from a 'C' drive of any computer. Moreover, having Oracle or any other RDBMS on the local machine would also not connect to Transformer without ODBC.   

[Blue]

Darek

The "Architect" tool you refer to is correctly named "BI Architect".  The original Cognos tool named "Architect" was designed to document PowerHouse 4GL applications and has nothing to do with BI meta data models.

Reagrds,

Blue

[syam]

the reason why we don't need to import from oracle into transformer can be

because RDBMS are build on joins and transformer doesn't have joins but catagories,levels and dimensions.

so even tought you import a schema into transformer i think it will be of no use.

[Blue]

There are other pragmatic reasons why Cognos uses a "standard" method of interacting with data sources in Transformer.  There is a multitude of database engines around now, hundreds in fact, and Cognos has no desire to waste/invest money in a) developing native drivers for each of these and b) maintaining such drivers as the database vendors change their APIs.  Oracle alone sells several different RDBMS options.

The "legacy" data source options such as dBase are there as much for historical reasons as any practical one.  Who on this forum has ever used a PowerHouse Portable Subfile as a data source for Transformer? :)

Blue