Which OLAP cube should I use, Microsoft SSAS or IBM Cognos PowerCube (Powerplay)?
An Online Analytical Processing (OLAP) cube is a powerful tool to analyze large amounts of data quickly. There are a number of products available that can create an OLAP cube. This article will discuss some of the factors that should be considered in choosing an OLAP technology and evaluate two major vendors’ products – Microsoft SQL Server Analysis Services 2008 (SSAS) and IBM Cognos PowerCube.
Three factors that are important in considering OLAP technologies are:
1. Your existing database and reporting technologies.
2. The amount of data that will be brought into the cube.
3. The skill set of workers that will design and create cubes.
Microsoft SQL Server
If your data warehouse platform is based on Microsoft SQL Server, especially if you are also using SQL Server Integration Services (SSIS), then SSAS is a natural extension of the architecture. SSIS can used to load and process the SSAS cube. SQL Server data will be optimized for loading into the SSAS cube. (It is important to note that SSAS can also source from Oracle, DB2, Access and Teradata data sources.) This Microsoft database environment can also be used as source data for an IBM Cognos cube, but the Cognos cube cannot be used with Microsoft reporting tools such as Reporting Services. IBM Cognos does have a software product that allows Powercube browsing with Excel; Microsoft SSAS cubes can be browsed natively with Excel pivot tables.
SharePoint
If your portal and collaboration solution is Microsoft SharePoint there, are exciting features in SharePoint 2010 that combines PerformancePoint and new PowerPivot software. These will leverage SSAS cubes and will not support IBM Cognos Powercubes.
IBM Cognos environment
If your database platform is not Microsoft and your reporting environment is purely IBM Cognos Business Intelligence, then using Powercubes is a natural extension. Analysis Services cubes may also be used in this environment if other considerations make this desirable, but all things being equal, Cognos is a better fit.
Size of data
The size of the source data and resulting cube is of paramount importance in your choice. Cognos Powercubes have an inherent limit of 2 Gb, although there are workaround techniques. Microsoft SSAS cubes are commonly 300-400 Gb in size with source data measured in terabytes. Multi-terabyte SSAS cubes are in use today. SSAS also gives the ability to use relational tables for aggregation (known as ROLAP) or a hybrid (known as HOLAP). This allows for even more scalability. For large amounts of data, Microsoft is a clear winner.
Learning Curve
Microsoft SSAS requires a more technical skill set for developers than IBM Cognos Powercubes. Microsoft cubes will require a working knowledge of the multi-dimensional language, MDX. For developers that know SQL it will look similar, but is a paradigm shift analogous to moving from procedural languages to object oriented languages. There will be a learning curve. The return for that investment is much more flexibility, programmability and extensibility.
IBM Cognos Transformer, the software used to design a Powercube, was designed with the developer or power user in mind. IBM envisions strong financial analysts creating their own cubes. The result is simpler and easier to use but lacks the rich capabilities present in SSAS. Organizations with a medium amount of data and limited technical resources can build solutions quicker using IBM Cognos cubes.
Conclusion
In conclusion, Microsoft SQL Server Analysis Services will provide more scalability, extensibility and functionality with a cost of complexity and learning curve. This makes it a more strategic choice and IBM Cognos Powercubes a tactical choice in the appropriate situations. Powercubes are best used with smaller sets of data in an existing Cognos environment and when a less advanced technical skill set is required by the designers.
40 Comments »
Leave a Reply
About
A Market Street Solutions business intelligence advisory consultant with deep experience in Microsoft and Cognos data warehousing architecture, data modeling, data loading and reporting. Microsoft certified – MCITP, MCT (Microsoft Certified Trainer) - in Business Intelligence and Database Development, Wharton MBA, chess player, dog lover, and husband. I live in Chattanooga, Tennessee, with my wife Jill, son Henry, dogs Bubba and Caesar, and cats Priscilla, Elvis, Prudence and Precious.
Email me, please: will.thrash@marketstreetsolutions.com
Twitter: @willthrash1
Website: Market Street Solutions
-
Recent Comments
-
Archives
- April 2012 (1)
- September 2011 (1)
- April 2011 (1)
- June 2010 (1)
- May 2010 (1)
- April 2010 (1)
- March 2010 (1)
- January 2010 (1)
- December 2009 (1)
- May 2009 (3)
- April 2009 (2)
- March 2009 (5)
-
Categories
-
RSS
Entries RSS
Comments RSS
Hello Will,
Intresting considerations on those 2 technologies. What about IBM’s Infosphere DWE Cubing Services ?
Thanks for your comment, Franky. Excellent question.
IBM actually offers three OLAP cube technologies — IBM Cognos Powercube, Infosphere Warehouse Cubing Services and IBM Cognos TM1.
Cubing services implements OLAP by aggregating in DB2 tables, apparently a ROLAP solution. This allows powerful scaling for very large dimensions and facts. It also requires a IT/DBA type modeling skills. There is an ODBO driver provided so that one could use Excel pivot tables as a client to analyse the cubes. Using DB2 for ROLAP aggregation doesn’t mean you can only use DB2 as a datasource. Infosphere tools can provide the necessary data integration.
TM1 is a column based, memory resident OLAP cube that is optimized for write-back capability. This makes TM1 suited for line-of-business applications such as financial planning and budgeting. The required skill set is less technical than Infosphere Cubing Services but requires more complexity than IBM Cognos Transformer in creating Powercubes.
The three IBM OLAP cube offerings are based on very different underlying technology structures and somewhat different target audiences. Microsoft’s OLAP cube offering has the choice of ROLAP, HOLAP, or MOLAP and attempts to cover all of the same audiences at a lower price point. A good next step would be a comparison of security, functionality and price among the OLAP cube options.
Hi Will,
What about cost difference between the two solutions. I have not yet checked into it, but I am told the licensing for Cognos may be more costly than SSAS?
Hi Annie,
A lot goes into total costs but you can look at the cost for end users and the cost for developers. SQL Server Analyis Services end user access is included in the SQL Server Client Access License and server CPU licenses for Standard and Enterprise so there is no additional cost there if you already have SQL Server. A cube developer would need a SQL Server developer license; it retails for about $50.
A Cognos Powercube can only be accessed by the Cognos tools so you will need at least BI Consumer licenses for end users. The Cognos tool which is used to create these cubes is called Transformer and will require an IBM Cognos Business Intelligence Professional license. Note that this is different from IBM Cognos Business Intelligence Professional Author which does not include Transformer.
Cost will vary depending on your size and type of organization but it’s safe to say that Microsoft will be much less expensive. The real advantage to IBM Cognos is in their reporting and dashboarding tools, not their OLAP cube.
Will,
You mention “Microsoft cubes will require a working knowledge of the multi-dimensional language, MDX.”. Can you explain why this is necessary?
Hi Ted,
Yes, MDX is necessary for the cube developer. There are several places where it is required in the design of the cube. Calculated measures are defined using MDX. Data level security is written with MDX. KPI definitions of actual, target and trend are written in MDX. The queries against the cube are in MDX language so it is useful in debugging and performance tuning to read the calls and to write queries for testing.
Most client tools used for end user reporting will generate MDX behind the scenes so it usually isn’t necessary for the user to know MDX.
Will –
I’ve been working w/ Cognos for the past 10 years and my company is starting to move towards SSAS.
In our existing Cognos world – we have a server dedicated to Transformer, which builds the cube and then moves to our reporting server. Is this the same in the SSAS world? So far the recommendation is to build the AS cube on the same box that people are reporting off of.
James,
The architecture of SSAS is more complex than the Transformer/PowerCube environment. In Cognos, a cube can be a single file that easily copied to a secure location for reporting as a data source. An Analysis Services instance can have one or more databases that contain one or more cubes and plus shared dimensions. To build on one server and deploy to another would actually involve installing two instances of SSAS and using static data replication to move the data from one to the other.
If the cube processing can be scheduled during off-peak hours when there are few users, one server will be much easier to manage. SSAS also has better incremental loading features than Transformer, even to the point where a cube can be updated in near real time if needed. This will allow you to process with users online.
The primary reason to use more than one server is for scaling. You can have one master server and add front-end servers for handling end user queries and add back-end servers for distributing large amounts of data.
A reasonable approach would be to begin with one server with plenty of RAM and add front-end or back-end servers as needed for scaling.
Thanks,
Will
We are a large company using mainly Cognos 7 cubes, with some Cognos 8 reporting already in place. We plan to kick off a project to slowly wind down the many Cognos 7 cubes we produce and provide a new BI solution across the company. We are debating Cognos 8 or SSAS but our problem is that our user base are used to being self sufficient with powerplay. If we use Cognos Cubes then they have all of the Studios (license cost is not an issue)but there are limitations on the cube build ie Cube size limit, out of memory errors, 2m category limit etc. We cant seem to build a cube at the lower level of granularity because we just have too many products/customers. If we uses SSAS then we have the problem of providing a front end for users to create reports or analyse the data. Any suggestions or comments appreciated.
Thanks
Sean
Sean,
Cognos supports SSAS cubes as an OLAP datasource. If you want, you can continue to use Cognos as a front-end. The functionality is the same as using a powercube.
Thanks,
Will
Hi Will
Thank you for your reply. Are there any negatives in doing this please? I read that the SSAS cube is very slow when used with Cognos. I also read that too many crossjoins are created and the MDX which cognos produces is inefficient. Do you have any idea about any of this please? Appreciate your time.
Thanks
Sean
Sean,
I’ve seen great performance with Cognos over SSAS. I have not formally compared MDX output for similar queries between Cognos, Excel, Reporting Services or other reporting clients so I can’t speak to that, but now that you bring it up I think I will experiment. Cross joins in MDX are not the same impact as cross joins in SQL, they are usually used to produce nesting of row or column members. There are many factors that impact performance that can be controlled by architecture, design and configuration. Good question.
Thanks,
Will
Hi Sean,
I understand your users – PowerPlay is really a nice and convenient tool for analysis. I hope the next version of Cognos 8 BI will contain the enhanced Analysis Studio, having similar flexibility with PowerPlay for Windows.
Do you create multi-file powercubes, splitting them by time periods? I use this approach and sometimes build powercubes based on more than 1 billion rows of data, the size of this cubes sometimes is more than 40Gb…
Jurii,
Have you looked at PowerPlay Studio? It is the same functionality as PowerPlay for Windows and was created in Cognos 8 for customers like you.
Thanks,
Will
Will,
I had a look at PowerPlay Studio. Unfortunately, it is the same functionality as PowerPlay Web. PowerPlay Studio/Web is good, but it is not flexible. I like Reporter mode of PowerPlay for Windows, where I can operate not only by dimensions and measures, but also by members. For example, I can place 3 selected cities into rows, expand one of these cities into some product types, I can easily create columns representing any periods (e.g. from Dec 15 till Dec 27 of 2008, from Dec 15 till Dec 27 of 2009), etc. Similar flexibility is available in Cognos 8 BI Report Studio (including its Express Mode). And it would be interesting to find out, is similar flexibility available in any other front-ends, e.g. of Microsoft? SAP BusinessObjects doesn’t have similar flexibility…
I meant to say PowerPlay Web, you’re right.
An excellent article and excellent responses as well. Very good information for those looking at selecting the right OLAP tool. Thanks Will!
In response to the following statement
“IBM Cognos does have a software product that allows Powercube browsing with Excel”
This statement is not true
Cognos has a very popular add in software called “Cognos Analysis for Excel” (CAFE`), which lets you browse Cognos Powercubes as well as any OLAP technolgy supported by Cognos including SSAS cubes, SAPBW cubes, Essbase etc.
That’s correct. The latest version of CAFE supports non-OLAP sources as well.
Hi Will,
I create a bunch named sets to filter time hierarchy in SSAS cube to simulate date relative functions in Powercube, ex. YTD, prior YTD etc. It works fine when I place it in subcube area in SSMS cube browser. but don’t know how to use them in Cognos Report Studio and Analysis Studio? could you please explain that? Our SSAS version is SQL 2008, Cognos is v8.4.1.
Appreciate your time.Thanks.
Hi Will,
i am currently using sql server 2008 R2 standard editions and want to know drawback of using it, apart from partitions and perspectives.
we are planning to deliver solutions to our customer, is that way is ok or do we need to look for other(enterprise) but licensing of enterprise is too much costly for us.
Please suggest
Thank you
Surjeet
Hi Surjeet,
While most of the differences between SSAS Standard and Enterprise editions are related to managing large amounts of data, there are two features of functionality that you will not get that are useful in financial statement analysis and reports that analyze snapshots in time like inventory balances. These features are account intelligence and semi-additive measures.
Account intelligence is not available in Standard Edition. This functionality is useful when defining financial reports. It helps you display and sum debits and credits with the correct signs and calculate totals based on financial account logic.
Semi-additive measures are used when you cannot sum a measure over time, but over any other dimensions. Examples of these applications are balance sheet amounts and inventory balances. You typically want to report the amount at the end of a period, not the sum of the amounts through the period.
In my opinion, semi-additive measures should be standard functionality as Enterprise pricing is too high for this typically required feature.
Using Standard you will have to create workarounds if you have these application requirements.
Thanks. I hope you are successful.
■Hi Will
Thank you for your reply.
there is one more question…..
I want to create SSAS solution using OWC 11.0 , so is it good idea or we can leave it with report builder 3.0, as RB 3.0 is very good for dash board.
do you have any good link(samples) for asp.net with OWC 11.0?
Office Web Components are actually obsolete with current Office software so I wouldn’t build a new application using that set of tools. There are Visual Studio tools for Office, Excel Services and the tools with SSRS available as current options.
Is there are any limitations to Transformer in Cognos 10 like cube size,number of categories or any. What will be the future for cognos developer. I am new in cognos.
Thanks,
Ravikiran Patil.
There doesn’t seem to be much improvement with Transformer in version 10. The 2 Gb limit is still there, due to the fact that the program is still 32-bit. Cognos has been marketing their TM1 product heavily and that seems to be their future direction in OLAP technology. PowerPlay is maintained because of the large customer base that still uses it.
Great article and very useful comment section. I am thinking of developing Sharepoint Webparts for Cognos Express (TM1)… can you please point me to the right direction?
Thank you so much for this post and all the follow on Q&A, perfectly answering the question I was asking. I’m a BI architect, originally (and favorably)exposed to SSAS cubes years ago. After an interlude with an SAP BW environment, I joined a Cognos shop 2 years ago and have just recently tried to work with their Powercubes. I’ve quickly become frustrated with performance and apparent scalability issues, and I think your insight has quickly given me several explanations as to why. It sounds like switching to over to SSAS cubes (our source data is in SQL Server anyway) might be a good idea. Would you agree? (Also our data and complexity is quickly growing, so I imagine we’ll hit the 2Gb limit sometime anyway.) Do you have any tips for migrating existing cubes from Cognos to SSAS? (We don’t have a lot of them.) We’re on Cognos 8.4 and still on SS 2005, if that makes a difference. Thanks again,
PowerCubes of IBM Cognos don’t have 2Gb limit if they are partitioned by time or another dimension (Cube Gruop tab in PowerCube properties).
It would be interesting to get some details regarding performance issues of PowerCubes. For example, what is the number of dimensions in these cubes, how many members/categories are available in these dimensions, how many rows of data were loaded into the cube, etc.
Thank you for the kind words.
There are certainly workarounds to the size limits of PowerCubes and when SSAS cubes get very large, partitioning is also one way to improve performance there as well.
If you do decide to migrate, there aren’t any automated tools to make it easy. It is usually a good opportunity to look at the design as far as what is really used and with SSAS you can add attributes to the dimensions for reporting. Dimensions don’t need to be strictly hierarchical as they are in PowerCubes. You can add attributes that are related to the dimension and available for reporting in Cognos or Excel.
Many thanks for speaking on this dizzying topic. My sponsor(s) have gravitated to the Cognos solution via TM1 with C10 in the hopes of circumventing IT costs associated with extensive RDBMS development. However, as the pseudo-IT fellow tasked with developing on OLAP and integrating with a BI ‘front-end’ I’m finding the SSAS GUI functionality/ease-of-use (including documentation/tutorials/samples) so far superior to TM1 and Cognos in general that I can’t bring myself to adopt TM1 (and almost the same can be said for C10). Cognos reservations and SSAS complexity notwithstanding, I’m thinking a “C10 with SSAS” combo might be the best bet. (Although at the moment I’m stuck simply trying to get SSAS connection to load into Cognos.)
If you could elaborate, I’d be interested to know what you see as the selling points of C10 versus Sharepoint or SSIS and also to what extent have you found personal product support (or lack thereof) to be a factor in adopting/using any of these platforms?
And lastly, if I may try your patience, have you been successful with cross-platform setups between Microsoft and IBM products or have you found interoperability issues to often be a deal-breaker?
Thanks,
John
John,
C10 has a better report writer, Report Studio, and now better end user report writing tools — Business Insight Professional and Business Insight. It is easier for end users to write more complex reports and they can design their own dashboard using parts of existing reports.
Having said that, IBM has a good knowledge base for self service product support but nothing compared to MSDN and TechNet and numerous blogs related to Microsoft.
I am currently working on a project which uses Cognos 10 as a front end to SSAS cubes and SQL Server relational tables (in star schema form). Using SSAS has been very successful in terms of performance and functionality — the ability to use properties.
The tricky area in compatibility that I’ve found is when you want to use user security in your cubes and therefore have to set up Kerberos correctly to pass the user from their desktop to IIS in Cognos to the Cognos service to the SSAS service and into the cube. We’re doing it successfully but have had struggles in the past.
Basic connectivity using the Cognos service account, if it has access to the cube, should be straightforward.
The one place where there might be a deal-breaker is using a SharePoint portal with Cognos web parts that use SSAS as a data source. We’ve been unable to pass the user through to the cube from SharePoint because the web parts don’t use Kerberos (yet) for authentication. They use a shared secret with a custom authentication provider in Cognos.
Hope this helps.
Thanks,
Will
Thanks for the response Will!
Using the dimension/member properties in order to get to where I need to with Cognos is what I’m wrestling with at the moment, so it’s reassuring that you mention that as a plus since I have gone ahead with the SSAS/C10 setup. C10 is so dang frustrating though! It “almost” does what I need it to, and figuring out that last little missing tweak is what always hangs me up… ugh!
And yes, user security is a beast which awaits me and I dread tackling.
Anyhow, once it becomes second nature I’m still confident that SSAS/C10 will be the platform combo worth sticking with.
Cheers,
John
Interesting Post. Definately given me a bit more to think about.
Hello Will, excellent article and post!.
We have been working with Cognos 10 this year and we have our cubes under SSAS. The problem we got is that some users need to analyse data (up to details – 50.000 rows ) using Query Studio or Analysis Studio. When we are trying to explore data using more than 3 dimensions ( for example: Monthly Sales by Product, Seller and City), the tool does not support it and throws an error. We read the user guides and they mention it, but we don’t know how to deal with it, i mean, if it’s necessary to make db desing changes ?, cube changes? new hierarchies? or simply use Excel to explore Cubes?..Cognos is a powerfull product with a lot of tools, but we still cannot use it with all its strength.
Thank you very much!
Claude
Hi Claude,
There are two approaches I use for solving this “nesting” issue:
1) To create some additional hierarchies in cubes (e.g. Product->Seller->City, and Seller->City->Product, if necessary, etc.) – I used this approach when designing IBM Cognos Transformer cubes, but I hope your SSAS cubes can be designed similarly. This approach allows to avoid the multiplication of dimension members (e.g. if you have 1000 products, 1000 sellers and 1000 cities, your nesting report will have 1 billion of rows, the lion share of them will be empty).
2) To design multidimensional metadata in Cognos 10 BI Framework Manager based on relational tables, not using cubes.
Regards,
Jurii
Excellent!!!….Thank you very much for your answer!
Did you tried to use dynamic Queries?
Hi Claude,
Dynamic Queries in 10.1.0 work with SAP Business Objects, Oracle Essbase and IBM Cognos TM1. 10.1.0.1 has added Netezza.
Thanks,
Will
[...] Which OLAP cube should I use, Microsoft SSAS or IBM Cognos …Feb 23, 2009 … This project contains SQL Server Analysis Services samples contributed by Microsoft outside of the regular release cycle, MVPs, and other … [...]
Pingback by Microsoft ssas | Chazksolutions | December 26, 2011 |