SSIS with non Microsoft BI tools

Hi

Is SSIS used with non microsoft tools like Cognos, BO or is it always used with SSRS and SSAS?

SSIS can be used to move data between a variety of Microsoft and non-Microsoft databases. In theory, you could use SSIS to extract data from a DB2 database and drop it into Oracle, although I doubt that is very common in actual practice!

Cognos and BObj are BI/reporting tools and don't actually store the data--some kind of database or data warehouse is requried, so SSIS would not be used directly with Cognos or BObj, but can certainly be used to load a data warehouse that is accessed by these tools.

Here's one possible data flow:

Source System --> SSIS --> Microsoft SQL Server --> SSAS --> Cognos/BObj

A single-vendor Microsoft solution might look like this:

Source System --> SSIS --> Microsoft SQL Server --> SSAS --> SSRS/Excel/SharePoint Performance Point

Thanks.

Is the below scenario possible?
Source System --> SSIS --> Microsoft SQL Server database--> Cognos/BObj

I am thinking of learning an ETL tool, and I am expert on Cognos/BObj. It would make sense to learn a tool that are compatible and used together more often in the industry.

dwcurious wrote:Is the below scenario possible?
Source System --> SSIS --> Microsoft SQL Server database--> Cognos/BObj

I've never worked with Cognos, but that approach is certainly doable with BObj. Most BObj tools are quite happy working off a Universe based on a relational star schema DW--no SSAS cube needed.

Source System --> SSIS --> Microsoft SQL Server database--> BObj Universe --> Crystal/WebI

SSIS is very flexible, but there is a bit of a learning curve. For loading a DW it isn't as mature (or as self-documenting) as some dedicated enterprise ETL tools, but it doesn't cost six figures, either! If you are using a SQL Server database anyway then SSIS certainly makes sense.

Update: I just checked and it appears that BObj Voyager requires a cube data source and will not work directly against a relational data source.

BObj Voyajer is OLAP tool, so if i doesnt work against relational data source, it should be fine. But, Bobj Reporting piece would work right?

BOBJ, Cognos and just about any other BI tool works against relational databases... SQL Server is certainly one of them.

So i can use SSIS to load data in the SQLServer database and use Cognos to report on top of it. Even though it is possible, is it normally implemented in such a way in the industry. Because i would assume with SSIS one would get SSAS and SSRS. ANd, users would be tempted to go witht he whole package

I certainly used to build a lot of solutions using SQL Server and DTS (precursor to SSIS) for Cognos, back in the day. SQL Server was probably the preferred platform due to ease of implementation and a superior Query Optimizer.

These days those projects appear to have dried up or even gone into reverse (I've even done some recent projects ripping out Cognos and replacing it with SSAS & SSRS - justified by the maintenance/licensing costs alone).

IMO it's not just the licensing equation but also that SSAS 2005 passed Cognos Transformer as an OLAP engine, and SSRS 2008 R2 + Excel 2010 + SharePoint 2010 Excel Web is a "good enough" solution vs Cognos, even superior in some aspects (scalability, open APIs).

Available skills are another consideration - in my region good experienced Cognos resources are very rare - most moved on years ago.

Good luck!
Mike

Need a suggestion. I intend to expand my DW knowledge and expertise beyond one tool. I have two options BO and MSBI. Which would be better to pick up. I am not defining perspectives but they could be from Market share, strength of the tools, features, number of jobs, etc

dwcurious wrote:So i can use SSIS to load data in the SQLServer database and use Cognos to report on top of it. Even though it is possible, is it normally implemented in such a way in the industry. Because i would assume with SSIS one would get SSAS and SSRS. ANd, users would be tempted to go witht he whole package

It doesn't matter how you get data into a database. SSIS is simply a tool to move into the database. Once it is in the database you can use just about any BI tool to do reporting and analysis.

As far as SSAS and SSRS goes, the choice to use them or not is a matter of cost and perference. They are not bad tools, but not the best either.

I have been working with both Microsoft SSRS and Business Objects (Crystal Reports/WebI) with our SQL Server 2008 data warehouse.

As a developer I like SSRS much better than Crystal for IT-developed reports, but for self-service ad hoc reporting WebI provides a much better product than Microsoft currently offers, although they keep improving their Report Builder product (currently version 3.0. in SQL 2008 R2.) I've got one group of users that is delighted with WebI, but another department doesn't want to touch it--in fact it looks like I am going to have to stand up an SSAS cube and let them hit it directly with Excel.

Microsoft has traditionaly had a lot of holes in their BI stack. They are starting to fill in the gaps, and it sounds like the next major release of SQL Server ("Denali") will include a symantic layer that sounds suspiciously like a BObj Universe. I suspect the gap will continue to narrow--and at some point Microsoft might pass BObj by depending on what direction SAP takes the product line. If SAP focuses the BObj line solely on SAP users they will eventually loose the existing customer base of users with non-SAP data sources. I'm sure Microsoft would be happy to take up the slack--the only question is can they mature their BI product line fast enough.

One thing is for certain--over the next few years there will be a lot of evolution in BI toolsets. Which doesn't make it any easier to pick a platform today! You need to look at what capabilities you need to deliver--reporting, ad hoc, analysis, dashboarding--and determine what product suite(s) to use to get the job done.

Microsoft is still the "low-end" BI solution with reasonably good reporting (SSRS) and analsysis (Excel) capabillities but only so-so ad hoc capabilitiy (Report Builder) and almost non-existant dashboarding (altough Dundas Dashboard provides a nice inexpensive 3rd party dashboard solution.) SharePoint (including Performance Point) also come into the mix, but once again Microsoft's BI offerings are still somewhat fragmented at this point, which could complicate deployment throughout a large enterprise.

On the other hand Business Objects provides a complete suite of enterprise-level tools, but comes at a higher price and is sometimes not as easy to customize.

We use Sql Server 2008, SSIS and Cognos. The ETL software and reporting software are completely different.

Cognos 10 has the ability to read cubes built with SSAS so you can keep the data in Microsoft and report using Cognos.

its really help full sharing, keep it up and share more information as like this

I disagree with Neil about SSAS not being the best tool. I don't know of any other OLAP technology that is as flexible or scalable as SSAS.

I am currently trying to introduce SSAS as a layer between Teradata and COGNOS because SSAS gives more flexibility for ROLAP/MOLAP and HOLAP. I have a prototype with a rolling 5 weeks MOLAP partition, and the remainder as HOLAP (so high level hierarchy queries use pre-built aggregations and lower level queries (leaf level) use ROLAP. SSAS writes far better SQL than COGNOS so is a better tool for a ROLAP solution, and COGNOS does not have the ability to have MOLAP dimensions and ROLAP measures.

As far as scalability, Yahoo have a 12Tb SSAS cube with 2 second response times!
Try doing that with something like Essbase where you have issues with data explosion, or with an in-memory technology like TM1 or QlikView.