WIXLIB

COGNOS TOSSRS MIGRATION- A Brief OverviewJayaprakasha Sarma C Practice Head - BINOUSINFOSYSTEMSLEVERAGING INTELLECT

ABSTRACTThis paper provides an overview of theBusiness Intelligence Reporting solutionbased on SSRS and Cognos and outlinesthe process for migrating from Cognos toSSRS. The paper elaborates on the architecture, features, Capabilities of SSRS andCognos reporting tools and concludeswith a comparison of SSRS and Cognos interms of how they stack up against eachother and other reporting tools in themarket. The challenges involved in migrating reports from Cognos to SSRS iscovered which would enable enterprisesto make an informed decision on thesame. Also featured is a brief overview ofother reporting tools dominating thisspace.INTRODUCTIONBusiness Intelligence reporting (BI reporting) is referred to the process of receiving/providing information or reports toend-user/organizations/applicationsthrough a BI software/solution. BI reporting primarily facilitates the capture ofoutput or results from a BI software/solution. Typically, BI reporting is a preconfigured function/feature/component within aBI software/solution. Based on the parameters set, BI reporting is generally anautomated process that captures andreports on analyzed data. These reportscan be in the form of statistical data,visual charts and/or standard textualcontent.Now, when we look at the BI landscape,there is a lot of focus on getting reports/dashboards in seconds rather than inminutes. In the overcrowded BI market,most reporting tools have commonfeatures like security, production, distribution, deployment and support. However,the differentiating factors include ease ofusability, report quality, scalability andperformance.With that said, let’s look at the 2 productsCognos and SSRS in detail.Cognos to SSRS Migration - A Brief Overview2

IMPLEMENTATIONCOGNOSAny Cognos implementation requires thefollowing three components, which can beinstalled on a single server, or multipleservers. In a basic distributed install, onecomponent would be installed per server.Web UITier 1Web ServerTier 2ApplicationServerTier 3JDBCAPIJDBCContentStoreRpt DBMetricStoreCognos has a three tier architecturenamely Presentation tier, Business tierand Data tier.Presentation Tier:The Web server presentin this tier handles request from user andresponds to the user. After receiving arequest from the user, it delegates therequest to dispatcher which processesthe request and sends the response backto the web server which is in turn delegated back to the user.Business Tier:Multiple Cognos servicesand BI servers are present in this tier.Dispatcher receives a request from thewebserver and delegates it to the appropriate services.Data Tier:This tier has a Content store. BIserver creates content store tables in thecontent store with the help of a contentmanager. BI server stores packages,reports, folders, scheduling, views, etc. inthe content store.Cognos to SSRS Migration - A Brief Overview1. Gateway:This is the first component that isaccessed (invisible to the user) when auser accesses Cognos.2. Cognos BI App server:This server consists of App Components,Dispatcher and Content Manager. TheDispatcher manages a number of different Cognos services such as reportservice, log server and many services likebatch report service and delivery service.Content Manager manages the CognosContent DB and all requests to accessinformation from the Content DB is donethrough the Content Manager.3. Cognos Content DB:This holds the metadata.FEATURES1. Reportinga) Usage environments includeScheduled Delivery, Real time deskto and Real time web.b) Data Layout options (Tabular,Matrixed and Financial)2. Data Analysis:a) OLAP, what-if Scenario?b) Predictive Modelling3. Usability:a) Mobile platform supportb) Highly scalable and ETL toolset3

BENEFITSThe architecture of the application isstructured and calibrated for optimalperformance. This allows enterprises toscale the application to support theirgrowing user base.With Integration of IBM’s SPSS, itprovides Predictive and StatisticalCapabilities.Uses Open architecture that leveragesXML, SOAP and WSDL.Multilingual capabilities automaticallydeliver reports in the users' nativelanguage.LIMITATIONSInitial setup isn't as simple as runningthe installer and activating it. Adjustments to the server setup may benecessary before it is up and running.Browser support can be tricky. Compatibility is best with Internet Explorer, butcertain browser setting changes cancause issues. Other browsers' compatibility can be questionable due to theirsignificantly more frequent updates.It works best with actual databases andnot so well with CSV or other fileformats. An ODBC or JDBC driver canbe used to treat files like databases, butnative support is rather limited.Using external ODBC drivers can bedone with 32-bit drivers only.Having a sandbox environment on adeveloper's personal machine isn'tfeasible due to the manner in whichCognos installs on a system.Cognos to SSRS Migration - A Brief Overview4

WHY MIGRATE FROMCOGNOS TO SSRS?Lacking TATIONDifficultValidationof pre &Post StateHIGH TCOHigh ManualEffortsHowever, each stack has its ownstrengths and weaknesses and each onemay address your business needs betterthan the other.AdditionalSoftware/HardwareRequirementThe Cognos report needs to be in XMLformat for the report to be converted toan SSRS report. Open a Cognos reportvia IBM Cognos Report Studio, go toTools menu and select “Show Specification”. From here, one can get the XMLequivalent of the Cognos report.Due to ever changing business needs andobsolete technologies, organizations arecompelled to move from one reporting formatto the other.Data can be retrieved from XML datasources directly with ReportingServices using the XML data provider. Itconverts XML structure into a data setand SSRS utilizes the same to generatereports.There are multiple reasons why enterprisesmay want to move from Cognos to Microsoft.Each suite has its own strengths and weaknesses. On balance, there’s probably notenough of a rationale on either side to migratefrom one to the other, unless addressingspecific business needs. Some of the important reasons are: -The XML content can be used directlywithin the query and can generate areport. Queries and data can be builtdynamically within the report usingexpressions. XML Data provider can beused as a) XML embedded within queryb) XML using URL c) XML using webservices.1. Licensing: Cognos is a suite of separateapplications that need to be licensed separately, as opposed to SQL Server which is asingle product.2. Cognos Upgrade: Your business may belooking at the cost of moving to Cognos 10from Cognos 11 and not seeing enough ROIpotential.3. Uncertainty: With the IBM buyout ofCognos, the number of products in the mixhas gone up; so we never know the toolsthat will be supported in the future.4. Skills Availability: There are simply moreMicrosoft BI developers out there andgenerally are cost effective to employ andtrain.5. Application Integration: Microsoft applications are quite open in terms of Connectivity,Customization and Integration with otherApplications. Cognos is a closed box.Cognos to SSRS Migration - A Brief OverviewDRAWBACKS INMIGRATIONSOnce the report data is in XML format,some limitations/drawbacks are asfollows:Unless specified in the query, data typeswill be returned as strings in XML. Thedata provider will not automaticallydetect the type information.Maps and charts should have minimumof 1 dimension and measure for XML tobe generated.If a report contains more than one datacontainer, such as a crosstab and a list,and both containers use the samequery, only the output for the list isproduced. So the person involved in themigration should have very good understanding of Cognos as well as XML forhim/her to convert this XML data toSSRS.5

COGNOS & SSRS WHICH GIVES BETTER ROI?CRITERIAIBM COGNOSSSRSTool PricingPricing is dependent on the size of thebusiness and the specific Cognostools/CPU/roles required.Unlike other major vendors, enterprises don’t have to pay for expensive add-ons to run their mostdemanding applications becauseevery feature and capability isalready in-built. SQL Server Enterprise Edition comes at 11,200approx.Cognos Enterprise Explorer TM1comes at 19300 approx.Resource Skill Set /CostHighly Technical resources required.An enterprise license is 125 per user,per month, with a minimum subscription of 150 users and a minimumone-year term.In memory aggregatesExtensible Visualization EnhancementsUser Interface ProfilesBI CapabilityScalabilityIBM Cognos BI was designed forscalability. It scales vertically usingmore powerful computers, andhorizontally using a greater number ofcomputers.Cognos to SSRS Migration - A Brief OverviewComparatively less technical experience required. However DB experience is preferred. Microsoft SQLEnterprise 2 Core license comeswith unlimited users and can beenhanced by speeding up storageand memory.R ConnectivityIn-memory analyticsAdvanced data miningAdvanced tabularFirst step to take after deciding toscale SSRS is to decide if one wantsto scale out or scale up. When ascale out occurs, a new server isadded to the existing infrastructureto increase resources. Each optioncomes with its own costs and benefits.6

COMPARING OTHER BI TOOLS IN THE MARKETMICROSTRATEGYGood for medium enterprises. It includes reporting, dashboards, data discovery and visualization across the organization. It has particularly strong mobile capabilities.PENTAHOPentaho is a solid open-source platform, and the data integration capabilities are particularly strong. This would bea good choice for companies that need to blend and integrate high-volume data from a wide range of structuredand unstructured sources.SAP BOIs an enterprise-level BI system best suited to larger companies, and companies already using SAP enterprise applications.SAP LUMERASAP BusinessObjects Lumira is an effective visualization and data exploration tool for business users if they arealready in the SAP ecosystem.SISENSEIt is designed for companies with a need to process disparate data sources or large data volumes without heavyreliance on an IT organization. It also provides self-service analytics tools.TIBCO JASPERSOFTJaspersoft is an open-source product suite, with particular strength in reporting and analysis, and an intuitive userinterface.ZOHO REPORTSZoho Reports is a suitable reporting tool for small and mid-sized companies looking for a relatively inexpensiveSaaS BI solution.QLIKQlikView is a good choice for companies with developer or power analyst resources to build reports and dashboards. Qlik Sense is an excellent tool for Business analysts with its ease of use and device agnostic UI. Any enduser can create complex dashboards very easily.TABLEAUAn excellent tool for business analysts, allowing them to do discovery and visualization on data from a large numberof data sources with ease.Cognos to SSRS Migration - A Brief Overview7