A Guided Tour of the OUS FIS Data Warehouse Cognos Data Model

A Guided Tour of the OUS FIS Data Warehouse Cognos Data Model

This is a brief guided tour of the OUS FIS Data Warehouse Cognos Data Model. The purpose of this document is to describe key elements of the FIS Cognos data model, particularly the differences between the BI Query data model and Cognos.

We assume that you are already authorized for access to the FIS Data Warehouse and Cognos. If this is not the case, please see your campus’ data security specialist or send an email to . We also expect that you are familiar with the FIS model in BI Query.

Starting the Tour

We’ll open the data model using these steps:

·  Run Cognos from: https://cognos.ous.edu/ibmcognos (Must be ‘https’, not ‘http’)

·  Sign in

·  At the Welcome page, select the “Author business reports” item

·  At the “Select a Package” window, open the “FIS Data Warehouse” folder and click on the “OUS FIS Data Warehouse” package (or choose it from the “Recently used” list)

·  In Business Insight Advanced, click “Create New”, then double-click “List”

The FIS Data Warehouse data model is now open.

The top level of the data model is shown at left. Items at this level are called “data subjects”. Clicking the small triangle to the left of the square symbol opens the data subject, revealing the “data objects” (or “tables”) within. The terms “data object” and “table” will be used interchangeably in the tour.

Similarly, clicking the small triangle to the left of the square symbol next to the data object will open the data object, and list all of the “attributes” (or simply, “fields”) within. The terms “attributes” and “fields” will be used interchangeably in the tour.

First Stop: General Ledger

Click the triangle to the far left of the “General Ledger” data subject. There are six data objects associated with that data subject, as shown in the screen clip on the right.

With Cognos, there is no graphical connection between related data objects, as there was with BI Query. The responsibility falls to each report author to know, for example, that “Account” and “Bank” don’t link together, but “Fund” and “Bank” do.

Nearly all the data subjects in FIS contain the first two data objects, “Campus” and “Fiscal Calendar,” and they are always listed first. They are key elements in creating efficient reports, so we’ll describe their purpose before going ahead with the tour.

The “Campus” Data Object

If you work at one of the university campuses, you may never have to use the “Campus” data object, since the set of data that you can work with is already site-specific. For people in the Chancellor’s Office that work with multiple sites, on the other hand, this data object is indispensable. There are only three attributes in “Campus,” as you can see in the screen clip to the right.

If you work at OIT, for example, the entire contents of the table are shown on the mini-report screen clip to the left. It may be useful to reference the attributes of this table if you want to have the university name or description appear in a report heading.

For Chancellor’s Office employees with multi-site responsibilities, the contents of “Campus” are much more extensive, as shown in the larger mini-report screen clip to the left.

The main function of the “Campus” data object is to enable quick filtering of large data sets—such as the General Ledger—down into a smaller set, consisting of a single campus or a set of campuses. In addition, “Campus” is connected to virtually all of the other data objects, so filtering by campus is immediately reflected throughout the FIS data warehouse. For example, filtering by Campus Name = “OIT” will cause data in the General Ledger to be filtered to just the OIT data, and the same restriction will also apply to data in the Fund and Account tables.

The fast access with this kind of data object is especially important when setting up report prompts for end-users. By default, Cognos wants to list all the available options and allow the user to choose. If Cognos has to read all the data in the General Ledger to find the 8 possible campus values, the result is very slow; but using “Campus” for the filter is lightning fast.

The “Fiscal Calendar” Data Object

Most robust data models include a “Date” or “Time” dimension. In the FIS world, the most important time units are the Fiscal Year and often, the Fiscal Period within the year. We’ve opted to keep it simple by providing a miniature time dimension called “Fiscal Calendar.”

Like “Campus,” the “Fiscal Calendar” data object exists to enable efficient data filtering. It has just 9 attributes, as you can see in the screen clip to the left. The first two, Fiscal Year and Fiscal Period, are the most important—but the others fill particular roles, too.

The Period Start/End Dates and Year Start/End Dates can be useful if, for example, you want your report title to display the dates for an audience not familiar with Banner fiscal periods.

Fiscal Quarter is ‘1’, ‘2’, ‘3’ or ‘4’ (or null for Period 14).

The Period Status and Year Status are demonstrated in the screen clip below. Either status attribute can be ‘Closed’, ‘Open’, or ‘Future’. The statuses are updated nightly and are universal, not campus-based—therefore, the exact close timing may not match each campus’ processing calendar. We are considering making the “Fiscal Calendar” table campus-aware in the next release of the FIS Model. Please let us know if this would be of interest.

The “General Ledger” Data Object

The “General Ledger” data object in Cognos is similar to the equivalent data object in BI Query, with a few key differences:

·  There is no COAS Code. The COAS Code reflects a campus identity, and the campus should be selected from the “Campus” data object.

·  There is no Fiscal Year or Fiscal Period. These should be selected from the “Fiscal Calendar” data object.

·  The amount columns are formatted to be presented as US Dollars. Where BI Query would show 1234.56, Cognos will show $1234.56 and negative values will be in parentheses, rather than denoted with a minus sign.

·  The “General Ledger” object contains FOAPAL elements and descriptions; the “General Ledger” object also has links to support tables for the FOAPAL elements. It would appear that you could pull Program Code and Description, for example, from either place. But there’s a fundamental difference in the two options.

o  If you show the Program Code and Description from the “General Ledger” object attributes, you’ll get all qualifying rows from the “General Ledger” whether there is a value in Program Code or not. This is known as an “outer join”.

o  If you show the Program Code and Description from the “Program” data object, you’ll only get the rows from the “General Ledger” object that actually have a value in Program Code. This is known as an “inner join”.

This is identical to the behavior in BI Query, and has been known to cause confusion. We are considering making the Ledger data objects’ links to the FOAPAL tables use the “outer join” behavior by default; you can still add a filter to force the “inner join”. Please let us know if this would be of interest.

“Account”, “Fund” and “Bank” Data Objects

As with “General Ledger”, there is no COAS Code or Fiscal Year in these tables; instead, use the special “Campus” and/or “Fiscal Calendar” to select, or filter, these criteria. In addition:

·  Most of the code/description combinations also have an equivalent “ID” attribute. For example, Account Code and Account Description have a corresponding Account ID. This attribute is the code and description merged together; for example, “A0330 [Student Activities Cash]”. These attributes were built to speed and simplify presenting code/description combinations in section and page headings, especially.

·  Some data objects, such as “Fund,” have attributes and one or more folders. For

example, the “Fund” data object has all of the Fund Level attributes in a folder as shown in the screen clip to the right. As with the other levels of the model, click the triangle to the left of the symbol to open the container. The idea is to relegate logical groups of less-often-used attributes to folders in order to simplify the appearance of the data subjects. If you have suggestions on what attributes are “folder-worthy”, please let us know.

That’s a walk through a typical data subject. For the remainder of the tour through the model’s data subjects, we’ll limit commentary to unique features of the Cognos data model.

Next Stop: Operating Ledger

This data subject has a lot in common with the “General Ledger” data subject. Besides the main

data object, “Operating Ledger” (shown in the screen clip to the left) has the “Campus” and “Fiscal Calendar” tables, support data objects for all six FOAPAL elements plus Indexes, and tables for “Grant” and “Agency”. There’s also a unique data object, “Operating Ledger Crosstab”.

The “Operating Ledger Crosstab” Data Object

This data object turns the regular Operating Ledger on its side: each FOAPAL combination gets one row/fiscal year, and there are columns in each row for each period’s Revenue, Expense/Transfer, Net, Total, and Adopted Budget amounts. Here’s a sample of the available amounts in the screen clip to the right:

This format makes it easier to look at activity from period to period throughout a year, and is a natural for export into Excel. The “Operating Ledger Crosstab” is supported by all the same data objects as “Operating Ledger”, so you can use whichever of the two structures makes the most sense for any particular report.

Currently, the downside to the “Operating Ledger Crosstab” is that it’s sometimes slow to run. We’re considering some steps to speed it up for the next release of the FIS Model. Please let us know if this would be of interest.

Next Stop: Fixed Assets

The Cognos “Fixed Assets” data subject looks a lot like its BI Query equivalent. The major difference results from the BI Query model’s two potential “loops” in joining data objects. These “loops” are places where the query could follow more than one possible route to link the data together. They are:

1. Fixed Assets + Fixed Assets Funding + Capitalization & Depreciation
2. Adjustment Detail + Adjustment Accounting + Adjustment Header (shown in the screen clip to the right)

SQL doesn’t tolerate such ambiguity. BI Query’s way of resolving the issue was to prevent the query from running until a route was explicitly chosen by the user. To do this, you had to click on the specific graphical link between data objects that you wanted your query to use.

Cognos doesn’t have a graphical representation of the links between data objects. Instead, Cognos gives data modelers the ability to call a single data object by multiple names, where each ‘alias’ denotes a clear route for the query to follow. Then, the report author can select the correct route by choosing which data objects to use in the query.

The two aliases in “Fixed Assets” are highlighted in the screen clip to the left. They are:

1. “Capital Depreciation Funding” (for “Fixed Assets Funding”)
2. “Adjustment Accounting Header” (for “Adjustment Header”)

Let’s work through an example. Suppose you need to pull data from “Adjustment Header,” “Adjustment Detail,” and “Adjustment Accounting” data objects. If you’re looking for the specifics of a single adjustment document, you may want to start from “Header” and pull associated “Detail” and “Accounting.” Or, you may want to filter “Detail” or “Accounting” data for specific characteristics, and then pick up associated “Header” data. Here’s how to tell Cognos which linkage you want:

·  If you want the starting point of your query to be the “Adjustment Header” table, you can use either the “Adjustment Header” data object or the alias, “Adjustment Accounting Header”. The route is different, but the query will work the same either way.

·  If you want to start from the “Adjustment Detail” data object, select header information from the “Adjustment Header” data object. This will select the direct route from “Detail” to “Header.”

·  The only time you must use “Adjustment Accounting Header” is when you want to start from “Adjustment Accounting” and link to “Adjustment Header” (the tip-off is the name—“Adjustment Accounting Header”—that combines the two component data objects). This will tell Cognos to use the link that goes directly from “Accounting” to “Header.”

The “Adjustment Header” and “Adjustment Accounting Header” are identical in all ways except the name—so you can select the same attributes from either one; the only reason for the alias is to enable the conditions of the join to be explicit.

Final Stop

Thanks for taking the tour! We hope it was helpful in understanding how the Cognos data subjects are set up, and you can see the possibilities for using the FIS data to create reports, graphs, metrics, etc. using the Cognos reporting suite.

If you have questions or comments, please contact John Peters at or (541) 737-3349. This is just the first version of this document; we’d like to see it become a full-size tutorial, so your input would be greatly appreciated.

3/2/2012 4:57 PMPage 1