If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Date Dimension without a data source in Report Studio

Started by tjohnson3050, 24 Nov 2016 05:07:33 PM

Previous topic - Next topic

tjohnson3050

I have been playing with date dimensions in Report Studio again.  In the past I have used SQL scripts that build date dimensions on the fly using recursive syntax.  DB vendors implement recursive syntax in different ways.  I have found a way to skip the DB altogether when building a date dimension on the fly in Report Studio. 

The following sql in a sql object will build a set of dates between two prompted date values by querying the report server, not the underlying db. Set the SQL Syntax to 'IBM Cognos'.  You still need to set a datasource on the SQL object, but it will not query the db. The benefit is one less db call (performance) and also Cognos syntax (portability). This only builds the date field.  I have also attached an xml fragment for a full query subject that builds other date dimension calculations on top of the date field returned by the sql script.

with dates as (
select #prompt('StartDate','date','current_date')# as  DT from (values('all_level')) "Query1.0"(c1)
union all
select _add_days(DT, 1) as DT from dates where DT < #prompt('EndDate','date','current_date')# )

select DT from dates

bdbits

While interesting and certainly creative, I would not skip putting a date dimension table in the database with links to facts. For one thing, no link to facts means additional processing will have to be done on the Cognos server (may or may not be significant). But the broader implication is that you are now tied into Cognos for any date work, and the warehouse is no longer tool agnostic. Cognos is a great tool and all, but I think everywhere I have worked with a data warehouse has had more than one thing consuming the data.

I would also suggest that the date dimension is small enough and joins nearly always on an index that in most cases, it is not going be a big issue in query optimization.

tjohnson3050

#2
100% agree with everything you said.  Given the choice, a data warehouse date dimension table is the way to go.  However, many in this community are tasked with building effective reports with Cognos against an existing model without the ability to add a date dimension table.  This solution is for those folks.

bdbits

Ah, this would be most excellent in that case. :-)