More Workbench Fun: Data Views

Good Morning. I thought the next logical step in my little blogging journey through Cognos Now! would be a general description of one of my favorite objects in the workbench - the view. You may also hear people refer to them as "Business Views" or "Data Views" but for simplicity, I'm just going to call them "views" here. 

Let's start with a basic description. In Cognos Now! a view is an object that allows you to extract data from a data stream for reporting purposes. It is the most basic way to extract this data and provides an easy way to quickly move data from the data source to a Cognos report. All views in Cognos Now! are based upon data from a data stream (which I discussed in depth previously). Views can also be built upon other views, and lookups - so they can become fairly complex objects. Mastery of views in Cognos Now! is critical if you want to be able to create general operational reports that don't necessarily fit into a data cube (which I will cover at a later date).

A simple example of how to create a view is a great place to start. In a previous post I took you through how to create a basic data stream built upon an Oracle data source and a simple query (select sysdate from dual). This data stream will only report one record with one column - the current date / time of the database server. Once the datastream is setup and active, it's time to create the view:

- From the Cognos Now! workbench, click on a folder where you want the view to appear - this is important because once you create a view you really can't move it.

- Select Activities > Create New > View. A dialogue will appear asking you to select a datastream or derivative view on which to base your new view. This will be the data source that should contain most, or all of the information you need for your report. Here's what the dialogue looks like:

You can see that I've selected a data stream called "test_cbs_ds" and the dialogue shows the contents of that data stream (sysdate).  Of course you can also select a view as the source, but let's keep it simple here and stick with the data stream. Click Next to create your new view.

- The dialogue will then ask if you want to select any additional lookup tables for your view. These can be based on other views or lookup objects (to be covered later as well) and if you selected any object you would be creating an inner join between the lookup and your primary data source. Let's skip this and just select "OK" to move on.

- Now you will see the Configure View screen. The first thing we need to do is give the view a name. I like to use a standard naming convention to keep things simple (all view objects are named with VW at the end and use underscores instead of spaces). Let's call our new view TEST_SYSDATE_VW. You can also add an optional description.

- Now you need to add one or more columns to your view. You can do this two ways - the simplest is to drag the column from the field on the left to the Add Field area to the right, or use the Add Field dialogue. Here I have created the view, given it a description, and added a column:

- There are other options that can be useful as well. Note that you can edit the field expression directly by clicking the elipses next to the field expression for the column. In this field builder you can embed formatting commands, summary commands, and even build out complex case statements if necessary. There is a function builder available if you select the "More Functions" field. More information about functions is available using the online help - just select the blue "help" link in any window.

- Another option available under the Advanced tab is View Persistance. This useful feature allows you to save the content of the view dynamically to any data source that you can write to (such as the postgresql database embedded in your Cognos Now appliance). Persistance will be covered at a later date, but the basic funcationality is that every time the data stream the view is built upon refreshes, the new data will be stored to the database specified in the view's persistance configuration. To setup persistance go to the Advanced Tab then set persistance status to Enabled and use the Define link to configure the database connection.

- You can also quickly review the view's setup using the "Display SQL Expression" button. This will give you a read-only view of the SQL your view object is based upon (built by the interface). My test view's SQL looks like this:

SELECT      Development.test_cbs_ds.SYSDATE AS SYSDATE

FROM        Development.test_cbs_ds

- Select Save View to create your new view object.

- Once your new view is saved, you should see the object in the workbench object tree. You may need to set the permissions of your view to allow the appropriate users to access it. I'll cover security and roles at a later date, but you can easily set this up using the "Permissions" link visible after you click on the view object in the workbench. 

-Note once the view is created you can check the contents easily by clicking on it in the workbench and selecting the "Results" tab. This will show the first fifty or so records that the view contains. Here's what my new test view looks like:

An imporant concept to understand is that a freshly created view will not contain any information until the data stream it is based upon refreshes and collects new data. This means that our new view will not show any data initially. Don't panic if you get a "data not available" message on your results tab. Just go into the data stream the view is built on and set the refresh to occur soon. It's also important to realize that any time you make a change to a view, this situation will occur again - ie no data will be available until the data stream refreshes.

The exception to this rule is when your view is built upon another view. In the configuration below:

Data Agent > Data Stream > View 1 > View 2 

you can modify view 2 as often as you like and it will always show data, as long as view 1 contains data. With view 1 however, any change you make will clear the contents of itself and both view 1 and view 2 will be empty until the data stream refreshes. So, with this in mind, if you are building a view for a report, you may want to create the scenario above, and use view 2 as your "presentation view" that your report is built upon. This allows you to easily make changes to the reports data source (view 2) without having to refresh the data stream. This is a critical design methodology that took me a while to understand.

I hope this gives a bit more insite into Cognos Now! and views in particular, and makes it easier for you to quickly get up and running. Stay tuned for the next installment, when we actually create a report and dashboard!