Modern Business Intelligence (BI) systems as exemplified by IBM Cognos BI
In the modern world, there are certain classes of software that focuses mainly on the corporate segment (large and medium-sized businesses) and, accordingly, is not widespread. But some software systems have quite interesting functions that can be applied not only in the field of small business, but also as a personal tool. One of such software systems will be discussed in this article.
I am a technical specialist, so the article has a more technical bias. If you want to read product information that is focused on the business of users, then you need to go to the IBM offline site.
The main goal of this article is to show you how to make your first “Hello World” (similar to programming) in IBM Cognos BI.
I also want to note that I have a lot of experience writing step-by-step instructions with screenshots of each step. But this article will not be another step-by-step instruction, here I want to show the concept of working with the system, and not make another manual.
What is BI?
So what exactly is a BI system? In a nutshell, this is an advanced reporting system. To make it more clear, I will list below a list of the main functions that modern BI class systems have:
- the ability to connect to various data sources (from an Excel file to a universal ODBC connection)
- the ability to build both simple reports (such as a graph or table), and complex parameterized reports with a combined structure and link relationships (Drill-Trough, Drill-Up / Drill-Down)
- the ability to transparently work with different data sources (for example, Excel and SQL Server) with full processing of the relationships between them
- the possibility of interactive work with data (reporting on the fly)
- the ability to represent relational data as multidimensional
- the ability to distribute access rights using both internal authentication sources and external (NTLM, LDAP, etc.)
- the ability to start generating reports both manually and automatically according to a schedule
- the ability to automatically send generated reports
- the ability to build reports in various formats (Excel, HTML, PDF, etc.)
In simple Russian, a BI system is a program that provides the user with convenient tools for analyzing virtually any data (be it an Excel file or an industrial data warehouse).
Ability to use the BI system as a personal tool
The question immediately becomes, how can this system be used as a personal tool? I will answer by personal example, I use IBM Cognos BI as a tool for analyzing statistics in my projects and a tool for analyzing statistics of home accounting.
Here, of course, one can object, something in the spirit of “I analyze statistics perfectly with regular SQL queries” or “Excel built-in functions are quite enough to analyze all home accounting”, but “everything is known by comparison”. As practice shows, it is much easier to simply drag the necessary data elements with the mouse and get the result in a finished form, than messing with writing SQL queries or reconfiguring Excel functions.
Again, everything written is my personal opinion, with which you are not obliged to agree.
IBM Cognos BI Architecture
The architecture of the system is relatively simple (as for an enterprise-class system). So, the key element of the system is the IBM Cognos BI server (see the diagram below), which works with data sources using a user-created description (called metadata). Further, through Web access, the IBM Cognos BI server provides access to all the basic functions of the system.
To make your first report, you need to perform several basic steps:
- Create a data source connection
- Generate a description of the data source, i.e. create metadata
- Create and publish metadata package on IBM Cognos BI server
- Create Report
Test Data Source Structure
Before embarking on the implementation of the above steps, I want to say a few words about the test data source. On the one hand, the structure of the test data source is relatively simple (as for an industrial data warehouse), on the other hand, it is somewhat more complicated than a simple Excel worksheet. All data in the source is synthetic (generated by random number algorithms), because of this, aggregate indicators look very even. As you can see in the diagram above, the test database contains 3 hierarchical dimensions: “Product group -> Product”, “Continent -> Country -> City -> Point of sale”, “Year -> Half year -> Quarter -> Month -> Date"; 2 flat (one-dimensional) measurements: “Cashier”, “Regional Manager”; and 2 fact tables: Sales, Sales Plan.
Moreover, the “Cashier” dimension is located in one of the fact tables in a denormalized form, and the “Regional manager” dimension is tied to the “Country” level of the “Point of sale” dimension with a many-to-many relationship (meaning that one leader can manage different countries).
Data Source Connection
In IBM Cognos BI, all the necessary parameters for connecting to data sources are stored in special system objects, which are called Data Source Connections. To create a new connection, you need to follow a few simple steps: go to the IBM Cognos BI portal, go to the “Administration” section, open the “Configuration” tab, select the “Data source connections” subsection (“ Data Source Connections ”) and click on the“ New Data Source ”button in the toolbar. Next, a series of dialog boxes will appear in which you will need to set several parameters, such as the name of the connection, connection type, server, login, password, etc.
The development of metadata is one of the most difficult and crucial moments. The performance of the system (the speed of generating reports, the correctness of the generated results, etc.) and the convenience of developing reports depend on the quality of metadata. But despite the above, the complexity of developing metadata is directly proportional to the complexity of the data source. For example, to build a relational description of our test data source, just start the metadata building wizard, click the Next button several times, and the metadata is ready.
So, as I wrote earlier, metadata is a description of the data source. At IBM Cognos BI. The foundation of metadata is the Query Subject and the relationships between them. The Query Subject is a synonym for “View” from relational DBMS. That is, the basis for Query Subject is a query to the DBMS that defines the structure of the source object, and the relationship between Query Subject is a description of the logical interaction between these queries.
IBM Cognos BI uses a separate IBM Cognos Framework Manager application (the only non-Web application in the IBM Cognos BI suite) to create metadata. After starting Framework Manager, you will be prompted to create a new project (you will need to enter the name of the project and its location in the local file system).
It should be understood that the Framework Manager project (also referred to as the Framework Manager model) is a set of local files that the local program works with, and the metadata package is the result that is located on the IBM Cognos BI server (if we draw an analogy with programming, the project is source code, and the package is a compiled application). You can create multiple sets of packages from a single Framework Manager project.
After the Framework Manager project is created, it is best to start by starting the metadata import wizard (Action -> Run Metadata Wizard ...). The import wizard will offer you to select an existing data source or create a new one and allow you to select the necessary objects for import. In the simplest case (for example, when the data source is an Excel file, which in 99.9% of cases contains data in a denormalized form), it will be necessary for the fields of the Query Subject to set the correct type of use (the Usage attribute) and work with the model on this Framework Manager can complete and begin building and publishing the metadata package. In a more complex version (as in our test example), it will be necessary to check the correctness of the imported relationships between the Query Subject objects, correct the incorrect ones and add the missing ones.
Create and publish a metadata package
After the metadata has been created, it is necessary to form a metapackage and publish it on the IBM Cognos BI server. As I mentioned earlier, a metapackage is a subset of metadata that is published on a server and with which all Web applications of the IBM Cognos BI complex work. Metapackage settings allow you to hide or not publish some metadata objects. For example, in the test metadata there is a certain “Query Subject” [Country_RegDir], which affects the logic of processing the source data (it is the link between the country and the regional director), but does not represent value when developing reports, it makes sense to hide such a metadata object at package. Or, for example, fields with identifiers, it also makes sense to hide them from users of meta-packages.
To create a meta-package, you need to use the Framework Manager in the “Packages” section to call the context menu and select “Create -> Package”, after which the wizard for creating a meta-package will appear. After the meta-package is created, the system will immediately offer to publish it on the server. For a novice user, you don’t have to delve into the options of the package publishing wizard (just click the Next and Publish buttons). The only thing is that on the last tab (where there will be not the Next button, but the Publish button) there will be a “Verify package before publish” bird, it determines whether the meta-package is checked for logical ambiguities before publication and displays a list of these ambiguities if they are found. I strongly recommend that you never skip this step and correct any ambiguities found before publishing.
Reporting (data analysis)
So we slowly got to the most interesting and regular process - this is reporting. It so happened that the tools for creating regular reports and the tools for quick data analysis in IBM Cognos BI are the same (despite the fact that in some it is more convenient to conduct a quick analysis, while in others it is more convenient to generate regular reports, they all allow you to save your results in the form reports).
Personally, I prefer to use the IBM Cognos Report Studio tool for all BI tasks. This is the most versatile tool that allows you to build reports of virtually any complexity and at the same time provides relatively convenient tools for quick data analysis.
Suppose that we need to create a quick report containing the fact of sales by country, product group and frequency per quarter. This fairly simple report can be done by following these steps:
- run the IBM Cognos Report Studio web application
- in the welcome window, click the "create" button
- in the list of base templates select “crosstab”
- place data elements according to the diagram below
- run report
After starting the report for execution, you get something like this. Looking at the resulting report, we can safely say that it is openly poorly formatted, the numbers are not formatted, the economic meaning is openly doubtful, etc. But all these design flaws can be removed by setting the properties of the corresponding settings elements, and to make the economic sense more interesting, you can For example, make a plan / fact analysis. For example, to make the report shown below (on ready-made metadata), I, as a specialist with experience, spent about 20-30 minutes. And to completely re-arrange it into a dark color scheme, I spent somewhere else 10 minutes.
I hope that in this article the reader was able to get general information about BI systems and how they work. Of course, within the framework of a small article it is impossible to consider in detail any of the issues touched upon (for example, how to correctly form metadata, you can write a whole book), but I think that if you decide to try, this article will tell you where to start and what result expected.
Also, I didn’t touch on some interesting mechanisms and functions (for example, the mechanism for representing a relational data source as multidimensional), but this is because the amount of necessary material (minimum theory and minimum practice) will be pulled into a separate article.
A bit about licenses
If you decide to buy the IBM Cognos BI system separately for personal use or for a small company, then the prices probably will not surprise you, but IBM has a special integrated system IBM Cognos Express, which is designed for small organizations, contains several products (including BI) and is much cheaper.