List Tables & Columns

The following SQL can be used to list tables and column names

SQL server

select TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
from .information_schema.columns

Oracle

select OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH
DATA_PRECISION
from ALL_tab_cols;

The inverted star schema

I have been using this method of architecture for reporting for sometime and it works well, despite flying in the fact of 'accepted' theory.

We all know that the secret to fast reports is to put as much processing into the data warehouse data mart as possible.

Some of us also know that this means making sure that the tables that are used by the reports are in the best structure possible, with the least amount of granularity possible.
The trouble with doing this is that by the time we have finished transforming the data we have an effective 2nd level of aggregation for the data in the data warehouse. Sometimes these tables are so focused for an individual report that they include both the fact and dimension date in one object.

This makes the reports fast - very fast. The only other work that you need to do is to ensure that the columns being used for filtering or grouping data is index with a good navigation index.

In Cognos ReportNet or Cognos Series8 you have the option of controlling data content within a report by using 'row level security'. You can also do a lookup to your security datasource (LDAP or NT normally). This makes the filtering by user dynamic.

Problem:
By holding the user's names in the table you are running the report from increases the granularity and slows down the report.

Assuming each user should only be able to review data for the customer to which they belong, the answer is this:
Create a user class for each customer. Grant membership to the appropriate customer user class for each user (you can do this by running an LDAP update routine).

Create a table in the data warehouse that lists the customer numbers. It is this table where the row level security should be set up. In the Framework Manager model then create a 1:n relationship to each 'fact' table you have.

Hey presto, when you now run the reports as a different user the reports are filtered to the appropriate customer.

The system is doing this:
ReportNet / Series8: Knows the user and therefore the user classes.
ReportNet / Series8: When running the report it runs a filter against the central dimension table (customers) by using the userclass from the LDAP.

So within the Framework Manager model, you have one central dimension table with multiple fact tables around the outside - An inverted star schema.

I hope you like it.

Phil

Welcome to the Cognos Blogger

Hi and welcome to the Cognos blog.
I'm intending to use this blog to post new techniques and answers to other Cognos developers questions. If you need help - just ask!

Cheers

Phil