• Skip to main content

IBM Blueview

Cognos Analytics and all things IBM

  • The Blog
  • Cognos Glossary
  • Cognos Resources
  • About Me
  • Categories
    • Cognos
      • Data Modules
      • Administration
      • Framework Manager
      • Dashboards
    • Opinion
    • Community Spotlight
  • PMsquare
  • Subscribe

Administration

The 3 Cognos Query Modes

January 29, 2020 by Ryan Dolley 12 Comments

This is actually a sequel one of the very first blog posts I ever wrote for blueview. While a lot has changed since 2015 understanding the difference between Compatible Query Mode and Dynamic Query Mode is still crucial. With the addition of data sets running on the compute service (aka ‘Flint’) things are even more complicated. My goal for this article is the run back the clock, peel back the onion and give you a historical, technical and practical understanding of the 3 Cognos query modes. Buckle up folks, this is going to get wild.

All 3 cognos query modes are available in Cognos 11.1
There are many query engines hidden beneath the hood of your 2020 model Cognos

Compatible Query Mode

Compatible Query Mode is the query mode introduced in ReportNet (AKA, my junior year of college…). It is a 32-bit C++ query engine that runs on the Cognos application server as part of the BIBusTKServerMain process. CQM was the default query mode for new models created in Framework Manager up to Cognos 10.2.x, after which Dynamic Query Mode became the default. The majority of FM models I encounter were built in CQM and thus the majority of queries processed by Cognos are CQM. It remains a workhorse.

CQM relies exclusively on the report service to deliver query results
CQM resides within the Report Service

It is, however, an aging workhorse. Query speed is hampered by the limitations of 32-bit processes, particularly as it relates to RAM utilization. CQM does have a query cache but it runs on a per session, per user basis and in my experience causes more problems than it’s worth. Furthermore, Cognos 11 features either don’t work with CQM (data modules) or must simulate DQM when using CQM-based models (dashboards). This almost always works but of course fails whenever you need it most…

CQM works just fine and moving to DQM is not urgent, however I strongly advise you to do all new Framework Manager modeling in DQM (or even better, build data modules) and start seriously considering what a migration might look like.

Dynamic Query Mode and the Query Service

Dynamic Query Mode is the query mode introduced in Cognos 10.1. It is a 64-bit java query engine that runs as one or many java.exe process on the Cognos application server and is managed by the query service. The terms ‘DQM’, ‘query service’ and ‘XQE’ all essentially refer to this java process. All native Cognos Analytics features utilize DQM only – CQM queries execute in simulated DQM as mentioned above. You can see the criteria necessary for this to work here. DQM is both very powerful and very controversial among long time Cognoids. Let’s take a look at why.

DQM uses the query service to deliver results
DQM features dramatically improved query performance

What’s great about DQM?

DQM has a ton going for it. As a 64-bit process it can handle vastly greater amounts of data before dumping to disk. If configured and modeled properly, it features a shared in-memory data and member cache that dramatically improves interactive query performance for all users on the Cognos platform. It even filters cached query results by applying your security rules at run time.

DQM is tuned via Cognos administration and by a large number of governors in Framework manager to optimize join execution, aggregation and sorting. It handles extremely large data volumes, especially when combined with the basically defunct Dynamic Cubes feature. It even combines cached results with live SQL executed against a database on the fly. On its own. Like you don’t have to tell it to do that, it just does. Magic!

What’s not great about DQM?

Unfortunately given the list of excellent attributes above, DQM has some problems. It is very complex to understand, manage and tune and requires DMR models to fully utilize the all the caching features – consider that the DQM Redbook produced by IBM is 106 pages. A standalone tool exists called Query Analyzer dedicated to help you understand what the heck DQM is even doing as it plans and executes queries.

Migrating from CQM to DQM is often a complex project to evaluate and execute. I once provided a customer an LEO estimate of 8 – 32 weeks to complete a migration project. I have seen migrations take almost a year. I’ve seen things you people wouldn’t believe…

The purpose of this blog is not to push professional services but this is one instance where I think you really should contact PMsquare for help. But let’s say you have a ton of CQM models and don’t have the time to migrate them all. Is there a shortcut to high performance on large(ish) data volumes? Why yes, yes there is.

Data Sets and the Compute Service (aka ‘Flint’)

Data sets are an in-memory data processing engine first introduced in Cognos 11.0 and greatly enhanced in 11.1. Cognos 11.1 data sets run on the compute service aka ‘Flint’. The compute service is a 64-bit spark-sql process that is created and managed by the same query service that manages DQM, so it’s not really an independent cognos query mode. I will write a more in-depth article about data sets and Flint in the future, but let’s take a super quick look at how they work before we get into why they are amazing.

The compute service uses Spark SQL to deliver results
The compute service is a modern in-memory compute engine

How do data sets and the compute service work?

Data sets are not live connections to the underlying data like CQM or DQM – rather, they are a data extraction that is stored in a parquet file and loaded into the Cognos application server memory when needed for query processing. It works like this:

  • An end user creates a data set from an existing package, cube or data module OR uploads an excel file (the process is the same!)
  • Cognos fetches the necessary data and loads it into an Apache parquet file
  • The parquet file persists in the content store and is available to all application servers
  • When the query service on an application server requires a data set for query processing, it first checks to see if it has a local and up-to-date copy of the parquet file
  • If not, it fetches one
  • In either case, the parquet file is then loaded into the memory of the application server
  • Data is processed by the compute service using Spark SQL and results are returned to the query service
  • The query service receives results from the compute service and may perform additional processing if necessary
  • The results are then passed to the report service or batch report service for presentation

What makes data sets great?

They’re easy to build, easy to join and manipulate in data modules, easy to schedule and the performance is great. Once loaded into memory a data set is shared between users on the same application server. I have done multiple projects where I accomplish weeks or even months of ETL by getting fancy with data sets and data modules. No wonder they are my favorite of the Cognos query modes.

What’s even better is how data sets provide a radically shorter path to high performance, DQM and Spark based queries for your existing CQM models without having to commit to a full conversion. You simply use a CQM FM package as the basis for a data set, then utilize that data set as a source in a data module. Once complete, you’ve unlocked the full set of incredible data module and dashboard capabilities like forecasting without having to do an 8 to 32 week project.

Which Cognos Query Mode is right for me?

Okay that was a ton of data, some of it pretty technical. Which of the Cognos query modes should you choose and how do you learn more?

TLDR

  • Immediately cease all development of new Framework Manager models using CQM
  • Consider migrating existing CQM Framework Manager models to DQM models or to data modules (PMsquare can help with this)
  • Data sets are your ‘get out of CQM free’ card; they vastly improve the performance of most CQM queries and simplify presentaiton for end users

References

  • Dynamic Query Mode Redbook
  • Cognos DQM vs CQM explainer
  • Queries on uploaded files and data sets
  • Configuring the Compute service

Read on to up your Cognos game

  • Cognos Union Queries in Reports
  • Cognos Relative Dates in 11.2
  • The 2021 Gartner BI Magic Quadrant is Broken for Cognos Analytics
  • Data Modeling for Success: BACon 2020
  • Cognos Analytics 11.1.6 What’s New

Cognos Analytics Data Servers

December 30, 2019 by Ryan Dolley 2 Comments

Joe Schmoe asks a fairly common question about Cognos Analytics data servers in the comments to my Framework Manager vs Data Modules article:

In Cognos Analytics 11.1.5, I don’t see a way to use a data source to feed a data module. It looks like in order to use data from a database, you need to use a package — which means you need to use Framework Manager anyway. Am I missing something?

Let’s take a look at how data servers work in Cognos Analytics to (eventually) answer Mr. Schmoe’s question.

What is a data server?

A data server is simply a connection to a database that has been defined within Cognos Analytics. This definition contains the database url, connection parameters, username and password necessary for Cognos to authenticate with, issue queries to and receive data from the desired database. A data server can be re-used by an infinite number of data modules to provide self-service users and report authors controlled access to desired data while maintaining high quality row-level security. Longtime Cognoids will no doubt say ‘Ryan, that sounds exactly like what we used to call a data source!’ Yes. Yes it does.

Cognos Analytics data server vs Cognos 10 data source

Cognos Analytics data servers and Cognos 10 data sources are fundamentally the same while having some differences in where and how they are configured. If you’re familiar with Cognos 10 you can breath a sigh of relief as your existing knowledge is almost 100% applicable. You just need to learn a slightly new UI.

Cognos Analytics data server

  • Introduced in Cognos Analytics
  • Provides a definition which Cognos uses to connect to a database
  • Configured via the manage menu
  • Requires JDBC drivers and uses exclusively JDBC connectivity
  • Uses Dynamic Query Mode exclusively
  • One data server can be used by infinite data modules
  • Source for data modules only – no Framework Manager [Editors note: Commentor Jerzy points out below that you can use data servers as a source for FM for DQM models only. I didn’t know that! Thanks Jerzy!]

Cognos 10 data source

  • Introduced in Cognos 8? 7? Reportnet? I was in college, it’s all very hazy
  • Provides a definition which Cognos uses to connect to a database
  • Configured via legacy administration console
  • Uses a wide variety of connection types including JDBC, ODBC and others
  • Uses Dynamic Query Mode or Classic Query Mode
  • One data source can be used by infinite Framework Manager packages
  • Source for Framework Manager only – no data modules (we’ll cover a simple workaround later in this article)

As you can see, data servers and data sources serve the same function within Cognos – defining a reusable database connection for Cognos – and have many of the same features. They differ primarily in the types of databases to which they can connect, the connectivity standard they use and how they are configured within Cognos.

Configuring data servers

Configuring a data server is easy and is – for the most part – a one time, ‘set it and forget it’ task most frequently done by Cognos administrators. Like everything in Cognos you have a few options for how to proceed. Before we dig into that though, let’s take a look at the info we need regardless of how we’re configuring data servers.

Data server configuration checklist

  1. Identify which type of database you want to connect to
  2. Make sure you have permission to connect to it!
  3. Check to make sure it’s supported by Cognos
  4. Check to see if there are any special considerations for that database
  5. Acquire the relevant JDBC driver and install it in the ‘drivers’ folder of the Cognos install directory
  6. Ask your DBA for the following information:
    • URL for connecting to the database
    • Database name
    • Schema name
    • Username for authentication
    • Password for authentication

Configuring a data server from scratch

Now that we have our ducks in a row we can build the data server. This is incredibly easy, though you might not know it from reading these instructions. Let me break it down for you:

  1. Click the manage icon then select ‘Data server connections’
  2. Click the plus button in the upper right of the data server connections window
  3. Select your database type from the list that appears
  4. The data server configuration window will automatically open for you to input your connection url from above.
  5. In authentication method, select ‘use the following signon’ and input your username and password
  6. Click ‘Test’ and ensure Cognos can connect to your database
  7. Click on the ‘Schemas’ tab and set the following:
    • In ‘Load options’, choose whether or not you want Cognos to retrieve statistical samples from the database. I generally leave this alone. Turning this off will disable certain advanced features of Cognos – like auto joins in data modules
    • In ‘Tables’, select which tables you want to expose via the data server. I highly recommend you select only the tables which you need. Allowing Cognos to profile your entire production database during work hours is an extremely bad idea…
  8. Click ‘Save.’ Your data server is ready for use in data modules!

Converting Cognos 10 data sources to Cognos Analytics data servers

While it’s true that a data source configured in Cognos 10 cannot directly feed a data module, you can easily convert a data source to a data server provided the administrator has configured a jdbc connection for the data source. To do so follow these steps:

  1. Click the manage icon then ‘Administration Console’
  2. Click ‘Configuration’ tab and ‘Data Source Connections’
  3. Click the ‘set Properties’ icon for the data source you wish to convert
  4. Check the ‘Connection’ tab
  5. Check the box next to ‘Allow web-based modeling’
  6. Click ‘OK’
  7. Exit the administration console and open ‘Data Servers’ in the ‘Manage’ menu. You should see your data source list as a data server.

Using data servers in Cognos analytics

By following the process outlined above we can easily use data servers to get our data into data modules and then into reports, dashboards or the new exploration feature without ever needing to reference Framework Manager. I hope this satisfies Mr. Schmoe and as always, contact me if you have any questions!


Read on to level up your Cognos skills!

  • Cognos Union Queries in Reports
  • Cognos Relative Dates in 11.2
  • The 2021 Gartner BI Magic Quadrant is Broken for Cognos Analytics
  • Data Modeling for Success: BACon 2020
  • Cognos Analytics 11.1.6 What’s New

Cognos 11.1.4 Performance Tips

October 17, 2019 by Ryan Dolley Leave a Comment

Are you attending Data and AI Forum in Miami next week? Want to talk some Cognos with me? Snag a half hour of my time to chat free from sales guys.

Thank you to those who attended Tuesday’s 11.1.4 livestream! Technical issues aside I had a great time doing it and will be hosting more in the future. There were some comments about performance in recent releases that I promised to address but didn’t – so here are my Cognos 11.1.4 performance tips!

Tip one: Dispatcher > Gateway

Cognos 11 brought with it the option to dispense with the gateway entirely and sending your traffic directly to the dispatcher. This is, among other things, a performance hack that can pay big dividends, particularly if your Cognos environment takes an extremely long time to load or if the UI feels generally sluggish.

The downside here is that for most enterprise deployments relying on a single dispatcher is just not an option for a ton of reasons – including little things like SSO, load balancing and disaster recovery. But if you are able to try this you might find it helps quite a bit.

Tip Two: Reality > Minimum Requirements

The minimum requirements for Cognos 11.1.4 are currently listed at two CPUs. This is a straight up no-go for using Cognos in almost all circumstances regardless of what version you’re on so long as that version starts with the number 11.

A tale of two configs

I typically deploy a single-instance Cognos machine on AWS with the following specs and have no performance issues for demos or small deployments:

  • ec2 t3.xlarge
  • 4 vCPUs @ 2.5Ghz
  • 16GB RAM

With 11.1.4 this configuration suffers from a laggy UI and poor query performance, especially in Dashboards. I observed CPU utilization hitting 100% frequently. As a remedy I migrated my AMI to:

  • ec2 c4.4xlarge
  • 16 vCPUs @ 3.4Ghz
  • 32GB RAM

This adjustment resulted in extremely snappy performance throughout the UI and like-for-like dashboard query execution improved from ~25 seconds to ~2 seconds.

Why did this jump so much?

Cognos 11.1.4 is doing A LOT more than 11.0.x or 10.x ever dreamed of with a host of new interactive services running in the background – services that do things like natural language query, automatic visualization or dashboard creation, on-the-fly predictive forecasting, integrated data prep and modeling…

Tip Three: SSD > HDD

One often unexplored area of performance improvement for Cognos 11.1.4 (or anyone using Transformer for that matter) is increasing the speed of your HDD or migrating to an SSD. All of the new features rely on either the Cognos 10 DQM engine or the Cognos 11.1 ‘Flint’ spark-sql engine to process queries. In either case data is written to and read from disk so IO becomes larger factor in determining performance than ever before, especially for any interactive task.

Tip Four: Your Browser Matters Now

Go ahead and conduct an experiment for me – pop into Dashboards on your local machine, build a bunch of visualizations and observer your resource use. You may notice that Cognos running in Chrome is capable of maxing the CPU on your laptop. It is capable of maxing the CPU on my monster gaming desktop as well if I push it hard enough.

So the final place I’d look to juice performance is actually at the hardware your users are toting around with them. It may seem a little unfair but just imagine life as a poor Power BI or Tableau administrator – outdated and slow laptops define the entirety of their user experience.

Cognos 11.1.4 Performance Summary

All of this is to say that I have had success increasing performance by significantly increasing the specs of the application server. This isn’t necessarily shocking but it’s comforting in a way because it means the problem isn’t bad code. It also means that if you’ve been skating by on the same server config you put into place in 2015 it’s probably time for an upgrade.

Cognos 64-bit Reporting and When to Use It

March 17, 2015 by Ryan Dolley 3 Comments

I discussed in an earlier post the misconception that 32-bit Cognos application servers cannot execute 64-bit Cognos queries when in fact they can. This means that for most Cognos customers leaving the report service execution mode set to 32-bit is a smart move; existing 32-bit CQE content will work alongside 64-bit DQM content and you do not have to set up advanced routing rules to prevent 32-bit queries from hitting a 64-bit server. There are three cases in which I recommend deviating from this path:

  • You have extremely visual reports: When a query comes in that requires DQM your 32-bit Report Service will hand it off to the 64-bit Query Service to build and execute the query plan. The Query Service will pass the result set back to the Report Service to generate the report and everyone wins. If, however, your reports are extremely visually complicated the 32-bit Report Service can still serve as a bottleneck as it is restricted by the old-school RAM limitations of 32-bit processes. Routing this report to a 64-bit DQM server will resolve this problem.
  • You are making the leap to Dynamic Cubes:  This one is more of a preference than a hard-and-fast rule, but if you intend to realize the ridiculous performance gains that come with Dynamic Cubes I highly suggest you quarantine them to a 64-bit only server and route all Dynamic Cube traffic to that server. This will simplify troubleshooting and performance tuning for your cubes and secure your CQE queries against disruption. It also gives you a 64-bit landing place as you migrate legacy queries to DQM.
  • You are a new Cognos customer: If you are reading this having just inked your five year enterprise licensing agreement with IBM, you need to ensure that you are only building and developing in DQM. I cannot emphasize enough the importance of this point. I have it on very good authority that CQE will receive only bug-fixes going forward and that future versions of Cognos will execute CQE queries but all new development will be DQM only. Do not devote a single second to learning about or developing CQE content!

So there you have it!  If you are an existing Cognos customer you should feel fine leaving the report service execution mode on 32-bit and riding those CQE queries as long as you can. I recommend standing up a 64-bit DQM server only if you run into performance issues caused by the 32-bit report service for the time being.

Everyone needs to be planning a migration to 64-bit only, however. Dynamic Cubes are a great reason to start but the fact is that 64 bit Cognos is the future, and the future may be much closer than you think…

Yes that ending was intentionally cryptic. Not sure how much I can talk about yet… 😉

64-bit vs 32-bit Ain’t Just for Playstation and Nintendo

March 17, 2015 by Ryan Dolley 1 Comment

Redditor Aybabtu123 on the IBM Cognos subreddit asks:

“I’m putting together a 10.2.2 sandbox environment and I’d like to enable dynamic query mode on it. Is it true that all datasources must be 64 bit for this?”

This is a great question and one that IBM frequently discusses in a way that is clear as mud, so here is the answer as succinctly as I can phrase it. Assuming a 64-bit installation of Cognos on a 64-bit server with a 64-bit OS…

  • With the Report Server Execution mode in Cognos Configuration set to 32-bit, queries routed to this server will execute in either CQE or DQM depending on how the datasource and package have been defined.
  • With the Report Server Execution mode in Cognos Configuration set to 64-bit, queries routed to this server will only execute in DQM. CQE queries will error out.

Cognos 64-bit option
Where the magic happens

Cognos is automatically set to 32-bit Report Server Execution upon installation, so as long as you don’t change it your application servers will be able to execute both 32-bit (CQE) and 64-bit (DQM) queries – and by extension utilize both CQE and DQM datasources.

Tomorrow (or soon anyway) we’ll discuss what situations would warrant switching to a 64-bit only application server and the steps you need to take to properly route traffic. I’ll give you a big hint though, and it rhymes with crynamic dubes.

Copyright © 2023 · Atmosphere Pro on Genesis Framework · WordPress · Log in