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.
Will Thrash's Blog 
Hello Will,
Intresting considerations on those 2 technologies. What about IBM’s Infosphere DWE Cubing Services ?
LikeLike
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.
LikeLike
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?
LikeLike
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.
LikeLike
Will,
You mention “Microsoft cubes will require a working knowledge of the multi-dimensional language, MDX.”. Can you explain why this is necessary?
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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…
LikeLike
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
LikeLike
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…
LikeLike
I meant to say PowerPlay Web, you’re right.
LikeLike
An excellent article and excellent responses as well. Very good information for those looking at selecting the right OLAP tool. Thanks Will!
LikeLike
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.
LikeLike
That’s correct. The latest version of CAFE supports non-OLAP sources as well.
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
■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?
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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?
LikeLike
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,
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hi Will
We are eventually going down the Cognos front end and SSAS back end route but I have 2 questions please. We are seeing performance issues with things like suppression. Running a trace I can see that the OLAP engine is being hit with the same query (as without suppression) and so Cognos is performing the suppress either at the server or locally. Ideally, the MDX passed to the OLAP engine would be changed but this is not the case. Have you seen similar issues and have you any tips to help with performance please?
My 2nd issue is with passing the user to the cube. I can easily do the user of the COgnos service but as you say, this is no good for security/auditing. I played around with my sandbox and managed to get this to work fine but I can’t replicate it on our Dev & Prod machines. My Sandbox uses C10.2 and IIS 6 whereas the Dev/Prod machines use IIS7.x and c10.1.1. Do you have any instructions on this please?
Appreciate your excellent help. Great Post!
Thanks
LikeLike
Interesting Post. Definately given me a bit more to think about.
LikeLike
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
LikeLike
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
LikeLike
Excellent!!!….Thank you very much for your answer!
LikeLike
Did you tried to use dynamic Queries?
LikeLike
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
LikeLike
An update, IBM Cognos BI 10.2 now supports SQL Server Analysis Services 2012, 2008 and 2005SP2 for Dynamic Query Mode.
See
Thanks,
Will
LikeLike
Pingback: Microsoft ssas | Chazksolutions
Excellent post.. I have been working with Cognos for 6 years. Today I was asked if I would be interested in working on SSAS. This post gave me all the answers I needed and I think its worth a try! Thanks.
LikeLike
Hi, In our Organization, we are using Cognos TM1 for last 2 years. Now thinking of using SSAS instead of Cognos TM1. Can you please advise on below clarifications.
1. Can we migrate from Cognos TM1 to SSAS? Is there any tool can be used for this Migration?
2. Pros & Cons of using SSAS instead of Cognos TM1?
3. We are currently Cognos reporting tool.. Can we use SSRS instead of cognos reporting tool in future?
4. Advantages of SSAS over Cognos TM1.
Thanks,
Ranjith
LikeLike
We are thinking of using TM1 in the BI space (read only) for speed & administrative simplicity – We’ve already used for years more traditionally… Have built/supported over 100 active cubes with just 2-3 fte. We are only using 1/4 of our single production server.
It’s plenty scalable for summary marts where the largest percentage of reporting/analysis is performed. It doesn’t need to calculate every dimensional aggregation, only what is requested, so it avoids data explosion… So a 300gb cube in ssas or other disk based cubes would be just a fraction in TM1 – probably 50gb.
With data loaded in the background, there is no cube downtime or build time… No need to partition anything or other complexities in tuning disk based approaches. Load from multiple sources to same cube. Only need to load new incremental data not already in the cube. Readily handle ragged hierarchies (simple parent/child file).
Then where more complex modeling or workflow apps are desired, the tool certainly handles that as well. Nice to have one “Swiss army knife” tool so easy to maintain…
LikeLike
Your assumption about data explosion in SSAS is incorrect. SSAS does not automatically build aggregations, although they can be added to improve performance if required. SSAS Multi-dimensional has a 3x compression ratio over a database, whereas SSAS Tabular is generally around 10x compression ratio. At my company we are getting 100 million rows of data into SSAS tabular for just over 1Gb of memory, e.g. we have a 1.4 Billion row cube using 15 Gb RAM.
LikeLike
Hello Will thanks for sharing your knowldege with us
actually im training for my Graduation Project in a compagny in the process of restructuring their information system
they are thinking to buy MS Dynamics AX , and already using cognos 10.2 (Powerplay and Report ) with Sql Server Database
and after lot of research ( and reading your article 😀 ) im convinced that schema is the best :
(MS Dynamics AX -> MSSQL (relational) -> SSIS – MSSQL (multidimensional) -> SSAS -> Report Studio ( Cognos )
but technically i dont know how to publish my UDM ( Cube ) to the portail of Report Studio
what do you think about this ?
LikeLike
Hi. It’s pretty straightforward to publish an SSAS cube as a package in Cognos. You will need to set up a data source for the cube in Cognos Administration, then create a new model in Framework Manager. Import the data source into the model and then you can publish a package to Cognos. The package will be used by Report Studio to allow report writers to develop against the cube.
With most data sources, you can create a metadata layer over the data source to customize what the report writer sees. SSAS is different in that it publishes what you have in the cube with no ability to add or change. It is possible to publish other data sources in the same package.
Thanks, Will
LikeLike
Thank you so much Will , im gonna try this
LikeLike
We originally had COGNOS as a front-end to Teradata but it wrote poor SQL queries so we implemented SSAS to improve performance. Unfortunately it also writes poor MDX, so we have removed COGNOS altogether are now using pure Microsoft (SharePoint, Excel, Excel Services, Power View etc.) and our users are loving it, and performance is no longer an issue.
LikeLike