<< VIEW FULL RESOURCE LIBRARY

Architectural Options for Using IBM Cognos with SAP

November 21, 2013

Cognos 10, Metadata Modeling, Metrics and Scorecarding, Mobile, OLAP, Report Authoring, SAP, System Admin & Security

Including Alternatives to the Standard Recommendations

In this webinar recording, we discuss the underlying architectural challenges of reporting from SAP. We present a number of options for connecting SAP to Cognos, along with pros and cons for each. Included are several lesser known approaches that have been highly successful with our clients – methods that can be implemented today without the high cost and risk associated with SAP’s standard answer to the problem, HANA.

Presenter, John Peterson, Senturus co-founder, draws from case studies of how these innovative architectural approaches were implemented successfully for our clients. 

If you've upgraded to Cognos Analytics, you may be interested in our Cognos Analytics Performance Tuning webinar recording. You will learn how to use the Interactive Performance Assistant feature and tips and tricks to improve the performance of your Cognos environment.

BUSINESS CONTEXT

Many organizations continue to struggle with getting enterprise BI tools to work well with SAP ECC (R/3) and BW. Too often we find that, despite following vendor recommended practices, companies continue to suffer from poor analytics performance and usability, crippled features, lack of self-service BI, and very high development and maintenance costs. Innovative architectural approaches have been shown to quickly and cost effectively overcome those difficulties and shortcomings.

TECHNOLOGIES COVERED

IBM Cognos; SAP

RECOMMENDED AUDIENCE

BI Managers, BI Directors, BI Developers

PRESENTER

John Peterson

Co-Founder, Senturus, Inc

John is the company's thought leader and visionary. John directs the delivery of all projects with Senturus, providing the bridge of technical and business understanding.

Outline

Basic options using IBM Cognos

  1. Use SAP BW Info/Multi-Providers Directly  (i.e. Publish InfoCubes thru Cognos FM without Bex queries)
  2. Use SAP BW InfoCubes and BEx (Info) Queries
  3. Skip SAP BW altogether and build data marts from the SAP ERP (R/3, ECC) source tables
    a. “Rolling your own ABAP code”
    b. Using special “connectors” (Informatica, Microsoft…)
  4. Use SAP BW Data Storage Object (DSO) + ETL Hybrid Approach

Option 1: SAP BW & Cognos via direct connect to InfoCubes

PROS

  • Enables full multi-layer model to be created & maintained in Cognos FM.
    • Implication: reports are abstracted from changes in underlying data source.
  • FM Model can be used to “join” additional non-SAP data into reporting layer.
  • Cognos report developers and data modelers have more control.
  • Published packages can (often) contain more subject area breadth allowing for great end-user self-service.
  • Can be fast if queries pass limited sets of parameters.
  • Multi-providers may be used to “join” other BW objects (e.g. DSO’s)

CONS

  • Business rules end up residing in SAP & Cognos. Need for dual data modelers.
  • Performance, particularly during prompting, not optimized due to MDX and query rendering limitations.
  • Cognos has no roles in data security when SAP security and authentication is used. Row level should be set by SAP Infocube (best practice).
  • Report results can become unreliable due to MDX translation issues, etc.

Option 2: SAP BW & Cognos via BEx

PROS

  • Reporting off InfoCubes is the most straightforward method for reporting out of SAP BI. Recommended by IBM.
  • All of the business rules reside in SAP. No need for dual modeler resources.
  • BEx queries allows passing variables to Cognos for prompting purposes – fast (see Cons in Option 1 re: alternative)
  • BEx queries can be augmented and supplemented without breaking Cognos reports. Also, one BEx query can be setup to serve multiple reports.

CONS

  • No ability to add “abstraction” layers in FM model. BEx queries are published thru “as is”. Any changes made in FM will be lost if query is republished.
  • Changes on a BEx query requires reloading all metadata from the source
  • Potential performance issues must be addressed in SAP system
  • Row level security must be controlled in SAP. Often has licensing implications.
  • “Joining” of multiple subject areas must be handled in SAP, not FM
  • Implication: Published BEx query packages are often limited to specific subject areas (less flexible reporting)

Option 3: SAP & Cognos via ETL and “connector”

PROS

  • Complete control over data warehouse layer
  • Allows use of EDW as data integration hub, as well
  • Not limited by SAP BW capabilities
  • No need to use SAP BW as repository for non-SAP sources
  • Mappings provided by vendors (Informatica, Microsoft…)
  • Virtualization/federation helps demystify SAP table structures & relationships
  • Connectors can fully generate ABAP code (less dependent on ABAP programmers)

CONS

  • Requires reverse engineering the table and column relationships in ECC
  • But at least, the connectors give you a significant headstart
  • Potentially higher cost due to additional datamart(s) required
  • Requires special connectors due to non-SQL nature of ECC
  • Additional ETL software required (connectors), some at a cost
  • Some connectors can introduce significant performance issues

Option 4:

PROS

  • Has same benefits of Option 3 in terms of open architecture and  data warehouse
  • Enables full multi-layer model to be created and maintained in Cognos FM.
  • FM Model can be used to “join” additional non-SAP data into reporting layer
  • Cognos report developers and data modelers have more control
  • Loading database and cubes can be very fast
  • Not as dependent on ABAP programmers
  • Does not require historical data to be put into SAP or BW
  • Does not rely on SAP user security (and licensing)
  • Opening up DSO table data to Cognos reduces load on direct ECC reporting and improves end-user access and control

CONS

  • Does not leverage SAP expertise and pre-built InfoCubes
  • Requires understanding DSO tables and the ability to add them
  • Requires greater understanding of open-architecture DW systems and Cognos
  • Potentially higher cost than SAP BW due to additional datamart(s) required

RECOMMENDATION: Hybrid Architecture (Option 4)

Additional benefits:

  1. Existing base of users with Cognos depth
  2. Need different security than what SAP BW provides
  3. Avoid pulling historical data through SAP BW
  4. Data and requirements constantly changing
  5. Speed of report development
  6. Limited resources
  7. Desire to re-use existing Cognos reports/logic/skills
  8. Need different security than what SAP BW provides

Case Study specifics (Ariat International)

  1. Desire to re-use existing Cognos reports/logic/skills
    Reduced SAP user licensing needs.
  2. Did not have to hire resources for the migration of historical data. There was no separate history migration project, and yet they still have access to all the history.
  3. Avoided using ECC for operational reports, which improved SAP performance.
  4. About 90% of the operational reports leverage SAP BW, with flexibility for business users to write ad hoc reports in Query Studio.
  5. No training costs for SAP report user as they were already trained in Cognos ad-hoc query tools
  6. Reduced SAP user licensing needs.
  7. Did not have to hire resources for the migration of historical data.
  8. There was no separate history migration project, and yet they still have access to all the history.

HANA: Potential Implications

PROS

  • May significantly enhance multi-provider report performance due to in-memory query rendering (i.e. overcomes negative MDX performance impact)
  • Non-BEx Query FM Model can be repointed to Hana without breaking Cognos reports CONS
  • BI Benefits & challenges posed by Hana still unknown at this time
  • Hana does not use summary tables, so Cognos queries relying on them would need to be remapped or lost.
  • Hana changes the KEY ID and Leaf level pointers to the new InfoCube, thus requiring remapping

OPEN QUESTIONS

  • Still lots of unknowns and untested items re: Hana and BI at this time

Typical Recommendation from SAP

  • Traditionally: Use SAP BW and BEx (as front-end)
  • Now: Use SAP BW and SAP Business Objects
    • BICS...
  • SAP also now often recommending Hana instead

Typical Recommendation from IBM Cognos

  • Use Option 2 – SAP BW InfoCubes connected to Cognos via BEx queries
  • Carefully create BEx queries prior to report development
    • Think of BEx Query Designer as Cognos FM Modeler
  • Make BEx queries broad so a single query can support multiple reports and drill-down/drill-thru (if possible)
  • Use row level security in SAP
    • Pass sign-on info from AD/Cognos to SAP via user group mapping

     

Senturus Recommendation

  • Seriously evaluate Option 3 and Option 4 beforesimply following the standard recommendations
  • We have become very strong supporters of hybrid architectures
  • The mappings and complex lookups are maintained in your ERP vendor’s system (usually the primary operating system and system of record for organizational information
  • Your company has control over company-specific items, such ashierarchies, other source systems, additional transformations, reporting & analytics, etc.

 

OPEN