Having Trouble with Dynamic Cubes and SQL?

I’m going to be honest. I love Transformer and PowerPlay cubes. There, I said it. I admit it. Since I have a love for the “older” technology, I was REALLY excited to hear that Dynamic Cubes and Cognos Cube Designer were part of the 10.2 BI release!  However are you having trouble with Dynamic Cubes and SQL?

Dynamic Cubes

For those of you who are not familiar with Dynamic Cubes, it is really Transformer on steroids for big data, as it allows for analyzing terabytes of data quickly and accurately.

Before you can start using this new technology, there are a few things you need to make sure are set up:
1. Your data warehouse must be in a star or snowflake schema format
2. Your data source configuration in Cognos Administration must be set up with JDBC connection
3. Most likely, you will need to download the JDBC drivers from Microsoft’s download area of their website
4. You will need to install them in a couple of places; see this link for exact direction: http://www-01.ibm.com/support/docview.wss?uid=swg21584451
5. Lastly, you will need to make sure you have 32-bit SQL installed, as all connections made through client are 32bit only

Once all of these things are complete, you should be able to launch Cognos Cube Designer and load up the sample model or connect to your own data source and be off and running with Dynamic Cubes.

My next blog will discuss deploying and reporting from Dynamic Cubes.

What’s new in IBM Cognos BI 10.2.1?

IBM Cognos BI 10.2.1 was released on June 11, 2013.  In this release, IBM developers focused on three areas:  visualization, performance and big data.


“A picture paints a thousand insights.”  Well-developed charts and visuals allow end users to quickly spot trends and/or issues.  IBM’s RAVE (Rapidly Adaptive Visualization Engine) significantly improves the consumer’s mobile and dashboard experiences with more flexible, adaptive and interactive visuals. Cognos 10.2.1 RAVE technology also enhances existing charting options and includes new and innovated charts.  Additionally, new charts are continuously being added and are available for download from AnalyticsZone.com.


Larger data sets that empower end users to query at will have their advantages, but can be a performance killer.  IBM developers recognized that performance is an important factor in end user adoption and a BI initiative’s success.  By tackling bottleneck issues in Active Reports, Bursting, and Dynamic Cubes they were able to improve performance on average by 40% over version 10.2.

  • Dynamic cube, the OLAP solution released in 10.2, is now 2 times faster.  10.2.1 brings reductions in required memory for aggregate caches and improved aggregate selection logic that chooses the aggregate with the lowest query cost.
  • Active Reports in 10.2.1 execute 5 times faster than 10.2.  This includes faster loading in browsers, mobile devices, and Cognos Connection.  Active Reports now have a smaller footprint due to better use of compression and engine optimization.  Additional authors can now have 10 times more data in Active Reports.
  • Bursted reports created in Report Studio 10.2.1 use hardware resources more efficiently by allowing distribution across all report servers and by optimizing the execution of batch reports.

Big Data

Buzz phrase of the year: “BIG DATA.”  Big data is inherently problematic from how to properly capture, organize, maintain, store, and analyze it.  Companies want the ability to look at the most complete data set possible to find correlations, spot trends, and make real time decisions.

Cognos BI 10.2.1 now has Big SQL support extended to:

  • IBM InfoSphere® BigInsights™ V2.1
  • Hive support for Apache Hive version 0.8 or version 0.9
  • Cloudera
  • Hortonworks
  • Amazon Elastic MapReduce

In addition to visualization, performance and big data enhancements, IBM Cognos Mobile has some cool and important new features, including the ability to connect to multiple servers and use auto server configuration, the ability to zoom in on Active reports and use touch-friendly HTML5 prompts, and report fidelity for consistent viewing.

Other Cognos BI applications and administrative functions were also enhanced and, as we learn more about Cognos 10.2.1, we will surely bring them to your attention.

What are IBM Cognos Dynamic Cubes?

With the release of Cognos 10.2, IBM Cognos introduced a new data modeler tool called IBM Cognos Dynamic Cubes.  Essentially, Dynamic cubes is an extension of Dynamic Query that leverages in-memory data to achieve high performance interactive analysis and reporting over terabytes of warehouse data.  Very important – you must have a data warehouse structured in a star or snowflake schema in order to maximize the performance!

This isn’t a replacement for Power Cubes or TM1 cubes; these product offerings still work well for smaller amounts of data coming from an operational or transactional system.  Dynamic cubes was added to the product offering to enable high-speed interactive analysis on fact table rows that exceed 20 – 25 million; as Dimensionally Modeled Relation(DMR) or OLAP generally work well on data sets less than that.

Cognos Dynamic Cubes are modeled with a modeling tool called IBM Cognos Cube Designer, which connects directly to the data warehouse and uses the relationships between fact tables and dimension tables to help accelerate the creation of cubes.  Think transformer but bigger!

Once cubes are created, you utilize the normal BI interfaces such as Report Studio, Business Workspace and Business Workspace Advanced.

Currently, the following databases are supported – IBM DB2, IBM Netezza, SQL Server, Oracle and Teradata.  There are no additional license roles that need to be purchased.  Existing roles such as Administrator and Modeler apply for Dynamic Cubes.

IBM’s Information on Demand 2012 – What We Learned

IBM's Information on Demand 2012 – What We Learned

What happened at IBM Information on Demand 2012 (IOD) in Las Vegas does not stay in Las Vegas!  There were many exciting Cognos related topics discussed at IBM Information on Demand Conference held this past fall.  This year’s conference was held at Mandalay Bay the week of October 22nd, and was attended by more than 12,000 people.  Some of the hot topics included Cognos 10.2 updates and new features including Active Reports, Dynamic Cubes, Cognos Mobile, Workspace and Cognos Insight.  Below is a brief description of each…

Active Reports –

How would you like to be able to perform interactive analytics while offline, perhaps traveling on an airplane with only your iPad?  You can!  With Active reports, you stay connected while disconnected!  You can interact with reports or dashboards and figure out trends that move your business forward while the competition is waiting to get connected again.  Report writers do need to be careful when creating Active Reports, especially those targeted for mobile devices, to ensure that the report is built to the limited screen size specifications for ease of use.

Dynamic Cubes –

Do you have a very large data warehouse based on a star or snowflake schema on which you want to provide an OLAP experience?  Then this product might be for you!  This 100% in-memory OLAP engine is optimized for data sets over 25,000,000 rows.  This product is similar in nature to Transformer or PowerCubes.  But fear not; it is in addition to these products, not a replacement.  Dynamic Cubes can provide up to 80 times faster response to similarly modeled DMR data sources.

Cognos Mobile –

IBM has spent a lot of time and resources on Cognos Mobile.  In versions 10.1 and 10.1.1, Cognos Mobile made huge leaps with outstanding functionality across a broad set of devices.  Now, in 10.2, it offers even more.  Push notifications to the IOS status bar on the iPad now allow users to know when a new version of their report is available.  Multi-page reports can now be streamed to devices, reducing load time, and administrators can now govern access to Cognos via Mobile devices through Cognos Administration.

Cognos Workspace (formerly Business Insight) –

First of all, IBM has changed the name from Business Insight to Cognos Workspace, a change in my opinion that is good!  Business Insight Advanced is now known as Cognos Workspace Advanced as well.  Some of the enhancements to Cognos Workspace include multiple tabbed workspaces, the ability to freeze and unfreeze column and row headers, and the ability to limit the user experience by either making them an author, user or consumer.   IBM has also added support for use in Google Chrome and Safari.  Check out Cognos Workspace and make dashboards that executives love!

Cognos Insight –

Cognos Insight is a Personal Analytics solution that resides on the user’s desktop and can take many different data sources or types and create cubes, reports and dashboards quickly and without IT help.  Insight now includes out of the box time rollups (think Transformer), as well as drill up and down charting with top/bottom filtering.  A major item of note is that customers who have Cognos TM1 Enterprise can now publish their Cognos Insight Dashboards to Cognos 10.2 Enterprise.  From a licensing perspective, the user just needs to have an enhanced consumer license.

There are many other exciting things happening in the Cognos world and Lodestar Solutions is here to help fill any need you have.   Next year’s IBM Information On Demand conference will again be held in Las Vegas in early November, 2013.  With all of the enhancements coming so quickly, IOD is a great way to be at the forefront of the latest and greatest in not only Cognos, but other IBM products as well.  Hope to see you there!