In this Blog we look at some of the options for connecting your SAP BW system to IBM’s Cognos suite of tools in their 8.4 and 10 versions.
By Dr. Berg
Cognos is a company that has provided BI front-end tools for the last 20 years to thousands of companies. I actually used PowerPlay on top of BW 1.2b for a large European telecom in 1999.
Now, I often meet companies that have a SAP BW platform and business units that are using BusinessObjects, BEx tools and Cognos. These companies are looking for options to leverage their investments in Cognos while using SAP BW as their data warehouse.
By-passing the SAP BW BEx Query
The first option is to ignore all tools and simply access BW directly. While it may be tempting to use ODBC and by-pass the BEx query (treat BW as just another database), that would be a mistake.
There are simply too many table joins and thousands of tables to make ODBC a feasible option. You would also bypass all calculated key figures, restrictive key figures, authorization relevant objects, time-depended masterdata and much more.
Fortunately, Cognos has recognized this and build several better solutions for customers who want use their Report Studio, Query Studio or other tools with their BW system.
Cognos and BW - the Right Way
The most common way for accessing BW data with Cognos is to use the win
dows based application called Framework Manager Modeler. This access the BEx queries and leverages the metadata inside BW.
The interface is based on Object-Link Enablement for Data Bases for OLAP (OLEDB). Just make sure you set this flag in the BEx queries you want to pick up in Cognos. The flag is found in the BEx query designer in the 'advanced properties' .
In the Framework modeler can also add your own logic, key figures and have access to many of the same functions found in BusinessObjects universes. However, instead of calling them universes, Cognos publish this as ‘Packages” which can be accessed by standard Cognos tools. There is even a Framework Manager Metadata Wizard that can help you import the BEx metadata into the Cognos packages.
Furthermore, Cognos provide a web based tool called Cognos studio for those accessing through web-enabled connections.
Multi-dimensional Online Analytical Processing (MOLAP)
Unlike the core SAP BW reporting tool suite, Cognos also support the creation of MOLAP cubes based on their PowerPlay technology (similar to SAS and Oracle Express MOLAP).
This takes the data and moves it into an indexes format that is much faster to access than traditional ROLAP (standard BW approach). While faster, the drawbacks of MOLAP are data replication, data latency and system complexity.
However, the great news is that you can use SAP BW as a source within the Cognos Transformer tool when creating the MOLAP cubes.
Once created, the MOLAP cubes can either be stored as a standard PowerPlay cube, or as a even faster Cognos TM1 cubes (in-memory storage). Actually, building MOLAP cubes
;is a rather simple task for those who know the Transformer tool.
More Cognos on SAP BW Ideas
In the latest release of Cognos, version 10, IBM introduced a new Dynamic Query Mode (DQM) option. This provides significant performance benefits for MDX based interfaces such as OLEDB. Prior to this we only had the Compatibility Query Mode.
Cognos also provide a web based tool called Business Insights Advanced. In this tool you can author and analyze data and it takes advantage of the new DQM enables packages (make sure you set your package to ‘fit-to-purpose’ (FM) when doing so.
Finally, you can use the Self-Service Package Wizard (SSPW) directly from Cognos connections and build your own semantic access layer. However, to keep it simple, and assure performance, Cognos has limited this to accessing no more than 2 SAP Infocubes and upto 5 BEx queries per connection. While this allows users to access InfoCubes and BEx queries directly, I strongly recommend accessing only BEx queries (that way all the features of the SAP BI Analytical Engine in SAP BW are leveraged).
Of all the 3rd party BI tools I have a working knowledge of (Oracle, MicroStrategy, SAS, SPSS, Teradata etc.), Cognos has in my opinion developed the most complete access methods to BW.
So, while I prefer a single BI stack based on BusinessObjects for SAP centric companies, Cognos is a very viable alternative for those who have already made that investment.
Next, ComiertLabs and I will explore some of the great features in the BI Launchpad in SAP BusinessObjects. Stay tuned.....