Reporting and Visualizations Tools

                    

With ever changing world and Business, the way organizations and management look at the data is also changing. A few years back the enterprise reporting tools like Cognos, BO, Micro-strategy were backbone of reporting.

They were the driving force for decision making and running business.

Then there was a shift to dash-boarding and offline reporting. That was technically more related to the in Memory caching of the data. Users no more wanted the screenshot of the dashboard. They wanted interactive reports and dashboards that could give them capability of slicing and dicing and allow them to look at the data with all possible dimensions.

Plus the major change they wanted was Self Service BI.

"A study showed that only 10-20% of written/spoken content is remembered, while 65% is remembered if displayed visually."

All this paved way for visualization tools to grab their market share which they deserved for Long. With the popularity for such tools started a competition to get the maximum share and procure maximum projects and clients.

Microsoft Power BI, Tableau, QlikView started gaining popularity, the ease of use, self service capability and attractive visuals, charts have added to the popularity.

There has been a gradual shift towards the use of Visualizations and dashboards and these tools. The enterprise reporting exists and there is no replacement for that, but eventually they have taken backseat in the current scenario.

IT giants are investing heavily in visualization tools to make them more interactive and power packing them with features like integration to vast number of data sources including latest ones like hadoop. Integration to Python and R to get data science scripts on the reports.

Most popular tools these days are Tableau, Microsoft Power BI, QlikSense, QlikView.

Google analytics is free and catching up. Google Data Studio is another tool. Looker is gaining popularity. Amazon Quicksights is making breakthrough.

Today there are more than a dozen visualization tools available with their on USP.

With little doubt the coming years in BI space belong to Visualization tools, these tools actually provide the eye catching visual to the data a data scientist or a ETL expert or DW developer works on.

KPIs, Summary, Charts make them attractive and provide complete data story in single report.

I believe "The visualizations on the report should be able to tell a story" , that is what would drive the growth story of these tools.

Burst Cognos Report to file system

Report Bursting

This document describes Cognos report burst procedure to store output to File system.

Cognos Configuration setting for Report Bursting. (Cognos Server)

Step-1: Create a shared folder on cognos server or any network area where all user going to check the report output.”BURST FOLDER”

Step-2: Go to Cognos Configuration, and navigate to Actions… Edit Global Configuration

Step-3: Under General, enter the value prefixed with file://

Step-4: Returning back to the main configuration screen, select Data Access… Content Manager, and set the Save Report Outputs… value to True

Cognos Administration  setting for Report Bursting. 

Within Cognos Connection we define what the actual destination output locations within this folder will be.

Step-5: Open up IBM Cognos Administration from the Launch menu in Cognos Connection. Then navigate to the Configuration tab and select Dispatchers and Services, and in the upper right side of the screen select Define File System Locations:

Step-6: Give the new location a name under the Name section (Report Name) then give it a location – this is where it will appear under the output file folder you set up above. 

Setting a report up for bursting is performed in the Report Studio interface

Step-7: In report studio design the report as per the requirement.

Step-8: From the File menu, click Burst Options. Select the Make report available for bursting check box.

Step-9: Under Burst Groups, in the Query box, click Query1. In the Label box, click “First_Name”. In the Data items box, drag “First_Name” to the Groups folder and click OK

Step-10: Under Burst Recipient, in the Query box, click Query1, In the Data Item box, click “First_Name” In the Type box, click Directory entries then Save the report. If the Directory entries does not work you can set it to email, this also works fine for file system.

Step-11: Locate the report in IBM Cognos Connection.

Step-12: Under Actions, click Run with options.

Step-13:  Click advanced options on the right of the screen.

Step-14: Under Time and mode, click Run in the background.

Step-15: Select the Burst the report check box.

Step-16: Select the save to the file system check box.

Step-17: You can execute report in any format excel,PDF,CSV. PDF output also gives you a choice to set password for output file.

The burst report output will be saved in the directory as per “First_Name” User can see  the report output by  access the folder.

Problem Statement: Cognos Locks the database tables.

Problem Statement: Cognos Locks the database tables.

Transaction Isolation Property.

Depending on how you use Cognos, you want to have it access your database in different ways.  This access affects how Cognos interacts with other process accessing and modifying your database.  For an overview of what Isolation Levels in Cognos means for various database providers, A very useful document by IBM l document here. This document from  IBM describes the level to be set for different database (ex Informix,oracle,DB2 etc). 

1.Read Uncommitted

Changes made by other transactions are immediately available to a transaction.

2.Read Committed

A transaction can access only rows committed by other transactions.

3.Cursor Stability

Other transactions cannot update the row in which a transaction is positioned.

4.Reproducible Read

Rows selected or updated by a transaction cannot be changed by another transaction until the transaction is complete.

5.Phantom Protection

A transaction cannot access rows inserted or deleted since the start of the transaction.

6.Serializable

A set of transactions executed concurrently produces the same result as if they were performed sequentially.

The Lowest level for SQL Server is Read Uncommitted. So if you want your database tables not to be affected by the reports running, set the Isolation level property to Read Uncommitted.

For our example we will consider having SQL Server as the Database and Isolation level set to Read Uncommitted. This means Cognos will read the database without creating Locks and ignoring all Locks in place.

What is a lock?

This goes by the literal meaning. A database Lock works just like a Drawer lock or the lock you put up at your house when you got to the office.  I go to office and lock my house, now my roommate wants to open the house and keys are with me, he cannot get inside unless I go and unlock, for instance no one can get inside my house unless they have keys or I go back and open the lock. This is good for me considering the fact that my stuff is safe at home.

Read Uncommitted doesn't add a lock. I don’t lock my house means others can mess with it, anyone irrespective of being my friend/roommate. Vulnerable for my house and potentially bad for the database. But since Cognos is Read Only anyway, I'm fine if someone changes the database (people are updating a record) while I'm running a report. Also if my report hooks up, I don't leave locks behind

Read Uncommitted ignores all Locks, which means if someone else is working on a row before I run a report, I can still read it to display.  Once in a while that will get me wrong or weird results, but it means I don't have to worry about people doing their work at the database or ETL level. Since Cognos isn't writing, it's not worrying so much about it being different the next time it gets to that row.  It will fire the same SQL statement the next time, even if the row is updated by any process.

 Follow steps below to set Isolation property

·         Launch Cognos Administration

·         Click on the Configuration Tab

·         Click the name of the Data Source you want to edit

·         Click on the Properties icon (to the right) of the Connection in the Data Source

·         Click the Connection Tab

·         Under Isolation Level, click the Radio Button by "Specify a Value" and use the drop down to select the value that works best for you.

·         Click OK

New Connections to Cognos will use the new Isolation Level.  You can force this by restarting the Cognos Services.  This can also be done from Framework Manager Level.

How to implement multilingual functionality in a FM Model

Introduction

This document illustrates detailed steps to create a multilingual model. No reporting tool has inbuilt language translation capability and this has to be done explicitly.  Modelers add multilingual metadata by defining which languages the model supports, translating text strings in the model for things such as query subject & query item names and descriptions, and defining which languages are exported in each package.

Create a Multilingual Project for Relational Metadata

If your project wants to create a model that can be used by English and German report authors; You also want the IBM Cognos 10 studios to automatically show metadata in the language required by the report author then open the project and perform the below listed steps to embed multilingual capability to your metadata .

Consider we have following model in which we want to include multilingual functionality. And we want to see all the data items of Business layer in English and German.

Following are the steps to achieve our objective

Define Language           

1. Open the FM project.

2. From the Project menu, click Languages, Define Languages.

3. In the Available languages box, select each language you want to add and click the arrow button to move it to the Project languages box.

  

4. If you want to change the active language, in the Project languages box, click a language and click Set as Active. In our case active language is English (encircled in red).

5. Click OK.

At the prompt, accept the changes you made to the project.

6. Click OK.

7. If you want to view multilingual property values in the Properties pane, click the Languages tab.  You can observe that if we change the language to German the names of all objects (Data item and Query subject) are changes and (de) is prefixed to each name. This is shown in below screenshot.

In the Project Viewer pane, click a query item and, in the Properties pane, click the Languages tab. For the name, description, and tool tip text, you see one entry for each language. Now we will manually translate these names (prefixed with(de)) to german language.

Export Language Translation

Export all the languages and objects in the project to a comma-separated value file (.csv) named name.csv. Steps:

1. From the Project menu, click Languages, Export Translation File.

2. In the Project Languages box, Ctrl+click English and German, and click the top arrow to move them to the Languages to be exported box.

3. In the Export languages to this file box, enter the location of <name>.csv.  in this case I have used multilingual.CSV. Select the “Selected objects” radio button since we want to translate only Business layer components. If you we to translate entire model then select “All”

4. Once the translation is exported open the multilingual.csv file in Microsoft Excel, and translate the strings as shown below:

Each column represents a given language, and the file contains only the text strings that exist in the model.

Import Language Translation

Now we will import the updated translation file .The imported file must be a translation table that was used by translators to enter the required translated values. Steps

1. From the Project menu, click Languages, Import Translation File.

Following pop up  appears

2. In the Project Languages box, click the languages in the translation table, and click the arrow buttons to move them to the Translate from and Translate into box.

3. In the Apply translation to box, select whether you want to apply the translation to all model objects, or only to preselected objects and their children.

4. Enter the location and name of the translation file.

 Click OK.

Check for the FM – Language Translation

Once the translation is complete; we can review the FM model by ensuring Active Language set to English as shown below.

As soon as active language is changes to English, the translated member names start appearing in English. Same thing applies if the active language is set to German.

Publish the metadata

Create and publish the package into Public Folder. We need to make sure that all the preferred languages got included into the model as shown below. Languages can be specified while creating the package or at a later stage from the package propertiesà languages in the properties pane appearing at the bottom part.

Click Ok.

Cognos Connection

To check the behavior of multilingual metadata that we have published, we need to change the Cognos Connection content language to the preferred language. In this case we change it to German/English (depends on the language we want user to see data in).

Please note that this is set by the administrator for each user and we will set this to German for users sitting in Germany and English for those in the United States.

Click OK.

Query Studio Appearance

Open Query Studio to ensure that the metadata got translated into the desired content language.

If the language is set to German, metadata appears in German Language.